poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jonathan.r.pe...@jpmorgan.com
Subject zero arg functions
Date Tue, 05 Jun 2007 14:25:58 GMT
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.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message