Return-Path: Delivered-To: apmail-poi-user-archive@www.apache.org Received: (qmail 8521 invoked from network); 7 Jan 2010 03:11:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 7 Jan 2010 03:11:33 -0000 Received: (qmail 62782 invoked by uid 500); 7 Jan 2010 03:11:33 -0000 Delivered-To: apmail-poi-user-archive@poi.apache.org Received: (qmail 62701 invoked by uid 500); 7 Jan 2010 03:11:32 -0000 Mailing-List: contact user-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: "POI Users List" Delivered-To: mailing list user@poi.apache.org Received: (qmail 62691 invoked by uid 99); 7 Jan 2010 03:11:32 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Jan 2010 03:11:32 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates 216.139.236.158 as permitted sender) Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Jan 2010 03:11:23 +0000 Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1NSim5-000593-Q4 for user@poi.apache.org; Wed, 06 Jan 2010 19:11:01 -0800 Message-ID: <27031517.post@talk.nabble.com> Date: Wed, 6 Jan 2010 19:11:01 -0800 (PST) From: Lion Liang To: user@poi.apache.org Subject: How can POI extend the support on excel functions? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: liangyue_829@163.com X-Virus-Checked: Checked by ClamAV on apache.org 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