ASP.NET操作Excel

使用npoi操作excel，无需office com组件

部分代码来自于：https://docs.microsoft.com/zh-tw/previous-versions/ee818993(v=msdn.10)?redirectedfrom=msdn

using system.data;
using system.io;
using system.text;
using system.web;
using npoi.hssf.usermodel;
using npoi.ss.usermodel;
/// <summary>
/// 使用npoi操作excel，无需office com组件
/// 部分代码取自http://msdn.microsoft.com/zh-tw/ee818993.asp
/// </summary>
public class excelrender
{
/// <summary>
/// 根据excel列类型获取列的值
/// </summary>
/// <param name="cell">excel列</param>
/// <returns></returns>
private static string getcellvalue(icell cell)
{
if (cell == null)
return string.empty;
switch (cell.celltype)
{
case celltype.blank:
return string.empty;
case celltype.boolean:
return cell.booleancellvalue.tostring();
case celltype.error:
return cell.errorcellvalue.tostring();
case celltype.numeric:
case celltype.unknown:
default:
return cell.tostring();//this is a trick to get the correct value of the cell. numericcellvalue will return a numeric value no matter the cell value is a date or a number
case celltype.string:
return cell.stringcellvalue;
case celltype.formula:
try
{
hssfformulaevaluator e = new hssfformulaevaluator(cell.sheet.workbook);
e.evaluateincell(cell);
return cell.tostring();
}
catch
{
return cell.numericcellvalue.tostring();
} 
}
}
/// <summary>
/// 自动设置excel列宽
/// </summary>
/// <param name="sheet">excel表</param>
private static void autosizecolumns(isheet sheet)
{
if (sheet.physicalnumberofrows > 0)
{
irow headerrow = sheet.getrow(0);
for (int i = 0, l = headerrow.lastcellnum; i < l; i++)
{
sheet.autosizecolumn(i);
}
}
}
/// <summary>
/// 保存excel文档流到文件
/// </summary>
/// <param name="ms">excel文档流</param>
/// <param name="filename">文件名</param>
private static void savetofile(memorystream ms, string filename)
{
using (filestream fs = new filestream(filename, filemode.create, fileaccess.write))
{
byte[] data = ms.toarray();
fs.write(data, 0, data.length);
fs.flush();
data = null;
}
}
/// <summary>
/// 输出文件到浏览器
/// </summary>
/// <param name="ms">excel文档流</param>
/// <param name="context">http上下文</param>
/// <param name="filename">文件名</param>
private static void rendertobrowser(memorystream ms, httpcontext context, string filename)
{
if (context.request.browser.browser == "ie")
filename = httputility.urlencode(filename);
context.response.addheader("content-disposition", "attachment;filename=" + filename);
context.response.binarywrite(ms.toarray());
}
/// <summary>
/// datareader转换成excel文档流
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static memorystream rendertoexcel(idatareader reader)
{
memorystream ms = new memorystream();
using (reader)
{
using (iworkbook workbook = new hssfworkbook())
{
using (isheet sheet = workbook.createsheet())
{
irow headerrow = sheet.createrow(0);
int cellcount = reader.fieldcount;
// handling header.
for (int i = 0; i < cellcount; i++)
{
headerrow.createcell(i).setcellvalue(reader.getname(i));
}
// handling value.
int rowindex = 1;
while (reader.read())
{
irow datarow = sheet.createrow(rowindex);
for (int i = 0; i < cellcount; i++)
{
datarow.createcell(i).setcellvalue(reader[i].tostring());
}
rowindex++;
}
autosizecolumns(sheet);
workbook.write(ms);
ms.flush();
ms.position = 0;
}
}
}
return ms;
}
/// <summary>
/// datareader转换成excel文档流，并保存到文件
/// </summary>
/// <param name="reader"></param>
/// <param name="filename">保存的路径</param>
public static void rendertoexcel(idatareader reader, string filename)
{
using (memorystream ms = rendertoexcel(reader))
{
savetofile(ms, filename);
}
}
/// <summary>
/// datareader转换成excel文档流，并输出到客户端
/// </summary>
/// <param name="reader"></param>
/// <param name="context">http上下文</param>
/// <param name="filename">输出的文件名</param>
public static void rendertoexcel(idatareader reader, httpcontext context, string filename)
{
using (memorystream ms = rendertoexcel(reader))
{
rendertobrowser(ms, context, filename);
}
}
/// <summary>
/// datatable转换成excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static memorystream rendertoexcel(datatable table)
{
memorystream ms = new memorystream();
using (table)
{
using (iworkbook workbook = new hssfworkbook())
{
using (isheet sheet = workbook.createsheet())
{
irow headerrow = sheet.createrow(0);
// handling header.
foreach (datacolumn column in table.columns)
headerrow.createcell(column.ordinal).setcellvalue(column.caption);//if caption not set, returns the columnname value
// handling value.
int rowindex = 1;
foreach (datarow row in table.rows)
{
irow datarow = sheet.createrow(rowindex);
foreach (datacolumn column in table.columns)
{
datarow.createcell(column.ordinal).setcellvalue(row[column].tostring());
}
rowindex++;
}
autosizecolumns(sheet);
workbook.write(ms);
ms.flush();
ms.position = 0;
}
}
}
return ms;
}
/// <summary>
/// datatable转换成excel文档流，并保存到文件
/// </summary>
/// <param name="table"></param>
/// <param name="filename">保存的路径</param>
public static void rendertoexcel(datatable table, string filename)
{
using (memorystream ms = rendertoexcel(table))
{
savetofile(ms, filename);
}
}
/// <summary>
/// datatable转换成excel文档流，并输出到客户端
/// </summary>
/// <param name="table"></param>
/// <param name="response"></param>
/// <param name="filename">输出的文件名</param>
public static void rendertoexcel(datatable table, httpcontext context, string filename)
{
using (memorystream ms = rendertoexcel(table))
{
rendertobrowser(ms, context, filename);
}
}
/// <summary>
/// excel文档流是否有数据
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <returns></returns>
public static bool hasdata(stream excelfilestream)
{
return hasdata(excelfilestream, 0);
}
/// <summary>
/// excel文档流是否有数据
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="sheetindex">表索引号，如第一个表为0</param>
/// <returns></returns>
public static bool hasdata(stream excelfilestream, int sheetindex)
{
using (excelfilestream)
{
using (iworkbook workbook = new hssfworkbook(excelfilestream))
{
if (workbook.numberofsheets > 0)
{
if (sheetindex < workbook.numberofsheets)
{
using (isheet sheet = workbook.getsheetat(sheetindex))
{
return sheet.physicalnumberofrows > 0;
}
}
}
}
}
return false;
}
/// <summary>
/// excel文档流转换成datatable
/// 第一行必须为标题行
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="sheetname">表名称</param>
/// <returns></returns>
public static datatable renderfromexcel(stream excelfilestream, string sheetname)
{
return renderfromexcel(excelfilestream, sheetname, 0);
}
/// <summary>
/// excel文档流转换成datatable
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="sheetname">表名称</param>
/// <param name="headerrowindex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static datatable renderfromexcel(stream excelfilestream, string sheetname, int headerrowindex)
{
datatable table = null;
using (excelfilestream)
{
using (iworkbook workbook = new hssfworkbook(excelfilestream))
{
using (isheet sheet = workbook.getsheet(sheetname))
{
table = renderfromexcel(sheet, headerrowindex);
}
}
}
return table;
}
/// <summary>
/// excel文档流转换成datatable
/// 默认转换excel的第一个表
/// 第一行必须为标题行
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <returns></returns>
public static datatable renderfromexcel(stream excelfilestream)
{
return renderfromexcel(excelfilestream, 0, 0);
}
/// <summary>
/// excel文档流转换成datatable
/// 第一行必须为标题行
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="sheetindex">表索引号，如第一个表为0</param>
/// <returns></returns>
public static datatable renderfromexcel(stream excelfilestream, int sheetindex)
{
return renderfromexcel(excelfilestream, sheetindex, 0);
}
/// <summary>
/// excel文档流转换成datatable
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="sheetindex">表索引号，如第一个表为0</param>
/// <param name="headerrowindex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static datatable renderfromexcel(stream excelfilestream, int sheetindex, int headerrowindex)
{
datatable table = null;
using (excelfilestream)
{
using (iworkbook workbook = new hssfworkbook(excelfilestream))
{
using (isheet sheet = workbook.getsheetat(sheetindex))
{
table = renderfromexcel(sheet, headerrowindex);
}
}
}
return table;
}
/// <summary>
/// excel表格转换成datatable
/// </summary>
/// <param name="sheet">表格</param>
/// <param name="headerrowindex">标题行索引号，如第一行为0</param>
/// <returns></returns>
private static datatable renderfromexcel(isheet sheet, int headerrowindex)
{
datatable table = new datatable();
irow headerrow = sheet.getrow(headerrowindex);
int cellcount = headerrow.lastcellnum;//lastcellnum = physicalnumberofcells
int rowcount = sheet.lastrownum;//lastrownum = physicalnumberofrows - 1
//handling header.
for (int i = headerrow.firstcellnum; i < cellcount; i++)
{
datacolumn column = new datacolumn(headerrow.getcell(i).stringcellvalue);
table.columns.add(column);
}
for (int i = (sheet.firstrownum + 1); i <= rowcount; i++)
{
irow row = sheet.getrow(i);
datarow datarow = table.newrow();
if (row != null)
{
for (int j = row.firstcellnum; j < cellcount; j++)
{
if (row.getcell(j) != null)
datarow[j] = getcellvalue(row.getcell(j));
}
}
table.rows.add(datarow);
}
return table;
}
/// <summary>
/// excel文档导入到数据库
/// 默认取excel的第一个表
/// 第一行必须为标题行
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="insertsql">插入语句</param>
/// <param name="dbaction">更新到数据库的方法</param>
/// <returns></returns>
public static int rendertodb(stream excelfilestream, string insertsql, dbaction dbaction)
{
return rendertodb(excelfilestream, insertsql, dbaction, 0, 0);
}
public delegate int dbaction(string sql, params idataparameter[] parameters);
/// <summary>
/// excel文档导入到数据库
/// </summary>
/// <param name="excelfilestream">excel文档流</param>
/// <param name="insertsql">插入语句</param>
/// <param name="dbaction">更新到数据库的方法</param>
/// <param name="sheetindex">表索引号，如第一个表为0</param>
/// <param name="headerrowindex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static int rendertodb(stream excelfilestream, string insertsql, dbaction dbaction, int sheetindex, int headerrowindex)
{
int rowaffected = 0;
using (excelfilestream)
{
using (iworkbook workbook = new hssfworkbook(excelfilestream))
{
using (isheet sheet = workbook.getsheetat(sheetindex))
{
stringbuilder builder = new stringbuilder();
irow headerrow = sheet.getrow(headerrowindex);
int cellcount = headerrow.lastcellnum;//lastcellnum = physicalnumberofcells
int rowcount = sheet.lastrownum;//lastrownum = physicalnumberofrows - 1
for (int i = (sheet.firstrownum + 1); i <= rowcount; i++)
{
irow row = sheet.getrow(i);
if (row != null)
{
builder.append(insertsql);
builder.append(" values (");
for (int j = row.firstcellnum; j < cellcount; j++)
{
builder.appendformat("'{0}',", getcellvalue(row.getcell(j)).replace("'", "''"));
}
builder.length = builder.length - 1;
builder.append(");");
}
if ((i % 50 == 0 || i == rowcount) && builder.length > 0)
{
//每50条记录一次批量插入到数据库
rowaffected += dbaction(builder.tostring());
builder.length = 0;
}
}
}
}
}
return rowaffected;
}
}

弄一个dbheple 就可以完成该操作excel