1 引言 在應用程式的設計中，經常需要讀取Excel數據或將Excel數據導入轉換到其他數據載體中，例如將Excel數據通過應用程式導入SQL Sever等資料庫中以備使用。筆者在開發“汽車產業鏈ASP協同商務平臺”中遇到了類似需求。某汽車整車生產企業需要將其車輛發車信息發佈到汽車產業鏈平臺上去，其 ...
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三種，分別代表導出、導入、混合模式。當我們設置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數據的情況非常普遍，本文以在.NET中對訪問含有混合類型數據的Excel表格擬採取的方法進行探討。當然，如果不存在混合類型的數據使用Microsoft.Jet.OLEDB為較佳方案。對於不是使用.NET開發的情況，本論文的分析和所提供的方法亦可參考。
OLEDB 連接EXCEL的連接字元串 IMEX的問題
今天碰到一個問題需要想EXCEL表中寫數據，折騰了好久才發現是IMEX惹得禍，所以記錄下提醒自己，也希望大家不要出同樣的錯。碰到問題：使用語句 "insert into [Sheet1$] (大類) values ('test')" 無法插入 。
原因：Provider=Microsoft.Jet.OLEDB.4.0;Data Source='2008-08.xls'; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'
解決方法： 去掉IMEX=1
補充：
向EXCEL插入數據時 數據類型是由前8行數據中數據類型占優選擇 例如：分數一列前前8行為空值 插入5為字元串格式，如果前8行為數字格式 插入5為數字格式關於IMEX的資料：
IMEX是用來告訴驅動程式使用Excel文件的模式，其值有0、1、2三種，分別代表導出、導入、混合模式。當我們設置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驅動的這種機制還是讓這列的數據變成空。
