4

I have XLSX files which when viewed in Excel have multiple sheets. However, some of the files using the snippet below actually have the WorksheetPart.First as the 2nd or 3rd worksheet when viewed in excel. I think this is because the sheets were re-arranged in excel at one point.

Q: How to use OpenXml to read the sheets in the "view" order that MS-Excel shows them in, versus what can be out of order via OpenXml? Note: I can't use sheet name as a workaround.

using (var document = SpreadsheetDocument.Open(".\test.xlsx", false)) { var workbookPart = document.WorkbookPart; var worksheetPart = workbookPart.WorksheetParts.First(); // worksheetPart is not always the first worksheet that Excel shows }

Matthew Mc
  • 43
  • 1
  • 3

2 Answers2

4

I guess the worksheet parts are not necessarily in order. What should be in order though is the Workbook.Sheets property (you can also search by name here). You can correlate a Sheet with its WorksheetPart through it's Id, see here for example.

Community
  • 1
  • 1
fejesjoco
  • 11,763
  • 3
  • 35
  • 65
  • Convoluted for sure. I'm convinced I am missing the point of worksheetpart separation altogether though now. – Matthew Mc Nov 14 '14 at 15:54
  • 2
    The OpenXML SDK comes with a tool (OpenXMLSDKToolV25.msi). You can use that open and visualize an XLSX or any other OpenXML document. (Or you can just rename a document to .zip and see what's in there.) It will all make sense: there are multiple XML's in there, which are the different parts. The WorkbookPart is workbook.xml, which contains some metadata about the sheets. Sheets are stored in files like sheet1.xml, sheet2.xml, etc., they contain the actual sheet data. And there are many other parts as well. – fejesjoco Nov 14 '14 at 16:49
0

I hope, this way you will get the right worksheetpart for the sheet index.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;

// ----------------------------------------------------------------------

using (SpreadsheetDocument xl = SpreadsheetDocument.Open(fileName, false))
{
int sheetNo = 0; // Index 0 => sheet 1

WorkbookPart wbPart = xl.WorkbookPart;
Sheet sheet = wbPart.Workbook
                     .Descendants<Sheet>()
                     .ElementAt(sheetNo);
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
}
Chandraprakash
  • 773
  • 1
  • 10
  • 18