How to generate Excel formulas in 3.7.1 ?

[glassner]

 

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 !

[slow]

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

[glassner]

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]

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 ?

[slow]

"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

[glassner]

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]

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. ! ;-))

[slow]

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

[glassner]

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 ?

[slow]

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

[glassner]

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.  

[slow]

try to set:

Preview--->XLS Preview (using JExcelAPI)

[glassner]

FUNZIONA !!!! IT WORKS !!!!

 

[slow]

Ottimo :)

have you other karma points to give me? :)

is it  for a university research study?

[glassner]

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 ...

 

 

 

[shivaseshendra]

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

[oleksandr.voloschuk]

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