From 218607468953e20663529ace42ae800a7c19e9e2 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Mon, 27 Jun 2011 15:40:48 +0000 Subject: [PATCH] Bug 51422 - Support using RecalcIdRecord to trigger a full formula recalculation on load git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1140210 13f79535-47bb-0310-9956-ffa450edef68 --- .../content/xdocs/spreadsheet/eval.xml | 53 ++++++++++++ src/documentation/content/xdocs/status.xml | 3 +- .../org/apache/poi/hssf/dev/BiffViewer.java | 1 + .../poi/hssf/model/InternalWorkbook.java | 81 ++++++++++--------- .../poi/hssf/record/RecalcIdRecord.java | 23 +++++- .../poi/hssf/record/UncalcedRecord.java | 8 +- .../apache/poi/hssf/usermodel/HSSFSheet.java | 21 ++++- .../poi/hssf/usermodel/HSSFWorkbook.java | 38 +++++---- .../org/apache/poi/ss/usermodel/Sheet.java | 17 +++- .../org/apache/poi/ss/usermodel/Workbook.java | 19 +++++ .../poi/xssf/streaming/SXSSFWorkbook.java | 17 ++++ .../apache/poi/xssf/usermodel/XSSFSheet.java | 19 ++++- .../poi/xssf/usermodel/XSSFWorkbook.java | 39 +++++---- .../poi/xssf/usermodel/TestXSSFWorkbook.java | 19 +++++ .../apache/poi/hssf/model/TestWorkbook.java | 23 ++++++ 15 files changed, 303 insertions(+), 78 deletions(-) diff --git a/src/documentation/content/xdocs/spreadsheet/eval.xml b/src/documentation/content/xdocs/spreadsheet/eval.xml index b19517e061..d11ff07859 100644 --- a/src/documentation/content/xdocs/spreadsheet/eval.xml +++ b/src/documentation/content/xdocs/spreadsheet/eval.xml @@ -219,6 +219,59 @@ for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { + +
Recalculation of Formulas +

+ In certain cases you may want to force Excel to re-calculate formulas when the workbook is opened. + Consider the following example: +

+

+ 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. +

+

+ Now modify the workbook with POI: +

+ + 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(); + +

+ 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: +

+

+ 1. Re-evaluate formuals with POI's FormulaEvaluator: +

+ + 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(); + +

+ 2. Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened: +

+ + 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); + +
+
Performance Notes
    diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 55edae324e..5be12f315f 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 51422 - Support using RecalcIdRecord to trigger a full formula recalculation on load 50474 - Example demonstrating how to update Excel workbook embedded in a WordprocessingML document 51431 - Avoid IndexOutOfBoundException when removing freeze panes in XSSF 48877 - Fixed XSSFRichTextString to respect leading and trailing line breaks @@ -45,7 +46,7 @@ 48408 - Improved documentation for Sheet.setColumnWidth 51390 - Added handling of additional properties to HWPF ParagraphSprmCompressor 51389 - Support for sprmPJc paragraph SPRM in HWPF - 48469 - New Case Study for PI web site + 48469 - New Case Study for POI web site 50681 - Avoid exceptions in HSSFDataFormat.getDataFormatString() 50681 - Fixed autosizing columns beyond 255 character limit 51374 - Fixed incorrect setting of lastPrinted OOXML core property diff --git a/src/java/org/apache/poi/hssf/dev/BiffViewer.java b/src/java/org/apache/poi/hssf/dev/BiffViewer.java index df4a0cac7c..ad0de2e84b 100644 --- a/src/java/org/apache/poi/hssf/dev/BiffViewer.java +++ b/src/java/org/apache/poi/hssf/dev/BiffViewer.java @@ -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); diff --git a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java index e781675efa..5159a82bef 100644 --- a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java +++ b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java @@ -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; + } } diff --git a/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java b/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java index 1d972aa630..3837a02350 100644 --- a/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java +++ b/src/java/org/apache/poi/hssf/record/RecalcIdRecord.java @@ -35,7 +35,20 @@ import org.apache.poi.util.LittleEndianOutput; 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 extends StandardRecord { return true; } + public void setEngineId(int val) { + _engineId = val; + } + + public int getEngineId() { + return _engineId; + } + public String toString() { StringBuffer buffer = new StringBuffer(); diff --git a/src/java/org/apache/poi/hssf/record/UncalcedRecord.java b/src/java/org/apache/poi/hssf/record/UncalcedRecord.java index 0c1f31ffc1..2ae3e03e79 100644 --- a/src/java/org/apache/poi/hssf/record/UncalcedRecord.java +++ b/src/java/org/apache/poi/hssf/record/UncalcedRecord.java @@ -30,7 +30,10 @@ import org.apache.poi.util.LittleEndianOutput; 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 extends StandardRecord { } 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() { diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 7a70b3d06c..77eada3495 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -640,9 +640,24 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } /** - * 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. + * + *

    + * 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. + *

    + * + *

    + * 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. + *

    + * @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) { diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 863e160b30..6b3348aa46 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -39,20 +39,7 @@ import org.apache.poi.hssf.model.HSSFFormulaParser; 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 POIDocument implements org.apache.poi.ss udfs.add(toopack); } + /** + * Whether the application shall perform a full recalculation when the workbook is opened. + *

    + * 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. + *

    + *

    + * 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 filename?" + * on close. + *

    + * + * @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); + } + } diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java index b1aa0e31d3..bb4810f50b 100644 --- a/src/java/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java @@ -299,11 +299,22 @@ public interface Sheet extends Iterable { Iterator 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. + * + *

    + * 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. + *

    + * + * 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); diff --git a/src/java/org/apache/poi/ss/usermodel/Workbook.java b/src/java/org/apache/poi/ss/usermodel/Workbook.java index 9312eba797..6ca460168a 100644 --- a/src/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/java/org/apache/poi/ss/usermodel/Workbook.java @@ -545,4 +545,23 @@ public interface Workbook { */ void addToolPack(UDFFinder toopack); + /** + * Whether the application shall perform a full recalculation when the workbook is opened. + *

    + * 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. + *

    + *

    + * 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 filename?" + * on close. + *

    + * + * @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); + } diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java index 0d2f39c38c..6ca1f51e6a 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java @@ -818,5 +818,22 @@ public class SXSSFWorkbook implements Workbook { _wb.addToolPack(toopack); } + + /** + * Whether the application shall perform a full recalculation when the workbook is opened. + *

    + * 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. + *

    + * + * @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 } 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 d881c2afe1..19ea45a3f1 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -1497,11 +1497,24 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } /** - * 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. + * + *

    + * 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. + *

    + * + *

    + * 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. + *

    + * @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()) { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index d7dd953f2a..95cbe73598 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -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 POIXMLDocument implements Workbook, Iterable + * 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. + *

    + *

    + * 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 filename?" + * on close. + *

    + * + * @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); + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java index 473b189239..a4ab3bb712 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java @@ -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 extends BaseTestWorkbook { 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()); + } + } diff --git a/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java b/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java index 8de2c77da8..897fba1ae6 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java +++ b/src/testcases/org/apache/poi/hssf/model/TestWorkbook.java @@ -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 TestCase { 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()); + } } -- 2.39.5