JasperReports Ultimate Guide - Sample Reference - Schema Reference - Configuration Reference - API (Javadoc)
JasperReports - Excel Data Adapter Sample (version 5.6.0) |

Shows how the Excel data adapters can be used to fill reports.
Download All Sample Source Files
Browse Sample Source Files on SVN

Main Features in This Sample
| Excel Data Adapter
Secondary Features
| XLS Data Source
| XLSX Data Source

top
| 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/xlsdatasource
/demo/samples/xlsxdatasource
| 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 object that stores the Excel data. This may be: an Excel data file saved on the disk. Can be set using the EXCEL_FILE parameter. a `java.io.InputStream` object. Can be set using the EXCEL_INPUT_STREAM parameter. an in-memory Excel workbook object. Can be set using the EXCEL_WORKBOOK parameter. a path to the location of an Excel data file. Can be set using the EXCEL_SOURCE parameter or report property. the internal format of the Excel document. Can be set using the EXCEL_FORMAT parameter or report property. Allowed values for the Excel format property are enumerated in the ExcelFormatEnum class: `xls` - for Excel 2003 and older documents `xlsx` - for Excel 2007 and newer documents `autodetect` - this is the default value. If `autodetect` is set, the engine will try to detect the format based on the internal structure of the document the number pattern of numeric columns. Can be set using either EXCEL_NUMBER_FORMAT parameter or EXCEL_NUMBER_PATTERN parameter/report property. the date pattern of date columns. Can be set using either EXCEL_DATE_FORMAT parameter or EXCEL_DATE_PATTERN parameter/report property. whether the first row in the data file should be used to provide the column names. Can be set using the EXCEL_USE_FIRST_ROW_AS_HEADER parameter. if column names are not specified in the first row of the data file, they have to be specified along with their column indexes. Report-field mapping for the data source implementation is very similar to the CSV data source field-mapping explained in the CSV Data Source sample. It works on the assumption that the workbook contains data in a tabular form (rows are records and columns contain report-field values). Column names can be set using either EXCEL_COLUMN_NAMES_ARRAY parameter or EXCEL_COLUMN_NAMES parameter/report property. Column indexes can be set using either EXCEL_COLUMN_INDEXES_ARRAY parameter or EXCEL_COLUMN_INDEXES parameter/report property. optionally, the name of the sheet in the Excel document to be used as single sheet data source. If not provided, data records will be collected from all sheets in the document. The Excel sheet can be specified using the XLS_SHEET_SELECTION parameter or report property. the `java.util.Locale` to be used when reading data. Can be set using either EXCEL_LOCALE parameter or EXCEL_LOCALE_CODE parameter/report property. the `java.util.TimeZone` to be used when reading data. Can be set using either EXCEL_TIMEZONE parameter or EXCEL_TIMEZONE_ID parameter/report property. 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. First, we need to set the appropriate JasperReports registry extension. In the `src/jasperreports_extension.properties` file we have to put the following setting: `net.sf.jasperreports.extension.registry.factory.parameter.contributor.data.adapter=net.sf.jasperreports.data.DataAdapterParameterContributorExtensionsRegistryFactory` Then we have to configure the data adapter. There are 4 distinct configurations in this sample, all of them saved in the `data` folder: `ExcelXlsDataAdapter.xml` - reads data from a XLS data file (see `data/XlsDataSource.data.xls` Excel file) and works in direct data source mode `ExcelXlsQeDataAdapter.xml` - reads data from the same XLS data file, but works in query executer mode `ExcelXlsxDataAdapter.xml` - reads data from a XLSX data file (see `data/XlsxDataSource.data.xlsx` Excel file) and works in direct data source mode `ExcelXlsxQeDataAdapter.xml` - reads data from the same XLSX data file, but works in query executer mode Below is the content of the `ExcelXlsDataAdapter.xml`:
    <?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: `reports/ExcelXlsDataAdapterReport.jrxml` - uses the `ExcelXlsDataAdapter.xml` that works in direct data source mode `reports/ExcelXlsQeDataAdapterReport.jrxml` - uses the `ExcelXlsQeDataAdapter.xml` that works in query executer mode `reports/ExcelXlsxDataAdapterReport.jrxml` - uses the `ExcelXlsxDataAdapter.xml` that works in direct data source mode `reports/ExcelXlsxQeDataAdapterReport.jrxml` - uses the `ExcelXlsxQeDataAdapter.xml` that works in query executer mode 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.xml"/>
    ...
    <field name="id" class="java.lang.Integer">
      <fieldDescription><![CDATA[id]] ></fieldDescription>
    </field>
    <field name="name" class="java.lang.String">
      <fieldDescription><![CDATA[name]] ></fieldDescription>
    </field>
    <field name="address" class="java.lang.String">
      <fieldDescription><![CDATA[street address]] ></fieldDescription>
    </field>
    <field name="city" class="java.lang.String">
      <fieldDescription><![CDATA[city]] ></fieldDescription>
    </field>
    <field name="state" class="java.lang.String">
      <fieldDescription><![CDATA[state]] ></fieldDescription>
    </field>
    <field name="date" class="java.util.Date">
      <fieldDescription><![CDATA[date]] ></fieldDescription>
    </field>

    <sortField name="city" order="Descending"/>
    <sortField name="name"/>
    ...
    <filterExpression><![CDATA[$P{IncludedStates}.contains($F{state}) ? Boolean.TRUE : Boolean.FALSE]] ></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.xml"/>
    ...
    <queryString language="excel">
      <![CDATA[]] >
    </queryString>
    <field name="id" class="java.lang.Integer">
      <fieldDescription><![CDATA[id]] ></fieldDescription>
    </field>
    <field name="name" class="java.lang.String">
      <fieldDescription><![CDATA[name]] ></fieldDescription>
    </field>
    <field name="address" class="java.lang.String">
      <fieldDescription><![CDATA[street address]] ></fieldDescription>
    </field>
    <field name="city" class="java.lang.String">
      <fieldDescription><![CDATA[city]] ></fieldDescription>
    </field>
    <field name="state" class="java.lang.String">
      <fieldDescription><![CDATA[state]] ></fieldDescription>
    </field>
    <field name="date" class="java.util.Date">
      <fieldDescription><![CDATA[date]] ></fieldDescription>
    </field>

    <sortField name="city" order="Descending"/>
    <sortField name="name"/>
    ...
    <filterExpression><![CDATA[$P{IncludedStates}.contains($F{state}) ? Boolean.TRUE : Boolean.FALSE]] ></filterExpression>
