shanzm-2020年12月8日 23:48:11
0. 前言
以前基於 .NET 開發的程式，我一般都是使用NPOI操作Excel檔案，
因為我的程式讀取的是另外一個成品WMS匯出的Excel檔案（至於為什麼不直接從資料庫獲取該WMS匯出到Excel的資料，此中原因，一言難盡！），在使用NPOI讀取Excel的時候，提示錯誤：
Initialisation of record 0x203(NumberRecord) left 4 bytes remaining still to be read.
StackOverFlow一個高贊答案就是升級NPOI版本，或使用Office Excel將Excel檔案另存為，儲存為新的Excel檔案再使用NPOI讀取該Excel檔案。
雖已使用最新的NPOI，但是依舊出現該錯誤，讓使用者另存為不合適，但是我又尚未找到一個簡單有效快速的處理方法。
考慮到伺服器上已經安裝的Office套件，所以使用Com元件的方法操作Excel檔案，但是讀取速度較慢。
最終嘗試使用ADO .NET 中的OleDbConnection類讀取Excel檔案，讀取速度，且相對方便，簡單記錄如下。
1. 使用NPOI庫讀取Excel檔案
在.NET程式中需要操作Excel檔案，不論xls格式還是xlsx格式都是可以使用NPOI
Nuget獲取該擴充套件：
PM> Install-Package NPOI -Version 2.5.2
簡單的一個示例，讀取使用者上傳的Excel檔案：
/// <summary>
/// 讀取Excel匯入DataTable
/// </summary>
/// <param name="filepath">匯入的檔案路徑（包括檔名）</ param>
/// <param name="sheetName">工作表名稱</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列 名</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string filePath,string sheetName,bool isFirstRowColumn)
{
DataTable data = new DataTable();
FileStream fs;
int startRow = 0;
using (fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
IWorkbook workbook = filePath.Contains(".xlsx") ? (IWorkbook)new XSSFWorkbook(fs) :newHSSFWorkbook(fs);//xlsx使用XSSFWorkbook， xls使用HSSFWorkbokk
ISheet sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);//如果沒有找到指sheetName 對應的sheet，則嘗試獲取第一個sheet
if (sheet != null)
{
IRow firstrow = sheet.GetRow(0);//第一行
int firstCellNum = firstrow.FirstCellNum;// 行第一個cell的編號,從0開始
int lastCellNum = firstrow.LastCellNum; // 行最後一個cell的編號 即總的列數,（不忽略中間某 列空格）
if (isFirstRowColumn)//如果第一行是表格列頭
{
for (int i = firstCellNum; i < lastCellNum; i++)
{
ICell cell = firstrow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//讀資料行
int rowCont = sheet.LastRowNum;
for (int i = startRow; i <=rowCont; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = data.NewRow();
//判斷需要讀取的最後一行
if (row != null && (row.GetCell(row.FirstCellNum) != null && row.GetCel (rowFirstCellNum).ToString() != "合計") )
{
for (int j = row.FirstCellNum; j < lastCellNum; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
else
{
break;
}
}
}
return data;
}
catch (Exception ex)
{
Debug.WriteLine("Exception: " + ex.Message);
return null;
}
finally
{
fs.Close();
fs.Dispose();
}
}
}
讀取到Excel表格中的資料到DataTable 中，可以使用SqlBulkCopy批量插入的方式將DataTable資料儲存到資料庫表中，關於ADO .NET 針對SQL Server的批量插入的各種方法以及比較，可以參考文末給出的參考連線
簡單示例如下：
/// <summary>
/// 使用SqlBulkCopy將DataTable中的資料批量插入資料庫中
/// 注意：DataTable中的列需要與資料庫表中的列完全一致。
/// </summary>
/// <param name="conStr">資料庫連線串</param>
/// <param name="dbTableName">資料庫中對應的表名</param>
/// <param name="dtData">資料集</param>
public static void SqlBulkCopyInsert(string dbTableName, DataTable dataTable)
{
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(connStr))//引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = dbTableName;//資料庫中對應的表名
sqlRevdBulkCopy.NotifyAfter = dataTable.Rows.Count;//有幾行資料
sqlRevdBulkCopy.WriteToServer(dataTable);//資料匯入資料庫
sqlRevdBulkCopy.Close();//關閉連線
}
}
2. 使用OleDbConnection
使用ADO .NET 中的OleDbConnection類連線查詢。簡單示例如下：
/// <summary>
/// 讀取Excel返回DataTable
/// </summary>
/// <param name="filePath">Excel檔案路徑</param>
/// <param name="tableName">Excel檔案中Sheet名稱</param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, string tableName)
{
//獲取副檔名
string strExtension = System.IO.Path.GetExtension(filePath);
OleDbConnection myConn = null;
switch (strExtension)
{
case ".xls":
myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
break;
case ".xlsx":
myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");
//此連線可以操作.xls與.xlsx檔案 (支援Excel2003 和 Excel2007 的連線字串)
//"HDR=yes;"是說Excel檔案的第一行是列名而不是數，"HDR=No;"正好與前面的相反。"IMEX=1 "如果列中的資料型別不一致，使用"IMEX=1"可必免資料型別衝突。
break;
default:
myConn = null;
break;
}
if (myConn == null)
{
return null;
}
string strCom = " SELECT * FROM [" + tableName + "$]";
myConn.Open();
//獲取Excel指定Sheet表中的資訊
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet ds;
ds = new DataSet();
myCommand.Fill(ds, tableName);
myConn.Close();
return ds.Tables[0];
}
使用OleDbConnection在本地測試沒有問題，在釋出部署在伺服器後出現了一個異常：
未在本地計算機上註冊“Microsoft.Jet.OLEDB.4.0”提供程式
解決方法：IIS-->應用程式池-->選中部署的專案右鍵-->高階設定-->啟用32位應用程式-->True