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

Hi,

How do you save data from an excel file to a datatable using VB.NET?


Thanks,

Victor
vcharlesAsked:
Who is Participating?
 
Lokesh B RDeveloperCommented:
Hi,

Check each column name and replace with your new value and so on...

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 = "AGD"
		End If

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

		End If
	Next
End If

Open in new window

0
 
BlueYonderCommented:
' 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
0
 
vcharlesAuthor Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vcharlesAuthor Commented:
Hi again,

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

Victor
0
 
Lokesh B RDeveloperCommented:
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

Open in new window

0
 
vcharlesAuthor Commented:
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
0
 
Lokesh B RDeveloperCommented:
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

Open in new window

0
 
vcharlesAuthor Commented:
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
0
 
vcharlesAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.