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

<sample>

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

  <mainFeature ref="xlsdatasource"/>
  <secondaryFeature name="datasources" sample="datasource" title="Data Sources"/>
  
  <!-- xlsdatasource -->
  
  <feature name="xlsdatasource" title="XLS Data Source">
    <description>
How to fill a report using data from an XLS file.
    </description>
    <since>3.6.1</since>
  	<otherSample ref="datasource"/>
    <content>
<b>XLS 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), the 
<api href="net/sf/jasperreports/engine/data/JRXlsDataSource.html">JRXlsDataSource</api> data source 
implementation can be used to read it and feed it into the report. 
<br/>
The XLS data source uses the <code>JExcelApi</code> library to load the XLS 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).
<br/>
<br/>
<b>XLS Data Source Example</b>
<br/>
<br/>
In our example data records are stored in the /data/XlsDataSource.data.xls file. 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 .xls file. This means 
that column names are set independently, as shown in the 
<code>getDataSource()</code> method in the /src/XlsDataSourceApp.java file: 
<pre><![CDATA[
  private static JRXlsDataSource getDataSource() throws JRException
  {
    JRXlsDataSource ds;
    try
    {
      String[] columnNames = new String[]{"city", "id", "name", "address", "state"};
      int[] columnIndexes = new int[]{0, 2, 3, 4, 5};
      ds = new JRXlsDataSource(JRLoader.getLocationInputStream("data/XlsDataSource.data.xls"));
      ds.setColumnNames(columnNames, columnIndexes);
    }
    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>JRXlsDataSource</code> object prepared above is passed to the engine at fill time (see again the /src/XlsDataSourceApp.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", "XlsDataSource.data.xls - XLS data source");
    Set states = new HashSet();
    states.add("Active");
    states.add("Trial");
    parameters.put("IncludedStates", states);

    JasperFillManager.fillReportToFile("build/reports/XlsDataSourceReport.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}) ? Boolean.TRUE : Boolean.FALSE]]&gt;&lt;/filterExpression&gt;
</code>
<br/>
<br/>
In order to figure out more on XLS data sources, just test this sample by running from the command line the <code>ant test view</code> command. 
It will generate all supported document types containing the sample report in the /build/reports directory, and then the report will be open with JasperReports internal viewer.
    </content>
  </feature>

</sample>
