Help with saving excel file to a datatable using VB.NET

' Load Excel file.
Dim workbook = ExcelFile.Load("Workbook.xls")

' Select active worksheet from the file.
Dim worksheet = workbook.Worksheets.ActiveWorksheet

' Extract the data from the worksheet to newly created DataTable starting at
' first row and first column for 10 rows or until the first empty row appears.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
    {
        .StartRow = 0,
        .StartColumn = 0,
        .NumberOfRows = 10,
        .ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
    })

' Change the value of the first cell in the DataTable.
dataTable.Rows(0)(0) = "Hello world!"

' Insert the data from DataTable to the worksheet starting at cell "A1".
worksheet.InsertDataTable(dataTable,
    New InsertDataTableOptions("A1") With {.ColumnHeaders = True})

' Save the file to XLS format.
workbook.Save("DataTable.xls")

The instructions are from http://www.gemboxsoftware.com/support/articles/import-export-datatable-xls-xlsx-ods-csv-html-net

Hi,
Thanks for the code. think I only need code up to })

Bottom part of code seem to copy data back to excel.

Will try it a d get back to you.
Victor

Hi again,

How do you change the colum names after you import the data to the table?

Victor

Hi,

To change the column names try this code

If dataTable IsNot Nothing AndAlso dataTable.Columns.Count > 0 Then
	For Each col As DataColumn In dataTable.Columns
		If col.ColumnName = "Column1" Then
			col.ColumnName = "Employee Name"
		End If

		If col.ColumnName = "Column2" Then
			col.ColumnName = "Address"

		End If
	Next
End If

Select allOpen in new window

Hi,

Thanks for the code. Is there a way to change them by putting the new names in a string? (new1,new2,new3,new4) where new1 would replace name of first column and so on.

Thanks,

Victor

Hi,

try this

If dataTable IsNot Nothing AndAlso dataTable.Columns.Count > 0 Then
	For i As Integer = 0 To dataTable.Columns.Count - 1
		dataTable.Columns(i).ColumnName = "new" & (i + 1)
	Next
End If

Select allOpen in new window

Hi,

Sorry for for not being clear, new1, new2 etc.. was just an example the new names will be very different, for example AGD, NSC, NAS.

Thanks,

Victor

Thank you.