nuget添加npoi
???/// <summary> ???/// npoi帮助类 ???/// </summary> ???public static class NpoiHelper ???{ ???????/// <summary> ???????/// 根据文件路径，获取表格集合 ???????/// </summary> ???????/// <param name="filePath"></param> ???????/// <returns></returns> ???????public static List<DataTable> GetDataTableList(string filePath) ???????{ ???????????var list = new ConcurrentBag<DataTable>(); ???????????using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) ???????????{ ???????????????var isExcel2007 = filePath.IsExcel2007(); ???????????????var workBook = stream.GetWorkbook(isExcel2007); ???????????????var sheetIndexList = new List<int>(); ???????????????for (int i = 0; i < workBook.NumberOfSheets; i++) sheetIndexList.Add(i); ???????????????Parallel.ForEach(sheetIndexList, new ParallelOptions ???????????????{ ???????????????????MaxDegreeOfParallelism = 3 ???????????????}, (source, state, index) => ???????????????{ ???????????????????try ???????????????????{ ???????????????????????if (!workBook.IsSheetHidden(source)) ???????????????????????????list.Add(GetDataTableToY(workBook, source)); ???????????????????} ???????????????????catch (NPOI.POIFS.FileSystem.OfficeXmlFileException nopiEx) ???????????????????{ ???????????????????????Console.WriteLine($"SheetIndex:{index}\t\tException:{nopiEx.Message}"); ???????????????????} ???????????????????catch (Exception e) ???????????????????{ ???????????????????????Console.WriteLine(e); ???????????????????} ???????????????}); ???????????} ???????????return list.ToList(); ???????} ???????/// <summary> ???????/// 根据sheet索引，把数据转换为datatable,以Y轴为准 ???????/// </summary> ???????/// <param name="workBook"></param> ???????/// <param name="sheetIndex">sheet索引</param> ???????/// <param name="validRowIndex"></param> ???????/// <returns></returns> ???????public static DataTable GetDataTableToY(IWorkbook workBook, int sheetIndex, int validRowIndex = 0) ???????{ ???????????var sheet = workBook.GetSheetAt(sheetIndex); ???????????var table = new DataTable(sheet.SheetName); ???????????// ?设置最大列，默认为1 ???????????var maxColumnNum = 1; ???????????// ?不是有效列集合，连续超过三行不读取后续所有列 ???????????var noValidColumnList = new List<int>(); ???????????// ?列：按照列把数据填充到datatable中，防止无限列出现 ???????????for (var columnIndex = 0; columnIndex < maxColumnNum; columnIndex++) ???????????{ ???????????????var column = new DataColumn(); ???????????????table.Columns.Add(column); ???????????????noValidColumnList.Add(columnIndex); ???????????????// ?列中所有数据都是null为true ???????????????var isAllEmpty = true; ???????????????// ?行 ???????????????for (var rowIndex = 0; rowIndex < sheet.LastRowNum; rowIndex++) ???????????????{ ???????????????????if (columnIndex == 0) table.Rows.Add(table.NewRow()); ???????????????????var itemRow = sheet.GetRow(rowIndex); ???????????????????if (itemRow == null) continue; ???????????????????maxColumnNum = maxColumnNum < itemRow.LastCellNum ? itemRow.LastCellNum : maxColumnNum; ???????????????????// ?把格式转换为utf-8 ???????????????????var itemCellValue = itemRow.GetValue(columnIndex).FormatUtf8String(); ???????????????????if (!itemCellValue.IsNullOrWhiteSpace()) isAllEmpty = false; ???????????????????table.Rows[rowIndex][columnIndex] = itemCellValue; ???????????????} ???????????????// ?当前列有值 ???????????????if (!isAllEmpty) ???????????????????noValidColumnList.Clear(); ???????????????// ?连续空白列超过三行 或 有空白行且当前行为最后一行 ???????????????else if (noValidColumnList.Count > 3 || (noValidColumnList.Count > 0 && columnIndex == maxColumnNum - 1)) ???????????????{ ???????????????????for (var i = noValidColumnList.Count - 1; i >= 0; i--) ???????????????????????table.Columns.RemoveAt(noValidColumnList[i]); ???????????????????break; ???????????????} ???????????} ???????????// 得到一个sheet中有多少个合并单元格 ???????????int sheetMergeCount = sheet.NumMergedRegions; ???????????for (var i = 0; i < sheetMergeCount; i++) ???????????{ ???????????????// 获取合并后的单元格 ???????????????var range = sheet.GetMergedRegion(i); ???????????????sheet.IsMergedRegion(range); ???????????????var cellValue = string.Empty; ???????????????for (var mRowIndex = range.FirstRow; mRowIndex <= range.LastRow; mRowIndex++) ???????????????{ ???????????????????for (var mColumnIndex = range.FirstColumn; mColumnIndex <= range.LastColumn; mColumnIndex++) ???????????????????{ ???????????????????????var itemCellValue = table.Rows[range.FirstRow][range.FirstColumn].FormatUtf8String(); ???????????????????????if (!itemCellValue.IsNullOrWhiteSpace()) ???????????????????????????cellValue = itemCellValue; ???????????????????????table.Rows[mRowIndex][mColumnIndex] = cellValue; ???????????????????} ???????????????} ???????????} ???????????return table; ???????} ???????#region 公共方法 ???????/// <summary> ???????/// 判断excel是否是2007版本：.xls ???????/// </summary> ???????/// <param name="filePath"></param> ???????/// <returns></returns> ???????public static bool IsExcel2007(this string filePath) ???????{ ???????????return Path.GetExtension(filePath)?.ToLower() == ".xls"; ???????} ???????/// <summary> ???????/// 根据版本创建IWorkbook对象 ???????/// </summary> ???????/// <param name="stream"></param> ???????/// <param name="isExcel2007"></param> ???????/// <returns></returns> ???????public static IWorkbook GetWorkbook(this Stream stream, bool isExcel2007) ???????{ ???????????return isExcel2007 ? (IWorkbook)new HSSFWorkbook(stream) : new XSSFWorkbook(stream); ???????} ???????/// <summary> ???????/// 获取XSSFRow的值（全部统一转成字符串） ???????/// </summary> ???????/// <param name="row"></param> ???????/// <param name="index"></param> ???????/// <returns></returns> ???????public static string GetValue(this IRow row, int index) ???????{ ???????????var rowCell = row.GetCell(index); ???????????return GetValueByCellStyle(rowCell, rowCell?.CellType); ???????} ???????/// <summary> ???????/// 根据单元格的类型获取单元格的值 ???????/// </summary> ???????/// <param name="rowCell"></param> ???????/// <param name="type"></param> ???????/// <returns></returns> ???????public static string GetValueByCellStyle(ICell rowCell, CellType? type) ???????{ ???????????string value = string.Empty; ???????????switch (type) ???????????{ ???????????????case CellType.String: ???????????????????value = rowCell.StringCellValue; ???????????????????break; ???????????????case CellType.Numeric: ???????????????????if (DateUtil.IsCellInternalDateFormatted(rowCell)) ???????????????????{ ???????????????????????value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); ???????????????????} ???????????????????else if (DateUtil.IsCellDateFormatted(rowCell)) ???????????????????{ ???????????????????????value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); ???????????????????} ???????????????????//有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式 ???????????????????else if (rowCell.CellStyle.GetDataFormatString() == null) ???????????????????{ ???????????????????????value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); ???????????????????} ???????????????????else if (rowCell.CellStyle.GetDataFormatString().Contains("$")) ???????????????????{ ???????????????????????value = "$" + rowCell.NumericCellValue.ToString(); ???????????????????} ???????????????????else if (rowCell.CellStyle.GetDataFormatString().Contains("￥")) ???????????????????{ ???????????????????????value = "￥" + rowCell.NumericCellValue.ToString(); ???????????????????} ???????????????????else if (rowCell.CellStyle.GetDataFormatString().Contains("￥")) ???????????????????{ ???????????????????????value = "￥" + rowCell.NumericCellValue.ToString(); ???????????????????} ???????????????????else if (rowCell.CellStyle.GetDataFormatString().Contains("�")) ???????????????????{ ???????????????????????value = "�" + rowCell.NumericCellValue.ToString(); ???????????????????} ???????????????????else ???????????????????{ ???????????????????????value = rowCell.NumericCellValue.ToString(); ???????????????????} ???????????????????break; ???????????????case CellType.Boolean: ???????????????????value = rowCell.BooleanCellValue.ToString(); ???????????????????break; ???????????????case CellType.Error: ???????????????????value = ErrorEval.GetText(rowCell.ErrorCellValue); ???????????????????break; ???????????????case CellType.Formula: ???????????????????// ?TODO: 是否存在 嵌套 公式类型 ???????????????????value = GetValueByCellStyle(rowCell, rowCell?.CachedFormulaResultType); ???????????????????break; ???????????} ???????????return value; ???????} ???????#endregion ???}ASPNET npoi帮助类
原文地址：https://www.cnblogs.com/Cailf/p/9988470.html