This project has moved. For the latest updates, please go here.

How does one read from a spreadsheet to a Datatable?

Jan 31, 2012 at 7:26 PM

I have a scenario where I am exporting information from a datatable to a spreadsheet. Later on, I want to import from the spreadsheet to the datatable, and eventually into a database. I couldn't find any documentation on this. Does EPPlus have an efficient way of doing this?

Jan 31, 2012 at 7:55 PM
Edited Jan 31, 2012 at 8:13 PM

No, there is yet no direct way to get a DataTable from an excel spreadsheet. What datatype you would choose for all that string values or how you would check whether it contains a header-row or not?

Here is an approach that you can modify or extend for your requirement:

 

        Dim pck = New OfficeOpenXml.ExcelPackage()
        pck.Load(New IO.FileInfo(path).OpenRead)
        If pck.Workbook.Worksheets.Count <> 0 Then
            Dim ws = pck.Workbook.Worksheets.First
            Dim tbl As New DataTable
            Dim hasHeader = False ' adjust accordingly '
            For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
                tbl.Columns.Add(
                    If(hasHeader,
                       firstRowCell.Text,
                       String.Format("Column {0}", firstRowCell.Start.Column)))
            Next
            Dim startRow = If(hasHeader, 2, 1)
            For rowNum = startRow To ws.Dimension.End.Row
                Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
                tbl.Rows.Add(wsRow.Select(Function(cell) cell.Text).ToArray)
            Next
End If
Jan 31, 2012 at 8:34 PM

Thanks. I will let you know if it works.