Create DataTable from Excel sheet in C# and VB.NET
The following example shows how you can create DataTable
from a specific cell range in the Excel worksheet in C# and VB.NET.
The example also shows some of the CreateDataTableOptions
members used for specifying options for creating DataTable
and filling it with values from ExcelWorksheet
.
The DataTable
column types are determined based on a value of a CreateDataTableOptions.Resolution
property.

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%");
// 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 content
var sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dataTable.Rows)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
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%")
' 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 content
Dim sb = New StringBuilder()
sb.AppendLine("DataTable content:")
For Each row As DataRow In dataTable.Rows
sb.AppendFormat("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}" & vbTab & "{4}", row(0), row(1), row(2), row(3), row(4))
sb.AppendLine()
Next
Console.WriteLine(sb.ToString())
End Sub
End Module
Want more?
Like it?
Published: December 13, 2018 | Modified: November 20, 2020 | Author: Marko Kozlina