I am updating a working webservice to work in azure. I was previously using ACE OleDB to pull data from an excel file but that isn't supported in Azure. So I am switching to OpenXML and can't quite find the functions I need. specifically, I can't seem to find my column headers. one "how-to" I found had me create a datatable then populate the datatable columns with values from rows 0:
'Fetch all the rows present in the Worksheet.
Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
'Create a new DataTable.
Dim dt As New DataTable()
for each cell as Cell in rows(0).Descendants(Of Cell)()
dt.Columns.Add(cell.CellValue.Text)
next
this makes sense to me, except openXML seems to recognize that my data is formatted with headers so rows(0) is actually giving me cells A2, B2, C2 etc. not A1, B1, C1 etc.
I found a microsoft doc that says I can get column heading or column name like this:
Dim cellName As String = "B2"
Dim s1 As String = GetColumnHeading(docName, worksheetName, cellName)
'or
Dim columnName As String = GetColumnName(cellName)
but I can't be sure how many columns I will get so I would need a way to iterate over an unknown number of cells without knowing the cell name, and both functions GetColumnHeading and GetColumnName are giving me undeclared errors so whatever implementation of OpenXML my Azure instance is using doesn't have those functions.
ideally I could just use it as if it was a datatable, but if you see anything I could fix to get this to work, please help. My preferred language is vb.net but I can translate from c# pretty well. I am new to OpenXML but use SQL and DataSets/DataTables all the time.
