Excel reporting plays very important role in many enterprise applications. Now jXLS allows you to place SQL queries directly into XLS template to be executed during template transformation so that all query results are available in your excel report.
To execute your SQL query and display its results in your excel file you have to put a special bean implementing
ReportManager interface into the bean context before running transformation. Currently this interface
has the only method
public List exec(String sql) throws SQLException
This method should take SQL query and execute it returning required results as a list of beans.
jXLS provides a default implementation of this interface called ReportManagerImpl
which uses RowSetDynaClass to wrap ResultSet object into collections of objects.
This technique is described in the JDBC resultset section.
The usage of this class can be as following
Connection conn = ...// get database connection in some way
Map beans = new HashMap();
ReportManager rm = new ReportManagerImpl( conn, beans );
beans.put("rm", rm);
InputStream is = new BufferedInputStream(new FileInputStream("reportTemplate.xls"));
XLSTransformer transformer = new XLSTransformer();
HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
Here we see that ReportManagerImpl constructor takes database connection object
and the map of beans passed to XLSTransformer as parameters. Then we place ReportManager object into
the bean context using "rm" key. It means that in XLS template we will be able to execute any SQL
query by passing it to rm.exec() method as a parameter. For example
${rm.exec("SELECT name, age FROM employee")}
Usually it will be used in conjunction with jx:forEach tag to iterate
ResultSet beans collection and display it in your excel. For example
<jx:forEach items="${rm.exec('SELECT e.name, e.age, e.payment FROM employee e')}" var="employee">
${employee.name} | ${employee.age} | ${employee.payment}
</jx:forEach>
You can use groupBy attribute of jx:forEach to group query results
by some column values. See Grouping data with jx:forEach tag section for details.
See Reporting Sample for real sample.
You should be able to pass any SQL queries to ReportManagerImpl if used jdbc driver supports them.
You can also use the results of the one query in the next queries. This can be done by placing the result of the first query into the bean context when processing so that it will be available when constructing dependent query.
Typical usage of dependent queries (or subqueries) can be demonstrated using two jx:forEach
tag where one of them is nested in the other. This is an example from Reporting Sample
<jx:forEach items="${rm.exec('SELECT d.name, d.id FROM department d')}" var="dep">
Department: ${dep.name}
Name | Payment | Bonus | Total
<jx:forEach items="${rm.exec('SELECT name, age, payment, bonus, birthDate FROM employee e where e.depid = ' + dep.id)}" var="employee">
${employee.empname} | ${employee.payment} | ${employee.bonus} | $[B23*(1+C23)]
</jx:forEach>
</jx:forEach>
Here we place each department we got from the first query into the context under dep key.
After that we use it in the inner jx:forEach tag
<jx:forEach items="${rm.exec('SELECT name, age, payment, bonus, birthDate FROM employee e where e.depid = ' + dep.id)}" var="employee">
jXLS will substitute the id value of currently processed department so that executed query will
select all the employees for the department.
Previous example already showed how to use a parameter inside SQL query. Also we can use an external parameter if we put it into the bean context
Map beans = new HashMap();
ReportManager reportManager = new ReportManagerImpl( conn, beans );
beans.put("rm", reportManager);
beans.put("minDate", "1979-01-01");
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(templateFileName, beans, destFileName);
Here we put date value "1979-01-01" into the bean context under minDate key. Next is how we can use it to construct a query
<jx:forEach items="${rm.exec("SELECT d.name depname, e.name empname, age, payment, bonus, birthDate FROM employee e, department d WHERE d.id = e.depid AND birthDate > '1975-01-01' AND birthDate < '" + minDate + "' order by age desc")}" var="employee">
Also you can see how it is possible to use single quotes inside SQL query if needed.
See usage sample in the Reporting sample section