NPOI解析多行表头Excel

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);