.NET Core中使用Open Xml读取Excel时如何获取行列及单元格信息

要输出row: 0 and column:0 and cellvalue: somevalue格式的信息，需从Open Xml的Row和Cell对象中提取行、列索引，再结合单元格值组合输出。以下是具体修改方案：

1. 添加列索引解析辅助方法 #

Open Xml的Cell.CellReference属性格式为A1、B2这类字符串，其中字母部分对应列号，我们需要将其转换为0-based索引：

private int ParseColumnIndex(string cellReference)
{
    int columnIndex = 0;
    foreach (char c in cellReference.ToUpperInvariant())
    {
        if (char.IsLetter(c))
        {
            columnIndex = columnIndex * 26 + (c - 'A' + 1);
        }
        else
        {
            break;
        }
    }
    // 转换为0-based索引
    return columnIndex - 1;
}

2. 修改循环逻辑输出目标格式 #

遍历行和单元格时，提取行索引（Row.RowIndex是1-based，需减1转为0-based），通过辅助方法解析列索引，最后组合输出：

修改后的核心循环代码：

foreach (Row row in rows)
{
    DataRow tempRow = table.NewRow();
    // 获取0-based行索引，兼容RowIndex为空的情况
    int rowIndex = row.RowIndex != null ? Convert.ToInt32(row.RowIndex) - 1 : -1;
    
    foreach (Cell cell in row.Descendants<Cell>())
    {
        string cellValue = GetCellValue(spreadSheetDocument, cell);
        int columnIndex = ParseColumnIndex(cell.CellReference);
        
        // 输出目标格式内容
        Console.WriteLine($"row: {rowIndex} and column: {columnIndex} and cellvalue: {cellValue}");
        // 保留原DataTable填充逻辑
        tempRow[columnIndex] = cellValue;
    }
    
    table.Rows.Add(tempRow);
}

完整修改后的方法代码 #

private DataTable ConvertExcelToDataTable(IFormFile uploadRegistration)
{
    var table = new DataTable();

    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(uploadRegistration.OpenReadStream(), false))
    {
        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();
        
        // 添加表头列
        foreach (Cell cell in rows.ElementAt(0))
        {
            table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }
        
        foreach (Row row in rows)
        {
            DataRow tempRow = table.NewRow();
            int rowIndex = row.RowIndex != null ? Convert.ToInt32(row.RowIndex) - 1 : -1;
            
            foreach (Cell cell in row.Descendants<Cell>())
            {
                string cellValue = GetCellValue(spreadSheetDocument, cell);
                int columnIndex = ParseColumnIndex(cell.CellReference);
                
                Console.WriteLine($"row: {rowIndex} and column: {columnIndex} and cellvalue: {cellValue}");
                tempRow[columnIndex] = cellValue;
            }
            
            table.Rows.Add(tempRow);
        }
    }
    
    table.Rows.RemoveAt(0);
    return table;
}

private int ParseColumnIndex(string cellReference)
{
    int columnIndex = 0;
    foreach (char c in cellReference.ToUpperInvariant())
    {
        if (char.IsLetter(c))
        {
            columnIndex = columnIndex * 26 + (c - 'A' + 1);
        }
        else
        {
            break;
        }
    }
    return columnIndex - 1;
}

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