Similar settings can be found in the `ExcelXlsxQeDataAdapterReport.jrxml` file. After having all the necessary stuff 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
    {
      ...
      //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("build/reports/ExcelXlsQeDataAdapterReport.jasper", parameters);
      parameters.put("DataFile", "XLSX query executer mode for Excel data adapter");
      JasperFillManager.fillReportToFile("build/reports/ExcelXlsxQeDataAdapterReport.jasper", parameters);

      //data source mode
      parameters.put("DataFile", "Excel data adapter for XLS data source");
      JasperFillManager.fillReportToFile("build/reports/ExcelXlsDataAdapterReport.jasper", parameters);
      parameters.put("DataFile", "Excel data adapter for XLSX data source");
      JasperFillManager.fillReportToFile("build/reports/ExcelXlsxDataAdapterReport.jasper", parameters);

      ...
    }
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 Ant library. Make sure that `ant` is already installed on your system (version 1.5 or later). In a command prompt/terminal window set the current folder to `demo/samples/exceldataadapter` within the JasperReports source project and run the `> ant test view` command. It will generate all supported document types containing the sample report in the `demo/samples/exceldataadapter/build/reports` directory. Then the `ExcelXlsDataAdapter` report will open in the JasperReports internal viewer.

© 2001- Jaspersoft Corporation www.jaspersoft.com