From 7618be0d35d959047333d344e231ae4b684141b5 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Wed, 16 Jan 2008 13:14:31 +0000 Subject: [PATCH] Patch to support UncalcedRecord and usermodel code for it, to indicate formulas on a sheet need recalculating (from bug #44233) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@612445 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + src/java/org/apache/poi/hssf/model/Sheet.java | 42 ++++++++- .../apache/poi/hssf/record/RecordFactory.java | 2 +- .../poi/hssf/record/UncalcedRecord.java | 81 +++++++++++++++++ .../apache/poi/hssf/usermodel/HSSFSheet.java | 20 +++++ .../poi/hssf/data/TestDataValidation.xls | Bin 21504 -> 21504 bytes .../apache/poi/hssf/data/UncalcedRecord.xls | Bin 0 -> 18432 bytes .../poi/hssf/usermodel/TestHSSFSheet.java | 84 +++++++++++++++++- 9 files changed, 224 insertions(+), 7 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/UncalcedRecord.java create mode 100644 src/testcases/org/apache/poi/hssf/data/UncalcedRecord.xls diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index be8df65763..bc95d9b7a2 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 44233 - Support for getting and setting a flag on the sheet, which tells excel to re-calculate all formulas on it at next reload 44201 - Enable cloning of sheets with data validation rules 44200 - Enable cloning of sheets with notes 43008 - Add a moveCell method to HSSFRow, and deprecate setCellNum(), which didn't update things properly diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 5868b0eb2d..122cab85c3 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 44233 - Support for getting and setting a flag on the sheet, which tells excel to re-calculate all formulas on it at next reload 44201 - Enable cloning of sheets with data validation rules 44200 - Enable cloning of sheets with notes 43008 - Add a moveCell method to HSSFRow, and deprecate setCellNum(), which didn't update things properly diff --git a/src/java/org/apache/poi/hssf/model/Sheet.java b/src/java/org/apache/poi/hssf/model/Sheet.java index 59e5de3246..f3f7deba07 100644 --- a/src/java/org/apache/poi/hssf/model/Sheet.java +++ b/src/java/org/apache/poi/hssf/model/Sheet.java @@ -97,6 +97,8 @@ public class Sheet implements Model protected ScenarioProtectRecord scenprotect = null; protected PasswordRecord password = null; + /** Add an UncalcedRecord if not true indicating formulas have not been calculated */ + protected boolean uncalced = false; public static final byte PANE_LOWER_RIGHT = (byte)0; public static final byte PANE_UPPER_RIGHT = (byte)1; @@ -161,6 +163,9 @@ public class Sheet implements Model break; } } + else if (rec.getSid() == UncalcedRecord.sid) { + retval.uncalced = true; + } else if (rec.getSid() == DimensionsRecord.sid) { // Make a columns aggregate if one hasn't ready been created. @@ -736,8 +741,14 @@ public class Sheet implements Model { Record record = (( Record ) records.get(k)); - //Once the rows have been found in the list of records, start - //writing out the blocked row information. This includes the DBCell references + // Don't write out UncalcedRecord entries, as + // we handle those specially just below + if (record instanceof UncalcedRecord) { + continue; + } + + // Once the rows have been found in the list of records, start + // writing out the blocked row information. This includes the DBCell references if (record instanceof RowRecordsAggregate) { pos += ((RowRecordsAggregate)record).serialize(pos, data, cells); // rec.length; } else if (record instanceof ValueRecordsAggregate) { @@ -745,8 +756,14 @@ public class Sheet implements Model } else { pos += record.serialize(pos, data ); // rec.length; } - //If the BOF record was just serialized then add the IndexRecord + + // If the BOF record was just serialized then add the IndexRecord if (record.getSid() == BOFRecord.sid) { + // Add an optional UncalcedRecord + if (uncalced) { + UncalcedRecord rec = new UncalcedRecord(); + pos += rec.serialize(pos, data); + } //Can there be more than one BOF for a sheet? If not then we can //remove this guard. So be safe it is left here. if (rows != null && !haveSerializedIndex) { @@ -2184,6 +2201,11 @@ public class Sheet implements Model retval += 2; } } + // Add space for UncalcedRecord + if (uncalced) { + retval += UncalcedRecord.getStaticRecordSize(); + } + return retval; } @@ -2651,8 +2673,22 @@ public class Sheet implements Model public boolean isDisplayRowColHeadings() { return windowTwo.getDisplayRowColHeadings(); } + /** + * @return whether an uncalced record must be inserted or not at generation + */ + public boolean getUncalced() { + return uncalced; + } + /** + * @param uncalced whether an uncalced record must be inserted or not at generation + */ + public void setUncalced(boolean uncalced) { + this.uncalced = uncalced; + } + + /** * Returns the array of margins. If not created, will create. * * @return the array of marings. diff --git a/src/java/org/apache/poi/hssf/record/RecordFactory.java b/src/java/org/apache/poi/hssf/record/RecordFactory.java index cf705a316d..20e8ba788a 100644 --- a/src/java/org/apache/poi/hssf/record/RecordFactory.java +++ b/src/java/org/apache/poi/hssf/record/RecordFactory.java @@ -76,7 +76,7 @@ public class RecordFactory WriteProtectRecord.class, FilePassRecord.class, PaneRecord.class, NoteRecord.class, ObjectProtectRecord.class, ScenarioProtectRecord.class, FileSharingRecord.class, ChartTitleFormatRecord.class, - DVRecord.class, DVALRecord.class + DVRecord.class, DVALRecord.class, UncalcedRecord.class }; } private static Map recordsMap = recordsToMap(records); diff --git a/src/java/org/apache/poi/hssf/record/UncalcedRecord.java b/src/java/org/apache/poi/hssf/record/UncalcedRecord.java new file mode 100644 index 0000000000..c3243f2585 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/UncalcedRecord.java @@ -0,0 +1,81 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record; + +import org.apache.poi.util.LittleEndian; + +/** + * Title: Uncalced Record + *

+ * If this record occurs in the Worksheet Substream, it indicates that the formulas have not + * been recalculated before the document was saved. + * + * @author Olivier Leprince + */ + +public class UncalcedRecord extends Record +{ + public final static short sid = 0x5E; + + /** + * Default constructor + */ + public UncalcedRecord() { + } + /** + * read constructor + */ + public UncalcedRecord(RecordInputStream in) { + super(in); + } + + public short getSid() { + return sid; + } + + protected void validateSid(short id) { + if (id != sid) { + throw new RecordFormatException("NOT AN UNCALCED RECORD"); + } + } + + protected void fillFields(RecordInputStream in) { + } + + public String toString() { + StringBuffer buffer = new StringBuffer(); + buffer.append("[UNCALCED]\n"); + buffer.append("[/UNCALCED]\n"); + return buffer.toString(); + } + + public int serialize(int offset, byte[] data) { + LittleEndian.putShort(data, 0 + offset, sid); + LittleEndian.putShort(data, 2 + offset, (short) 2); + LittleEndian.putShort(data, 4 + offset, (short) 0); // unused + return getRecordSize(); + } + + public int getRecordSize() { + return UncalcedRecord.getStaticRecordSize(); + } + + public static int getStaticRecordSize() { + return 6; + } +} diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 7fe6ea9dcb..bb779fef36 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -594,6 +594,26 @@ public class HSSFSheet region.getColumnTo()); } + /** + * 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 + */ + public void setForceFormulaRecalculation(boolean value) + { + sheet.setUncalced(value); + } + /** + * Whether a record must be inserted or not at generation to indicate that + * formula must be recalculated when workbook is opened. + * @return true if an uncalced record must be inserted or not at generation + */ + public boolean getForceFormulaRecalculation() + { + return sheet.getUncalced(); + } + + /** * determines whether the output is vertically centered on the page. * @param value true to vertically center, false otherwise. diff --git a/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls b/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls index 6b71a77f2d0e03b699cecc29f6bc31f5c413e84e..556ea8192758d22c8b5427a379673771298ee08d 100644 GIT binary patch delta 23 ecmZoz!Pu~Zaf3D!OI~Jj_GCk*^v%%LVMM@;0LJ9_AAxmkyp<%UJz-UZl zQF+j4f|8({kQfqQh(<9Q^})og@qvdDjgf%*VEK7B8ce_x*6*Cz>FiE-mzH0_d#C6A zoO|v)XTF)8neWbSfBd0x{Mcu;=g5`XsfeznO30g`*HEvw+ZBZVREnE+X`&95O7{=~ zB|aQTuZL<{PZf`bQN=+%M>?G_Mv)Vphis$g2v!?nLoW;sj+pDaHukK0^6@V7cBa)5 zL!YLTMoGl9O7o(oK(wmNZ$ouH}PO|ZAvT(R; z_t^R+dGyVB^o@D+B~$c=b%QG6MJX;T<6bGN;)ayf@nR_{PEyvyJ*gcUAH{>X;(LoZ zN-wP)+&2_!WzA+^hGrRSdP_1ip|{Au^GZF9YCZ_jjvmdAg_t_>yjVCb%ZT6+1Q%s&AP`XuDR>klP8fO_b6aG?U1*WYHOzt(4w&y^6k+C%~|6jj|5AUBvfd z#)UUs{{usa9);vblcRA`8IIIlLy|+*Z3OMojieK+z#z@AqqJbK+6+3UnZr;GyzUwd z60^Pgc6rNT5HBiI2q**;0t$iu7y>0eJkoeP^!+2Jiq9doQMs>wi&_$L9wO}jj&gs~ z>;)H=n<_5@Tr_r~o{#q54Arl3Q~l17p}trvD9B0dc*YXI4e9+xEmQi2=7xrrZDt@I zXxkQa8h&FD9-0<9iniXK!GZ1BCMI)pFRbc!iL9D4L?s*ckM%W=TMzm_C}IBS(%93@DUkvrrHg~nlTMoe_M5>|?PzWdl6aor? z`vQS$c!KrFJC^L-VPZ6X@PoguY}i%t(K|kBdg$|CTA=$H!VjO^ABGr^Cm{U%IRN4J z?r{h|DZLLVhkOXBhMa&fX}X9(ew6&q!@aNo)ph%r%5@L*j|}e_j_x(PUhE%?Nh}oH zf4p?dNiB^2_}N|EWvKI;5S0`H3ITy)5Kssx1QY@a0fm4y)5Kssx1QY`Qdj$ABKGSIC$#^Y~FRWYqp7}TbUkCm{ zC4UK!`8{)a=JotbL+1V5Ux||6`7;H#f5SKz^?4BPpO3N@vH-FWQU_tm&fnhT{;QBc zx{j(!cNl>l8pgJlOzNU%QI60{I2zMWbMfsFJ7hkGDtvr45x#>fX{}Xc=X~64&1&(# z+iuRdoJIPie~x216Y%;|`u6~|1^!29H|@N^IPf1RH2w_P7)-~%*ziIbl7}|YK6(aQ zpJ2ZZMi>2JnB7cPH(i Ut2di$?5Df`H|x(m{$BR~FUIE`6aWAK literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java index f0deb68e0c..5523638d9a 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java @@ -19,14 +19,18 @@ package org.apache.poi.hssf.usermodel; -import java.io.*; +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.File; +import java.io.FileInputStream; +import java.io.FileOutputStream; import junit.framework.TestCase; import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.record.HCenterRecord; -import org.apache.poi.hssf.record.ProtectRecord; import org.apache.poi.hssf.record.PasswordRecord; +import org.apache.poi.hssf.record.ProtectRecord; import org.apache.poi.hssf.record.SCLRecord; import org.apache.poi.hssf.record.VCenterRecord; import org.apache.poi.hssf.record.WSBoolRecord; @@ -790,7 +794,81 @@ public class TestHSSFSheet assertTrue(sheet3.getColumnWidth((short)0) <= maxWithRow1And2); } - public static void main(java.lang.String[] args) { + /** + * Setting ForceFormulaRecalculation on sheets + */ + public void testForceRecalculation() throws Exception { + String filename = System.getProperty("HSSF.testdata.path"); + filename = filename + "/UncalcedRecord.xls"; + HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filename)); + + HSSFSheet sheet = workbook.getSheetAt(0); + HSSFSheet sheet2 = workbook.getSheetAt(0); + HSSFRow row = sheet.getRow(0); + row.createCell((short) 0).setCellValue(5); + row.createCell((short) 1).setCellValue(8); + assertFalse(sheet.getForceFormulaRecalculation()); + assertFalse(sheet2.getForceFormulaRecalculation()); + + // Save and manually verify that on column C we have 0, value in template + File tempFile = new File(System.getProperty("java.io.tmpdir")+"/uncalced_err.xls" ); + tempFile.delete(); + FileOutputStream fout = new FileOutputStream( tempFile ); + workbook.write( fout ); + fout.close(); + sheet.setForceFormulaRecalculation(true); + assertTrue(sheet.getForceFormulaRecalculation()); + + // Save and manually verify that on column C we have now 13, calculated value + tempFile = new File(System.getProperty("java.io.tmpdir")+"/uncalced_succ.xls" ); + tempFile.delete(); + fout = new FileOutputStream( tempFile ); + workbook.write( fout ); + fout.close(); + + // Try it can be opened + HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(tempFile)); + + // And check correct sheet settings found + sheet = wb2.getSheetAt(0); + sheet2 = wb2.getSheetAt(1); + assertTrue(sheet.getForceFormulaRecalculation()); + assertFalse(sheet2.getForceFormulaRecalculation()); + + // Now turn if back off again + sheet.setForceFormulaRecalculation(false); + + fout = new FileOutputStream( tempFile ); + wb2.write( fout ); + fout.close(); + wb2 = new HSSFWorkbook(new FileInputStream(tempFile)); + + assertFalse(wb2.getSheetAt(0).getForceFormulaRecalculation()); + assertFalse(wb2.getSheetAt(1).getForceFormulaRecalculation()); + assertFalse(wb2.getSheetAt(2).getForceFormulaRecalculation()); + + // Now add a new sheet, and check things work + // with old ones unset, new one set + HSSFSheet s4 = wb2.createSheet(); + s4.setForceFormulaRecalculation(true); + + assertFalse(sheet.getForceFormulaRecalculation()); + assertFalse(sheet2.getForceFormulaRecalculation()); + assertTrue(s4.getForceFormulaRecalculation()); + + fout = new FileOutputStream( tempFile ); + wb2.write( fout ); + fout.close(); + + HSSFWorkbook wb3 = new HSSFWorkbook(new FileInputStream(tempFile)); + assertFalse(wb3.getSheetAt(0).getForceFormulaRecalculation()); + assertFalse(wb3.getSheetAt(1).getForceFormulaRecalculation()); + assertFalse(wb3.getSheetAt(2).getForceFormulaRecalculation()); + assertTrue(wb3.getSheetAt(3).getForceFormulaRecalculation()); + } + + + public static void main(java.lang.String[] args) { junit.textui.TestRunner.run(TestHSSFSheet.class); } } -- 2.39.5