GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

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.

Screenshot
Create DataTable from Excel Sheet Screenshot

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

Upload your file(Drag files here)

Download a sample file

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.