How to generate Excel formulas in 3.7.1 ?

 

All I have been able to find on how we can use Excel formulas in JasperReports is in the link below and it refers to pre 3.0.0 versions. How is it done now ? My excel report does not show formula al all and I have not been able to find any documentation. Thanks !

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=44205&page=1

 

Thanks !

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

15 Answers:

To use a dynamic formula in this case, please refer to the article -> Exporting Crosstab Formula "SUM" To Excel

oleksandr.voloschuk's picture
Joined: Nov 24 2017 - 6:35am
Last seen: 4 years 5 months ago

set:

tools-->options-->iReport-->export option-->excel-->AutoDetectCellType = TRUE

 

for each texfield you want be a formula in excel set:

properties-->properties expression-->add----> ADD:

net.sf.jasperreports.export.xls.formula as name

a formula (without =) (example: SUM(A1,A2) ) as value

 

pay attention to use the correct cell type (String, Integer etc) and cell name in formula (A1,A2...) etc.

 

 

_________________________________________

if it works... give me KARMA points please!    : )  
_________________________________________

 

 

 listening: Underworld - Beautiful Burnout

 

 



Post Edited by slow at 05/11/2010 09:26
slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

I would be very glad to give you all the karma points I can. but it does not work  yet.

I have a very simple report that counts the number of records starting with a letter combination. The report shows rows of last_names and counts for that lastName letter combination. The last row is simply the total ($V{Total Count) of the counts per lastname. Please show me how the property value should be set then. The versions I have tried do not work. I am sorry, I would like to have a good reference/documentation on this, but I have found little or no info. 

 <field name="LAST_NAME" class="java.lang.String">
  <fieldDescription><![CDATA[]]></fieldDescription>
 </field>
 <field name="COUNT(*)" class="java.lang.Integer">
  <fieldDescription><![CDATA[]]></fieldDescription>
 </field>
 <variable name="Total Count" class="java.lang.Integer" calculation="Sum">
  <variableExpression><![CDATA[$F{COUNT(*)}]]></variableExpression>
 </variable>

<textField isBlankWhenNull="true">
    <reportElement isPrintRepeatedValues="false" x="158" y="0" width="100" height="20">
     <property name="net.sf.jasperreports.export.xls.formula" value="SUM($F{COUNT(*))"/>
    </reportElement>
    <textElement/>
    <textFieldExpression class="java.lang.Integer"><![CDATA[$V{Total Count}]]></textFieldExpression>
   </textField>

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

Obviously, one would like to have a dynamic way of setting formula to an excel cell. How does one know in what cell the counts will be, and to sum, say, cells between A1:A500 or cells between C2:C250 ?! I assume in setting the property value we can use the names of the variables/fields we want to sum, right ?

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

"I assume in setting the property value we can use the names of the variables/fields we want to sum, right ?"

NO, i think... :)

i think you're confusing the use of varible to sum values, how to diplay that variable and the exporting excel formula...

in your example why export a formula if you have the sum to display?

or... if you want to display formula... you must think in "excel mode"... not in iReport one...  so you must set formula as: SUM(A1,a7)

so, when i use formula, i export it referencing the excel cells name that i know only after a preview of the report in excel format.

 

 



Post Edited by slow at 05/11/2010 12:32
slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

I did as you suggested. It just does not work ...., the total cell is not a formula when I export it in Excel. Do you see anything wrong with this xml snipped ?

 

<textField isBlankWhenNull="true">
    <reportElement isPrintRepeatedValues="false" x="118" y="0" width="100" height="20">
     <property name="net.sf.jasperreports.export.xls.formula" value="SUM(C2:C5)"/>
    </reportElement>
    <textElement/>
    <textFieldExpression class="java.lang.Integer"><![CDATA[$V{Total Count}]]></textFieldExpression>
   </textField>

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

Your example works, but the propertyExpression lien is completely different form mine, and I do not understand why instead of a simple SUM(B2:B5) you need to have a reference to   SUM(B2:B"+($V{REPORT_COUNT}.intValue()+1. I do not undestand what that means.

I am trying to imitate it, unsuccessfully. This is very maddening. It disappoints me that iReport does not have adequate documention on excel formatting. I have given you karma points, I hope you are happy with those. ! ;-))

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

when you write:
SUM(B2:B5)  you know that you want sum cells from B2 to B5...

writing "SUM(B2:B"+($V{REPORT_COUNT}.intValue()+1)+")"
I sum cells from B2 to BN, where N is a number equal to the number of record of the detail band ($V{REPORT_COUNT}) plus one (+1)(the one cell occupied by the column header).
(i don't know the N value when i write the report code... so writing that makes the report generic)

it' a simple trick create to make your example dinamic... it's not mandatory.

 

Thanks for karma... :)

I guess a lot of things for work...so I read the small details...  :(



Post Edited by slow at 05/11/2010 13:31
slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

hi.. how is this applicable in terms of cross tab ?

shivaseshendra - 10 years 3 months ago

I'll continue the conversation in English to benefit others. I tried your example, but the total I get in the excel report I generate from your jrxml file is simply 0 (zero). instead if I place the cursor over teh excel you uploaded, then there is =SUM(B2:B10).

There must be something odd in my iReport set up. Is there a specific library I need to import ?

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

goodmorning...

have you set

tools-->options-->iReport-->export option-->excel-->AutoDetectCellType = TRUE   ?

I suppose you haven't

:)

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________


listening: Pixies- Where is my mind

 

 

 



Post Edited by slow at 05/12/2010 09:11
slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

unfortunately, yes. AutoDetectCellType = true. Still same issue. As I said, I think there must be something in my iReport set up (such as a library) or maybe Excel version. Your jrxml, which works fine for you, does not print the formula for me. And it is the exact same jrxml. I am at a loss here.  

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

try to set:

Preview--->XLS Preview (using JExcelAPI)

slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

FUNZIONA !!!! IT WORKS !!!!

 

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago

Ottimo :)

have you other karma points to give me? :)

is it  for a university research study?

slow's picture
7525
Joined: Feb 17 2010 - 6:57am
Last seen: 13 years 7 months ago

slow
Wrote:
 

when you write:
SUM(B2:B5)  you know that you want sum cells from B2 to B5...

writing "SUM(B2:B"+($V{REPORT_COUNT}.intValue()+1)+")"
I sum cells from B2 to BN, where N is a number equal to the number of record of the detail band ($V{REPORT_COUNT}) plus one (+1)(the one cell occupied by the column header).
(i don't know the N value when i write the report code... so writing that makes the report generic)

it' a simple trick create to make your example dinamic... it's not mandatory.

 

Thanks for karma... :)

I guess a lot of things for work...so I read the small details...  :(



Post Edited by slow at 05/11/2010 13:31

 

How do we apply this to a crosstab context ? How do we replace "B" ? In a crosstab, in fact, we do not know whetehr the formula will end in column B or C or D ... ; also, we want to compute totals in each column ...

 

 

 

glassner's picture
1719
Joined: Mar 5 2010 - 9:57am
Last seen: 13 years 7 months ago
Feedback
randomness