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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
vcharlesAuthor Commented:
Hi again,

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

Victor
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.