title: JasperReports: Export a xls/ods file with formulas
body: <p>Recently, I had to export some reports using JasperReports under ods /xls format. </p>
  
  <p>The export works fine but I didn't manage to find a way to add some excel formulas between the exported columns (let's simplify it by saying a sum of some columns as described below). </p>
  
  <p><a href="https://i.stack.imgur.com/uXORP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uXORP.png" alt="Screenshot"></a></p>
  
  <p>When user modifies the column A in the excel file, column C gets modified (basic excel formulas, nothing new...)</p>
  
  <p>Based on some comments and answers here is what I've done  </p>
  
  <pre><code>&lt;detail&gt;
  &lt;band height="134" splitType="Stretch"&gt;
      &lt;componentElement&gt;
              &lt;reportElement x="-20" y="0" width="120" height="60" uuid="884b6c49-9006-464d-982e-e2a5f2cb3e3e"&gt;
                  &lt;property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/&gt;
              &lt;/reportElement&gt;
              &lt;jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd"&gt;
                  &lt;datasetRun subDataset="Empty Dataset1" uuid="b60c3bee-0624-4a6a-bbb1-e706521c0a9a"&gt;
                      &lt;dataSourceExpression&gt;&lt;![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource(java.util.Arrays.asList($F{dto}.getNbLgtA()))]]&gt;&lt;/dataSourceExpression&gt;
                  &lt;/datasetRun&gt;
                  &lt;jr:column width="40" uuid="1dbb56d0-f25f-410b-9e02-d08b7fe84388"&gt;
                      &lt;property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne1"/&gt;
                      &lt;jr:tableHeader height="30"&gt;
                          &lt;staticText&gt;
                              &lt;reportElement x="0" y="0" width="40" height="30" uuid="ff8f394e-39ea-4d08-898f-601e92e6d1f3"/&gt;
                              &lt;text&gt;&lt;![CDATA[A]]&gt;&lt;/text&gt;
                          &lt;/staticText&gt;
                      &lt;/jr:tableHeader&gt;
                      &lt;jr:detailCell height="30"&gt;
                          &lt;staticText&gt;
                              &lt;reportElement x="0" y="0" width="40" height="30" uuid="c0c2b023-2d00-4080-88a3-a73f19fe9cda"/&gt;
                              &lt;text&gt;&lt;![CDATA[1]]&gt;&lt;/text&gt;
                          &lt;/staticText&gt;
                      &lt;/jr:detailCell&gt;
                  &lt;/jr:column&gt;
                  &lt;jr:column width="40" uuid="33f41b08-3d77-4076-9712-9514211dd3af"&gt;
                      &lt;property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne2"/&gt;
                      &lt;jr:tableHeader height="30"&gt;
                          &lt;staticText&gt;
                              &lt;reportElement x="0" y="0" width="40" height="30" uuid="ad2b74aa-95bf-43d7-83a4-6528f344b410"/&gt;
                              &lt;text&gt;&lt;![CDATA[B]]&gt;&lt;/text&gt;
                          &lt;/staticText&gt;
                      &lt;/jr:tableHeader&gt;
                      &lt;jr:detailCell height="30"&gt;
                          &lt;staticText&gt;
                              &lt;reportElement x="0" y="0" width="40" height="30" uuid="6d121f9e-e92d-45ba-9a48-82bf8b1245e0"/&gt;
                              &lt;text&gt;&lt;![CDATA[2]]&gt;&lt;/text&gt;
                          &lt;/staticText&gt;
                      &lt;/jr:detailCell&gt;
                  &lt;/jr:column&gt;
                  &lt;jr:column width="40" uuid="42d367d1-2be7-46bf-838b-ba4c1c6f3399"&gt;
                      &lt;property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne3"/&gt;
                      &lt;jr:tableHeader height="30"&gt;
                          &lt;staticText&gt;
                              &lt;reportElement x="0" y="0" width="40" height="30" uuid="63e3dd54-0297-463f-b061-4c536baf62a8"/&gt;
                              &lt;text&gt;&lt;![CDATA[C]]&gt;&lt;/text&gt;
                          &lt;/staticText&gt;
                      &lt;/jr:tableHeader&gt;
                      &lt;jr:detailCell height="30"&gt;
                          &lt;property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2, B2)"/&gt;
                      &lt;/jr:detailCell&gt;
                  &lt;/jr:column&gt;
              &lt;/jr:table&gt;
          &lt;/componentElement&gt;
      &lt;/band&gt;
  &lt;/detail&gt;
  </code></pre>
  
  <p>But it doesn't work.</p>