NPOI解析Excel的例子很多,一般是针对单行表头,输出DataTable的,如果遇到多行表头,输出泛型对象的,要稍微复杂一些,参考了一篇博文,理解后进行了简化,支持了第五篇的合并单元格读取(参考地址:https://www.cnblogs.com/csqb-511612371/p/4891492.html 与 https://www.cnblogs.com/csqb-511612371/p/4895548.html)原文一共6篇,写得很详细。
定义:
public class ExcelHelper
{
public List<T> ExcelToCollection<T>(string excelPath, Dictionary<string, string> headerPropDict, string sheetName, int indexHeaderStart, int indexHeaderEnd)
{
var result = new List<T>();
if (string.IsNullOrEmpty(excelPath) || !File.Exists(excelPath))
{
throw new FileNotFoundException();
}
if (indexHeaderStart < 0 || indexHeaderEnd < 0 || indexHeaderStart > indexHeaderEnd)
{
throw new ArgumentException();
}
try
{
using (Stream fileStream = new FileStream(excelPath, FileMode.Open))
{
IWorkbook workbook;
if (excelPath.EndsWith(".xls"))
{
workbook = new HSSFWorkbook(fileStream);
}
else if (excelPath.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook(fileStream);
}
else
{
throw new Exception("格式不支持");
}
ISheet sheet;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new Exception("sheet name Error");
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
var headerDict = GetHeaderIndexDict(sheet, indexHeaderStart, indexHeaderEnd);
if (headerDict.Count == 0)
{
throw new Exception("未读取到表头,请检查Excel模板与调用入参");
}
for (int i = indexHeaderEnd; i <= sheet.LastRowNum; i++)
{
// 产生一个新的泛型对象
var model = Activator.CreateInstance<T>();
// 是否为空行
bool isEmptyRow = true;
IRow dataRow = sheet.GetRow(i);
int cellCount = headerDict.Count;
if (dataRow != null)
{
// 循环列数据
for (int j = dataRow.FirstCellNum; j < cellCount; j++)
{
var propertyName = headerPropDict[headerDict[j]];
PropertyInfo prop = model.GetType().GetProperty(propertyName);
ICell cell = dataRow.GetCell(j);
var value = GetCellValue<T>(sheet, cell, j, i, indexHeaderEnd, result, prop.Name);
if (!string.IsNullOrEmpty(value))
{
// 赋值
switch (prop.PropertyType.FullName)
{
case "System.Double":
if (double.TryParse(value, out double valueDecimal))
{
prop.SetValue(model, valueDecimal, null);
}
break;
case "System.Int16":
if (Int16.TryParse(value, out short valueInt16))
{
prop.SetValue(model, valueInt16, null);
}
break;
case "System.Int32":
if (Int32.TryParse(value, out int valueInt32))
{
prop.SetValue(model, valueInt32, null);
}
break;
case "System.Boolean":
if (Boolean.TryParse(value, out bool valueBoolean))
{
prop.SetValue(model, valueBoolean, null);
}
break;
case "System.DateTime":
if (DateTime.TryParse(value, out DateTime valueDateTime))
{
prop.SetValue(model, valueDateTime, null);
}
break;
default:
prop.SetValue(model, value, null);
break;
}
isEmptyRow = false;
}
}
// 添加非空行数据到DTO
if (!isEmptyRow)
{
result.Add(model);
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("解析失败", ex);
}
return result;
}
public DataTable ExcelToDataTable(string excelPath, string sheetName, int indexHeaderStart, int indexHeaderEnd)
{
DataTable dataTable = new DataTable();
if (string.IsNullOrEmpty(excelPath) || !File.Exists(excelPath))
{
throw new FileNotFoundException();
}
try
{
using (Stream fileStream = new FileStream(excelPath, FileMode.Open))
{
IWorkbook workbook;
if (excelPath.EndsWith(".xls"))
{
workbook = new HSSFWorkbook(fileStream);
}
else if (excelPath.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook(fileStream);
}
else
{
throw new Exception("格式不支持");
}
ISheet sheet;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new Exception("sheet name Error");
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
//表头
if (indexHeaderStart > 0)
{
GetDataTableColumns(sheet, indexHeaderStart, indexHeaderEnd, ref dataTable);
}
else
{
var firstRow = sheet.GetRow(sheet.FirstRowNum);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn();
dataTable.Columns.Add(column);
}
}
//取值
for (int i = indexHeaderEnd; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = dataTable.NewRow();
int cellCount = row.LastCellNum;
bool isEmptyRow = true;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
var value = GetCellValue(sheet, cell, j, i, indexHeaderEnd, dataTable);
if (!string.IsNullOrEmpty(value))
{
dataRow[j] = value;
isEmptyRow = false;
}
}
if (!isEmptyRow)
{
dataTable.Rows.Add(dataRow);
}
}
}
}
catch (Exception ex)
{
throw new Exception("解析失败", ex);
}
return dataTable;
}
/// <summary>
/// 获取表头与所在列索引的映射
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstHeaderRowIndex"></param>
/// <param name="lastHeaderRowIndex"></param>
/// <returns></returns>
private Dictionary<int, string> GetHeaderIndexDict(ISheet sheet, int firstHeaderRowIndex, int lastHeaderRowIndex)
{
var dict = new Dictionary<int, string>();
try
{
// 循环获得表头
for (int i = firstHeaderRowIndex - 1; i < lastHeaderRowIndex; i++)
{
IRow headerRow = sheet.GetRow(i);
int cellCount = headerRow.LastCellNum;
for (int j = headerRow.FirstCellNum; j < cellCount; j++)
{
var value = headerRow.GetCell(j).StringCellValue.Trim();
if (!string.IsNullOrEmpty(value))
{
if (dict.Keys.Contains(j))
{
dict[j] += value;
}
else
{
dict.Add(j, value);
}
}
}
}
// 遍历表头字典,消除空格
for (int i = 0; i < dict.Count; i++)
{
var value = dict[i];
ReplaceSpace(ref value);
dict[i] = value;
}
}
catch (Exception ex)
{
throw new Exception("获取表头映射异常", ex);
}
return dict;
}
/// <summary>
/// 去除空值与转义符号
/// </summary>
/// <param name="cellValue"></param>
private void ReplaceSpace(ref string cellValue)
{
cellValue = cellValue.Replace(" ", string.Empty);
cellValue = Regex.Replace(cellValue, @"\t|\n|\r", string.Empty);
}
/// <summary>
/// 判断单元格是否被合并
/// </summary>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="sheet"></param>
/// <param name="firstRegionRow"></param>
/// <returns></returns>
private static bool IsMergedRegionCell(int cellIndex, int rowIndex, ISheet sheet, ref int firstRegionRow)
{
bool isMerged = false;
var regionLists = GetMergedCellRegion(sheet);
foreach (var cellRangeAddress in regionLists)
{
for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
if (rowIndex == i)
{
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
if (cellIndex == j)
{
isMerged = true;
firstRegionRow = cellRangeAddress.FirstRow;
break;
}
else
{
continue;
}
}
}
else
{
continue;
}
}
}
return isMerged;
}
/// <summary>
/// 获取合并区域信息
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static List<CellRangeAddress> GetMergedCellRegion(ISheet sheet)
{
int mergedRegionCellCount = sheet.NumMergedRegions;
var returnList = new List<CellRangeAddress>();
for (int i = 0; i < mergedRegionCellCount; i++)
{
returnList.Add(sheet.GetMergedRegion(i));
}
return returnList;
}
/// <summary>
/// 读取DataTable的列名
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstHeaderRowIndex"></param>
/// <param name="lastHeaderRowIndex"></param>
/// <param name="dataTable"></param>
private void GetDataTableColumns(ISheet sheet, int firstHeaderRowIndex, int lastHeaderRowIndex, ref DataTable dataTable)
{
var headerDict = GetHeaderIndexDict(sheet, firstHeaderRowIndex, lastHeaderRowIndex);
foreach (var key in headerDict.Keys.OrderBy(o => o))
{
var head = headerDict[key];
DataColumn column = new DataColumn(head);
dataTable.Columns.Add(column);
}
}
/// <summary>
/// 读取单元格的内容
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="indexHeaderEnd"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GetCellValue(ISheet sheet, ICell cell, int cellIndex, int rowIndex, int indexHeaderEnd, DataTable dataTable)
{
if (sheet == null || cell == null)
{
return null;
}
string value;
switch (cell.CellType)
{
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
value = cell.NumericCellValue + string.Empty;
break;
case CellType.Boolean:
value = cell.BooleanCellValue + string.Empty;
break;
default:
value = cell.StringCellValue;
break;
}
break;
default:
value = cell.ToString();
break;
}
if (!string.IsNullOrEmpty(value))
{
return value;
}
int firstRegionRow = 0;
if (IsMergedRegionCell(cellIndex, rowIndex, sheet, ref firstRegionRow)) //2、单元格为合并单元格且不在合并区域左上角
{
if (firstRegionRow >= indexHeaderEnd && rowIndex != firstRegionRow)//合并单元格 第一行无值为cell合并
{
int resultIndex = firstRegionRow - indexHeaderEnd;
var regionValue = dataTable.Rows[resultIndex].ItemArray[cellIndex] + string.Empty; //获得合并单元格第一行数据
return regionValue;
}
}
return null;
}
/// <summary>
/// 读取单元格的内容
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="indexHeaderEnd"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GetCellValue<T>(ISheet sheet, ICell cell, int cellIndex, int rowIndex, int indexHeaderEnd, List<T> list, string propName)
{
if (sheet == null || cell == null)
{
return null;
}
string value;
switch (cell.CellType)
{
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
value = cell.NumericCellValue + string.Empty;
break;
case CellType.Boolean:
value = cell.BooleanCellValue + string.Empty;
break;
default:
value = cell.StringCellValue;
break;
}
break;
default:
value = cell.ToString();
break;
}
if (!string.IsNullOrEmpty(value))
{
return value;
}
int firstRegionRow = 0;
if (IsMergedRegionCell(cellIndex, rowIndex, sheet, ref firstRegionRow)) //2、单元格为合并单元格且不在合并区域左上角
{
if (firstRegionRow >= indexHeaderEnd && rowIndex != firstRegionRow)//合并单元格 第一行无值为cell合并
{
int resultIndex = firstRegionRow - indexHeaderEnd;
var oldModel = list.Select((p, d) => new { p, d })
.Where(p => p.d == resultIndex)
.Select(p => p.p).First();
var regionValue = oldModel.GetType().GetProperty(propName).GetValue(oldModel, null);//获得合并单元格第一行数据
return regionValue + string.Empty;
}
}
return null;
}
}
调用:
class Data
{
public string Region { get; set; }
public double TotalAmount { get; set; }
public double UrbanPermanentPopulation { get; set; }
public double UrbanPermanentPopulationShare { get; set; }
public double RuralPermanentPopulation { get; set; }
public double RuralPermanentPopulationShare { get; set; }
}
Dictionary<string, string> dict = new Dictionary<string, string>()
{
{ "地区", "Region" },
{ "总人口(年末)(万人)", "TotalAmount" },
{ "城镇人口人口数", "UrbanPermanentPopulation" },
{ "比重(%)", "UrbanPermanentPopulationShare" },
{ "乡村人口人口数", "RuralPermanentPopulation" },
{ "#比重(%)", "RuralPermanentPopulationShare" },
};
var dtos = ExcelHelper.ExcelToCollection<Data>("..\\..\\data.xlsx", dict, "", 2, 4);