JasperReports logo version 7.0.0 Home - Samples - Configuration - Functions - FAQ - API (Javadoc)

JasperReports - Excel Data Adapter Sample

Shows how the Excel data adapters can be used to fill reports.

Main Features in This Sample

Excel Data Adapter

Secondary Features

Excel Data Source

Excel Data Adapter

Documented by Sanda Zaharia

Description / Goal
How to fill a report using data from an Excel file.

Since:

Other Samples
/demo/samples/exceldatasource

The Excel Data Source

The next step after the report compilation is the report filling. During this process required data is read from the report data source and/or calculated from report expressions, while report sections are filled one by one.

Data sources are used when data come as a set of structured records, either extracted from a relational database, or loaded from specific files. In order to become more familiar with data source objects please consult the Data Sources section.

When reporting data is stored in Microsoft Excel files (either XLSX or XLS format), the ExcelDataSource implementation can be used to read it and feed it into the report. Excel files are parsed according to their internal structure and resulting data are returned in the form of one or multiple data source records. In order to obtain such records, the ExcelDataSource needs to know:

The Excel Data Adapter

The built-in Excel data adapter tool can be used to create and populate an Excel data source. Necessary parameters or properties can be set using the existing ExcelDataAdapter implementation:

public interface ExcelDataAdapter extends XlsDataAdapter
{
  public ExcelFormatEnum getFormat();
  public void setFormat(ExcelFormatEnum format);
}

Settings inherited from the XlsDataAdapter are presented below:

public interface XlsDataAdapter extends DataAdapter
{
  public String getDatePattern();
  public String getNumberPattern();
  public String getFileName();
  public void setFileName(String filename);
  public boolean isUseFirstRowAsHeader();
  public List<String> getColumnNames();
  public List<Integer> getColumnIndexes();
  public void setColumnNames(List<String> columnNames);
  public void setColumnIndexes(List<Integer> columnIndexes);
  public void setUseFirstRowAsHeader(boolean useFirstRowAsHeader);
  public void setDatePattern(String datePattern);
  public void setNumberPattern(String numberPattern);
  public boolean isQueryExecuterMode();
  public void setQueryExecuterMode(boolean queryExecuterMode);
  public String getSheetSelection();
  public void setSheetSelection(String sheetSelection);
}

All operations required to create and populate the Excel data source are performed in the ExcelDataAdapterService class. The isQueryExecuterMode() setting specifies whether the built-in ExcelQueryExecuter class will be used to prepare the data source. If not set, the data source will be created and populated by the ExcelDataAdapterService.

The Excel Data Adapter Sample

Now we'll see how to configure and use the built-in Excel data adapter in order to obtain a valid data source.

There are 4 distinct configurations in this sample, all of them saved in the data folder:

Below is the content of the ExcelXlsDataAdapter.jrdax:

<?xml version="1.0" encoding="UTF-8"?>
<excelDataAdapter class="net.sf.jasperreports.data.excel.ExcelDataAdapterImpl">
  <name>excel</name>
  <fileName>/data/XlsDataSource.data.xls</fileName>
  <useFirstRowAsHeader>false</useFirstRowAsHeader>
  <queryExecuterMode>false</queryExecuterMode>
  <numberPattern>#,##0</numberPattern>
  <datePattern>yyyy-MM-dd</datePattern>
  <columnNames>city</columnNames>
  <columnNames>id</columnNames>
  <columnNames>name</columnNames>
  <columnNames>address</columnNames>
  <columnNames>state</columnNames>
  <columnNames>date</columnNames>
  <columnIndexes>0</columnIndexes>
  <columnIndexes>2</columnIndexes>
  <columnIndexes>3</columnIndexes>
  <columnIndexes>4</columnIndexes>
  <columnIndexes>5</columnIndexes>
  <columnIndexes>6</columnIndexes>
  <sheetSelection>xlsdatasource2</sheetSelection>
  <format>xls</format>
</excelDataAdapter>

One can see there are 6 columns with their 0-based indexes (0,2,3,4,5,6) and appropriate names: (city, id, name, address, state, date). The second column (ie the one having the index 1) is an empty column, so it can be omitted. Dates are represented using the "yyyy-MM-dd" date pattern and numbers are represented as integer values with the "#,##0" number pattern. The first row in the data file may not be used as column names holder and the data adapter doesn't work in query executer mode.

Data are read from a single sheet named xlsdatasource2. This is the second sheet in the data file.

The other 3 data adapter configurations are set in a similar way, with differences regarding the data file, the query executer mode and the sheet selection.

For each data adapter there is a JRXML file to be compiled, filled and exported to various output formats:

Settings for data adapter are very simple in JRXML files. We need to set the net.sf.jasperreports.data.adapter report property to point to the appropriate data adapter configuration. We also have to define the fields to be picked up from the data source.
For instance, in the ExcelXlsDataAdapterReport.jrxml we have the following settings:

<property name="net.sf.jasperreports.data.adapter" value="/data/ExcelXlsDataAdapter.jrdax"/>
...
<field name="id" class="java.lang.Integer"/>
<field name="name" class="java.lang.String"/>
<field name="address" class="java.lang.String"/>
<field name="city" class="java.lang.String"/>
<field name="state" class="java.lang.String"/>
<field name="date" class="java.util.Date"/>

