Hi... I am trying to use the Open XML SDK to generate an Excel document that has multiple worksheets. My current strategy is to use Excel to create a template that already has all the worksheets present and named (via the tabs at the bottom of Excel), make a copy of the template, and then fill the data into each worksheet as required.
I am able to get a collection of all the worksheets using VB code like this:
Using excelDoc As SpreadsheetDocument = SpreadsheetDocument.Open("C:\excelFile.xlsx", True) Dim excelDocWorksheetParts = excelDoc.WorkbookPart.GetPartsOfType(Of WorksheetPart)() For Each excelDocWorksheetPart In excelDocWorksheetParts ... Next End Using
But when I cycle through the WorksheetParts in excelDocWorksheetParts using a For Each loop, my testing has shown that the collection of worksheets returned by the above code are ordered in an apparently random manner. I was expecting them to be ordered from left-to-right as they appear in the tabs at the bottom of Excel, but this is not so.
Clearly, I need a way to figure out which worksheet I am working with in each iteration of the For Each loop.
It seems reasonable to me that I should be able to distinguish between worksheets by using the name that I have given each worksheet in the Excel tabs, but I just can't figure out how to do this. Unfortunately, every code example I can find deals only with documents have only one worksheet.
If anybody has any ideas as to how I might go about distinguishing between worksheets within the For Each loop, I would love to read them.
Better yet, if you know how I can just get the one WorksheetPart by name that I am interested in, that would be excellent.
I found example code on the Microsoft website that is exactly what I am looking for.