poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1140210 - in /poi/trunk/src: documentation/content/xdocs/ documentation/content/xdocs/spreadsheet/ java/org/apache/poi/hssf/dev/ java/org/apache/poi/hssf/model/ java/org/apache/poi/hssf/record/ java/org/apache/poi/hssf/usermodel/ java/org/...
Date Mon, 27 Jun 2011 15:40:49 GMT
Author: yegor
Date: Mon Jun 27 15:40:48 2011
New Revision: 1140210

URL: http://svn.apache.org/viewvc?rev=1140210&view=rev
Log:
Bug 51422 - Support using RecalcIdRecord to trigger a full formula recalculation on load 

Modified:
    poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/dev/BiffViewer.java
    poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
    poi/trunk/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java
    poi/trunk/src/java/org/apache/poi/hssf/record/UncalcedRecord.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
    poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
    poi/trunk/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java

Modified: poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml Mon Jun 27 15:40:48 2011
@@ -219,6 +219,59 @@ for(int sheetNum = 0; sheetNum < wb.g
 			</section>
 		</section>
 		
+		<anchor id="recalculation"/>
+		<section><title>Recalculation of Formulas</title>
+      <p>
+        In certain cases  you may want to force Excel to re-calculate formulas when the workbook
is opened.
+        Consider the following example:
+      </p>  
+      <p>
+        Open Excel and create a new workbook. On the first sheet set A1=1, B1=1, C1=A1+B1.

+        Excel automatically calculates formulas and the value in C1 is 2. So far so good.
+      </p>  
+      <p>
+        Now modify the workbook with POI:
+      </p>
+      <source>
+  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
+
+  Sheet sh = wb.getSheetAt(0);
+  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2
+
+  FileOutputStream out = new FileOutputStream("workbook2.xls");
+  wb.write(out);
+  out.close();
+      </source>      
+      <p>
+        Now open workbook2.xls in Excel and the value in C1 is still 2 while you expected
3. Wrong? No!
+        The point is that Excel caches previously calculated results and you need to trigger
recalculation to updated them. 
+        It is not an issue when you are creating new workbooks from scratch, but important
to remember when you are modifing 
+        existing workbooks with formulas. This can be done in two ways:  
+      </p>   
+      <p>
+        1. Re-evaluate  formuals with POI's FormulaEvaluator:
+      </p>   
+        <source>
+  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
+
+  Sheet sh = wb.getSheetAt(0);
+  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2
+
+  wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+        </source>        
+      <p>
+        2. Delegate re-calculation to Excel. The application will perform a full recalculation
when the workbook is opened:
+      </p>           
+        <source>
+  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
+
+  Sheet sh = wb.getSheetAt(0);
+  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2
+
+  wb.setForceFormulaRecalculation(true);
+        </source>        
+		</section>
+
 		<anchor id="Performance"/>
 		<section><title>Performance Notes</title>
 			<ul>

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Mon Jun 27 15:40:48 2011
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta4" date="2011-??-??">
+           <action dev="poi-developers" type="add">51422 - Support using RecalcIdRecord
to trigger a full formula recalculation on load  </action>
            <action dev="poi-developers" type="add">50474 - Example demonstrating how
to update Excel workbook embedded in a WordprocessingML document </action>
            <action dev="poi-developers" type="fix">51431 - Avoid IndexOutOfBoundException
when removing freeze panes in XSSF </action>
            <action dev="poi-developers" type="fix">48877 - Fixed XSSFRichTextString
to respect leading and trailing line breaks </action>
@@ -45,7 +46,7 @@
            <action dev="poi-developers" type="fix">48408 - Improved documentation for
Sheet.setColumnWidth </action>
            <action dev="poi-developers" type="add">51390 - Added handling of additional
properties to HWPF ParagraphSprmCompressor</action>
            <action dev="poi-developers" type="add">51389 - Support for sprmPJc paragraph
SPRM in HWPF</action>
-           <action dev="poi-developers" type="fix">48469 - New Case Study for PI web
site  </action>
+           <action dev="poi-developers" type="fix">48469 - New Case Study for POI web
site  </action>
            <action dev="poi-developers" type="fix">50681 - Avoid exceptions in HSSFDataFormat.getDataFormatString()
 </action>
            <action dev="poi-developers" type="fix">50681 - Fixed autosizing columns
beyond 255 character limit </action>
            <action dev="poi-developers" type="fix">51374 - Fixed incorrect setting
of lastPrinted OOXML core property </action>

Modified: poi/trunk/src/java/org/apache/poi/hssf/dev/BiffViewer.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/dev/BiffViewer.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/dev/BiffViewer.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/dev/BiffViewer.java Mon Jun 27 15:40:48 2011
@@ -242,6 +242,7 @@ public final class BiffViewer {
 			case TextRecord.sid:           return new TextRecord(in);
 			case TickRecord.sid:           return new TickRecord(in);
 			case TopMarginRecord.sid:      return new TopMarginRecord(in);
+            case UncalcedRecord.sid:       return new UncalcedRecord(in);
 			case UnitsRecord.sid:          return new UnitsRecord(in);
 			case UseSelFSRecord.sid:       return new UseSelFSRecord(in);
 			case VCenterRecord.sid:        return new VCenterRecord(in);

Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java Mon Jun 27 15:40:48
2011
@@ -1018,36 +1018,32 @@ public final class InternalWorkbook {
         {
 
             Record record = records.get( k );
-            // Let's skip RECALCID records, as they are only use for optimization
-            if ( record.getSid() != RecalcIdRecord.sid || ( (RecalcIdRecord) record ).isNeeded()
)
+            int len = 0;
+            if (record instanceof SSTRecord)
             {
-                int len = 0;
-                if (record instanceof SSTRecord)
-                {
-                    sst = (SSTRecord)record;
-                    sstPos = pos;
-                }
-                if (record.getSid() == ExtSSTRecord.sid && sst != null)
-                {
-                    record = sst.createExtSSTRecord(sstPos + offset);
-                }
-                if (record instanceof BoundSheetRecord) {
-                     if(!wroteBoundSheets) {
-                        for (int i = 0; i < boundsheets.size(); i++) {
-                            len+= getBoundSheetRec(i)
-                                             .serialize(pos+offset+len, data);
-                        }
-                        wroteBoundSheets = true;
-                     }
-                } else {
-                   len = record.serialize( pos + offset, data );
-                }
-                /////  DEBUG BEGIN /////
+                sst = (SSTRecord)record;
+                sstPos = pos;
+            }
+            if (record.getSid() == ExtSSTRecord.sid && sst != null)
+            {
+                record = sst.createExtSSTRecord(sstPos + offset);
+            }
+            if (record instanceof BoundSheetRecord) {
+                 if(!wroteBoundSheets) {
+                    for (int i = 0; i < boundsheets.size(); i++) {
+                        len+= getBoundSheetRec(i)
+                                         .serialize(pos+offset+len, data);
+                    }
+                    wroteBoundSheets = true;
+                 }
+            } else {
+               len = record.serialize( pos + offset, data );
+            }
+            /////  DEBUG BEGIN /////
 //                if (len != record.getRecordSize())
 //                    throw new IllegalStateException("Record size does not match serialized
bytes.  Serialized size = " + len + " but getRecordSize() returns " + record.getRecordSize());
-                /////  DEBUG END /////
-                pos += len;   // rec.length;
-            }
+            /////  DEBUG END /////
+            pos += len;   // rec.length;
         }
         if (log.check( POILogger.DEBUG ))
             log.log( DEBUG, "Exiting serialize workbook" );
@@ -1062,16 +1058,12 @@ public final class InternalWorkbook {
         for ( int k = 0; k < records.size(); k++ )
         {
             Record record = records.get( k );
-            // Let's skip RECALCID records, as they are only use for optimization
-            if ( record.getSid() != RecalcIdRecord.sid || ( (RecalcIdRecord) record ).isNeeded()
)
-            {
-                if (record instanceof SSTRecord)
-                    sst = (SSTRecord)record;
-                if (record.getSid() == ExtSSTRecord.sid && sst != null)
-                    retval += sst.calcExtSSTRecordSize();
-                else
-                    retval += record.getRecordSize();
-            }
+            if (record instanceof SSTRecord)
+                sst = (SSTRecord)record;
+            if (record.getSid() == ExtSSTRecord.sid && sst != null)
+                retval += sst.calcExtSSTRecordSize();
+            else
+                retval += record.getRecordSize();
         }
         return retval;
     }
@@ -2395,4 +2387,19 @@ public final class InternalWorkbook {
         }
     }
 
+    /**
+     * Get or create RecalcIdRecord
+     *
+     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#setForceFormulaRecalculation(boolean)
+     */
+    public RecalcIdRecord getRecalcId(){
+        RecalcIdRecord record = (RecalcIdRecord)findFirstRecordBySid(RecalcIdRecord.sid);
+        if(record == null){
+            record = new RecalcIdRecord();
+            // typically goes after the Country record
+            int pos = findFirstRecordLocBySid(CountryRecord.sid);
+            records.add(pos + 1, record);
+        }
+        return record;
+    }
 }

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java Mon Jun 27 15:40:48
2011
@@ -35,7 +35,20 @@ import org.apache.poi.util.LittleEndianO
 public final class RecalcIdRecord extends StandardRecord {
     public final static short sid = 0x01C1;
     private final int _reserved0;
-    private final int _engineId;
+
+    /**
+     * An unsigned integer that specifies the recalculation engine identifier
+     * of the recalculation engine that performed the last recalculation.
+     * If the value is less than the recalculation engine identifier associated with the
application,
+     * the application will recalculate the results of all formulas on
+     * this workbook immediately after loading the file
+     */
+    private int _engineId;
+
+    public RecalcIdRecord() {
+        _reserved0 = 0;
+        _engineId = 0;
+    }
 
     public RecalcIdRecord(RecordInputStream in) {
     	in.readUShort(); // field 'rt' should have value 0x01C1, but Excel doesn't care during
reading
@@ -47,6 +60,14 @@ public final class RecalcIdRecord extend
         return true;
     }
 
+    public void setEngineId(int val) {
+        _engineId = val;
+    }
+
+    public int getEngineId() {
+        return _engineId;
+    }
+
     public String toString() {
         StringBuffer buffer = new StringBuffer();
 

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/UncalcedRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/UncalcedRecord.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/UncalcedRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/UncalcedRecord.java Mon Jun 27 15:40:48
2011
@@ -30,7 +30,10 @@ import org.apache.poi.util.LittleEndianO
 public final class UncalcedRecord extends StandardRecord  {
 	public final static short sid = 0x005E;
 
+    private short _reserved;
+
 	public UncalcedRecord() {
+        _reserved = 0;
 	}
 
 	public short getSid() {
@@ -38,18 +41,19 @@ public final class UncalcedRecord extend
 	}
 
 	public UncalcedRecord(RecordInputStream in) {
-		in.readShort(); // unused
+		_reserved = in.readShort(); // unused
 	}
 
 	public String toString() {
 		StringBuffer buffer = new StringBuffer();
 		buffer.append("[UNCALCED]\n");
+        buffer.append("    _reserved: ").append(_reserved).append('\n');
 		buffer.append("[/UNCALCED]\n");
 		return buffer.toString();
 	}
 
 	public void serialize(LittleEndianOutput out) {
-		out.writeShort(0);
+		out.writeShort(_reserved);
 	}
 
 	protected int getDataSize() {

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Mon Jun 27 15:40:48 2011
@@ -640,9 +640,24 @@ public final class HSSFSheet implements 
     }
 
     /**
-     * Whether a record must be inserted or not at generation to indicate that
-     * formula must be recalculated when workbook is opened.
-     * @param value true if an uncalced record must be inserted or not at generation
+     * Control if Excel should be asked to recalculate all formulas on this sheet
+     * when the workbook is opened.
+     *
+     *  <p>
+     *  Calculating the formula values with {@link org.apache.poi.ss.usermodel.FormulaEvaluator}
is the
+     *  recommended solution, but this may be used for certain cases where
+     *  evaluation in POI is not possible.
+     *  </p>
+     *
+     *  <p>
+     *  It is recommended to force recalcuation of formulas on workbook level using
+     *  {@link org.apache.poi.ss.usermodel.Workbook#setForceFormulaRecalculation(boolean)}
+     *  to ensure that all cross-worksheet formuals and external dependencies are updated.
+     *  </p>
+     * @param value true if the application will perform a full recalculation of
+     * this worksheet values when the workbook is opened
+     *
+     * @see org.apache.poi.ss.usermodel.Workbook#setForceFormulaRecalculation(boolean)
      */
     public void setForceFormulaRecalculation(boolean value)
     {

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Mon Jun 27 15:40:48
2011
@@ -39,20 +39,7 @@ import org.apache.poi.hssf.model.HSSFFor
 import org.apache.poi.hssf.model.RecordStream;
 import org.apache.poi.hssf.model.InternalSheet;
 import org.apache.poi.hssf.model.InternalWorkbook;
-import org.apache.poi.hssf.record.AbstractEscherHolderRecord;
-import org.apache.poi.hssf.record.BackupRecord;
-import org.apache.poi.hssf.record.DrawingGroupRecord;
-import org.apache.poi.hssf.record.EmbeddedObjectRefSubRecord;
-import org.apache.poi.hssf.record.ExtendedFormatRecord;
-import org.apache.poi.hssf.record.FontRecord;
-import org.apache.poi.hssf.record.LabelRecord;
-import org.apache.poi.hssf.record.LabelSSTRecord;
-import org.apache.poi.hssf.record.NameRecord;
-import org.apache.poi.hssf.record.ObjRecord;
-import org.apache.poi.hssf.record.Record;
-import org.apache.poi.hssf.record.RecordFactory;
-import org.apache.poi.hssf.record.SSTRecord;
-import org.apache.poi.hssf.record.UnknownRecord;
+import org.apache.poi.hssf.record.*;
 import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
 import org.apache.poi.hssf.record.common.UnicodeString;
 import org.apache.poi.ss.formula.ptg.Area3DPtg;
@@ -1782,4 +1769,27 @@ public final class HSSFWorkbook extends 
         udfs.add(toopack);
     }
 
+    /**
+     * Whether the application shall perform a full recalculation when the workbook is opened.
+     * <p>
+     * Typically you want to force formula recalculation when you modify cell formulas or
values
+     * of a workbook previously created by Excel. When set to true, this flag will tell Excel
+     * that it needs to recalculate all formulas in the workbook the next time the file is
opened.
+     * </p>
+     * <p>
+     * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
+     * Depending on the version, Excel may prompt you with "Do you want to save the changes
in <em>filename</em>?"
+     * on close.
+     * </p>
+     *
+     * @param value true if the application will perform a full recalculation of
+     * workbook values when the workbook is opened
+     * @since 3.8
+     */
+    public void setForceFormulaRecalculation(boolean value){
+        InternalWorkbook iwb = getWorkbook();
+        RecalcIdRecord recalc = iwb.getRecalcId();
+        recalc.setEngineId(0);
+    }
+
 }

Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java Mon Jun 27 15:40:48 2011
@@ -299,11 +299,22 @@ public interface Sheet extends Iterable<
     Iterator<Row> rowIterator();
 
     /**
-     * Control if Excel should be asked to recalculate all formulas when the
-     *  workbook is opened. 
-     * Calculating the formula values with {@link FormulaEvaluator} is the
+     * Control if Excel should be asked to recalculate all formulas on this sheet
+     * when the workbook is opened.
+     *
+     *  <p>
+     *  Calculating the formula values with {@link FormulaEvaluator} is the
      *  recommended solution, but this may be used for certain cases where
      *  evaluation in POI is not possible.
+     *  </p>
+     *
+     *  To force recalcuation of formulas in the entire workbook
+     *  use {@link Workbook#setForceFormulaRecalculation(boolean)} instead.
+     *
+     * @param value true if the application will perform a full recalculation of
+     * this worksheet values when the workbook is opened
+     *
+     * @see Workbook#setForceFormulaRecalculation(boolean)
      */
     void setForceFormulaRecalculation(boolean value);
 

Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java Mon Jun 27 15:40:48 2011
@@ -545,4 +545,23 @@ public interface Workbook {
      */
     void addToolPack(UDFFinder toopack);
 
+    /**
+     * Whether the application shall perform a full recalculation when the workbook is opened.
+     * <p>
+     * Typically you want to force formula recalculation when you modify cell formulas or
values
+     * of a workbook previously created by Excel. When set to true, this flag will tell Excel
+     * that it needs to recalculate all formulas in the workbook the next time the file is
opened.
+     * </p>
+     * <p>
+     * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
+     * Depending on the version, Excel may prompt you with "Do you want to save the changes
in <em>filename</em>?"
+     * on close.
+     * </p>
+     *
+     * @param value true if the application will perform a full recalculation of
+     * workbook values when the workbook is opened
+     * @since 3.8
+     */
+    public void setForceFormulaRecalculation(boolean value);
+
 }

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java Mon Jun 27 15:40:48
2011
@@ -818,5 +818,22 @@ public class SXSSFWorkbook implements Wo
     {
         _wb.addToolPack(toopack);
     }
+
+    /**
+     * Whether the application shall perform a full recalculation when the workbook is opened.
+     * <p>
+     * Typically you want to force formula recalculation when you modify cell formulas or
values
+     * of a workbook previously created by Excel. When set to 0, this flag will tell Excel
+     * that it needs to recalculate all formulas in the workbook the next time the file is
opened.
+     * </p>
+     *
+     * @param value true if the application will perform a full recalculation of
+     * workbook values when the workbook is opened
+     * @since 3.8
+     */
+    public void setForceFormulaRecalculation(boolean value){
+        _wb.setForceFormulaRecalculation(value);
+    }
+
 //end of interface implementation
 }

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=1140210&r1=1140209&r2=1140210&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 Mon Jun 27 15:40:48
2011
@@ -1497,11 +1497,24 @@ public class XSSFSheet extends POIXMLDoc
     }
 
     /**
-     * Control if Excel should be asked to recalculate all formulas when the
-     *  workbook is opened, via the "sheetCalcPr fullCalcOnLoad" option.
-     * Calculating the formula values with {@link FormulaEvaluator} is the
+     * Control if Excel should be asked to recalculate all formulas on this sheet
+     * when the workbook is opened.
+     *
+     *  <p>
+     *  Calculating the formula values with {@link org.apache.poi.ss.usermodel.FormulaEvaluator}
is the
      *  recommended solution, but this may be used for certain cases where
      *  evaluation in POI is not possible.
+     *  </p>
+     *
+     *  <p>
+     *  It is recommended to force recalcuation of formulas on workbook level using
+     *  {@link org.apache.poi.ss.usermodel.Workbook#setForceFormulaRecalculation(boolean)}
+     *  to ensure that all cross-worksheet formuals and external dependencies are updated.
+     *  </p>
+     * @param value true if the application will perform a full recalculation of
+     * this worksheet values when the workbook is opened
+     *
+     * @see org.apache.poi.ss.usermodel.Workbook#setForceFormulaRecalculation(boolean)
      */
     public void setForceFormulaRecalculation(boolean value) {
        if(worksheet.isSetSheetCalcPr()) {

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Mon Jun 27 15:40:48
2011
@@ -60,19 +60,7 @@ import org.apache.xmlbeans.XmlException;
 import org.apache.xmlbeans.XmlObject;
 import org.apache.xmlbeans.XmlOptions;
 import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookView;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookViews;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookProtection;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.STSheetState;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
 
 /**
  * High level representation of a SpreadsheetML workbook.  This is the first object most
users
@@ -1584,5 +1572,28 @@ public class XSSFWorkbook extends POIXML
         _udfFinder.add(toopack);
     }
 
-
+    /**
+     * Whether the application shall perform a full recalculation when the workbook is opened.
+     * <p>
+     * Typically you want to force formula recalculation when you modify cell formulas or
values
+     * of a workbook previously created by Excel. When set to true, this flag will tell Excel
+     * that it needs to recalculate all formulas in the workbook the next time the file is
opened.
+     * </p>
+     * <p>
+     * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
+     * Depending on the version, Excel may prompt you with "Do you want to save the changes
in <em>filename</em>?"
+     * on close.
+     * </p>
+     *
+     * @param value true if the application will perform a full recalculation of
+     * workbook values when the workbook is opened
+     * @since 3.8
+     */
+   public void setForceFormulaRecalculation(boolean value){
+        CTWorkbook ctWorkbook = getCTWorkbook();
+        CTCalcPr calcPr = ctWorkbook.isSetCalcPr() ? ctWorkbook.getCalcPr() : ctWorkbook.addNewCalcPr();
+        // when set to 0, will tell Excel that it needs to recalculate all formulas
+        // in the workbook the next time the file is opened.
+        calcPr.setCalcId(0);
+    }
 }

Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java Mon
Jun 27 15:40:48 2011
@@ -32,6 +32,8 @@ import org.apache.poi.util.TempFile;
 import org.apache.poi.xssf.XSSFITestDataProvider;
 import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.model.StylesTable;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
 
 public final class TestXSSFWorkbook extends BaseTestWorkbook {
@@ -405,4 +407,21 @@ public final class TestXSSFWorkbook exte
 	        fail("Shouldn't be able to get style at 3 that doesn't exist");
 	    } catch(IndexOutOfBoundsException e) {}
 	}
