How to sum non consecutive cells in jxls template

Question

I am creating a xls report from template that uses data obtained from the Oracle database. I have a group and a subgroup. In a subgroup I create a table that displays the PREMIUM values and then the total using:

$[SUM(I18)]

Because values here are consecutive this formula is converted to i.e. SUM(I18:I35).

After all subgroups are displayed I want to show the total of all PREMIUMS from the subgroups, to do this I use:

$[SUM(I21)]

jxls is smart enough to convert it to i.e. SUM(I36, I46, I67, ...) And this is where I come across a problem. There is some limit as to how many parameters can be passed in to SUM function. Any ideas how can I do the SUM on the collection in xls template?

My template looks like:

<jx:forEach items="${group.items}"  groupBy="client">     
 <jx:forEach items="${group.items}"  groupBy="contract">    
  <jx:forEach items="${group.items}" var="result">   
  PREMIUM  
  ${result.premium}  
  </jx:forEach>  
 Group Total $[SUM(I18)]  
 </jx:forEach>   
 Total $[SUM(I20)] // This is where the problem
                   // is because Group Total are not consecutive 
</jx:forEach> 

I as well tried:

 ${group.items.premium}
 Total $[SUM(I22)]

and made the row with ${group.items.premium} in it hidden but when the template is generated it unhides the rows...

As well if you know a good resource with jsxl tutorial or something similar please post it here. I really struggle to find something descent...

Answer 1

would using an jxls aggregate work for you?

ie. ${sum(premium):group.items} // sum all premiums in each "client" group

<jx:forEach items="${group.items}"  groupBy="client">     
 <jx:forEach items="${group.items}"  groupBy="contract">    
  <jx:forEach items="${group.items}" var="result">   
  PREMIUM  
  ${result.premium}  
  </jx:forEach>  
 Group Total $[SUM(I18)]  
 </jx:forEach>   
 ${sum(premium):group.items} 
</jx:forEach>