Insert or Import DataTable into Excel in C# and VB.NET

The following sample demonstrates how to insert or import a DataTable object into an Excel file with C# and VB.NET at the specific cell range in the Excel worksheet.

Use InsertDataTableOptions to set various options for inserting data from DataTable to ExcelWorksheet. Set the ColumnHeaders property to true to insert DataTable column names above the data.

Screenshot

Insert DataTable Screenshot

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


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 = new ExcelFile();
14        ExcelWorksheet ws = ef.Worksheets.Add("Insert DataTable");
15
16        DataTable dt = new DataTable();
17
18        dt.Columns.Add("ID", typeof(int));
19        dt.Columns.Add("FirstName", typeof(string));
20        dt.Columns.Add("LastName", typeof(string));
21
22        dt.Rows.Add(new object[] { 100, "John", "Doe" });
23        dt.Rows.Add(new object[] { 101, "Fred", "Nurk" });
24        dt.Rows.Add(new object[] { 103, "Hans", "Meier" });
25        dt.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
26        dt.Rows.Add(new object[] { 105, "Jean", "Dupont" });
27        dt.Rows.Add(new object[] { 106, "Mario", "Rossi" });
28
29        ws.Cells[0, 0].Value = "DataTable insert example:";
30
31        // Insert DataTable into an Excel worksheet.
32        ws.InsertDataTable(dt,
33            new InsertDataTableOptions()
34            {
35                ColumnHeaders = true,
36                StartRow = 2
37            });
38
39        ef.Save("Insert DataTable.xls");
40    }
41}
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 = New ExcelFile
13        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Insert DataTable")
14
15        Dim dt As DataTable = New DataTable
16
17        dt.Columns.Add("ID", Type.GetType("System.Int32"))
18        dt.Columns.Add("FirstName", Type.GetType("System.String"))
19        dt.Columns.Add("LastName", Type.GetType("System.String"))
20
21        dt.Rows.Add(New Object() {100, "John", "Doe"})
22        dt.Rows.Add(New Object() {101, "Fred", "Nurk"})
23        dt.Rows.Add(New Object() {103, "Hans", "Meier"})
24        dt.Rows.Add(New Object() {104, "Ivan", "Horvat"})
25        dt.Rows.Add(New Object() {105, "Jean", "Dupont"})
26        dt.Rows.Add(New Object() {106, "Mario", "Rossi"})
27
28        ws.Cells(0, 0).Value = "DataTable insert example:"
29
30        ' Insert DataTable into an Excel worksheet.
31        ws.InsertDataTable(dt, _
32            New InsertDataTableOptions() With _
33            {
34                .ColumnHeaders = True,
35                .StartRow = 2
36            })
37
38        ef.Save("Insert DataTable.xls")
39
40    End Sub
41
42End Module

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