testnamedrange.java

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

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.hssf.usermodel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import junit.framework.TestCase;

import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.util.AreaReference;
import org.apache.poi.hssf.util.CellReference;

/**
 * 
 * @author ROMANL
 * @author Andrew C. Oliver (acoliver at apache dot org)
 * @author Danny Mui (danny at muibros.com)
 * @author Amol S. Deshmukh < amol at ap ache dot org > 
 */
public final class TestNamedRange extends TestCase {

	private static HSSFWorkbook openSample(String sampleFileName) {
		return HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
	}

	/** Test of TestCase method, of class test.RangeTest. */
	public void testNamedRange() {
		HSSFWorkbook wb = openSample("Simple.xls");

		//Creating new Named Range
		HSSFName newNamedRange = wb.createName();

		//Getting Sheet Name for the reference
		String sheetName = wb.getSheetName(0);

		//Setting its name
		newNamedRange.setNameName("RangeTest");
		//Setting its reference
		newNamedRange.setReference(sheetName + "!$D$4:$E$8");

		//Getting NAmed Range
		HSSFName namedRange1 = wb.getNameAt(0);
		//Getting it sheet name
		sheetName = namedRange1.getSheetName();
		//Getting its reference
		String referece = namedRange1.getReference();

		// sanity check
		SanityChecker c = new SanityChecker();
		c.checkHSSFWorkbook(wb);

		wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
		HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
		assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
		assertEquals(wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference());
	}
	 
	/**
	 * Reads an excel file already containing a named range.
	 * <p>
	 * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
	 */   
	public void testNamedRead() {
		HSSFWorkbook wb = openSample("namedinput.xls");

		//Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
		int NamedRangeIndex	 = wb.getNameIndex("NamedRangeName");

		//Getting NAmed Range
		HSSFName namedRange1 = wb.getNameAt(NamedRangeIndex);
		String sheetName = wb.getSheetName(0);

		//Getting its reference
		String reference = namedRange1.getReference();
		 
		assertEquals(sheetName+"!$A$1:$D$10", reference);

		HSSFName namedRange2 = wb.getNameAt(1);

		assertEquals(sheetName+"!$D$17:$G$27", namedRange2.getReference());
		assertEquals("SecondNamedRange", namedRange2.getNameName());
	}

	/**
	 * Reads an excel file already containing a named range and updates it
	 * <p>
	 * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
	 */
	public void testNamedReadModify() {
		HSSFWorkbook wb = openSample("namedinput.xls");

		HSSFName name = wb.getNameAt(0);
		String sheetName = wb.getSheetName(0);

		assertEquals(sheetName+"!$A$1:$D$10", name.getReference());

		name = wb.getNameAt(1);
		String newReference = sheetName +"!$A$1:$C$36"; 
		 
		name.setReference(newReference);
		assertEquals(newReference, name.getReference()); 
	}

	/**
	 * Test that multiple named ranges can be added written and read
	 */
	public void testMultipleNamedWrite() {
		HSSFWorkbook wb	 = new HSSFWorkbook();
		 

		wb.createSheet("testSheet1");
		String sheetName = wb.getSheetName(0);

		assertEquals("testSheet1", sheetName);
		 
		//Creating new Named Range
		HSSFName newNamedRange = wb.createName();

		newNamedRange.setNameName("RangeTest");
		newNamedRange.setReference(sheetName + "!$D$4:$E$8");

		//Creating another new Named Range
		HSSFName newNamedRange2 = wb.createName();

		newNamedRange2.setNameName("AnotherTest");
		newNamedRange2.setReference(sheetName + "!$F$1:$G$6");


		HSSFName namedRange1 = wb.getNameAt(0);
		String referece = namedRange1.getReference();

		wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
		HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
		assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
		assertTrue("Reference is "+nm.getReference(),(wb.getSheetName(0)+"!$D$4:$E$8").equals(nm.getReference()));
		 
		nm = wb.getNameAt(wb.getNameIndex("AnotherTest"));
		assertTrue("Name is "+nm.getNameName(),"AnotherTest".equals(nm.getNameName()));
		assertTrue("Reference is "+nm.getReference(),newNamedRange2.getReference().equals(nm.getReference()));
	}

	/**
	 * Test case provided by [e-mail] (Chun Zhang)
	 * <p>
	 * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a>
	 */
	public void testMultiNamedRange() {

		 // Create a new workbook
		 HSSFWorkbook wb = new HSSFWorkbook ();


		 // Create a worksheet 'sheet1' in the new workbook
		 wb.createSheet ();
		 wb.setSheetName (0, "sheet1");

		 // Create another worksheet 'sheet2' in the new workbook
		 wb.createSheet ();
		 wb.setSheetName (1, "sheet2");

		 // Create a new named range for worksheet 'sheet1'
		 HSSFName namedRange1 = wb.createName();

		 // Set the name for the named range for worksheet 'sheet1'
		 namedRange1.setNameName("RangeTest1");

		 // Set the reference for the named range for worksheet 'sheet1'
		 namedRange1.setReference("sheet1" + "!$A$1:$L$41");

		 // Create a new named range for worksheet 'sheet2'
		 HSSFName namedRange2 = wb.createName();

		 // Set the name for the named range for worksheet 'sheet2'
		 namedRange2.setNameName("RangeTest2");

		 // Set the reference for the named range for worksheet 'sheet2'
		 namedRange2.setReference("sheet2" + "!$A$1:$O$21");
 
		 // Write the workbook to a file
		 // Read the Excel file and verify its content
		 wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
		 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()));
	 }	   

	public void testUnicodeNamedRange() {
		HSSFWorkbook workBook = new HSSFWorkbook();
		workBook.createSheet("Test");
		HSSFName name = workBook.createName();
		name.setNameName("\u03B1");
		name.setReference("Test!$D$3:$E$8");


		HSSFWorkbook workBook2 = HSSFTestDataSamples.writeOutAndReadBack(workBook);
		HSSFName name2 = workBook2.getNameAt(0);

		assertEquals("\u03B1", name2.getNameName());
		assertEquals("Test!$D$3:$E$8", name2.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("'" + sheetName + "'!$A$1:$B$1", 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() {
		 HSSFWorkbook workbook = openSample("SimpleWithPrintArea.xls");

		String sheetName = workbook.getSheetName(0);
		String reference = sheetName+"!$A$1:$C$5";

		assertEquals(reference, workbook.getPrintArea(0));
	}

	 /**
	  * Test to see if the print area made it to the file
	  */