项目需要引用npoi的nuget包:dotnetcore.npoi-v1.2.2

 

本篇文章是对webapi项目使用npoi操作excel时的帮助类:excelhelper的改进优化做下记录:

备注:下面的帮助类代码使用的文件格式为:xlsx文件,xlsx相对xls的优缺点代码里有注释,推荐使用xlsx文件保存数据!

 

using microsoft.aspnetcore.mvc;
using microsoft.extensions.logging;
using microsoft.net.http.headers;
using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using system;
using system.collections.generic;
using system.io;
using system.linq;
using system.reflection;
namespace paymentaccountapi.helper
{
/// <summary>
/// excel帮助类
/// </summary>
/// <typeparam name="t">泛型类</typeparam>
/// <typeparam name="tcollection">泛型类集合</typeparam>
public class excelhelp
{
private ilogger logger = null;
public excelhelp(ilogger<excelhelp> logger)
{
this.logger = logger;
}
/// <summary>
/// 将数据导出excel
/// </summary>
/// <param name="tlist">要导出的数据集</param>
/// <param name="fieldnameandshownamedic">键值对集合(键:字段名,值:显示名称)</param>
/// <param name="filedirectorypath">文件路径</param>
/// <param name="excelname">文件名(必须是英文或数字)</param>
/// <returns></returns>
public iworkbook createorupdateworkbook<t>(list<t> tlist, dictionary<string, string> fieldnameandshownamedic, iworkbook workbook = null, string sheetname = "sheet1") where t : new()
{
//xls文件格式属于老版本文件,一个sheet最多保存65536行;而xlsx属于新版文件类型;
//excel 07 - 2003一个工作表最多可有65536行,行用数字1—65536表示; 最多可有256列,列用英文字母a—z,aa—az,ba—bz,……,ia—iv表示;一个工作簿中最多含有255个工作表,默认情况下是三个工作表;
//excel 2007及以后版本,一个工作表最多可有1048576行,16384列;
if (workbook == null)
{
workbook = new xssfworkbook();
//workbook = new hssfworkbook();
}
isheet worksheet = workbook.createsheet(sheetname);
list<string> columnnamelist = fieldnameandshownamedic.values.tolist();
//设置首列显示
irow row1 = worksheet.createrow(0);
icell cell = null;
icellstyle cellheadstyle = workbook.createcellstyle();
//设置首行字体加粗
ifont font = workbook.createfont();
font.boldweight = short.maxvalue;
cellheadstyle.setfont(font);
int cloumncount = columnnamelist.count;
for (var i = 0; i < cloumncount; i++)
{
cell = row1.createcell(i);
cell.setcellvalue(columnnamelist[i]);
cell.cellstyle = cellheadstyle;
}
//根据反射创建其他行数据
var raws = tlist.count;
dictionary<string, propertyinfo> titlepropertydic = this.getindexpropertydic<t>(fieldnameandshownamedic);
propertyinfo propertyinfo = null;
t t = default(t);
for (int i = 0; i < raws; i++)
{
if (i % 10000 == 0)
{
this.logger.loginformation($"excel已创建{i + 1}条数据");
}
row1 = worksheet.createrow(i + 1);
t = tlist[i];
int cellindex = 0;
foreach (var titlepropertyitem in titlepropertydic)
{
propertyinfo = titlepropertyitem.value;
cell = row1.createcell(cellindex);
if (propertyinfo.propertytype == typeof(int)
|| propertyinfo.propertytype == typeof(decimal)
|| propertyinfo.propertytype == typeof(double))
{
cell.setcellvalue(convert.todouble(propertyinfo.getvalue(t) ?? 0));
}
else if (propertyinfo.propertytype == typeof(datetime))
{
cell.setcellvalue(convert.todatetime(propertyinfo.getvalue(t)?.tostring()).tostring("yyyy-mm-dd hh:mm:ss"));
}
else if (propertyinfo.propertytype == typeof(bool))
{
cell.setcellvalue(convert.toboolean(propertyinfo.getvalue(t).tostring()));
}
else
{
cell.setcellvalue(propertyinfo.getvalue(t)?.tostring() ?? "");
}
cellindex++;
}
//重要:设置行宽度自适应(大批量添加数据时,该行代码需要注释,否则会极大减缓excel添加行的速度!)
//worksheet.autosizecolumn(i, true);
}
return workbook;
}
/// <summary>
/// 保存workbook数据为文件
/// </summary>
/// <param name="workbook"></param>
/// <param name="filedirectorypath"></param>
/// <param name="filename"></param>
public void saveworkbooktofile(iworkbook workbook, string filedirectorypath, string filename)
{
//xls文件格式属于老版本文件,一个sheet最多保存65536行;而xlsx属于新版文件类型;
//excel 07 - 2003一个工作表最多可有65536行,行用数字1—65536表示; 最多可有256列,列用英文字母a—z,aa—az,ba—bz,……,ia—iv表示;一个工作簿中最多含有255个工作表,默认情况下是三个工作表;
//excel 2007及以后版本,一个工作表最多可有1048576行,16384列;
memorystream ms = new memorystream();
//这句代码非常重要,如果不加,会报:打开的excel格式与扩展名指定的格式不一致
ms.seek(0, seekorigin.begin);
workbook.write(ms);
byte[] mybytearray = ms.getbuffer();
filedirectorypath = filedirectorypath.trimend('\\') + "\\";
if (!directory.exists(filedirectorypath))
{
directory.createdirectory(filedirectorypath);
}
string filepath = filedirectorypath + filename;
if (file.exists(filepath))
{
file.delete(filepath);
}
file.writeallbytes(filepath, mybytearray);
}
/// <summary>
/// 保存workbook数据为下载文件
/// </summary>
public filecontentresult saveworkbooktodownloadfile(iworkbook workbook)
{
memorystream ms = new memorystream();
//这句代码非常重要,如果不加,会报:打开的excel格式与扩展名指定的格式不一致
ms.seek(0, seekorigin.begin);
workbook.write(ms);
byte[] mybytearray = ms.getbuffer();
//对于.xls文件
//application/vnd.ms-excel
//用于.xlsx文件。
//application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
mediatypeheadervalue mediatype = new mediatypeheadervalue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
mediatype.encoding = system.text.encoding.utf8;
return new filecontentresult(mybytearray, mediatype.tostring());
}
/// <summary>
/// 读取excel数据
/// </summary>
/// <param name="filepath"></param>
/// <param name="fieldnameandshownamedic"></param>
/// <returns></returns>
public list<t> readdatalist<t>(string filepath, dictionary<string, string> fieldnameandshownamedic) where t : new()
{
list<t> tlist = null;
t t = default(t);
//标题属性字典列表
dictionary<string, propertyinfo> titlepropertydic = this.getindexpropertydic<t>(fieldnameandshownamedic);
//标题下标列表
dictionary<string, int> titleindexdic = new dictionary<string, int>(0);
propertyinfo propertyinfo = null;
using (filestream filestream = new filestream(filepath, filemode.open, fileaccess.read))
{
iworkbook xssfworkbook = new xssfworkbook(filestream);
var sheet = xssfworkbook.getsheetat(0);
var rows = sheet.getrowenumerator();
tlist = new list<t>(sheet.lastrownum + 1);
//第一行数据为标题,
if (rows.movenext())
{
irow row = (xssfrow)rows.current;
icell cell = null;
string cellvalue = null;
for (int i = 0; i < row.cells.count; i++)
{
cell = row.cells[i];
cellvalue = cell.stringcellvalue;
if (titlepropertydic.containskey(cellvalue))
{
titleindexdic.add(cellvalue, i);
}
}
}
//从第2行数据开始获取
while (rows.movenext())
{
irow row = (xssfrow)rows.current;
t = new t();
foreach (var titleindexitem in titleindexdic)
{
var cell = row.getcell(titleindexitem.value);
if (cell != null)
{
propertyinfo = titlepropertydic[titleindexitem.key];
if (propertyinfo.propertytype == typeof(int))
{
propertyinfo.setvalue(t, convert.toint32(cell.numericcellvalue));
}
else if (propertyinfo.propertytype == typeof(decimal))
{
propertyinfo.setvalue(t, convert.todecimal(cell.numericcellvalue));
}
else if (propertyinfo.propertytype == typeof(double))
{
propertyinfo.setvalue(t, convert.todouble(cell.numericcellvalue));
}
else if (propertyinfo.propertytype == typeof(bool))
{
propertyinfo.setvalue(t, convert.toboolean(cell.stringcellvalue));
}
else if (propertyinfo.propertytype == typeof(datetime))
{
propertyinfo.setvalue(t, convert.todatetime(cell.stringcellvalue));
}
else
{
propertyinfo.setvalue(t, cell.stringcellvalue);
}
}
}
tlist.add(t);
}
}
return tlist ?? new list<t>(0);
}
/// <summary>
/// 根据属性名顺序获取对应的属性对象
/// </summary>
/// <param name="fieldnamelist"></param>
/// <returns></returns>
private dictionary<string, propertyinfo> getindexpropertydic<t>(dictionary<string, string> fieldnameandshownamedic)
{
dictionary<string, propertyinfo> titlepropertydic = new dictionary<string, propertyinfo>(fieldnameandshownamedic.count);
list<propertyinfo> tpropertyinfolist = typeof(t).getproperties().tolist();
propertyinfo propertyinfo = null;
foreach (var item in fieldnameandshownamedic)
{
propertyinfo = tpropertyinfolist.find(m => m.name.equals(item.key, stringcomparison.ordinalignorecase));
titlepropertydic.add(item.value, propertyinfo);
}
return titlepropertydic;
}
}
}