none
Can we use indexing and .Count with OpenXML?

    Question

  • I am told that OpenXML is very fast and am porting one of my apps to it. One of the operations is rather common: to iterate through the worksheet names. I am used to doing this sort of thing with a for loop.

    However, the example that I found here:

    https://docs.microsoft.com/en-us/office/open-xml/how-to-retrieve-a-list-of-the-worksheets-in-a-spreadsheet

    performs the iteration using a foreach() statement.

    static void Main(string[] args)
    {
       var results = GetAllWorksheets(DEMOFILE);
       foreach (Sheet item in results)
       {
         Console.WriteLine(item.Name);
       }
    }

    I am definitely partial to for loops, and being able to .Count items. Are those essential features missing from OpenXML?

    TIA



    mardi 10 juillet 2018 23:02

Toutes les réponses

  • It seems that anything related to indexing is not available? This code does not work:

    int sheetIndex = 0;
    foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
    {                     
        Worksheet worksheet = worksheetpart.Worksheet;
    
        // Grab the sheet name each time through your loop
        string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;
    
        foreach (SheetData sheetData in worksheet.Elements<SheetData>())
        {
    
           ...
        }
        sheetIndex++;
    }
    

    It was posted here:

    https://stackoverflow.com/questions/7504285/how-to-retrieve-tab-names-from-excel-sheet-using-openxml

    More specifically, the .ElementAt(index) is not reachable. Perhaps I need a cast?

    TIA

    mercredi 11 juillet 2018 00:08
  • Hello Travis Banger,

    What do you mean it does not work? Did you get any error message?

    Below code works for me to count the sheet number and iterate through sheets name. Please check if it could work for you.

        string fileName = @"C:\Users\terryx\Desktop\TestFolder\Book1.xlsx";
                using (SpreadsheetDocument document =SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart wbPart = document.WorkbookPart;
                    int sheetCount = wbPart.Workbook.Descendants<Sheet>().Count();
                    string sheetName;
                    for (int i = 1; i <=sheetCount; i++) {
                        sheetName = wbPart.Workbook.Descendants<Sheet>().ElementAt(i-1).Name;
                        MessageBox.Show("The name of sheet " + i + " is " + sheetName);
                    }              
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    mercredi 11 juillet 2018 05:19
    Modérateur
  • Hello Travis Banger,

    What's the current state of the thread? If your issue has been solved, I would suggest you mark helpful reply to close the thread or provide your solution and mark it to help more people who runs into the same issue.

    If not, please feel free to let us know if you have any updates for your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    il y a 5 heure(s) et 1 minute(s)
    Modérateur