From 9a7877deb78da7158f40965f08d45c5803f34505 Mon Sep 17 00:00:00 2001 From: Danny Mui Date: Thu, 6 Mar 2003 20:41:17 +0000 Subject: [PATCH] Print Area Patch RFE Bug #14089 Also Includes Name Reference refactoring to support references of the form: Sheet1!$A$1:$B$4 instead of Sheet1.$A$1:$B$4 PR: Bug #9632 Bug #16411 Bug #16315 Bug #13775 (1.5 Bug) Submitted by: testMultiNamedRange (TestNamedRange.java) Test Case Submitted by czhang@cambian.com (Chun Zhang) git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@353021 13f79535-47bb-0310-9956-ffa450edef68 --- .../org/apache/poi/hssf/model/Workbook.java | 43 ++ .../poi/hssf/model/WorkbookRecordList.java | 124 +++- .../apache/poi/hssf/record/NameRecord.java | 177 +++++- .../poi/hssf/record/formula/Area3DPtg.java | 555 +++++++++--------- .../apache/poi/hssf/usermodel/HSSFName.java | 7 +- .../poi/hssf/usermodel/HSSFWorkbook.java | 52 +- .../apache/poi/hssf/util/RangeAddress.java | 23 +- .../org/apache/poi/hssf/HSSFTests.java | 2 + .../poi/hssf/data/SimpleWithPrintArea.xls | Bin 0 -> 18432 bytes .../org/apache/poi/hssf/data/namedinput.xls | Bin 0 -> 13824 bytes .../poi/hssf/usermodel/TestHSSFCell.java | 2 +- .../poi/hssf/usermodel/TestNamedRange.java | 402 ++++++++++++- .../poi/hssf/usermodel/TestWorkbook.java | 43 +- .../poi/hssf/util/TestRangeAddress.java | 81 +++ 14 files changed, 1159 insertions(+), 352 deletions(-) create mode 100755 src/testcases/org/apache/poi/hssf/data/SimpleWithPrintArea.xls create mode 100755 src/testcases/org/apache/poi/hssf/data/namedinput.xls create mode 100755 src/testcases/org/apache/poi/hssf/util/TestRangeAddress.java diff --git a/src/java/org/apache/poi/hssf/model/Workbook.java b/src/java/org/apache/poi/hssf/model/Workbook.java index 64b0714f80..f622707d38 100644 --- a/src/java/org/apache/poi/hssf/model/Workbook.java +++ b/src/java/org/apache/poi/hssf/model/Workbook.java @@ -335,6 +335,29 @@ public class Workbook implements Model { return retval; } + + /**Retrieves the Builtin NameRecord that matches the name and index + * There shouldn't be too many names to make the sequential search too slow + * @param name byte representation of the builtin name to match + * @param sheetIndex zero-based sheet reference + * @return null if no builtin NameRecord matches + */ + public NameRecord getSpecificBuiltinRecord(byte name, int sheetIndex) + { + Iterator iterator = names.iterator(); + while (iterator.hasNext()) { + NameRecord record = ( NameRecord ) iterator.next(); + + //print areas are one based + if (record.getBuiltInName() == name && record.getIndexToSheet() == sheetIndex) { + return record; + } + } + + return null; + + } + public int getNumRecords() { return records.size(); } @@ -1817,6 +1840,7 @@ public class Workbook implements Model { return name; } + /** creates new name * @return new name record */ @@ -1832,6 +1856,22 @@ public class Workbook implements Model { return name; } + /**Generates a NameRecord to represent a built-in region + * @return a new NameRecord unless the index is invalid + */ + public NameRecord createBuiltInName(byte builtInName, int index) + { + if (index == -1 || index+1 > (int)Short.MAX_VALUE) + throw new IllegalArgumentException("Index is not valid ["+index+"]"); + + NameRecord name = new NameRecord(builtInName, (short)(index)); + + addName(name); + + return name; + } + + /** removes the name * @param namenum name index */ @@ -2018,4 +2058,7 @@ public class Workbook implements Model { } return palette; } + + } + diff --git a/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java b/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java index a846d67532..8140119ded 100644 --- a/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java +++ b/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java @@ -1,10 +1,65 @@ -package org.apache.poi.hssf.model; -import org.apache.poi.hssf.record.Record; +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 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 apache@apache.org. + * + * 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 + * . + */ +package org.apache.poi.hssf.model; import java.util.ArrayList; -import java.util.List; import java.util.Iterator; +import java.util.List; + +import org.apache.poi.hssf.record.NameRecord; +import org.apache.poi.hssf.record.Record; public class WorkbookRecordList { @@ -16,8 +71,9 @@ public class WorkbookRecordList private int fontpos = 0; // hold the position of the last font record private int xfpos = 0; // hold the position of the last extended font record private int backuppos = 0; // holds the position of the backup record. -// public int namepos = 0; // holds the position of last name record -// public int supbookpos = 0; // holds the position of sup book + private int namepos = 0; // holds the position of last name record + private int supbookpos = 0; // holds the position of sup book + private int externsheetPos = 0;// holds the position of the extern sheet private int palettepos = 0; // hold the position of the palette, if applicable @@ -45,9 +101,10 @@ public class WorkbookRecordList if (getFontpos() >= pos) setFontpos( fontpos + 1 ); if (getXfpos() >= pos) setXfpos( xfpos + 1 ); if (getBackuppos() >= pos) setBackuppos( backuppos + 1 ); -// if (namepos >= pos) namepos++; -// if (supbookpos >= pos) supbookpos++; + if (getNamepos() >= pos) setNamepos(namepos+1); + if (getSupbookpos() >= pos) setSupbookpos(supbookpos+1); if (getPalettepos() >= pos) setPalettepos( palettepos + 1 ); + if (getExternsheetPos() >= pos) setExternsheetPos(getExternsheetPos() + 1); } public List getRecords() @@ -69,9 +126,10 @@ public class WorkbookRecordList if (getFontpos() >= pos) setFontpos( fontpos - 1 ); if (getXfpos() >= pos) setXfpos( xfpos - 1 ); if (getBackuppos() >= pos) setBackuppos( backuppos - 1 ); -// if (namepos >= pos) namepos--; -// if (supbookpos >= pos) supbookpos--; + if (getNamepos() >= pos) setNamepos(getNamepos()-1); + if (getSupbookpos() >= pos) setSupbookpos(getSupbookpos()-1); if (getPalettepos() >= pos) setPalettepos( palettepos - 1 ); + if (getExternsheetPos() >= pos) setExternsheetPos( getExternsheetPos() -1); } public int getProtpos() @@ -144,5 +202,53 @@ public class WorkbookRecordList this.palettepos = palettepos; } + + /** + * Returns the namepos. + * @return int + */ + public int getNamepos() { + return namepos; + } + + /** + * Returns the supbookpos. + * @return int + */ + public int getSupbookpos() { + return supbookpos; + } + + /** + * Sets the namepos. + * @param namepos The namepos to set + */ + public void setNamepos(int namepos) { + this.namepos = namepos; + } + + /** + * Sets the supbookpos. + * @param supbookpos The supbookpos to set + */ + public void setSupbookpos(int supbookpos) { + this.supbookpos = supbookpos; + } + + /** + * Returns the externsheetPos. + * @return int + */ + public int getExternsheetPos() { + return externsheetPos; + } + + /** + * Sets the externsheetPos. + * @param externsheetPos The externsheetPos to set + */ + public void setExternsheetPos(int externsheetPos) { + this.externsheetPos = externsheetPos; + } } diff --git a/src/java/org/apache/poi/hssf/record/NameRecord.java b/src/java/org/apache/poi/hssf/record/NameRecord.java index 30b5ade2ae..406b06b23c 100644 --- a/src/java/org/apache/poi/hssf/record/NameRecord.java +++ b/src/java/org/apache/poi/hssf/record/NameRecord.java @@ -80,6 +80,53 @@ public class NameRecord extends Record { /** */ public final static short sid = 0x18; //Docs says that it is 0x218 + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_CONSOLIDATE_AREA = (byte)1; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_AUTO_OPEN = (byte)2; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_AUTO_CLOSE = (byte)3; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_DATABASE = (byte)4; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_CRITERIA = (byte)5; + + public final static byte BUILTIN_PRINT_AREA = (byte)6; + public final static byte BUILTIN_PRINT_TITLE = (byte)7; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_RECORDER = (byte)8; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_DATA_FORM = (byte)9; + + /**Included for completeness sake, not implemented + */ + + public final static byte BUILTIN_AUTO_ACTIVATE = (byte)10; + + /**Included for completeness sake, not implemented + */ + + public final static byte BUILTIN_AUTO_DEACTIVATE = (byte)11; + + /**Included for completeness sake, not implemented + */ + public final static byte BUILTIN_SHEET_TITLE = (byte)12; + + private short field_1_option_flag; private byte field_2_keyboard_shortcut; private byte field_3_length_name_text; @@ -135,6 +182,28 @@ public class NameRecord extends Record { super(id, size, data, offset); } + /** + * Constructor to create a built-in named region + * @param builtin Built-in byte representation for the name record, use the public constants + * @param index + */ + public NameRecord(byte builtin, short index) + { + this(); + this.field_12_builtIn_name = builtin; + this.setOptionFlag((short)(this.getOptionFlag() | (short)0x20)); + this.setNameTextLength((byte)1); + this.setEqualsToIndexToSheet(index); //the extern sheets are set through references + + //clearing these because they are not used with builtin records + this.setCustomMenuLength((byte)0); + this.setDescriptionTextLength((byte)0); + this.setHelpTopicLength((byte)0); + this.setStatusBarLength((byte)0); + + + } + /** sets the option flag for the named range * @param flag option flag */ @@ -142,6 +211,7 @@ public class NameRecord extends Record { field_1_option_flag = flag; } + /** sets the keyboard shortcut * @param shortcut keyboard shortcut */ @@ -179,6 +249,15 @@ public class NameRecord extends Record { return field_6_equals_to_index_to_sheet; } + /** + * Convenience method to retrieve the index the name refers to. + * @see getEqualsToIndexToSheet() + * @return short + */ + public short getIndexToSheet() { + return getEqualsToIndexToSheet(); + } + public void setEqualsToIndexToSheet(short value) { field_6_equals_to_index_to_sheet = value; @@ -329,12 +408,30 @@ public class NameRecord extends Record { return field_11_compressed_unicode_flag; } - /** gets the name - * @return name - */ - public String getNameText(){ - return field_12_name_text; - } + /**Convenience Function to determine if the name is a built-in name + */ + public boolean isBuiltInName() + { + return ((this.getOptionFlag() & (short)0x20) != 0); + } + + + /** gets the name + * @return name + */ + public String getNameText(){ + + return this.isBuiltInName() ? this.translateBuiltInName(this.getBuiltInName()) : field_12_name_text; + } + + /** Gets the Built In Name + * @return the built in Name + */ + public byte getBuiltInName() + { + return this.field_12_builtIn_name; + } + /** gets the definition, reference (Formula) * @return definition -- can be null if we cant parse ptgs @@ -425,20 +522,28 @@ public class NameRecord extends Record { else { */ LittleEndian.putShort( data, 2 + offset, (short) ( 15 + getTextsLength() ) ); + + int start_of_name_definition = 19 + field_3_length_name_text; + if (this.isBuiltInName()) { + //can send the builtin name directly in + data [19 + offset] = this.getBuiltInName(); + } else { + StringUtil.putCompressedUnicode( getNameText(), data, 19 + offset ); + + } - StringUtil.putCompressedUnicode( getNameText(), data, 19 + offset ); - int start_of_name_definition = 19 + field_3_length_name_text; - if ( this.field_13_name_definition != null ) - { - serializePtgs( data, start_of_name_definition + offset ); - } - else - { - System.arraycopy( field_13_raw_name_definition, 0, data - , start_of_name_definition + offset, field_13_raw_name_definition.length ); - } + if ( this.field_13_name_definition != null ) + { + serializePtgs( data, start_of_name_definition + offset ); + } + else + { + System.arraycopy( field_13_raw_name_definition, 0, data + , start_of_name_definition + offset, field_13_raw_name_definition.length ); + } + int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition; StringUtil.putCompressedUnicode( getCustomMenuText(), data, start_of_custom_menu_text + offset ); @@ -617,7 +722,7 @@ public class NameRecord extends Record { field_8_length_description_text = data [11 + offset]; field_9_length_help_topic_text = data [12 + offset]; field_10_length_status_bar_text = data [13 + offset]; - + /* temp: gjs @@ -644,6 +749,13 @@ public class NameRecord extends Record { else { */ field_11_compressed_unicode_flag= data [14 + offset]; + + + //store the name in byte form if it's a builtin name + if (this.isBuiltInName()) { + field_12_builtIn_name = data[ 15 + offset ]; + } + field_12_name_text = new String(data, 15 + offset, LittleEndian.ubyteToInt(field_3_length_name_text)); @@ -779,11 +891,12 @@ public class NameRecord extends Record { .append("\n"); buffer.append(" .Name (Unicode flag) = ").append( field_11_compressed_unicode_flag ) .append("\n"); - buffer.append(" .Name (Unicode text) = ").append( field_12_name_text ) + buffer.append(" .Name (Unicode text) = ").append( getNameText() ) .append("\n"); buffer.append(" .Formula data (RPN token array without size field) = ").append( HexDump.toHex( ((field_13_raw_name_definition != null) ? field_13_raw_name_definition : new byte[0] ) ) ) .append("\n"); + buffer.append(" .Menu text (Unicode string without length field) = ").append( field_14_custom_menu_text ) .append("\n"); buffer.append(" .Description text (Unicode string without length field) = ").append( field_15_description_text ) @@ -797,4 +910,30 @@ public class NameRecord extends Record { return buffer.toString(); } + /**Creates a human readable name for built in types + * @return Unknown if the built-in name cannot be translated + */ + protected String translateBuiltInName(byte name) + { + switch (name) + { + case NameRecord.BUILTIN_AUTO_ACTIVATE : return "Auto_Activate"; + case NameRecord.BUILTIN_AUTO_CLOSE : return "Auto_Close"; + case NameRecord.BUILTIN_AUTO_DEACTIVATE : return "Auto_Deactivate"; + case NameRecord.BUILTIN_AUTO_OPEN : return "Auto_Open"; + case NameRecord.BUILTIN_CONSOLIDATE_AREA : return "Consolidate_Area"; + case NameRecord.BUILTIN_CRITERIA : return "Criteria"; + case NameRecord.BUILTIN_DATABASE : return "Database"; + case NameRecord.BUILTIN_DATA_FORM : return "Data_Form"; + case NameRecord.BUILTIN_PRINT_AREA : return "Print_Area"; + case NameRecord.BUILTIN_PRINT_TITLE : return "Print_Titles"; + case NameRecord.BUILTIN_RECORDER : return "Recorder"; + case NameRecord.BUILTIN_SHEET_TITLE : return "Sheet_Title"; + + } + + return "Unknown"; + } + + } diff --git a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java index a428a0d5fd..541c32a4b5 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java @@ -75,287 +75,280 @@ import org.apache.poi.util.BitField; public class Area3DPtg extends Ptg { - public final static byte sid = 0x3b; - private final static int SIZE = 11; // 10 + 1 for Ptg - private short field_1_index_extern_sheet; - private short field_2_first_row; - private short field_3_last_row; - private short field_4_first_column; - private short field_5_last_column; - - private BitField rowRelative = new BitField( 0x8000 ); - private BitField colRelative = new BitField( 0x4000 ); - - /** Creates new AreaPtg */ - public Area3DPtg() - { - } - - public Area3DPtg( String arearef, short externIdx ) - { - AreaReference ar = new AreaReference( arearef ); - - setFirstRow( (short) ar.getCells()[0].getRow() ); - setFirstColumn( (short) ar.getCells()[0].getCol() ); - setLastRow( (short) ar.getCells()[1].getRow() ); - setLastColumn( (short) ar.getCells()[1].getCol() ); - setFirstColRelative( !ar.getCells()[0].isColAbsolute() ); - setLastColRelative( !ar.getCells()[1].isColAbsolute() ); - setFirstRowRelative( !ar.getCells()[0].isRowAbsolute() ); - setLastRowRelative( !ar.getCells()[1].isRowAbsolute() ); - setExternSheetIndex( externIdx ); - - } - - public Area3DPtg( byte[] data, int offset ) - { - offset++; - field_1_index_extern_sheet = LittleEndian.getShort( data, 0 + offset ); - field_2_first_row = LittleEndian.getShort( data, 2 + offset ); - field_3_last_row = LittleEndian.getShort( data, 4 + offset ); - field_4_first_column = LittleEndian.getShort( data, 6 + offset ); - field_5_last_column = LittleEndian.getShort( data, 8 + offset ); - } - - public String toString() - { - StringBuffer buffer = new StringBuffer(); - - buffer.append( "AreaPtg\n" ); - buffer.append( "Index to Extern Sheet = " + getExternSheetIndex() ).append( "\n" ); - buffer.append( "firstRow = " + getFirstRow() ).append( "\n" ); - buffer.append( "lastRow = " + getLastRow() ).append( "\n" ); - buffer.append( "firstCol = " + getFirstColumn() ).append( "\n" ); - buffer.append( "lastCol = " + getLastColumn() ).append( "\n" ); - buffer.append( "firstColRel= " - + isFirstRowRelative() ).append( "\n" ); - buffer.append( "lastColRowRel = " - + isLastRowRelative() ).append( "\n" ); - buffer.append( "firstColRel = " + isFirstColRelative() ).append( "\n" ); - buffer.append( "lastColRel = " + isLastColRelative() ).append( "\n" ); - return buffer.toString(); - } - - public void writeBytes( byte[] array, int offset ) - { - array[0 + offset] = (byte) ( sid + ptgClass ); - LittleEndian.putShort( array, 1 + offset, getExternSheetIndex() ); - LittleEndian.putShort( array, 3 + offset, getFirstRow() ); - LittleEndian.putShort( array, 5 + offset, getLastRow() ); - LittleEndian.putShort( array, 7 + offset, getFirstColumnRaw() ); - LittleEndian.putShort( array, 9 + offset, getLastColumnRaw() ); - } - - public int getSize() - { - return SIZE; - } - - public short getExternSheetIndex() - { - return field_1_index_extern_sheet; - } - - public void setExternSheetIndex( short index ) - { - field_1_index_extern_sheet = index; - } - - public short getFirstRow() - { - return field_2_first_row; - } - - public void setFirstRow( short row ) - { - field_2_first_row = row; - } - - public short getLastRow() - { - return field_3_last_row; - } - - public void setLastRow( short row ) - { - field_3_last_row = row; - } - - public short getFirstColumn() - { - return (short) ( field_4_first_column & 0xFF ); - } - - public short getFirstColumnRaw() - { - return field_4_first_column; - } - - public boolean isFirstRowRelative() - { - return rowRelative.isSet( field_4_first_column ); - } - - public boolean isFirstColRelative() - { - return colRelative.isSet( field_4_first_column ); - } - - public void setFirstColumn( short column ) - { - field_4_first_column &= 0xFF00; - field_4_first_column |= column & 0xFF; - } - - public void setFirstColumnRaw( short column ) - { - field_4_first_column = column; - } - - public short getLastColumn() - { - return (short) ( field_5_last_column & 0xFF ); - } - - public short getLastColumnRaw() - { - return field_5_last_column; - } - - public boolean isLastRowRelative() - { - return rowRelative.isSet( field_5_last_column ); - } - - public boolean isLastColRelative() - { - return colRelative.isSet( field_5_last_column ); - } - - public void setLastColumn( short column ) - { - field_5_last_column &= 0xFF00; - field_5_last_column |= column & 0xFF; - } - - public void setLastColumnRaw( short column ) - { - field_5_last_column = column; - } - - /** - * sets the first row to relative or not - * @param isRelative or not. - */ - public void setFirstRowRelative( boolean rel ) - { - field_4_first_column = rowRelative.setShortBoolean( field_4_first_column, rel ); - } - - /** - * set whether the first column is relative - */ - public void setFirstColRelative( boolean rel ) - { - field_4_first_column = colRelative.setShortBoolean( field_4_first_column, rel ); - } - - /** - * set whether the last row is relative or not - * @param last row relative - */ - public void setLastRowRelative( boolean rel ) - { - field_5_last_column = rowRelative.setShortBoolean( field_5_last_column, rel ); - } - - /** - * set whether the last column should be relative or not - */ - public void setLastColRelative( boolean rel ) - { - field_5_last_column = colRelative.setShortBoolean( field_5_last_column, rel ); - } - - - /*public String getArea(){ - RangeAddress ra = new RangeAddress( getFirstColumn(),getFirstRow() + 1, getLastColumn(), getLastRow() + 1); - String result = ra.getAddress(); - - return result; - }*/ - - public void setArea( String ref ) - { - RangeAddress ra = new RangeAddress( ref ); - - String from = ra.getFromCell(); - String to = ra.getToCell(); - - setFirstColumn( (short) ( ra.getXPosition( from ) - 1 ) ); - setFirstRow( (short) ( ra.getYPosition( from ) - 1 ) ); - setLastColumn( (short) ( ra.getXPosition( to ) - 1 ) ); - setLastRow( (short) ( ra.getYPosition( to ) - 1 ) ); - - } - - public String toFormulaString( SheetReferences refs ) - { - StringBuffer retval = new StringBuffer(); - if ( refs != null ) - { - retval.append( refs.getSheetName( this.field_1_index_extern_sheet ) ); - retval.append( '!' ); - } - retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).toString() ); - retval.append( ':' ); - retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).toString() ); - return retval.toString(); - } - - public byte getDefaultOperandClass() - { - return Ptg.CLASS_REF; - } - - public Object clone() - { - Area3DPtg ptg = new Area3DPtg(); - ptg.field_1_index_extern_sheet = field_1_index_extern_sheet; - ptg.field_2_first_row = field_2_first_row; - ptg.field_3_last_row = field_3_last_row; - ptg.field_4_first_column = field_4_first_column; - ptg.field_5_last_column = field_5_last_column; - return ptg; - } - - - public boolean equals( Object o ) - { - if ( this == o ) return true; - if ( !( o instanceof Area3DPtg ) ) return false; - - final Area3DPtg area3DPtg = (Area3DPtg) o; - - if ( field_1_index_extern_sheet != area3DPtg.field_1_index_extern_sheet ) return false; - if ( field_2_first_row != area3DPtg.field_2_first_row ) return false; - if ( field_3_last_row != area3DPtg.field_3_last_row ) return false; - if ( field_4_first_column != area3DPtg.field_4_first_column ) return false; - if ( field_5_last_column != area3DPtg.field_5_last_column ) return false; - - return true; - } - - public int hashCode() - { - int result; - result = (int) field_1_index_extern_sheet; - result = 29 * result + (int) field_2_first_row; - result = 29 * result + (int) field_3_last_row; - result = 29 * result + (int) field_4_first_column; - result = 29 * result + (int) field_5_last_column; - return result; - } + public final static byte sid = 0x3b; + private final static int SIZE = 11; // 10 + 1 for Ptg + private short field_1_index_extern_sheet; + private short field_2_first_row; + private short field_3_last_row; + private short field_4_first_column; + private short field_5_last_column; + + private BitField rowRelative = new BitField( 0x8000 ); + private BitField colRelative = new BitField( 0x4000 ); + + /** Creates new AreaPtg */ + public Area3DPtg() + { + } + + public Area3DPtg( String arearef, short externIdx ) + { + setArea(arearef); + setExternSheetIndex( externIdx ); + + } + + public Area3DPtg( byte[] data, int offset ) + { + offset++; + field_1_index_extern_sheet = LittleEndian.getShort( data, 0 + offset ); + field_2_first_row = LittleEndian.getShort( data, 2 + offset ); + field_3_last_row = LittleEndian.getShort( data, 4 + offset ); + field_4_first_column = LittleEndian.getShort( data, 6 + offset ); + field_5_last_column = LittleEndian.getShort( data, 8 + offset ); + } + + public String toString() + { + StringBuffer buffer = new StringBuffer(); + + buffer.append( "AreaPtg\n" ); + buffer.append( "Index to Extern Sheet = " + getExternSheetIndex() ).append( "\n" ); + buffer.append( "firstRow = " + getFirstRow() ).append( "\n" ); + buffer.append( "lastRow = " + getLastRow() ).append( "\n" ); + buffer.append( "firstCol = " + getFirstColumn() ).append( "\n" ); + buffer.append( "lastCol = " + getLastColumn() ).append( "\n" ); + buffer.append( "firstColRel= " + + isFirstRowRelative() ).append( "\n" ); + buffer.append( "lastColRowRel = " + + isLastRowRelative() ).append( "\n" ); + buffer.append( "firstColRel = " + isFirstColRelative() ).append( "\n" ); + buffer.append( "lastColRel = " + isLastColRelative() ).append( "\n" ); + return buffer.toString(); + } + + public void writeBytes( byte[] array, int offset ) + { + array[0 + offset] = (byte) ( sid + ptgClass ); + LittleEndian.putShort( array, 1 + offset, getExternSheetIndex() ); + LittleEndian.putShort( array, 3 + offset, getFirstRow() ); + LittleEndian.putShort( array, 5 + offset, getLastRow() ); + LittleEndian.putShort( array, 7 + offset, getFirstColumnRaw() ); + LittleEndian.putShort( array, 9 + offset, getLastColumnRaw() ); + } + + public int getSize() + { + return SIZE; + } + + public short getExternSheetIndex() + { + return field_1_index_extern_sheet; + } + + public void setExternSheetIndex( short index ) + { + field_1_index_extern_sheet = index; + } + + public short getFirstRow() + { + return field_2_first_row; + } + + public void setFirstRow( short row ) + { + field_2_first_row = row; + } + + public short getLastRow() + { + return field_3_last_row; + } + + public void setLastRow( short row ) + { + field_3_last_row = row; + } + + public short getFirstColumn() + { + return (short) ( field_4_first_column & 0xFF ); + } + + public short getFirstColumnRaw() + { + return field_4_first_column; + } + + public boolean isFirstRowRelative() + { + return rowRelative.isSet( field_4_first_column ); + } + + public boolean isFirstColRelative() + { + return colRelative.isSet( field_4_first_column ); + } + + public void setFirstColumn( short column ) + { + field_4_first_column &= 0xFF00; + field_4_first_column |= column & 0xFF; + } + + public void setFirstColumnRaw( short column ) + { + field_4_first_column = column; + } + + public short getLastColumn() + { + return (short) ( field_5_last_column & 0xFF ); + } + + public short getLastColumnRaw() + { + return field_5_last_column; + } + + public boolean isLastRowRelative() + { + return rowRelative.isSet( field_5_last_column ); + } + + public boolean isLastColRelative() + { + return colRelative.isSet( field_5_last_column ); + } + + public void setLastColumn( short column ) + { + field_5_last_column &= 0xFF00; + field_5_last_column |= column & 0xFF; + } + + public void setLastColumnRaw( short column ) + { + field_5_last_column = column; + } + + /** + * sets the first row to relative or not + * @param isRelative or not. + */ + public void setFirstRowRelative( boolean rel ) + { + field_4_first_column = rowRelative.setShortBoolean( field_4_first_column, rel ); + } + + /** + * set whether the first column is relative + */ + public void setFirstColRelative( boolean rel ) + { + field_4_first_column = colRelative.setShortBoolean( field_4_first_column, rel ); + } + + /** + * set whether the last row is relative or not + * @param last row relative + */ + public void setLastRowRelative( boolean rel ) + { + field_5_last_column = rowRelative.setShortBoolean( field_5_last_column, rel ); + } + + /** + * set whether the last column should be relative or not + */ + public void setLastColRelative( boolean rel ) + { + field_5_last_column = colRelative.setShortBoolean( field_5_last_column, rel ); + } + + + /*public String getArea(){ + RangeAddress ra = new RangeAddress( getFirstColumn(),getFirstRow() + 1, getLastColumn(), getLastRow() + 1); + String result = ra.getAddress(); + + return result; + }*/ + + public void setArea( String ref ) + { + AreaReference ar = new AreaReference( ref ); + + setFirstRow( (short) ar.getCells()[0].getRow() ); + setFirstColumn( (short) ar.getCells()[0].getCol() ); + setLastRow( (short) ar.getCells()[1].getRow() ); + setLastColumn( (short) ar.getCells()[1].getCol() ); + setFirstColRelative( !ar.getCells()[0].isColAbsolute() ); + setLastColRelative( !ar.getCells()[1].isColAbsolute() ); + setFirstRowRelative( !ar.getCells()[0].isRowAbsolute() ); + setLastRowRelative( !ar.getCells()[1].isRowAbsolute() ); + + } + + public String toFormulaString( SheetReferences refs ) + { + StringBuffer retval = new StringBuffer(); + if ( refs != null ) + { + retval.append( refs.getSheetName( this.field_1_index_extern_sheet ) ); + retval.append( '!' ); + } + retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).toString() ); + retval.append( ':' ); + retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).toString() ); + return retval.toString(); + } + + public byte getDefaultOperandClass() + { + return Ptg.CLASS_REF; + } + + public Object clone() + { + Area3DPtg ptg = new Area3DPtg(); + ptg.field_1_index_extern_sheet = field_1_index_extern_sheet; + ptg.field_2_first_row = field_2_first_row; + ptg.field_3_last_row = field_3_last_row; + ptg.field_4_first_column = field_4_first_column; + ptg.field_5_last_column = field_5_last_column; + return ptg; + } + + + public boolean equals( Object o ) + { + if ( this == o ) return true; + if ( !( o instanceof Area3DPtg ) ) return false; + + final Area3DPtg area3DPtg = (Area3DPtg) o; + + if ( field_1_index_extern_sheet != area3DPtg.field_1_index_extern_sheet ) return false; + if ( field_2_first_row != area3DPtg.field_2_first_row ) return false; + if ( field_3_last_row != area3DPtg.field_3_last_row ) return false; + if ( field_4_first_column != area3DPtg.field_4_first_column ) return false; + if ( field_5_last_column != area3DPtg.field_5_last_column ) return false; + + return true; + } + + public int hashCode() + { + int result; + result = (int) field_1_index_extern_sheet; + result = 29 * result + (int) field_2_first_row; + result = 29 * result + (int) field_3_last_row; + result = 29 * result + (int) field_4_first_column; + result = 29 * result + (int) field_5_last_column; + return result; + } } + diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java index 3aa50bc3f4..6e2c38ed9b 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java @@ -161,11 +161,8 @@ public class HSSFName { setSheetName(sheetName); } - if (ra.getFromCell().equals(ra.getToCell()) == false) { - name.setAreaReference(ra.getFromCell() + ":" + ra.getToCell()); - } else { - name.setAreaReference(ra.getFromCell()); - } + //allow the poi utilities to parse it out + name.setAreaReference(ref); } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 914e9947f4..e76f9c0147 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -523,21 +523,14 @@ public class HSSFWorkbook } if ( nameRecord == null ) { - nameRecord = workbook.createName(); + nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, externSheetIndex+1); + //does a lot of the house keeping for builtin records, like setting lengths to zero etc isNewRecord = true; } - nameRecord.setOptionFlag((short)0x20); - nameRecord.setKeyboardShortcut((byte)0); + short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b; nameRecord.setDefinitionTextLength(definitionTextLength); - nameRecord.setNameTextLength((byte)1); - nameRecord.setNameText(((char)7) + ""); - nameRecord.setUnused((short)0); - nameRecord.setEqualsToIndexToSheet((short)(externSheetIndex+1)); - nameRecord.setCustomMenuLength((byte)0); - nameRecord.setDescriptionTextLength((byte)0); - nameRecord.setHelpTopicLength((byte)0); - nameRecord.setStatusBarLength((byte)0); + Stack ptgs = new Stack(); if (settingRowAndColumn) @@ -820,6 +813,43 @@ public class HSSFWorkbook return result; } + /** + * Sets the printarea for the sheet provided + *

