XLS and XLSX Data Sources

This documentation is an older version of JasperReports Server Administration Guide. View the latest documentation.

XLS and XLSX data sources read a file in the Microsoft Excel XLS or XLSX format and allow you to query its contents as a relational table. These data sources support features domain-related features like returning JDBC metadata, creating derived tables, supporting calculated fields, domain pre-filtering, joining other data sources in a virtual data source, and full support in the Domain Designer.

To create an XLS or XLSX data source:

Log on as an administrator.

Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. The New Data Source page appears.

In the Type field, select either the XLS or XLSX data source. In this example, we create an XLSX data source.

File Data Source Page for a JSON Data Source

Enter the date and time format patterns used for the data source.

Enter the file path for the XLSX file. You can specify a file in the repository with the repo: syntax, followed by the repository path of the file. Specify ftp:, http: or https: to use those Internet protocols. To specify a file on your server's file system, specify its path directly, starting from the root, for example /tmp/MyDataFile.xlsx. The user running the server process must have permission to access the file.

Enter the regional locale for the file. The locale property displays the data in a specific format based on the region.

Enter the number pattern for the file.

Enter the name of the Excel file sheet to use for the data source.

Enter the local time zone for the file.

10.

Specify whether the first row in the XLSX file is a header as true or false.

11.

Click Save. The Save dialog appears.

12.

Enter a name for the data source and an optional description. The Resource ID is generated from the name you enter. If you haven't already specified a location, expand the folder tree and select the location for your data source.

13.

Click Save in the dialog. The data source appears in the repository.