.NET6导入和导出EXCEL

使用npoi导入.xlsx遇到“eof in header”报错，网上找好很多方法，没解决，最后换成epplus.core导入。

导出默认是.xls。

npoi操作类：

using npoi.hpsf;using npoi.hssf.urmodel;using npoi.ss.urmodel;using npoi.xssf.urmodel;using system.collections;using system.data;namespace commonutils{/// <summary>/// excel操作相关/// </summary>public class excelhelper{#region 读取excel到datatable/// <summary>/// 读取excel文件的内容/// </summary>/// <param name="path"></param>/// <param name="sheetname">工作表名称</param>/// <returns></returns>public static datatable getdatatable(string path, string sheetname = null){if (path.tolower().endswith(".xlsx"))return epplushelper.worksheettotable(path, sheetname);using (filestream file = new filestream(path, filemode.open, fileaccess.read)){return getdatatable(file, sheetname);}}/// <summary>/// 从excel文件流读取内容/// </summary>/// <param name="file"></param>/// <param name="sheetname"></param>/// <returns></returns>public static datatable getdatatable(stream file, string contenttype, string sheetname = null){//载入工作簿iworkbook workbook = null;if (contenttype == "application/vnd.ms-excel"){workbook = new hssfworkbook(file);}el if (contenttype == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){workbook = new xssfworkbook(file);}el{try{workbook = new hssfworkbook(file);}catch{try{workbook = new xssfworkbook(file);}catch{throw new exception("文件格式不被支持!");}}}//获取工作表(sheetname为空则默认获取第一个工作表)var sheet = string.isnullorempty(sheetname) ? workbook.getsheetat(0) : workbook.getsheet(sheetname);//生成datatableif (sheet != null)return getdatatable(sheet);elthrow new e节约用水的作文xception(string.format("工作表{0}不存在!", sheetname ?? ""));}/// <summary>/// 读取工作表数据/// </summary>/// <param name="sheet"></param>/// <returns></returns>private static datatable getdatatable(isheet sheet){ienumerator rows = sheet.getrowenumerator();datatable dt = new datatable(sheet.sheetname);//默认第一个非空行为列头bool istitle = true;//标题行索引int titlerowindex = 0;//默认列头后的第一个数据行，作为datatable列类型的依据irow firstdatarow = null;while (rows.movenext()){irow row = null;if (rows.current is xssfrow)//*.xlsx{row = (xssfrow)rows.current;}el//*.xls{row = (hssfrow)rows.current;}//是否空行if (imptyrow(row)){if (istitle){titlerowindex++;}continue;}el{if (istitle){firstdatarow = sheet.getrow(titlerowindex + 1);//默认列头后的第一个数据行，作为datatable列类型的依据}}datarow dr = dt.newrow();for (int i = 0; i < row.lastcellnum; i++){var cell = row.getcell(i);if (istitle){var firstdatarowcell = firstdatarow.getcell(i);if (firstdatarowcell != null || cell != null){dt.columns.add(cell.stringcellvalue.trim());}el{dt.columns.add(string.format("未知列{0}", i + 1));}}el{if (i > dt.columns.count - 1) break;dr[i] = getcellvalue(cell, dt.columns[i].datatype);}}if (!istitle && !imptyrow(dr, dt.columns.count)){dt.rows.add(dr);}istitle = fal;}return dt;}/// <summary>/// 获取单元格值/// </summary>/// <param name="cell"></param>/// <param name="coltype"></param>/// <returns></returns>private static object getcellvalue(icell cell, type coltype){if (cell == null || cell.tostring().toupper().equals("null") || cell.celltype == npoi.ss.urmodel.celltype.blank)return dbnull.value;object val = null;switch (cell.celltype){ca npoi.ss.urmodel.celltype.boolean:val = cell.booleancellvalue;break;ca npoi.ss.urmodel.celltype.numeric:var cellvaluestr = cell.tostring().trim();if (cellvaluestr.indexof('-') >= 0 || cellvaluestr.indexof('/') >= 0){datetime d = datetime.minvalue;datetime.trypar(cellvaluestr, out d);if (!d.equals(datetime.minvalue)) val = cellvaluestr;}if (val == null){decimal vnum = 0;decimal.trypar(cellvaluestr, out vnum);val = vnum;}break;ca npoi.ss.urmodel.celltype.string:val = cell.stringcellvalue;break;ca npoi.ss.urmodel.celltype.error:val = cell.errorcellvalue;break;ca npoi.ss.urmodel.celltype.formula:default:val = "=" + cell.cellformula;break;}return val;}/// <summary>/// 检查是否空数据行/// </summary>/// <param name="dr"></param>/// <returns></returns>private static bool imptyrow(datarow dr, int colcount){bool imptyrow = true;for (int i = 0; i < colcount; i++){if (dr[i] != null && !dr[i].equals(dbnull.value)){imptyrow = fal;break;}}return imptyrow;}/// <summary>/// 检查是否空的excel行/// </summary>/// <param name="row"></param>/// <returns></returns>private static bool imptyrow(irow row){bool imptyrow = true;for (int i = 0; i < row.lastcellnum; i++){if (row.getcell(i) != null){imptyrow = fal;break;}}return imptyrow;}#endregion#region 生成datatable到excel/// <summary>/// 生成excel数据到路径/// </summary>/// <param name="data"></param>/// <param name="path"></param>public static void generateexcel(datatable data, string path){var workbook = generateexceldata(data);//保存至路径using (filestream fs = file.openwrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！{workbook.write(fs);   //向打开的这个xls文件中写入mysheet表并保存。}}/// <summary>/// 生成excel数据到字节流/// </summary>/// <param name="data"></param>/// <param name="path"></param>public static byte[] generateexcel(datatable data){var workbook = generateexceldata(data);using (memorystream ms = new memorystream()){workbook.write(ms);return ms.getbuffer();}}/// <summary>/// 生成datatable到excel/// </summary>/// <param name="data"></param>/// <param name="path"></param>private static iworkbook generateexceldata(datatable data){//创建工作簿var workbook = new hssfworkbook();//生成文件基本信息generatesummaryinformation(workbook);//创建工作表var sheet = workbook.createsheet("sheet1");//创建标题行if (da新民主主义革命的性质ta != null && data.columns.count > 0){irow row = sheet.createrow(0);for (int i = 0; i < data.columns.count; i++){var cell = row.createcell(i);cell.tcellvalue(data.columns[i].columnname);}}//创建数据行if (data != null && data.rows.count > 0){for (int rowindex = 1; rowindex <= data.rows.count; rowindex++){irow row = sheet.createrow(rowindex);for (int colindex = 0; colindex < data.columns.count; colindex++){var cell = row.createcell(colindex);var cellvalue = data.rows[rowindex - 1][colindex];switch (data.columns[colindex].datatype.name){ca "byte":ca "int16":ca "int32":ca "int64":ca "decimal":ca "single":ca "double":double doubleval = 0;if (cellvalue != null && !cellvalue.equals(system.dbnull.value)){double.trypar(cellvalue.tostring(), out doubleval);cell.tcellvalue(doubleval);}break;ca "datetime":datetime dtval = datetime.minvalue;if (cellvalue != null && !cellvalue.equals(system.dbnull.value)){datetime.trypar(cellvalue.tostring(), out dtval);if (dtval != datetime.minvalue){cell.tcellvalue(dtval);}}break;default:if (cellvalue != null && !cellvalue.equals(system.dbnull.value)){cell.t一开始就错cellvalue(cellvalue.tostring());}break;}}}}return workbook;}/// <summary>/// 创建文档的作文的英语基本信息(右击文件属性可看到的)/// </summary>/// <param 生物技术有哪些name="workbook"></param>private static void generatesummaryinformation(hssfworkbook workbook){documentsummaryinformation dsi = propertytfactory.createdocumentsummaryinformation();dsi.company = "company";summaryinformation si = propertytfactory.createsummaryinformation();si.subject = "subject";//主题si.author = "author";//作者workbook.documentsummaryinformation = dsi;workbook.summaryinformation = si;}#endregion}}

epplus.core工具类：

//using epplus.extensions;using officeopenxml;using system.data;namespace commonutils{/// <summary>/// 使用  epplus 第三方的组件读取excel/// </summary>public class epplushelper{private static string getstring(object obj){if (obj == null)return "";return obj.tostring();}/// <summary>///将指定的excel的文件转换成datatable （excel的第一个sheet）/// </summary>/// <param name="fullfielpath">文件的绝对路径</param>/// <returns></returns>public static datatable worksheettotable(string fullfielpath, string sheetname = null){//如果是“epplus”，需要指定licencontext。//epplus.core 不需要指定。//excelpackage.licencontext = licencontext.noncommercial;fileinfo existingfile = new fileinfo(fullfielpath);excelpackage package = new excelpackage(existingfile);excelworksheet worksheet = null;if (string.isnullorempty(sheetname)){//不传入 sheetname 默认取第1个sheet。//epplus 索引是0//epplus.core 索引是1worksheet = package.workbook.worksheets[1];}el{                worksheet = package.workbook.worksheets[sheetname];}if (worksheet == null)throw new exception("指定的sheetname不存在");return worksheettotable(worksheet);}/// <summary>/// 将worksheet转成datatable/// </summary>/// <param name="worksheet">待处理的worksheet</param>/// <returns>返回处理后的datatable</returns>public static datatable worksheettotable(excelworksheet worksheet){//获取worksheet的行数int rows = worksheet.dimension.end.row;//获取worksheet的列数int cols = worksheet.dimension.end.column;datatable dt = new datatable(worksheet.name);datarow dr = null;for (int i = 1; i <= rows; i++){if (i > 1)dr = dt.rows.add();for (int j = 1; j <= cols; j++){//默认将第一行设置为datatable的标题if (i == 1)dt.columns.add(getstring(worksheet.cells[i, j].value));//剩下的写入datatableeldr[j - 1] = getstring(worksheet.cells[i, j].value);}}return dt;}}}

使用：

// e https://aka.ms/new-console-template for more informationusing commonutils;using system.data;console.writeline("hello, world!");try{string dir = appcontext.badirectory;//2003string fullname = path.combine(dir, "测试excel.xls");datatable dt = excelhelper.getdatatable(fullname);console.writeline("hello, world!" + dir);//2007string fullname2 = path.combine(dir, "测试excel.xlsx");//dt = excelhelper.getdatatable(fullname);//datatable dt2 = excelhelper.getdatatable(fullname2, "sheetf");datatable dt2 = excelhelper.getdatatable(fullname2);string savefullname = path.combine(dir, "save_excel.xls");//excelhelper2.exportexcelbymemorystream(savefullname, dt2);string savefullname2 = path.combine(dir, "save_excel2.xls");excelhelper.generateexcel(dt2, savefullname2);console.writeline("hello, world!" + dir);}catch (exception ex){console.writeline("ex:" + ex.message);}console.readkey();

源码：/d/file/titlepic/consoleoperexcel_jb51.rar，使用vs2022 。

到此这篇关于.net6导入和导出excel的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持www.887551.com。