Import or Export DataSet to XLS, XLSX, CSV or HTML Files from your .NET applications

FAQ

Help

Bugs & Features

Articles

How to Import or Export DataSet to Excel or HTML files from your .NET applications?

Following example shows how to import or export DataSet to Excel or HTML files by using only GemBox.Spreadsheet .NET component.

GemBox.Spreadsheet is a .NET component which provides easy and high performance way to write, read or convert native Microsoft Excel files (XLS, CSV or XLSX) and HTML files without the need for Microsoft Excel on either the developer or client machines. GemBox.Spreadsheet Free comes free of charge while GemBox.Spreadsheet Professional is a commercial version (licensed per developer). Find more information about GemBox.Spreadsheet features or reasons why our component is better then Excel Automation.

Example imports/exports DataSet to Excel file (in XLS format) by directly working with DataSet using InsertDataTable and ExtractToDataTable methods:

C# code:

ExcelFile ef = new ExcelFile();
DataSet dataSet = new DataSet();

// Load Excel file.
ef.LoadXls("FileName.xls");

// Export all worksheets to DataSet.
foreach (ExcelWorksheet ws in ef.Worksheets)
{
    DataTable dataTable = new DataTable();

    // Depending on the format of the input file, you need to change this:
    dataTable.Columns.Add("FirstName", typeof(string));
    dataTable.Columns.Add("LastName", typeof(string));

    // Extract the data from the worksheet to the DataTable.
    ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    dataTable.TableName = ws.Name;

    // Add DataTable to DataSet.
    dataSet.Tables.Add(dataTable);
}

// Create new ExcelFile.
ExcelFile ef2 = new ExcelFile();

// Imports all the tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
    // Add new worksheet to the file.
    ExcelWorksheet ws = ef2.Worksheets.Add(dataTable.TableName);

    // Change the value of the first cell in the DataTable.
    dataTable.Rows[0][0] = "This is new file!";

    // Insert the data from DataTable to the worksheet starting at cell "A1".
    ws.InsertDataTable(dataTable, "A1", true);
}

// Save the file to XLS format.
ef2.SaveXls("DataSet.xls");

Visual Basic .NET code:

Dim ef As New ExcelFile
Dim dataSet As New DataSet

' Load Excel file.
ef.LoadXls("FileName.xls")

' Export all worksheets to DataSet.
For Each ws In ef.Worksheets
    Dim dataTable As DataTable

    ' Depending on the format of the input file, you need to change this:
    dataTable.Columns.Add("FirstName", GetType(String))
    dataTable.Columns.Add("LastName", GetType(String))

    ' Extract the data from the worksheet to the DataTable.
    ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows(0), ws.Columns(0))
    dataTable.TableName = ws.Name

    ' Add DataTable to DataSet.
    [dataSet].Tables.Add(dataTable)
Next

' Create new ExcelFile.
Dim ef2 As New ExcelFile

' Imports all the tables from DataSet to new file.
For Each table In [dataSet].Tables
    ' Add new worksheet to the file.
    Dim ws As ExcelWorksheet = ef2.Worksheets.Add(table.TableName)

    ' Change the value of the first cell in the DataTable.
    table.Rows(0)(0) = "This is new file!"

    ' Insert the data from DataTable to the worksheet starting at cell "A1".
    ws.InsertDataTable(table, "A1", True)
Next

' Save the file to XLS format.
ef2.SaveXls("DataSet.xls")

To import/export the DataSet in another format you only need to replace the LoadXls/SaveXls method with preferred one (LoadXlsx/SaveXlsx, LoadCsv/SaveCsv or SaveHtml).