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.
EasyXLS permits you to export Excel files without Excel installed or any other additional software installed.
Concept in action
The below example shows how to export ResultSet to Excel in Java.
// 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");