error parsing formula with sheet references - new info

[Chris Cheshire <cc...@bigredwire.com>]

Actually, after experimenting further it's not that it is a sheet 
reference, but the fact that the cell range is an entire column, and 
doesn't contain a cell index.

I changed the formula to COUNT('Credit Cards (Auto)'!C4:C100) + 
COUNT('Credit Cards (Manual)'!C4:C100)

and it worked.

So now the question becomes, can we write formulas that reference an 
entire column like you can in excel, or do I have to include an 
arbitrary number big enough to encompass all my data, just in case?

Thanks

Chris

Chris Cheshire wrote:

> [Sysinfo - Solaris 2.8 x86,  J2SE 1.4.2_03-b02, 
> poi-2.5-final-20040302.jar]
>
> I have a workbook with multiple sheets and I am trying to put a 
> formula on the summary sheet to sum up columns across sheets. The 
> formula is:
> COUNT('Credit Cards (Auto)'!C:C) + COUNT('Credit Cards (Manual)'!C:C)
>
> If I enter this in excel, it works. Using POI to generate the 
> spreadsheet I get the following exception listed at the end of this 
> message.
> I tried changing the sheet names to not have anything but letters, and 
> it still happened. The sheets referenced have nothing in C1 or C2, and 
> a label in C3, then numeric values from then on.
>
> There seemed to be a similar bug year (#25457) but is flagged as fixed 
> in a previous version.
>
> Is this formula actually possible, in which case I should bug it, or 
> am I trying to do something that isn't yet supported?
>
> Thanks
>
> Chris
>
> Exception in thread "main" java.lang.StringIndexOutOfBoundsException: 
> String index out of range: 0
>        at java.lang.String.charAt(String.java:444)
>        at 
> org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:44)
>        at 
> org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:33)
>        at 
> org.apache.poi.hssf.record.formula.Area3DPtg.setArea(Area3DPtg.java:243)
>        at 
> org.apache.poi.hssf.record.formula.Area3DPtg.<init>(Area3DPtg.java:58)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:474)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:548)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596) 
>
>        at 
> org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:443)
>        at 
> org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:315)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:265)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:474)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:548)
>        at 
> org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596) 
>
>        at 
> org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:700)
>        at 
> org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:678)
>        at 
> com.bigredwire.reportgenerator.reports.xls.XLSPayments.writeSummaryData(XLSPayments.java:326) 
>
>        at 
> com.bigredwire.reportgenerator.reports.xls.XLSPayments.buildReport(XLSPayments.java:123) 
>
>        at 
> com.bigredwire.reportgenerator.ReportGenerator.run(ReportGenerator.java:315) 
>
>        at 
> com.bigredwire.reportgenerator.ReportGenerator.main(ReportGenerator.java:176) 
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>



---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org