testhssfsheet.java

来自「EXCEL read and write」· Java 代码 · 共 914 行 · 第 1/3 页

        short leftcol = (short) 50;
        sheet.showInPane(toprow,leftcol);
        assertEquals("HSSFSheet.getTopRow()", toprow, sheet.getTopRow());
        assertEquals("HSSFSheet.getLeftCol()", leftcol, sheet.getLeftCol());
    }

    /** cell with formula becomes null on cloning a sheet*/
     public void test35084() {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet s = wb.createSheet("Sheet1");
        HSSFRow r = s.createRow(0);
        r.createCell(0).setCellValue(1);
        r.createCell(1).setCellFormula("A1*2");
        HSSFSheet s1 = wb.cloneSheet(0);
        r = s1.getRow(0);
        assertEquals("double", r.getCell(0).getNumericCellValue(), 1, 0); // sanity check
        assertNotNull(r.getCell(1));
        assertEquals("formula", r.getCell(1).getCellFormula(), "A1*2");
    }

    /** test that new default column styles get applied */
    public void testDefaultColumnStyle() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle style = wb.createCellStyle();
        HSSFSheet s = wb.createSheet();
        s.setDefaultColumnStyle((short) 0, style);
        HSSFRow r = s.createRow(0);
        HSSFCell c = r.createCell(0);
        assertEquals("style should match", style.getIndex(), c.getCellStyle().getIndex());
    }


    /**
     *
     */
    public void testAddEmptyRow() {
        //try to add 5 empty rows to a new sheet
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        for (int i = 0; i < 5; i++) {
            sheet.createRow(i);
        }

        workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);

        //try adding empty rows in an existing worksheet
        workbook = openSample("Simple.xls");

        sheet = workbook.getSheetAt(0);
        for (int i = 3; i < 10; i++) sheet.createRow(i);

        workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
    }

    public void testAutoSizeColumn() {
        HSSFWorkbook wb = openSample("43902.xls");
        String sheetName = "my sheet";
        HSSFSheet sheet = wb.getSheet(sheetName);

        // Can't use literal numbers for column sizes, as
        //  will come out with different values on different
        //  machines based on the fonts available.
        // So, we use ranges, which are pretty large, but
        //  thankfully don't overlap!
        int minWithRow1And2 = 6400;
        int maxWithRow1And2 = 7800;
        int minWithRow1Only = 2750;
        int maxWithRow1Only = 3300;

        // autoSize the first column and check its size before the merged region (1,0,1,1) is set:
        // it has to be based on the 2nd row width
        sheet.autoSizeColumn((short)0);
        assertTrue("Column autosized with only one row: wrong width", sheet.getColumnWidth(0) >= minWithRow1And2);
        assertTrue("Column autosized with only one row: wrong width", sheet.getColumnWidth(0) <= maxWithRow1And2);

        //create a region over the 2nd row and auto size the first column
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
        sheet.autoSizeColumn((short)0);
        HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb);

        // check that the autoSized column width has ignored the 2nd row
        // because it is included in a merged region (Excel like behavior)
        HSSFSheet sheet2 = wb2.getSheet(sheetName);
        assertTrue(sheet2.getColumnWidth(0) >= minWithRow1Only);
        assertTrue(sheet2.getColumnWidth(0) <= maxWithRow1Only);

        // remove the 2nd row merged region and check that the 2nd row value is used to the autoSizeColumn width
        sheet2.removeMergedRegion(1);
        sheet2.autoSizeColumn((short)0);
        HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
        HSSFSheet sheet3 = wb3.getSheet(sheetName);
        assertTrue(sheet3.getColumnWidth(0) >= minWithRow1And2);
        assertTrue(sheet3.getColumnWidth(0) <= maxWithRow1And2);
    }

    /**
     * Setting ForceFormulaRecalculation on sheets
     */
    public void testForceRecalculation() throws Exception {
        HSSFWorkbook workbook = openSample("UncalcedRecord.xls");

        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFSheet sheet2 = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        row.createCell(0).setCellValue(5);
        row.createCell(1).setCellValue(8);
        assertFalse(sheet.getForceFormulaRecalculation());
        assertFalse(sheet2.getForceFormulaRecalculation());

        // Save and manually verify that on column C we have 0, value in template
        File tempFile = new File(System.getProperty("java.io.tmpdir")+"/uncalced_err.xls" );
        tempFile.delete();
        FileOutputStream fout = new FileOutputStream( tempFile );
        workbook.write( fout );
        fout.close();
        sheet.setForceFormulaRecalculation(true);
        assertTrue(sheet.getForceFormulaRecalculation());

        // Save and manually verify that on column C we have now 13, calculated value
        tempFile = new File(System.getProperty("java.io.tmpdir")+"/uncalced_succ.xls" );
        tempFile.delete();
        fout = new FileOutputStream( tempFile );
        workbook.write( fout );
        fout.close();

        // Try it can be opened
        HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(tempFile));

        // And check correct sheet settings found
        sheet = wb2.getSheetAt(0);
        sheet2 = wb2.getSheetAt(1);
        assertTrue(sheet.getForceFormulaRecalculation());
        assertFalse(sheet2.getForceFormulaRecalculation());

        // Now turn if back off again
        sheet.setForceFormulaRecalculation(false);

        fout = new FileOutputStream( tempFile );
        wb2.write( fout );
        fout.close();
        wb2 = new HSSFWorkbook(new FileInputStream(tempFile));

        assertFalse(wb2.getSheetAt(0).getForceFormulaRecalculation());
        assertFalse(wb2.getSheetAt(1).getForceFormulaRecalculation());
        assertFalse(wb2.getSheetAt(2).getForceFormulaRecalculation());

        // Now add a new sheet, and check things work
        //  with old ones unset, new one set
        HSSFSheet s4 = wb2.createSheet();
        s4.setForceFormulaRecalculation(true);

        assertFalse(sheet.getForceFormulaRecalculation());
        assertFalse(sheet2.getForceFormulaRecalculation());
        assertTrue(s4.getForceFormulaRecalculation());

        fout = new FileOutputStream( tempFile );
        wb2.write( fout );
        fout.close();

        HSSFWorkbook wb3 = new HSSFWorkbook(new FileInputStream(tempFile));
        assertFalse(wb3.getSheetAt(0).getForceFormulaRecalculation());
        assertFalse(wb3.getSheetAt(1).getForceFormulaRecalculation());
        assertFalse(wb3.getSheetAt(2).getForceFormulaRecalculation());
        assertTrue(wb3.getSheetAt(3).getForceFormulaRecalculation());
    }

    public void testColumnWidth() {
        //check we can correctly read column widths from a reference workbook
        HSSFWorkbook wb = openSample("colwidth.xls");

        //reference values
        int[] ref = {365, 548, 731, 914, 1097, 1280, 1462, 1645, 1828, 2011, 2194, 2377, 2560, 2742, 2925, 3108, 3291, 3474, 3657};

        HSSFSheet sh = wb.getSheetAt(0);
        for (char i = 'A'; i <= 'S'; i++) {
            int idx = i - 'A';
            int w = sh.getColumnWidth(idx);
            assertEquals(ref[idx], w);
        }

        //the second sheet doesn't have overridden column widths
        sh = wb.getSheetAt(1);
        int def_width = sh.getDefaultColumnWidth();
        for (char i = 'A'; i <= 'S'; i++) {
            int idx = i - 'A';
            int w = sh.getColumnWidth(idx);
            //getDefaultColumnWidth returns width measured in characters
            //getColumnWidth returns width measured in 1/256th units
            assertEquals(def_width*256, w);
        }

        //test new workbook
        wb = new HSSFWorkbook();
        sh = wb.createSheet();
        sh.setDefaultColumnWidth(10);
        assertEquals(10, sh.getDefaultColumnWidth());
        assertEquals(256*10, sh.getColumnWidth(0));
        assertEquals(256*10, sh.getColumnWidth(1));
        assertEquals(256*10, sh.getColumnWidth(2));
        for (char i = 'D'; i <= 'F'; i++) {
            short w = (256*12);
            sh.setColumnWidth(i, w);
            assertEquals(w, sh.getColumnWidth(i));
        }

        //serialize and read again
        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);

        sh = wb.getSheetAt(0);
        assertEquals(10, sh.getDefaultColumnWidth());
        //columns A-C have default width
        assertEquals(256*10, sh.getColumnWidth(0));
        assertEquals(256*10, sh.getColumnWidth(1));
        assertEquals(256*10, sh.getColumnWidth(2));
        //columns D-F have custom width
        for (char i = 'D'; i <= 'F'; i++) {
            short w = (256*12);
            assertEquals(w, sh.getColumnWidth(i));
        }

        // check for 16-bit signed/unsigned error:
        sh.setColumnWidth(0, 40000);
        assertEquals(40000, sh.getColumnWidth(0));
    }

    /**
     * Some utilities write Excel files without the ROW records.
     * Excel, ooo, and google docs are OK with this.
     * Now POI is too.
     */
    public void testMissingRowRecords_bug41187() {
        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ex41187-19267.xls");

        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        if(row == null) {
            throw new AssertionFailedError("Identified bug 41187 a");
        }
        if (row.getHeight() == 0) {
            throw new AssertionFailedError("Identified bug 41187 b");
        }
        assertEquals("Hi Excel!", row.getCell(0).getRichStringCellValue().getString());
        // check row height for 'default' flag
        assertEquals((short)0xFF, row.getHeight());

        HSSFTestDataSamples.writeOutAndReadBack(wb);
    }

    /**
     * If we clone a sheet containing drawings,
     * we must allocate a new ID of the drawing group and re-create shape IDs
     *
     * See bug #45720.
     */
    public void testCloneSheetWithDrawings() {
        HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("45720.xls");

        HSSFSheet sheet1 = wb1.getSheetAt(0);

        wb1.getWorkbook().findDrawingGroup();
        DrawingManager2 dm1 = wb1.getWorkbook().getDrawingManager();

        wb1.cloneSheet(0);

        HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
        wb2.getWorkbook().findDrawingGroup();
        DrawingManager2 dm2 = wb2.getWorkbook().getDrawingManager();

        //check EscherDggRecord - a workbook-level registry of drawing objects
        assertEquals(dm1.getDgg().getMaxDrawingGroupId() + 1, dm2.getDgg().getMaxDrawingGroupId());

        HSSFSheet sheet2 = wb2.getSheetAt(1);

        //check that id of the drawing group was updated
        EscherDgRecord dg1 = (EscherDgRecord)sheet1.getDrawingEscherAggregate().findFirstWithId(EscherDgRecord.RECORD_ID);
        EscherDgRecord dg2 = (EscherDgRecord)sheet2.getDrawingEscherAggregate().findFirstWithId(EscherDgRecord.RECORD_ID);
        int dg_id_1 = dg1.getOptions() >> 4;
        int dg_id_2 = dg2.getOptions() >> 4;
        assertEquals(dg_id_1 + 1, dg_id_2);

        //TODO: check shapeId in the cloned sheet
    }
    
    /**
     * POI now (Sep 2008) allows sheet names longer than 31 chars (for other apps besides Excel).
     * Since Excel silently truncates to 31, make sure that POI enforces uniqueness on the first
     * 31 chars. 
     */
    public void testLongSheetNames() {
        HSSFWorkbook wb = new HSSFWorkbook();
        final String SAME_PREFIX = "A123456789B123456789C123456789"; // 30 chars
        
        wb.createSheet(SAME_PREFIX + "Dxxxx");
        try {
            wb.createSheet(SAME_PREFIX + "Dyyyy"); // identical up to the 32nd char
            throw new AssertionFailedError("Expected exception not thrown");
        } catch (IllegalArgumentException e) {
            assertEquals("The workbook already contains a sheet of this name", e.getMessage());
        }
        wb.createSheet(SAME_PREFIX + "Exxxx"); // OK - differs in the 31st char
    }
}