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
excel
