I. Overview
1) Excel dataset means that the data source is a series of xls or xlsx files.
2) Excel files of the Project should be stored under the directory: \webapps\webroot\WEB-INF\reportlets.
| You will learn |
|---|
|
II. Creation of Excel Dataset
1. Create a file
Create a datasource.xls file in Excel or download it here:
2. Set the dataset
1) After the Designer is enabled, the dataset management interface, which is at the bottom left of the Designer, is divided into two parts: one for template dataset, and the other for server dataset. Let’s take the template dataset as an example.
2) Select the template dataset and then click the + above it. Click File dataset.
3) Set the file dataset
Set the title as “Sales” on the File window that pops up.
Click the drop-down in File type and change it to Excel.
Click Select in the Local file field.
4) Select the datasource.xls on the window that pops up. Click Open.
5) CheckFirst row contains column headingsin the Setting, so that the data in the first row can be stored as the field name.
3. Complete the creation of dataset
1) Click Preview to confirm that all data are correct, and then click OK.
2) ClickOKagain to return.
3) You can see that an Excel dataset is added in the template dataset list at the bottom left of the dataset management interface.
III. Creation of Dynamic Excel Dataset
Dynamic excel datasets can use parameters to switch different Excel files to display data.
1. Create a file
1) Create two files respectively named as 2018data.xlsx and 2019data.xlsx, which can be downloaded here.
2) We can add a folder named Excel under the “reportlets” directory to save these two Excel files, With the storage path being \webapps\webroot\WEB-INF\reportlets\Excel
2. Set the dataset
1) Select the template dataset and then click the+above it. ClickFile dataset.
2) Set the file dataset
Set the title as Age on the File window that pops up.
Click the drop-down in File type and change it to Excel;
ClickSelectingin theLocal filefield.
3) Access to the Excel folder and select the file named 2018data.xlsx. ClickOpen.
3. Set parameters
1) Modify it in the local file path by replace 2018data with the parameter formula ${name}, and change the original path to reportlets/Excel/${name}.xlsx.
2) Click the Refresh button, set the parameter name, select Stringas the parameter value type, and input 2018data into the Value field,
3) Check First row contains column headings in the Setting, so that the data in the first row can be stored as the field name.
4. Complete the creation of dataset
1) Click Preview without modifying the parameter value, and then ClickOK. Confirm that all data are correct, and then click OK.
2) Click OKagain to return.
3) You can see that a dynamic Excel dataset is added in the template dataset list at the bottom left of the dataset management interface.
5. Preview effect
1) Select all the fields in the Age Dataset, drag them to Cell A2. The menu bar will appear. Select From left to right.
2) Input titles in the title row and adjust the cell style.
3) Save the templates and select Pagination Preview.
4) The preview result shows that the default Excel data is the data of the file named 2018data.
5) We can input the parameter &name=2019dataafter the URL to transfer the parameters, then click the Refresh, and switch different data of Excel files.
