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.
