DataTable Import
GemBox.Spreadsheet allows you to programmatically import your Excel sheet to an existing DataTable object. The following example shows how to import data from a specific cell range in the Excel worksheet to a DataTable object using the ExtractToDataTable method in C# and VB.NET.
using GemBox.Spreadsheet;
using System;
using System.Data;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
// Create DataTable with specified columns.
var dataTable = new DataTable();
dataTable.Columns.Add("First_Column", typeof(string));
dataTable.Columns.Add("Second_Column", typeof(string));
dataTable.Columns.Add("Third_Column", typeof(int));
dataTable.Columns.Add("Fourth_Column", typeof(double));
// Select the first worksheet from the file.
var worksheet = workbook.Worksheets[0];
// Extract the data from an Excel worksheet to the DataTable.
var options = new ExtractToDataTableOptions(0, 0, 20);
options.ExcelCellToDataTableCellConverting += (sender, e) =>
{
if (!e.IsDataTableValueValid)
{
// Convert ExcelCell value to string.
if (e.DataTableColumnType == typeof(string))
e.DataTableValue = e.ExcelCell.Value?.ToString();
else
e.DataTableValue = DBNull.Value;
}
};
worksheet.ExtractToDataTable(dataTable, options);
// Write DataTable columns.
foreach (DataColumn column in dataTable.Columns)
Console.Write(column.ColumnName.PadRight(20));
Console.WriteLine();
foreach (DataColumn column in dataTable.Columns)
Console.Write($"[{column.DataType}]".PadRight(20));
Console.WriteLine();
foreach (DataColumn column in dataTable.Columns)
Console.Write(new string('-', column.ColumnName.Length).PadRight(20));
Console.WriteLine();
// Write DataTable rows.
foreach (DataRow row in dataTable.Rows)
{
foreach (object item in row.ItemArray)
{
string value = item.ToString();
value = value.Length > 20 ? value.Remove(19) + "…" : value;
Console.Write(value.PadRight(20));
}
Console.WriteLine();
}
}
}
Imports GemBox.Spreadsheet
Imports System
Imports System.Data
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
' Create DataTable with specified columns.
Dim dataTable As New DataTable()
dataTable.Columns.Add("First_Column", GetType(String))
dataTable.Columns.Add("Second_Column", GetType(String))
dataTable.Columns.Add("Third_Column", GetType(Integer))
dataTable.Columns.Add("Fourth_Column", GetType(Double))
' Select the first worksheet from the file.
Dim worksheet = workbook.Worksheets(0)
' Extract the data from an Excel worksheet to the DataTable.
Dim options As New ExtractToDataTableOptions(0, 0, 20)
AddHandler options.ExcelCellToDataTableCellConverting,
Sub(sender, e)
If Not e.IsDataTableValueValid Then
' Convert ExcelCell value to string.
e.DataTableValue = If(e.DataTableColumnType = GetType(String),
e.ExcelCell.Value?.ToString(),
DBNull.Value)
End If
End Sub
worksheet.ExtractToDataTable(dataTable, options)
' Write DataTable columns.
For Each column As DataColumn In dataTable.Columns
Console.Write(column.ColumnName.PadRight(20))
Next
Console.WriteLine()
For Each column As DataColumn In dataTable.Columns
Console.Write($"[{column.DataType}]".PadRight(20))
Next
Console.WriteLine()
For Each column As DataColumn In dataTable.Columns
Console.Write(New String("-"c, column.ColumnName.Length).PadRight(20))
Next
Console.WriteLine()
' Write DataTable rows.
For Each row As DataRow In dataTable.Rows
For Each item In row.ItemArray
Dim value As String = item.ToString()
value = If(value.Length > 20, value.Remove(19) & "…", value)
Console.Write(value.PadRight(20))
Next
Console.WriteLine()
Next
End Sub
End Module
You can use the ExtractToDataTableOptions object to specify the options for importing data to a DataTable. For example, you can specify the number of Excel rows or columns being extracted, the index of the first Excel row or column being extracted, etc.
Note, your DataTable should have predefined DataColumn objects with the correct data type. If it doesn't, you can handle the data type conversion with the ExtractToDataTableOptions.ExcelCellToDataTableCellConverting event.
Create DataTables from Excel sheets
It's also possible to create a new DataTable object from a specific cell range in the Excel sheet, as shown in the next example.
using GemBox.Spreadsheet;
using System;
using System.Data;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
// Select the first worksheet from the file.
var worksheet = workbook.Worksheets[0];
// Create DataTable from an Excel worksheet.
var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
{
ColumnHeaders = true,
StartRow = 1,
NumberOfColumns = 5,
NumberOfRows = worksheet.Rows.Count - 1,
Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
});
// Write DataTable columns.
foreach (DataColumn column in dataTable.Columns)
Console.Write(column.ColumnName.PadRight(20));
Console.WriteLine();
foreach (DataColumn column in dataTable.Columns)
Console.Write($"[{column.DataType}]".PadRight(20));
Console.WriteLine();
foreach (DataColumn column in dataTable.Columns)
Console.Write(new string('-', column.ColumnName.Length).PadRight(20));
Console.WriteLine();
// Write DataTable rows.
foreach (DataRow row in dataTable.Rows)
{
foreach (object item in row.ItemArray)
{
string value = item.ToString();
value = value.Length > 20 ? value.Remove(19) + "…" : value;
Console.Write(value.PadRight(20));
}
Console.WriteLine();
}
}
}
Imports GemBox.Spreadsheet
Imports System
Imports System.Data
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
' Select the first worksheet from the file.
Dim worksheet = workbook.Worksheets(0)
' Create DataTable from an Excel worksheet.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
{
.ColumnHeaders = True,
.StartRow = 1,
.NumberOfColumns = 5,
.NumberOfRows = worksheet.Rows.Count - 1,
.Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
})
' Write DataTable columns.
For Each column As DataColumn In dataTable.Columns
Console.Write(column.ColumnName.PadRight(20))
Next
Console.WriteLine()
For Each column As DataColumn In dataTable.Columns
Console.Write($"[{column.DataType}]".PadRight(20))
Next
Console.WriteLine()
For Each column As DataColumn In dataTable.Columns
Console.Write(New String("-"c, column.ColumnName.Length).PadRight(20))
Next
Console.WriteLine()
' Write DataTable rows.
For Each row As DataRow In dataTable.Rows
For Each item In row.ItemArray
Dim value As String = item.ToString()
value = If(value.Length > 20, value.Remove(19) & "�", value)
Console.Write(value.PadRight(20))
Next
Console.WriteLine()
Next
End Sub
End Module
You can use the CreateDataTableOptions object to specify the options for creating a DataTable. Note, the data type of DataColumn objects that GemBox.Spreadsheet will create is based on the value of the CreateDataTableOptions.Resolution property. The list of possible values can be found on this page.
