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.

Screenshot

Export or Extract Excel to DataTable Screenshot

See the full code below, use RUN EXAMPLE to execute.

Download input file
Upload your file (Drag files here) 
1using System.Data;
2using System.Text;
3using GemBox.Spreadsheet;
4
5class Sample
6{
7    [STAThread]
8    static void Main(string[] args)
9    {
10        // If using Professional version, put your serial key below.
11        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
12
13        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
14
15        DataTable dataTable = new DataTable();
16
17        // Depending on the format of the input file, you need to change this:
18        dataTable.Columns.Add("FirstColumn", typeof(string));
19        dataTable.Columns.Add("SecondColumn", typeof(string));
20
21        // Select the first worksheet from the file.
22        ExcelWorksheet ws = ef.Worksheets[0];
23
24        ExtractToDataTableOptions options = new ExtractToDataTableOptions(0, 0, 10);
25        options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow;
26        options.ExcelCellToDataTableCellConverting += (sender, e) =>
27        {
28            if (!e.IsDataTableValueValid)
29            {
30                // GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; 
31                // someone would expect the number 12.4 as "12.4" and someone else as "12,4".
32                e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString();
33                e.Action = ExtractDataEventAction.Continue;
34            }
35        };
36
37        // Extract the data from an Excel worksheet to the DataTable.
38        // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
39        ws.ExtractToDataTable(dataTable, options);
40
41        // Write DataTable content
42        StringBuilder sb = new StringBuilder();
43        sb.AppendLine("DataTable content:");
44        foreach (DataRow row in dataTable.Rows)
45        {
46            sb.AppendFormat("{0}    {1}", row[0], row[1]);
47            sb.AppendLine();
48        }
49
50        Console.WriteLine(sb.ToString());
51    }
52}

1Imports System.Data
2Imports System.Text
3Imports GemBox.Spreadsheet
4
5Module Samples
6
7    Sub Main()
8
9        ' If using Professional version, put your serial key below.
10        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")
13
14        Dim dataTable As New DataTable
15
16        ' Depending on the format of the input file, you need to change this:
17        dataTable.Columns.Add("FirstColumn", GetType(String))
18        dataTable.Columns.Add("SecondColumn", GetType(String))
19
20        ' Select the first worksheet from the file.
21        Dim ws As ExcelWorksheet = ef.Worksheets(0)
22
23        Dim options = New ExtractToDataTableOptions(0, 0, 10)
24        options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
25        AddHandler options.ExcelCellToDataTableCellConverting, _
26            Sub(sender, e)
27                If Not e.IsDataTableValueValid Then
28                    ' GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; 
29                    ' someone would expect the number 12.4 as "12.4" and someone else as "12,4".
30                    e.DataTableValue = If(e.ExcelCell.Value Is Nothing, Nothing, e.ExcelCell.Value.ToString())
31                    e.Action = ExtractDataEventAction.Continue
32                End If
33            End Sub
34
35        ' Extract the data from an Excel worksheet to the DataTable.
36        ' Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
37        ws.ExtractToDataTable(dataTable, options)
38
39        ' Write DataTable content
40        Dim sb As New StringBuilder
41        sb.AppendLine("DataTable content:")
42        For Each row As DataRow In dataTable.Rows
43            sb.AppendFormat("{0}    {1}", row(0), row(1))
44            sb.AppendLine()
45        Next
46
47        Console.WriteLine(sb.ToString())
48
49    End Sub
50
51End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.