Return-Path: Delivered-To: apmail-poi-commits-archive@minotaur.apache.org Received: (qmail 29917 invoked from network); 12 Sep 2010 08:55:27 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 12 Sep 2010 08:55:27 -0000 Received: (qmail 86390 invoked by uid 500); 12 Sep 2010 08:55:27 -0000 Delivered-To: apmail-poi-commits-archive@poi.apache.org Received: (qmail 86354 invoked by uid 500); 12 Sep 2010 08:55:26 -0000 Mailing-List: contact commits-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@poi.apache.org Delivered-To: mailing list commits@poi.apache.org Received: (qmail 86347 invoked by uid 99); 12 Sep 2010 08:55:25 -0000 Received: from Unknown (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 12 Sep 2010 08:55:25 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 12 Sep 2010 08:55:07 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id D292B23889E2; Sun, 12 Sep 2010 08:54:45 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r996265 - in /poi/trunk/src: documentation/content/xdocs/ ooxml/java/org/apache/poi/xssf/usermodel/ ooxml/testcases/org/apache/poi/xssf/usermodel/ Date: Sun, 12 Sep 2010 08:54:45 -0000 To: commits@poi.apache.org From: yegor@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100912085445.D292B23889E2@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: yegor Date: Sun Sep 12 08:54:45 2010 New Revision: 996265 URL: http://svn.apache.org/viewvc?rev=996265&view=rev Log: fixed evaluation of shared formulas in XSSF, see Bugzilla 49872 Modified: poi/trunk/src/documentation/content/xdocs/status.xml poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Modified: poi/trunk/src/documentation/content/xdocs/status.xml URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=996265&r1=996264&r2=996265&view=diff ============================================================================== --- poi/trunk/src/documentation/content/xdocs/status.xml (original) +++ poi/trunk/src/documentation/content/xdocs/status.xml Sun Sep 12 08:54:45 2010 @@ -34,6 +34,7 @@ + 49872 - avoid exception in XSSFFormulaEvaluator.evaluateInCell when evaluating shared formulas 49895 - avoid corruption of XSSFWorkbook after removing all merged cells from sheet 49907 - fixed inconsistent behaviour between HSSF and XSSF when creating consecutive names Add getMimeType() method to HWPF Picture, alongside existing file extension Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=996265&r1=996264&r2=996265&view=diff ============================================================================== --- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original) +++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Sun Sep 12 08:54:45 2010 @@ -373,20 +373,27 @@ public final class XSSFCell implements C /** * Creates a non shared formula from the shared formula counterpart * + * @param si Shared Group Index * @return non shared formula created for the given shared formula and this cell */ - private String convertSharedFormula(int idx){ + private String convertSharedFormula(int si){ XSSFSheet sheet = getSheet(); - XSSFCell sfCell = sheet.getSharedFormulaCell(idx); - if(sfCell == null){ - throw new IllegalStateException("Shared Formula not found for group index " + idx); - } - String sharedFormula = sfCell.getCTCell().getF().getStringValue(); + + CTCellFormula f = sheet.getSharedFormula(si); + if(f == null) throw new IllegalStateException( + "Master cell of a shared formula with sid="+si+" was not found"); + + String sharedFormula = f.getStringValue(); + //Range of cells which the shared formula applies to + String sharedFormulaRange = f.getRef(); + + CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange); + int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook()); Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex); Ptg[] fmla = SharedFormulaRecord.convertSharedFormulas(ptgs, - getRowIndex() - sfCell.getRowIndex(), getColumnIndex() - sfCell.getColumnIndex()); + getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn()); return FormulaRenderer.toFormulaString(fpb, fmla); } Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=996265&r1=996264&r2=996265&view=diff ============================================================================== --- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original) +++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Sun Sep 12 08:54:45 2010 @@ -85,7 +85,11 @@ public class XSSFSheet extends POIXMLDoc private List hyperlinks; private ColumnHelper columnHelper; private CommentsTable sheetComments; - private Map sharedFormulas; + /** + * cache of master shared formulas in this sheet. + * Master shared formula is the first formula in a group of shared formulas is saved in the f element. + */ + private Map sharedFormulas; private List arrayFormulas; private XSSFDataValidationHelper dataValidationHelper; @@ -168,7 +172,7 @@ public class XSSFSheet extends POIXMLDoc private void initRows(CTWorksheet worksheet) { _rows = new TreeMap(); - sharedFormulas = new HashMap(); + sharedFormulas = new HashMap(); arrayFormulas = new ArrayList(); for (CTRow row : worksheet.getSheetData().getRowList()) { XSSFRow r = new XSSFRow(row, this); @@ -2401,12 +2405,12 @@ public class XSSFSheet extends POIXMLDoc } /** - * Return a cell holding shared formula by shared group index + * Return a master shared formula by index * * @param sid shared group index - * @return a cell holding shared formula or null if not found + * @return a CTCellFormula bean holding shared formula or null if not found */ - XSSFCell getSharedFormulaCell(int sid){ + CTCellFormula getSharedFormula(int sid){ return sharedFormulas.get(sid); } @@ -2415,7 +2419,9 @@ public class XSSFSheet extends POIXMLDoc CTCell ct = cell.getCTCell(); CTCellFormula f = ct.getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { - sharedFormulas.put((int)f.getSi(), cell); + // save a detached copy to avoid XmlValueDisconnectedException, + // this may happen when the master cell of a shared formula is changed + sharedFormulas.put((int)f.getSi(), (CTCellFormula)f.copy()); } if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) { arrayFormulas.add(CellRangeAddress.valueOf(f.getRef())); Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=996265&r1=996264&r2=996265&view=diff ============================================================================== --- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java (original) +++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Sun Sep 12 08:54:45 2010 @@ -32,4 +32,29 @@ public final class TestXSSFFormulaEvalua public void testSharedFormulas(){ baseTestSharedFormulas("shared_formulas.xlsx"); } + + public void testSharedFormulas_evaluateInCell(){ + XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("49872.xlsx"); + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + XSSFSheet sheet = wb.getSheetAt(0); + + double result = 3.0; + + // B3 is a master shared formula, C3 and D3 don't have the formula written in their f element. + // Instead, the attribute si for a particular cell is used to figure what the formula expression + // should be based on the cell's relative location to the master formula, e.g. + // B3: B1+B2 + // C3 and D3: + + // get B3 and evaluate it in the cell + XSSFCell b3 = sheet.getRow(2).getCell(1); + assertEquals(result, evaluator.evaluateInCell(b3).getNumericCellValue()); + + //at this point the master formula is gone, but we are still able to evaluate dependent cells + XSSFCell c3 = sheet.getRow(2).getCell(2); + assertEquals(result, evaluator.evaluateInCell(c3).getNumericCellValue()); + + XSSFCell d3 = sheet.getRow(2).getCell(3); + assertEquals(result, evaluator.evaluateInCell(d3).getNumericCellValue()); + } } --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org For additional commands, e-mail: commits-help@poi.apache.org