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