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

The following example demonstrates how to 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 demonstrates 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

Upload your file (Drag file here)

Input file name

C#
VB.NET

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

class Program
{
    static void Main(string[] args)
    {
        // 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

Check next example or download examples from GitHub.