poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: How can POI extend the support on excel functions?
Date Thu, 07 Jan 2010 09:00:22 GMT

If you are using OpenOffice to convert the files and it is experiencing
problems recognising some of Excel's function names then I would be loath to
proceed further. I do not know that this is the case but I would suspect
that OpenOffice will rpeserve the function names following the conversion
process, rendering the file unreadable - well unparsable - to Excel.

Yours

Mark B


Lion Liang wrote:
> 
> 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--tp27031517p27056823.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