如何用OpenXML像读取数据库行一样读取Excel XLSX行并处理空单元格？

昨天这事本来应该简单，结果完全不是那么回事，差点搞崩我。这个工作簿用了共享字符串，我已经找到获取单元格值的方法，但要是能直接拿到单元格的显示值就不用绕圈子了。更关键的是，我要获取每行A、B、C列的值，但如果单元格是空的，程序就会认为这个单元格不存在！能不能把foreach Row里的伪代码补全？最好能直接取值，不过你可以假设我已经有处理共享字符串表查找的方法。

原代码片段：

try
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open("C:\\temp\\sitemap.xlsx", false))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
        StringBuilder excelResult = new StringBuilder();

        foreach (Sheet thesheet in thesheetcollection)
        {
            excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name);
            excelResult.AppendLine("----------------------------------------------- ");

            Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
            SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();

            foreach (Row thecurrentrow in thesheetdata)
            {
                // Help needed here
                var val1 = thecurrentrow[0].InnerText; // Col A
                var val2 = thecurrentrow[1].InnerText; // Col B
                var val3 = thecurrentrow[2].InnerText; // Col C
            }
            excelResult.Append("");
            Console.WriteLine(excelResult.ToString());
            Console.ReadLine();
        }
    }
}
catch (Exception)
{

}

核心问题是空单元格不会被写入Excel的XML结构，直接用索引访问会报错。下面是补全后的代码，同时兼容共享字符串解析：

try
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open("C:\\temp\\sitemap.xlsx", false))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
        StringBuilder excelResult = new StringBuilder();

        foreach (Sheet thesheet in thesheetcollection)
        {
            excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name);
            excelResult.AppendLine("----------------------------------------------- ");

            Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
            SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();

            foreach (Row thecurrentrow in thesheetdata)
            {
                // 按列名查找单元格，空单元格会返回null
                Cell cellA = thecurrentrow.Elements<Cell>().FirstOrDefault(c => c.CellReference?.Value.StartsWith("A") == true);
                Cell cellB = thecurrentrow.Elements<Cell>().FirstOrDefault(c => c.CellReference?.Value.StartsWith("B") == true);
                Cell cellC = thecurrentrow.Elements<Cell>().FirstOrDefault(c => c.CellReference?.Value.StartsWith("C") == true);

                // 处理值：存在则解析共享字符串，不存在则返回空字符串
                var val1 = cellA != null ? GetSharedStringValue(workbookPart, cellA.InnerText) : string.Empty;
                var val2 = cellB != null ? GetSharedStringValue(workbookPart, cellB.InnerText) : string.Empty;
                var val3 = cellC != null ? GetSharedStringValue(workbookPart, cellC.InnerText) : string.Empty;

                // 将获取到的值追加到结果中
                excelResult.AppendLine($"A列: {val1}, B列: {val2}, C列: {val3}");
            }
            Console.WriteLine(excelResult.ToString());
            Console.ReadLine();
            // 清空StringBuilder，避免下一个Sheet的数据重叠
            excelResult.Clear();
        }
    }
}
catch (Exception ex)
{
    // 建议添加异常日志，方便排查问题
    Console.WriteLine($"出错了：{ex.Message}");
}

// 假设你已实现的共享字符串解析方法
private static string GetSharedStringValue(WorkbookPart workbookPart, string sharedStringId)
{
    if (!int.TryParse(sharedStringId, out int id))
        return sharedStringId;

    SharedStringTablePart sharedStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
    if (sharedStringPart == null)
        return sharedStringId;

    var sharedStringItems = sharedStringPart.SharedStringTable.Elements<SharedStringItem>();
    return id < sharedStringItems.Count() ? sharedStringItems.ElementAt(id).InnerText : sharedStringId;
}

关键说明 #

• 不用索引直接访问单元格，而是通过CellReference的列前缀查找，避免空单元格导致的索引越界
• 空单元格直接返回空字符串，保证val1/val2/val3始终有有效值
• 集成共享字符串解析逻辑，直接返回单元格的显示文本
• 补充了异常捕获的日志输出，以及StringBuilder的清空逻辑，避免多Sheet数据混乱

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