Export Excel sheet into DataTable in C# and VB.NET
The following example shows how you can export or extract data from a specific cell range in the Excel worksheet to a DataTable object in C# and VB.NET.
DataTable must have all columns defined (with correct types) before calling ExtractToDataTable method.
The example also shows some of the ExtractToDataTableOptions members used for specifying options for exporting data from ExcelWorksheet to DataTable.
using System;
using System.Data;
using System.Text;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
var dataTable = new DataTable();
// Depending on the format of the input file, you need to change this:
dataTable.Columns.Add("FirstColumn", typeof(string));
dataTable.Columns.Add("SecondColumn", typeof(string));
// Select the first worksheet from the file.
var worksheet = workbook.Worksheets[0];
var options = new ExtractToDataTableOptions(0, 0, 10);
options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow;
options.ExcelCellToDataTableCellConverting += (sender, e) =>
{
if (!e.IsDataTableValueValid)
{
// GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues;
// someone would expect the number 12.4 as "12.4" and someone else as "12,4".
e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString();
e.Action = ExtractDataEventAction.Continue;
}
};
// Extract the data from an Excel worksheet to the DataTable.
// Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
worksheet.ExtractToDataTable(dataTable, options);
// Write DataTable content.
var sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dataTable.Rows)
{
sb.AppendFormat("{0} {1}", row[0], row[1]);
sb.AppendLine();
}
Console.WriteLine(sb.ToString());
}
}Imports System
Imports System.Data
Imports System.Text
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
Dim dataTable = New DataTable
' Depending on the format of the input file, you need to change this:
dataTable.Columns.Add("FirstColumn", GetType(String))
dataTable.Columns.Add("SecondColumn", GetType(String))
' Select the first worksheet from the file.
Dim worksheet = workbook.Worksheets(0)
Dim options = New ExtractToDataTableOptions(0, 0, 10)
options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
AddHandler options.ExcelCellToDataTableCellConverting,
Sub(sender, e)
If Not e.IsDataTableValueValid Then
' GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues;
' someone would expect the number 12.4 as "12.4" and someone else as "12,4".
e.DataTableValue = If(e.ExcelCell.Value Is Nothing, Nothing, e.ExcelCell.Value.ToString())
e.Action = ExtractDataEventAction.Continue
End If
End Sub
' Extract the data from an Excel worksheet to the DataTable.
' Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
worksheet.ExtractToDataTable(dataTable, options)
' Write DataTable content.
Dim sb = New StringBuilder
sb.AppendLine("DataTable content:")
For Each row As DataRow In dataTable.Rows
sb.AppendFormat("{0} {1}", row(0), row(1))
sb.AppendLine()
Next
Console.WriteLine(sb.ToString())
End Sub
End Module