Thread: excel
View Single Post
  #2 (permalink)  
Old July 18th, 2013, 08:05 AM
john.glenn965 john.glenn965 is offline
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re: excel

Hi, to me the easiest way would be to export Excel to DataGridView like this:
Code:
' Load Excel (XLSX) from a file.
Dim workbook = ExcelFile.Load("Workbook.xlsx")

' Export active worksheet to DataGridView.
DataGridViewConverter.ExportToDataGridView(
    workbook.Worksheets.ActiveWorksheet,
    dataGridView,
    New ExportToDataGridViewOptions() With {.ColumnHeaders = True})
Also here is how to import Excel sheets to a DataSet, every sheet is a new DataTable:
Code:
Dim dataSet = New DataSet()

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

' Export all worksheets to DataSet.
For Each worksheet In workbook.Worksheets
    ' 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
        })
    dataTable.TableName = worksheet.Name

    ' Add DataTable to DataSet.
    dataSet.Tables.Add(dataTable)
Next