[Logo] SpagoWorld Forums
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing  
[Groups] Back to home page  [Moderation Log] Moderation Log   [Register] Register /  [Login] Login 
Jasper to excel number values are not recognized as numbers  XML
Forum Index » Technical Help - SpagoBI Server - REPORTING
Author Message
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi,

I tested this both on SpagoBI 2.7 and 2.8 and I guess I need to change some settings but I don't know which ones.

If I export a QBE report to excel or openoffice numbers are recognized fine. Now i've created a jasper with a crosstab and I exported it to excel and openoffice and it doesn't recognize the numbers.

I've used multiple browsers, both Ubuntu and Windows7, I've changed my language from nl to en-us. Nothing helped so far.

Anyone any clue?

Thanks in advance,
Freek
angioia

SpagoBI hero
[Avatar]

Joined: 12/04/2010 15:41:11
Messages: 562
Location: Milan, Italy
Offline

I have just tested on our online demo and it works (bidemo/bidemo ; menu qbe->pivoting). Can you check it and let me know.

Regards,
AG

Andrea Gioia
-------------------------
@andrea_gioia
@geobinitiative
-------------------------
[WWW]
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi,

Sorry for the misunderstanding, but as I wrote exporting from QBE works fine, but if I create a Jasper report and export from there it goes wrong.

I've taken the menu option 'Reporting', 'Salary by state - JR' and also there the numbers are in excel starting with a quote.

Best regards,
Freek
angioia

SpagoBI hero
[Avatar]

Joined: 12/04/2010 15:41:11
Messages: 562
Location: Milan, Italy
Offline

Ok, sorry. The problem is in the jasperreport xls exporter. Better the problem is in the way the exporter is configurated. Just add a file named jasperreport.properties in folder \webapps\SpagoBIJasperReportEngine\WEB-INF\classes with the following content ...



the key line is "net.sf.jasperreports.export.xls.detect.cell.type=true" because by default it is set to false.

Restart the server and let me know.

AG

This message was edited 1 time. Last update was at 26/04/2011 13:45:48


Andrea Gioia
-------------------------
@andrea_gioia
@geobinitiative
-------------------------
[WWW]
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi,

Thanks for that.

Two results:
1) On SpagoBI 2.7:
It did not help on the numeric values, BUT I at least have been able to remove the empty rows.

I installed the following file:

# XLS exporter settings
net.sf.jasperreports.export.xls.create.custom.palette=false
net.sf.jasperreports.export.xls.one.page.per.sheet=false
net.sf.jasperreports.export.xls.remove.empty.space.between.rows=true
net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true
net.sf.jasperreports.export.xls.white.page.background=true
net.sf.jasperreports.export.xls.detect.cell.type=true
net.sf.jasperreports.export.xls.size.fix.enabled=false
net.sf.jasperreports.export.xls.ignore.graphics=false
net.sf.jasperreports.export.xls.collapse.row.span=false
net.sf.jasperreports.export.xls.ignore.cell.border=false
net.sf.jasperreports.export.xls.ignore.cell.background=false
net.sf.jasperreports.export.xls.max.rows.per.sheet=0
#net.sf.jasperreports.export.xls.password=pa22w0rd
net.sf.jasperreports.export.xls.wrap.text=true

2) On SpagoBI 2.8:
It works.....

Unfortunately I can not use this version at my customers yet due to some outstanding issues.

So thanks.. On this topic (so 2.7) nothing more needed. I just hope that the other topics will be closed soon for me so that I can move forward to 2.8.

You're a star.

Best regards,
Freek
pietrotower.spw


[Avatar]

Joined: 14/09/2012 10:24:16
Messages: 7
Location: Milan, Italy
Offline

Hi,
i am using SpagoBI 3.4.0 and I have the same problem. I have already tried your tips but they didn't work.
any suggestions?

thanks.
txmy.spw



Joined: 13/12/2011 11:18:30
Messages: 6
Offline

Hi,
i am using SpagoBI 3.6.0 and I have the same problem.
I have already tried your tips but they didn't work.
Any suggestions?

Thanks.
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi I've tested this on SpagoBI 3.6 and it works perfectly. Perhaps you have not restarted the tomcat server?

Best regards,

Freek
pietrotower.spw


[Avatar]

Joined: 14/09/2012 10:24:16
Messages: 7
Location: Milan, Italy
Offline

freekb.spw wrote:Hi I've tested this on SpagoBI 3.6 and it works perfectly. Perhaps you have not restarted the tomcat server?

Best regards,

Freek


