1. added support for shared formulas in XSSF, see bug #464332. improved validation of workbook-global and sheet-global names

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

4 files changed, 107 insertions, 28 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 bb3aafe07a..53db069108 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -24,16 +24,21 @@ import java.util.Date;
 
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
 import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.SharedFormulaRecord;
 import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.formula.FormulaParser;
 import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.FormulaRenderer;
 import org.apache.poi.xssf.model.StylesTable;
 import org.apache.poi.xssf.model.SharedStringsTable;
 import org.apache.poi.POIXMLException;
+import org.apache.poi.util.POILogger;
+import org.apache.poi.util.POILogFactory;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
 
 /**
  * High level representation of a cell in a row of a spreadsheet.
@@ -50,6 +55,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
  * </p>
  */
 public final class XSSFCell implements Cell {
+    private static POILogger logger = POILogFactory.getLogger(XSSFCell.class);
 
     /**
      * The maximum  number of columns in SpreadsheetML
@@ -108,7 +114,7 @@ public final class XSSFCell implements Cell {
     protected SharedStringsTable getSharedStringSource() {
         return sharedStringSource;
     }
-    
+
     /**
      * @return table of cell styles shared across this workbook
      */
@@ -122,8 +128,8 @@ public final class XSSFCell implements Cell {
      * @return the sheet this cell belongs to
      */
     public XSSFSheet getSheet() {
-		return getRow().getSheet();
-	}
+        return getRow().getSheet();
+    }
 
     /**
      * Returns the row this cell belongs to
@@ -131,8 +137,8 @@ public final class XSSFCell implements Cell {
      * @return the row this cell belongs to
      */
     public XSSFRow getRow() {
-		return row;
-	}
+        return row;
+    }
 
     /**
      * Get the value of the cell as a boolean.
@@ -321,7 +327,31 @@ public final class XSSFCell implements Cell {
         int cellType = getCellType();
         if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
 
-        return this.cell.getF().getStringValue();
+        CTCellFormula f = cell.getF();
+        if(f.getT() == STCellFormulaType.SHARED){
+            return convertSharedFormula((int)f.getSi());
+        } else {
+            return f.getStringValue();
+        }
+    }
+
+    /**
+     * Creates a non shared formula from the shared formula counterpart
+     *
+     * @return non shared formula created for the given shared formula and this cell
+     */
+    private String convertSharedFormula(int idx){
+        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();
+        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
+        Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb);
+        Ptg[] fmla = SharedFormulaRecord.convertSharedFormulas(ptgs,
+                getRowIndex() - sfCell.getRowIndex(), getColumnIndex() - sfCell.getColumnIndex());
+        return FormulaRenderer.toFormulaString(fpb, fmla);
     }
 
     /**
@@ -363,7 +393,7 @@ public final class XSSFCell implements Cell {
      * @return zero-based column index of a column in a sheet.
      */
     public int getColumnIndex() {
-    	return this.cellNum;
+        return this.cellNum;
     }
 
     /**
@@ -372,8 +402,8 @@ public final class XSSFCell implements Cell {
      * @return zero-based row index of a row in the sheet that contains this cell
      */
     public int getRowIndex() {
-		return row.getRowNum();
-	}
+        return row.getRowNum();
+    }
 
     /**
      * Returns an A1 style reference to the location of this cell
@@ -381,8 +411,8 @@ public final class XSSFCell implements Cell {
      * @return A1 style reference to the location of this cell
      */
     public String getReference() {
-		return cell.getR();
-	}
+        return cell.getR();
+    }
 
     /**
      * Return the cell's style.
@@ -406,8 +436,8 @@ public final class XSSFCell implements Cell {
         if(style == null) {
             if(cell.isSetS()) cell.unsetS();
         } else {
-			XSSFCellStyle xStyle = (XSSFCellStyle)style;
-			xStyle.verifyBelongsToStylesSource(stylesSource);
+            XSSFCellStyle xStyle = (XSSFCellStyle)style;
+            xStyle.verifyBelongsToStylesSource(stylesSource);
 
             long idx = stylesSource.putStyle(xStyle);
             cell.setS(idx);
@@ -486,7 +516,7 @@ public final class XSSFCell implements Cell {
      *        will change the cell to a numeric cell and set its value.
      */
     public void setCellValue(Date value) {
-	    boolean date1904 = getSheet().getWorkbook().isDate1904();
+        boolean date1904 = getSheet().getWorkbook().isDate1904();
         setCellValue(DateUtil.getExcelDate(value, date1904));
     }
 
