testbugs.java

来自「EXCEL read and write」· Java 代码 · 共 1,523 行 · 第 1/4 页

    }

    /**
     * From the mailing list - ensure we can handle a formula
     *  containing a zip code, eg ="70164"
     */
    public void testZipCodeFormulas() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet s = wb.createSheet();
        s.createRow(0);
        HSSFCell c1 = s.getRow(0).createCell(0);
        HSSFCell c2 = s.getRow(0).createCell(1);
        HSSFCell c3 = s.getRow(0).createCell(2);

        // As number and string
        c1.setCellFormula("70164");
        c2.setCellFormula("\"70164\"");
        c3.setCellFormula("\"90210\"");

        // Check the formulas
        assertEquals("70164.0", c1.getCellFormula());
        assertEquals("\"70164\"", c2.getCellFormula());

        // And check the values - blank
        confirmCachedValue(0.0, c1);
        confirmCachedValue(0.0, c2);
        confirmCachedValue(0.0, c3);

        // Try changing the cached value on one of the string
        //  formula cells, so we can see it updates properly
        c3.setCellValue(new HSSFRichTextString("test"));
        confirmCachedValue("test", c3);
        try {
            c3.getNumericCellValue();
            throw new AssertionFailedError("exception should have been thrown");
        } catch (IllegalStateException e) {
            assertEquals("Cannot get a numeric value from a text formula cell", e.getMessage());
        }


        // Now evaluate, they should all be changed
        HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
        eval.evaluateFormulaCell(c1);
        eval.evaluateFormulaCell(c2);
        eval.evaluateFormulaCell(c3);

        // Check that the cells now contain
        //  the correct values
        confirmCachedValue(70164.0, c1);
        confirmCachedValue("70164", c2);
        confirmCachedValue("90210", c3);


        // Write and read
        HSSFWorkbook nwb = writeOutAndReadBack(wb);
        HSSFSheet ns = nwb.getSheetAt(0);
        HSSFCell nc1 = ns.getRow(0).getCell(0);
        HSSFCell nc2 = ns.getRow(0).getCell(1);
        HSSFCell nc3 = ns.getRow(0).getCell(2);

        // Re-check
        confirmCachedValue(70164.0, nc1);
        confirmCachedValue("70164", nc2);
        confirmCachedValue("90210", nc3);

        CellValueRecordInterface[] cvrs = ns.getSheet().getValueRecords();
        for (int i = 0; i < cvrs.length; i++) {
            CellValueRecordInterface cvr = cvrs[i];
            if(cvr instanceof FormulaRecordAggregate) {
                FormulaRecordAggregate fr = (FormulaRecordAggregate)cvr;

                if(i == 0) {
                    assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001);
                    assertNull(fr.getStringRecord());
                } else if (i == 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());
                }
            }
        }
        assertEquals(3, cvrs.length);
    }

    private static void confirmCachedValue(double expectedValue, HSSFCell cell) {
        assertEquals(HSSFCell.CELL_TYPE_FORMULA, cell.getCellType());
        assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType());
        assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
    }
    private static void confirmCachedValue(String expectedValue, HSSFCell cell) {
        assertEquals(HSSFCell.CELL_TYPE_FORMULA, cell.getCellType());
        assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCachedFormulaResultType());
        assertEquals(expectedValue, cell.getRichStringCellValue().getString());
    }

    /**
     * Problem with "Vector Rows", eg a whole
     *  column which is set to the result of
     *  {=sin(B1:B9)}(9,1), so that each cell is
     *  shown to have the contents
     *  {=sin(B1:B9){9,1)[rownum][0]
     * In this sample file, the vector column
     *  is C, and the data column is B.
     *
     * For now, blows up with an exception from ExtPtg
     *  Expected ExpPtg to be converted from Shared to Non-Shared...
     */
    public void DISABLEDtest43623() {
        HSSFWorkbook wb = openSample("43623.xls");
        assertEquals(1, wb.getNumberOfSheets());

        HSSFSheet s1 = wb.getSheetAt(0);

        HSSFCell c1 = s1.getRow(0).getCell(2);
        HSSFCell c2 = s1.getRow(1).getCell(2);
        HSSFCell c3 = s1.getRow(2).getCell(2);

        // These formula contents are a guess...
        assertEquals("{=sin(B1:B9){9,1)[0][0]", c1.getCellFormula());
        assertEquals("{=sin(B1:B9){9,1)[1][0]", c2.getCellFormula());
        assertEquals("{=sin(B1:B9){9,1)[2][0]", c3.getCellFormula());

        // Save and re-open, ensure it still works
        HSSFWorkbook nwb = writeOutAndReadBack(wb);
        HSSFSheet ns1 = nwb.getSheetAt(0);
        HSSFCell nc1 = ns1.getRow(0).getCell(2);
        HSSFCell nc2 = ns1.getRow(1).getCell(2);
        HSSFCell nc3 = ns1.getRow(2).getCell(2);

        assertEquals("{=sin(B1:B9){9,1)[0][0]", nc1.getCellFormula());
        assertEquals("{=sin(B1:B9){9,1)[1][0]", nc2.getCellFormula());
        assertEquals("{=sin(B1:B9){9,1)[2][0]", nc3.getCellFormula());
    }

    /**
     * People are all getting confused about the last
     *  row and cell number
     */
    public void test30635() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet s = wb.createSheet();

        // No rows, everything is 0
        assertEquals(0, s.getFirstRowNum());
        assertEquals(0, s.getLastRowNum());
        assertEquals(0, s.getPhysicalNumberOfRows());

        // One row, most things are 0, physical is 1
        s.createRow(0);
        assertEquals(0, s.getFirstRowNum());
        assertEquals(0, s.getLastRowNum());
        assertEquals(1, s.getPhysicalNumberOfRows());

        // And another, things change
        s.createRow(4);
        assertEquals(0, s.getFirstRowNum());
        assertEquals(4, s.getLastRowNum());
        assertEquals(2, s.getPhysicalNumberOfRows());


        // Now start on cells
        HSSFRow r = s.getRow(0);
        assertEquals(-1, r.getFirstCellNum());
        assertEquals(-1, r.getLastCellNum());
        assertEquals(0, r.getPhysicalNumberOfCells());

        // Add a cell, things move off -1
        r.createCell(0);
        assertEquals(0, r.getFirstCellNum());
        assertEquals(1, r.getLastCellNum()); // last cell # + 1
        assertEquals(1, r.getPhysicalNumberOfCells());

        r.createCell(1);
        assertEquals(0, r.getFirstCellNum());
        assertEquals(2, r.getLastCellNum()); // last cell # + 1
        assertEquals(2, r.getPhysicalNumberOfCells());

        r.createCell(4);
        assertEquals(0, r.getFirstCellNum());
        assertEquals(5, r.getLastCellNum()); // last cell # + 1
        assertEquals(3, r.getPhysicalNumberOfCells());
    }

    /**
     * Data Tables - ptg 0x2
     */
    public void test44958() {
        HSSFWorkbook wb = openSample("44958.xls");
        HSSFSheet s;
        HSSFRow r;
        HSSFCell c;

        // Check the contents of the formulas

        // E4 to G9 of sheet 4 make up the table
        s = wb.getSheet("OneVariable Table Completed");
        r = s.getRow(3);
        c = r.getCell(4);
        assertEquals(HSSFCell.CELL_TYPE_FORMULA, c.getCellType());

        // TODO - check the formula once tables and
        //  arrays are properly supported


        // E4 to H9 of sheet 5 make up the table
        s = wb.getSheet("TwoVariable Table Example");
        r = s.getRow(3);
        c = r.getCell(4);
        assertEquals(HSSFCell.CELL_TYPE_FORMULA, c.getCellType());

        // TODO - check the formula once tables and
        //  arrays are properly supported
    }

    /**
     * 45322: HSSFSheet.autoSizeColumn fails when style.getDataFormat() returns -1
     */
    public void test45322() {
        HSSFWorkbook wb = openSample("44958.xls");
        HSSFSheet sh = wb.getSheetAt(0);
        for(short i=0; i < 30; i++) sh.autoSizeColumn(i);
     }

    /**
     * We used to add too many UncalcRecords to sheets
     *  with diagrams on. Don't any more
     */
    public void test45414() {
        HSSFWorkbook wb = openSample("WithThreeCharts.xls");
        wb.getSheetAt(0).setForceFormulaRecalculation(true);
        wb.getSheetAt(1).setForceFormulaRecalculation(false);
        wb.getSheetAt(2).setForceFormulaRecalculation(true);

        // Write out and back in again
        // This used to break
        HSSFWorkbook nwb = writeOutAndReadBack(wb);

        // Check now set as it should be
        assertTrue(nwb.getSheetAt(0).getForceFormulaRecalculation());
        assertFalse(nwb.getSheetAt(1).getForceFormulaRecalculation());
        assertTrue(nwb.getSheetAt(2).getForceFormulaRecalculation());
    }

    /**
     * Very hidden sheets not displaying as such
     */
    public void test45761() {
        HSSFWorkbook wb = openSample("45761.xls");
        assertEquals(3, wb.getNumberOfSheets());

        assertFalse(wb.isSheetHidden(0));
        assertFalse(wb.isSheetVeryHidden(0));
        assertTrue(wb.isSheetHidden(1));
        assertFalse(wb.isSheetVeryHidden(1));
        assertFalse(wb.isSheetHidden(2));
        assertTrue(wb.isSheetVeryHidden(2));

        // Change 0 to be very hidden, and re-load
        wb.setSheetHidden(0, 2);

        HSSFWorkbook nwb = writeOutAndReadBack(wb);

        assertFalse(nwb.isSheetHidden(0));
        assertTrue(nwb.isSheetVeryHidden(0));
        assertTrue(nwb.isSheetHidden(1));
        assertFalse(nwb.isSheetVeryHidden(1));
        assertFalse(nwb.isSheetHidden(2));
        assertTrue(nwb.isSheetVeryHidden(2));
    }
    
    /**
     * header / footer text too long
     */
    public void test45777() {
    	HSSFWorkbook wb = new HSSFWorkbook();
    	HSSFSheet s = wb.createSheet();
    	
    	String s248 = "";
    	for(int i=0; i<248; i++) {
    		s248 += "x";
    	}
    	String s249 = s248 + "1";
    	String s250 = s248 + "12";
    	String s251 = s248 + "123";
    	assertEquals(248, s248.length());
    	assertEquals(249, s249.length());
    	assertEquals(250, s250.length());
    	assertEquals(251, s251.length());
    	
    	
    	// Try on headers
    	s.getHeader().setCenter(s248);
    	assertEquals(254, s.getHeader().getRawHeader().length());
    	writeOutAndReadBack(wb);
    	
    	s.getHeader().setCenter(s249);
    	assertEquals(255, s.getHeader().getRawHeader().length());
    	writeOutAndReadBack(wb);
    	
    	try {
    		s.getHeader().setCenter(s250); // 256
    		fail();
    	} catch(IllegalArgumentException e) {}
    	
    	try {
    		s.getHeader().setCenter(s251); // 257
    		fail();
    	} catch(IllegalArgumentException e) {}
    	
    	
    	// Now try on footers
    	s.getFooter().setCenter(s248);
    	assertEquals(254, s.getFooter().getRawFooter().length());
    	writeOutAndReadBack(wb);
    	
    	s.getFooter().setCenter(s249);
    	assertEquals(255, s.getFooter().getRawFooter().length());
    	writeOutAndReadBack(wb);
    	
    	try {
    		s.getFooter().setCenter(s250); // 256
    		fail();
    	} catch(IllegalArgumentException e) {}
    	
    	try {
    		s.getFooter().setCenter(s251); // 257
    		fail();
    	} catch(IllegalArgumentException e) {}
    }
    
    /**
     * Charts with long titles
     */
    public void test45784() {
    	// This used to break
        HSSFWorkbook wb = openSample("45784.xls");
        assertEquals(1, wb.getNumberOfSheets());
    }
    
   /**
     * Cell background colours
     */
    public void test45492() {
    	HSSFWorkbook wb = openSample("45492.xls");
    	HSSFSheet s = wb.getSheetAt(0);
    	HSSFRow r = s.getRow(0);
    	HSSFPalette p = wb.getCustomPalette();
    	
    	HSSFCell auto = r.getCell(0);
    	HSSFCell grey = r.getCell(1);
    	HSSFCell red = r.getCell(2);
    	HSSFCell blue = r.getCell(3);
    	HSSFCell green = r.getCell(4);
    	
    	assertEquals(64, auto.getCellStyle().getFillForegroundColor());
    	assertEquals(64, auto.getCellStyle().getFillBackgroundColor());
    	assertEquals("0:0:0", p.getColor(64).getHexString());
    	
    	assertEquals(22, grey.getCellStyle().getFillForegroundColor());
    	assertEquals(64, grey.getCellStyle().getFillBackgroundColor());
    	assertEquals("C0C0:C0C0:C0C0", p.getColor(22).getHexString());
    	
    	assertEquals(10, red.getCellStyle().getFillForegroundColor());
    	assertEquals(64, red.getCellStyle().getFillBackgroundColor());
    	assertEquals("FFFF:0:0", p.getColor(10).getHexString());
    	
    	assertEquals(12, blue.getCellStyle().getFillForegroundColor());
    	assertEquals(64, blue.getCellStyle().getFillBackgroundColor());
    	assertEquals("0:0:FFFF", p.getColor(12).getHexString());
    	
    	assertEquals(11, green.getCellStyle().getFillForegroundColor());
    	assertEquals(64, green.getCellStyle().getFillBackgroundColor());
    	assertEquals("0:FFFF:0", p.getColor(11).getHexString());
    }
}