testhssfsheet.java

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

        sheet = wb.getSheetAt(0);
    	readStream.close();
    
    
    	assertEquals(sheet.isDisplayGridlines(), false);
    	assertEquals(sheet.isDisplayRowColHeadings(), false);
    	assertEquals(sheet.isDisplayFormulas(), true);
    }

    
    /**
     * Make sure the excel file loads work
     *
     */
    public void testPageBreakFiles() throws Exception{
        FileInputStream fis = null;
        HSSFWorkbook wb     = null;
        
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithPageBreaks.xls";
        fis = new FileInputStream(filename);
        wb = new HSSFWorkbook(fis);
        fis.close();
        
        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);
        
        File tempFile = TempFile.createTempFile("display", "testPagebreaks.xls");
        FileOutputStream stream = new FileOutputStream(tempFile);
        wb.write(stream);
        stream.close();
        
        wb = new HSSFWorkbook(new FileInputStream(tempFile));
        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 () throws Exception {
    	FileInputStream fis = null;
        HSSFWorkbook wb     = null;
        
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/DBCSSheetName.xls";
        fis = new FileInputStream(filename);
        wb = new HSSFWorkbook(fis);
        HSSFSheet s= 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() throws Exception
    {
        FileInputStream fis = null;
        HSSFWorkbook wb     = null;
        
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithPageBreaks.xls";
        fis = new FileInputStream(filename);
        wb = new HSSFWorkbook(fis);
        fis.close();
        
        HSSFSheet sheet = wb.getSheetAt(0);
        assertNotNull(sheet);
        
        short toprow = (short) 100;
        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((short)0).setCellValue(1);
   	r.createCell((short)1).setCellFormula("A1*2");
   	HSSFSheet s1 = wb.cloneSheet(0);
   	r=s1.getRow(0);
   	assertEquals("double" ,r.getCell((short)0).getNumericCellValue(),(double)1,0); //sanity check
   	assertNotNull(r.getCell((short)1)); 
   	assertEquals("formula", r.getCell((short)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((short)0);
	assertEquals("style should match", style.getIndex(), c.getCellStyle().getIndex());
    }


    /**
     *
     */
    public void testAddEmptyRow() throws Exception {
        //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);

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        workbook.write(out);
        out.close();

        workbook = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
        assertTrue("No Exceptions while reading file", true);

        //try adding empty rows in an existing worksheet
        String cwd = System.getProperty("HSSF.testdata.path");
        FileInputStream in = new FileInputStream(new File(cwd, "Simple.xls"));
        workbook = new HSSFWorkbook(in);
        in.close();
        assertTrue("No Exceptions while reading file", true);

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

        out = new ByteArrayOutputStream();
        workbook.write(out);
        out.close();

        workbook = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
        assertTrue("No Exceptions while reading file", true);

    }
    
    public void testAutoSizeColumn() throws Exception {
		String filename = System.getProperty("HSSF.testdata.path");
		filename = filename + "/43902.xls";
		String sheetName = "my sheet";
		FileInputStream is = new FileInputStream(filename);
		POIFSFileSystem fs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		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 = 3024;
		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((short)0) >= minWithRow1And2);
		assertTrue("Column autosized with only one row: wrong width", sheet.getColumnWidth((short)0) <= maxWithRow1And2);
		
		//create a region over the 2nd row and auto size the first column
		sheet.addMergedRegion(new Region(1,(short)0,1,(short)1));
		sheet.autoSizeColumn((short)0);
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		wb.write(out);
		out.close();
		
		// check that the autoSized column width has ignored the 2nd row 
		// because it is included in a merged region (Excel like behavior)
		HSSFWorkbook wb2 = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
		HSSFSheet sheet2 = wb2.getSheet(sheetName);
		assertTrue(sheet2.getColumnWidth((short)0) >= minWithRow1Only);
		assertTrue(sheet2.getColumnWidth((short)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);
		out = new ByteArrayOutputStream();
		wb2.write(out);
		out.close();
		HSSFWorkbook wb3 = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
		HSSFSheet sheet3 = wb3.getSheet(sheetName);
		assertTrue(sheet3.getColumnWidth((short)0) >= minWithRow1And2);
		assertTrue(sheet3.getColumnWidth((short)0) <= maxWithRow1And2);
    }

    /**
     * Setting ForceFormulaRecalculation on sheets
     */
    public void testForceRecalculation() throws Exception {
        String filename = System.getProperty("HSSF.testdata.path");
        filename = filename + "/UncalcedRecord.xls";
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filename));
        
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFSheet sheet2 = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        row.createCell((short) 0).setCellValue(5);
        row.createCell((short) 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 static void main(java.lang.String[] args) {
		 junit.textui.TestRunner.run(TestHSSFSheet.class);
	}    
}