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: [email protected] For additional commands, e-mail: [email protected]
