poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
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 GMT
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 @@
 
     <changes>
         <release version="3.7-beta3" date="2010-??-??">
+           <action dev="poi-developers" type="fix">49872 - avoid exception in XSSFFormulaEvaluator.evaluateInCell
when evaluating shared formulas</action>
            <action dev="poi-developers" type="fix">49895 - avoid corruption of XSSFWorkbook
after removing all merged cells from sheet</action>
            <action dev="poi-developers" type="fix">49907 - fixed inconsistent behaviour
between HSSF and XSSF when creating consecutive names</action>
            <action dev="poi-developers" type="add">Add getMimeType() method to HWPF
Picture, alongside existing file extension</action>

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<XSSFHyperlink> hyperlinks;
     private ColumnHelper columnHelper;
     private CommentsTable sheetComments;
-    private Map<Integer, XSSFCell> 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<Integer, CTCellFormula> sharedFormulas;
     private List<CellRangeAddress> arrayFormulas;
     private XSSFDataValidationHelper dataValidationHelper;    
 
@@ -168,7 +172,7 @@ public class XSSFSheet extends POIXMLDoc
 
     private void initRows(CTWorksheet worksheet) {
         _rows = new TreeMap<Integer, XSSFRow>();
-        sharedFormulas = new HashMap<Integer, XSSFCell>();
+        sharedFormulas = new HashMap<Integer, CTCellFormula>();
         arrayFormulas = new ArrayList<CellRangeAddress>();
         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 <code>null</code> if not found
+     * @return a CTCellFormula bean holding shared formula or <code>null</code>
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:        <f t="shared" ref="B3:D3" si="0">B1+B2</f>
+        // C3 and D3: <f t="shared" si="0"/>
+
+        // 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


Mime
View raw message