Export Excel sheet into DataTable in C# and VB.NET

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

DataTable must have all columns defined (with correct types) before calling ExtractToDataTable method.

The example also shows some of the ExtractToDataTableOptions members used for specifying options for exporting data from ExcelWorksheet to DataTable.

DataTable data exported from Excel worksheet with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

using System;
using System.Data;
using System.Text;
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%");

        var dataTable = new DataTable();

        // Depending on the format of the input file, you need to change this:
        dataTable.Columns.Add("FirstColumn", typeof(string));
        dataTable.Columns.Add("SecondColumn", typeof(string));

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

        var options = new ExtractToDataTableOptions(0, 0, 10);
        options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow;
        options.ExcelCellToDataTableCellConverting += (sender, e) =>
        {
            if (!e.IsDataTableValueValid)
            {
                // GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; 
                // someone would expect the number 12.4 as "12.4" and someone else as "12,4".
                e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString();
                e.Action = ExtractDataEventAction.Continue;
            }
        };

        // Extract the data from an Excel worksheet to the DataTable.
        // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
        worksheet.ExtractToDataTable(dataTable, options);

        // Write DataTable content.
        var sb = new StringBuilder();
        sb.AppendLine("DataTable content:");
        foreach (DataRow row in dataTable.Rows)
        {
            sb.AppendFormat("{0}    {1}", row[0], row[1]);
            sb.AppendLine();
        }

        Console.WriteLine(sb.ToString());
    }
}

Imports System
Imports System.Data
Imports System.Text
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%")

        Dim dataTable = New DataTable

        ' Depending on the format of the input file, you need to change this:
        dataTable.Columns.Add("FirstColumn", GetType(String))
        dataTable.Columns.Add("SecondColumn", GetType(String))

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

        Dim options = New ExtractToDataTableOptions(0, 0, 10)
        options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
        AddHandler options.ExcelCellToDataTableCellConverting,
            Sub(sender, e)
                If Not e.IsDataTableValueValid Then

                    ' GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; 
                    ' someone would expect the number 12.4 as "12.4" and someone else as "12,4".
                    e.DataTableValue = If(e.ExcelCell.Value Is Nothing, Nothing, e.ExcelCell.Value.ToString())
                    e.Action = ExtractDataEventAction.Continue
                End If
            End Sub

        ' Extract the data from an Excel worksheet to the DataTable.
        ' Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
        worksheet.ExtractToDataTable(dataTable, options)

        ' Write DataTable content.
        Dim sb = New StringBuilder
        sb.AppendLine("DataTable content:")
        For Each row As DataRow In dataTable.Rows

            sb.AppendFormat("{0}    {1}", row(0), row(1))
            sb.AppendLine()
        Next

        Console.WriteLine(sb.ToString())
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: September 8, 2021 | Author: Damir Stipinovic