Bug 61869: updating a cell with shared formula produces an unreadable file

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1818818 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 46 insertions, 6 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
index 574cecfc1c..941e03711c 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -558,21 +558,30 @@ public final class XSSFCell implements Cell {
         XSSFWorkbook wb = _row.getSheet().getWorkbook();
         if (formula == null) {
             wb.onDeleteFormula(this);
-            if(_cell.isSetF()) {
+            if (_cell.isSetF()) {
                 _cell.unsetF();
             }
             return;
         }
 
-        if(wb.getCellFormulaValidation()) {
+        if (wb.getCellFormulaValidation()) {
             XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
             //validate through the FormulaParser
             FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex());
         }
 
-        CTCellFormula f = CTCellFormula.Factory.newInstance();
-        f.setStringValue(formula);
-        _cell.setF(f);
+        CTCellFormula f;
+        if (_cell.isSetF()) {
+            f = _cell.getF();
+            f.setStringValue(formula);
+            if(f.getT() == STCellFormulaType.SHARED){
+                getRow().getSheet().onReadCell(this);
+            }
+        } else {
+            f = CTCellFormula.Factory.newInstance();
+            f.setStringValue(formula);
+            _cell.setF(f);
+        }
         if(_cell.isSetV()) {
             _cell.unsetV();
         }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
index 01cd880332..0735456e5c 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
@@ -48,6 +48,7 @@ import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.model.SharedStringsTable;
 import org.junit.Test;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
 
 import static org.junit.Assert.*;
@@ -670,4 +671,34 @@ public final class TestXSSFCell extends BaseTestXCell {
         
         destCell.setCellValue(true);
     }
-}
+
+    /**
+     * Bug 61869: updating a shared formula produces an unreadable file
+     */
+    @Test
+    public void test61869() throws Exception {
+        try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("61869.xlsx")) {
+            XSSFSheet sheet = wb.getSheetAt(0);
+            XSSFCell c2 = sheet.getRow(1).getCell(2);
+            assertEquals("SUM(A2,B2)", c2.getCellFormula());
+            assertEquals(STCellFormulaType.SHARED, c2.getCTCell().getF().getT());
+            assertEquals(0, c2.getCTCell().getF().getSi());
+            XSSFCell c3 = sheet.getRow(2).getCell(2);
+            assertEquals(STCellFormulaType.SHARED, c3.getCTCell().getF().getT());
+            assertEquals(0, c3.getCTCell().getF().getSi());
+            assertEquals("SUM(A3,B3)", c3.getCellFormula());
+
+            assertEquals("SUM(A2,B2)", sheet.getSharedFormula(0).getStringValue());
+
+            c2.setCellFormula("SUM(A2:B2)");
+            assertEquals(STCellFormulaType.SHARED, c2.getCTCell().getF().getT()); // c2 remains the master formula
+
+            assertEquals("SUM(A2:B2)", sheet.getSharedFormula(0).getStringValue());
+            assertEquals(STCellFormulaType.SHARED, c3.getCTCell().getF().getT());
+            assertEquals(0, c3.getCTCell().getF().getSi());
+            assertEquals("SUM(A3:B3)", c3.getCellFormula());  // formula in the follower cell is rebuilt
+
+        }
+
+    }
+}
\ No newline at end of file
diff --git a/test-data/spreadsheet/61869.xlsx b/test-data/spreadsheet/61869.xlsx
new file mode 100644
index 0000000000..bc55a3a1c7
--- /dev/null
+++ b/test-data/spreadsheet/61869.xlsx