Hi,
I've just tried to export an excel file from the SpagoBI 3.6 demo. Even with those options set (as anyone said on this topic, and with tomcat restarted) nothing works fine and this is still an issue.
Can you explain the steps that you followed to do it (since you don't seem to be affected by this problem) ?

The problem is: I need a way to recognize numeric content in the excel file resulting from the export automatically.

When I export a report in excel, the numeric field are not recognized from excel and are treated as text.
More specifically, you can't use the function "SUM()" for those fields because they are ignored (this happens when a field is text and is included in a numeric function in excel).
On the other hand, the strange thing is: if in a cell you put "=A1+A2" (let's say that those cells contain the data of the field affected by this error) you get a numeric result!! meaning: excel is capable of understanding that these cells contain numbers, even if they are text at the moment. But the problem is that I need to use the "SUM()" function in the resulting excel file!

In order to be more accurate I also want to specify that the export operation from iReport (from which I make the jasperreports) works fine and excel recognize the data type. It seems that you can specify the option "autodetect cell type" of the excel export format in iReport, in addition to the jasperreports option mentioned above.
Could this matter be connected to the problem ? In other terms, is that possible that exporting excel from spagoBI doesn't handle well datatype because you are able only to specify the jasperreports option but there is not any option in SpagoBI to do what the option integrated in iReport does?

thanks in advance.
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi,

I've changed the file:
/usr/local/tomcat/webapps/SpagoBIJasperReportEngine/WEB-INF/classes/jasperreports.properties with the content described before. I've restarted my server and it works fine. If I export I can do sums, etc, etc.

Best regards,
Freek
pietrotower.spw


[Avatar]

Joined: 14/09/2012 10:24:16
Messages: 7
Location: Milan, Italy
Offline

freekb.spw wrote:Hi,

I've changed the file:
/usr/local/tomcat/webapps/SpagoBIJasperReportEngine/WEB-INF/classes/jasperreports.properties with the content described before. I've restarted my server and it works fine. If I export I can do sums, etc, etc.

Best regards,
Freek


Hi again,
I have some questions for you:
- what version of excel do you have?
- did you set any specific options in excel to read datatype?
- what is the datatype of the field in the originary report before you export? (numeric/bigdecimal/integer, also specify the precision if possible)

thanks
bhushankable.spw



Joined: 05/12/2011 16:37:57
Messages: 5
Offline

I am using 3.4 and tried to use the above trick and restart the server but its not working for me. What version of Excel are you using ? did you tried both integer and decimals ?
freekb.spw

SpagoBI supporter

Joined: 08/07/2010 09:46:26
Messages: 152
Offline

Hi,
I'm using excel 2007 but even on open-office, which I'm using most of the time, it's not a problem. So I expect the problem to be somewhere in your setup of the file.
Best regards,
Freek
bhushankable.spw



Joined: 05/12/2011 16:37:57
Messages: 5
Offline

Thanks for the reply. I recheck all the step and redid everything again but still no luck.

Here is what I am doing.
Step 1 : Copy the Properties to the jasperreports.properties file @ location : spago-3.4/webapps/SpagoBIJasperReportEngine/WEB-INF/classes
Step 2 : Restart the Server @ Location : spago-3.4/bin run : ./shutdown.sh followed by ./startup.sh
Step 3 : Going back to UI and running the report . The report is designed in IReport (Jasper Report tool).
The datatype of the field I was expecting to be number is defined as Double and the Report Language is Groovy.
Step 4 : Export report to Excel after its been executed using the Exporters. The worksheet I am using is Excel 97-2003 (can this be the issue)

Properties :

net.sf.jasperreports.awt.ignore.missing.font=true
# XLS exporter settings
net.sf.jasperreports.export.xls.create.custom.palette=false
net.sf.jasperreports.export.xls.one.page.per.sheet=false
net.sf.jasperreports.export.xls.remove.empty.space.between.rows=true
net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true
net.sf.jasperreports.export.xls.white.page.background=true
net.sf.jasperreports.export.xls.detect.cell.type=true
net.sf.jasperreports.export.xls.size.fix.enabled=false
net.sf.jasperreports.export.xls.ignore.graphics=false
net.sf.jasperreports.export.xls.collapse.row.span=false
net.sf.jasperreports.export.xls.ignore.cell.border=false
net.sf.jasperreports.export.xls.ignore.cell.background=false
# net.sf.jasperreports.export.xls.max.rows.per.sheet=0
# net.sf.jasperreports.export.xls.password=pa22w0rd
net.sf.jasperreports.export.xls.wrap.text=true
 
Forum Index » Technical Help - SpagoBI Server - REPORTING
Go to:   
Powered by JForum 2.1.9 © JForum Team