DataTable Import

GemBox.Spreadsheet allows you to programmatically import your Excel sheet to an existing DataTable object. The following example shows how to import data from a specific cell range in the Excel worksheet to a DataTable object using the ExtractToDataTable method in C# and VB.NET.

Upload your file (Drag file here)
using GemBox.Spreadsheet;
using System;
using System.Data;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%InputFileName%");

        // Create DataTable with specified columns.
        var dataTable = new DataTable();
        dataTable.Columns.Add("First_Column", typeof(string));
        dataTable.Columns.Add("Second_Column", typeof(string));
        dataTable.Columns.Add("Third_Column", typeof(int));
        dataTable.Columns.Add("Fourth_Column", typeof(double));

        // Select the first worksheet from the file.
        var worksheet = workbook.Worksheets[0];

        // Extract the data from an Excel worksheet to the DataTable.
        var options = new ExtractToDataTableOptions(0, 0, 20);
        options.ExcelCellToDataTableCellConverting += (sender, e) =>
        {
            if (!e.IsDataTableValueValid)
            {
                // Convert ExcelCell value to string.
                if (e.DataTableColumnType == typeof(string))
                    e.DataTableValue = e.ExcelCell.Value?.ToString();
                else
                    e.DataTableValue = DBNull.Value;
            }
        };
        worksheet.ExtractToDataTable(dataTable, options);

        // 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 GemBox.Spreadsheet
Imports System
Imports System.Data

Module Program

    Sub Main()

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

        Dim workbook = ExcelFile.Load("%InputFileName%")

        ' Create DataTable with specified columns.
        Dim dataTable As New DataTable()
        dataTable.Columns.Add("First_Column", GetType(String))
        dataTable.Columns.Add("Second_Column", GetType(String))
        dataTable.Columns.Add("Third_Column", GetType(Integer))
        dataTable.Columns.Add("Fourth_Column", GetType(Double))

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

        ' Extract the data from an Excel worksheet to the DataTable.
        Dim options As New ExtractToDataTableOptions(0, 0, 20)
        AddHandler options.ExcelCellToDataTableCellConverting,
            Sub(sender, e)
                If Not e.IsDataTableValueValid Then
                    ' Convert ExcelCell value to string.
                    e.DataTableValue = If(e.DataTableColumnType = GetType(String),
                        e.ExcelCell.Value?.ToString(),
                        DBNull.Value)
                End If
            End Sub
        worksheet.ExtractToDataTable(dataTable, options)

        ' 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
Excel worksheet data exported to DataTable object
Screenshot of an Excel worksheet data imported to DataTable

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

Note, your DataTable should have predefined DataColumn objects with the correct data type. If it doesn't, you can handle the data type conversion with the ExtractToDataTableOptions.ExcelCellToDataTableCellConverting event.

Create DataTables from Excel sheets

It's also possible to create a new DataTable object from a specific cell range in the Excel sheet, as shown in the next example.

Upload your file (Drag file here)
using GemBox.Spreadsheet;
using System;
using System.Data;

class Program
{
    static void Main()
    {
        // If using the 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 GemBox.Spreadsheet
Imports System
Imports System.Data

Module Program

    Sub Main()

        ' If using the 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
DataTable object created from Excel worksheet
Screenshot of DataTable data created from Excel worksheet

You can use the CreateDataTableOptions object to specify the options for creating a DataTable. Note, the data type of DataColumn objects that GemBox.Spreadsheet will create is based on the value of the CreateDataTableOptions.Resolution property. The list of possible values can be found on this page.

See also


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