Though jXLS was not designed to export JDBC query results it can be easily done
using dynabean classes from Jakarta Commons BeanUtils. There are two ways to export
ResultSet
object with XLSTransformer
. First one uses org.apache.commons.beanutils.RowSetDynaClass
.
Second one is based on net.sf.jxls.report.ResultSetCollection
class.
When you construct an instance of org.apache.commons.beanutils.RowSetDynaClass
,
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.
To use this approach first you have to construct new RowSetDynaClass
instance passing your ResultSet to it.
ResultSet resultSet = ... RowSetDynaClass rowSet = new RowSetDynaClass(resultSet, false);
Second constructor parameter indicates that property names should not be lowercased in resulted collection of dynamic beans.
After you have initialized instance of RowSetDynaClass
you can invoke its getRows()
method
to get collection of DynaBeans which can be exported in common way.
Map beans = new HashMap(); beans.put( "employee", rowSet.getRows() ); XLSTransformer transformer = new XLSTransformer(); transformer.transformXLS( templateFileName, beans, destFileName);
See ResultSet sample for more details.
If you don't want to load all ResultSet
data in memory and agree to keep database connection open
while processing all data you may use net.sf.jxls.report.ResultSetCollection
class.
This class takes ResultSet
object as a parameter and implements Collection
interface
to operate with underlying data. In its turn ResultSetCollection
uses org.apache.commons.beanutils.ResultSetDynaClass
to return retrieved data as dynamic objects.
Usage of net.sf.jxls.report.ResultSetCollection
class is as easy as
ResultSetCollection rsc = new ResultSetCollection(resultSet, false); beans.put( "employee", rsc );
The second constructor argument indicates that property names should not be lowercased before processing.
See ResultSet sample for more details