Excel讀入dataset數(shù)據(jù)丟失的問題!
dev.firnow.com 時間 ： 2010-11-15 作者：佚名 編輯：fnw 點擊： [ 評論 ]
-
-
1 引言 在應(yīng)用程序的設(shè)計中，經(jīng)常需要讀取Excel數(shù)據(jù)或?qū)xcel數(shù)據(jù)導(dǎo)入轉(zhuǎn)換到其他數(shù)據(jù)載體中，例如將Excel數(shù)據(jù)通過應(yīng)用程序?qū)隨QL Sever等數(shù)據(jù)庫中以備使用。筆者在開發(fā)“汽車產(chǎn)業(yè)鏈ASP協(xié)同商務(wù)平臺”中遇到了類似需求。某汽車整車生產(chǎn)企業(yè)需要將其車輛發(fā)車信息發(fā)布到汽車產(chǎn)業(yè)鏈平臺上去，其數(shù)據(jù)為內(nèi)部ERP系統(tǒng)生成的Excel數(shù)據(jù)表，用戶首先將該數(shù)據(jù)表上傳至汽車產(chǎn)業(yè)鏈平臺，平臺將此Excel數(shù)據(jù)讀取導(dǎo)入到平臺內(nèi)部的SQL Sever數(shù)據(jù)庫中，以供其它應(yīng)用使用。汽車產(chǎn)業(yè)鏈平臺的開發(fā)使用的開發(fā)工具為VS.NET，使用的語言是C#，在開發(fā)的過程中發(fā)現(xiàn)使用Microsoft.Jet.OLEDB.4.0讀取數(shù)據(jù)會出現(xiàn)當某一字段內(nèi)分別含有文本和數(shù)字的混合數(shù)據(jù)時，某一類型的數(shù)據(jù)會產(chǎn)生丟失。本文就對此問題產(chǎn)生的根源進行了分析并給出了相應(yīng)的解決方法。
2 問題描述
Excel是Microsoft公司的電子表格處理軟件，在現(xiàn)代辦公及企業(yè)信息化的應(yīng)用中使用非常廣泛，正因如此，在程序設(shè)計中我們經(jīng)常要通過訪問Excel文件來獲得數(shù)據(jù)，但Excel文件不是標準數(shù)據(jù)庫[1]。
ASP.NET也是Microsoft公司的產(chǎn)品，作為.NET FrameWork框架中的一個重要組成部分，其主要用于Web設(shè)計。我們在.NET中訪問讀取Excel數(shù)據(jù)時一般采用Microsoft.Jet.OLEDB.4.0[2]?，F(xiàn)以讀取一個Excel文件auto.xls中sheet1工作表為例，工作表的內(nèi)容如表1所示。 表1 sheet1表的數(shù)據(jù)內(nèi)容 現(xiàn)將該表的數(shù)據(jù)內(nèi)容讀取并顯示到到DataGrid中，簡化的代碼如下：
顯示代碼打印01 String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0;
02
03 DataSource=c:/auto.xls;Extended Properties='Excel 8.0;HDR=YES';";
04
05 OleDbConnection Conn=new OleDbConnection(ConnStr);
06
07 Conn.Open();
08
09 string SQL="select * from [sheet1$]";
10
11 OleDbDataAdapter da=new OleDbDataAdapter(SQL,ConnStr);
12
13 DataSet ds=new DataSet();
14
15 da.Fill(ds);
16
17 DataGrid1.DataSource=ds;
18
19 DataGrid1.DataBind();
20
21 Conn.Close();
但是運行以上代碼的結(jié)果并不是期望的，它將顯示為表2所示的內(nèi)容?？梢园l(fā)現(xiàn)第一個字段中為“1042”的兩個數(shù)據(jù)項變?yōu)榭铡? 表2 DataGrid1所顯示的數(shù)據(jù)內(nèi)容
有程序設(shè)計人員將以上代碼OleDbConnection連接字符串中的Extended Properties一項作了如下改動，Extended Properties='Excel 8.0;HDR=NO;IMEX=1’，認為可以解決此問題。由于在開發(fā)“汽車產(chǎn)業(yè)鏈協(xié)同商務(wù)平臺”中碰到過類似問題，作了大量的測試后發(fā)現(xiàn)，添加IMEX=1后并未實質(zhì)上解決此問題。表現(xiàn)為：如果某字段前8條記錄中全部為純數(shù)字的話，那么在該字段隨后的記錄中含有字母或漢字的項將仍然變?yōu)榭眨侨绻撟侄吻?條記錄中有一條不為純數(shù)字，將能得到預(yù)期想要的結(jié)果。
3 問題分析 產(chǎn)生這種問題的根源與Excel ISAM[3]（Indexed Sequential Access Method，即索引順序存取方法）驅(qū)動程序的限制有關(guān)，Excel ISAM 驅(qū)動程序通過檢查前幾行中實際值確定一個 Excel 列的類型，然后選擇能夠代表其樣本中大部分值的數(shù)據(jù)類型[4]。也即Excel ISAM查找某列前幾行（默認情況下是8行），把占多的類型作為其處理類型。例如如果數(shù)字占多，那么其它含有字母等文本的數(shù)據(jù)項就會置空；相反如果文本居多，純數(shù)字的數(shù)據(jù)項就會被置空。
現(xiàn)具體分析在第1節(jié)程序代碼Extended Properties項中的HDR和IMEX所代表的含義。HDR用來設(shè)置是否將Excel表中第一行作為字段名，“YES”代表是，“NO”代表不是即也為數(shù)據(jù)內(nèi)容；IMEX是用來告訴驅(qū)動程序使用Excel文件的模式，其值有0、1、2三種，分別代表導(dǎo)出、導(dǎo)入、混合模式。當我們設(shè)置IMEX＝1時將強制混合數(shù)據(jù)轉(zhuǎn)換為文本，但僅僅這種設(shè)置并不可靠，IMEX＝1只確保在某列前8行數(shù)據(jù)至少有一個是文本項的時候才起作用，它只是把查找前8行數(shù)據(jù)中數(shù)據(jù)類型占優(yōu)選擇的行為作了略微的改變。例如某列前8行數(shù)據(jù)全為純數(shù)字，那么它仍然以數(shù)字類型作為該列的數(shù)據(jù)類型，隨后行里的含有文本的數(shù)據(jù)仍然變空。 另一個改進的措施是IMEX＝1與注冊表值TypeGuessRows配合使用，TypeGuessRows 值決定了ISAM 驅(qū)動程序從前幾條數(shù)據(jù)采樣確定數(shù)據(jù)類型，默認為“8”。可以通過修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的該注冊表值來更改采樣行數(shù)。但是這種改進還是沒有根本上解決問題，即使我們把IMEX設(shè)為“1”， TypeGuessRows設(shè)得再大，例如1000，假設(shè)數(shù)據(jù)表有1001行，某列前1000行全為純數(shù)字，該列的第1001行又是一個文本，ISAM驅(qū)動的這種機制還是讓這列的數(shù)據(jù)變成空。
4 解決方法
從以上的分析中可以得知，當某列數(shù)據(jù)中含有混合類型時，在.NET中使用Microsoft.Jet.OLEDB.4.0來讀取Excel文件造成數(shù)據(jù)丟失是不可避免的，要解決這個問題只能考慮采用其它數(shù)據(jù)讀取方法。
在.NET中讀取Excel文件的另外一種方法是回到使用傳統(tǒng)COM組件，這種方法在很多技術(shù)文章或論文中都有涉及，本文不作贅述。需要指出的是，使用COM組件來讀取Excel文件數(shù)據(jù)的效率較低，在作釋放的時候有可能碰到不可預(yù)知的錯誤，特別開發(fā)Web應(yīng)用的程序應(yīng)該慎重使用。
本文提出另外一種利用讀取CSV純文本格式解決此問題的方法。
（1）在讀取Excel的.xls類型的文本數(shù)據(jù)之前，先將其轉(zhuǎn)換為.csv格式，在Excel中直接另存為這種格式就可以達到轉(zhuǎn)換的目的。CSV文件又稱為逗號分隔的文件，是一種純文本文件，它以“,”分隔數(shù)據(jù)列，本文表1的數(shù)據(jù)表用CSV格式存儲后用純文本編輯器打開的表現(xiàn)形式如表3所示。 表3 采用CSV格式保存的表1數(shù)據(jù) 需要指出的是，CSV文件也可以用Ole DB或ODBC的方式讀取，但是如果采用這些方式讀取其數(shù)據(jù)又會回到丟失數(shù)據(jù)的老路上，ISAM機制同樣會發(fā)揮作用。
（2）采用普通的讀取文本文件的方法打開文件，讀取第一行，用“,”作為分隔符獲得各字段名，在DataTable中創(chuàng)建對應(yīng)的各字段，字段的類型可以統(tǒng)一創(chuàng)建成“String”。
（3）逐行讀取數(shù)據(jù)行， 用“,”作為分隔符獲得某行各列的數(shù)據(jù)并填入DataTable相應(yīng)的字段中。
實現(xiàn)的簡化代碼如下：
顯示代碼打印01 String line;
02
03 String [] split = null;
04
05 DataTable table=new DataTable("auto");
06
07 DataRow row=null;
08
09 StreamReader sr=new StreamReader("c:/auto.csv",System.Text.Encoding.Default);
10
11 //創(chuàng)建與數(shù)據(jù)源對應(yīng)的數(shù)據(jù)列
12
13 line = sr.ReadLine();
14
15 split=line.Split(',');
16
17 foreach(String colname in split){
18
19 table.Columns.Add(colname,System.Type.GetType("System.String")); }
20
21 //將數(shù)據(jù)填入數(shù)據(jù)表
22
23 int j=0;
24
25 while((line=sr.ReadLine())!=null){
26
27 j=0;
28
29 row = table.NewRow();
30
31 split=line.Split(',');
32
33 foreach(String colname in split){
34
35 row[j]=colname; j++;}
36
37 table.Rows.Add(row);}
38
39 sr.Close(); //顯示數(shù)據(jù)
40
41 dataGrid1.DataSource=table.DefaultView;
42
43 dataGrid1.DataBind();
5 結(jié)語
在應(yīng)用程序的設(shè)計中，需要訪問Excel數(shù)據(jù)的情況非常普遍，本文以在.NET中對訪問含有混合類型數(shù)據(jù)的Excel表格擬采取的方法進行探討。當然，如果不存在混合類型的數(shù)據(jù)使用Microsoft.Jet.OLEDB為較佳方案。對于不是使用.NET開發(fā)的情況，本論文的分析和所提供的方法亦可參考。
