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.

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. For a complete JXLS2 documentation refer to http://jxls.sourceforge.net/.

You can find JXLS examples in ReportServer here: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/jxls.

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.

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 (http://jxls.sourceforge.net/samples/object_collection.html):

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.

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.

Note that you have to enter the fields in lower case so that these are correctly mapped by the JXLS engine.

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 7.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.