<sortField name="city" order="Descending"/>
<sortField name="name"/>
...
<filterExpression><![CDATA[$P{IncludedStates}.contains($F{state})]] ></filterExpression>

We can notice that data can be sorted as well as filtered in such a dataset.

Similar settings can be found in the ExcelXlsxDataAdapterReport.jrxml file.

If the data adapter is designed to work in query executer mode (ie <queryExecuterMode>true</queryExecuterMode>), we need to add a query string in the JRXML file. The query language should be set to "excel" (or "EXCEL"). An example can be seen in the ExcelXlsQeDataAdapterReport.jrxml file:

<property name="net.sf.jasperreports.data.adapter" value="/data/ExcelXlsQeDataAdapter.jrdax"/>
...
<query language="excel">
  <![CDATA[]] >
</query>
<field name="id" class="java.lang.Integer"/>
<field name="name" class="java.lang.String"/>
<field name="address" class="java.lang.String"/>
<field name="city" class="java.lang.String"/>
<field name="state" class="java.lang.String"/>
<field name="date" class="java.util.Date"/>

<sortField name="city" order="Descending"/>
<sortField name="name"/>
...
<filterExpression><![CDATA[$P{IncludedStates}.contains($F{state})]] ></filterExpression>

Similar settings can be found in the ExcelXlsxQeDataAdapterReport.jrxml file.

After having all the necessary input prepared as shown above, we can now fill the report. See the fill() method in the src/ExcelDataAdapterApp.java class:

public void fill() throws JRException
{
  long start = System.currentTimeMillis();
  //Preparing parameters
  Map<String, Object> parameters = new HashMap<String, Object>();
  parameters.put("ReportTitle", "Address Report");
  Set<String> states = new HashSet<String>();
  states.add("Active");
  states.add("Trial");
  parameters.put("IncludedStates", states);

  //query executer mode
  parameters.put("DataFile", "XLS query executer mode for Excel data adapter");
  JasperFillManager.fillReportToFile("target/reports/ExcelXlsQeDataAdapterReport.jasper", new HashMap<String, Object>(parameters));
  parameters.put("DataFile", "XLSX query executer mode for Excel data adapter");
  JasperFillManager.fillReportToFile("target/reports/ExcelXlsxQeDataAdapterReport.jasper", new HashMap<String, Object>(parameters));

  JasperReport jasperReport = (JasperReport)JRLoader.loadObjectFromFile("target/reports/ExcelXlsQeDataAdapterReport.jasper");
  jasperReport.setProperty(DataAdapterParameterContributorFactory.PROPERTY_DATA_ADAPTER_LOCATION, "data/XlsQeDataAdapter.jrdax");
  JasperFillManager.fillReportToFile(jasperReport, "target/reports/XlsQeDataAdapterReport.jrprint", new HashMap<String, Object>(parameters));

  jasperReport = (JasperReport)JRLoader.loadObjectFromFile("target/reports/ExcelXlsxQeDataAdapterReport.jasper");
  jasperReport.setProperty(DataAdapterParameterContributorFactory.PROPERTY_DATA_ADAPTER_LOCATION, "data/XlsxQeDataAdapter.jrdax");
  JasperFillManager.fillReportToFile(jasperReport, "target/reports/XlsxQeDataAdapterReport.jrprint", new HashMap<String, Object>(parameters));

  //data source mode
  parameters.put("DataFile", "Excel data adapter for XLS data source");
  JasperFillManager.fillReportToFile("target/reports/ExcelXlsDataAdapterReport.jasper", new HashMap<String, Object>(parameters));
  parameters.put("DataFile", "Excel data adapter for XLSX data source");
  JasperFillManager.fillReportToFile("target/reports/ExcelXlsxDataAdapterReport.jasper", new HashMap<String, Object>(parameters));

  jasperReport = (JasperReport)JRLoader.loadObjectFromFile("target/reports/ExcelXlsDataAdapterReport.jasper");
  jasperReport.setProperty(DataAdapterParameterContributorFactory.PROPERTY_DATA_ADAPTER_LOCATION, "data/XlsDataAdapter.jrdax");
  JasperFillManager.fillReportToFile(jasperReport, "target/reports/XlsDataAdapterReport.jrprint", new HashMap<String, Object>(parameters));

  jasperReport = (JasperReport)JRLoader.loadObjectFromFile("target/reports/ExcelXlsxDataAdapterReport.jasper");
  jasperReport.setProperty(DataAdapterParameterContributorFactory.PROPERTY_DATA_ADAPTER_LOCATION, "data/XlsxDataAdapter.jrdax");
  JasperFillManager.fillReportToFile(jasperReport, "target/reports/XlsxDataAdapterReport.jrprint", new HashMap<String, Object>(parameters));

  System.err.println("Filling time : " + (System.currentTimeMillis() - start));
}

One can notice that there are no data source or connection parameters for the fillReportToFile(...) method. The data adapter will prepare for us the needed data source before filling the report.

Running the Sample

Running the sample requires the Apache Maven library. Make sure that maven is already installed on your system (version 3.6 or later). In a command prompt/terminal window set the current folder to demo/samples/exceldataadapter within the JasperReports source project and run the following command:

> mvn clean compile exec:exec@all

It will generate all supported document types containing the sample report in the demo/samples/exceldataadapter/target/reports directory.



© 2001- Cloud Software Group, Inc. www.jaspersoft.com