<html xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:jr="http://jasperreports.sourceforge.net/jasperreports">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JasperReports 4.0.2 - XLS Data Source Sample</title>
<style type="text/css">
.title {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 28px;
font-weight: normal;
}
.toc {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.name {
font-family: Courier New, Courier, serif;
font-size: 16px;
font-weight: bold;
}
.label {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: bold;
font-style: italic;
}
.description {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: normal;
}
.value {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.element {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.attribute {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: bold;
}
.code {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.copy {
font-decoration: none;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 8pt;
font-style: normal;
color: #000000;
}
.subtitle {
font-family: inherit;
font-size: inherit;
font-style: inherit;
font-weight: bold;
text-decoration: none;
color: inherit;
}
</style>
</head>
<body bgcolor="#FFFFFF">
<a name="top"></a>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td colspan="2" align="right"><span class="element"><a href="../../sample.reference.html">Sample Reference</a> - <a href="../../schema.reference.html">Schema Reference</a> - <a href="../../config.reference.html">Configuration Reference</a> - <a href="http://jasperreports.sourceforge.net/api/index.html">API (Javadoc)</a></span>
<br>
</td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
<tr valign="middle">
<td nowrap="true"><span class="title">JasperReports - XLS Data Source Sample (version 4.0.2)</span></td><td align="right"><img src="../../resources/jasperreports.png" border="0"></td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
</table>
<br>
<span class="description"><span class="description">Shows how the XLS data source implementation could be used to fill reports.</span></span>
<br>
<br>
<span class="element"><a href="http://sourceforge.net/projects/jasperreports/files/jasperreports/JasperReports%204.0.2/jasperreports-4.0.2-project.zip/download" target="_blank">Download All Sample Source Files</a></span>
<br>
<span class="element"><a href="http://jasperforge.org/scm/viewvc.php/tags/jr-4-0-2/jasperreports/demo/samples/xlsdatasource/?root=jasperreports" target="_blank">Browse Sample Source Files on SVN</a></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 20px;">
<br>
</td><td>
<br>
</td>
</tr>
<tr>
<td colspan="2"><span class="label">Main Features in This Sample</span></td>
</tr>
<tr>
<td>
<br>
</td><td><span class="element"><a href="#xlsdatasource">XLS Data Source</a></span></td>
</tr>
<tr>
<td colspan="2">
<br>
</td>
</tr>
<tr>
<td colspan="2"><span class="label">Secondary Features</span></td>
</tr>
<tr>
<td></td><td><span class="element"><a href="../datasource/index.html#datasources">Data Sources</a></span></td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tr>
<td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td width="80%">
<br>
</td><td width="20%">
<br>
</td>
</tr>
<tr>
<td colspan="6" align="right"><a name="xlsdatasource"></a><a href="#top" class="toc">top</a></td>
</tr>
<tr>
<td colspan="6">
<hr size="1">
</td>
</tr>
<tr valign="top">
<td><img src="../../resources/jr-16x16.png" border="0"></td><td colspan="4"><span class="name">XLS Data Source</span></td><td align="right"><span class="copy">Documented by
<a href="mailto:shertage@users.sourceforge.net" class="copy">Sanda Zaharia</a></span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr valign="top">
<td>
<br>
</td><td nowrap="true"><span class="label">Description / Goal</span></td><td>
<br>
</td><td colspan="3"><span class="description">
How to fill a report using data from an XLS file.
</span></td>
</tr>
<tr valign="top">
<td>
<br>
</td><td colspan="1"><span class="label">Since</span></td><td>
<br>
</td><td colspan="3"><span class="description">3.6.1</span></td>
</tr>
<tr valign="top">
<td>
<br>
</td><td nowrap="true"><span class="label">Other Samples</span></td><td>
<br>
</td><td colspan="3">
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tr>
<td><span class="element"><a href="../datasource/index.html">/demo/samples/datasource</a></span></td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr>
<td>
<br>
</td><td colspan="5"><span class="description">
<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" target="_blank" class="element">Data Sources</a> section.
<br>
When reporting data is stored in Microsoft Excel files (XLS), the
<span class="element"><a href="http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/data/JRXlsDataSource.html" target="_blank">JRXlsDataSource</a></span> data source
implementation can be used to read it and feed it into the report.
<br>
The XLS data source uses the <span class="code"><code>JExcelApi</code></span> 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" target="_blank" class="element">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" target="_blank" class="element">CSV Data Source</a> sample, but the <span class="code"><code>city</code></span> and
<span class="code"><code>id</code></span> 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
<span class="code"><code>getDataSource()</code></span> method in the /src/XlsDataSourceApp.java file:
<pre>
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: <span class="code"><code>city</code></span>, <span class="code"><code>id</code></span>, <span class="code"><code>name</code></span>, <span class="code"><code>address</code></span> and <span class="code"><code>state</code></span>.
But they are associated with particular column indexes: <span class="code"><code>0, 2, 3, 4, 5</code></span>. The empty column's index (1) is skipped, and doing so,
the empty content of the second column will be neglected.
<br>
The <span class="code"><code>JRXlsDataSource</code></span> object prepared above is passed to the engine at fill time (see again the /src/XlsDataSourceApp.java file):
<pre>
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 <span class="code"><code>IncludedStates</code></span> parameter defined above is used for data filtering. Only records with <span class="code"><code>Active</code></span>
or <span class="code"><code>Trial</code></span> states will be taken into account:
<br>
<br>
<span class="code"><code>
<parameter name="IncludedStates" class="java.util.Set"/>
<br>
<br>
...
<br>
<br>
<filterExpression><![CDATA[$P{IncludedStates}.contains($F{state}) ? Boolean.TRUE : Boolean.FALSE]]></filterExpression>
</code></span>
<br>
<br>
<b>Running the Sample</b>
<br>
<br>
Running the sample requires the <a href="http://ant.apache.org/" target="_blank" class="element">Apache Ant</a> library. Make sure that <span class="code"><code>ant</code></span> is already installed on your system (version 1.5 or later).
<br>
In a command prompt/terminal window set the current folder to <span class="code"><code>demo/samples/xlsdatasource</code></span> within the JasperReports source project and run the <span class="code"><code>> ant test view</code></span> command.
<br>
It will generate all supported document types containing the sample report in the <span class="code"><code>demo/samples/xlsdatasource/build/reports</code></span> directory.
<br>
Then the report will open in the JasperReports internal viewer.
</span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
</table>
<br>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td>
<hr size="1">
</td>
</tr>
<tr>
<td align="center"><span class="copy">© 2001-2010 Jaspersoft Corporation <a href="http://www.jaspersoft.com" target="_blank" class="copy">www.jaspersoft.com</a></span></td>
</tr>
</table>
</body>
</html>
