poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lion Liang <liangyue_...@163.com>
Subject How can POI extend the support on excel functions?
Date Thu, 07 Jan 2010 03:11:01 GMT

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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message