using System; using System.Collections.Generic; using System.Data; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; namespace Hh.Mes.Common.Exel { /// <summary> /// 通用NPOI Excel导入数据帮助类 /// https://www.cnblogs.com/Can-daydayup/tag/%E3%80%90Npoi%E3%80%91/ /// https://www.cnblogs.com/knowledgesea/archive/2012/11/16/2772547.html /// </summary> public class NpoiExcelImportHelper { private static NpoiExcelImportHelper _excelImportHelper; public static NpoiExcelImportHelper _ { get => _excelImportHelper ?? (_excelImportHelper = new NpoiExcelImportHelper()); set => _excelImportHelper = value; } /// <summary> /// 读取excel表格中的数据,将Excel文件流转化为dataTable数据源 /// 默认第一行为标题 /// </summary> /// <param name="stream">excel文档文件流</param> /// <param name="fileType">文档格式</param> /// <param name="isSuccess">是否转化成功</param> /// <param name="resultMsg">转换结果消息</param> /// <returns></returns> public Dictionary<string, SysDtAllCls> ExcelToDataTable(Stream stream, string fileType, out bool isSuccess, out string resultMsg) { isSuccess = false; resultMsg = "Excel文件流成功转化为 Dictionary<string, DataTable>数据源"; Dictionary<string, SysDtAllCls> dicDt = null; try { //Workbook对象代表一个工作簿,首先定义一个Excel工作薄 IWorkbook workbook; //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式 #region 判断Excel版本 switch (fileType) { //.XLSX是07版(或者07以上的)的Office Excel case ".xlsx": workbook = new XSSFWorkbook(stream); break; //.XLS是03版的Office Excel case ".xls": workbook = new HSSFWorkbook(stream); break; default: throw new Exception("Excel文档格式有误"); } #endregion int SheetCount = workbook.NumberOfSheets; dicDt = new Dictionary<string, SysDtAllCls>(SheetCount); for (int sheetSum = 0; sheetSum < SheetCount; sheetSum++) { var sheet = workbook.GetSheetAt(sheetSum); dicDt.Add(sheet.SheetName, new SysDtAllCls()); dicDt[sheet.SheetName].dtTitle = new DataTable(); dicDt[sheet.SheetName].dtData = new DataTable(); //var rows = sheet.GetRowEnumerator(); var headerRow = sheet.GetRow(0); if (headerRow == null) continue; int cellCount = headerRow.LastCellNum;//最后一行列数(即为总列数) //获取第一行标题列数据源,转换为dataTable数据源的表格标题名称 for (var j = 0; j < cellCount; j++) { var cell = headerRow.GetCell(j); dicDt[sheet.SheetName].dtTitle.Columns.Add(cell.ToString()); dicDt[sheet.SheetName].dtData.Columns.Add(cell.ToString()); } //获取Excel表格中除标题以为的所有数据源,转化为dataTable中的表格数据源 for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { var dataRowDtData = dicDt[sheet.SheetName].dtData.NewRow(); DataRow dataRowDtTitle = null; if (i == 1) dataRowDtTitle = dicDt[sheet.SheetName].dtTitle.NewRow(); var row = sheet.GetRow(i); if (row == null||row.Cells.Count==0) continue; //没有数据的行默认是null for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null)//单元格内容非空验证 { #region NPOI获取Excel单元格中不同类型的数据 //获取指定的单元格信息 var cell = row.GetCell(j); switch (cell.CellType) { //首先在NPOI中数字和日期都属于Numeric类型 //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型 case CellType.Numeric when DateUtil.IsCellDateFormatted(cell): if (i == 1) { dataRowDtTitle[j] = cell.DateCellValue; } else { dataRowDtData[j] = cell.DateCellValue; } break; //其他数字类型 case CellType.Numeric: if (i == 1) { dataRowDtTitle[j] = cell.DateCellValue; } else { dataRowDtData[j] = cell.NumericCellValue; } break; //空数据类型 case CellType.Blank: if (i == 1) { dataRowDtTitle[j] = ""; } else { dataRowDtData[j] = ""; } break; //公式类型 case CellType.Formula: { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook); if (i == 1) { dataRowDtTitle[j] = eva.Evaluate(cell).StringValue; } else { dataRowDtData[j] = eva.Evaluate(cell).StringValue; } break; } //布尔类型 case CellType.Boolean: if (i == 1) { dataRowDtTitle[j] = row.GetCell(j).BooleanCellValue; } else { dataRowDtData[j] = row.GetCell(j).BooleanCellValue; } break; //错误 case CellType.Error: if (i == 1) { dataRowDtTitle[j] = HSSFErrorConstants.GetText(row.GetCell(j).ErrorCellValue); } else { dataRowDtData[j] = HSSFErrorConstants.GetText(row.GetCell(j).ErrorCellValue); } break; //其他类型都按字符串类型来处理(未知类型CellType.Unknown,字符串类型CellType.String) default: if (i == 1) { dataRowDtTitle[j] = cell.StringCellValue; } else { dataRowDtData[j] = cell.StringCellValue; } break; } #endregion } } //除去DataTable中的空行 bool IsNull = true; for (int j = 0; j < dataRowDtData.ItemArray.Length; j++) { if (!string.IsNullOrEmpty(dataRowDtData.ItemArray[j].ToString().Trim())) { IsNull = false; } } if (i == 1) dicDt[sheet.SheetName].dtTitle.Rows.Add(dataRowDtTitle); if (!IsNull) dicDt[sheet.SheetName].dtData.Rows.Add(dataRowDtData); } isSuccess = true; } } catch (Exception e) { resultMsg = e.Message; } return dicDt; } public class SysDtAllCls { /// <summary> /// dt 表头 中文 用于校验验证 /// </summary> public DataTable dtTitle { get; set; } /// <summary> /// 数据源 /// </summary> public DataTable dtData { get; set; } } } }