Return-Path: Delivered-To: apmail-poi-commits-archive@locus.apache.org Received: (qmail 49015 invoked from network); 7 Apr 2008 03:02:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 7 Apr 2008 03:02:38 -0000 Received: (qmail 92100 invoked by uid 500); 7 Apr 2008 03:02:38 -0000 Delivered-To: apmail-poi-commits-archive@poi.apache.org Received: (qmail 92062 invoked by uid 500); 7 Apr 2008 03:02:38 -0000 Mailing-List: contact commits-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@poi.apache.org Delivered-To: mailing list commits@poi.apache.org Received: (qmail 92053 invoked by uid 99); 7 Apr 2008 03:02:38 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Apr 2008 20:02:38 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Apr 2008 03:02:02 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 1A3DA1A983A; Sun, 6 Apr 2008 20:02:15 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r645348 [2/6] - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/util/ testcases/org/apache/poi/ testcases/org/apache/poi/hssf/ testcases/org/apache/poi/hssf/eventusermodel/ testcases/org/apache/poi/hssf/extractor/ testca... Date: Mon, 07 Apr 2008 03:02:06 -0000 To: commits@poi.apache.org From: josh@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080407030215.1A3DA1A983A@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java?rev=645348&r1=645347&r2=645348&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java Sun Apr 6 20:02:03 2008 @@ -26,6 +26,7 @@ import junit.framework.AssertionFailedError; import junit.framework.TestCase; +import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; @@ -66,17 +67,17 @@ public final static String README_SHEET_NAME = "Read Me"; - /** Row (zero-based) in each sheet where the evaluation cases start. */ + /** Row (zero-based) in each sheet where the evaluation cases start. */ public static final int START_TEST_CASES_ROW_INDEX = 4; // Row '5' /** Index of the column that contains the function names */ - public static final short COLUMN_INDEX_MARKER = 0; // Column 'A' - public static final short COLUMN_INDEX_EVALUATION = 1; // Column 'B' - public static final short COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C' - public static final short COLUMN_ROW_COMMENT = 3; // Column 'D' + public static final short COLUMN_INDEX_MARKER = 0; // Column 'A' + public static final short COLUMN_INDEX_EVALUATION = 1; // Column 'B' + public static final short COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C' + public static final short COLUMN_ROW_COMMENT = 3; // Column 'D' - /** Used to indicate when there are no more test cases on the current sheet */ + /** Used to indicate when there are no more test cases on the current sheet */ public static final String TEST_CASES_END_MARKER = ""; - /** Used to indicate that the test on the current row should be ignored */ + /** Used to indicate that the test on the current row should be ignored */ public static final String SKIP_CURRENT_TEST_CASE_MARKER = ""; } @@ -90,8 +91,8 @@ - private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { - if (expected == null) { + private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { + if (expected == null) { throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); } if(actual == null) { @@ -108,21 +109,21 @@ throw wrongTypeError(msg, expected, actual); } - + switch (expected.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: - assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); - break; + assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); + break; case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation - throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); + throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); case HSSFCell.CELL_TYPE_NUMERIC: - assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0); - break; + assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0); + break; case HSSFCell.CELL_TYPE_STRING: - assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); - break; + assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); + break; } - } + } private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) { @@ -178,55 +179,48 @@ protected void setUp() throws Exception { - _sheetFailureCount = 0; - _sheetSuccessCount = 0; - _evaluationFailureCount = 0; - _evaluationSuccessCount = 0; - } - - public void testFunctionsFromTestSpreadsheet() { - String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME; - HSSFWorkbook workbook; - try { - FileInputStream fin = new FileInputStream( filePath ); - workbook = new HSSFWorkbook( fin ); - } catch (IOException e) { - throw new RuntimeException(e); - } + _sheetFailureCount = 0; + _sheetSuccessCount = 0; + _evaluationFailureCount = 0; + _evaluationSuccessCount = 0; + } + + public void testFunctionsFromTestSpreadsheet() { + HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook(SS.FILENAME); - confirmReadMeSheet(workbook); - int nSheets = workbook.getNumberOfSheets(); - for(int i=1; i< nSheets; i++) { - int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i)); - switch(sheetResult) { - case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break; - case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break; - } - } - - // confirm results - String successMsg = "There were " - + _sheetSuccessCount + " successful sheets(s) and " + confirmReadMeSheet(workbook); + int nSheets = workbook.getNumberOfSheets(); + for(int i=1; i< nSheets; i++) { + int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i)); + switch(sheetResult) { + case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break; + case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break; + } + } + + // confirm results + String successMsg = "There were " + + _sheetSuccessCount + " successful sheets(s) and " + _evaluationSuccessCount + " function(s) without error"; if(_sheetFailureCount > 0) { String msg = _sheetFailureCount + " sheets(s) failed with " + _evaluationFailureCount + " evaluation(s). " + successMsg; - throw new AssertionFailedError(msg); - } + throw new AssertionFailedError(msg); + } if(false) { // normally no output for successful tests System.out.println(getClass().getName() + ": " + successMsg); } } - private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) { + private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) { HSSFSheet sheet = workbook.getSheetAt(sheetIndex); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); int maxRows = sheet.getLastRowNum()+1; - int result = Result.NO_EVALUATIONS_FOUND; // so far + int result = Result.NO_EVALUATIONS_FOUND; // so far String currentGroupComment = null; for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndexnull if cell is missing, empty or blank - */ + */ private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) { if(r == null) { return null; Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java?rev=645348&r1=645347&r2=645348&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java Sun Apr 6 20:02:03 2008 @@ -14,42 +14,35 @@ See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ + package org.apache.poi.hssf.usermodel; -import java.io.File; -import java.io.FileInputStream; import java.util.Iterator; import java.util.List; import junit.framework.TestCase; +import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; import org.apache.poi.hssf.util.CellReference; +/** + * + */ public final class TestBug42464 extends TestCase { - String dirname; - - protected void setUp() throws Exception { - super.setUp(); - dirname = System.getProperty("HSSF.testdata.path"); - } public void testOKFile() throws Exception { - HSSFWorkbook wb = new HSSFWorkbook( - new FileInputStream(new File(dirname,"42464-ExpPtg-ok.xls")) - ); + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("42464-ExpPtg-ok.xls"); process(wb); } public void testExpSharedBadFile() throws Exception { - HSSFWorkbook wb = new HSSFWorkbook( - new FileInputStream(new File(dirname,"42464-ExpPtg-bad.xls")) - ); + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("42464-ExpPtg-bad.xls"); process(wb); } - protected void process(HSSFWorkbook wb) { + private static void process(HSSFWorkbook wb) { for(int i=0; i0) { // fetch the first merged region...EXCEPTION OCCURS HERE template.getMergedRegionAt(0); - } + } //make sure we dont exception - + } - + /*Tests read and write of Unicode strings in formula results * bug and testcase submitted by Sompop Kumnoonsate - * The file contains THAI unicode characters. + * The file contains THAI unicode characters. */ - public void testUnicodeStringFormulaRead() throws Exception { - - String filename = System.getProperty("HSSF.testdata.path"); - filename=filename+"/25695.xls"; - FileInputStream in = new FileInputStream(filename); - HSSFWorkbook w; - w = new HSSFWorkbook(in); - in.close(); - - HSSFCell a1 = w.getSheetAt(0).getRow(0).getCell((short) 0); - HSSFCell a2 = w.getSheetAt(0).getRow(0).getCell((short) 1); - HSSFCell b1 = w.getSheetAt(0).getRow(1).getCell((short) 0); - HSSFCell b2 = w.getSheetAt(0).getRow(1).getCell((short) 1); - HSSFCell c1 = w.getSheetAt(0).getRow(2).getCell((short) 0); - HSSFCell c2 = w.getSheetAt(0).getRow(2).getCell((short) 1); - HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell((short) 0); - HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell((short) 1); - - /* // THAI code page - System.out.println("a1="+unicodeString(a1.getStringCellValue())); - System.out.println("a2="+unicodeString(a2.getStringCellValue())); - // US code page - System.out.println("b1="+unicodeString(b1.getStringCellValue())); - System.out.println("b2="+unicodeString(b2.getStringCellValue())); - // THAI+US - System.out.println("c1="+unicodeString(c1.getStringCellValue())); - System.out.println("c2="+unicodeString(c2.getStringCellValue())); - // US+THAI - System.out.println("d1="+unicodeString(d1.getStringCellValue())); - System.out.println("d2="+unicodeString(d2.getStringCellValue())); -*/ - assertEquals("String Cell value", a1.getStringCellValue(), a2.getStringCellValue()); - assertEquals("String Cell value", b1.getStringCellValue(), b2.getStringCellValue()); - assertEquals("String Cell value", c1.getStringCellValue(), c2.getStringCellValue()); - assertEquals("String Cell value", d1.getStringCellValue(), d2.getStringCellValue()); - - File xls = TempFile.createTempFile("testFormulaUnicode", ".xls"); - FileOutputStream out = new FileOutputStream(xls); - w.write(out); - out.close(); - in = new FileInputStream(xls); - - HSSFWorkbook rw = new HSSFWorkbook(in); - in.close(); - - HSSFCell ra1 = rw.getSheetAt(0).getRow(0).getCell((short) 0); - HSSFCell ra2 = rw.getSheetAt(0).getRow(0).getCell((short) 1); - HSSFCell rb1 = rw.getSheetAt(0).getRow(1).getCell((short) 0); - HSSFCell rb2 = rw.getSheetAt(0).getRow(1).getCell((short) 1); - HSSFCell rc1 = rw.getSheetAt(0).getRow(2).getCell((short) 0); - HSSFCell rc2 = rw.getSheetAt(0).getRow(2).getCell((short) 1); - HSSFCell rd1 = rw.getSheetAt(0).getRow(3).getCell((short) 0); - HSSFCell rd2 = rw.getSheetAt(0).getRow(3).getCell((short) 1); - - assertEquals("Re-Written String Cell value", a1.getStringCellValue(), ra1.getStringCellValue()); - assertEquals("Re-Written String Cell value", b1.getStringCellValue(), rb1.getStringCellValue()); - assertEquals("Re-Written String Cell value", c1.getStringCellValue(), rc1.getStringCellValue()); - assertEquals("Re-Written String Cell value", d1.getStringCellValue(), rd1.getStringCellValue()); - assertEquals("Re-Written Formula String Cell value", a1.getStringCellValue(), ra2.getStringCellValue()); - assertEquals("Re-Written Formula String Cell value", b1.getStringCellValue(), rb2.getStringCellValue()); - assertEquals("Re-Written Formula String Cell value", c1.getStringCellValue(), rc2.getStringCellValue()); - assertEquals("Re-Written Formula String Cell value", d1.getStringCellValue(), rd2.getStringCellValue()); - - } - - private static String unicodeString(String ss) { + public void testUnicodeStringFormulaRead() { + + HSSFWorkbook w = openSample("25695.xls"); + + HSSFCell a1 = w.getSheetAt(0).getRow(0).getCell((short) 0); + HSSFCell a2 = w.getSheetAt(0).getRow(0).getCell((short) 1); + HSSFCell b1 = w.getSheetAt(0).getRow(1).getCell((short) 0); + HSSFCell b2 = w.getSheetAt(0).getRow(1).getCell((short) 1); + HSSFCell c1 = w.getSheetAt(0).getRow(2).getCell((short) 0); + HSSFCell c2 = w.getSheetAt(0).getRow(2).getCell((short) 1); + HSSFCell d1 = w.getSheetAt(0).getRow(3).getCell((short) 0); + HSSFCell d2 = w.getSheetAt(0).getRow(3).getCell((short) 1); + + if (false) { + // THAI code page + System.out.println("a1="+unicodeString(a1)); + System.out.println("a2="+unicodeString(a2)); + // US code page + System.out.println("b1="+unicodeString(b1)); + System.out.println("b2="+unicodeString(b2)); + // THAI+US + System.out.println("c1="+unicodeString(c1)); + System.out.println("c2="+unicodeString(c2)); + // US+THAI + System.out.println("d1="+unicodeString(d1)); + System.out.println("d2="+unicodeString(d2)); + } + confirmSameCellText(a1, a2); + confirmSameCellText(b1, b2); + confirmSameCellText(c1, c2); + confirmSameCellText(d1, d2); + + HSSFWorkbook rw = writeOutAndReadBack(w); + + HSSFCell ra1 = rw.getSheetAt(0).getRow(0).getCell((short) 0); + HSSFCell ra2 = rw.getSheetAt(0).getRow(0).getCell((short) 1); + HSSFCell rb1 = rw.getSheetAt(0).getRow(1).getCell((short) 0); + HSSFCell rb2 = rw.getSheetAt(0).getRow(1).getCell((short) 1); + HSSFCell rc1 = rw.getSheetAt(0).getRow(2).getCell((short) 0); + HSSFCell rc2 = rw.getSheetAt(0).getRow(2).getCell((short) 1); + HSSFCell rd1 = rw.getSheetAt(0).getRow(3).getCell((short) 0); + HSSFCell rd2 = rw.getSheetAt(0).getRow(3).getCell((short) 1); + + confirmSameCellText(a1, ra1); + confirmSameCellText(b1, rb1); + confirmSameCellText(c1, rc1); + confirmSameCellText(d1, rd1); + + confirmSameCellText(a1, ra2); + confirmSameCellText(b1, rb2); + confirmSameCellText(c1, rc2); + confirmSameCellText(d1, rd2); + } + + private static void confirmSameCellText(HSSFCell a, HSSFCell b) { + assertEquals(a.getRichStringCellValue().getString(), b.getRichStringCellValue().getString()); + } + private static String unicodeString(HSSFCell cell) { + String ss = cell.getRichStringCellValue().getString(); char s[] = ss.toCharArray(); - java.lang.StringBuffer sb=new java.lang.StringBuffer(); + StringBuffer sb = new StringBuffer(); for (int x=0;xROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))"; + "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))"; cell.setCellFormula(formulaText); assertEquals(formulaText, cell.getCellFormula()); - if(false) { - // this file can be inspected manually - try { - OutputStream os = new FileOutputStream("/tmp/output28031.xls"); - wb.write(os); - os.close(); - } catch (IOException e) { - throw new RuntimeException(e); - } + writeTestOutputFileForViewing(wb, "output28031.xls"); + } + + public void test33082() { + openSample("33082.xls"); + } + + public void test34775() { + try { + openSample("34775.xls"); + } catch (NullPointerException e) { + throw new AssertionFailedError("identified bug 34775"); } } - - public void test33082() throws java.io.IOException { - String filename = System.getProperty("HSSF.testdata.path"); - filename=filename+"/33082.xls"; - FileInputStream in = new FileInputStream(filename); - HSSFWorkbook wb = new HSSFWorkbook(in); - assertTrue("Read book fine!" , true); - } - - /*NullPointerException on reading file*/ - public void test34775() throws java.io.IOException { - String filename = System.getProperty("HSSF.testdata.path"); - filename=filename+"/34775.xls"; - FileInputStream in = new FileInputStream(filename); - HSSFWorkbook wb = new HSSFWorkbook(in); - assertTrue("Read book fine!" , true); - } - - /** Error when reading then writing ArrayValues in NameRecord's*/ - public void test37630() throws java.io.IOException { - String filename = System.getProperty("HSSF.testdata.path"); - filename=filename+"/37630.xls"; - FileInputStream in = new FileInputStream(filename); - HSSFWorkbook wb = new HSSFWorkbook(in); - File file = TempFile.createTempFile("test37630",".xls"); - FileOutputStream out = new FileOutputStream(file); - wb.write(out); - - assertTrue("Read book fine!" , true); - } - - protected String cwd = System.getProperty("HSSF.testdata.path"); + + /** Error when reading then writing ArrayValues in NameRecord's*/ + public void test37630() { + HSSFWorkbook wb = openSample("37630.xls"); + writeOutAndReadBack(wb); + } /** * Bug 25183: org.apache.poi.hssf.usermodel.HSSFSheet.setPropertiesFromSheet */ - public void test25183() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "25183.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test25183() { + HSSFWorkbook wb = openSample("25183.xls"); + writeOutAndReadBack(wb); } /** * Bug 26100: 128-character message in IF statement cell causes HSSFWorkbook open failure */ - public void test26100() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "26100.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test26100() { + HSSFWorkbook wb = openSample("26100.xls"); + writeOutAndReadBack(wb); } /** * Bug 27933: Unable to use a template (xls) file containing a wmf graphic */ - public void test27933() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "27933.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test27933() { + HSSFWorkbook wb = openSample("27933.xls"); + writeOutAndReadBack(wb); } /** - * Bug 29206: NPE on HSSFSheet.getRow for blank rows + * Bug 29206: NPE on HSSFSheet.getRow for blank rows */ - public void test29206() throws Exception { + public void test29206() { //the first check with blank workbook HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); @@ -629,50 +485,34 @@ for(int i = 1; i < 400; i++) { HSSFRow row = sheet.getRow(i); if(row != null) { - HSSFCell cell = row.getCell((short)0); + row.getCell((short)0); } } //now check on an existing xls file - FileInputStream in = new FileInputStream(new File(cwd, "Simple.xls")); - wb = new HSSFWorkbook(in); - in.close(); + wb = openSample("Simple.xls"); for(int i = 1; i < 400; i++) { HSSFRow row = sheet.getRow(i); if(row != null) { - HSSFCell cell = row.getCell((short)0); + row.getCell((short)0); } } - - assertTrue("No Exceptions while reading file", true); } /** * Bug 29675: POI 2.5 final corrupts output when starting workbook has a graphic */ - public void test29675() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "29675.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test29675() { + HSSFWorkbook wb = openSample("29675.xls"); + writeOutAndReadBack(wb); } /** * Bug 29942: Importing Excel files that have been created by Open Office on Linux */ - public void test29942() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "29942.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test29942() { + HSSFWorkbook wb = openSample("29942.xls"); HSSFSheet sheet = wb.getSheetAt(0); int count = 0; @@ -685,170 +525,84 @@ } } assertEquals(85, count); //should read 85 rows - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + writeOutAndReadBack(wb); } /** * Bug 29982: Unable to read spreadsheet when dropdown list cell is selected - * Unable to construct record instance */ - public void test29982() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "29982.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test29982() { + HSSFWorkbook wb = openSample("29982.xls"); + writeOutAndReadBack(wb); } /** * Bug 30540: HSSFSheet.setRowBreak throws NullPointerException */ - public void test30540() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "30540.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test30540() { + HSSFWorkbook wb = openSample("30540.xls"); HSSFSheet s = wb.getSheetAt(0); s.setRowBreak(1); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + writeOutAndReadBack(wb); } /** * Bug 31749: {Need help urgently}[This is critical] workbook.write() corrupts the file......? */ - public void test31749() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "31749.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test31749() { + HSSFWorkbook wb = openSample("31749.xls"); + writeOutAndReadBack(wb); } /** * Bug 31979: {urgent help needed .....}poi library does not support form objects properly. */ - public void test31979() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "31979.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - //wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + public void test31979() { + HSSFWorkbook wb = openSample("31979.xls"); + writeOutAndReadBack(wb); } /** * Bug 35564: HSSFCell.java: NullPtrExc in isGridsPrinted() and getProtect() * when HSSFWorkbook is created from file */ - public void test35564() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "35564.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test35564() { + HSSFWorkbook wb = openSample("35564.xls"); HSSFSheet sheet = wb.getSheetAt( 0 ); assertEquals(false, sheet.isGridsPrinted()); assertEquals(false, sheet.getProtect()); - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + writeOutAndReadBack(wb); } /** * Bug 35565: HSSFCell.java: NullPtrExc in getColumnBreaks() when HSSFWorkbook is created from file */ - public void test35565() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "35565.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test35565() { + HSSFWorkbook wb = openSample("35565.xls"); HSSFSheet sheet = wb.getSheetAt( 0 ); assertNotNull(sheet); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + writeOutAndReadBack(wb); } /** * Bug 37376: Cannot open the saved Excel file if checkbox controls exceed certain limit */ - public void test37376() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "37376.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + public void test37376() { + HSSFWorkbook wb = openSample("37376.xls"); + writeOutAndReadBack(wb); } /** - * Bug 40285: CellIterator Skips First Column + * Bug 40285: CellIterator Skips First Column */ - public void test40285() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "40285.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test40285() { + HSSFWorkbook wb = openSample("40285.xls"); HSSFSheet sheet = wb.getSheetAt( 0 ); int rownum = 0; @@ -864,18 +618,16 @@ } /** - * Bug 40296: HSSFCell.setCellFormula throws + * Bug 40296: HSSFCell.setCellFormula throws * ClassCastException if cell is created using HSSFRow.createCell(short column, int type) */ - public void test40296() throws Exception { + public void test40296() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet workSheet = workBook.createSheet("Sheet1"); HSSFCell cell; - HSSFRow row; - - row = workSheet.createRow(0); + HSSFRow row = workSheet.createRow(0); cell = row.createCell((short)0, HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(1.0); cell = row.createCell((short)1, HSSFCell.CELL_TYPE_NUMERIC); @@ -883,13 +635,7 @@ cell = row.createCell((short)2, HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("SUM(A1:B1)"); - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + writeOutAndReadBack(wb); } /** @@ -902,12 +648,10 @@ * 3. Try adding a row break (via sheet.setRowBreak()) to the sheet mentioned in step #1 * 4. Get a NullPointerException */ - public void test38266() throws Exception { + public void test38266() { String[] files = {"Simple.xls", "SimpleMultiCell.xls", "duprich1.xls"}; for (int i = 0; i < files.length; i++) { - FileInputStream in = new FileInputStream(new File(cwd, files[i])); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + HSSFWorkbook wb = openSample(files[i]); HSSFSheet sheet = wb.getSheetAt( 0 ); int[] breaks = sheet.getRowBreaks(); @@ -917,142 +661,60 @@ for (int j = 1; j <= 3; j++) { sheet.setRowBreak(j*20); } - - assertTrue("No Exceptions while adding row breaks in " + files[i], true); } } - public void test40738() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "SimpleWithAutofilter.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + public void test40738() { + HSSFWorkbook wb = openSample("SimpleWithAutofilter.xls"); + writeOutAndReadBack(wb); } /** * Bug 44200: Sheet not cloneable when Note added to excel cell */ - public void test44200() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "44200.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); + public void test44200() { + HSSFWorkbook wb = openSample("44200.xls"); wb.cloneSheet(0); - assertTrue("No Exceptions while cloning sheet", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + writeOutAndReadBack(wb); } /** * Bug 44201: Sheet not cloneable when validation added to excel cell */ - public void test44201() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "44201.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - wb.cloneSheet(0); - assertTrue("No Exceptions while cloning sheet", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + public void test44201() { + HSSFWorkbook wb = openSample("44201.xls"); + writeOutAndReadBack(wb); } /** * Bug 37684 : Unhandled Continue Record Error */ - public void test37684 () throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "37684-1.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No exceptions while reading workbook", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - assertTrue("No exceptions while saving workbook", true); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No exceptions while reading saved stream", true); - - - in = new FileInputStream(new File(cwd, "37684-2.xls")); - wb = new HSSFWorkbook(in); - in.close(); + public void test37684 () { + HSSFWorkbook wb = openSample("37684-1.xls"); + writeOutAndReadBack(wb); - assertTrue("No exceptions while reading workbook", true); - //serialize and read again - out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - assertTrue("No exceptions while saving workbook", true); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No exceptions while reading saved stream", true); + wb = openSample("37684-2.xls"); + writeOutAndReadBack(wb); } /** * Bug 41139: Constructing HSSFWorkbook is failed,threw threw ArrayIndexOutOfBoundsException for creating UnknownRecord */ - public void test41139() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "41139.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test41139() { + HSSFWorkbook wb = openSample("41139.xls"); + writeOutAndReadBack(wb); } /** * Bug 41546: Constructing HSSFWorkbook is failed, * Unknown Ptg in Formula: 0x1a (26) */ - public void test41546() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "41546.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); + public void test41546() { + HSSFWorkbook wb = openSample("41546.xls"); assertEquals(1, wb.getNumberOfSheets()); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + wb = writeOutAndReadBack(wb); assertEquals(1, wb.getNumberOfSheets()); } @@ -1060,189 +722,159 @@ * Bug 42564: Some files from Access were giving a RecordFormatException * when reading the BOFRecord */ - public void test42564() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "42564.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); + public void test42564() { + HSSFWorkbook wb = openSample("42564.xls"); + writeOutAndReadBack(wb); } - + /** * Bug 42564: Some files from Access also have issues * with the NameRecord, once you get past the BOFRecord * issue. - * TODO - still broken */ - public void DISABLEDtest42564Alt() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "42564-2.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - } - - /** - * Bug 42618: RecordFormatException reading a file containing - * =CHOOSE(2,A2,A3,A4) - * TODO - support getCellFormula too! - */ - public void test42618() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "SimpleWithChoose.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - assertTrue("No Exceptions while reading file", true); - - //serialize and read again - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wb.write(out); - out.close(); - - wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue("No Exceptions while reading file", true); - + public void DISABLEDtest42564Alt() { + HSSFWorkbook wb = openSample("42564-2.xls"); + writeOutAndReadBack(wb); + } + + /** + * Bug 42618: RecordFormatException reading a file containing + * =CHOOSE(2,A2,A3,A4) + */ + public void test42618() { + HSSFWorkbook wb = openSample("SimpleWithChoose.xls"); + wb = writeOutAndReadBack(wb); // Check we detect the string properly too HSSFSheet s = wb.getSheetAt(0); - + // Textual value HSSFRow r1 = s.getRow(0); HSSFCell c1 = r1.getCell((short)1); assertEquals("=CHOOSE(2,A2,A3,A4)", c1.getRichStringCellValue().toString()); - + // Formula Value HSSFRow r2 = s.getRow(1); HSSFCell c2 = r2.getCell((short)1); assertEquals(25, (int)c2.getNumericCellValue()); - - // This will blow up with a - // "EmptyStackException" - //assertEquals("=CHOOSE(2,A2,A3,A4)", c2.getCellFormula()); + + if (false) { // TODO (Apr-2008) This will blow up with IllegalStateException (stack underflow) + // excel function "CHOOSE" probably needs some special handling in FormulaParser.toFormulaString() + assertEquals("=CHOOSE(2,A2,A3,A4)", c2.getCellFormula()); + } } - + /** * Something up with the FileSharingRecord */ - public void test43251() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "43251.xls")); - + public void test43251() { + // Used to blow up with an IllegalArgumentException // when creating a FileSharingRecord - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - + HSSFWorkbook wb; + try { + wb = openSample("43251.xls"); + } catch (IllegalArgumentException e) { + throw new AssertionFailedError("identified bug 43251"); + } + assertEquals(1, wb.getNumberOfSheets()); } - + /** - * Crystal reports generates files with short + * Crystal reports generates files with short * StyleRecords, which is against the spec */ - public void test44471() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "OddStyleRecord.xls")); - + public void test44471() { + // Used to blow up with an ArrayIndexOutOfBounds // when creating a StyleRecord - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - + HSSFWorkbook wb; + try { + wb = openSample("OddStyleRecord.xls"); + } catch (ArrayIndexOutOfBoundsException e) { + throw new AssertionFailedError("Identified bug 44471"); + } + assertEquals(1, wb.getNumberOfSheets()); } - + /** * Files with "read only recommended" were giving * grief on the FileSharingRecord */ - public void test44536() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "ReadOnlyRecommended.xls")); - + public void test44536() { + // Used to blow up with an IllegalArgumentException // when creating a FileSharingRecord - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - + HSSFWorkbook wb = openSample("ReadOnlyRecommended.xls"); + // Check read only advised assertEquals(3, wb.getNumberOfSheets()); assertTrue(wb.isWriteProtected()); - + // But also check that another wb isn't - in = new FileInputStream(new File(cwd, "SimpleWithChoose.xls")); - wb = new HSSFWorkbook(in); - in.close(); + wb = openSample("SimpleWithChoose.xls"); assertFalse(wb.isWriteProtected()); } - + /** * Some files were having problems with the DVRecord, * probably due to dropdowns */ - public void test44593() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "44593.xls")); - + public void test44593() { + // Used to blow up with an IllegalArgumentException // when creating a DVRecord // Now won't, but no idea if this means we have // rubbish in the DVRecord or not... - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - + HSSFWorkbook wb; + try { + wb = openSample("44593.xls"); + } catch (IllegalArgumentException e) { + throw new AssertionFailedError("Identified bug 44593"); + } + assertEquals(2, wb.getNumberOfSheets()); } - + /** * Used to give problems due to trying to read a zero * length string, but that's now properly handled */ - public void test44643() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "44643.xls")); - + public void test44643() { + // Used to blow up with an IllegalArgumentException - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - + HSSFWorkbook wb; + try { + wb = openSample("44643.xls"); + } catch (IllegalArgumentException e) { + throw new AssertionFailedError("identified bug 44643"); + } + assertEquals(1, wb.getNumberOfSheets()); } - + /** * User reported the wrong number of rows from the * iterator, but we can't replicate that */ - public void test44693() throws Exception { - FileInputStream in = new FileInputStream(new File(cwd, "44693.xls")); - - HSSFWorkbook wb = new HSSFWorkbook(in); + public void test44693() { + + HSSFWorkbook wb = openSample("44693.xls"); HSSFSheet s = wb.getSheetAt(0); // Rows are 1 to 713 assertEquals(0, s.getFirstRowNum()); assertEquals(712, s.getLastRowNum()); assertEquals(713, s.getPhysicalNumberOfRows()); - + // Now check the iterator int rowsSeen = 0; for(Iterator i = s.rowIterator(); i.hasNext(); ) { - HSSFRow r = (HSSFRow)i.next(); - rowsSeen++; + HSSFRow r = (HSSFRow)i.next(); + assertNotNull(r); + rowsSeen++; } assertEquals(713, rowsSeen); } } - - - Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java?rev=645348&r1=645347&r2=645348&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java Sun Apr 6 20:02:03 2008 @@ -14,31 +14,31 @@ See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.usermodel; -import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; -import org.apache.poi.hssf.record.formula.AreaPtg; -import org.apache.poi.hssf.record.formula.FuncVarPtg; +package org.apache.poi.hssf.usermodel; import java.io.File; -import java.io.FileInputStream; import java.io.FileOutputStream; -import java.io.IOException; -import java.util.Iterator; import java.util.List; import junit.framework.TestCase; +import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.FuncVarPtg; +/** + * + */ public final class TestFormulaEvaluatorBugs extends TestCase { - private String dirName; + private String tmpDirName; - protected void setUp() throws Exception { - super.setUp(); - dirName = System.getProperty("HSSF.testdata.path"); + protected void setUp() { + tmpDirName = System.getProperty("java.io.tmpdir"); } - + /** * An odd problem with evaluateFormulaCell giving the * right values when file is opened, but changes @@ -51,39 +51,39 @@ public void test44636() throws Exception { // Open the existing file, tweak one value and // re-calculate - FileInputStream in = new FileInputStream(new File(dirName,"44636.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); + + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("44636.xls"); HSSFSheet sheet = wb.getSheetAt (0); HSSFRow row = sheet.getRow (0); - + row.getCell((short)0).setCellValue(4.2); row.getCell((short)2).setCellValue(25); - + HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); assertEquals(4.2*25, row.getCell((short)3).getNumericCellValue(), 0.0001); - + // Save File existing = new File(tmpDirName,"44636-existing.xls"); FileOutputStream out = new FileOutputStream(existing); wb.write(out); out.close(); System.err.println("Existing file for bug #44636 written to " + existing.toString()); - - + + // Now, do a new file from scratch wb = new HSSFWorkbook(); sheet = wb.createSheet(); - + row = sheet.createRow(0); row.createCell((short)0).setCellValue(1.2); row.createCell((short)1).setCellValue(4.2); - + row = sheet.createRow(1); row.createCell((short)0).setCellFormula("SUM(A1:B1)"); - + HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); assertEquals(5.4, row.getCell((short)0).getNumericCellValue(), 0.0001); - + // Save File scratch = new File(tmpDirName,"44636-scratch.xls"); out = new FileOutputStream(scratch); @@ -99,72 +99,71 @@ * * @author Yegor Kozlov */ - public void test44297() throws IOException { - FileInputStream in = new FileInputStream(new File(dirName, "44297.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - HSSFRow row; - HSSFCell cell; - - HSSFSheet sheet = wb.getSheetAt(0); - - HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); - - row = (HSSFRow)sheet.getRow(0); - cell = row.getCell((short)0); - assertEquals("31+46", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(77, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(1); - cell = row.getCell((short)0); - assertEquals("30+53", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(83, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(2); - cell = row.getCell((short)0); - assertEquals("SUM(A1:A2)", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(160, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(4); - cell = row.getCell((short)0); - assertEquals("32767+32768", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(65535, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(7); - cell = row.getCell((short)0); - assertEquals("32744+42333", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(75077, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(8); - cell = row.getCell((short)0); - assertEquals("327680.0/32768", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(10, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(9); - cell = row.getCell((short)0); - assertEquals("32767+32769", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(65536, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(10); - cell = row.getCell((short)0); - assertEquals("35000+36000", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(71000, eva.evaluate(cell).getNumberValue(), 0); - - row = (HSSFRow)sheet.getRow(11); - cell = row.getCell((short)0); - assertEquals("-1000000.0-3000000.0", cell.getCellFormula()); - eva.setCurrentRow(row); - assertEquals(-4000000, eva.evaluate(cell).getNumberValue(), 0); - } + public void test44297() { + + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("44297.xls"); + + HSSFRow row; + HSSFCell cell; + + HSSFSheet sheet = wb.getSheetAt(0); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); + + row = sheet.getRow(0); + cell = row.getCell((short)0); + assertEquals("31+46", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(77, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(1); + cell = row.getCell((short)0); + assertEquals("30+53", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(83, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(2); + cell = row.getCell((short)0); + assertEquals("SUM(A1:A2)", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(160, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(4); + cell = row.getCell((short)0); + assertEquals("32767+32768", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(65535, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(7); + cell = row.getCell((short)0); + assertEquals("32744+42333", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(75077, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(8); + cell = row.getCell((short)0); + assertEquals("327680.0/32768", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(10, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(9); + cell = row.getCell((short)0); + assertEquals("32767+32769", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(65536, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(10); + cell = row.getCell((short)0); + assertEquals("35000+36000", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(71000, eva.evaluate(cell).getNumberValue(), 0); + + row = sheet.getRow(11); + cell = row.getCell((short)0); + assertEquals("-1000000.0-3000000.0", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(-4000000, eva.evaluate(cell).getNumberValue(), 0); + } /** * Bug 44410: SUM(C:C) is valid in excel, and means a sum @@ -172,86 +171,85 @@ * * @author Nick Burch */ - public void test44410() throws IOException { - FileInputStream in = new FileInputStream(new File(dirName, "SingleLetterRanges.xls")); - HSSFWorkbook wb = new HSSFWorkbook(in); - in.close(); - - HSSFSheet sheet = wb.getSheetAt(0); - - HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); - - // =index(C:C,2,1) -> 2 - HSSFRow rowIDX = (HSSFRow)sheet.getRow(3); - // =sum(C:C) -> 6 - HSSFRow rowSUM = (HSSFRow)sheet.getRow(4); - // =sum(C:D) -> 66 - HSSFRow rowSUM2D = (HSSFRow)sheet.getRow(5); - - // Test the sum - HSSFCell cellSUM = rowSUM.getCell((short)0); - - FormulaRecordAggregate frec = - (FormulaRecordAggregate)cellSUM.getCellValueRecord(); - List ops = frec.getFormulaRecord().getParsedExpression(); - assertEquals(2, ops.size()); - assertEquals(AreaPtg.class, ops.get(0).getClass()); - assertEquals(FuncVarPtg.class, ops.get(1).getClass()); - - // Actually stored as C1 to C65536 - // (last row is -1 === 65535) - AreaPtg ptg = (AreaPtg)ops.get(0); - assertEquals(2, ptg.getFirstColumn()); - assertEquals(2, ptg.getLastColumn()); - assertEquals(0, ptg.getFirstRow()); - assertEquals(65535, ptg.getLastRow()); - assertEquals("C:C", ptg.toFormulaString(wb.getWorkbook())); - - // Will show as C:C, but won't know how many - // rows it covers as we don't have the sheet - // to hand when turning the Ptgs into a string - assertEquals("SUM(C:C)", cellSUM.getCellFormula()); - eva.setCurrentRow(rowSUM); - - // But the evaluator knows the sheet, so it - // can do it properly - assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0); - - - // Test the index - // Again, the formula string will be right but - // lacking row count, evaluated will be right - HSSFCell cellIDX = rowIDX.getCell((short)0); - assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula()); - eva.setCurrentRow(rowIDX); - assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0); - - // Across two colums - HSSFCell cellSUM2D = rowSUM2D.getCell((short)0); - assertEquals("SUM(C:D)", cellSUM2D.getCellFormula()); - eva.setCurrentRow(rowSUM2D); - assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0); - } + public void test44410() { + + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("SingleLetterRanges.xls"); + + HSSFSheet sheet = wb.getSheetAt(0); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); + + // =index(C:C,2,1) -> 2 + HSSFRow rowIDX = sheet.getRow(3); + // =sum(C:C) -> 6 + HSSFRow rowSUM = sheet.getRow(4); + // =sum(C:D) -> 66 + HSSFRow rowSUM2D = sheet.getRow(5); + + // Test the sum + HSSFCell cellSUM = rowSUM.getCell((short)0); + + FormulaRecordAggregate frec = + (FormulaRecordAggregate)cellSUM.getCellValueRecord(); + List ops = frec.getFormulaRecord().getParsedExpression(); + assertEquals(2, ops.size()); + assertEquals(AreaPtg.class, ops.get(0).getClass()); + assertEquals(FuncVarPtg.class, ops.get(1).getClass()); + + // Actually stored as C1 to C65536 + // (last row is -1 === 65535) + AreaPtg ptg = (AreaPtg)ops.get(0); + assertEquals(2, ptg.getFirstColumn()); + assertEquals(2, ptg.getLastColumn()); + assertEquals(0, ptg.getFirstRow()); + assertEquals(65535, ptg.getLastRow()); + assertEquals("C:C", ptg.toFormulaString(wb.getWorkbook())); + + // Will show as C:C, but won't know how many + // rows it covers as we don't have the sheet + // to hand when turning the Ptgs into a string + assertEquals("SUM(C:C)", cellSUM.getCellFormula()); + eva.setCurrentRow(rowSUM); + + // But the evaluator knows the sheet, so it + // can do it properly + assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0); + + + // Test the index + // Again, the formula string will be right but + // lacking row count, evaluated will be right + HSSFCell cellIDX = rowIDX.getCell((short)0); + assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula()); + eva.setCurrentRow(rowIDX); + assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0); + + // Across two colums + HSSFCell cellSUM2D = rowSUM2D.getCell((short)0); + assertEquals("SUM(C:D)", cellSUM2D.getCellFormula()); + eva.setCurrentRow(rowSUM2D); + assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0); + } /** * Tests that we can evaluate boolean cells properly */ - public void testEvaluateBooleanInCell_bug44508() { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sheet = wb.createSheet(); - wb.setSheetName(0, "Sheet1"); - HSSFRow row = sheet.createRow(0); - HSSFCell cell = row.createCell((short)0); - - cell.setCellFormula("1=1"); - - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); - fe.setCurrentRow(row); - try { - fe.evaluateInCell(cell); - } catch (NumberFormatException e) { - fail("Identified bug 44508"); - } - assertEquals(true, cell.getBooleanCellValue()); - } + public void testEvaluateBooleanInCell_bug44508() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + wb.setSheetName(0, "Sheet1"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell((short)0); + + cell.setCellFormula("1=1"); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + try { + fe.evaluateInCell(cell); + } catch (NumberFormatException e) { + fail("Identified bug 44508"); + } + assertEquals(true, cell.getBooleanCellValue()); + } } --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org For additional commands, e-mail: commits-help@poi.apache.org