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

    e:formula in excel generation

    oneilltg
      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>
        • 1. Re: e:formula in excel generation
          nickarls

          a) There is currently no row/col variable exposed so you can either keep track of it youself or try switching Excel to R1C1 style (Tools -> Options -> Formula somewhere) and use e.g =RC[-2]+RC[-1] to sum the previous two columns.


          b) Try using a formatMask=accounting_red_float in the cell


          c) Dunno, still editable for me. Have you changed Excel version or something?

          • 2. Re: e:formula in excel generation
            oneilltg
            a) I'll keep track of it myself.

            b) I'm missing something. I tried this: 
            <e:cell value="#{breakDownItem.billed}" formatMask="accounting_red_float" />

            and it had no effect. Does my syntax look good?

            c) I've always used OpenOffice, and I have upgraded it recently. My co-worker uses excel and I had him try it out, and it was read-only for him too. I'll poke around more on this.

            Thanks,
            Tom
            • 3. Re: e:formula in excel generation
              nickarls

              Oops, now I remember. It was changed to css. Try style="xls-format-mask=accounting_red_float"

              • 4. Re: e:formula in excel generation
                oneilltg
                a) FYI, I have the formulas working using a row # I added to the backing data.

                b) still no luck. I tried how you specified it, as well as with a colon instead of the equals.

                <e:formula value="B#{breakDownItem.itemNumber+2}+C#{breakDownItem.itemNumber+2}" style="xls-format-mask: accounting_red_float"/>

                c) FYI, I've noticed that it opens read-only just in firefox. In IE and safari, it opens read-write.
                • 5. Re: e:formula in excel generation
                  nickarls

                  Hmm, I'll have to take a look (the colon is correct). Tried using xls-format-mask on a normal cell (in case it's a formula formatting related bug)? Anything in the console?

                  • 6. Re: e:formula in excel generation
                    oneilltg

                    Using it in a regular e:cell doesn't work for me either. Maybe I just don't understand what the xls-format-mask is supposed to do. My goal is to tell a particular cell that the data is a number and should show two decimal places. Is xls-format-mask the correct approach?


                    Currently, the cells get correctly labeled as being number, but being these are dollars I'm displaying, I want to have 1.50 displayed as 1.50 instead of 1.5 (without the trailing zero). I started by outputting strings, but my formulas can't add strings.

                    • 7. Re: e:formula in excel generation
                      nickarls

                      Hmm, it appears to ignore format masks for others cells than numeric/data currently, I need to rewrite the mask handling to be more generic. Shouldn't be that complicated...

                      • 8. Re: e:formula in excel generation
                        oneilltg

                        Any chance you could post an updated jboss-seam-excel.jar, assuming that is where the fix is?

                        • 9. Re: e:formula in excel generation
                          nickarls

                          It should be in the nightly builds once the commit is made. I'm on vacation this week, though so don't hold your breath.

                          • 10. Re: e:formula in excel generation
                            nickarls

                            Fix candidate for 3976 committed. Now you should be able to use e.g


                            <e:workbook
                                 xmlns:e="http://jboss.com/products/seam/excel"
                                 xmlns:f="http://java.sun.com/jsf/core">
                                 <e:worksheet name="Developers">
                                      <e:cell column="0" row="0" value="1"/>
                                      <e:cell column="1" row="0" value="1"/>
                                      <e:formula column="2" row="0" value="A1+B1" style="xls-format-mask:accounting_red_float"/>
                                 </e:worksheet>
                            </e:workbook>