+ * i.e. Reference = Sheet2!$A$1:$B$2 + * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java) + * @param reference Valid name Reference for the Print Area + */ + public void setPrintArea(int sheetIndex, String reference) + { + NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1); + + + if (name == null) + name = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1); + //adding one here because 0 indicates a global named region; doesnt make sense for print areas + + HSSFName nameWrapper = new HSSFName(workbook, name); + //the external name does some housekeeping, refactor to lower level? + + nameWrapper.setReference(reference); + } + + + /** + * Retrieves the reference for the printarea of the specified sheet + * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java) + * @return String Null if no print area has been defined + */ + public String getPrintArea(int sheetIndex) + { + NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1); + if (name == null) return null; + //adding one here because 0 indicates a global named region; doesnt make sense for print areas + + return name.getAreaReference(workbook.getSheetReferences()); + } + /** creates a new named range and add it to the model * @return named range high level diff --git a/src/java/org/apache/poi/hssf/util/RangeAddress.java b/src/java/org/apache/poi/hssf/util/RangeAddress.java index 05d5877d63..33841262ca 100644 --- a/src/java/org/apache/poi/hssf/util/RangeAddress.java +++ b/src/java/org/apache/poi/hssf/util/RangeAddress.java @@ -62,7 +62,7 @@ package org.apache.poi.hssf.util; * * REFERENCE:

