打开 XML 电子表格读取表

Question

我有一个 excel 电子表格，我需要能够导入。此电子表格在 1 个工作表中有许多命名表，这些表具有几个不同的架构以及整个工作表中的其他一些随机数据。我需要能够读取每个表中的数据。

我已经弄清楚了如何获取可用表的列表以及如何读取它们的列标题，但我不确定如何访问数据。

以下是我用于读取标头的代码示例。我真的不需要这些信息(因为我将把这些信息放入模型中并且已经知道列的顺序(，它确实显示了我正在使用的内容。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
...
public static DataTable GetNamedDataTable(SpreadsheetDocument spreadsheetDocument, string cellRange, string DataTableName)
{
    var dataTable = new DataTable();
    Workbook woorkbook = spreadsheetDocument.WorkbookPart.Workbook;
    //Sheet 
    var bustedCellRange = BustCellRange(cellRange);
    Sheet sheet = woorkbook.Descendants<Sheet>().Where(s => s.Name == bustedCellRange["sheetName"]).FirstOrDefault();
    SharedStringTable sharedStringTable = woorkbook.WorkbookPart.SharedStringTablePart.SharedStringTable;
    List<SharedStringItem> allSharedStringItems= sharedStringTable.Descendants<SharedStringItem>().ToList();
    WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
    TableDefinitionPart tableDefinitionPart = worksheetPart.TableDefinitionParts.FirstOrDefault(r => r.Table.Name == DataTableName);
    QueryTablePart queryTablePart = tableDefinitionPart.QueryTableParts.FirstOrDefault();
    Table excelTable = tableDefinitionPart.Table;
    int columnCounter = 0;
    foreach(TableColumn column in excelTable.TableColumns)
    {
        DataColumn dataColumn = dataTable.Columns.Add(column.Name);
        dataColumn.SetOrdinal(columnCounter);
        columnCounter++;
    }
    return dataTable;
}

Answer 1

我终于想出了怎么做。如果您遇到同样的问题，请参阅下文，请注意，我确实计划在我自己的项目中清理很多问题，但这应该可以让您继续前进。

public static DataTable GetNamedDataTable(SpreadsheetDocument spreadsheetDocument, string DataTableName)
{
    var dataTable = new DataTable();
    Workbook woorkbook = spreadsheetDocument.WorkbookPart.Workbook;
    Sheet sheet = woorkbook.Descendants<Sheet>().Where(s => s.Name == "SheetName").FirstOrDefault();
    SharedStringTable sharedStringTable = woorkbook.WorkbookPart.SharedStringTablePart.SharedStringTable;
    List<SharedStringItem> allSharedStringItems= sharedStringTable.Descendants<SharedStringItem>().ToList();
    WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
    TableDefinitionPart tableDefinitionPart = worksheetPart.TableDefinitionParts.FirstOrDefault(r => r.Table.Name == DataTableName);
    QueryTablePart queryTablePart = tableDefinitionPart.QueryTableParts.FirstOrDefault();
    Table excelTable = tableDefinitionPart.Table;
    int columnCounter = 0;
    foreach(TableColumn column in excelTable.TableColumns)
    {
       DataColumn dataColumn = dataTable.Columns.Add(column.Name);
       dataColumn.SetOrdinal(columnCounter);
       columnCounter++;
    }
    var newCellRange = excelTable.Reference;
    var startCell = newCellRange.Value.Split(':')[0];
    var endCell = newCellRange.Value.Split(':')[1];
    uint firstRowNum = GetRowIndex(startCell);
    uint lastRowNum = GetRowIndex(endCell);
    string firstColumn = GetColumnName(startCell);
    string lastColumn = GetColumnName(endCell);
    var columnIndex = 0;
    foreach (Row row in worksheetPart.Worksheet.Descendants<Row>().Where(r => r.RowIndex.Value > firstRowNum && r.RowIndex.Value <= lastRowNum))
    {
        var dataRow = dataTable.NewRow();
        foreach (Cell cell in row)
        {                
            string columnName = GetColumnName(cell.CellReference.Value);
            if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0)
            {
                if (cell.CellValue != null)
                {
                    dataRow[columnIndex] = cell.CellValue.Text;
                }
                columnIndex++;
            }
        }
        dataTable.Rows.Add(dataRow);
        columnIndex = 0;
        Console.WriteLine("");
    }
    return dataTable;
}
private static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"d+");
    Match match = regex.Match(cellName);
    return uint.Parse(match.Value);
}
// Given a cell name, parses the specified cell to get the column name.
private static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);
    return match.Value;
}
// Given two columns, compares the columns.
private static int CompareColumn(string column1, string column2)
{
    if (column1.Length > column2.Length)
    {
        return 1;
    }
    else if (column1.Length < column2.Length)
    {
        return -1;
    }
    else
    {
        return string.Compare(column1, column2, true);
    }
}

这个答案的功劳必须转到以下网站，因为它导致我能够弄清楚这一点。 https://learn.microsoft.com/en-us/office/open-xml/how-to-calculate-the-sum-of-a-range-of-cells-in-a-spreadsheet-document