如何用OpenXML和C#基于Excel定义名称获取表格/单元格数据

核心思路概述 #

定义名称（Defined Name）的Text属性存储的是关联的单元格范围表达式（如Sheet1!A1:C10），核心步骤为：解析范围表达式 → 定位目标工作表和单元格区域 → 提取该区域内的所有数据。

具体实现步骤 #

1. 解析定义名称的范围表达式 #

需要将dn.Text的字符串拆分为工作表名称、起始单元格、结束单元格，并将列字母转换为数字索引（OpenXML中列以数字标识，A=1，B=2...）。

示例解析方法：

// 解析定义名称的范围，返回(工作表名, 起始行, 起始列, 结束行, 结束列)
private static (string SheetName, uint StartRow, uint StartCol, uint EndRow, uint EndCol) ParseDefinedNameRange(string rangeText)
{
    // 拆分工作表名和区域部分，比如"Sheet1!A1:C10" → ["Sheet1", "A1:C10"]
    var parts = rangeText.Split('!');
    string sheetName = parts[0].Trim('\''); // 处理带空格的表名，比如'Sheet 1'!A1:C10
    var rangeParts = parts[1].Split(':');
    
    // 解析起始单元格
    var startCell = ParseCellReference(rangeParts[0]);
    // 解析结束单元格，处理单个单元格的情况
    var endCell = rangeParts.Length == 2 ? ParseCellReference(rangeParts[1]) : startCell;
    
    return (sheetName, startCell.Row, startCell.Column, endCell.Row, endCell.Column);
}

// 解析单元格引用（如A1 → 列1，行1）
private static (uint Row, uint Column) ParseCellReference(string cellRef)
{
    // 分离字母列和数字行
    int splitIndex = 0;
    while (splitIndex < cellRef.Length && char.IsLetter(cellRef[splitIndex]))
    {
        splitIndex++;
    }
    string columnLetters = cellRef.Substring(0, splitIndex);
    uint row = uint.Parse(cellRef.Substring(splitIndex));
    
    // 列字母转数字：A=1, B=2... Z=26, AA=27...
    uint column = 0;
    foreach (char c in columnLetters.ToUpper())
    {
        column = column * 26 + (uint)(c - 'A' + 1);
    }
    
    return (row, column);
}

2. 优化定义名称的获取（避免重复打开文档） #

原代码中GetDefinedNames单独打开文档，可整合到同一个using块中减少IO操作：

// 从已打开的WorkbookPart获取定义名称
private static Dictionary<string, string> GetDefinedNames(WorkbookPart workbookPart)
{
    var returnValue = new Dictionary<string, string>();
    DefinedNames definedNames = workbookPart.Workbook.DefinedNames;
    if (definedNames != null)
    {
        foreach (DefinedName dn in definedNames)
        {
            if (!returnValue.ContainsKey(dn.Name.Value))
            {
                returnValue.Add(dn.Name.Value, dn.Text);
            }
        }
    }
    return returnValue;
}

3. 根据解析的范围提取数据 #

遍历指定行和列的单元格，提取值并整理为DataTable：

// 根据定义名称的范围提取数据到DataTable
private static DataTable GetDataFromRange(SpreadsheetDocument doc, string sheetName, uint startRow, uint startCol, uint endRow, uint endCol)
{
    DataTable table = new DataTable();
    WorkbookPart workbookPart = doc.WorkbookPart;
    
    // 定位目标工作表
    Sheet targetSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().First(s => s.Name.Value == sheetName);
    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(targetSheet.Id.Value);
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    
    // 遍历范围内的行
    foreach (Row row in sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= startRow && r.RowIndex.Value <= endRow))
    {
        DataRow dataRow = table.NewRow();
        // 遍历范围内的列
        for (uint col = startCol; col <= endCol; col++)
        {
            Cell cell = row.Descendants<Cell>().FirstOrDefault(c => GetColumnIndexFromCellReference(c.CellReference) == col);
            string cellValue = GetCellValue(doc, cell);
            
            // 第一行（标题行）初始化列
            if (row.RowIndex.Value == startRow)
            {
                table.Columns.Add(cellValue);
            }
            else
            {
                dataRow[col - startCol] = cellValue ?? string.Empty;
            }
        }
        // 非标题行添加到DataTable
        if (row.RowIndex.Value > startRow)
        {
            table.Rows.Add(dataRow);
        }
    }
    return table;
}

// 从单元格引用获取列索引（如A1 → 1）
private static uint GetColumnIndexFromCellReference(string cellRef)
{
    if (string.IsNullOrEmpty(cellRef))
        return 0;
    
    int splitIndex = 0;
    while (splitIndex < cellRef.Length && char.IsLetter(cellRef[splitIndex]))
    {
        splitIndex++;
    }
    string columnLetters = cellRef.Substring(0, splitIndex);
    
    uint column = 0;
    foreach (char c in columnLetters.ToUpper())
    {
        column = column * 26 + (uint)(c - 'A' + 1);
    }
    return column;
}

// 复用你已有的GetCellValue方法
private static string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
    if (cell == null)
        return string.Empty;
    
    string value = cell.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        SharedStringTablePart stringTablePart = doc.WorkbookPart.SharedStringTablePart;
        value = stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;
    }
    return value;
}

4. 整合到主逻辑中 #

修改原有的主代码，调用上述方法：

using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
{
    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
    // 获取定义名称
    var definedNames = GetDefinedNames(workbookPart);
    
    foreach (KeyValuePair<string, string> dn in definedNames)
    {
        Console.WriteLine($"处理定义名称: {dn.Name}，范围: {dn.Value}");
        // 解析范围
        var rangeInfo = ParseDefinedNameRange(dn.Value);
        // 获取对应数据
        DataTable table = GetDataFromRange(spreadSheetDocument, rangeInfo.SheetName, rangeInfo.StartRow, rangeInfo.StartCol, rangeInfo.EndRow, rangeInfo.EndCol);
        
        // 这里可以对获取到的DataTable进行后续处理，比如输出、保存等
        Console.WriteLine($"获取到{table.Rows.Count}行数据");
    }
}

注意事项 #

• 处理带空格的工作表名：这类表名在范围表达式中会被单引号包裹（如'Sales Data'!A1:D20），解析时需要去掉单引号
• 处理单个单元格的定义名称：范围表达式可能只有一个单元格（如Sheet1!B5），此时起始和结束单元格相同
• 空单元格处理：当行中不存在某列的单元格时，默认填充空字符串

内容的提问来源于stack exchange，提问作者Jack