（1）oledb方式
优点：将excel直接当做数据源处理，通过sql直接读取内容，读取速度较快。
缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个sheet页读取出来后（结果为datatable）再在datatable中根据行列数来获取指定的值。
当excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。
读取代码如下：
我要评论
public datatable getexceltablebyoledb(string strexcelpath, string tablename)
{
try
{
datatable dtexcel = new datatable();
//数据表
dataset ds = new dataset();
//获取文件扩展名
string strextension = system.io.path.getextension(strexcelpath);
string strfilename = system.io.path.getfilename(strexcelpath);
//excel的连接
oledbconnection objconn = null;
switch (strextension)
{
case ".xls":
objconn = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source=" + strexcelpath + ";" + "extended properties=\"excel 8.0;hdr=no;imex=1;\"");
break;
case ".xlsx":
objconn = new oledbconnection("provider=microsoft.ace.oledb.12.0;data source=" + strexcelpath + ";" + "extended properties=\"excel 12.0;hdr=no;imex=1;\"");
break;
default:
objconn = null;
break;
}
if (objconn == null)
{
return null;
}
objconn.open();
//获取excel中所有sheet表的信息
//system.data.datatable schematable = objconn.getoledbschematable(system.data.oledb.oledbschemaguid.tables, null);
//获取excel的第一个sheet表名
//string tablename = schematable.rows[0][2].tostring().trim();
string strsql = "select * from [" + tablename + "]";
//获取excel指定sheet表中的信息
oledbcommand objcmd = new oledbcommand(strsql, objconn);
oledbdataadapter mydata = new oledbdataadapter(strsql, objconn);
mydata.fill(ds, tablename);//填充数据
objconn.close();
//dtexcel即为excel文件中指定表中存储的信息
dtexcel = ds.tables[tablename];
return dtexcel;
}
catch
{
return null;
}
}
下面说明一下连接字符串
hdr=yes，这代表第一行是标题，不做为数据使用（但是我在实际使用中，如果第一行存在复杂数值，那么读取得到的datatable列标题会自动设置为f1、f2等方式命名，与实际应用不符，所以当时是通过hdr=no方式将所有内容读取到datatable中，然后手动将第一行设置成标题的）；imex ( import export mode )设置
imex 有三种模式：
0 is export mode
1 is import mode
2 is linked mode (full update capabilities)
我这里特别要说明的就是 imex 参数了，因为不同的模式代表著不同的读写行为：
当 imex=0 时为“汇出模式”，这个模式开启的 excel 档案只能用来做“写入”用途。
当 imex=1 时为“汇入模式”，这个模式开启的 excel 档案只能用来做“读取”用途。
当 imex=2 时为“链接模式”，这个模式开启的 excel 档案可同时支援“读取”与“写入”用途。
---------------------------------
另外，读取excel2007版本的文件时，版本应该从8.0改为12.0，同时驱动不能再用jet，而应该用ace。负责会造成“找不到可安装的 isam”的错误。
---------------------------------
在网上还发现采用这种方式存在取出的sheet表的个数多于实际excel表中的sheet表个数的情况，其原因有二：
1. 取出的名称中，包括了xl命名管理器中的名称（参见xl2007的公式--命名管理器， 快捷键crtl+f3）；
2. 取出的名称中，包括了filterdatabase后缀的， 这是xl用来记录filter范围的。
对于第一点比较简单， 删除已有命名管理器中的内容即可；第二点处理起来比较麻烦， filter删除后这些名称依然保留着，简单的做法是新增sheet然后将原sheet copy进去。但实际情况并不能为每个excel做以上检查。下面给出了过滤的方案。（此问题我们有验证过，大家自己验证一下吧）
//objconn为读取excel的链接，下面通过过滤来获取有效的sheet页名称集合
system.data.datatable schematable = objconn.getoledbschematable(system.data.oledb.oledbschemaguid.tables, null);
list<string> lstsheetnames = new list<string>();
for (int i = 0; i < schematable.rows.count; i++)
{
string strsheetname = (string)dtsheetname.rows[i]["table_name"];
if (strsheetname.contains("$") && !strsheetname.replace("'", "").endswith("$"))
{
//过滤无效sheetname完毕....
continue;
}
if (lstsheetnames != null && !lstsheetnames.contains(strsheetname))
lstsheetnames.add(strsheetname);
}
因为读取出来无效sheetname一般情况最后一个字符都不会是$。如果sheetname有一些特殊符号，读取出来的sheetname会自动加上单引号。比如在excel中将sheetname编辑成mysheet(1)，此时读取出来的sheetname就为：'mysheet(1)$'，所以判断最后一个字符是不是$之前最好过滤一下单引号。
---------------------------------
（2）com组件的方式（通过添加 microsoft.office.interop.excel引用实现）
优点：能够非常灵活的读取excel中的数据，用户可以灵活的调用各种函数进行处理。
缺点：基于单元格的处理，读取速度较慢，对于数据量较大的文件最好不要使用此种方式读取。
需要添加相应的dll引用，必须存在此引用才可使用，如果是web站点部署在iis上时，还需要服务器机子已安装了excel，有时候还需要为配置iis权限。
读取代码如下：
private stopwatch wath = new stopwatch();
/// <summary>
/// 使用com读取excel
/// </summary>
/// <param name="excelfilepath">路径</param>
/// <returns>datatabel</returns>
public system.data.datatable getexceldata(string excelfilepath)
{
excel.application app = new excel.application();
excel.sheets sheets;
excel.workbook workbook = null;
object omissiong = system.reflection.missing.value;
system.data.datatable dt = new system.data.datatable();
wath.start();
try
{
if (app == null)
{
return null;
}
workbook = app.workbooks.open(excelfilepath, omissiong, omissiong, omissiong, omissiong, omissiong,
omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong);
//将数据读入到datatable中——start
sheets = workbook.worksheets;
excel.worksheet worksheet = (excel.worksheet)sheets.get_item(1);//读取第一张表
if (worksheet == null)
return null;
string cellcontent;
int irowcount = worksheet.usedrange.rows.count;
int icolcount = worksheet.usedrange.columns.count;
excel.range range;
//负责列头start
datacolumn dc;
int columnid = 1;
range = (excel.range)worksheet.cells[1, 1];
while (range.text.tostring().trim() != "")
{
dc = new datacolumn();
dc.datatype = system.type.gettype("system.string");
dc.columnname = range.text.tostring().trim();
dt.columns.add(dc);
range = (excel.range)worksheet.cells[1, ++columnid];
}
//end
for (int irow = 2; irow <= irowcount; irow++)
{
datarow dr = dt.newrow();
for (int icol = 1; icol <= icolcount; icol++)
{
range = (excel.range)worksheet.cells[irow, icol];
cellcontent = (range.value2 == null) ? "" : range.text.tostring();
dr[icol - 1] = cellcontent;
}
dt.rows.add(dr);
}
wath.stop();
timespan ts = wath.elapsed;
//将数据读入到datatable中——end
return dt;
}
catch
{
return null;
}
finally
{
workbook.close(false, omissiong, omissiong);
system.runtime.interopservices.marshal.releasecomobject(workbook);
workbook = null;
app.workbooks.close();
app.quit();
system.runtime.interopservices.marshal.releasecomobject(app);
app = null;
gc.collect();
gc.waitforpendingfinalizers();
}
}
/// <summary>
/// 使用com，多线程读取excel（1 主线程、4 副线程）
/// </summary>
/// <param name="excelfilepath">路径</param>
/// <returns>datatabel</returns>
public system.data.datatable threadreadexcel(string excelfilepath)
{
excel.application app = new excel.application();
excel.sheets sheets = null;
excel.workbook workbook = null;
object omissiong = system.reflection.missing.value;
system.data.datatable dt = new system.data.datatable();
wath.start();
try
{
if (app == null)
{
return null;
}
workbook = app.workbooks.open(excelfilepath, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong,
omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong);
//将数据读入到datatable中——start
sheets = workbook.worksheets;
excel.worksheet worksheet = (excel.worksheet)sheets.get_item(1);//读取第一张表
if (worksheet == null)
return null;
string cellcontent;
int irowcount = worksheet.usedrange.rows.count;
int icolcount = worksheet.usedrange.columns.count;
excel.range range;
//负责列头start
datacolumn dc;
int columnid = 1;
range = (excel.range)worksheet.cells[1, 1];
while (icolcount >= columnid)
{
dc = new datacolumn();
dc.datatype = system.type.gettype("system.string");
string strnewcolumnname = range.text.tostring().trim();
if (strnewcolumnname.length == 0) strnewcolumnname = "_1";
//判断列名是否重复
for (int i = 1; i < columnid; i++)
{
if (dt.columns[i - 1].columnname == strnewcolumnname)
strnewcolumnname = strnewcolumnname + "_1";
}
dc.columnname = strnewcolumnname;
dt.columns.add(dc);
range = (excel.range)worksheet.cells[1, ++columnid];
}
//end
//数据大于500条，使用多进程进行读取数据
if (irowcount - 1 > 500)
{
//开始多线程读取数据
//新建线程
int b2 = (irowcount - 1) / 10;
datatable dt1 = new datatable("dt1");
dt1 = dt.clone();
sheetoptions sheet1thread = new sheetoptions(worksheet, icolcount, 2, b2 + 1, dt1);
thread othread1 = new thread(new threadstart(sheet1thread.sheettodatatable));
othread1.start();
//阻塞 1 毫秒，保证第一个读取 dt1
thread.sleep(1);
datatable dt2 = new datatable("dt2");
dt2 = dt.clone();
sheetoptions sheet2thread = new sheetoptions(worksheet, icolcount, b2 + 2, b2 * 2 + 1, dt2);
thread othread2 = new thread(new threadstart(sheet2thread.sheettodatatable));
othread2.start();
datatable dt3 = new datatable("dt3");
dt3 = dt.clone();
sheetoptions sheet3thread = new sheetoptions(worksheet, icolcount, b2 * 2 + 2, b2 * 3 + 1, dt3);
thread othread3 = new thread(new threadstart(sheet3thread.sheettodatatable));
othread3.start();
datatable dt4 = new datatable("dt4");
dt4 = dt.clone();
sheetoptions sheet4thread = new sheetoptions(worksheet, icolcount, b2 * 3 + 2, b2 * 4 + 1, dt4);
thread othread4 = new thread(new threadstart(sheet4thread.sheettodatatable));
othread4.start();
//主线程读取剩余数据
for (int irow = b2 * 4 + 2; irow <= irowcount; irow++)
{
datarow dr = dt.newrow();
for (int icol = 1; icol <= icolcount; icol++)
{
range = (excel.range)worksheet.cells[irow, icol];
cellcontent = (range.value2 == null) ? "" : range.text.tostring();
dr[icol - 1] = cellcontent;
}
dt.rows.add(dr);
}
othread1.join();
othread2.join();
othread3.join();
othread4.join();
//将多个线程读取出来的数据追加至 dt1 后面
foreach (datarow dr in dt.rows)
dt1.rows.add(dr.itemarray);
dt.clear();
dt.dispose();
foreach (datarow dr in dt2.rows)
dt1.rows.add(dr.itemarray);
dt2.clear();
dt2.dispose();
foreach (datarow dr in dt3.rows)
dt1.rows.add(dr.itemarray);
dt3.clear();
dt3.dispose();
foreach (datarow dr in dt4.rows)
dt1.rows.add(dr.itemarray);
dt4.clear();
dt4.dispose();
return dt1;
}
else
{
for (int irow = 2; irow <= irowcount; irow++)
{
datarow dr = dt.newrow();
for (int icol = 1; icol <= icolcount; icol++)
{
range = (excel.range)worksheet.cells[irow, icol];
cellcontent = (range.value2 == null) ? "" : range.text.tostring();
dr[icol - 1] = cellcontent;
}
dt.rows.add(dr);
}
}
wath.stop();
timespan ts = wath.elapsed;
//将数据读入到datatable中——end
return dt;
}
catch
{
return null;
}
finally
{
workbook.close(false, omissiong, omissiong);
system.runtime.interopservices.marshal.releasecomobject(workbook);
system.runtime.interopservices.marshal.releasecomobject(sheets);
workbook = null;
app.workbooks.close();
app.quit();
system.runtime.interopservices.marshal.releasecomobject(app);
app = null;
gc.collect();
gc.waitforpendingfinalizers();
}
}
（3）npoi方式读取excel（此方法未经过测试）
npoi 是 poi 项目的 .net 版本。poi是一个开源的java读写excel、word等微软ole2组件文档的项目。使用 npoi 你就可以在没有安装 office 或者相应环境的机器上对 word/excel 文档进行读写。
优点：读取excel速度较快，读取方式操作灵活性
缺点：需要下载相应的插件并添加到系统引用当中。
/// <summary>
/// 将excel中的数据导入到datatable中
/// </summary>
/// <param name="sheetname">excel工作薄sheet的名称</param>
/// <param name="isfirstrowcolumn">第一行是否是datatable的列名</param>
/// <returns>返回的datatable</returns>
public datatable exceltodatatable(string sheetname, bool isfirstrowcolumn)
{
isheet sheet = null;
datatable data = new datatable();
int startrow = 0;
try
{
fs = new filestream(filename, filemode.open, fileaccess.read);
if (filename.indexof(".xlsx") > 0) // 2007版本
workbook = new xssfworkbook(fs);
else if (filename.indexof(".xls") > 0) // 2003版本
workbook = new hssfworkbook(fs);
if (sheetname != null)
{
sheet = workbook.getsheet(sheetname);
}
else
{
sheet = workbook.getsheetat(0);
}
if (sheet != null)
{
irow firstrow = sheet.getrow(0);
int cellcount = firstrow.lastcellnum; //一行最后一个cell的编号 即总的列数
if (isfirstrowcolumn)
{
for (int i = firstrow.firstcellnum; i < cellcount; ++i)
{
datacolumn column = new datacolumn(firstrow.getcell(i).stringcellvalue);
data.columns.add(column);
}
startrow = sheet.firstrownum + 1;
}
else
{
startrow = sheet.firstrownum;
}
//最后一列的标号
int rowcount = sheet.lastrownum;
for (int i = startrow; i <= rowcount; ++i)
{
irow row = sheet.getrow(i);
if (row == null) continue; //没有数据的行默认是null
datarow datarow = data.newrow();
for (int j = row.firstcellnum; j < cellcount; ++j)
{
if (row.getcell(j) != null) //同理，没有数据的单元格都默认是null
datarow[j] = row.getcell(j).tostring();
}
data.rows.add(datarow);
}
}
return data;
}
catch (exception ex)
{
console.writeline("exception: " + ex.message);
return null;
}
}
下面是一些相关的文章，大家可以参考下
如对本文有疑问， 点击进行留言回复！！
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
C#实现获取本地内网(局域网)和外网(公网)IP地址的方法分析
网友评论