OpenXml WorksheetParts.First() is not always the 1st sheet MS Excel shows

Question

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 }

Answer 1

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.

Answer 2

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