使用epplus库读取excel表头并建立列名映射,核心做法是加载工作簿后获取指定sheet的dimension属性确定范围,遍历首行单元格提取文本作为键,结合业务逻辑生成映射字典。该方式能自动适配不同列顺序,确保后续数据读取时通过列名准确定位值。

如何获取Excel文件中的首行列名?
直接调用worksheet.Dimension.Start.Row定位第一行,循环读取该行所有单元格的Text或Value属性即可获取原始表头字符串。需注意处理空单元格和合并单元格的情况,通常以非空文本为准。

using OfficeOpenXml;
using System;
using System.IO;
using System.Collections.Generic;
class Program
{
static void Main()
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
string filePath = "data.xlsx";
using var package = new ExcelPackage(new FileInfo(filePath));
var worksheet = package.Workbook.Worksheets[0];
var headers = new Dictionary<string, int>();
var startCol = worksheet.Dimension.Start.Column;
var endCol = worksheet.Dimension.End.Column;
for (int col = startCol; col <= endCol; col++)
{
var cell = worksheet.Cells[1, col];
if (!string.IsNullOrEmpty(cell.Text))
{
headers[cell.Text] = col;
}
}
foreach (var kvp in headers)
{
Console.WriteLine($"Header: {kvp.Key}, Index: {kvp.Value}");
}
}
}
如何将表头映射转换为可用的索引结构?
在获取到表头文本后,将其存入Dictionary<string int></string>,键为列名,值为列号(从1开始)。这样在读取数据行时,只需根据目标列名查找对应的列号,再读取该列单元格的值即可完成映射转换。

using OfficeOpenXml;
using System;
using System.IO;
using System.Collections.Generic;
class DataReader
{
private readonly Dictionary<string, int> _headerMap;
public DataReader(string filePath, string sheetName = null)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage(new FileInfo(filePath));
var worksheet = string.IsNullOrEmpty(sheetName)
? package.Workbook.Worksheets[0]
: package.Workbook.Worksheets[sheetName];
_headerMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
var startCol = worksheet.Dimension.Start.Column;
var endCol = worksheet.Dimension.End.Column;
for (int col = startCol; col <= endCol; col++)
{
var cell = worksheet.Cells[1, col];
if (!string.IsNullOrEmpty(cell.Text))
{
_headerMap[cell.Text] = col;
}
}
}
public object GetValue(int row, string columnName)
{
if (_headerMap.TryGetValue(columnName, out int colIndex))
{
return worksheet?.Cells[row, colIndex].Value;
}
return null;
}
}
当Excel中存在重复列名时映射如何处理?
若Excel表中存在重复的列名,上述Dictionary实现会因键冲突而抛出异常或覆盖前值。实际开发中应检查重复项,可选择保留第一个、最后一个或抛出明确错误。例如,在遍历时检测ContainsKey,若已存在则记录警告或替换为新列号,确保映射逻辑符合业务预期。


















