From f6a7950b0c142da1a4494bb42525f579fb5fafa8 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.eu.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 + .../org/apache/poi/hssf/usermodel/HSSFCell.java | 23 +++++++++-- src/testcases/org/apache/poi/hssf/data/43623.xls | Bin 0 -> 13824 bytes .../org/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 808bea2..f6f0bce 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 21d279a..4e95db3 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 7d2a53d..dd1770f 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 zcwX&WYitx%6#iy*c6XN6?sh3qz%EN0gY>~eL7O0KF;E|YNI)J@u$0o;TCk)K1fsG& zNQ_{D;fMYp3c*B;i9$@&1fomg4@`I%B2oNRj4|>{G@955UC%jpXJ=;F0yQy8xZmVH z=G=R}d+wb(cjnCF>oetp2aim;fC4N)0K?e`LLQkxVWP7W1C?j9oORMl3R#Vt3K5$Q zWGINUQ%;4y%1|}{T!4wg)^Un~A1JKF7Q}(2U2UDMo01Q=wr%h3YJaAyZAbFo!~J4I ztHCy8XyVPMoPqr$Eu%Te(zwK9CH_e29g}!Im0Us;dz!b-Y|PEuHD;64drjhMiOax@ zpNh`+BAJc5aDwOX&b@{xK2~ltqZb3{M;D$%JLQv@gSiwh5lJVN{QtEQRwaToQm8&u zT~l9EGi%=31}B@as^}cA^primynW+3Z-oTHeA+vL8MD0lw3o1IU?btLQNSYA=Ass} zfEJe4CPm)Tpt#)KTxoSfwd^#tG$gEgOvf}<^lEuk3hK{CJsSNDkpTs5DT&)$D&-Q) zK^m3LSBe*xwLrqmWf70V(K1jGnUI&sYTI< zip(e?GmFRqMPybHS+IyKR74goBC`u*&zoZ;8)2_CohRq*?0D~qGRqQr7L`@oQld9Mqgi9$NR3CZ3B_CW*6zjyFHkhYIJ=#nQlk#@*W9u)E?8dpx= z@l;eg<|7Y!$NURc@sWo-!?K06!+!FRXISFWF&}xzJLW_l_LGNQ^22?kZTrc?LO$^B z(Thi~jFXP}$iqT@I&Zn{C%0Yl^j0A^yyUiL%qL36eB`!g%#R!s55^(?7UOXb9nk~W zj90PFvn?>=_(c4kCb*~~qClxkFkUn%7x+cS^Fep}PcGip*1kz>Nq_gv?y~3!gY($_^XZ9{=k;1c zLqkJDLqkJDLqkJD<5tJD{D1S@sdM{hOpfh)gZ%&cp%2;r=eN(1T=_DnXR~W4OrX$7 zp@hN~C?9eE!^M`|_!6zWqhi VLYvsoE=OQ=_B8&3{>SnW@HgIphPMC! literal 0 HcwPel00001 diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index 20826b9..115efc8 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.11.4.GIT