基于.NET的程序读取Excel文件的解决方案
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
