testhssfsheet.java

介绍java核心技术的pio编程方法以及基本概念

        sheetPM.getPrintSetup().setCopies((short)3);
        
        // Check taken
        assertTrue(sheetL.getPrintSetup().getLandscape());
        assertFalse(sheetPM.getPrintSetup().getLandscape());
        assertTrue(sheetLS.getPrintSetup().getLandscape());
        assertEquals(1, sheetL.getPrintSetup().getCopies());
        assertEquals(3, sheetPM.getPrintSetup().getCopies());
        assertEquals(1, sheetLS.getPrintSetup().getCopies());
        
        // Save and re-load, and check still there
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray()));
        
        assertTrue(sheetL.getPrintSetup().getLandscape());
        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() throws Exception {
        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
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook = new HSSFWorkbook(
        		new ByteArrayInputStream(baos.toByteArray())
        );
        
        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() throws Exception {
        String filename = System.getProperty("HSSF.testdata.path");
        filename = filename + "/NoGutsRecords.xls";
        HSSFWorkbook workbook = 
        	new HSSFWorkbook(new FileInputStream(filename));
        
        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
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook = new HSSFWorkbook(
        		new ByteArrayInputStream(baos.toByteArray())
        );
        
        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() throws Exception {
        String filename = System.getProperty("HSSF.testdata.path");
    	HSSFWorkbook wb1c = new HSSFWorkbook(
    			new FileInputStream(new File(filename,"WithChart.xls"))
    	);
    	HSSFWorkbook wb2c = new HSSFWorkbook(
    			new FileInputStream(new File(filename,"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.setProtect(true);
		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()
            throws Exception
    {
        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();
		Region region = new Region(0, (short)0, 1, (short)1);   	
		sheet.addMergedRegion(region);
		region = new Region(1, (short)0, 2, (short)1);
		sheet.addMergedRegion(region);
		
    	sheet.removeMergedRegion(0);
    	
    	region = sheet.getMergedRegionAt(0);
    	assertEquals("Left over region should be starting at row 1", 1, region.getRowFrom());
    	
    	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.setRowTo(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.getMergedRegionAt(0);
		assertEquals("the merged row to doesnt match the one we put in ", 4, region.getRowTo());
    	
    }

	public void testShiftMerged() {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell((short)0);
		cell.setCellValue("first row, first cell");
		
		row = sheet.createRow(1);
		cell = row.createCell((short)1);
		cell.setCellValue("second row, second cell");
		
		Region region = new Region(1, (short)0, 1, (short)1);   	
		sheet.addMergedRegion(region);
		
		sheet.shiftRows(1, 1, 1);
		
		region = sheet.getMergedRegionAt(0);
		assertEquals("Merged region not moved over to row 2", 2, region.getRowFrom());
		
	}

    /**
     * Tests the display of gridlines, formulas, and rowcolheadings.
     * @author Shawn Laubach (slaubach at apache dot org)
     */
    public void testDisplayOptions() throws Exception {
    	HSSFWorkbook wb = new HSSFWorkbook();
    	HSSFSheet sheet = wb.createSheet();
	
        File tempFile = TempFile.createTempFile("display", "test.xls");
        FileOutputStream stream = new FileOutputStream(tempFile);
        wb.write(stream);
        stream.close();

        FileInputStream readStream = new FileInputStream(tempFile);
        wb = new HSSFWorkbook(readStream);
        sheet = wb.getSheetAt(0);
    	readStream.close();
    
    	assertEquals(sheet.isDisplayGridlines(), true);
    	assertEquals(sheet.isDisplayRowColHeadings(), true);
    	assertEquals(sheet.isDisplayFormulas(), false);
    
    	sheet.setDisplayGridlines(false);
    	sheet.setDisplayRowColHeadings(false);
    	sheet.setDisplayFormulas(true);

        tempFile = TempFile.createTempFile("display", "test.xls");
        stream = new FileOutputStream(tempFile);
        wb.write(stream);
        stream.close();

        readStream = new FileInputStream(tempFile);
        wb = new HSSFWorkbook(readStream);