Create DataTable from Excel sheet in C# and VB.NET

The following example demonstrates how to create DataTable from a specific cell range in the Excel worksheet in C# and VB.NET.

The example also demonstrates 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.

Create DataTable from Excel Sheet Screenshot — Screenshot

See the full code below, use Run Example to execute.

C#
VB.NET

using System.Data;
using System.Text;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");

        // Select the first worksheet from the file.
        ExcelWorksheet ws = ef.Worksheets[0];

        // Create DataTable from an Excel worksheet.
        DataTable dataTable = ws.CreateDataTable(new CreateDataTableOptions()
        {
            ColumnHeaders = true,
            StartRow = 1,
            NumberOfColumns = 5,
            NumberOfRows = ws.Rows.Count - 1,
            Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
        });

        // Write DataTable content
        StringBuilder 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.Data
Imports System.Text
Imports GemBox.Spreadsheet

Module Samples

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")

        ' Select the first worksheet from the file.
        Dim ws = ef.Worksheets(0)

        ' Create DataTable from an Excel worksheet.
        Dim dataTable As DataTable = ws.CreateDataTable(New CreateDataTableOptions() With _
         {
          .ColumnHeaders = True, _
          .StartRow = 1, _
          .NumberOfColumns = 5, _
          .NumberOfRows = ws.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

Check next sample.