使用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.ToLowerstring GetString(object obj) { if (obj == null) return ""; ijfMgRa 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 informationusing 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的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。
