|
User Guide EasyXLS Basics Export files Export ResultSet to Excel file in Java
|
|
|
Export ResultSet to Excel file in Java
|
Concept
EasyXLS™
library allows you to export a ResultSet 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 ResultSet to Excel in Java.
|
Source code sample
|
|
|
|
|
|
|
// Create the database connection
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sConnectionString =
"jdbc:microsoft:sqlserver://localhost:1433;databasename=Northwind;user=sa;password=;";
Connection sqlConnection = (Connection) DriverManager.getConnection(sConnectionString);
// Create the result set used to populate the sheet
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";
PreparedStatement pStatement = sqlConnection.prepareStatement(sQueryString,
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
ResultSet resultset = pStatement.executeQuery();
// Export resultset to Excel file
System.out.println("Writing file C:\\Samples\\ResultSet to Excel.xlsx.");
workbook.easy_WriteXLSXFile_FromResultSet("C:\\Samples\\ResultSet to Excel.xlsx",
resultset, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "ResultSet");
|
Click here to see Continuous Code Listing
|
|
|
|
|
|
|
The screen shot below provides an example of exported data to Excel file from a ResultSet.
Formatting cells
EasyXLS™ enables you to format cells, rows and columns in order to set the fonts and colors.
|
Export ResultSet to XLSX, XLSB, XLSM and XLS files
This code sample shows how to export a ResultSet to XLSX file. Similarly, you can export a ResultSet to XLS file using ExcelDocument.easy_WriteXLSFile_FromResultSet method or export ResultSet to XLSB file using ExcelDocument.easy_WriteXLSBFile_FromResultSet method.
|
Export ResultSet to Excel file with multiple sheets
For an Excel file having multiple sheets, the ResultSet can be inserted using ExcelWorksheet.easy_insertResultSet 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 Java project and add EasyXLS.jar to external libraries.
|
|
|
|
Available for: Professional, Excel Writer, Express Excel Writer
|
