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

Shows how the XLSX data source implementation could be used to fill reports.
Download All Sample Source Files
Browse Sample Source Files on SVN

Main Features in This Sample
| XLSX Data Source
Secondary Features
| Data Sources

top
| XLSX Data Source | Documented by Sanda Zaharia
| Description / Goal |  | How to fill a report using data from an XLSX file.
| Since |  | 4.0.3
| Other Samples |  |
/demo/samples/datasource
| XLSX Data Sources Report filling is one of the basic operations during the report generation. After the report compilation, report data are read from the report data source, and/or calculated from report expressions, and the generated report sections are filled one by one. Data sources are very useful 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 (XLSX), the JRXlsxDataSource data source implementation can be used to read it and feed it into the report. The XLSX data source uses the `Apache POI` library to load the XLSX workbook and read from it. Instances of this data source can be created by supplying either an in-memory workbook object, a file, or an input stream to read the data from. Report-field mapping for this 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). XLSX Data Source Example In our example data records are stored in the /data/XlsxDataSource.data.xlsx file. It contains the same records as in the CSV Data Source sample, but the `city` and `id` columns are separated by an empty column (ie. records contain in fact 6 fields, but the second field in each record is always empty). There are no column headers in the .xlsx file. This means that column names are set independently, as shown in the `getDataSource()` method in the /src/XlsxDataSourceApp.java file:
      private static JRXlsxDataSource getDataSource() throws JRException
      {
        JRXlsxDataSource ds;
        try
        {
          String[] columnNames = new String[]{"city", "id", "name", "address", "state"};
          int[] columnIndexes = new int[]{0, 2, 3, 4, 5};
          ds = new JRXlsxDataSource(JRLoader.getLocationInputStream("data/XlsxDataSource.data.xlsx"));
          ds.setColumnNames(columnNames, columnIndexes);
        }
        catch (IOException e)
        {
          throw new JRException(e);
        }

        return ds;
      }
Column names are the same as in the CSV example: `city`, `id`, `name`, `address` and `state`. But they are associated with particular column indexes: `0, 2, 3, 4, 5`. The empty column's index (1) is skipped, and doing so, the empty content of the second column will be neglected. The `JRXlsxDataSource` object prepared above is passed to the engine at fill time (see again the /src/XlsxDataSourceApp.java file):
      public void fill() throws JRException
      {
        long start = System.currentTimeMillis();
        //Preparing parameters
        Map parameters = new HashMap();
        parameters.put("ReportTitle", "Address Report");
        parameters.put("DataFile", "XlsxDataSource.data.xlsx - XLSX data source");
        Set states = new HashSet();
        states.add("Active");
        states.add("Trial");
        parameters.put("IncludedStates", states);

        JasperFillManager.fillReportToFile("build/reports/XlsxDataSourceReport.jasper", parameters, getDataSource());
        System.err.println("Filling time : " + (System.currentTimeMillis() - start));
      }
The `IncludedStates` parameter defined above is used for data filtering. Only records with `Active` or `Trial` states will be taken into account: `<parameter name="IncludedStates" class="java.util.Set"/> ... <filterExpression><![CDATA[$P{IncludedStates}.contains($F{state}) ? Boolean.TRUE : Boolean.FALSE]]></filterExpression>` 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/xlsxdatasource` 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/xlsxdatasource/build/reports` directory. Then the report will open in the JasperReports internal viewer.

© 2001- Jaspersoft Corporation www.jaspersoft.com