Basics of processing JDBC ResultSet with jXLS is described here.
Next samples assume we have a table
employee with columns
name, age, payment, bonus, birthDate. XLS template is the same as in
Simple collection sample
XLS file employees.xls
This is a source code snippet to query data using
org.apache.commons.beanutils.ResultSetDynaClass from Commons BeanUtils library.
Connection con = ...// get connection
Statement stmt = con.createStatement();
String query = "SELECT name, age, payment, bonus, birthDate FROM employee";
ResultSet rs = stmt.executeQuery(query);
Map beans = new HashMap();
ResultSetCollection rsc = new ResultSetCollection(rs, false);
beans.put( "employee", rsc );
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS( templateFileName, beans, destFileName);
An important problem with this approach is that the underlying ResultSet must remain open throughout the period of time that the rows are being processed by jXLS engine. Next approach does not have such restriction.
When you construct an instance of org.apache.commons.beanutils.RowSetDynaClass from Commons BeanUtils,
the underlying data are copied into a set of in-memory DynaBeans that represent the result.
So you can immediately close the ResultSet, normally before you even process the actual data that was returned.
But the disadvantage is that you must pay the performance and memory costs of copying the result data.
Next code snippet demonstrates how to use RowSet to export JDBC query results.
Connection con = ...// get connection
Statement stmt = con.createStatement();
String query = "SELECT name, age, payment, bonus, birthDate FROM employee";
ResultSet rs = stmt.executeQuery(query);
// second parameter to RowSetDynaClass constructor indicates
// that properties should not be lowercased
RowSetDynaClass rsdc = new RowSetDynaClass(rs, false);
Map beans = new HashMap();
beans.put( "employee", rsdc.getRows() );
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS( templateFileName, beans, destFileName);
Result looks like
Excel file is employees_output.xls