如何用C#(OpenXML)读取Excel中合并单元格的roles列单个值？

问题场景 #

你有如下结构的Excel表格，其中NURSE行对应的roles列是跨3行的合并单元格，需要将该合并单元格的内容读取为单个值：

解决方案步骤 #

• 先解析Excel的合并单元格区域，建立合并单元格与起始单元格的映射关系
• 读取单元格时，判断当前单元格是否属于合并区域，若是则读取对应起始单元格的内容；若合并单元格内有多行内容，可按换行符拆分后拼接为单个值

代码实现 #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;

public class ExcelMergeCellHandler
{
    private Dictionary<string, string> _mergeCellMapping = new Dictionary<string, string>();

    public void ReadMergedCellContent(string excelPath)
    {
        using (var doc = SpreadsheetDocument.Open(excelPath, false))
        {
            var workbookPart = doc.WorkbookPart;
            var worksheetPart = workbookPart.WorksheetParts.First();
            var worksheet = worksheetPart.Worksheet;

            // 初始化合并单元格映射表
            BuildMergeCellMap(worksheet);

            var sharedStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // 遍历所有行读取内容
            foreach (var row in worksheet.Descendants<Row>())
            {
                foreach (var cell in row.Descendants<Cell>())
                {
                    var cellRef = cell.CellReference.Value;
                    // 如果当前单元格属于合并区域，切换到起始单元格的引用
                    if (_mergeCellMapping.ContainsKey(cellRef))
                    {
                        cellRef = _mergeCellMapping[cellRef];
                    }

                    // 获取实际单元格内容
                    var cellValue = GetActualCellValue(doc, cell, sharedStringPart);

                    // 处理合并单元格内的换行内容，拼接为单个值
                    if (cellValue.Contains(Environment.NewLine))
                    {
                        cellValue = string.Join(", ", cellValue.Split(new[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries));
                    }

                    Console.WriteLine($"单元格{cellRef}内容：{cellValue}");
                }
            }
        }
    }

    private void BuildMergeCellMap(Worksheet worksheet)
    {
        var mergeCells = worksheet.Elements<MergeCells>().FirstOrDefault();
        if (mergeCells == null) return;

        foreach (var mergeCell in mergeCells.Elements<MergeCell>())
        {
            var rangeParts = mergeCell.Reference.Value.Split(':');
            if (rangeParts.Length != 2) continue;

            var startCell = rangeParts[0];
            var allMergedCells = EnumerateCellsInRange(startCell, rangeParts[1]);

            foreach (var cell in allMergedCells)
            {
                if (!_mergeCellMapping.ContainsKey(cell))
                {
                    _mergeCellMapping.Add(cell, startCell);
                }
            }
        }
    }

    private IEnumerable<string> EnumerateCellsInRange(string startCell, string endCell)
    {
        var startCol = GetColumnFromRef(startCell);
        var startRow = int.Parse(startCell.Substring(startCol.Length));
        var endCol = GetColumnFromRef(endCell);
        var endRow = int.Parse(endCell.Substring(endCol.Length));

        // 仅处理同列合并（符合当前场景，如需多列合并可扩展逻辑）
        if (startCol != endCol) yield break;

        for (int row = startRow; row <= endRow; row++)
        {
            yield return $"{startCol}{row}";
        }
    }

    private string GetColumnFromRef(string cellRef)
    {
        return new string(cellRef.TakeWhile(char.IsLetter).ToArray());
    }

    private string GetActualCellValue(SpreadsheetDocument doc, Cell cell, SharedStringTablePart sharedStringPart)
    {
        if (cell.CellValue == null) return string.Empty;

        var value = cell.CellValue.InnerText;

        // 处理共享字符串类型的单元格
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            if (sharedStringPart != null && int.TryParse(value, out int idx))
            {
                value = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ElementAt(idx).InnerText;
            }
        }

        return value;
    }
}

// 使用示例
class Program
{
    static void Main(string[] args)
    {
        var handler = new ExcelMergeCellHandler();
        handler.ReadMergedCellContent(@"你的Excel文件路径.xlsx");
    }
}

关键说明 #

• 合并单元格映射：通过BuildMergeCellMap方法将合并区域内的所有单元格指向起始单元格，确保读取时能获取到唯一的内容源
• 共享字符串处理：Excel会将重复内容存入共享字符串表，需判断单元格类型并从表中读取实际内容
• 换行内容拼接：针对合并单元格内的多行内容，通过拆分换行符再拼接的方式，将其转为单个逗号分隔的字符串

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