testnamedrange.java

Office格式转换代码

/* ====================================================================
 * The Apache Software License, Version 1.1
 *
 * Copyright (c) 2003 The Apache Software Foundation.  All rights
 * reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. The end-user documentation included with the redistribution,
 *    if any, must include the following acknowledgment:
 *       "This product includes software developed by the
 *        Apache Software Foundation (http://www.apache.org/)."
 *    Alternately, this acknowledgment may appear in the software itself,
 *    if and wherever such third-party acknowledgments normally appear.
 *
 * 4. The names "Apache" and "Apache Software Foundation" and
 *    "Apache POI" must not be used to endorse or promote products
 *    derived from this software without prior written permission. For
 *    written permission, please contact [e-mail].
 *
 * 5. Products derived from this software may not be called "Apache",
 *    "Apache POI", nor may "Apache" appear in their name, without
 *    prior written permission of the Apache Software Foundation.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 * ====================================================================
 *
 * This software consists of voluntary contributions made by many
 * individuals on behalf of the Apache Software Foundation.  For more
 * information on the Apache Software Foundation, please see
 * <http://www.apache.org/>.
 */

package org.apache.poi.hssf.usermodel;

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

import junit.framework.TestCase;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;


/**
 * 
 * @author ROMANL
 * @author Andrew C. Oliver (acoliver at apache dot org)
 * @author Danny Mui (danny at muibros.com)
 */
public class TestNamedRange
    extends TestCase {
    
    public TestNamedRange(String testName) {
        super(testName);
    }
    
    public static void main(java.lang.String[] args) {
        String filename = System.getProperty("HSSF.testdata.path");
        
        // assume andy is running this in the debugger
        if (filename == null)
        {
            if (args != null && args.length == 1) {
            System.setProperty(
                "HSSF.testdata.path",
                args[0]);
            } else {
                System.err.println("Geesh, no HSSF.testdata.path system " +
                          "property, no command line arg with the path "+
                          "what do you expect me to do, guess where teh data " +
                          "files are?  Sorry, I give up!");
                                   
            }
            
        }
        
        
        junit.textui.TestRunner.run(TestNamedRange.class);
    }
    
    /** Test of TestCase method, of class test.RangeTest. */
    public void testNamedRange() 
        throws IOException
    {
        FileInputStream fis = null;
        POIFSFileSystem fs  = null;
        HSSFWorkbook wb     = null;
        
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Simple.xls";
        
        
        fis = new FileInputStream(filename);
        fs = new POIFSFileSystem(fis);
        wb = new HSSFWorkbook(fs);
        
        
        //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);

        File             file = File.createTempFile("testNamedRange",
                                        ".xls");

        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fis.close();
        fileOut.close();
        
        assertTrue("file exists",file.exists());
            
        FileInputStream in = new FileInputStream(file);
        wb = new HSSFWorkbook(in);
        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://nagoya.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
     */   
	public void testNamedRead() throws IOException
	{        
		FileInputStream fis = null;
 	 	POIFSFileSystem fs  = null;
		HSSFWorkbook wb     = null;
        
		String filename = System.getProperty("HSSF.testdata.path");

		filename = filename + "/namedinput.xls";
        
        
		fis = new FileInputStream(filename);
		fs = new POIFSFileSystem(fis);
		wb = new HSSFWorkbook(fs);

		//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();
		 
		fis.close();
		 
		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://nagoya.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
     */
    public void testNamedReadModify() throws IOException
    {
		FileInputStream fis = null;
		POIFSFileSystem fs  = null;
		HSSFWorkbook wb     = null;
        
		String filename = System.getProperty("HSSF.testdata.path");

		filename = filename + "/namedinput.xls";
        
        
		fis = new FileInputStream(filename);
		fs = new POIFSFileSystem(fis);
		wb = new HSSFWorkbook(fs);
    	
    	
		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() 
	    throws IOException
	{
	    HSSFWorkbook wb     = new HSSFWorkbook();
         

		HSSFSheet sheet = wb.createSheet("Sheet1");
		String sheetName = wb.getSheetName(0);
        
        assertEquals("Sheet1", 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();
                                
	    File file = File.createTempFile("testMultiNamedRange", ".xls");
 
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();