.NET Core中用OpenXML按行列获取Excel单元格值的方法实现

现有实现代码 #

Excel转DataTable方法 #

private DataTable ConvertExcelToDataTable(IFormFile uploadRegistration)
{
    //Create a new DataTable.
    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();
            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                // tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                Console.WriteLine(GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i))); // Here i am able to display cell value but not row and column details
            }
            table.Rows.Add(tempRow);
        }
    }
    table.Rows.RemoveAt(0);
    return table;
}

解析单元格地址方法 #

public static (int row, int col) ParseCellAddress(string s)
{
     StringBuilder stringBuilderCol = new StringBuilder();
     StringBuilder stringBuilderRow = new StringBuilder();

     foreach (char c in s)
     {
         if (char.IsLetter(c))
         {
             if (stringBuilderRow.Length > 0)
             {
                 throw new ArgumentException($"Invalid Cell adress {s}.");
             }

             stringBuilderCol.Append(c);
         }
         else if (char.IsDigit(c))
         {
             if (stringBuilderCol.Length < 1)
             {
                 throw new ArgumentException($"Invalid Cell adress {s}.");
             }

             stringBuilderRow.Append(c);
         }
         else
         {
             throw new ArgumentException($"Invalid Cell adress {s}.");
         }
     }

     if (stringBuilderRow.Length == 0 || stringBuilderCol.Length == 0)
     {
         throw new ArgumentException($"Invalid Cell adress {s}.");
     }

     int rowIdx = int.Parse(stringBuilderRow.ToString());

     // calc column index from column name

     string columnName = stringBuilderCol.ToString();

     int colIdx = 0;
     int pow = 1;
     for (int i = columnName.Length - 1; i >= 0; i--)
     {
         colIdx += (columnName[i] - 'A' + 1) * pow;
         pow *= 26;
     }

     return (rowIdx, colIdx);
 }

待实现方法 #

private string GetCellValue(int row, int column){
//return data for this particular row and column
}

实现方案 #

方式1：基于已转换的DataTable直接获取 #

如果已经完成Excel到DataTable的转换，直接通过DataTable索引获取是最简便的方式：

// 需将转换后的DataTable作为类成员变量，或传入方法
private string GetCellValue(int row, int column)
{
    // 注意：Excel行号从1开始，DataTable行索引从0开始，且你移除了第一行表头，需做偏移
    int dataTableRowIndex = row - 2;
    int dataTableColumnIndex = column - 1;

    if (dataTableRowIndex < 0 || dataTableRowIndex >= excelDataTable.Rows.Count)
        return string.Empty;
    if (dataTableColumnIndex < 0 || dataTableColumnIndex >= excelDataTable.Columns.Count)
        return string.Empty;

    return excelDataTable.Rows[dataTableRowIndex][dataTableColumnIndex]?.ToString() ?? string.Empty;
}

方式2：直接操作OpenXML对象获取 #

若不想依赖DataTable，可直接从Excel文档中定位目标单元格：

// 新增列号转Excel列名的工具方法
private static string ConvertColumnIndexToName(int columnIndex)
{
    StringBuilder sb = new StringBuilder();
    while (columnIndex > 0)
    {
        int remainder = (columnIndex - 1) % 26;
        sb.Insert(0, (char)('A' + remainder));
        columnIndex = (columnIndex - 1) / 26;
    }
    return sb.ToString();
}

// 核心实现方法，需在SpreadsheetDocument的using块内调用
private string GetCellValue(int targetRow, int targetColumn, SpreadsheetDocument doc, Worksheet worksheet)
{
    string targetColumnName = ConvertColumnIndexToName(targetColumn);
    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
    
    // 定位目标行
    Row row = sheetData.Descendants<Row>().FirstOrDefault(r => r.RowIndex == targetRow);
    if (row == null)
        return string.Empty;
    
    // 定位目标列的单元格
    Cell cell = row.Descendants<Cell>().FirstOrDefault(c => c.CellReference?.StartsWith(targetColumnName) == true);
    
    return cell != null ? GetCellValue(doc, cell) : string.Empty;
}

使用说明 #

• 方式1需确保ConvertExcelToDataTable执行后，DataTable实例被保留供后续调用
• 方式2需在SpreadsheetDocument的using代码块内调用，避免文档对象被提前释放

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