asp.net使用NPOI读取excel文件

asp.net 使用npoi读取excel文件内容

npoi下载地址：npoi

public class excelhelper
{
///

excel文件的物理路径
///
public static dataset getdatafromexcel(string strexcelphysicalpath, out string strerror)
{
try
{
dataset dsresult = new dataset();
strerror = "";
iworkbook wbook = null;
using (filestream fs = new filestream(strexcelphysicalpath, filemode.open, fileaccess.read))
{
if (strexcelphysicalpath.indexof(".xlsx") > 0)
{
wbook = new xssfworkbook(fs);
}
else
{
wbook = new hssfworkbook(fs);
}
}
for (int i = 0; i {
isheet wsheet = wbook.getsheetat(i);
if (wsheet == null) continue;
datatable dtsheet = getdatafromsheet(wsheet, out strerror);
if (dtsheet != null)
{
dtsheet.tablename = wsheet.sheetname.trim();
dsresult.tables.add(dtsheet);
}
else
{
dsresult = null;
break;
}
}
return dsresult;
}
catch (exception ex)
{
strerror = ex.message.tostring();
return null;
}
}
private static datatable getdatafromsheet(isheet wsheet, out string strerror)
{
try
{
datatable dtresult = new datatable();
strerror = "";
//取sheet最大列数
int max_column = 0;
for (int i = wsheet.firstrownum; i <= wsheet.lastrownum; i++)
{
irow rsheet = wsheet.getrow(i);
if (rsheet != null && rsheet.lastcellnum > max_column)
{
max_column = rsheet.lastcellnum;
}
}
//给datatable添加列
for (int i = 0; i {
dtresult.columns.add("a" + i.tostring());
}
for (int i = wsheet.firstrownum; i <= wsheet.lastrownum; i++)
{
datarow drow = dtresult.newrow();
irow rsheet = wsheet.getrow(i);
if (rsheet == null) continue;
for (int j = rsheet.firstcellnum; j {
icell csheet = rsheet.getcell(j);
if (csheet == null) continue;
switch (csheet.celltype)
{
case celltype.blank:
drow[j] = "";
break;
case celltype.boolean:
drow[j] = csheet.booleancellvalue;
break;
case celltype.error:
drow[j] = csheet.errorcellvalue;
break;
case celltype.formula:
try
{
drow[j] = csheet.numericcellvalue;
short format1 = csheet.cellstyle.dataformat;
if (format1 == 177 || format1 == 178 || format1 == 188)
{
drow[j] = csheet.numericcellvalue.tostring("#0.00");
}
}
catch
{
drow[j] = csheet.stringcellvalue.trim();
}
break;
case celltype.numeric:
try
{
short format2 = csheet.cellstyle.dataformat;
if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58)
{
drow[j] = csheet.datecellvalue;
}
else
{
drow[j] = csheet.numericcellvalue;
}
if (format2 == 177 || format2 == 178 || format2 == 188)
{
drow[j] = csheet.numericcellvalue.tostring("#0.00");
}
}
catch
{
drow[j] = csheet.stringcellvalue.trim();
}
break;
case celltype.string:
drow[j] = csheet.stringcellvalue.trim();
break;
default:
drow[j] = csheet.stringcellvalue.trim();
break;
}
}
dtresult.rows.add(drow);
}
return dtresult;
}
catch (exception ex)
{
strerror = ex.message.tostring();
return null;
}
}
}