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 C# / VB.NET component which provides simple and efficient way to read, write, convert and print native Microsoft Excel (XLSX, XLS) and other spreadsheet (ODS, CSV) formats and various document formats (HTML, PDF, XPS) from .NET applications without the need for Microsoft Excel on either the developer or client machines.
GemBox.Spreadsheet Free is free of charge while GemBox.Spreadsheet Professional is a commercial version licensed per developer.
For more information, see GemBox.Spreadsheet features and why GemBox.Spreadsheet outperforms Excel automation.

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

C# code:

var dataSet = new DataSet();

// Load Excel file.
var workbook = ExcelFile.Load("Workbook.xls");

// Export all worksheets to DataSet.
foreach (var worksheet in workbook.Worksheets)
{
// Extract the data from the worksheet to newly created DataTable starting at
// first row and first column for 10 rows or until the first empty row appears.
var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
{
StartRow = 0,
StartColumn = 0,
NumberOfRows = 10,
ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
});
dataTable.TableName = worksheet.Name;

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

// Create new ExcelFile.
var workbook2 = new ExcelFile();

// Imports all tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
// Add new worksheet to the file.
var worksheet = workbook2.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".
worksheet.InsertDataTable(dataTable,
new InsertDataTableOptions("A1") { ColumnHeaders = true });
}

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

Visual Basic .NET code:

Dim dataSet = New DataSet()

' Load Excel file.
Dim workbook = ExcelFile.Load("Workbook.xls")

' Export all worksheets to DataSet.
For Each worksheet In workbook.Worksheets
' Extract the data from the worksheet to newly created DataTable starting at
' first row and first column for 10 rows or until the first empty row appears.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
{
.StartRow = 0,
.StartColumn = 0,
.NumberOfRows = 10,
.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
})
dataTable.TableName = worksheet.Name

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

' Create new ExcelFile.
Dim workbook2 = New ExcelFile()

' Imports all tables from DataSet to new file.
For Each dataTable As DataTable In dataSet.Tables
' Add new worksheet to the file.
Dim worksheet = workbook2.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".
worksheet.InsertDataTable(dataTable,
New InsertDataTableOptions("A1") With {.ColumnHeaders = True})
Next

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

For a more detailed examples, see Import/Export DataTable samples from GemBox.Spreadsheet Sample Explorer.