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.

Export or Extract Excel to DataTable Screenshot — Screenshot

See the full code below, use Run Example to execute.

C#
VB.NET

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

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");

        DataTable 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.
        ExcelWorksheet ws = ef.Worksheets[0];

        ExtractToDataTableOptions 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.
        ws.ExtractToDataTable(dataTable, options);

        // Write DataTable content
        StringBuilder 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.Data
Imports System.Text
Imports GemBox.Spreadsheet

Module Samples

    Sub Main()

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

        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")

        Dim dataTable As 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 ws As ExcelWorksheet = ef.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.
        ws.ExtractToDataTable(dataTable, options)

        ' Write DataTable content
        Dim sb As 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 sample.