@@ -507,7 +537,7 @@ public final class XSSFCell implements Cell {
      *        will change the cell to a numeric cell and set its value.
      */
     public void setCellValue(Calendar value) {
-	    boolean date1904 = getSheet().getWorkbook().isDate1904();
+        boolean date1904 = getSheet().getWorkbook().isDate1904();
         setCellValue( DateUtil.getExcelDate(value, date1904 ));
     }
 
@@ -765,8 +795,8 @@ public final class XSSFCell implements Cell {
      * @return hyperlink associated with this cell or <code>null</code> if not found
      */
     public XSSFHyperlink getHyperlink() {
-		return getSheet().getHyperlink(row.getRowNum(), cellNum);
-	}
+        return getSheet().getHyperlink(row.getRowNum(), cellNum);
+    }
 
     /**
      * Assign a hypelrink to this cell
@@ -774,14 +804,14 @@ public final class XSSFCell implements Cell {
      * @param hyperlink the hypelrink to associate with this cell
      */
     public void setHyperlink(Hyperlink hyperlink) {
-		XSSFHyperlink link = (XSSFHyperlink)hyperlink;
-		
-		// Assign to us
-		link.setCellReference( new CellReference(row.getRowNum(), cellNum).formatAsString() );
-		
-		// Add to the lists
-		getSheet().setCellHyperlink(link);
-	}
+        XSSFHyperlink link = (XSSFHyperlink)hyperlink;
+
+        // Assign to us
+        link.setCellReference( new CellReference(row.getRowNum(), cellNum).formatAsString() );
+
+        // Add to the lists
+        getSheet().setCellHyperlink(link);
+    }
 
     /**
      * Returns the xml bean containing information about the cell's location (reference), value,
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
index d9761a0014..85e919bef4 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
@@ -137,17 +137,45 @@ public final class XSSFName implements Name {
      * Sets the name that will appear in the user interface for the defined name.
      * Names must begin with a letter or underscore, not contain spaces and be unique across the workbook.
      *
+     * <p>
+     * A name must always be unique within its scope. POI prevents you from defining a name that is not unique
+     * within its scope. However you can use the same name in different scopes. Example:
+     * <pre><blockquote>
+     * //by default names are workbook-global
+     * XSSFName name;
+     * name = workbook.createName();
+     * name.setNameName("sales_08");
+     *
+     * name = workbook.createName();
+     * name.setNameName("sales_08"); //will throw an exception: "The workbook already contains this name (case-insensitive)"
+     *
+     * //create sheet-level name
+     * name = workbook.createName();
+     * name.setSheetIndex(0); //the scope of the name is the first sheet
+     * name.setNameName("sales_08");  //ok
+     *
+     * name = workbook.createName();
+     * name.setSheetIndex(0);
+     * name.setNameName("sales_08");  //will throw an exception: "The sheet already contains this name (case-insensitive)"
+     *
+     * </blockquote></pre>
+    * </p>
      * @param name name of this defined name
      * @throws IllegalArgumentException if the name is invalid or the workbook already contains this name (case-insensitive)
      */
     public void setNameName(String name) {
         validateName(name);
 
+        int sheetIndex = getSheetIndex();
+
         //Check to ensure no other names have the same case-insensitive name
         for (int i = 0; i < workbook.getNumberOfNames(); i++) {
             XSSFName nm = workbook.getNameAt(i);
-            if (nm != this && nm.getNameName().equalsIgnoreCase(name)) {
-                throw new IllegalArgumentException("The workbook already contains this name: " + name);
+            if (nm != this) {
+                if(name.equalsIgnoreCase(nm.getNameName()) && sheetIndex == nm.getSheetIndex()){
+                    String msg = "The "+(sheetIndex == -1 ? "workbook" : "sheet")+" already contains this name: " + name;
+                    throw new IllegalArgumentException(msg);
+               }
             }
         }
         ctName.setName(name);
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
index d402eed4fc..9de3ea46cb 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
@@ -63,7 +63,8 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
         this.cells = new TreeMap<Integer, Cell>();
         for (CTCell c : row.getCArray()) {
             XSSFCell cell = new XSSFCell(this, c);
-            this.cells.put(cell.getColumnIndex(), cell);
+            cells.put(cell.getColumnIndex(), cell);
+            sheet.onReadCell(cell);
         }
     }
 
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index d2f781bb27..c8de77972d 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -74,6 +74,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
     private List<XSSFHyperlink> hyperlinks;
     private ColumnHelper columnHelper;
     private CommentsTable sheetComments;
+    private Map<Integer, XSSFCell> sharedFormulas;
 
     /**
      * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
@@ -147,6 +148,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
 
     private void initRows(CTWorksheet worksheet) {
         rows = new TreeMap<Integer, Row>();
+        sharedFormulas = new HashMap<Integer, XSSFCell>();
         for (CTRow row : worksheet.getSheetData().getRowArray()) {
             XSSFRow r = new XSSFRow(row, this);
             rows.put(r.getRowNum(), r);
@@ -1664,6 +1666,24 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
         return getDefaultSheetView().getPane();
     }
 
+    /**
+     * Return a cell holding shared formula by shared group index
+     *
+     * @param sid shared group index
+     * @return a cell holding shared formula or <code>null</code> if not found
+     */
+    XSSFCell getSharedFormulaCell(int sid){
+        return sharedFormulas.get(sid);
+    }
+
+    void onReadCell(XSSFCell cell){
+        //collect cells holding shared formulas
+        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);
+        }
+    }
 
     @Override
     protected void commit() throws IOException {