none
How to identify if an Excel sheet is a WorkSheet or a ChartSheet RRS feed

  • Question

  • I am looking for a way to identify if an Excel sheet is a WorkSheet or a ChartSheet, in the below when it executes the last line, it will throw an exception if the sheet is a ChartSheet. Is there a way to identify the type of sheet using the Sheet.Id?

                Using SDocument As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
                    Dim workbookPart As WorkbookPart = SDocument.WorkbookPart
                    workbookPart.Workbook.Descendants(Of Sheet)()
                    For Each Sheet As Sheet In SDocument.WorkbookPart.Workbook.Sheets
                        Try
                            Dim sID As String = Sheet.Id
                            Console.WriteLine(sID)
                            Dim part As WorksheetPart = workbookPart.GetPartById(sID)

    Thursday, April 10, 2014 11:33 AM

Answers

  • Hi,

    The exception is caused by Unable to cast object of type when you cast 'DocumentFormat.OpenXml.Packaging.ChartsheetPart' to type 'DocumentFormat.OpenXml.Packaging.WorksheetPart'.

    You can use code below to identify the type of sheet part:

    For Each Sheet As Sheet In SDocument.WorkbookPart.Workbook.Sheets
    
                    Try
    
                        Dim sID As String = Sheet.Id
    
                        Console.WriteLine(sID)
    
                        'Dim part As WorksheetPart = workbookPart.GetPartById(sID)
    
                        Dim part = workbookPart.GetPartById(sID)
    
                        If TypeOf part Is ChartsheetPart Then
    
                            Console.WriteLine("     is ChartsheetPart!")
    
                        End If
    
    
                        If TypeOf part Is WorksheetPart Then
    
                            Console.WriteLine("     is WorksheetPart!")
    
                        End If
    
    
                    Catch
    
    
                    End Try
    
    
                Next Sheet
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 3:17 AM
    Moderator

All replies

  • I am looking for a way to identify if an Excel sheet is a WorkSheet or a ChartSheet, in the below when it executes the last line, it will throw an exception if the sheet is a ChartSheet. Is there a way to identify the type of sheet using the Sheet.Id?

                Using SDocument As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
                    Dim workbookPart As WorkbookPart = SDocument.WorkbookPart
                    workbookPart.Workbook.Descendants(Of Sheet)()
                    For Each Sheet As Sheet In SDocument.WorkbookPart.Workbook.Sheets
                        Try
                            Dim sID As String = Sheet.Id
                            Console.WriteLine(sID)
                            Dim part As WorksheetPart = workbookPart.GetPartById(sID)

                            
    Thursday, April 10, 2014 11:28 AM
  • Hi,

    I'm afraid that it is not the correct forum about this issue, since this forum is to discuss Visual Basic.

    So I have moved this thread to Office Development forum for better response.

    Thanks for your understanding.

    Best regards,
    Franklin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 2:39 AM
  • Hi,

    The exception is caused by Unable to cast object of type when you cast 'DocumentFormat.OpenXml.Packaging.ChartsheetPart' to type 'DocumentFormat.OpenXml.Packaging.WorksheetPart'.

    You can use code below to identify the type of sheet part:

    For Each Sheet As Sheet In SDocument.WorkbookPart.Workbook.Sheets
    
                    Try
    
                        Dim sID As String = Sheet.Id
    
                        Console.WriteLine(sID)
    
                        'Dim part As WorksheetPart = workbookPart.GetPartById(sID)
    
                        Dim part = workbookPart.GetPartById(sID)
    
                        If TypeOf part Is ChartsheetPart Then
    
                            Console.WriteLine("     is ChartsheetPart!")
    
                        End If
    
    
                        If TypeOf part Is WorksheetPart Then
    
                            Console.WriteLine("     is WorksheetPart!")
    
                        End If
    
    
                    Catch
    
    
                    End Try
    
    
                Next Sheet
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 3:17 AM
    Moderator
  • Great, thank you, does exactly what I was looking for.
    Friday, April 11, 2014 9:53 AM