Export a DataTable or DataSet to an Excel file in C# and VB.NET

The following examples shows how you can export a DataTable object to an Excel sheet or a DataSet object to an Excel file in C# and VB.NET.

GemBox.Spreadsheet uses the same API for all spreadsheet file formats (XLS, XLSX, ODS, CSV), so you can use the same code for filling customer files or your templates of various file formats with different data sources, such as DataTable or DataSet.

Export DataTable to Excel sheet

To insert a DataTable in an Excel sheet simply call the InsertDataTable method. You can use the InsertDataTableOptions to set various options for exporting data from DataTable to ExcelWorksheet such as:

  • ColumnHeaders - Defines whether to insert column headers.
  • 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 which you can use to apply settings such as cell style.

Try exporting a DataTable online

You can test inserting a DataTable to an empty file with the interactive example below. Just choose an output file format, click Run Example, and download the generated file.

The example creates a DataTable, fills it with data, inserts it into an Excel file and saves The Excel file 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.

DataTable data to Excel sheet with GemBox.Spreadsheet
Screenshot of DataTable exported to Excel sheet with GemBox.Spreadsheet
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

Export DataTable performance

Since GemBox.Spreadsheet is completely written in C# and uses caching techniques, it offers both speed and memory optimizations. Your users will not need to wait for their reports to be generated any more. With GemBox.Spreadsheet you can read and write 1,000,000 rows with less than 256MB RAM and less than 4 seconds. You can try the performance yourself with the Performance example.

Export DataSet to Excel file

The following example shows how you can 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 and for each DataTable you should first create an empty sheet, and after that you should insert the DataTable into the sheet.

Try exporting DataSet online

You can test inserting a DataSet to an empty file with the interactive example below. Just choose an output file format, click Run Example, and download the generated file.

The example creates a DataSet, fills it with data, inserts it into an Excel file and saves The Excel file 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.

DataSet to Excel file with GemBox.Spreadsheet
Screenshot of DataSet exported to Excel file with GemBox.Spreadsheet
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

Supported Excel file formats

With GemBox.Spreadsheet you can also write the following file formats using the same API: XLSX, XLS, PDF, HTML, MHTML, ODS, CSV, TXT, XLSM, XLTX, XLTM, XPS, PNG, JPEG, GIF, BMP, TIFF, WMP.

See also


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