Jump to content
Changes to the Jaspersoft community edition download ×

How to create an Excel data source in JasperReports Server?


baseballstorm50
Go to solution Solved by Yuri Savochenko,

Recommended Posts

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:

 

JasperServer2.PNG.c284138e506b71ae028d4e3666911f17.PNG

 

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: 

 

JasperServer3.PNG.d2e5a3dde02dd0071ae378ba35c50b79.PNG

 

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

 

JasperServer2.PNG.65eb4286d584a020e64b4f5c7c3489f5.PNG

JasperServer3.PNG.61a84b312a087f3dd77953b703d38d90.PNG

Link to comment
Share on other sites

  • Replies 11
  • Created
  • Last Reply

Top Posters In This Topic

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:


ExcelData/path/of/your/excelfile.xlstrueM/d/yy h:mm a#,##0.###falseColumn1Name	Column2Name	Column3Name	Column4Name0123

 
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"<)
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

  • 1 month later...

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!

 

Link to comment
Share on other sites

  • 1 month later...
  • 2 months later...
  • Solution

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.

Link to comment
Share on other sites

  • 3 weeks later...

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

 

2. copy jasperserver-datasources-file-wSOAP-webappWEB-INF directory and paste it into {js}apache-tomcatwebappsjasperserver-ws-sample-pro.

 

3. Restart jasperserver service.

 

4. Enjoy it.

 

Sokhay,

Link to comment
Share on other sites

  • 1 month later...

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.(JRXlsDataSource.java:105) at net.sf.jasperreports.engine.data.JRXlsDataSource.(JRXlsDataSource.java:129) at net.sf.jasperreports.data.xls.XlsDataAdapterService.contributeParameters(XlsDataAdapterService.java:100) at

Link to comment
Share on other sites

  • 9 months later...

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

 

Link to comment
Share on other sites

  • 2 years later...

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:Jaspersoftjasperreports-server-6.3.0buildomaticinstall_resourcesexportjs-catalogresourcespublicauditreports

 

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:Jaspersoftjasperreports-server-6.3.0buildomaticinstall_resourcesexportjs-catalogresourcespublictemplates

 

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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...