baseballstorm50 Posted January 15, 2013 Share Posted January 15, 2013 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: 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: 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 SourceSo, 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 Link to comment Share on other sites More sharing options...
marianol Posted January 15, 2013 Share Posted January 15, 2013 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 More sharing options...
marianol Posted January 15, 2013 Share Posted January 15, 2013 The site messed up my XML those "< a href=" are not part of the file structure Link to comment Share on other sites More sharing options...
baseballstorm50 Posted January 16, 2013 Author Share Posted January 16, 2013 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 More sharing options...
baseballstorm50 Posted January 23, 2013 Author Share Posted January 23, 2013 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 More sharing options...
ajandradem Posted March 11, 2013 Share Posted March 11, 2013 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 More sharing options...
lalitha.nov20 Posted April 30, 2013 Share Posted April 30, 2013 Hi All,did anyone got this working? Link to comment Share on other sites More sharing options...
Solution Yuri Savochenko Posted July 5, 2013 Solution Share Posted July 5, 2013 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.xml2. Upload it to the server with the name xls_data into /reports folder3. 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 folder5. 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 wizardIt's done, now you should be able to run report. Link to comment Share on other sites More sharing options...
sokhay-jcgroup Posted July 23, 2013 Share Posted July 23, 2013 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 More sharing options...
ADR707 Posted August 23, 2013 Share Posted August 23, 2013 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 More sharing options...
bhavi.kruti Posted June 5, 2014 Share Posted June 5, 2014 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 More sharing options...
jjtuson Posted September 2, 2016 Share Posted September 2, 2016 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now