c# - 如何使用 OpenXML 在 Excel 中读取指定范围

Question

我想读取指定的行单元格范围。

例如：

• 从 Row=3 和 Column=8 开始到 Row=3 和 Column=25
• 从 Row=7 和 Column=4 开始到 Row=7 和 Column=32

我可以使用 OpenXML 获取 WorkSheet 信息。（官方文档在这里）

WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
string text;

foreach (Row r in sheetData.Elements<Row>())
{
    foreach (Cell c in r.Elements<Cell>())
    {
        text = c.CellValue.Text;
        Console.Write(text + " ");
    }
}

但我不想这样使用foreach。因为某些文档太长或某些指定的行和单元格被合并了。

我需要一种读取范围的方法。

标签： c#excelopenxml

Answer 1

您可以通过循环遍历范围来遍历所需的单元格：

class Program
{
    static void Main(string[] args)
    {
        int rowStart = 1;
        string colStart = "Y";
        int rowEnd = 5;
        string colEnd = "AC";
        string currentRow = colStart;
        bool done = false;
        // check if rowStart < rowEnd && colStart < colEnd

        using (document = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart wbPart = document.WorkbookPart;
            Worksheet sheet = wbPart.WorksheetParts.First().Worksheet;
            for (; ; )
            {
                for (int i = rowStart; i <= rowEnd; i++)
                {
                    // Your cell
                    Cell cell = GetCell(sheet, currentRow, i);
                }

                currentRow = GetNextColumn(currentRow);
                if (done)
                {
                    break;
                }
                if (currentRow == colEnd)
                {
                    done = true;
                }
            }
        }
        Console.Read();
    }

    private static Cell GetCell(Worksheet worksheet,
        string columnName, uint rowIndex)
    {
        Row row = GetRow(worksheet, rowIndex);

        if (row == null)
            return null;

        return row.Elements<Cell>().Where(c => string.Compare
                                               (c.CellReference.Value, columnName +
                                                                       rowIndex, true) == 0).First();
    }


    // Given a worksheet and a row index, return the row.
    private static Row GetRow(Worksheet worksheet, uint rowIndex)
    {
        return worksheet.GetFirstChild<SheetData>().
            Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
    }

    static string GetNextColumn(string col)
    {
        char[] charArr = col.ToCharArray();
        var cur = Convert.ToChar((int) charArr[charArr.Length - 1]);
        if (cur == 'Z')
        {
            if (charArr.Length == 1)
            {
                return "AA";
            }
            else
            {
                char[] newArray = charArr.Take(charArr.Length - 1).ToArray();
                var ret = GetNextColumn(new string(newArray));
                return ret + "A";
            }
        }
        charArr[charArr.Length - 1] = Convert.ToChar((int)charArr[charArr.Length - 1] + 1);
        return new string(charArr);

    }
}