Create DataTables from Excel sheets in C# and VB.NET

With GemBox.Spreadsheet you can easily create a new DataTable object from an Excel sheet, or export the sheet to an existing DataTable (as shown in the Sheet to DataTable example).

You can use the CreateDataTableOptions object to specify the options for creating a DataTable. For example, you can set the number of Excel rows or columns being extracted, the index of the first Excel row or column being extracted, etc.

Note, the data type of DataColumn objects that GemBox.Spreadsheet will create is based on the value of the CreateDataTableOptions.Resolution property.

The following example shows how you can create a new DataTable from a specific cell range in the Excel worksheet using the CreateDataTable method in C# and VB.NET.

DataTable object created from Excel worksheet — Screenshot of DataTable data created from Excel worksheet

C#
VB.NET

View on GitHub

using System;
using System.Data;
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 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 System
Imports System.Data
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 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

Next steps

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.

Download Buy

Published: December 13, 2018 | Modified: June 1, 2022 | Author: Marko Kozlina

Create DataTables from Excel sheets in C# and VB.NET

See also

Next steps