在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
使用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(); 源码:https://gitee.com/runliuv/mypub/tree/master/NetCoreProj,使用vs2022 。 |
请发表评论