You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by jo...@jpmorgan.com on 2007/06/05 16:25:58 UTC

zero arg functions

I am using POI to evaluate formula. I have sucessfully implemented some of 
the missing date functions, such as DAY, MONTH, YEAR. However I am unable 
to implement TODAY() and NOW(). I get this exception when I try and 
evaluate

java.lang.IllegalArgumentException: Invalid Formula cell reference: 
'TODAY'
        at org.apache.poi.hssf.util.CellReference.<init>(
CellReference.java:48)
        at org.apache.poi.hssf.record.formula.ReferencePtg.<init>(
ReferencePtg.java:54)
        at org.apache.poi.hssf.model.FormulaParser.Ident(
FormulaParser.java:317)
        at org.apache.poi.hssf.model.FormulaParser.Factor(
FormulaParser.java:505)
        at org.apache.poi.hssf.model.FormulaParser.Term(
FormulaParser.java:606)
        at org.apache.poi.hssf.model.FormulaParser.Expression(
FormulaParser.java:654)
        at org.apache.poi.hssf.model.FormulaParser.parse(
FormulaParser.java:758)
        at 
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(
HSSFFormulaEvaluator.java:296)
        at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(
HSSFFormulaEvaluator.java:192)
        at com.jpm.rsp.excel.ExcelUtils.evaluate(ExcelUtils.java:59)

It seems that it is being interpretted as a cell reference and not a 
function. I tried evaluating PI(), which is implemented in the latest 
release. This give exactly the same exception. Is there a general problem 
with zero arg functions? Here is some example code

e.g.

  public static void main(String[] args) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
    HSSFRow row = sheet.createRow(0);
    evaluator.setCurrentRow(row);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellFormula("PI()");
    CellValue value = evaluator.evaluate(cell);
    System.err.println(value.getNumberValue());
  }

Any ideas?

Jon

This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase & Co., its subsidiaries and affiliates.

This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by JPMorgan Chase & Co., its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for disclosures relating to UK legal entities.

Re: zero arg functions

Posted by de...@yahoo.com.
Jonathan,

I will look into this in a couple of days (I just moved and
haven't got the internet set up at home yet :( )

>From the stack trace it seems like an issue with the
Formula parser - should take a few hours to fix and
regression test.


Regards,
~ amol


--- jonathan.r.perry@jpmorgan.com wrote:

> I am using POI to evaluate formula. I have sucessfully
> implemented some of 
> the missing date functions, such as DAY, MONTH, YEAR.
> However I am unable 
> to implement TODAY() and NOW(). I get this exception when
> I try and 
> evaluate
> 
> java.lang.IllegalArgumentException: Invalid Formula cell
> reference: 
> 'TODAY'
>         at org.apache.poi.hssf.util.CellReference.<init>(
> CellReference.java:48)
>         at
> org.apache.poi.hssf.record.formula.ReferencePtg.<init>(
> ReferencePtg.java:54)
>         at org.apache.poi.hssf.model.FormulaParser.Ident(
> FormulaParser.java:317)
>         at
> org.apache.poi.hssf.model.FormulaParser.Factor(
> FormulaParser.java:505)
>         at org.apache.poi.hssf.model.FormulaParser.Term(
> FormulaParser.java:606)
>         at
> org.apache.poi.hssf.model.FormulaParser.Expression(
> FormulaParser.java:654)
>         at org.apache.poi.hssf.model.FormulaParser.parse(
> FormulaParser.java:758)
>         at 
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(
> HSSFFormulaEvaluator.java:296)
>         at
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(
> HSSFFormulaEvaluator.java:192)
>         at
> com.jpm.rsp.excel.ExcelUtils.evaluate(ExcelUtils.java:59)
> 
> It seems that it is being interpretted as a cell
> reference and not a 
> function. I tried evaluating PI(), which is implemented
> in the latest 
> release. This give exactly the same exception. Is there a
> general problem 
> with zero arg functions? Here is some example code
> 
> e.g.
> 
>   public static void main(String[] args) {
>     HSSFWorkbook wb = new HSSFWorkbook();
>     HSSFSheet sheet = wb.createSheet();
>     HSSFFormulaEvaluator evaluator = new
> HSSFFormulaEvaluator(sheet, wb);
>     HSSFRow row = sheet.createRow(0);
>     evaluator.setCurrentRow(row);
>     HSSFCell cell = row.createCell((short) 0);
>     cell.setCellFormula("PI()");
>     CellValue value = evaluator.evaluate(cell);
>     System.err.println(value.getNumberValue());
>   }
> 
> Any ideas?
> 
> Jon
> 
> This communication is for informational purposes only. It
> is not intended as an offer or solicitation for the
> purchase or sale of any financial instrument or as an
> official confirmation of any transaction. All market
> prices, data and other information are not warranted as
> to completeness or accuracy and are subject to change
> without notice. Any comments or statements made herein do
> not necessarily reflect those of JPMorgan Chase & Co.,
> its subsidiaries and affiliates.
> 
> This transmission may contain information that is
> privileged, confidential, legally privileged, and/or
> exempt from disclosure under applicable law. If you are
> not the intended recipient, you are hereby notified that
> any disclosure, copying, distribution, or use of the
> information contained herein (including any reliance
> thereon) is STRICTLY PROHIBITED. Although this
> transmission and any attachments are believed to be free
> of any virus or other defect that might affect any
> computer system into which it is received and opened, it
> is the responsibility of the recipient to ensure that it
> is virus free and no responsibility is accepted by
> JPMorgan Chase & Co., its subsidiaries and affiliates, as
> applicable, for any loss or damage arising in any way
> from its use. If you received this transmission in error,
> please immediately contact the sender and destroy the
> material in its entirety, whether in electronic or hard
> copy format. Thank you.
> Please refer to http://www.jpmorgan.com/pages/disclosures
> for disclosures relating to UK legal entities.
> 


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