StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code> Public Shared Function ExcelToDataTable(filename As String) As DataTable Try Dim dt As New DataTable() Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False) Dim workbookPart As WorkbookPart = doc.WorkbookPart Dim sheets As IEnumerable(Of Sheet) = doc.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)() Dim relationshipId As String = sheets.First().Id.Value Dim worksheetPart As WorksheetPart = DirectCast(doc.WorkbookPart.GetPartById(relationshipId), WorksheetPart) Dim workSheet As Worksheet = worksheetPart.Worksheet Dim sheetData As SheetData = workSheet.GetFirstChild(Of SheetData)() Dim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)() For Each cell As Cell In rows.ElementAt(0) dt.Columns.Add(GetCellValue(doc, cell)) Next For Each row As Row In rows 'this will also include your header row... Dim tempRow As DataRow = dt.NewRow() For i As Integer = 0 To row.Descendants(Of Cell)().Count() - 1 tempRow(i) = GetCellValue(doc, row.Descendants(Of Cell)().ElementAt(i)) Next dt.Rows.Add(tempRow) Next End Using dt.Rows.RemoveAt(0) Return dt Catch ex As Exception Throw ex End Try End Function Public Shared Function GetCellValue(document As SpreadsheetDocument, cell As Cell) As String Try If IsNothing(cell.CellValue) Then Return "" End If Dim value As String = cell.CellValue.InnerXml If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then Dim stringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart Return stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText Else Return value End If Catch ex As Exception Return "" End Try End Function </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload