4

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?

Eric Eskildsen
  • 4,269
  • 2
  • 38
  • 55

1 Answers1

4

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);
}
petelids
  • 12,305
  • 3
  • 47
  • 57
  • `SheetDimension` is null as the worksheet is new. Does this only work with existing files or is there a way to get it for newly generated worksheets? – Eric Eskildsen Apr 13 '17 at 12:44
  • For a newly generated sheet the value won't exist unless you've written it yourself via OpenXml. There is nothing automated within OpenXml to generate that information unfortunately. If the sheet is new though, does that mean you're generating it? In that case won't you already know the range? – petelids Apr 13 '17 at 12:48
  • I know the numeric range but was looking for a built-in way to get the address range as with VBA. If that's not implemented you have [a great answer for generating Excel column names](http://stackoverflow.com/a/31035990/1958726) that I'll work from. – Eric Eskildsen Apr 13 '17 at 12:55
  • Excellent, thanks @EricEskildsen - good luck with your project. – petelids Apr 13 '17 at 12:57
  • Thanks for the help. I suggest adding a link to your other answer for extra exposure if this is the usual path for new worksheets. Edit: Future readers, see also [this answer](http://stackoverflow.com/a/182924/1958726), which is specifically about generating column names rather than the reverse. – Eric Eskildsen Apr 13 '17 at 13:02