From f906677aa61f8d1d47098155d0e09b353d2cec30 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Thu, 10 Jul 2008 20:54:42 +0000 Subject: [PATCH] Fix cell.getRichStringCellValue() for formula cells with string results git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@675727 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/usermodel/HSSFCell.java | 23 +++++++-- .../org/apache/poi/hssf/data/43623.xls | Bin 0 -> 13824 bytes .../apache/poi/hssf/usermodel/TestBugs.java | 44 ++++++++++++++---- 5 files changed, 56 insertions(+), 13 deletions(-) create mode 100644 src/testcases/org/apache/poi/hssf/data/43623.xls diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 808bea2a9c..f6f0bce1a1 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + Fix cell.getRichStringCellValue() for formula cells with string results 45365 - Handle more excel number formatting rules in FormatTrackingHSSFListener / XLS2CSVmra 45373 - Improve the performance of HSSFSheet.shiftRows 45367 - Fixed bug when last row removed from sheet is row zero diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 21d279ada1..4e95db3230 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + Fix cell.getRichStringCellValue() for formula cells with string results 45365 - Handle more excel number formatting rules in FormatTrackingHSSFListener / XLS2CSVmra 45373 - Improve the performance of HSSFSheet.shiftRows 45367 - Fixed bug when last row removed from sheet is row zero diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index 7d2a53d207..dd1770f56d 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -601,13 +601,28 @@ public class HSSFCell // Set the 'pre-evaluated result' for the formula // note - formulas do not preserve text formatting. FormulaRecordAggregate fr = (FormulaRecordAggregate) record; - // must make new sr because fr.getStringRecord() may be null - StringRecord sr = new StringRecord(); - sr.setString(value.getString()); // looses format - fr.setStringRecord(sr); + + // Save the string into a String Record, creating + // one if required + StringRecord sr = fr.getStringRecord(); + if(sr == null) { + // Wasn't a string before, need a new one + sr = new StringRecord(); + fr.setStringRecord(sr); + } + + // Save, loosing the formatting + sr.setString(value.getString()); + // Update our local cache to the un-formatted version + stringValue = new HSSFRichTextString(sr.getString()); + + // All done return; } + // If we get here, we're not dealing with a formula, + // so handle things as a normal rich text cell + if (cellType != CELL_TYPE_STRING) { setCellType(CELL_TYPE_STRING, false, row, col, styleIndex); } diff --git a/src/testcases/org/apache/poi/hssf/data/43623.xls b/src/testcases/org/apache/poi/hssf/data/43623.xls new file mode 100644 index 0000000000000000000000000000000000000000..401cb2a88747c288c52eea61b6c5c95b3d3b9d8d GIT binary patch literal 13824 zcmeHOYiv|S6#nkr-F9nBpMX5o?VgfR=IWu$5 z%sF%Bn=^Co%)R^7sj~ikhsK;kPMD1lE~g5R7vMY0kM^%i!SYl}mwq~md9rM{i4Z7= zav<{}I6dw}{+CH+L%=z>I2b<482FyF5*ttsEbMIUXjvD3xTSSVS7+NZovmBr|6Q1C zY-n+0Lz0d+i#Z2-$StN1id$~t3KM^5>K!rhES6kA33fJZnmmK`K)DreqpA0riK|Rp z%-F#vM&>)inToq{jP+C7cFEO&$I6^0^k5_U(1|C}#(W&pFrD!N!|7njjV~1ok3s|$ zK-K=L>bmOcDKl5r``K8yl5?Ch*JsRpd~w^_Rly1|>4>* zOttB#!4#lbjn~ADCC&AoEt$)7X;pm{%bM#Oo9koYI!wd_31+IT4CKYnsy_>L;Lryf zq5(N=d7Mmh4GozFr=b>;0}cAp>Gpk5(`-hrAH$8?{GN)N)QggF@#Rp4|C?WQeZD;W zTJ;CxOY61j|DX8|=V2!Jc-DG2{Nb1Q{E36`>jvT148kwCioe?pqcjmpDJ@InnY26+ zHfcp7-=s*uq{9=D)M~TlZ0AaLDk3ZMu%x4{t#9g1gy|=BIO;@6oKK%QxX7vHhZI+- zC|aWV#*DX|mu4;Jg|6q8s#ieT!F&1uD-SaZU=icBTrRa3mN5oO*~rEB zemT8x&7y{8!>=*?S`)u$IEjE+ce2|+rSk$yj)v8iOnS-Ge%ZwPP-SX3Y{OwEGoio1 z%PU>Vx=}m_S>$4GVF`f=o9i%B{~B(w5m8UThZG>{@)l?c{xx_SuVB6BU5yrqyF#8;Pa@-885WM0CoV-Xj~B@~%bYxn zWzNm2;lNErC`I;ocMOZ8aMeG!my0l8mrl}2&Jm1bRm~SpG&Qm&Z*vqD`$)Kg#m1we z1z5mmx@4>#Ax}z1jjEsb=%3@yH>ir^Nja1%H&Rp)jq-Mgly4{PqR{b~+uGmS`y
<)Lil8q2iu{?$~@aSSRCQC$g6 zkH2Tzvu9m{LY8tWWATYAmWQ*I%PD-z+bK+?WU~MmK+bi1UllrVOT0L_@s7eV{3b*G;PaQW^&WI#jj^N4XAe2$zd)|EFFS@Tr~@j72$t~w z9qajPF@1lCD6|`!#L@pgy2%+l+{N}ahcthN+P==Y%mEpb2tV=dMys)*&5Xc5vuDP? L=)dWDy8nLx-hqa< literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index 20826b979d..115efc808b 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -31,9 +31,7 @@ import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.CellValueRecordInterface; import org.apache.poi.hssf.record.EmbeddedObjectRefSubRecord; -import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.NameRecord; -import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; import org.apache.poi.hssf.record.formula.DeletedArea3DPtg; import org.apache.poi.hssf.util.Region; @@ -1151,10 +1149,12 @@ public final class TestBugs extends TestCase { s.createRow(0); HSSFCell c1 = s.getRow(0).createCell((short)0); HSSFCell c2 = s.getRow(0).createCell((short)1); + HSSFCell c3 = s.getRow(0).createCell((short)2); // As number and string c1.setCellFormula("70164"); c2.setCellFormula("\"70164\""); + c3.setCellFormula("\"90210\""); // Check the formulas assertEquals("70164.0", c1.getCellFormula()); @@ -1165,20 +1165,31 @@ public final class TestBugs extends TestCase { assertEquals("", c1.getRichStringCellValue().getString()); assertEquals(0.0, c2.getNumericCellValue(), 0.00001); assertEquals("", c2.getRichStringCellValue().getString()); + assertEquals(0.0, c3.getNumericCellValue(), 0.00001); + assertEquals("", c3.getRichStringCellValue().getString()); - // Now evaluate + // Try changing the cached value on one of the string + // formula cells, so we can see it updates properly + c3.setCellValue(new HSSFRichTextString("test")); + assertEquals(0.0, c3.getNumericCellValue(), 0.00001); + assertEquals("test", c3.getRichStringCellValue().getString()); + + + // Now evaluate, they should all be changed HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(s, wb); eval.setCurrentRow(s.getRow(0)); eval.evaluateFormulaCell(c1); eval.evaluateFormulaCell(c2); + eval.evaluateFormulaCell(c3); - // Check + // Check that the cells now contain + // the correct values assertEquals(70164.0, c1.getNumericCellValue(), 0.00001); assertEquals("", c1.getRichStringCellValue().getString()); assertEquals(0.0, c2.getNumericCellValue(), 0.00001); - - // TODO - why isn't this working? -// assertEquals("70164", c2.getRichStringCellValue().getString()); + assertEquals("70164", c2.getRichStringCellValue().getString()); + assertEquals(0.0, c3.getNumericCellValue(), 0.00001); + assertEquals("90210", c3.getRichStringCellValue().getString()); // Write and read @@ -1186,12 +1197,15 @@ public final class TestBugs extends TestCase { HSSFSheet ns = nwb.getSheetAt(0); HSSFCell nc1 = ns.getRow(0).getCell((short)0); HSSFCell nc2 = ns.getRow(0).getCell((short)1); + HSSFCell nc3 = ns.getRow(0).getCell((short)2); // Re-check assertEquals(70164.0, nc1.getNumericCellValue(), 0.00001); assertEquals("", nc1.getRichStringCellValue().getString()); assertEquals(0.0, nc2.getNumericCellValue(), 0.00001); assertEquals("70164", nc2.getRichStringCellValue().getString()); + assertEquals(0.0, nc3.getNumericCellValue(), 0.00001); + assertEquals("90210", nc3.getRichStringCellValue().getString()); // Now check record level stuff too ns.getSheet().setLoc(0); @@ -1204,15 +1218,27 @@ public final class TestBugs extends TestCase { if(fn == 0) { assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001); assertNull(fr.getStringRecord()); - } else { + } else if (fn == 1) { assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); assertNotNull(fr.getStringRecord()); assertEquals("70164", fr.getStringRecord().getString()); + } else { + assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001); + assertNotNull(fr.getStringRecord()); + assertEquals("90210", fr.getStringRecord().getString()); } fn++; } } - assertEquals(2, fn); + assertEquals(3, fn); + } + + /** + * Problem with "Vector Rows" + * @throws Exception + */ + public void test43623() throws Exception { + } } -- 2.39.5