|
User Guide EasyXLS Basics Export files Export DataSet to Excel file in .NET
|
|
|
Export DataSet to Excel file in .NET
|
Concept
EasyXLS™
library allows you to export a DataSet to an Excel file. The data in cells can be formatted using predefined formats or user-defined formats.
EasyXLS can be successfully used inclusively to export large Excel files having big volume of data with fast exporting time.
Concept in action
The below example shows how to export DataSet to Excel in C#, VB.NET, C++.NET and J#.
|
Source code sample
|
|
|
|
|
|
|
// Create the database connection
String sConnectionString =
"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
System.Data.SqlClient.SqlConnection sqlConnection =
new System.Data.SqlClient.SqlConnection(sConnectionString);
sqlConnection.Open();
// Create the adapter used to fill the dataset
String sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + " +
"'/' + CAST(Day(ord.OrderDate) AS varchar) + " +
"'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " +
"P.ProductName AS 'Product Name', O.UnitPrice AS Price, O.Quantity, " +
"O.UnitPrice * O. Quantity AS Value " +
"FROM Orders AS ord, [Order Details] AS O, Products AS P " +
"WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID";
System.Data.SqlClient.SqlDataAdapter adp =
new System.Data.SqlClient.SqlDataAdapter(sQueryString, sqlConnection);
// Populate the dataset
DataSet dataset = new DataSet();
adp.Fill(dataset);
// Export dataset to Excel file
Console.WriteLine("Writing file C:\\Samples\\DataSet to Excel.xlsx.");
workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\DataSet to Excel.xlsx", dataset,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataSet");
|
Click here to see Continuous Code Listing
|
|
|
|
' Create the database connection
Dim sConnectionString As String = _
"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(sConnectionString)
sqlConnection.Open()
' Create the adapter used to fill the dataset
Dim sQueryString As String = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + "
sQueryString += "'/' + CAST(Day(ord.OrderDate) AS varchar) + "
sQueryString += "'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', "
sQueryString += "P.ProductName AS 'Product Name', O.UnitPrice AS Price, "
sQueryString += "O.Quantity, O.UnitPrice * O. Quantity AS Value "
sQueryString += "FROM Orders AS ord, [Order Details] AS O, Products AS P "
sQueryString += "WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID"
Dim adp as System.Data.SqlClient.SqlDataAdapter = _
new System.Data.SqlClient.SqlDataAdapter(sQueryString, sqlConnection)
' Populate the dataset
Dim dataset As DataSet = New DataSet
adp.Fill(dataset)
' Export dataset to Excel file
Console.WriteLine("Writing file C:\\Samples\\DataSet to Excel.xlsx.")
workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\DataSet to Excel.xlsx", dataset, _
New ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataSet")
|
Click here to see Continuous Code Listing
|
|
|
|
// Create the database connection
String *sConnectionString =
"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
System::Data::SqlClient::SqlConnection *sqlConnection =
new System::Data::SqlClient::SqlConnection(sConnectionString);
sqlConnection->Open();
// Create the adapter used to fill the dataset
String *sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + ";
sQueryString = String::Concat(
sQueryString, "'/' + CAST(Day(ord.OrderDate) AS varchar) + ");
sQueryString = String::Concat(
sQueryString, "'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', ");
sQueryString = String::Concat(
sQueryString, "P.ProductName AS 'Product Name', O.UnitPrice AS Price, ");
sQueryString = String::Concat(
sQueryString, "O.Quantity, O.UnitPrice * O. Quantity AS Value ");
sQueryString = String::Concat(
sQueryString, "FROM Orders AS ord, [Order Details] AS O, Products AS P ");
sQueryString = String::Concat(
sQueryString, "WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID");
System::Data::SqlClient::SqlDataAdapter *adp =
new System::Data::SqlClient::SqlDataAdapter(sQueryString, sqlConnection);
// Populate the dataset
System::Data::DataSet *dataset = new System::Data::DataSet();
adp->Fill(dataset);
//Export dataset to Excel file
Console::WriteLine("Writing file C:\\Samples\\DataSet to Excel.xlsx.");
workbook->easy_WriteXLSXFile_FromDataSet("C:\\Samples\\DataSet to Excel.xlsx", dataset,
new ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS1), "DataSet");
|
Click here to see Continuous Code Listing
|
|
|
|
// Create the database connection
String sConnectionString =
"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
System.Data.SqlClient.SqlConnection sqlConnection =
new System.Data.SqlClient.SqlConnection(sConnectionString);
sqlConnection.Open();
// Create the adapter used to fill the dataset
String sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + " +
"'/' + CAST(Day(ord.OrderDate) AS varchar) + " +
"'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " +
"P.ProductName AS 'Product Name', O.UnitPrice AS Price, O.Quantity, " +
"O.UnitPrice * O. Quantity AS Value " +
"FROM Orders AS ord, [Order Details] AS O, Products AS P " +
"WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID";
System.Data.SqlClient.SqlDataAdapter adp =
new System.Data.SqlClient.SqlDataAdapter(sQueryString, sqlConnection);
// Populate the dataset
DataSet dataset = new DataSet();
adp.Fill(dataset);
// Export dataset to Excel file
Console.WriteLine("Writing file C:\\Samples\\DataSet to Excel.xlsx.");
workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\DataSet to Excel.xlsx", dataset,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataSet");
|
Click here to see Continuous Code Listing
|
|
|
|
|
|
|
The screen shot below provides an example of exported data to Excel file from a DataSet.
Formatting cells
EasyXLS™ enables you to format cells, rows and columns in order to set the fonts and colors.
|
Export DataSet to XLSX, XLSB, XLSM and XLS files
This code sample shows how to export a DataSet to XLSX file. Similarly, you can export a DataSet to XLS file using ExcelDocument.easy_WriteXLSFile_FromDataSet method or export DataSet to XLSB file using ExcelDocument.easy_WriteXLSBFile_FromDataSet method.
|
Export DataSet to Excel file with multiple sheets
For an Excel file having multiple sheets, the DataSet can be inserted using ExcelWorksheet.easy_insertDataSet method and the Excel file must be exported using ExcelDocument.easy_WriteXLSXFile method.
|
Getting started with EasyXLS Excel library
To download the trial version of EasyXLS Excel Library, press the below button:
|
|
If you already own a license key, you may login and download EasyXLS from your account.
After installation, you will need to create a C#, VB.NET, C++.NET or J# project and add a reference to EasyXLS.dll.
|
|
|
|
Available for: Professional, Excel Writer, Express Excel Writer
|
