testnamedrange.java

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

	 public void testPrintAreaFile() {
	 	HSSFWorkbook workbook = new HSSFWorkbook();
	 	workbook.createSheet("Test Print Area");
	 	String sheetName = workbook.getSheetName(0);
		 
	 
	 	String reference = sheetName+"!$A$1:$B$1";
	 	workbook.setPrintArea(0, reference);
		 
		workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
		 
	 	String retrievedPrintArea = workbook.getPrintArea(0);	   
	 	assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
	 	assertEquals("References Match", "'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
	}

	/**
	 * Test to see if multiple print areas made it to the file
	 */
	public void testMultiplePrintAreaFile() {
		HSSFWorkbook workbook = new HSSFWorkbook();

		workbook.createSheet("Sheet1");
		workbook.createSheet("Sheet2");
		workbook.createSheet("Sheet3");
		String reference1 = "Sheet1!$A$1:$B$1";
		String reference2 = "Sheet2!$B$2:$D$5";
		String reference3 = "Sheet3!$D$2:$F$5";
		
		workbook.setPrintArea(0, reference1); 
		workbook.setPrintArea(1, reference2);
		workbook.setPrintArea(2, reference3);

		//Check created print areas
		String retrievedPrintArea;
		
		retrievedPrintArea = workbook.getPrintArea(0);
		assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
		assertEquals(reference1, retrievedPrintArea);

		retrievedPrintArea = workbook.getPrintArea(1);
		assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
		assertEquals(reference2, retrievedPrintArea);

		retrievedPrintArea = workbook.getPrintArea(2);
		assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
		assertEquals(reference3, retrievedPrintArea);

		// Check print areas after re-reading workbook
		workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);

		retrievedPrintArea = workbook.getPrintArea(0);
		assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
		assertEquals(reference1, retrievedPrintArea);

		retrievedPrintArea = workbook.getPrintArea(1);
		assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
		assertEquals(reference2, retrievedPrintArea);

		retrievedPrintArea = workbook.getPrintArea(2);
		assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
		assertEquals(reference3, retrievedPrintArea);
	}

	/**
	 * 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("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
	}


	/**
	 * Tests the parsing of union area expressions, and re-display in the presence of sheet names
	 * with special characters.
	 */
	public void testPrintAreaUnion(){
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Test Print Area");
		String sheetName = workbook.getSheetName(0);

 
		String reference =	   sheetName +  "!$A$1:$B$1, " + sheetName + "!$D$1:$F$2";
		String expResult = "'" + sheetName + "'!$A$1:$B$1,'" + sheetName + "'!$D$1:$F$2";
		workbook.setPrintArea(0, reference);
				 
		String retrievedPrintArea = workbook.getPrintArea(0);

		assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
		assertEquals(expResult, 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(0).setCellValue(new HSSFRichTextString(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());
		assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell());

		CellReference cref = aref.getFirstCell();
		assertNotNull(cref);
		HSSFSheet s = wb.getSheet(cref.getSheetName());
		assertNotNull(s);
		HSSFRow r = sheet.getRow(cref.getRow());
		HSSFCell c = r.getCell(cref.getCol());
		String contents = c.getRichStringCellValue().getString();
		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(0).setCellValue(new HSSFRichTextString(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.getRichStringCellValue().getString();
		assertEquals("Contents of cell retrieved by its named reference", contents, cvalue);
	}

	public void testDeletedReference() throws Exception {
		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("24207.xls");
		assertEquals(2, wb.getNumberOfNames());

		HSSFName name1 = wb.getNameAt(0);
		assertEquals("a", name1.getNameName());
		assertEquals("Sheet1!$A$1", name1.getReference());
		AreaReference ref1 = new AreaReference(name1.getReference());
		assertTrue("Successfully constructed first reference", true);

		HSSFName name2 = wb.getNameAt(1);
		assertEquals("b", name2.getNameName());
		assertEquals("Sheet1!#REF!", name2.getReference());
		assertTrue(name2.isDeleted());
		try {
			AreaReference ref2 = new AreaReference(name2.getReference());
			fail("attempt to supply an invalid reference to AreaReference constructor results in exception");
		} catch (StringIndexOutOfBoundsException e) { // TODO - use a different exception for this condition
			// expected during successful test
		}
   }
	
	public void testRepeatingRowsAndColumsNames() {
		// First test that setting RR&C for same sheet more than once only creates a 
		// single  Print_Titles built-in record
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("FirstSheet");
		
		// set repeating rows and columns twice for the first sheet
		for (int i = 0; i < 2; i++) {
			wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3-1);
			sheet.createFreezePane(0, 3);
		}
		assertEquals(1, wb.getNumberOfNames());
		HSSFName nr1 = wb.getNameAt(0);
		
		assertEquals("Print_Titles", nr1.getNameName());
		if (false) {
			// 	TODO - full column references not rendering properly, absolute markers not present either
			assertEquals("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.getReference());
		} else {
			assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
		}
		
		// Save and re-open
		HSSFWorkbook nwb = HSSFTestDataSamples.writeOutAndReadBack(wb);

		assertEquals(1, nwb.getNumberOfNames());
		nr1 = nwb.getNameAt(0);
		
		assertEquals("Print_Titles", nr1.getNameName());
		assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
		
		// check that setting RR&C on a second sheet causes a new Print_Titles built-in
		// name to be created
		sheet = nwb.createSheet("SecondSheet");
		nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0);

		assertEquals(2, nwb.getNumberOfNames());
		HSSFName nr2 = nwb.getNameAt(1);
		
		assertEquals("Print_Titles", nr2.getNameName());
		assertEquals("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.getReference());
		
		if (false) {
			// In case you fancy checking in excel, to ensure it
			//  won't complain about the file now
			try {
				File tempFile = File.createTempFile("POI-45126-", ".xls");
				FileOutputStream fout = new FileOutputStream(tempFile);
				nwb.write(fout);
				fout.close();
				System.out.println("check out " + tempFile.getAbsolutePath());
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
	}
}