Dynamic recognition of cell type excel



I have a report that may look like this simplified:

String, String, Integer, Integer, String

Or sometimes, i could change dynamicaly and look like this:

String, String, String, Integer, Integer

Or any other combination.

Is there any way i can get Excel reports to format numbers as numbers and strings as strings?

I can't set the correct datatypes of my textfields in templates, because it is determined on runtime. The only way I see i using the API to replace textfields with designTextfields, which can have their class type set.

Am I missing something, or is this the only way? Any help is very appreciated:)




roarf's picture
Joined: Mar 11 2010 - 2:51am
Last seen: 2 years 6 months ago
Are you use java for export or only iReport? The ways is different...
sanbez - 8 years 3 months ago
Additional question. Which version of JR (iReport) ?
sanbez - 8 years 3 months ago
I design i ireport, but do the export in Java. I use JR 4.0.1, but are willing to upgrade if nescessary.
roarf - 8 years 3 months ago

1 Answer:


You probably need to set the property net.sf.jasperreports.export.xls.detect.cell.type to true on your report.  See here:



kcollins's picture
Joined: Aug 30 2011 - 12:22pm
Last seen: 9 months 1 week ago

Actually.. If you don't know what datatype the fields will be, how do you have them set in the report? I'm pretty sure it just going to determine the cell type based on the datatype of the field, which will remain static after the report is created. How are you dynamically changing the datatypes of the fields in the report?

kcollins - 8 years 3 months ago

That is excatly what I'm wondering how to do. I think "detect cell types" only will read the type of the textfield in the report template, and convert the value to it. What I need is the other way: to read the type of the value, and convert the textfield to it..

roarf - 8 years 3 months ago

It seems to me this is incorrect attempt to solve the problem of bad data (error in design DB?). Each attribute should be have exact type.

sanbez - 8 years 3 months ago
show 3 more...

No, the problem is that the number and order of columns in the report should change dynamically, based on what choices the user makes before generation.

roarf - 8 years 3 months ago

I do not see any solutions. Just rewrite (extend? class) the report generator to specify the exact type of the fields. And after that set "auto detect cell type" (Kris answer).

sanbez - 8 years 3 months ago

Maybe this one would work: try to use 2 separate textfields with valid printWhenExpressions, sharing the same position in the report. Use one of them to render String values only, and the other one for numeric values.

shertage - 8 years 3 months ago