e:formula in excel generation
oneilltg Feb 13, 2009 7:20 PMHello,
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>
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>