List of Excel column headers from OpenXML SDK 2

[WaterGeek]

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 am using vb.net 2008 express edition.

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

 

[jebenson]

Post the code snippet you use to get the individual column header and we'll go from there.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[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()