Create DataTable from Excel in C# and VB.NET

The following sample demonstrates how to create DataTable from a specific cell range in the Excel worksheet in C# and VB.NET.

The sample also demonstrates some of the CreateDataTableOptions members used for specifying options for creating DataTable and filling it with values from ExcelWorksheet.

The DataTable column types are determined based on a value of a CreateDataTableOptions.Resolution property.

Screenshot

Create DataTable Screenshot

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

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        // Select the first worksheet from the file.
16        ExcelWorksheet ws = ef.Worksheets[0];
17
18        // Create DataTable from an Excel worksheet.
19        DataTable dataTable = ws.CreateDataTable(new CreateDataTableOptions()
20        {
21            ColumnHeaders = true,
22            StartRow = 1,
23            NumberOfColumns = 5,
24            NumberOfRows = ws.Rows.Count - 1,
25            Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
26        });
27
28        // Write DataTable content
29        StringBuilder sb = new StringBuilder();
30        sb.AppendLine("DataTable content:");
31        foreach (DataRow row in dataTable.Rows)
32        {
33            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
34            sb.AppendLine();
35        }
36
37        Console.WriteLine(sb.ToString());
38    }
39}
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        ' Select the first worksheet from the file.
15        Dim ws = ef.Worksheets(0)
16
17        ' Create DataTable from an Excel worksheet.
18        Dim dataTable As DataTable = ws.CreateDataTable(New CreateDataTableOptions() With _
19         {
20          .ColumnHeaders = True, _
21          .StartRow = 1, _
22          .NumberOfColumns = 5, _
23          .NumberOfRows = ws.Rows.Count - 1,
24          .Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
25         })
26
27        ' Write DataTable content
28        Dim sb = New StringBuilder()
29        sb.AppendLine("DataTable content:")
30        For Each row As DataRow In dataTable.Rows
31            sb.AppendFormat("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}" & vbTab & "{4}", row(0), row(1), row(2), row(3), row(4))
32            sb.AppendLine()
33        Next
34
35        Console.WriteLine(sb.ToString())
36
37    End Sub
38
39End Module

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