testhssfsheet.java

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

        assertFalse(sheetPM.getPrintSetup().getLandscape());
        assertTrue(sheetLS.getPrintSetup().getLandscape());
        assertEquals(1, sheetL.getPrintSetup().getCopies());
        assertEquals(3, sheetPM.getPrintSetup().getCopies());
        assertEquals(1, sheetLS.getPrintSetup().getCopies());
    }

    public void testGroupRows() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet s = workbook.createSheet();
        HSSFRow r1 = s.createRow(0);
        HSSFRow r2 = s.createRow(1);
        HSSFRow r3 = s.createRow(2);
        HSSFRow r4 = s.createRow(3);
        HSSFRow r5 = s.createRow(4);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(0, r3.getOutlineLevel());
        assertEquals(0, r4.getOutlineLevel());
        assertEquals(0, r5.getOutlineLevel());

        s.groupRow(2,3);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(1, r3.getOutlineLevel());
        assertEquals(1, r4.getOutlineLevel());
        assertEquals(0, r5.getOutlineLevel());

        // Save and re-open
        workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);

        s = workbook.getSheetAt(0);
        r1 = s.getRow(0);
        r2 = s.getRow(1);
        r3 = s.getRow(2);
        r4 = s.getRow(3);
        r5 = s.getRow(4);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(1, r3.getOutlineLevel());
        assertEquals(1, r4.getOutlineLevel());
        assertEquals(0, r5.getOutlineLevel());
    }

    public void testGroupRowsExisting() {
        HSSFWorkbook workbook = openSample("NoGutsRecords.xls");

        HSSFSheet s = workbook.getSheetAt(0);
        HSSFRow r1 = s.getRow(0);
        HSSFRow r2 = s.getRow(1);
        HSSFRow r3 = s.getRow(2);
        HSSFRow r4 = s.getRow(3);
        HSSFRow r5 = s.getRow(4);
        HSSFRow r6 = s.getRow(5);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(0, r3.getOutlineLevel());
        assertEquals(0, r4.getOutlineLevel());
        assertEquals(0, r5.getOutlineLevel());
        assertEquals(0, r6.getOutlineLevel());

        // This used to complain about lacking guts records
        s.groupRow(2, 4);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(1, r3.getOutlineLevel());
        assertEquals(1, r4.getOutlineLevel());
        assertEquals(1, r5.getOutlineLevel());
        assertEquals(0, r6.getOutlineLevel());

        // Save and re-open
        try {
            workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
        } catch (OutOfMemoryError e) {
            throw new AssertionFailedError("Identified bug 39903");
        }

        s = workbook.getSheetAt(0);
        r1 = s.getRow(0);
        r2 = s.getRow(1);
        r3 = s.getRow(2);
        r4 = s.getRow(3);
        r5 = s.getRow(4);
        r6 = s.getRow(5);

        assertEquals(0, r1.getOutlineLevel());
        assertEquals(0, r2.getOutlineLevel());
        assertEquals(1, r3.getOutlineLevel());
        assertEquals(1, r4.getOutlineLevel());
        assertEquals(1, r5.getOutlineLevel());
        assertEquals(0, r6.getOutlineLevel());
    }

    public void testGetDrawings() {
        HSSFWorkbook wb1c = openSample("WithChart.xls");
        HSSFWorkbook wb2c = openSample("WithTwoCharts.xls");

        // 1 chart sheet -> data on 1st, chart on 2nd
        assertNotNull(wb1c.getSheetAt(0).getDrawingPatriarch());
        assertNotNull(wb1c.getSheetAt(1).getDrawingPatriarch());
        assertFalse(wb1c.getSheetAt(0).getDrawingPatriarch().containsChart());
        assertTrue(wb1c.getSheetAt(1).getDrawingPatriarch().containsChart());

        // 2 chart sheet -> data on 1st, chart on 2nd+3rd
        assertNotNull(wb2c.getSheetAt(0).getDrawingPatriarch());
        assertNotNull(wb2c.getSheetAt(1).getDrawingPatriarch());
        assertNotNull(wb2c.getSheetAt(2).getDrawingPatriarch());
        assertFalse(wb2c.getSheetAt(0).getDrawingPatriarch().containsChart());
        assertTrue(wb2c.getSheetAt(1).getDrawingPatriarch().containsChart());
        assertTrue(wb2c.getSheetAt(2).getDrawingPatriarch().containsChart());
    }

    /**
     * Test that the ProtectRecord is included when creating or cloning a sheet
     */
    public void testProtect() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = workbook.createSheet();
        Sheet sheet = hssfSheet.getSheet();
        ProtectRecord protect = sheet.getProtect();

        assertFalse(protect.getProtect());

        // This will tell us that cloneSheet, and by extension,
        // the list forms of createSheet leave us with an accessible
        // ProtectRecord.
        hssfSheet.protectSheet("secret");
        Sheet cloned = sheet.cloneSheet();
        assertNotNull(cloned.getProtect());
        assertTrue(hssfSheet.getProtect());
    }

    public void testProtectSheet() {
        short expected = (short)0xfef1;
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet s = wb.createSheet();
        s.protectSheet("abcdefghij");
        Sheet sheet = s.getSheet();
        ProtectRecord protect = sheet.getProtect();
        PasswordRecord pass = sheet.getPassword();
        assertTrue("protection should be on",protect.getProtect());
        assertTrue("object protection should be on",sheet.isProtected()[1]);
        assertTrue("scenario protection should be on",sheet.isProtected()[2]);
        assertEquals("well known value for top secret hash should be "+Integer.toHexString(expected).substring(4),expected,pass.getPassword());
    }


    public void testZoom() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        assertEquals(-1, sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid));
        sheet.setZoom(3,4);
        assertTrue(sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid) > 0);
        SCLRecord sclRecord = (SCLRecord) sheet.getSheet().findFirstRecordBySid(SCLRecord.sid);
        assertEquals(3, sclRecord.getNumerator());
        assertEquals(4, sclRecord.getDenominator());

        int sclLoc = sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid);
        int window2Loc = sheet.getSheet().findFirstRecordLocBySid(WindowTwoRecord.sid);
        assertTrue(sclLoc == window2Loc + 1);
    }


    /**
     * When removing one merged region, it would break
     *
     */
    public void testRemoveMerged() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
        sheet.addMergedRegion(region);
        region = new CellRangeAddress(1, 2, 0, 1);
        sheet.addMergedRegion(region);

        sheet.removeMergedRegion(0);

        region = sheet.getMergedRegion(0);
        assertEquals("Left over region should be starting at row 1", 1, region.getFirstRow());

        sheet.removeMergedRegion(0);

        assertEquals("there should be no merged regions left!", 0, sheet.getNumMergedRegions());

        //an, add, remove, get(0) would null pointer
        sheet.addMergedRegion(region);
        assertEquals("there should now be one merged region!", 1, sheet.getNumMergedRegions());
        sheet.removeMergedRegion(0);
        assertEquals("there should now be zero merged regions!", 0, sheet.getNumMergedRegions());
        //add it again!
        region.setLastRow(4);

        sheet.addMergedRegion(region);
        assertEquals("there should now be one merged region!", 1, sheet.getNumMergedRegions());

        //should exist now!
        assertTrue("there isn't more than one merged region in there", 1 <= sheet.getNumMergedRegions());
        region = sheet.getMergedRegion(0);
        assertEquals("the merged row to doesnt match the one we put in ", 4, region.getLastRow());
    }

    public void testShiftMerged() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString("first row, first cell"));

        row = sheet.createRow(1);
        cell = row.createCell(1);
        cell.setCellValue(new HSSFRichTextString("second row, second cell"));

        CellRangeAddress region = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(region);

        sheet.shiftRows(1, 1, 1);

        region = sheet.getMergedRegion(0);
        assertEquals("Merged region not moved over to row 2", 2, region.getFirstRow());
    }

    /**
     * Tests the display of gridlines, formulas, and rowcolheadings.
     * @author Shawn Laubach (slaubach at apache dot org)
     */
    public void testDisplayOptions() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();

        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        sheet = wb.getSheetAt(0);

        assertEquals(sheet.isDisplayGridlines(), true);
        assertEquals(sheet.isDisplayRowColHeadings(), true);
        assertEquals(sheet.isDisplayFormulas(), false);

        sheet.setDisplayGridlines(false);
        sheet.setDisplayRowColHeadings(false);
        sheet.setDisplayFormulas(true);

        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        sheet = wb.getSheetAt(0);

        assertEquals(sheet.isDisplayGridlines(), false);
        assertEquals(sheet.isDisplayRowColHeadings(), false);
        assertEquals(sheet.isDisplayFormulas(), true);
    }


    /**
     * Make sure the excel file loads work
     *
     */
    public void testPageBreakFiles() {
        HSSFWorkbook wb = openSample("SimpleWithPageBreaks.xls");

        HSSFSheet sheet = wb.getSheetAt(0);
        assertNotNull(sheet);

        assertEquals("1 row page break", 1, sheet.getRowBreaks().length);
        assertEquals("1 column page break", 1, sheet.getColumnBreaks().length);

        assertTrue("No row page break", sheet.isRowBroken(22));
        assertTrue("No column page break", sheet.isColumnBroken((short)4));

        sheet.setRowBreak(10);
        sheet.setColumnBreak((short)13);

        assertEquals("row breaks number", 2, sheet.getRowBreaks().length);
        assertEquals("column breaks number", 2, sheet.getColumnBreaks().length);

        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        sheet = wb.getSheetAt(0);

        assertTrue("No row page break", sheet.isRowBroken(22));
        assertTrue("No column page break", sheet.isColumnBroken((short)4));

        assertEquals("row breaks number", 2, sheet.getRowBreaks().length);
        assertEquals("column breaks number", 2, sheet.getColumnBreaks().length);
    }

    public void testDBCSName () {
        HSSFWorkbook wb = openSample("DBCSSheetName.xls");
        wb.getSheetAt(1);
        assertEquals ("DBCS Sheet Name 2", wb.getSheetName(1),"\u090f\u0915" );
        assertEquals("DBCS Sheet Name 1", wb.getSheetName(0),"\u091c\u093e");
    }

    /**
     * Testing newly added method that exposes the WINDOW2.toprow
     * parameter to allow setting the toprow in the visible view
     * of the sheet when it is first opened.
     */
    public void testTopRow() {
        HSSFWorkbook wb = openSample("SimpleWithPageBreaks.xls");

        HSSFSheet sheet = wb.getSheetAt(0);
        assertNotNull(sheet);

        short toprow = (short) 100;