6.8. JXLS Reports

JXLS (http://jxls.sourceforge.net) is a templating engine for Microsoft Excel and can be used together with the Dynamic List in order for users to export data into a predefined Excel spreadsheet (see the ReportServer User Guide). JXLS can, however, also be used as a first class report type which offers some advantage over the use as a Dynamic List templating engine: In particular you can define SQL queries directly from within the template and work with parameters.
In order to define a JXLS report, all that is needed in terms of configuration is an Excel file which serves as the template and a datasource which can be accessed from within the template. In addition as with any other report type you can specify parameters that can also be accessed from within the template.
JXLS is available in two different versions in ReportServer: the current JXLS2 version and a legacy, JXLS1 version. We will discuss both in the corresponding sections below.

6.8.1. JXLS2

In JXLS2 (http://jxls.sourceforge.net), you define the JXLS2 commands via Excel comments. The JXLS2 engine parses these comments and transforms the template accordingly. We will first explain some JXLS2 concepts and then we will show some examples to make it work together with ReportServer.

XLS Area

A XLS area represents a rectangular area in an Excel file which needs to be transformed. This basically defines the boundaries of your JXLS2 template in your Excel file. Each XLS Area may have a list of transformation commands associated with it and a set of nested child areas.

Constructing a XLS Area

You can use a special markup in your Excel template to construct a XLS area. The markup should be placed into an Excel comment in the first cell of the area. Its syntax is:

	jx:area(lastCell = "<AREA_LAST_CELL>")

where <AREA_LAST_CELL> is the last cell of the defined area.

This markup defines a top-level area starting in the cell containing the markup comment and ending in the <AREA_LAST_CELL> cell. Refer to the following example:

The XLS area is defined in a comment in the A1 cell as

	jx:area(lastCell="D4")

In the example we have an area covering the A1:D4 cell range.

Command Markup

A command represents a transformation action on a single or on multiple XlsAreas. It should be defined inside the boundaries of an XlsArea:

	jx:<command_name>(attr1='val1' attr2='val2' ... attrN='valN' lastCell=<last_cell> areas=["<command_area1>", "<command_area2", ... "<command_areaN>"])

Example commands include the following:

  • each
  • if
  • image

attr1, attr2,..., attrN are the command specific attributes.

<last_cell> defines the bottom-right cell of the command body area, analogously to the XLS area.

<command_area1>, <command_area2>, ... <command_areaN> - define XLS areas to be passed to the command as parameter.

Note that in a single cell comment you can define multiple commands. For example, in a single cell comment, you can have the following:

	jx:each(items="department.staff", var="employee", lastCell="F8")
	jx:if(condition="employee.payment <= 2000", lastCell="F8", areas=["A8:F8","A13:F13"])

Consider the following example.

	jx:area(lastCell="B1")
	jx:each(items="data" var="customer" lastCell="B1")
	
	${customer.CUS_CUSTOMERNAME} |${customer.CUS_PHONE}

The cell A1 contains an Excel comment with the text jx:area(lastCell="B1"). It defines the boundaries of our template to be A1:B1. It also contains a Jxls Each-Command with the following text: jx:each(items="data" var="customer" lastCell="B1"). The Each-Command will iterate the collection of objects in the ''data'' collection and print the corresponding information. The body area of the Each-Command is A1:B1 (defined by the lastCell attribute), which means that the cells will be cloned and processed with each new Customer object in the context.
To access an individual attribute you can use customer.colname, where ''customer'' is the variable bound to the current data row and ''colname'' is the name of the attribute.

When used as a template for the dynamic list (refer to the chapter on the Dynamic List in the User Guide), the ''data'' variable contains the data selected by the dynamic list.

When used as a first-class report type you need to select the data from a sql datasource. For this purpose, you can use the object jdbc.query inside the items attribute which provides access to the underlying datasource. Here, we select two fields from the table T_AGG_CUSTOMER.

	jx:area(lastCell="B2")
	jx:each(items="jdbc.query('select CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER')" var="customer" lastCell="B2")
	
	${customer.name} | ${customer.phone}

In order to use parameters within queries, you can use the standard syntax for parameters in the query described in Section 6.3.10. This is best understood with an example: Here, we assume that you previously created a parameter with key ''parameterKey''. You can also access parameters via the parameters object. Again, assuming that there is a parameter with key 'parameterKey'', you could access the parameters value via

	${parameters.parameterKey}
	jx:area(lastCell="B2")
	jx:each(items="jdbc.query('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER WHERE CUS_CUSTOMERNAME = ${parameterKey}')" var="customer" lastCell="B2")
	
	Name | Phone
	${customer.name} | ${customer.phone}

Following is a complete example, which works for the demo data that is shipped with ReportServer. It shows a very simple employee report showing some basic information of the employee and the customers served by the employee. The report uses a single parameter with key ''employee'' which is assumed to hold an employee number.

	jx:area(lastCell="C10")
	jx:each(items="jdbc.query('SELECT EMP_FIRSTNAME as firstname, EMP_LASTNAME as lastname FROM T_AGG_EMPLOYEE WHERE EMP_EMPLOYEENUMBER=${employee}')" var="employee" lastCell="B3")
	jx:each(items="jdbc.query('SELECT CUS_CUSTOMERNAME as name, CUS_CUSTOMERNUMBER as num, Y_VOLUME as volume FROM T_AGG_CUSTOMER WHERE EMP_EMPLOYEENUMBER=${employee}')" var="customer" lastCell="C8")
	
	Employee Number: | ${parameters.employee}
	First name: | ${employee.firstname}
	Last name: | ${employee.lastname}
	
	List of Customers
	
	Customer Name | Customer Number | Volume
	${customer.name}  | ${customer.num}  | ${customer.volume}
	TOTAL:	| =SUM(C8)

A full documentation of the possibilities offered by JXLS2 is out of scope of this documentation, and we refer the interested reader to the official JXLS documentation available on http://jxls.sourceforge.net.

6.8.2. JXLS1

The legacy JXLS1 (http://jxls.sourceforge.net/1.x/) is a templating engine for Microsoft Excel and can be used together with the Dynamic List in order for users to export data into a predefined Excel spreadsheet (see the ReportServer User Guide). JXLS1 can, however, also be used as a first class report type which offers some advantage over the use as a Dynamic List templating engine: In particular you can define SQL queries directly from within the template and work with parameters.

In order to define a JXLS1 report, all that is needed in terms of configuration is an Excel file which serves as the template and a datasource which can be accessed from within the template. In addition as with any other report type you can specify parameters that can also be accessed from within the template.

The Basics

Templates are formed using two basic constructs: tags and replacements. A so called tag surrounds an area and defines how the cells within are to be interpreted:

	<jx:forEach items="${data}" var="department">
	${department.name} | ${department.chief}
	</jx:forEach>
In the example each line of the example becomes one line in the Excel document and the pipe symbol (|) marks the end of cell, that is the second line consists of two cells. It is also important to note that commands should start at the very beginning of a cell and that no leading whitespace may exist.

In the example the tag <jx:forEach ...> marks the beginning of a block. The block is closed by the corresponding end tag </jx:forEach>. All cells within the block are repeated for every record in the data variable. To access the individual attributes within the "forEach-block" the variable defined in the "var" attribute can be used: in the example "department". To access an individual attribute use department.colname where "department" is the variable bound to the current data row and "colname" is the name of the attribute.

When used as a template for the dynamic list (see the chapter on the Dynamic List in the User guide), then ${data} contains the data selected by the dynamic list. When used as a first-class report type you need to select the data from the specified datasource. For this, you can use the object ${sql} which provides access to the underlying datasource. As an example consider the following template:

	<jx:forEach items="${sql.exec('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER')}" var="customer">
	${customer.name} | ${customer.phone} | 
	</jx:forEach>
Variable ${rm} can be used synonymously to ${sql} in order to be consistent with the documentation of JXLS (http://jxls.sourceforge.net/1.x/samples/reportsample.html.

Here we select two fields from table T_AGG_CUSTOMER. In order to use parameters within queries, you can use the standard syntax for parameters in the query described in Section 6.3.10. This is best understood with an example:

	<jx:forEach items="${sql.exec('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER WHERE CUS_CUSTOMERNAME = ${parameterKey}')}" var="customer">
	${customer.name} | ${customer.phone} | 
	</jx:forEach>

Here we assume that there exists a parameter with key "parameterKey". Additionally you can access parameters via the ${parameters} object. Again assuming that there is a parameter with key "parameterKey" you could access the parameters value via

	${parameters.parameterKey}

Following is a complete example, which works for the demo data that is shipped with ReportServer. It shows a very simple employee report showing some basic information of the employee and the customers served by the employee. The report uses a single parameter with key employee which is assumed to hold an employee number.

	Employee Number: | ${parameters.employee}		
	<jx:forEach items="${rm.exec('SELECT EMP_FIRSTNAME as firstname, EMP_LASTNAME as lastname FROM T_AGG_EMPLOYEE WHERE EMP_EMPLOYEENUMBER=${employee}')}" var="employee">			
	First name: | ${employee.firstname}		
	Last name: | ${employee.lastname}		
	</jx:forEach>			
				
	List of Customers			
				
	Customer Name | Customer Number | Volume	
	<jx:outline detail="true">			
	<jx:forEach items="${rm.exec('SELECT CUS_CUSTOMERNAME as name, CUS_CUSTOMERNUMBER as num, Y_VOLUME as volume FROM T_AGG_CUSTOMER WHERE EMP_EMPLOYEENUMBER=${employee}')}" var="customer">			
	${customer.name}  | ${customer.num}  | ${customer.volume}	
	</jx:forEach>			
	</jx:outline>			
				
	| TOTAL:	| $[SUM(C11)]

A full documentation of the possibilities offered by JXLS is out of scope of this documentation, and we refer the interested reader to the official JXLS documentation available on http://jxls.sourceforge.net/1.x.