poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r642571 - in /poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel: TestBug44297.java TestBug44410.java TestBug44508.java TestFormulaEvaluatorBugs.java
Date Sat, 29 Mar 2008 18:11:49 GMT
Author: nick
Date: Sat Mar 29 11:11:48 2008
New Revision: 642571

URL: http://svn.apache.org/viewvc?rev=642571&view=rev
Log:
Merge several bug tests into one file

Removed:
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44297.java
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44508.java
Modified:
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java

Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java?rev=642571&r1=642570&r2=642571&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
(original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
Sat Mar 29 11:11:48 2008
@@ -16,10 +16,16 @@
 ==================================================================== */
 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;
+
 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;
 
@@ -85,4 +91,167 @@
 		out.close();
 		System.err.println("New file for bug #44636 written to " + scratch.toString());
 	}
+
+	/**
+	 * Bug 44297: 32767+32768 is evaluated to -1
+	 * Fix: IntPtg must operate with unsigned short. Reading signed short results in incorrect
formula calculation
+	 * if a formula has values in the interval [Short.MAX_VALUE, (Short.MAX_VALUE+1)*2]
+	 *
+	 * @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);
+    }
+
+	/**
+	 * Bug 44410: SUM(C:C) is valid in excel, and means a sum
+	 *  of all the rows in Column C
+	 *
+	 * @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);
+    }
+
+	/**
+	 * 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());
+   }
 }



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message