+
+    public void testRecalcId() {
+        XSSFWorkbook wb = new XSSFWorkbook();
+        CTWorkbook ctWorkbook = wb.getCTWorkbook();
+        assertFalse(ctWorkbook.isSetCalcPr());
+
+        wb.setForceFormulaRecalculation(true); // resets the EngineId flag to zero
+
+        CTCalcPr calcPr = ctWorkbook.getCalcPr();
+        assertNotNull(calcPr);
+        assertEquals(0, (int) calcPr.getCalcId());
+
+        calcPr.setCalcId(100);
+        wb.setForceFormulaRecalculation(true); // resets the EngineId flag to zero
+        assertEquals(0, (int) calcPr.getCalcId());
+    }
+
 }

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java?rev=1140210&r1=1140209&r2=1140210&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java Mon Jun 27 15:40:48
2011
@@ -19,7 +19,9 @@ package org.apache.poi.hssf.model;
 
 import junit.framework.TestCase;
 
+import org.apache.poi.hssf.record.CountryRecord;
 import org.apache.poi.hssf.record.FontRecord;
+import org.apache.poi.hssf.record.RecalcIdRecord;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.hssf.usermodel.TestHSSFWorkbook;
 import org.apache.poi.ss.formula.udf.UDFFinder;
@@ -114,4 +116,25 @@ public final class TestWorkbook extends 
 
         assertNull(wb.getNameXPtg("myFunc3", udff));  // myFunc3 is unknown
     }
+
+    public void testRecalcId(){
+        HSSFWorkbook wb = new HSSFWorkbook();
+        InternalWorkbook iwb = TestHSSFWorkbook.getInternalWorkbook(wb);
+        int countryPos = iwb.findFirstRecordLocBySid(CountryRecord.sid);
+        assertTrue(countryPos != -1);
+        // RecalcIdRecord is not present in new workbooks
+        assertEquals(null, iwb.findFirstRecordBySid(RecalcIdRecord.sid));
+        RecalcIdRecord record = iwb.getRecalcId();
+        assertNotNull(record);
+        assertSame(record, iwb.getRecalcId());
+
+        assertSame(record, iwb.findFirstRecordBySid(RecalcIdRecord.sid));
+        assertEquals(countryPos + 1, iwb.findFirstRecordLocBySid(RecalcIdRecord.sid));
+
+        record.setEngineId(100);
+        assertEquals(100, record.getEngineId());
+
+        wb.setForceFormulaRecalculation(true); // resets the EngineId flag to zero
+        assertEquals(0, record.getEngineId());
+    }
 }



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message