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

The following example demonstrates how to export 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 exporting data from DataTable to ExcelWorksheet. Set the ColumnHeaders property to true to insert DataTable column names above the data.

Screenshot

Export DataTable to Excel 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("DataTable to Sheet");
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("DataTable to Sheet.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("DataTable to Sheet")
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("DataTable to Sheet.xls")
39
40    End Sub
41
42End Module

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