DataTable Export

The following examples show how to export a DataTable object to an Excel sheet or a DataSet object to an Excel file in C# and VB.NET. For that, you will use the GemBox.Spreadsheet library that supports the most popular spreadsheet file formats (XLS, XLSX, ODS, CSV).

Export DataTable to an Excel sheet

DataTables provide a way of storing and manipulating tabular data in rows and columns. They are commonly used for organizing and working with structured data in applications, supporting features such as sorting, filtering, and data binding to user interface controls.

To insert a DataTable in an Excel sheet, call the InsertDataTable method. You can use the InsertDataTableOptions to customize the export of data from a DataTable to anExcelWorksheet such as:

ColumnHeaders - Use it to specify whether to insert column headers in the output, or not.
StartRow and StartColumn - Define the position where to insert DataTable.
DataTableCellToExcelCellConverting - An event that fires when converting a DataTable cell value to an ExcelCell value allowing you to apply settings like cell style, format, and similar.

Try exporting a DataTable online

You can test exporting a DataTable to an Excel file with the interactive example below. Apart from Excel, you can choose other output file formats, supported by the GemBox.Spreadsheet component.

The example creates a DataTable object, fills it with data, inserts it into an ExcelFile instance and saves it to Excel, or to a file format of your choice. After clicking on Run example, the C#/VB.NET code will be compiled and executed with only .NET framework and the GemBox.Spreadsheet component.

C#
VB.NET

View on GitHub

using System.Data;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("DataTable to Sheet");

        var dataTable = new DataTable();

        dataTable.Columns.Add("ID", typeof(int));
        dataTable.Columns.Add("FirstName", typeof(string));
        dataTable.Columns.Add("LastName", typeof(string));

        dataTable.Rows.Add(new object[] { 100, "John", "Doe" });
        dataTable.Rows.Add(new object[] { 101, "Fred", "Nurk" });
        dataTable.Rows.Add(new object[] { 103, "Hans", "Meier" });
        dataTable.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
        dataTable.Rows.Add(new object[] { 105, "Jean", "Dupont" });
        dataTable.Rows.Add(new object[] { 106, "Mario", "Rossi" });

        worksheet.Cells[0, 0].Value = "DataTable insert example:";

        // Insert DataTable to an Excel worksheet.
        worksheet.InsertDataTable(dataTable,
            new InsertDataTableOptions()
            {
                ColumnHeaders = true,
                StartRow = 2
            });

        workbook.Save("DataTable to Sheet.%OutputFileType%");
    }
}

Imports System.Data
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = New ExcelFile
        Dim worksheet = workbook.Worksheets.Add("DataTable to Sheet")

        Dim dataTable = New DataTable

        dataTable.Columns.Add("ID", Type.GetType("System.Int32"))
        dataTable.Columns.Add("FirstName", Type.GetType("System.String"))
        dataTable.Columns.Add("LastName", Type.GetType("System.String"))

        dataTable.Rows.Add(New Object() {100, "John", "Doe"})
        dataTable.Rows.Add(New Object() {101, "Fred", "Nurk"})
        dataTable.Rows.Add(New Object() {103, "Hans", "Meier"})
        dataTable.Rows.Add(New Object() {104, "Ivan", "Horvat"})
        dataTable.Rows.Add(New Object() {105, "Jean", "Dupont"})
        dataTable.Rows.Add(New Object() {106, "Mario", "Rossi"})

        worksheet.Cells(0, 0).Value = "DataTable insert example:"

        ' Insert DataTable to an Excel worksheet.
        worksheet.InsertDataTable(dataTable,
            New InsertDataTableOptions() With
            {
                .ColumnHeaders = True,
                .StartRow = 2
            })

        workbook.Save("DataTable to Sheet.%OutputFileType%")
    End Sub
End Module

DataTable data to Excel sheet with GemBox.Spreadsheet — Screenshot of a DataTable exported to an Excel sheet with GemBox.Spreadsheet

Export DataTable performance

GemBox.Spreadsheet, being fully written in C#, utilizes caching techniques to provide optimal speed and memory performance. This makes this component ideal for generating big reports or working with large datasets. Your users won't need to wait long for their reports to be generated anymore. With GemBox.Spreadsheet users can read and write 1,000,000 rows within 4 seconds and with less than 256MB of RAM.. You can try the performance yourself with the Performance example.

