<?xml version="1.0" encoding="UTF-8"?>

<sample>

  <name>exceldatasource</name>
  <title>Excel Data Source Sample</title>
  <description>Shows how the Excel data source implementation could be used to fill reports.</description>

  <mainFeature ref="exceldatasource"/>
  <secondaryFeature name="datasources" sample="datasource" title="Data Sources"/>
  
  <!-- exceldatasource -->
  
  <feature name="exceldatasource" title="Excel Data Source">
    <description>
How to fill a report using data from an Excel file.
    </description>
    <since>3.6.1</since>
    <documentedBy>
      <author>
    	<name>Sanda Zaharia</name>
    	<email>shertage@users.sourceforge.net</email>
      </author>
    </documentedBy>
  	<otherSample ref="datasource"/>
    <content>
<b>Excel Data Sources</b>
<br/>
<br/>
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. 
<br/>
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 <a href="../datasources.html#datasources">Data Sources</a> section.
<br/>
When reporting data is stored in Microsoft Excel files (XLS or XLSX), the 
<api href="net/sf/jasperreports/engine/data/ExcelDataSource.html">ExcelDataSource</api> data source 
implementation can be used to read it and feed it into the report. 
<br/>
The Excel data source uses the <code>Apache POI</code> library to load the Excel 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. 
<br/>
Report-field mapping for this data source implementation is very similar to the CSV data 
source field-mapping explained in the <a href="../csvdatasource">CSV Data Source</a> sample. It works on the assumption that 
the workbook contains data in a tabular form (rows are records and columns contain 
report-field values). The main difference is that one can specify a given sheet to be used as single sheet data source using the 
<api href="net/sf/jasperreports/engine/query/AbstractXlsQueryExecuterFactory.html#XLS_SHEET_SELECTION">XLS_SHEET_SELECTION</api> parameter 
or report property.
<br/>
<br/>
<b>Excel Data Source Example</b>
<br/>
<br/>
In our example data records are stored in the /data/MultisheetXlsDataSource.data.xls file and the MultisheetXlsxDataSource.data.xlsx file
respectively, depending on the Excel file format (XLS vs XLSX). It contains the same records as in the <a href="../csvdatasource">CSV Data Source</a> sample, 
but the <code>city</code> and <code>id</code> columns are separated by an empty column (ie. records contain in fact 6 fields, but 
the second field in each record is always empty).
<br/>
There are no column headers in the Excel files. This means 
that column names are set independently, as shown in the 
<code>getDataSource()</code> method in the /src/ExcelDataSourceApp.java file: 
<pre><![CDATA[
  private static ExcelDataSource getDataSource() throws JRException
  {
    ExcelDataSource ds;
    try
    {
      String[] columnNames = new String[]{"city", "id", "name", "address", "state"};
      int[] columnIndexes = new int[]{0, 2, 3, 4, 5};
      ds = new ExcelDataSource(JRLoader.getLocationInputStream("data/MultisheetXlsxDataSource.data.xlsx"));
      ds.setColumnNames(columnNames, columnIndexes);
      
      //uncomment the below line to see how sheet selection works
      //ds.setSheetSelection("Data Sheet 2");
    }
    catch (IOException e)
    {
      throw new JRException(e);
    }

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

    JasperFillManager.fillReportToFile("build/reports/ExcelDataSourceReport.jasper", parameters, getDataSource());
    System.err.println("Filling time : " + (System.currentTimeMillis() - start));
  }
]]></pre>
The <code>IncludedStates</code> parameter defined above is used for data filtering. Only records with <code>Active</code> 
or <code>Trial</code> states will be taken into account:
<br/>
<br/>
<code>
&#160;&#160;&lt;parameter name="IncludedStates" class="java.util.Set"/&gt;
<br/>
<br/>
&#160;&#160;...
<br/>
<br/>
&#160;&#160;&lt;filterExpression&gt;&lt;![CDATA[$P{IncludedStates}.contains($F{state})]]&gt;&lt;/filterExpression&gt;
</code>
<br/>
<br/>
<b>Running the Sample</b>
<br/>
<br/>
Running the sample requires the <a href="http://ant.apache.org/">Apache Ant</a> library. Make sure that <code>ant</code> is already installed on your system (version 1.5 or later).
<br/>
In a command prompt/terminal window set the current folder to <code>demo/samples/exceldatasource</code> within the JasperReports source project and run the <code>&gt; ant test view</code> command.
<br/>
It will generate all supported document types containing the sample report in the <code>demo/samples/exceldatasource/build/reports</code> directory. 
<br/>
Then the report will open in the JasperReports internal viewer.
    </content>
  </feature>

</sample>
