This sample demonstrates usage of reporting techniques available in jXLS. See also SQL Reporting section for detailed explanation of how it works.
In the next sample we use two tables employee and department.
id column in the department table is referenced by foreign key depid
column from the employee table.
We use the same template to demonstrate several ways of using SQL queries to get required data.
The template can be found in here report.xls.
And the result is here report_output.xls.
This is an example of rather a simple query which joins employee and department
tables to list all employees and their corresponding departments
<jx:forEach items="${rm.exec('SELECT d.name depname, e.name empname, payment, bonus FROM employee e, department d where d.id = e.depid order by age desc')}" var="employee">
${employee.empname} | ${employee.payment} | ${employee.bonus} | $[B4*(1+C4)] | ${employee.depname}
</jx:forEach>
The screenshot is here
This sample demonstrates how to group the results of the previous join by department names
<jx:forEach items="${rm.exec('SELECT d.name depname, e.name empname, payment, bonus FROM employee e, department d where d.id = e.depid order by age desc')}" groupBy="depname">
Department: ${group.item.depname}
Name | Payment | Bonus | Total
<jx:forEach items="${group.items}" var="employee">
${employee.empname} | ${employee.payment} | ${employee.bonus} | $[B13*(1+C13)]
</jx:forEach>
</jx:forEach>
Result looks like
This sample demonstrates how to execute dependent queries. First we select all the departments using the next query
SELECT d.name, d.id FROM department d
Then we iterate the results and for each department executing a query to get all employees for this department
SELECT name, age, payment, bonus, birthDate FROM employee e where e.depid = dep.id
In the template it looks like this
<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, payment, bonus FROM employee e where e.depid = ' + dep.id)}" var="employee">
${employee.empname} | ${employee.payment} | ${employee.bonus} | $[B23*(1+C23)]
</jx:forEach>
</jx:forEach>
Pay attention that we place every department in the dep object and
then use it in the dependent query as a parameter
'SELECT name, age, payment, bonus, birthDate FROM employee e where e.depid = ' + dep.id
Take care not to include dep.id into the quoted string as it will not be replaced by real value in such case.
Result looks like
This sample shows how to use a parameter in SQL query and how to construct queries containing quotes
Let assume we need to get all employees having birthdate between '1975-01-01' and some minDate parameter
which is set dynamically before report generation. In this case we have to pass required date to XLSTransformer
with minDate key before transforming template
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);
In the template we may invoke corresponding rm.exec() method using the following syntax
${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")}
See the last section in the template file report.xls and in the result output report_output.xls to understand how the whole jx:forEach tag works in this case. The result looks like this
