.NET6导入和导出EXCEL .NET6导入和导出EXCEL

使用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{/// /// Excel操作相关/// public class ExcelHelper{#region 读取Excel到DataTable/// /// 读取Excel文件的内容/// /// /// 工作表名称/// 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); }}/// /// 从Excel文件流读取内容/// /// /// /// 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); //生成DataTableif (sheet != null)return GetDataTable(sheet); elsethrow new Exception(string.Format("工作表{0}不存在!", sheetName ?? "")); }/// /// 读取工作表数据/// /// /// 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; }/// /// 获取单元格值/// /// /// /// 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; }/// /// 检查是否空数据行/// /// /// 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; }/// /// 检查是否空的Excel行/// /// /// 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/// /// 生成Excel数据到路径/// /// /// public static void GenerateExcel(DataTable data, string path){var workBook = GenerateExcelData(data); //保存至路径using (FileStream fs = File.OpenWrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！{workBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。}}/// /// 生成Excel数据到字节流/// /// /// public static byte[] GenerateExcel(DataTable data){var workBook = GenerateExcelData(data); using (MemoryStream ms = new MemoryStream()){workBook.Write(ms); return ms.GetBuffer(); }}/// /// 生成DataTable到Excel/// /// /// 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 = https://www.it610.com/article/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; }/// /// 创建文档的基本信息(右击文件属性可看到的)/// /// 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{/// /// 使用EPPlus 第三方的组件读取Excel/// public class EPPlusHelper{private static string GetString(object obj){if (obj == null)return ""; return obj.ToString(); }/// ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）/// /// 文件的绝对路径/// 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 索引是1worksheet = package.Workbook.Worksheets[1]; }else{worksheet = package.Workbook.Worksheets[sheetName]; }if (worksheet == null)throw new Exception("指定的sheetName不存在"); return WorksheetToTable(worksheet); }/// /// 将worksheet转成datatable/// /// 待处理的worksheet/// 返回处理后的datatablepublic 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)); //剩下的写入datatableelsedr[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; //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();

文章图片

源码：http://xiazai.jb51.net/202112/yuanma/ConsoleOperExcel_jb51.rar，使用vs2022 。
【.NET6导入和导出EXCEL】到此这篇关于.NET6导入和导出EXCEL的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。