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.
The source code samples can be integrated in:
- ASP.NET web pages - Windows applications - Windows Forms (WinForms) - Console applications - Windows service applications - ASP.NET MVC web applications
EasyXLS permits you to export Excel files without Excel installed, without Interop or any other additional software installed.
// 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");
' Create the database connectionDim 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 datasetDim 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 datasetDim 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")