List from OpenXML Excel Column Headers

[watergeek]

I am using VB.net 2008 express edition

I have figured out how to use some of the code snippets for the open xml sdk to create a drop down list of all the sheets in an excel file.

I can also use the code snippet to get the value of an individual column header.

What I want is a drop down list of all column headers.

I could create an array containing the alphabet and loop through each column based on the alphabet letter in the array. I would then just exit the loop on the first column with no value returned for column header...but there must be an easy way to return all headers in one query of the xml.

any examples or suggestions would be apreciated.

Thanks

[kvprajapati]

Show us your code. I recommend extrememl.

[watergeek]

here is the code from the snippets:

' Given a document name, a worksheet name, and a cell name, get the column of the cell and return
    ' the content of the first cell in that column.
    Public Function XLGetColumnHeader(ByVal docName As String, _
        ByVal worksheetName As String, ByVal cellName As String) As String

        Dim returnValue As String = Nothing

        ' Open the document as read-only.
        Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, False)
            Dim wbPart As WorkbookPart = document.WorkbookPart

            ' Given a worksheet name, first find the Sheet that corresponds to the name.
            Dim sheet = wbPart.Workbook.Descendants(Of Sheet)(). _
              Where(Function(s) s.Name = worksheetName).FirstOrDefault
            If sheet Is Nothing Then
                ' The specified worksheet does not exist.
                Return Nothing
            End If

            ' Given the Sheet, 
            Dim worksheetPart As WorksheetPart = CType(wbPart.GetPartById(sheet.Id), WorksheetPart)

            ' Get the column name for the specified cell.
            Dim columnName As String = GetColumnName(cellName)

            ' Get the cells in the specified column and order them by row.
            Dim headCell = worksheetPart.Worksheet.Descendants(Of Cell)(). _
              Where(Function(c) String.Compare(GetColumnName(c.CellReference.Value), columnName, True) = 0). _
              OrderBy(Function(r) GetRowIndex(r.CellReference)).FirstOrDefault()

            If headCell Is Nothing Then
                ' The specified column does not exist.
                Return Nothing
            End If

            ' If the content of the first cell is stored as a shared string, get the text of the first cell
            ' from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
            If headCell.DataType IsNot Nothing AndAlso headCell.DataType.Value = CellValues.SharedString Then
                Dim sharedStringPart As SharedStringTablePart = wbPart.GetPartsOfType(Of SharedStringTablePart)().FirstOrDefault()
                If sharedStringPart IsNot Nothing Then
                    Dim items = sharedStringPart.SharedStringTable.Elements(Of SharedStringItem)()
                    returnValue = items.ElementAt(Integer.Parse(headCell.CellValue.Text)).InnerText
                End If
            Else
                returnValue = headCell.CellValue.Text
            End If
        End Using
        Return returnValue
    End Function

    ' Given a cell name, parses the specified cell to get the column name.
    Private Function GetColumnName(ByVal cellName As String) As String
        ' Create a regular expression to match the column name portion of the cell name.
        Dim regex As Regex = New Regex("[A-Za-z]+")
        Dim match As Match = regex.Match(cellName)
        Return match.Value
    End Function

    ' Given a cell name, parses the specified cell to get the row index.
    Private Function GetRowIndex(ByVal cellName As String) As UInteger
        ' Create a regular expression to match the row index portion the cell name.
        Dim regex As Regex = New Regex("\d+")
        Dim match As Match = regex.Match(cellName)
        Return UInteger.Parse(match.Value)
    End Function

here is what I came up with looping trough each column until the first column with no header:

' get a list of column headers.
    Public Function XLColumnList(ByVal fileName As String, ByVal worksheetName As String) As List(Of String)
        'Declare variable TO HOLd list of column headers
        Dim Headers As List(Of String) = New List(Of String)

        'declare variable to hold the alphabet & a letter number
        Dim Alphabet() As String = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", _
                                  "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}

        'Variable to hold the current index for the alphabet array
        Dim AlphabetIndex As Integer = 0

        'Declare variable to count which iteration of the alphabet the loop is on
        Dim AlphabetCount As Integer = 0


        'variable to hold last column header name and give it a value so the loop will start
        Dim LastHeader As String = "None"


        While Not LastHeader Is Nothing

            Select Case AlphabetCount
                Case 0 To 26
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, Alphabet(AlphabetIndex) + "1")
                Case 27 To 52
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "A" + Alphabet(AlphabetIndex) + "1")
                Case 52 To 78
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "B" + Alphabet(AlphabetIndex) + "1")
                Case 79 To 104
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "C" + Alphabet(AlphabetIndex) + "1")
                Case 105 To 130
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "D" + Alphabet(AlphabetIndex) + "1")
                Case 131 To 156
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "E" + Alphabet(AlphabetIndex) + "1")
                Case 157 To 182
                    LastHeader = XLGetColumnHeader(fileName, worksheetName, "F" + Alphabet(AlphabetIndex) + "1")
            End Select

            If Not LastHeader Is Nothing Then
                Headers.Add(LastHeader)
            End If
            AlphabetCount = AlphabetCount + 1
            If AlphabetIndex < 25 Then
                AlphabetIndex = AlphabetIndex + 1
            Else
                AlphabetIndex = 0
            End If
            If AlphabetCount > 182 Then
                Headers.Add("Exceeded 182 column limit")
                LastHeader = Nothing
            End If
        End While
        If Headers Is Nothing Then
            Headers.Add("No column headers.")
        End If
        Return Headers
    End Function

I was thinking that it would be cleaner if I could do something similar to "select CellValue where row = 1" (after opening the apropriate file and worksheet).

It seems like editing this part of the XLGetColumnHeader function:

Dim headCell = worksheetPart.Worksheet.Descendants(Of Cell)(). _
              Where(Function(c) String.Compare(GetColumnName(c.CellReference.Value), columnName, True) = 0). _
              OrderBy(Function(r) GetRowIndex(r.CellReference)).FirstOrDefault()

[kvprajapati]

I don't think that there is an easy way to get list of columns with OpenXML API. Of course, look at my post #2, ExtremeML; a new open source library that adds a powerful layer of additional functionality to the OpenXml SDK. Read this article.

[watergeek]

I assume you are thinking that I would do something similar to the example for itrating over cells?

Are there any example of using extremeML to populate a datagridview with speadsheet content?

[David Sanchez]

for opening xml excel column header, you can use avb.net excel component. It's named Spire.XLS.

Hope help to you.