C#_.NetCore_WebAPI项目_EXCEL数据导出(ExcelHelper_第二版_优化逻辑)
程序员文章站
2022-06-10 16:05:58
项目需要引用NPOI的Nuget包:DotNetCore.NPOI-v1.2.2 本篇文章是对WebAPI项目使用NPOI操作Excel时的帮助类:ExcelHelper的改进优化做下记录: 备注:下面的帮助类代码使用的文件格式为:xlsx文件,xlsx相对xls的优缺点代码里有注释,推荐使用xls ......
项目需要引用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; } } }
上一篇: 广东鲤鱼价格是多少,鲤鱼应该怎样挑