Export DataSet to Excel file

The following example shows how to export a DataSet object to an Excel file in C# or VB.NET.

The process is similar to exporting a DataTable to a sheet. The only difference is that you need to iterate through every DataTable in a DataSet. For each DataTable, you can create an empty sheet, and then insert the DataTable into the sheet.

Try exporting DataSet online

You can try exporting a DataSet into an Excel file using the interactive example below. Apart from Excel, you can choose other output file formats, supported by the GemBoxSpreadsheet library.

The example creates a DataSet object, fills it with data, inserts it into an ExcelFile instance, and saves it to Excel , or file format of your choice. After clicking on Run example, the C#/VB.NET code will be compiled and executed with only .NET framework and the GemBox.Spreadsheet library.

C#
VB.NET

View on GitHub

using System.Data;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If you are using the Professional version, enter your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Create test DataSet with five DataTables
        DataSet dataSet = new DataSet();
        for (int i = 0; i < 5; i++)
        {
            DataTable dataTable = new DataTable("Table " + (i + 1));
            dataTable.Columns.Add("ID", typeof(int));
            dataTable.Columns.Add("FirstName", typeof(string));
            dataTable.Columns.Add("LastName", typeof(string));

            dataTable.Rows.Add(new object[] { 100, "John", "Doe" });
            dataTable.Rows.Add(new object[] { 101, "Fred", "Nurk" });
            dataTable.Rows.Add(new object[] { 103, "Hans", "Meier" });
            dataTable.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
            dataTable.Rows.Add(new object[] { 105, "Jean", "Dupont" });
            dataTable.Rows.Add(new object[] { 106, "Mario", "Rossi" });

            dataSet.Tables.Add(dataTable);
        }

        // Create and fill a sheet for every DataTable in a DataSet
        var workbook = new ExcelFile();
        foreach (DataTable dataTable in dataSet.Tables)
        {
            ExcelWorksheet worksheet = workbook.Worksheets.Add(dataTable.TableName);

            // Insert DataTable to an Excel worksheet.
            worksheet.InsertDataTable(dataTable,
                new InsertDataTableOptions()
                {
                    ColumnHeaders = true
                });
        }

        workbook.Save("DataSet to Excel file.%OutputFileType%");
    }
}

Imports System.Data
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        ' Create test DataSet with five DataTables
        Dim dataSet = New DataSet
        For value As Integer = 0 To 4
            Dim dataTable = New DataTable

            dataTable.Columns.Add("ID", Type.GetType("System.Int32"))
            dataTable.Columns.Add("FirstName", Type.GetType("System.String"))
            dataTable.Columns.Add("LastName", Type.GetType("System.String"))

            dataTable.Rows.Add(New Object() {100, "John", "Doe"})
            dataTable.Rows.Add(New Object() {101, "Fred", "Nurk"})
            dataTable.Rows.Add(New Object() {103, "Hans", "Meier"})
            dataTable.Rows.Add(New Object() {104, "Ivan", "Horvat"})
            dataTable.Rows.Add(New Object() {105, "Jean", "Dupont"})
            dataTable.Rows.Add(New Object() {106, "Mario", "Rossi"})

            dataSet.Tables.Add(dataTable)
        Next

        ' Create and fill a sheet for every DataTable in a DataSet
        Dim workbook = New ExcelFile
        For Each dataTable As DataTable In dataSet.Tables
            Dim worksheet = workbook.Worksheets.Add(dataTable.TableName)

            ' Insert DataTable to an Excel worksheet.
            worksheet.InsertDataTable(dataTable,
                New InsertDataTableOptions() With
                {
                    .ColumnHeaders = True
                })
        Next

        workbook.Save("DataSet to Excel file.%OutputFileType%")
    End Sub
End Module

DataSet to Excel file with GemBox.Spreadsheet — Screenshot of DataSet exported to an Excel file with GemBox.Spreadsheet

Supported Excel file formats

GemBox.Spreadsheet allows you to write to various file formats using the same API:

XLSX
XLS
PDF
HTML
MHTML
ODS
CSV
TXT
XLSM
XLTX
XLTM
XPS
PNG
JPEG
GIF
BMP
TIFF
WMP

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy