Excel讀入dataset數據丟失的問題!

摘要：研究了ISAM驅動限制，分析了在.Net中采用Microsoft.Jet.OLEDB讀取Excel文件產生數據丟失的原因，給出了解決此問題的方法。
　　關鍵詞：Excel;數據訪問;Ole DB;ISAM
　　中圖分類號：TP 311.1文獻標識碼：A 文章編號：1009-3044(2007)04-10937-02
　　
　　1 引言
　　在應用程序的設計中，經常需要讀取Excel數據或將Excel數據導入轉換到其他數據載體中，例如將Excel數據通過應用程序導入SQL Sever等數據庫中以備使用。筆者在開發“汽車產業鏈ASP協同商務平台”中遇到了類似需求。某汽車整車生產企業需要將其車輛發車信息發布到汽車產業鏈平台上去，其數據為內部ERP系統生成的Excel數據表，用戶首先將該數據表上傳至汽車產業鏈平台，平台將此Excel數據讀取導入到平台內部的SQL Sever數據庫中，以供其它應用使用。汽車產業鏈平台的開發使用的開發工具為VS.Net，使用的語言是C#，在開發的過程中發現使用Microsoft.Jet.OLEDB.4.0讀取數據會出現當某一字段內分別含有文本和數字的混合數據時，某一類型的數據會產生丟失。本文就對此問題產生的根源進行了分析並給出了相應的解決方法。
　　
　　2 問題描述
　　Excel是Microsoft公司的電子表格處理軟件，在現代辦公及企業信息化的應用中使用非常廣泛，正因如此，在程序設計中我們經常要通過訪問Excel文件來獲得數據，但Excel文件不是標准數據庫[1]。
　　ASP.NET也是Microsoft公司的產品，作為.NET FrameWork框架中的一個重要組成部分，其主要用於Web設計。我們在.Net中訪問讀取Excel數據時一般采用Microsoft.Jet.OLEDB.4.0[2]。現以讀取一個Excel文件auto.xls中sheet1工作表為例，工作表的內容如表1所示。
　　表1 sheet1表的數據內容
　　現將該表的數據內容讀取並顯示到到DataGrid中，簡化的代碼如下：
　　String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0; DataSource=c:/auto.xls;Extended PropertIEs=''Excel 8.0;HDR=YES'';";
　　OleDbConnection Conn=new OleDbConnection(ConnStr);
　　Conn.Open();
　　string SQL="select * from [sheet1$]";
　　OleDbDataAdapter da=new OleDbDataAdapter(SQL,ConnStr);
　　DataSet ds=new DataSet();
　　da.Fill(ds);
　　DataGrid1.DataSource=ds;
　　DataGrid1.DataBind();
　　Conn.Close();
　　但是運行以上代碼的結果並不是期望的，它將顯示為表2所示的內容。可以發現第一個字段中為“1042”的兩個數據項變為空。
　　表2 DataGrid1所顯示的數據內容
　　有程序設計人員將以上代碼OleDbConnection連接字符串中的Extended Properties一項作了如下改動，Extended PropertIEs=''Excel 8.0;HDR=NO;IMEX=1’，認為可以解決此問題。由於在開發“汽車產業鏈協同商務平台”中碰到過類似問題，作了大量的測試後發現，添加IMEX=1後並未實質上解決此問題。表現為：如果某字段前8條記錄中全部為純數字的話，那麼在該字段隨後的記錄中含有字母或漢字的項將仍然變為空，但是如果該字段前8條記錄中有一條不為純數字，將能得到預期想要的結果。
　　
　　3 問題分析
　　產生這種問題的根源與Excel ISAM[3]（Indexed Sequential Access Method，即索引順序存取方法）驅動程序的限制有關，Excel ISAM 驅動程序通過檢查前幾行中實際值確定一個 Excel 列的類型，然後選擇能夠代表其樣本中大部分值的數據類型[4]。也即Excel ISAM查找某列前幾行（默認情況下是8行），把占多的類型作為其處理類型。例如如果數字占多，那麼其它含有字母等文本的數據項就會置空；相反如果文本居多，純數字的數據項就會被置空。
　　現具體分析在第1節程序代碼Extended PropertIEs項中的HDR和IMEX所代表的含義。HDR用來設置是否將Excel表中第一行作為字段名，“YES”代表是，“NO”代表不是即也為數據內容；IMEX是用來告訴驅動程序使用Excel文件的模式，其值有0、1、2三種，分別代表導出、導入、混合模式。歡迎光臨學網,收藏本篇文章 [1] [2] [3]

$False$

