testnamedrange.java

java 读写word excel ppt

		 // Set the reference for the named range for worksheet 'sheet2'
		 namedRange2.setReference("sheet2" + "!$A$1:$O$21");        
 
		 // Write the workbook to a file
		 File file = TempFile.createTempFile("testMuiltipletNamedRanges", ".xls");
		 FileOutputStream fileOut = new FileOutputStream(file);
		 wb.write(fileOut);
		 fileOut.close();
        
		 assertTrue("file exists",file.exists());
            
		 // Read the Excel file and verify its content
		 FileInputStream in = new FileInputStream(file);
		 wb = new HSSFWorkbook(in);
		 HSSFName nm1 =wb.getNameAt(wb.getNameIndex("RangeTest1"));
		 assertTrue("Name is "+nm1.getNameName(),"RangeTest1".equals(nm1.getNameName()));
		 assertTrue("Reference is "+nm1.getReference(),(wb.getSheetName(0)+"!$A$1:$L$41").equals(nm1.getReference()));

		 HSSFName nm2 =wb.getNameAt(wb.getNameIndex("RangeTest2"));
		 assertTrue("Name is "+nm2.getNameName(),"RangeTest2".equals(nm2.getNameName()));
		 assertTrue("Reference is "+nm2.getReference(),(wb.getSheetName(1)+"!$A$1:$O$21").equals(nm2.getReference()));
	 }       
        
        
	 /**
	  * Test to see if the print areas can be retrieved/created in memory
	  */
	 public void testSinglePrintArea()
	 {
	     HSSFWorkbook workbook = new HSSFWorkbook();        
	     HSSFSheet sheet = workbook.createSheet("Test Print Area");                
	     String sheetName = workbook.getSheetName(0);
	     
	     String reference = sheetName+"!$A$1:$B$1";
	     workbook.setPrintArea(0, reference);
	             
	     String retrievedPrintArea = workbook.getPrintArea(0);
	    
	 	 assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);        
	     assertEquals(reference, retrievedPrintArea);
	     
	 }

	 /**
	  * For Convenience, dont force sheet names to be used
	  */
	 public void testSinglePrintAreaWOSheet()
	 {
		 HSSFWorkbook workbook = new HSSFWorkbook();        
		 HSSFSheet sheet = workbook.createSheet("Test Print Area");                
		 String sheetName = workbook.getSheetName(0);
	     
		 String reference = "$A$1:$B$1";
		 workbook.setPrintArea(0, reference);
	             
		 String retrievedPrintArea = workbook.getPrintArea(0);
	    
		 assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);        
		 assertEquals(sheetName+"!"+reference, retrievedPrintArea);
	     
	 }


	 /**
	  * Test to see if the print area can be retrieved from an excel created file
	  */
	 public void testPrintAreaFileRead()
	 throws IOException
	 {
		FileInputStream fis = null;
		POIFSFileSystem fs  = null;
		HSSFWorkbook workbook     = null;
        
		String filename = System.getProperty("HSSF.testdata.path");

		filename = filename + "/SimpleWithPrintArea.xls";
        
        try {
        
			fis = new FileInputStream(filename);
			fs = new POIFSFileSystem(fis);
			workbook = new HSSFWorkbook(fs);
			
			String sheetName = workbook.getSheetName(0);
			String reference = sheetName+"!$A$1:$C$5";
			
			assertEquals(reference, workbook.getPrintArea(0));
		
        } finally {
			fis.close();
		
        }
		
		     
         
	}


	 /**
	  * Test to see if the print area made it to the file
	  */
	 public void testPrintAreaFile()
	 throws IOException
	 {
	 	HSSFWorkbook workbook = new HSSFWorkbook();        
	 	HSSFSheet sheet = workbook.createSheet("Test Print Area");                
	 	String sheetName = workbook.getSheetName(0);
	     
	 
	 	String reference = sheetName+"!$A$1:$B$1";
	 	workbook.setPrintArea(0, reference);
	     
	    File file = TempFile.createTempFile("testPrintArea",".xls");
	     
	    FileOutputStream fileOut = new FileOutputStream(file);
	    workbook.write(fileOut);
	    fileOut.close();
	     
	    assertTrue("file exists",file.exists());
	     
	    FileInputStream in = new FileInputStream(file);
	    workbook = new HSSFWorkbook(in);
	     
	 	String retrievedPrintArea = workbook.getPrintArea(0);       
	 	assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);        
	 	assertEquals("References Match", reference, retrievedPrintArea);
         
	}

	/**
	 * Test to see if multiple print areas made it to the file
	 */
	public void testMultiplePrintAreaFile()
	throws IOException
	{
	    HSSFWorkbook workbook = new HSSFWorkbook();        
	    
	    HSSFSheet sheet = workbook.createSheet("Sheet 1");
	    sheet = workbook.createSheet("Sheet 2");
	    sheet = workbook.createSheet("Sheet 3");
	    
	    String sheetName = workbook.getSheetName(0);
		String reference = null;

		reference = sheetName+"!$A$1:$B$1";
		workbook.setPrintArea(0, reference); 

		sheetName = workbook.getSheetName(1);
	    String reference2 = sheetName+"!$B$2:$D$5";
	    workbook.setPrintArea(1, reference2);

		sheetName = workbook.getSheetName(2);
		String reference3 = sheetName+"!$D$2:$F$5";
		workbook.setPrintArea(2, reference3);
	    
	    File file = TempFile.createTempFile("testMultiPrintArea",".xls");        
	    
	    FileOutputStream fileOut = new FileOutputStream(file);
	    workbook.write(fileOut);
	    fileOut.close();
	    
	    assertTrue("file exists",file.exists());
	    
	    FileInputStream in = new FileInputStream(file);
	    workbook = new HSSFWorkbook(in);
	    
	    String retrievedPrintArea = workbook.getPrintArea(0);        
	    assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
	    assertEquals(reference, retrievedPrintArea);
	    
		String retrievedPrintArea2 = workbook.getPrintArea(1);        
	    assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea2);
	    assertEquals(reference2, retrievedPrintArea2);
		
		String retrievedPrintArea3 = workbook.getPrintArea(2);        
	    assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea3);
	    assertEquals(reference3, retrievedPrintArea3);
	    
	    
	}
    
    /**
     * Tests the setting of print areas with coordinates (Row/Column designations)
     *
     */
    public void testPrintAreaCoords(){
		HSSFWorkbook workbook = new HSSFWorkbook();        
		HSSFSheet sheet = workbook.createSheet("Test Print Area");                
		String sheetName = workbook.getSheetName(0);
	     
		String reference = sheetName+"!$A$1:$B$1";
		workbook.setPrintArea(0, 0, 1, 0, 0);
	             
		String retrievedPrintArea = workbook.getPrintArea(0);
	    
		assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);        
		assertEquals(reference, retrievedPrintArea);    	
    }
     
    /**
     * Verifies an existing print area is deleted
     *
     */
    public void testPrintAreaRemove() {
		HSSFWorkbook workbook = new HSSFWorkbook();        
		HSSFSheet sheet = workbook.createSheet("Test Print Area");                
		String sheetName = workbook.getSheetName(0);
	     
		String reference = sheetName+"!$A$1:$B$1";
		workbook.setPrintArea(0, 0, 1, 0, 0);
	             
		String retrievedPrintArea = workbook.getPrintArea(0);
	    
		assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);        
    	
    	workbook.removePrintArea(0);
    	assertNull("PrintArea was not removed", workbook.getPrintArea(0)); 
    }
    
    /**
     * Verifies correct functioning for "single cell named range" (aka "named cell")
     */
    public void testNamedCell_1() {
        
        // setup for this testcase
        String sheetName = "Test Named Cell";
        String cellName = "A name for a named cell";
        String cellValue = "TEST Value";
        HSSFWorkbook wb = new HSSFWorkbook();        
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.createRow(0).createCell((short) 0).setCellValue(cellValue);
         
        // create named range for a single cell using areareference
        HSSFName namedCell = wb.createName();
        namedCell.setNameName(cellName);
        String reference = sheetName+"!A1:A1";
        namedCell.setReference(reference);
        
        // retrieve the newly created named range
        int namedCellIdx = wb.getNameIndex(cellName);
        HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
        assertNotNull(aNamedCell);
        
        // retrieve the cell at the named range and test its contents
        AreaReference aref = new AreaReference(aNamedCell.getReference());
        CellReference[] crefs = aref.getCells();
        assertNotNull(crefs);
        assertEquals("Should be exactly 1 cell in the named cell :'" +cellName+"'", 1, crefs.length);
        for (int i=0, iSize=crefs.length; i<iSize; i++) {
            CellReference cref = crefs[i];
            assertNotNull(cref);
            HSSFSheet s = wb.getSheet(cref.getSheetName());
            HSSFRow r = sheet.getRow(cref.getRow());
            HSSFCell c = r.getCell(cref.getCol());
            String contents = c.getStringCellValue();
            assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
        }
    }

    /**
     * Verifies correct functioning for "single cell named range" (aka "named cell")
     */
    public void testNamedCell_2() {
        
        // setup for this testcase
        String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sname);
        sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);
         
        // create named range for a single cell using cellreference
        HSSFName namedCell = wb.createName();
        namedCell.setNameName(cname);
        String reference = sname+"!A1";
        namedCell.setReference(reference);
        
        // retrieve the newly created named range
        int namedCellIdx = wb.getNameIndex(cname);
        HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
        assertNotNull(aNamedCell);
        
        // retrieve the cell at the named range and test its contents
        CellReference cref = new CellReference(aNamedCell.getReference());
        assertNotNull(cref);
        HSSFSheet s = wb.getSheet(cref.getSheetName());
        HSSFRow r = sheet.getRow(cref.getRow());
        HSSFCell c = r.getCell(cref.getCol());
        String contents = c.getStringCellValue();
        assertEquals("Contents of cell retrieved by its named reference", contents, cvalue);
    }

}