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.

RowSet based export

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