How can I get an Excel worksheet's used range with OpenXML?

Question

I need to get the used range of cells in a new worksheet. For example:

A1:AY55

In Excel VBA, this can be obtained through the aptly named UsedRange property. Is there an equivalent in OpenXML?

Answer 1

It can be found in the SheetDimension class which can be found as a property of a Worksheet. The following code will write the used range to the console:

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    //get the correct sheet
    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").First();
    WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
    Console.WriteLine(worksheetPart.Worksheet.SheetDimension.Reference);
}