.NET6导入和导出EXCEL
程序员文章站
2022-03-06 13:57:54
使用npoi导入.xlsx遇到“eof in header”报错,网上找好很多方法,没解决,最后换成epplus.core导入。导出默认是.xls。npoi操作类:using npoi.hpsf;us...
使用npoi导入.xlsx遇到“eof in header”报错,网上找好很多方法,没解决,最后换成epplus.core导入。
导出默认是.xls。
npoi操作类:
using npoi.hpsf; using npoi.hssf.usermodel; using npoi.ss.usermodel; using npoi.xssf.usermodel; 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); } else if (contenttype == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { workbook = new xssfworkbook(file); } else { 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); //生成datatable if (sheet != null) return getdatatable(sheet); else throw new exception(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; } else//*.xls { row = (hssfrow)rows.current; } //是否空行 if (isemptyrow(row)) { if (istitle) { titlerowindex++; } continue; } else { 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()); } else { dt.columns.add(string.format("未知列{0}", i + 1)); } } else { if (i > dt.columns.count - 1) break; dr[i] = getcellvalue(cell, dt.columns[i].datatype); } } if (!istitle && !isemptyrow(dr, dt.columns.count)) { dt.rows.add(dr); } istitle = false; } 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.usermodel.celltype.blank) return dbnull.value; object val = null; switch (cell.celltype) { case npoi.ss.usermodel.celltype.boolean: val = cell.booleancellvalue; break; case npoi.ss.usermodel.celltype.numeric: var cellvaluestr = cell.tostring().trim(); if (cellvaluestr.indexof('-') >= 0 || cellvaluestr.indexof('/') >= 0) { datetime d = datetime.minvalue; datetime.tryparse(cellvaluestr, out d); if (!d.equals(datetime.minvalue)) val = cellvaluestr; } if (val == null) { decimal vnum = 0; decimal.tryparse(cellvaluestr, out vnum); val = vnum; } break; case npoi.ss.usermodel.celltype.string: val = cell.stringcellvalue; break; case npoi.ss.usermodel.celltype.error: val = cell.errorcellvalue; break; case npoi.ss.usermodel.celltype.formula: default: val = "=" + cell.cellformula; break; } return val; } /// <summary> /// 检查是否空数据行 /// </summary> /// <param name="dr"></param> /// <returns></returns> private static bool isemptyrow(datarow dr, int colcount) { bool isemptyrow = true; for (int i = 0; i < colcount; i++) { if (dr[i] != null && !dr[i].equals(dbnull.value)) { isemptyrow = false; break; } } return isemptyrow; } /// <summary> /// 检查是否空的excel行 /// </summary> /// <param name="row"></param> /// <returns></returns> private static bool isemptyrow(irow row) { bool isemptyrow = true; for (int i = 0; i < row.lastcellnum; i++) { if (row.getcell(i) != null) { isemptyrow = false; break; } } return isemptyrow; } #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 (data != 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.setcellvalue(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) { case "byte": case "int16": case "int32": case "int64": case "decimal": case "single": case "double": double doubleval = 0; if (cellvalue != null && !cellvalue.equals(system.dbnull.value)) { double.tryparse(cellvalue.tostring(), out doubleval); cell.setcellvalue(doubleval); } break; case "datetime": datetime dtval = datetime.minvalue; if (cellvalue != null && !cellvalue.equals(system.dbnull.value)) { datetime.tryparse(cellvalue.tostring(), out dtval); if (dtval != datetime.minvalue) { cell.setcellvalue(dtval); } } break; default: if (cellvalue != null && !cellvalue.equals(system.dbnull.value)) { cell.setcellvalue(cellvalue.tostring()); } break; } } } } return workbook; } /// <summary> /// 创建文档的基本信息(右击文件属性可看到的) /// </summary> /// <param name="workbook"></param> private static void generatesummaryinformation(hssfworkbook workbook) { documentsummaryinformation dsi = propertysetfactory.createdocumentsummaryinformation(); dsi.company = "company"; summaryinformation si = propertysetfactory.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”,需要指定licensecontext。 //epplus.core 不需要指定。 //excelpackage.licensecontext = licensecontext.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 索引是1 worksheet = package.workbook.worksheets[1]; } else { 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)); //剩下的写入datatable else dr[j - 1] = getstring(worksheet.cells[i, j].value); } } return dt; } } }
使用:
// see https://aka.ms/new-console-template for more information using commonutils; using system.data; console.writeline("hello, world!"); try { string dir = appcontext.basedirectory; //2003 string fullname = path.combine(dir, "测试excel.xls"); datatable dt = excelhelper.getdatatable(fullname); console.writeline("hello, world!" + dir); //2007 string 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();
源码:http://xiazai.jb51.net/202112/yuanma/consoleoperexcel_jb51.rar,使用vs2022 。
到此这篇关于.net6导入和导出excel的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: 某商城列表和详情页
下一篇: 打糍粑要打到什么状态
推荐阅读
-
精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换
-
精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换
-
php导入导出excel实例
-
data loader怎么用 如何使用data loader对SalesForce数据进行导入、导出和删除
-
Excel-Boot(一款Excel导入导出解决方案组成的轻量级开源组件)
-
【C#常用方法】2.DataTable(或DataSet)与Excel文件之间的导出与导入(使用NPOI)
-
Coreldraw(CDR)中导入Excel表格的方法和处理技巧实例教程
-
利用phpexcel把excel导入数据库和数据库导出excel实现
-
建议收藏:.net core 使用EPPlus导入导出Excel详细案例,精心整理源码已更新至开源模板
-
MariaDB/MySQL备份和恢复(二):数据导入、导出