Excel table to datatable

Question

I am trying to add an entire Excel table into a datatable and declaring variables like this:

workbook = APP.Workbooks.Open(fileString)
worksheet = workbook.Worksheets("Report")

It is able to read from the Excel file. I have checked using a message box:

MsgBox(worksheet.Cells(1, 1).Value)

This works but I can't find a method to add the values from the Excel file into my datatable:

Dim table As New DataTable

table.Columns.Add("DATE")
table.Columns.Add("VALUE")

Can anyone help me with this?

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

You can use some library to do it For example NPOI (example), or Epplus (example) Or use OleDb provider

edited May 23 '17 at 11:43

Community

1
1

answered Apr 16 '15 at 11:38

SalientBrain

2,431
16
18

score 1 · Answer 2 · answered Apr 16 '15 at 11:56

You can export a excel in to datatable using oledb

NOTE:

since we are going to use oledb connection we have to Imports System.Data.OleDb

Try the following code

Dim dt As New DataTable
        dt.Columns.Add("date")
        dt.Columns.Add("value")
        Dim dr As DataRow = dt.NewRow()
        Time = System.DateTime.Now
        Try
            XLDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\name.xls;Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""") 'give your file path in source
        Catch ex As Exception
            Throw (ex)
            Exit Sub
        End Try
        XLDbConnection.Open()
        Try
            XLDbCommand = New OleDbCommand("select * from [sheet1$]", XLDbConnection) 'sheet1 is name of the excel sheet
            XLDbDataReader = XLDbCommand.ExecuteReader
        Catch ex As Exception

            Throw (ex)
            Exit Sub
        End Try
        If XLDbDataReader.HasRows = True Then
            'Assign excel data to datacolumn of our datatable
            While XLDbDataReader.Read
                Try
                    dr("date") = XLDbDataReader.Item("date").ToString() 'column name as it is in excel file 
                    dr("value") = XLDbDataReader.Item("value").ToString()
                    dt.Rows.Add(dr)
                Catch ex As Exception
                    Throw (ex)
                End Try
            End While
        End If

hope this helps

Excel table to datatable

2 Answers2