* @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.) - * @version 1.0 +@version 1.0 */ public class RangeAddress { @@ -72,7 +72,13 @@ public class RangeAddress { String m_sheetName; String m_cellFrom; String m_cellTo; - + + /** + * Accepts an external reference from excel. + *

+ * i.e. Sheet1!$A$4:$B$9 + * @param _url + */ public RangeAddress (String _url) { init (_url); } @@ -82,10 +88,14 @@ public class RangeAddress { + numTo26Sys (_endCol) + _endRow); } + /** + * + * @return String note: All absolute references are removed + */ public String getAddress (){ String result = ""; if(m_sheetName != null) - result += m_sheetName; + result += m_sheetName+"!"; if(m_cellFrom != null){ result += m_cellFrom; if(m_cellTo != null) @@ -94,6 +104,7 @@ public class RangeAddress { return result; } + public String getSheetName (){ return m_sheetName; } @@ -231,21 +242,21 @@ public class RangeAddress { if (index >= 0) { String fromStr = _url.substring(0, index); String toStr = _url.substring(index+1); - index = fromStr.indexOf('.'); + index = fromStr.indexOf('!'); if (index >= 0) { result[0] = fromStr.substring(0, index); result[1] = fromStr.substring(index+1); } else { result[1] = fromStr; } - index = toStr.indexOf('.'); + index = toStr.indexOf('!'); if (index >= 0) { result[2] = toStr.substring(index+1); } else { result[2] = toStr; } } else { - index = _url.indexOf('.'); + index = _url.indexOf('!'); if (index >= 0) { result[0] = _url.substring(0, index); result[1] = _url.substring(index+1); diff --git a/src/testcases/org/apache/poi/hssf/HSSFTests.java b/src/testcases/org/apache/poi/hssf/HSSFTests.java index 6882169e66..ca43bfdb3a 100644 --- a/src/testcases/org/apache/poi/hssf/HSSFTests.java +++ b/src/testcases/org/apache/poi/hssf/HSSFTests.java @@ -63,6 +63,7 @@ import org.apache.poi.hssf.usermodel.TestWorkbook; import org.apache.poi.hssf.util.TestAreaReference; import org.apache.poi.hssf.util.TestCellReference; import org.apache.poi.hssf.util.TestRKUtil; +import org.apache.poi.hssf.util.TestRangeAddress; import org.apache.poi.hssf.util.TestSheetReferences; /** @@ -144,6 +145,7 @@ public class HSSFTests suite.addTest(new TestSuite(TestRowRecordsAggregate.class)); suite.addTest(new TestSuite(TestAreaReference.class)); suite.addTest(new TestSuite(TestCellReference.class)); + suite.addTest(new TestSuite(TestRangeAddress.class)); suite.addTest(new TestSuite(TestRKUtil.class)); suite.addTest(new TestSuite(TestSheetReferences.class)); diff --git a/src/testcases/org/apache/poi/hssf/data/SimpleWithPrintArea.xls b/src/testcases/org/apache/poi/hssf/data/SimpleWithPrintArea.xls new file mode 100755 index 0000000000000000000000000000000000000000..0434dbcc8249ee9ac6f74c16ec820e6d71dd71e1 GIT binary patch literal 18432 zcmeHPS!@$m6umQy9VZZQ680qw21F846fm2JAf|;15U2=fLBdsPz$O+DBgd7(SKWob zR;5r;SxQy$*QyfGU22gq{i0P+DjGk;>yRKFCKu7VP-o{6dz8Y{GKY|1l0j+AX|>@-&cv$h zK|G$O$?R$+u9Nr-Y>CcEyqapM8^77DJLau~me6*W84$a-B=$)>9|`y@f0H8Jm1{rxpQ0E8(V`7st|0fx8~qjoLNx6 zds}$li-kr$s-RgpqeU%+{h-WUEg%p1imi%z%bKpBCR#$&&Qoc!)y%TRV1p=vjjdUQ z?l=sAHxHq28bV(=K!3ut$eSFK;j|{{mb5l$Nm`ezkd%@n>BOWf^AgS#1|7v2 z=vSN)alWMO9pP|dF>Ai?~>dg5iM>C081Bmu8Czn_%uo_$Cs;GEB80@{S$xQ~RiLvfVC)@>VK z=))!UVTY;Gp?3lQuxB7nSp7X?j9^-0c-#2~jrUzS;S??o2KK#!rU?{P>%KN8*Vksy z^XM1*+8i$`M%h_K9@E)eXKYqZpnI|g344UYPdI9Pl3Qce(wB0J8IZCQ&4L~8F$C@w zi^%+4ZbVn0H1uLFMEMX7z0q693>hzV2QoQkv!D$P85(4Rz##d6QXwsZfosZ#ApyMp zOBmGlJVF@M5IWTeXaqC@8i6tpsPy0(?s8H4tV{S8D#@B*+cFz&W-m}nnyNn6eh{F7xN`|tK*h(?jBgfcMt;|2LHxM zFtON4ur?kI9bAn2@rxKQ&w21b`g&_RI|CH^AK(g@jk}?ld9_8uB zKsa!{H6Jkg3T=vd${H?XC%}Wa0GinaP=*6wH9G+E2Xba!I{jbD^uK>V)p}&|@7y~w z2~XR+Vu<=ccX$axcWWda-|7|H4W{FMi|rm)9TWIMT!g&eF4PHeFVYEVFVYENFVYEF z@7I+})Q{uj<+%RfjTIWCB0f8a2AL$opN*18lUzV1olYZ3QYw|9ls^Kub}#k9K7wBc zdWxXj%j7C4eQ}>KLWzH@<`_!YaJyc#&ty`RLOGK{Tl9P5#trgEGUV^=g&x){DM$p} zM^be13yN#Zc)GuQB9vZZU|BM9RdXt zw^z>kW3zmXPnTY(cR?ODV^E%t;XDD`3|wo1v3RI+e>k{dtGzkY6^?HTC+wvy%UbO1 z;dnGGOuuJjrXNlu!g1aUPPiPiS}H9kKTj#kPM=&RuP&;%w3>?5^D4m(5gClpTPA$b z2orG08_Wa69Dk`;41SqiV^(FEKN}TMUF<7Vv(qYnnX)U(340#_w;y?lIvn3&_y+NM zG6&)K3e4OcNHp z*2f^#kRC`K@fwp4Ra7H%0*Uw7l-^`&Yj;vd<4#qk~33BcKt`2xtT}0vZ90fJQ(g zpb^jrXaxTE2n@&n%`2C#^emd;J@Fp+fAgKw%>S9gGcRZU&yUymjlKYc-&@@bVUE57 z!n{8SVL!l8NIvi9wgleKZ8)g39*vo|vwy*Pnw85g$z}aC4u;$zI|aD$&RG0F7r%zv zCG9o$2PQgb1T+E~0gZr0KqH_L&tjgpXX6ZQ+`)P992km%9qCnXwL)A1OI`-;A2Q(z+IOV*inYy z!k5f1(;@sqg8fG9g^0|FH3|IRi5N6PhX#z#i1V>q>h{af{o}902zzA?VOas?&1dBU c;4j}UXU89U%Sx|N*q6HfrT1K#<>Ng5KOBe%!vFvP literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/data/namedinput.xls b/src/testcases/org/apache/poi/hssf/data/namedinput.xls new file mode 100755 index 0000000000000000000000000000000000000000..115fcd5908ab1302732fef38f14f1cd93f951c24 GIT binary patch literal 13824 zcmeHOZ)jUp6hANjnyg)yw)@k~t;Te+B(0@u!4=EasGGPC)6ps$SczGi+R`=|X@m|0 zQ{6rY6DAY##o<(qxA3?)%RJ<3XC_2gtv#=JKNj46Vd+Rh;uL;ZS&lRdvR+; z{WF89(Pt}_!cr&ON=jYbmFK}RPqjn*5?-mb;%r6LT@sTXkpx|dg&fa{hrsx0dtDyqSJAInR1C{naeDB*g9&lY?i6 zjl+{~ibC@eEIe{6- zqmM?P42`E!xt)~M*U|5yq=%Abf0?BBqU8}QY1Nf+{({d9yE&1j8yT5#M;K;#&gvw(o?@<1-O7dlv z?+4bHVPy_Aiyq#08$hq9_HpHBI6XmwXjwq5l$HkUGV`aQi~hJ8OVwK1<7$?Vbadin zHQV5`I|xYlFi>!OKLZ|-U7%WF_zXjQ7h!Pwv|A!I!=M1fmD@~Vu#3*Mcl$7W3E1x< z49nfQgD@=HFa?8vLBJqj5HJX=J_K%H1;?xLZ)M;3Zm@LXtzWM_IUaoPb-#4%_~5&4 z$j*cKn#=k)i0@{PgZN%K4r&I?f*L@lKx;wspcc?OAigjDghp-1H-ZjKjAS!2nXz2x z(MNybO{ue=v^^b$N9&i%G^??sTUTJ_L4RH~H4j#&6Cug{AH>2&%S^+RI$sk}5 zFbEg~3<3rLgMdN6AYc$M2p9xbH3E#e85%PN=3YL&^VfZM#?<^@2V;GH9LcbmF+1aT zes9URpY=`1x#!O~-}#c0u|C%WSpO%8xk>*`1O_F8Urs`@PtwSJ zjbBpxz8@%zsZW&T@V}1?B-K*{_IVHS{;AbIzm?GiGGt!a3kxIaLrO>B_WbGl2mRNs H7yJJg=219D literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java index 5953c7d481..f81bd5a765 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java @@ -232,7 +232,7 @@ extends TestCase { throws java.io.IOException { String readFilename = System.getProperty("HSSF.testdata.path"); - File file = File.createTempFile("testBoolErr",".xls"); + File file = File.createTempFile("testFormulaStyle",".xls"); FileOutputStream out = new FileOutputStream(file); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("Sheet1"); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java index 3f76ec7ba3..d7f4f5f444 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java @@ -1,27 +1,75 @@ -/* - * RangeTestTest.java - * NetBeans JUnit based test + +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 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 apache@apache.org. + * + * 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. * - * Created on April 21, 2002, 6:23 PM + * 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 + * . */ package org.apache.poi.hssf.usermodel; -import junit.framework.*; - -import org.apache.poi.poifs.filesystem.POIFSFileSystem; - import java.io.File; import java.io.FileInputStream; -import java.io.FileNotFoundException; 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 { @@ -67,9 +115,9 @@ public class TestNamedRange filename = filename + "/Simple.xls"; - fis = new FileInputStream(filename); - fs = new POIFSFileSystem(fis); - wb = new HSSFWorkbook(fs); + fis = new FileInputStream(filename); + fs = new POIFSFileSystem(fis); + wb = new HSSFWorkbook(fs); //Creating new Named Range @@ -81,7 +129,7 @@ public class TestNamedRange //Setting its name newNamedRange.setNameName("RangeTest"); //Setting its reference - newNamedRange.setReference(sheetName + ".$D$4:$E$8"); + newNamedRange.setReference(sheetName + "!$D$4:$E$8"); //Getting NAmed Range HSSFName namedRange1 = wb.getNameAt(0); @@ -108,10 +156,336 @@ public class TestNamedRange wb = new HSSFWorkbook(in); 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())); + assertEquals(wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference()); + + } + + /** + * Reads an excel file already containing a named range. + *

+ * Addresses Bug #9632 + */ + 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 + *

+ * Addresses Bug #16411 + */ + 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(); + + + 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())); + 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 czhang@cambian.com (Chun Zhang) + *

+ * Addresses Bug #13775 + * @throws IOException + */ + public void testMultiNamedRange() + throws IOException + { + + // 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 + File file = File.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); + + } + + /** + * 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 = File.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 = File.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); + + + } + } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java b/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java index 845e49b44b..9a0f4f8da5 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java @@ -55,19 +55,21 @@ package org.apache.poi.hssf.usermodel; -import java.io.*; +import java.io.File; +import java.io.FileInputStream; +import java.io.FileOutputStream; +import java.io.IOException; +import java.util.Iterator; -import java.util.*; +import junit.framework.TestCase; -import junit.framework.*; - -import org.apache.poi.poifs.filesystem.POIFSFileSystem; -import org.apache.poi.hssf.util.*; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.BackupRecord; import org.apache.poi.hssf.record.LabelSSTRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate; +import org.apache.poi.hssf.util.Region; +import org.apache.poi.poifs.filesystem.POIFSFileSystem; /** * Class to test Workbook functionality @@ -720,6 +722,35 @@ public class TestWorkbook file.deleteOnExit(); } + /** + * Generate a file to visually/programmatically verify repeating rows and cols made it + */ + public void testRepeatingColsRows() throws IOException + { + HSSFWorkbook workbook = new HSSFWorkbook(); + HSSFSheet sheet = workbook.createSheet("Test Print Titles"); + String sheetName = workbook.getSheetName(0); + + HSSFRow row = sheet.createRow(0); + + HSSFCell cell = row.createCell((short)1); + cell.setCellValue("hi"); + + + workbook.setRepeatingRowsAndColumns(0, 0, 1, 0, 0); + + File file = File.createTempFile("testPrintTitles",".xls"); + + FileOutputStream fileOut = new FileOutputStream(file); + workbook.write(fileOut); + fileOut.close(); + + assertTrue("file exists",file.exists()); + + + } + + public static void main(String [] ignored_args) { String filename = System.getProperty("HSSF.testdata.path"); diff --git a/src/testcases/org/apache/poi/hssf/util/TestRangeAddress.java b/src/testcases/org/apache/poi/hssf/util/TestRangeAddress.java new file mode 100755 index 0000000000..9c9280d6d6 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/util/TestRangeAddress.java @@ -0,0 +1,81 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 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 apache@apache.org. + * + * 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 + * . + */ + +package org.apache.poi.hssf.util; + +import junit.framework.TestCase; + +/** + * Tests the Range Address Utility Functionality + * @author Danny Mui (danny at muibros.com) + */ +public class TestRangeAddress extends TestCase { + public TestRangeAddress(String s) { + super(s); + } + + public static void main(java.lang.String[] args) { + junit.textui.TestRunner.run(TestRangeAddress.class); + } + + + + public void testReferenceParse() { + String reference = "Sheet2!$A$1:$C$3"; + RangeAddress ra = new RangeAddress(reference); + + assertEquals("Sheet2!A1:C3", ra.getAddress()); + + } +} \ No newline at end of file -- 2.39.5