<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>