esProc can not only prepare and compute data, but also call the external library ReportLite to generate reports with complex format.

Environment configuration

Download and Install ReportLite

Download ReportLite from the official website, decompress the zip file and install it directly. After installation, a trial license is provided, allowing you to use ReportLite directly.

Configure external library

Create a new directory ‘extlib\ReportLiteCli’ under the esProc installation directory\esProc\ (you can also put ReportLiteCli in another directory, as long as you can find it when setting in esProc tool).

Copy the following jars from ReportLite installation directory\reportlite\lib to the newly created extlib\ReportLiteCli under esProc installation directory\esProc:

jboss-servlet-api_3.1-1.0.0.jar
esproc-ext-********.jar
htmlparser-1.6.jar
pdfbox-2.0.27.jar
fontbox-2.0.27.jar
graphics-box2.0.27.jar
mail-1.4.4.jar
reportlite.jar
scu-reportlite-cli-2.10.jar

Start esProc, and select Tool > Options > Environment from the menu bar to open up the following interface:

Click the Browse button after External library directory to open up the interface for selecting external libraries, as shown in the following figure:

Select the directory ‘extlib’ where the external library folder is located and check ReportLiteCli.

Click OK and restart esProc.

Configuration file

The file raqsoftConfig.xml under ReportLite installation directory\reportLite\config can be used to set the information such as data source connection, report template’s root directory. The examples in this article use the configuration file that comes with esProc after installation (raqsoftConfig.xml under esProc installation directory\config), which can be used directly. If you need personalized settings, you can put the raqsoftConfig.xml that comes with ReportLite in another directory on your computer to modify, as long as the new configuration file is read when reading configuration file in esProc.

All the examples in this article are carried out under Windows, and the xls and report files involved are all put on E drive. In practice, you can set according to the specific circumstances, and the only thing you need to do is to replace directory as needed when the directory is involved in report design and esProc reading.

A simple example

There is a xls file shown as below:

Now we want to generate a xls file with the following structure:

Design of report template

Set dataset

Start ReportLite, create a new report and set the data used in the report: ReportLite tool bar > Report > Dataset > Add > File dateset, then select the xls file to be used.

The ds1 in the Name column is automatically generated, representing the name of the xls file read into ReportLite. In the report template, this name is used to set the binding relationship between cell and data. In esProc script, the data in the report can be replaced based on this name.

Report template

ReportLite adopts an Excel-like design; its cell naming and some common formulas are consistent with Excel. The formula ‘=ds1.select(EID)’ in cell A3 means to retrieve the data of EID column from ds1. When viewing the report results, the data in this cell will be automatically filled based on the data in data file. For other cells, writing ‘=ds1.column name’ can retrieve the data of the corresponding column directly.

For cell operations, such as setting font color, filling color and merging cells, the methods are basically the same as Excel. You just need to set according to requirements.

Save the report file to E drive, name it “employee.rptx”.

SPL script

Start esProc and write the following script:

	A
1	=file("E:/employee.xlsx").xlsimport@t()
2	>reportlite_config("config/raqsoftConfig.xml")
3	=reportlite_open("E:/employee.rptx")
4	=reportlite_run(A3;A1:"ds1")
5	=reportlite_export@xn(A3,"E:/empreport.xlsx")

A1: Read the file ‘employee.xlsx’ and convert it to a table sequence in SPL.

A2: Read the configuration file information.

A3: Read the report file.

A4: Calculate the report, and take the data in A1 as the result set of the ds1 dataset in the report.

A5: Generate the result as an xls file and save it to E drive.

Results:

Open emprport.xlsx in E drive to view results.

Other file data sources (txt, csv)

In the above example, the data read by SPL script and the data of report dataset come from the same xls file. In practice, different xls files or other types of data files can be used, as long as the data structure is consistent with that of report dataset.

txt data

The report template is employee.rptx.

SPL script

	A
1	=file("E:/employee.txt").import@t()
2	>reportlite_config("config/raqsoftConfig.xml")
3	=reportlite_open("E:/employee.rptx")
4	=reportlite_run(A3;A1:"ds1")
5	=reportlite_export@xn(A3,"E:/empreport1.xlsx")

Results:

csv data

SPL script

	A
1	=file("E:/employee.csv").import@t(;,",")
2	>reportlite_config("config/raqsoftConfig.xml")
3	=reportlite_open("E:/employee.rptx")
4	=reportlite_run(A3;A1:"ds1")
5	=reportlite_export@xn(A3,"E:/empreport2.xlsx")

Results:

Database data

SPL script

	A
1	=connect("demo")
2	=A1.query("select * from EMPLOYEE where gender='F'")
3	>reportlite_config("config/raqsoftConfig.xml")
4	=reportlite_open("E:/employee.rptx")
5	=reportlite_run(A4;A2:"ds1")
6	=reportlite_export@xn(A4,"E:/empreport2.xlsx")
7	>A1.close()

Results:

Multiple export formats

In SPL, not only can the results be generated in xls format, but in various other formats such as pdf, doc, html.

SPL script:

	A
1	>reportlite_config("config/raqsoftConfig.xml")
2	=reportlite_open("E:/employee.rptx")
3	=reportlite_run(A2)
4	=reportlite_export@xn(A2,"E:/empreport.xlsx")
5	=reportlite_export@d(A2,"E:/empreport.docx")
6	=reportlite_export@p(A2,"E:/empreport.pdf")
7	=reportlite_export@h(A2,"E:/empreport.html")

Results

Using parameter

There is a xls file with the following sales data:

Now we want to generate the results in the following structure:

The xls file contains all sales data. The requirement is to generate results by different dimensions and put the data of each of States into a separate xls file.

Design of report template

Set dataset

Create a new report and set the data used in the report: ReportLite tool bar > Report > Dataset > Add > File dateset, then select the xls file to be used.

Set parameter

ReportLite tool bar > Report > Parameter > Add

The States in the Name is the parameter name, through which the external parameter is received.

Report template

A4: Group and stretch vertically by City; B2: Stretch horizontally by Category. For intersection areas, perform sum operation on Amount and count operation.

A1: =States+“SalesDataSummary” means to concatenate States parameter with the following fixed information together.

Save the report file to E drive, name it “sales.rptx”.

SPL script:

	A
1	=file("E:/sales1.xlsx").xlsimport@t()
2	>reportlite_config("config/raqsoftConfig.xml")
3	=reportlite_open("E:/sales.rptx")
4	=A1.group(States)
5	for A4	=reportlite_run(A3,A5.States:"States";A5:"ds1")
6		=reportlite_export@xn(A3,"E://salesdata//"+A5.States+"salesdata.xlsx")

A4: Group the data of A1 by States.A5: Loop according to the grouping result of A4.B5: Pass A5.States to the States parameter of the report, and take A5 as the result set of the ds1 dataset in the report.B6: Export the xls file and save it to E://salesdata, and concatenate the corresponding States name to the xls name.

Results: