How to create an Excel data source in JasperReports Server?

2

Hi, I am fairly new to JasperServer, and I am trying to deploy a new report to JasperReports Server 5.0.0 using an Excel data source. I successfully connected my Excel file as a data source to create the report using iReport, but I do not understand how to add that Excel file to JasperServer so that I can connect it to the report on the server. When I am logged into JasperServer using the default admin accout, "jasperadmin," I right-click on "Data Sources" on the left side of the screen and then I select "Add Resource > JasperReport."  Then I see this page:

 

Click for Larger View

 

I select my local JRXML file and type the name "testPieChart," and then I select the "Data Source" button on the left, where you set up the data source connection. Then, I select the option to "Click here to create a new data source" which then brings me to this page: 

 

Click for a Larger View

 

Under the drop-down menu for "type" there are options for 

  • Bean Data Source
  • Hadoop-Hive Data Source
  • Internal Diagnostic Data Source
  • JDBC Data Source
  • JNDI Data Source
  • Mongo DB Data Source
  • Virtual Data Source

So, how to I create an Excel data source? Do I use one of the above types? I really need to use Excel files as data sources for my application so any help would be extremely appreciated.

Thanks!

Edit - I'm sorry about the formatting of the screen shots

 

baseballstorm50's picture
Joined: Dec 13 2012 - 5:16pm
Last seen: 6 years 3 months ago

5 Answers:

8

Hi, here are the proper steps to resolve this issue:

1. Get your xls file (data should be stored in columns, smth like JR table report) and rename it to xls_data.xml

2. Upload it to the server with the name xls_data into /reports folder

3. Create xls_data_provider.xml, like:

<?xml version="1.0" encoding="UTF-8"?>
<xlsDataAdapter class="net.sf.jasperreports.data.xls.XlsDataAdapterImpl">
<name>ExcelData</name>
 <fileName>repo:/reports/xls_data</fileName>
<useFirstRowAsHeader>true</useFirstRowAsHeader>
<queryExecuterMode>false</queryExecuterMode>
<datePattern>yyyy-MM-dd HH:mm:ss</datePattern>
<columnNames>city</columnNames>
<columnNames>country</columnNames>
<columnNames>state</columnNames>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">0</columnIndexes>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">1</columnIndexes>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">2</columnIndexes>
</xlsDataAdapter>
 
Note that all your xml-tags should be in CamelCase as it is defined in net.sf.jasperreports.data.xls.XlsDataAdapterImpl. Also note that your xls data file should contain the first row with column names and those names should be the same as defined in <columnNames> tags
 
4. Upload your xls_data_provider.xml as xls_data_provider into the /reports folder
5. Create report in iReport or JSS using XLS file (the original one). After all fields were added view report as xml and add few more lines:
 
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ec8d891c-4188-4de7-b77f-71d1440b831f">
<property name="net.sf.jasperreports.data.adapter" value="repo:/reports/xls_data_provider "/>
<queryString language="xls">
<![CDATA[]]>
</queryString>
<field name="city" class="java.lang.String"/>
<field name="country" class="java.lang.String"/>

6. In JRS goto Add new report and use previously created jrxml, don't select any datasource in wizard

It's done, now you should be able to run report.

Yuri Savochenko's picture
Joined: May 6 2009 - 10:39am
Last seen: 5 months 1 week ago

1. Please make sure you have "jasperserver-datasources-file-wSOAP-<version>" in your hand.

2. copy jasperserver-datasources-file-wSOAP-<version>\webapp\WEB-INF directory and paste it into {js}\apache-tomcat\webapps\jasperserver-ws-sample-pro.

3. Restart jasperserver service.

4. Enjoy it.

Sokhay,

sokhay-jcgroup - 5 years 10 months ago

Hi,
I'm getting the following error while trying the above steps,
Can anyone help.

Error Message

com.jaspersoft.jasperserver.api.JSExceptionWrapper: jxl.read.biff.BiffException: Unable to recognize OLE stream

Error Trace

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: jxl.read.biff.BiffException: Unable to recognize OLE stream at net.sf.jasperreports.engine.data.JRXlsDataSource.<init>(JRXlsDataSource.java:105) at net.sf.jasperreports.engine.data.JRXlsDataSource.<init>(JRXlsDataSource.java:129) at net.sf.jasperreports.data.xls.XlsDataAdapterService.contributeParameters(XlsDataAdapterService.java:100) at

ADR707 - 5 years 9 months ago

Hi,

I'm still struggling with this..

Regarding steps 2 and 4 - could you please tell me where exactly I should upload the XML files (to "/reports")?

I am running Jasperserver locally, so I can find a "/reports" folder here, amongst other places:

C:\Jaspersoft\jasperreports-server-6.3.0\buildomatic\install_resources\export\js-catalog\resources\public\audit\reports

But after putting the XML files here I don't see anything different in the Jasperserver web interface.

And regarding step 6, I have my JRXML file here:

C:\Jaspersoft\jasperreports-server-6.3.0\buildomatic\install_resources\export\js-catalog\resources\public\templates\

