DataTable Export
The following examples show how to export a DataTables provide a way of storing and manipulating tabular data in rows and columns. They are commonly used for organizing and working with structured data in applications, supporting features such as sorting, filtering, and data binding to user interface controls. To insert a You can test exporting a The example creates a GemBox.Spreadsheet, being fully written in C#, utilizes caching techniques to provide optimal speed and memory performance. This makes this component ideal for generating big reports or working with large datasets. Your users won't need to wait long for their reports to be generated anymore. With GemBox.Spreadsheet users can read and write 1,000,000 rows within 4 seconds and with less than 256MB of RAM.. You can try the performance yourself with the Performance example. The following example shows how to export a The process is similar to exporting a You can try exporting a The example creates a GemBox.Spreadsheet allows you to write to various file formats using the same API:DataTable object to an Excel sheet or a DataSet object to an Excel file in C# and VB.NET. For that, you will use the GemBox.Spreadsheet library that supports the most popular spreadsheet file formats (XLS, XLSX, ODS, CSV).Export DataTable to an Excel sheet
DataTable in an Excel sheet, call the InsertDataTable method. You can use the InsertDataTableOptions to customize the export of data from a DataTable to anExcelWorksheet such as:ColumnHeaders - Use it to specify whether to insert column headers in the output, or not.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 allowing you to apply settings like cell style, format, and similar.Try exporting a DataTable online
DataTable to an Excel file with the interactive example below. Apart from Excel, you can choose other output file formats, supported by the GemBox.Spreadsheet component.DataTable object, fills it with data, inserts it into an ExcelFile instance and saves it to Excel, or 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 GemBox.Spreadsheet;
using System.Data;
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 GemBox.Spreadsheet
Imports System.Data
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
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. For each DataTable, you can create an empty sheet, and then insert the DataTable into the sheet.Try exporting DataSet online
DataSet into an Excel file using the interactive example below. Apart from Excel, you can choose other output file formats, supported by the GemBoxSpreadsheet library.DataSet object, fills it with data, inserts it into an ExcelFile instance, and saves it to Excel , or 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 library.using GemBox.Spreadsheet;
using System.Data;
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 GemBox.Spreadsheet
Imports System.Data
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
