10 Replies Latest reply on Feb 26, 2009 10:09 PM by Nicklas Karlsson

    e:formula in excel generation

    Tom ONeill Newbie
      Hello,
      I am generating an excel spreadsheet that has four columns: description, amount, amount, and total (sum of the two amounts). And the last row is the sum of the amounts in each column. I want to use e:formula to generate the totals, so that if the user changes a value in a cell, it is reflected in the total. Currently I just use the backing bean to compute the total at the time of xls generation. My problem is knowing what row I am on so I can specify the formula.

      I want to do something like:       <e:formula value="B3+C3" />
      but I can't hardcode the "3" because it needs to increment for each line. I could add a "rowNum" to each object in my list, but I hope that it is available to me already.

      Also, how can I specify the mask for a cell? I want to use ACCOUNTING_RED_FLOAT but I have no idea how to tell it that in my xhtml.

      And one last thing... my spreadsheet always opens in read-only mode. With the preview excel with seam 2.0 it was always read-write. How can I change that? I specified workbookProtected="false", but no help

      Thanks very much!
      Tom

      Here is what I have, btw.

      <html xmlns="http://www.w3.org/1999/xhtml"
           xmlns:ui="http://java.sun.com/jsf/facelets"
           xmlns:e="http://jboss.com/products/seam/excel"
           xmlns:f="http://java.sun.com/jsf/core">

      <e:workbook type="jxl" workbookProtected="false">

           <e:worksheet sheetProtected="false"
                       automaticFormulaCalculation="true"
                       startRow="1"
                       value="#{billTypeBreakDownBB.billingBreakDown.breakDownItems}"
                       var="breakDownItem">
              <e:column style="xls-column-autosize: true">
                     <f:facet name="header">
                          <e:cell value="Source" style="xls-font: red bold 10 Verdana; xls-alignment: centre"/>
                     </f:facet>
                     <e:cell value="#{breakDownItem.source}" />
                  <f:facet name="footer">
                          <e:cell value="Total" style="xls-font: red bold 10 Verdana"/>
                     </f:facet>
                </e:column>
              <e:column style="xls-column-autosize: true">
                     <f:facet name="header">
                          <e:cell value="Billed" style="xls-font: red bold 10 Verdana; xls-alignment: centre"/>
                     </f:facet>
                     <e:cell value="#{breakDownItem.billed}" style="xls-alignment: right" />
                  <f:facet name="footer">
                          <e:cell value="#{billTypeBreakDownBB.billingBreakDown.billedTotal}" style="xls-font: red bold 10 Verdana; xls-alignment: right"/>
                     </f:facet>
                </e:column>
              <e:column style="xls-column-autosize: true">
                     <f:facet name="header">
                          <e:cell value="Not Billed" style="xls-font: red bold 10 Verdana; xls-alignment: centre"/>
                     </f:facet>
                     <e:cell value="#{breakDownItem.noBilled}"  style="xls-alignment: right" />
                  <f:facet name="footer">
                          <e:cell value="#{billTypeBreakDownBB.billingBreakDown.noBilledTotal}" style="xls-font: red bold 10 Verdana; xls-alignment: right"/>
                     </f:facet>
                </e:column>
              <e:column style="xls-column-autosize: true">
                     <f:facet name="header">
                          <e:cell value="Total" style="xls-font: red bold 10 Verdana; xls-alignment: centre"/>
                     </f:facet>
                     <e:cell value="#{breakDownItem.total}" style="xls-alignment: right"/>
                  <f:facet name="footer">
                          <e:cell value="#{billTypeBreakDownBB.billingBreakDown.total}" style="xls-font: red bold 10 Verdana; xls-alignment: right"/>
                     </f:facet>
                </e:column>


           </e:worksheet>
      </e:workbook>
      </html>