But after entering it as the Custom Report Template, I get an error that:

"The repository URI /public/templates/ABC.jrxml is not a report template. "

Sorry if these are simple questions - I am trying to create prototypes using CSV data for a comparison of four different BI tools, and I am struggling quite a bit with Jaspersoft.

Thanks for any help!

jjtuson - 2 years 8 months ago
4

To use flat files as a datasource (like XLS files) you need to use DataAdapters in Jasperserver instead of regular datasources. Data Adapters are part of JasperSoft Studio but you can create them manually in the server (it's just and XML file). You have some examples using CSV as a data adapter in the standard Jasper Server Samples (under /Reports/Interactive).

For using XLS your data adapter has to be something like this:


<!--?xml version="1.0" encoding="UTF-8" ?-->
<xlsdataadapter class="net.sf.jasperreports.data.xls.XlsDataAdapterImpl"></xlsdataadapter>
<name>ExcelData</name>
<filename>/path/of/your/excelfile.xls</filename>
<usefirstrowasheader>true</usefirstrowasheader>
<datepattern>M/d/yy h:mm a</datepattern>
<numberpattern>#,##0.###</numberpattern>
<queryexecutermode>false</queryexecutermode>
<columnnames>Column1Name</columnnames>
	<columnnames>Column2Name</columnnames>
	<columnnames>Column3Name</columnnames>
	<columnnames>Column4Name</columnnames>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">0</columnIndexes>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">1</columnIndexes>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">2</columnIndexes>
<columnIndexes xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:type="java:java.lang.Integer">3</columnIndexes>
 
And then point your report to this data adapter by setting the net.sf.jasperreports.data.adapter property to point to the repository path of your Data Adapter (i.e. >property name="net.sf.jasperreports.data.adapter" value="repo:excel_data_adapter.xml"
marianol's picture
4337
Joined: Sep 13 2011 - 8:04am
Last seen: 7 months 3 weeks ago

The site messed up my XML those "< a href=" are not part of the file structure

marianol - 6 years 4 months ago

Thank you! I have added the XML file, but I do not quite understand the last part of your comment. Do you mean that I add those attributes to the 'xlsdataadapter' tag of the XML file (i.e. property name="net.sf.jasperreports.data.adapter" value="repo:/reports/Test/XLS_Data_Adapter.xml") ?

Also does that mean that under the "Data Source" section of my report I will select "Do not link a data source"? Should I put any information about this data adapter under the "Controls & Resources" section?

baseballstorm50 - 6 years 4 months ago
0

 

Thank you Marianol. I have added the XML file, but I do not quite understand the last part of your comment. Do you mean that I add those attributes to the 'xlsdataadapter' tag of the XML file (i.e. property name="net.sf.jasperreports.data.adapter" value="repo:/reports/Test/XLS_Data_Adapter.xml") ?

Also does that mean that under the "Data Source" section of my report I will select "Do not link a data source"? Should I put any information about this data adapter under the "Controls & Resources" section?

baseballstorm50's picture
Joined: Dec 13 2012 - 5:16pm
Last seen: 6 years 3 months ago
0

Hi Marianol,

I followed your instructions but got the following error:
 

Mensaje de error

net.sf.jasperreports.engine.JRRuntimeException: org.exolab.castor.xml.MarshalException: The class for the root element 'xlsdataadapter' could not be found.{File: [not available]; line: 2; column: 76}

Traza de error

net.sf.jasperreports.engine.JRRuntimeException: org.exolab.castor.xml.MarshalException: The class for the root element 'xlsdataadapter' could not be found.{File: [not available]; line: 2; column: 76} at net.sf.jasperreports.util.CastorUtil.read(CastorUtil.java:263) at net.sf.jasperreports.repo.CastorObjectPersistenceService.load(CastorObjectPersistenceService.java:75) at net.sf.jasperreports.repo.CastorDataAdapterPersistenceService.load(CastorDataAdapterPersistenceService.java:62) at net.sf.jasperreports.repo.DefaultRepositoryService.getResource(DefaultRepositoryService.java:187) at

....

How could I fix it?

Thank you!

 

ajandradem's picture
Joined: May 23 2012 - 7:17am
Last seen: 2 months 1 week ago
0

Hi All,

did anyone got this working?

 

lalitha.nov20's picture
Joined: Feb 5 2013 - 8:47pm
Last seen: 6 years 1 week ago

hi Lalitha,

I got this working by following the steps in kmkdz's and marianol's answers.
Initially I got a "jrruntimeexception org.exolab.castor.xml.MarshalException" with the message "The processing instruction target matching "[xX][mM][lL]" is not allowed". Turned out there was a blank space at the begining of my xml document.
After that I got a "jxl.read.biff.biffexception Unable to recognize OLE stream" which got solved by converting the .xlsx(Excel 2007/2010 format) to .xls(Excel 97-2003 format).
Hope this helps!

Best

bhavi.kruti - 4 years 11 months ago
Feedback