用OLEDB方式讀取EXCEL的速度是非常快的。但是當(dāng)Excel數(shù)據(jù)量很大時。會非常占用內(nèi)存，當(dāng)內(nèi)存不夠時會拋出內(nèi)存溢出的異常。
OLEDB方式將Excel作為一個數(shù)據(jù)源，直接用Sql語句操作數(shù)據(jù)，并且不需要安裝Office Excel就可以使用。但缺點(diǎn)是不能靈活操作Excel，例如設(shè)置字體，單元格格式等。
一、讀取Excel
連接字符串的設(shè)置：讀取“.xls”時使用"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'" 讀取“.xlsx”時使用 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"
其中HDR和IMEX的設(shè)置：
HDR=Yes，這代表第一行是標(biāo)題，不做為數(shù)據(jù)使用,系統(tǒng)默認(rèn)的是YES
當(dāng) IMEX=0 時為“匯出模式”，這個模式開啟的 Excel 檔案只能用來做“寫入”用途。 0 ---輸出模式;
當(dāng) IMEX=1 時為“匯入模式”，這個模式開啟的 Excel 檔案只能用來做“讀取”用途。 1---輸入模式;
當(dāng) IMEX=2 時為“鏈接模式”，這個模式開啟的 Excel 檔案可同時支持“讀取”與“寫入”用途。2----鏈接模式(完全更新能力)
GetOleDbSchemaTable：GetOleDbSchemaTable 的第一個參數(shù)是架構(gòu)參數(shù)，它是一個 OleDbSchemaGuid 類型的標(biāo)識，指定了要返回的架構(gòu)信息的類型（如表、列和主鍵）。例如：System.Data.OleDb.OleDbSchemaGuid.Tables
第二個參數(shù)是一個限制對象數(shù)組，對 DataTable 架構(gòu)中返回的行進(jìn)行過濾（例如，您可以指定對表的名稱、類型、所有者和/或架構(gòu)的限制）。制數(shù)組如下所示：{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}。 例如:new object[] { null, null, "Sheet1$", null }
下面貼上代碼
1、獲取Excel的工作表名集合
/// <summary> /// C#中獲取Excel文件的表名 /// </summary> /// <param name="excelFileName">路徑名</param> /// <returns></returns> public static List<string> GetExcelTableName(string pathName) { List<string> tableName = new List<string>(); if (File.Exists(pathName)) { string strConn = string.Empty; FileInfo file = new FileInfo(pathName); string extension = file.Extension; switch (extension) { case ".xls": strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; case ".xlsx": strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; default: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; } using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn)) { conn.Open(); System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); foreach (System.Data.DataRow row in dt.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); //過濾無效SheetName if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$")) { strSheetTableName = strSheetTableName.Replace("'", ""); //可能會有 '1X$' 出現(xiàn) strSheetTableName = strSheetTableName.Substring(0, strSheetTableName.Length - 1); tableName.Add(strSheetTableName); } } } } return tableName; }2、 獲取EXCEL工作表的列名 返回list集合
/// <summary> /// 獲取EXCEL工作表的列名 返回list集合 /// </summary> /// <param name="Path">Excel路徑名</param> /// <returns></returns> public static List<string> getExcelFileInfo(string pathName) { string strConn; List<string> lstColumnName = new List<string>(); FileInfo file = new FileInfo(pathName); if (!file.Exists) { throw new Exception("文件不存在"); } string extension = file.Extension; switch (extension) { case ".xls": strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'"; break; case ".xlsx": strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'"; break; default: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; } System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, null }); foreach (System.Data.DataRow drow in table.Rows) { string TableName = drow["Table_Name"].ToString(); if (TableName.Contains("$") && TableName.Replace("'", "").EndsWith("$")) { System.Data.DataTable tableColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null }); foreach (System.Data.DataRow drowColumns in tableColumns.Rows) { string ColumnName = drowColumns["Column_Name"].ToString(); lstColumnName.Add(ColumnName); } } } return lstColumnName; }3、OLEDB方式讀取Excel，返回DataTable
在實(shí)際應(yīng)用中碰到了問題。某一列的前幾十行全是數(shù)字，而在中間混雜了文字，結(jié)果讀取的時候沒有讀取到，為空白。后來查找資料發(fā)現(xiàn)原來OLEDB會根據(jù)表中數(shù)據(jù)的前8行來決定該列的類型。問題解決：我把HDR設(shè)置為no，把原來的列名認(rèn)為是普通數(shù)據(jù)，這樣只要保證列名不為純數(shù)字就行了，最后再重新組織以下列名就好了。或者直接在Excel中把單元格設(shè)置為 文本。
/// <summary> /// OLEDB方式讀取Excel /// </summary> /// <param name="pathName">Excel路徑</param> /// <param name="sheetName">工作表名，默認(rèn)讀取第一個有數(shù)據(jù)的工作表（至少有2列數(shù)據(jù)）</param> /// <returns></returns> public static System.Data.DataTable DBExcelToDataTable(string pathName,string sheetName="") { System.Data.DataTable dt = new System.Data.DataTable(); string ConnectionString = string.Empty; FileInfo file = new FileInfo(pathName); if (!file.Exists) { throw new Exception("文件不存在"); } string extension = file.Extension; switch (extension) // 連接字符串 { case ".xls": ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'"; break; case ".xlsx": ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'"; break; default: ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'"; break; } System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString); try { con.Open(); if (sheetName != "") //若指定了工作表名 { //讀Excel的過程中，發(fā)現(xiàn)dt末尾有些行是空的，所以在sql語句中加了Where 條件篩選符合要求的數(shù)據(jù)。OLEDB會自動生成列名F1,F2……Fn System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con); System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd); try { apt.Fill(dt); } catch(Exception ex) { throw new Exception("該Excel文件中未找到指定工作表名," + ex.Message); } dt.TableName = sheetName; } else { //默認(rèn)讀取第一個有數(shù)據(jù)的工作表 var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { }); if (tables.Rows.Count == 0) { throw new Exception("Excel必須包含一個表"); } foreach (System.Data.DataRow row in tables.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); //過濾無效SheetName if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$")) { System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strSheetTableName, null }); if (tableColumns.Rows.Count < 2) //工作表列數(shù) continue; System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con); System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd); apt.Fill(dt); dt.TableName = strSheetTableName.Replace("$", "").Replace("'", ""); break; } } } if (dt.Rows.Count < 2) throw new Exception("表必須包含數(shù)據(jù)"); //重構(gòu)字段名 System.Data.DataRow headRow = dt.Rows[0]; foreach (System.Data.DataColumn c in dt.Columns) { string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim(); if (headValue.Length == 0) { throw new Exception("必須輸入列標(biāo)題"); } if (dt.Columns.Contains(headValue)) { throw new Exception("不能用重復(fù)的列標(biāo)題：" + headValue); } c.ColumnName = headValue; } dt.Rows.RemoveAt(0); return dt; } catch (Exception ee) { throw ee; } finally { con.Close(); } }測試讀了24000行23列的Excel，耗費(fèi)第一次3501毫秒，第二次2721，第三次2774……
一、寫入EXCEL
向Excel中寫入數(shù)據(jù)的情況下，就要使用insert語句直接向Excel中輸入了，當(dāng)然是用update更新，delete刪除也行。
起先在OLEDB連接Excel的擴(kuò)展屬性中我都用的HDR=Yes;IMEX=2。實(shí)際運(yùn)行起來時發(fā)現(xiàn)在寫入Office 97-2003時沒問題，但是在操作Office 2007，卻出現(xiàn)了"不能修改表“Sheet1”的設(shè)計(jì)。它在只讀數(shù)據(jù)庫中。"之后把IMEX設(shè)置為0，或者直接不加HDR和IMEX后居然都可以運(yùn)行了。
/// <summary> /// OLEDB方式導(dǎo)出DataTable /// </summary> /// <param name="Path">路徑</param> /// <param name="dt">DataTable</param> public static void DTToExcel(string Path, System.Data.DataTable dt) { string strCon = string.Empty; FileInfo file = new FileInfo(Path); string extension = file.Extension; switch (extension) { case ".xls": strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;"; //strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'"; //strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'"; break; case ".xlsx": //strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=Excel 12.0;"; //strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'"; //出現(xiàn)錯誤了 strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'"; break; default: strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'"; break; } try { using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon)) { con.Open(); StringBuilder strSQL = new StringBuilder(); System.Data.OleDb.OleDbCommand cmd; try { cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", dt.TableName), con); //覆蓋文件時可能會出現(xiàn)Table 'Sheet1' already exists.所以這里先刪除了一下 cmd.ExecuteNonQuery(); } catch { } //創(chuàng)建表格字段 strSQL.Append("CREATE TABLE ").Append("[" + dt.TableName + "]"); strSQL.Append("("); for (int i = 0; i < dt.Columns.Count; i++) { strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,"); } strSQL = strSQL.Remove(strSQL.Length - 1, 1); strSQL.Append(")"); cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con); cmd.ExecuteNonQuery(); //添加數(shù)據(jù) for (int i = 0; i < dt.Rows.Count; i++) { strSQL.Clear(); StringBuilder strvalue = new StringBuilder(); for (int j = 0; j < dt.Columns.Count; j++) { strvalue.Append("'" + dt.Rows[i[j].ToString().Replace("'", "''") + "'"); if (j != dt.Columns.Count - 1) { strvalue.Append(","); } else { } } cmd.CommandText = strSQL.Append(" insert into [" + dt.TableName + "] values (").Append(strvalue).Append(")").ToString(); cmd.ExecuteNonQuery(); } con.Close(); } } catch { } }測試將24000行23列的DataTable寫入Excel平均要00:02:40.1184609,2分40秒。還有就是不能在Excel里設(shè)置樣式。
由于上段時間要解析數(shù)據(jù)，就從網(wǎng)上收集整理了這些方法，寫在這里。方便以后還能用到
