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 To insert a You can test inserting a The example creates a 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. The following example shows how you can export a The process is similar to exporting a You can test inserting a The example creates a 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.DataTable or DataSet.Export DataTable to Excel sheet
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
DataTable to an empty file with the interactive example below. Just choose an output file format, click Run Example, and download the generated file.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.
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 ModuleExport DataTable performance
Export DataSet to Excel file
DataSet object to an Excel file in C# or VB.NET.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
DataSet to an empty file with the interactive example below. Just choose an output file format, click Run Example, and download the generated file.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.
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 ModuleSupported Excel file formats
