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:
|
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");
|
|
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).
|
|
|
© GemBox Software. All rights reserved.
|
