Export Excel sheets to DataTables in C# and VB.NET

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

The following example shows how you can export or extract data from a specific cell range in the Excel worksheet to a DataTable object by using the ExtractToDataTable method in C# and VB.NET.

You can use the ExtractToDataTableOptions object to specify the options for exporting 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.

Excel worksheet data exported to DataTable object — Screenshot of Excel worksheet data exported to DataTable

C#
VB.NET

View on GitHub

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

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

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

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: December 19, 2022 | Author: Damir Stipinovic

Export Excel sheets to DataTables in C# and VB.NET

See also

Next steps