How to get all rows/cell values from a range with OpenXml and c#

Question

I'm using c# with OpenXml to try to read some cell values from an Excel workbook.

I have strings that define the sheet and range of cells I need to extract for example,

'Master Products'!$Q$4:$AD$89"

I'm obtaining the above string from a DefinedName in my Workbookpart instance.

How can I get the cell values described in this range using OpenXml?

FortyTwo FortyTwo · Accepted Answer · 2019-02-06T10:36:55

You can create your customs methods to iterate through a range and read the cell values. I would do it the following way:

static void Main(string[] args)
{
    int rowStart = 4;
    string colStart = "Q";
    int rowEnd = 89;
    string colEnd = "AD";
    string currentRow = colStart;
    // make sure rowStart < rowEnd && colStart < colEnd

    using (document = SpreadsheetDocument.Open(filePath, true))
    {
        WorkbookPart wbPart = document.WorkbookPart;
        Worksheet sheet = wbPart.WorksheetParts.First().Worksheet;
        while(currentRow != GetNextColumn(colEnd))
        {
            for (int i = rowStart; i <= rowEnd; i++)
            {
                Cell cell = GetCell(sheet, currentRow, i);
            }
            currentRow = GetNextColumn(currentRow);
        }
    }
    Console.Read();
}

Method that gets you the cell value:

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();
}

Method to get the row:

// 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();
}

Method to get the next column:

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);
}

How to get all rows/cell values from a range with OpenXml and c#

1 Answers