當我們設置IMEX＝1時將強制混合數據轉換為文本，但僅僅這種設置並不可靠，IMEX＝1只確保在某列前8行數據至少有一個是文本項的時候才起作用，它只是把查找前8行數據中數據類型占優選擇的行為作了略微的改變。例如某列前8行數據全為純數字，那麼它仍然以數字類型作為該列的數據類型，隨後行裡的含有文本的數據仍然變空。
　　另一個改進的措施是IMEX＝1與注冊表值TypeGuessRows配合使用，TypeGuessRows 值決定了ISAM 驅動程序從前幾條數據采樣確定數據類型，默認為“8”。可以通過修改“HKEY_LOCAL_MacHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的該注冊表值來更改采樣行數。但是這種改進還是沒有根本上解決問題，即使我們把IMEX設為“1”， TypeGuessRows設得再大，例如1000，假設數據表有1001行，某列前1000行全為純數字，該列的第1001行又是一個文本，ISAM驅動的這種機制還是讓這列的數據變成空。
　　
　　4 解決方法
　　從以上的分析中可以得知，當某列數據中含有混合類型時，在.Net中使用Microsoft.Jet.OLEDB.4.0來讀取Excel文件造成數據丟失是不可避免的，要解決這個問題只能考慮采用其它數據讀取方法。
　　在.Net中讀取Excel文件的另外一種方法是回到使用傳統COM組件，這種方法在很多技術文章或論文中都有涉及，本文不作贅述。需要指出的是，使用COM組件來讀取Excel文件數據的效率較低，在作釋放的時候有可能碰到不可預知的錯誤，特別開發Web應用的程序應該慎重使用。  

本文提出另外一種利用讀取CSV純文本格式解決此問題的方法。
　　（1）在讀取Excel的.xls類型的文本數據之前，先將其轉換為.csv格式，在Excel中直接另存為這種格式就可以達到轉換的目的。CSV文件又稱為逗號分隔的文件，是一種純文本文件，它以“,”分隔數據列，本文表1的數據表用CSV格式存儲後用純文本編輯器打開的表現形式如表3所示。
　　表3 采用CSV格式保存的表1數據
　　需要指出的是，CSV文件也可以用Ole DB或ODBC的方式讀取，但是如果采用這些方式讀取其數據又會回到丟失數據的老路上，ISAM機制同樣會發揮作用。
　　（2）采用普通的讀取文本文件的方法打開文件，讀取第一行，用“,”作為分隔符獲得各字段名，在DataTable中創建對應的各字段，字段的類型可以統一創建成“String”。
　　
本文原文
　　（3）逐行讀取數據行， 用“,”作為分隔符獲得某行各列的數據並填入DataTable相應的字段中。
　　實現的簡化代碼如下：
　　String line;
　　String [] split = null;
　　DataTable table=new DataTable("auto");
　　DataRow row=null;
　　StreamReader sr=new StreamReader("c:/auto.csv",System.Text.Encoding.Default);
　　//創建與數據源對應的數據列
　　line = sr.ReadLine();
　　split=line.Split('','');
　　foreach(String colname in split){
　　table.Columns.Add(colname,System.Type.GetType("System.String")); }
　　//將數據填入數據表
　　int j=0;
　　while((line=sr.ReadLine())!=null){
　　 j=0;
　　 row = table.NewRow();
　　 split=line.Split('','');
　　 foreach(String colname in split){
　　 row[j]=colname;
　　 j++;}
　　 table.Rows.Add(row);}
　　 sr.Close();
　　//顯示數據
　　dataGrid1.DataSource=table.DefaultVIEw;
　　dataGrid1.DataBind();
　　
　　5 結語
　　在應用程序的設計中，需要訪問Excel數據的情況非常普遍，文章整理:學網 http://www.xue5.com (本站) [1] [2] [3]

本文以在.NET中對訪問含有混合類型數據的Excel表格擬采取的方法進行探討。當然，如果不存在混合類型的數據使用Microsoft.Jet.OLEDB為較佳方案。對於不是使用.Net開發的情況，本論文的分析和所提供的方法亦可參考。
　　參考文獻：
　　[1]Linuxmine.利用ASP.Net來訪問Excel文檔[EB/OL]. http://www.Linuxmine.com/77726.Html,2007-1-22.
　　[2]劉洪成.C#高級編程[M]. 北京:清華大學出版社,2003. 187-200.
　　[3]肖正宏,曹元大,韓秋風.數據訪問技術——DAO、ADO、RDO的比較[J].電腦與信息技術,2001,(1):31-32.
　　[4]PBR.Excel使用DAO OpenRecordset NULL作為返回值[EB/OL].http://support.microsoft.com/kb/194124,2004-6-24.
　　本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文。