I have a requirement to open the POI generated Microsoft native Excel
spreadsheet in Open Office, there is a function called "DATEDIF" supported
by Excel but not Open Office, it has a replacement function in Open Office
called "MONTHS" conversely which is not supported Excel.
When generating the spreasheet by POI:
targetCell.setCellFormula(sourceCell.getCellFormula());
it throws:
org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Attempt to
use name 'MONTHS' as a function, but defined name in workbook does not refer
to a function
at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:495)
at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:603)
at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:603)
at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:603)
at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:506)
at
org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:302)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:671)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:631)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:618)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:920)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1001)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:985)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:942)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1043)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:165)
at
org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:47)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:593)
at PartIncremental.copyRow(PartIncremental.java:147)
at PartIncremental.main(PartIncremental.java:45)
I am trying to add reference of "MONTHS" to POI source code by:
1. append
poi-3.2-FINAL\src\resources\main\org\apache\poi\hssf\record\formula\function\functionMetadata.txt
with this function info:
MONTHS 3 3 V V V V
2. modify
poi-src-3.2-FINAL-20081019\poi-3.2-FINAL\src\java\org\apache\poi\hssf\record\formula\eval\FunctionEval.java
with this :
retval[368] = new Datedif(); // MONTHS
After these changes program exits without any exception, but when the new
generated spreadsheet is opened in Open Office, and with #NAME! in the
formula.
I might not do the enough source changes to have POI support this new
function "MONTHS", can someone help on this?
Also, anyone has idea to replace DATEDIF/MONTHS with a formula consist of
simple functions are welcomed, I think this will also solve my issue. Thanks
a lot.
--
View this message in context:
http://old.nabble.com/How-can-POI-extend-the-support-on-excel-functions--tp27031517p27031517.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
