Attachment #4631 for bug #16557

View | Details | Raw Unified | Return to bug 16557
Collapse All | Expand All

Lines 136-142 Link Here

(-)src/java/org/apache/poi/hssf/model/Workbook.java (-12 / +76 lines)
136	protected ArrayList formats = new ArrayList();	136	protected ArrayList formats = new ArrayList();
137		137
138	protected ArrayList names = new ArrayList();	138	protected ArrayList names = new ArrayList();
139		139
140	protected int protpos = 0; // holds the position of the protect record.	140	protected int protpos = 0; // holds the position of the protect record.
141	protected int bspos = 0; // holds the position of the last bound sheet.	141	protected int bspos = 0; // holds the position of the last bound sheet.
142	protected int tabpos = 0; // holds the position of the tabid record	142	protected int tabpos = 0; // holds the position of the tabid record
Lines 147-152 Link Here
147	private int backuppos = 0; // holds the position of the backup record.	147	private int backuppos = 0; // holds the position of the backup record.
148	private int namepos = 0; // holds the position of last name record	148	private int namepos = 0; // holds the position of last name record
149	private int supbookpos = 0; // holds the position of sup book	149	private int supbookpos = 0; // holds the position of sup book
		150	private int externsheetpos = 0; //holds the position of the last external sheet representation
150	private int palettepos = 0; // hold the position of the palette, if applicable	151	private int palettepos = 0; // hold the position of the palette, if applicable
151	private short maxformatid = -1; // holds the max format id	152	private short maxformatid = -1; // holds the max format id
152	private boolean uses1904datewindowing = false; // whether 1904 date windowing is being used	153	private boolean uses1904datewindowing = false; // whether 1904 date windowing is being used
Lines 342-349 Link Here
342	records.add( bsr );	343	records.add( bsr );
343	retval.boundsheets.add( bsr );	344	retval.boundsheets.add( bsr );
344	retval.bspos = records.size() - 1;	345	retval.bspos = records.size() - 1;
345	}	346	}
		347	//slot the external sheet references here
		348	retval.externsheetpos = records.size() -1;
346	records.add( retval.createCountry() );	349	records.add( retval.createCountry() );
		350
		351	retval.namepos = records.size()-1;
		352
347	retval.sst = (SSTRecord) retval.createSST();	353	retval.sst = (SSTRecord) retval.createSST();
348	records.add( retval.sst );	354	records.add( retval.sst );
349	records.add( retval.createExtendedSST() );	355	records.add( retval.createExtendedSST() );
Lines 494-499 Link Here
494	/**	500	/**
495	* if we're trying to address one more sheet than we have, go ahead and add it! if we're	501	* if we're trying to address one more sheet than we have, go ahead and add it! if we're
496	* trying to address >1 more than we have throw an exception!	502	* trying to address >1 more than we have throw an exception!
		503	* <P>
		504	* Since the extern sheets and names should come after bound sheets, advance those pointers too.
497	*/	505	*/
498		506
499	private void checkSheets(int sheetnum) {	507	private void checkSheets(int sheetnum) {
Lines 505-510 Link Here
505	( BoundSheetRecord ) createBoundSheet(sheetnum);	513	( BoundSheetRecord ) createBoundSheet(sheetnum);
506		514
507	records.add(++bspos, bsr);	515	records.add(++bspos, bsr);
		516	this.externsheetpos++;
		517	this.namepos++;
		518
508	boundsheets.add(bsr);	519	boundsheets.add(bsr);
509	fixTabIdRecord();	520	fixTabIdRecord();
510	}	521	}
Lines 1809-1814 Link Here
1809	return name;	1820	return name;
1810	}	1821	}
1811		1822
		1823	/**Generates a NameRecord to represent a built-in region
		1824	* @return a new NameRecord unless the index is invalid
		1825	*/
		1826	public NameRecord createBuiltInName(byte builtInName, int index)
		1827	{
		1828	if (index == -1 \|\| index+1 > (int)Short.MAX_VALUE) return null;
		1829
		1830	//for sheet specific stuff, the index is one-based
		1831	NameRecord name = new NameRecord(builtInName, (short)(index+1));
		1832
		1833	records.add(++namepos, name);
		1834	names.add(name);
		1835
		1836	return name;
		1837	}
		1838
		1839
1812	/** removes the name	1840	/** removes the name
1813	* @param namenum name index	1841	* @param namenum name index
1814	*/	1842	*/
Lines 1822-1841 Link Here
1822	}	1850	}
1823		1851
1824	/** creates a new extern sheet record	1852	/** creates a new extern sheet record
		1853	* <p>
		1854	* Since external workbook references are not supported, only one
		1855	* supbookrecord will be maintained through this function.
		1856	* <p>
		1857	* Need to slot the extern sheets after the bound sheets
1825	* @return the new extern sheet record	1858	* @return the new extern sheet record
1826	*/	1859	*/
1827	protected ExternSheetRecord createExternSheet(){	1860	protected ExternSheetRecord createExternSheet(){
1828	ExternSheetRecord rec = new ExternSheetRecord();
1829
1830	records.add(supbookpos + 1 , rec);
1831
1832	//We also adds the supBook for internal reference	1861	//We also adds the supBook for internal reference
1833	SupBookRecord supbook = new SupBookRecord();	1862	SupBookRecord supbook = null;
1834		1863
1835	supbook.setNumberOfSheets((short)getNumSheets());	1864	//only do this once
1836	//supbook.setFlag();	1865	if (!(records.get(supbookpos) instanceof SupBookRecord))
1837		1866	{
1838	records.add(supbookpos + 1 , supbook);	1867	supbook = new SupBookRecord();
		1868	supbook.setNumberOfSheets((short)getNumSheets());
		1869	//supbook.setFlag();
		1870	records.add(bspos + 1 , supbook);
		1871	this.supbookpos = bspos+1;
		1872
		1873	//names and extern sheets go after sup book records
		1874	this.externsheetpos = supbookpos+1;
		1875	this.namepos++;
		1876
		1877	}
		1878
		1879	ExternSheetRecord rec = new ExternSheetRecord();
		1880	records.add(++this.externsheetpos , rec);
1839		1881
1840	return rec;	1882	return rec;
1841	}	1883	}
Lines 1949-1954 Link Here
1949	return null;	1991	return null;
1950	}	1992	}
1951		1993
		1994	/**Retrieves the Builtin NameRecord that matches the name and index
		1995	* There shouldn't be too many names to make the sequential search too slow
		1996	* @param name byte representation of the builtin name to match
		1997	* @param sheetIndex zero-based sheet reference
		1998	* @return null if no builtin NameRecord matches
		1999	*/
		2000	public NameRecord getSpecificBuiltinRecord(byte name, int sheetIndex)
		2001	{
		2002	Iterator iterator = names.iterator();
		2003	while (iterator.hasNext()) {
		2004	NameRecord record = ( NameRecord ) iterator.next();
		2005
		2006	//print areas are one based
		2007	if (record.getBuiltInName() == name && record.getIndexToSheet() == sheetIndex+1) {
		2008	return record;
		2009	}
		2010	}
		2011
		2012	return null;
		2013
		2014	}
		2015
1952	public List getRecords()	2016	public List getRecords()
1953	{	2017	{
1954	return records;	2018	return records;

Lines 55-63 Link Here

(-)src/java/org/apache/poi/hssf/record/NameRecord.java (-55 / +215 lines)
55		55
56	package org.apache.poi.hssf.record;	56	package org.apache.poi.hssf.record;
57		57
		58
58	import org.apache.poi.util.HexDump;	59	import org.apache.poi.util.HexDump;
59	import org.apache.poi.util.LittleEndian;	60	import org.apache.poi.util.LittleEndian;
60	import org.apache.poi.util.StringUtil;	61	import org.apache.poi.util.StringUtil;
		62	import org.apache.poi.util.POILogFactory;
		63	import org.apache.poi.util.POILogger;
61	import java.util.Stack;	64	import java.util.Stack;
62	import org.apache.poi.hssf.record.formula.Ptg;	65	import org.apache.poi.hssf.record.formula.Ptg;
63	import org.apache.poi.hssf.record.formula.Area3DPtg;	66	import org.apache.poi.hssf.record.formula.Area3DPtg;
Lines 79-84 Link Here
79	/**	82	/**
80	*/	83	*/
81	public final static short sid = 0x18; //Docs says that it is 0x218	84	public final static short sid = 0x18; //Docs says that it is 0x218
		85
		86	/**Included for completeness sake, not tested
		87	*/
		88	public final static byte BUILTIN_CONSOLIDATE_AREA = (byte)1;
		89
		90	/**Included for completeness sake, not tested
		91	*/
		92	public final static byte BUILTIN_AUTO_OPEN = (byte)2;
		93
		94	/**Included for completeness sake, not tested
		95	*/
		96	public final static byte BUILTIN_AUTO_CLOSE = (byte)3;
		97
		98	/**Included for completeness sake, not tested
		99	*/
		100	public final static byte BUILTIN_DATABASE = (byte)4;
		101
		102	/**Included for completeness sake, not tested
		103	*/
		104	public final static byte BUILTIN_CRITERIA = (byte)5;
		105
		106	public final static byte BUILTIN_PRINT_AREA = (byte)6;
		107	public final static byte BUILTIN_PRINT_TITLE = (byte)7;
		108
		109	/**Included for completeness sake, not tested
		110	*/
		111	public final static byte BUILTIN_RECORDER = (byte)8;
		112
		113	/**Included for completeness sake, not tested
		114	*/
		115	public final static byte BUILTIN_DATA_FORM = (byte)9;
		116
		117	/**Included for completeness sake, not tested
		118	*/
		119
		120	public final static byte BUILTIN_AUTO_ACTIVATE = (byte)10;
		121
		122	/**Included for completeness sake, not tested
		123	*/
		124
		125	public final static byte BUILTIN_AUTO_DEACTIVATE = (byte)11;
		126
		127	/**Included for completeness sake, not tested
		128	*/
		129	public final static byte BUILTIN_SHEET_TITLE = (byte)12;
		130
		131
		132
		133	private static POILogger log = POILogFactory.getLogger(NameRecord.class);
		134
82	private short field_1_option_flag;	135	private short field_1_option_flag;
83	private byte field_2_keyboard_shortcut;	136	private byte field_2_keyboard_shortcut;
84	private byte field_3_length_name_text;	137	private byte field_3_length_name_text;
Lines 110-115 Link Here
110	field_17_status_bar_text = new String();	163	field_17_status_bar_text = new String();
111	}	164	}
112		165
		166	/**Constructor to create a built-in named region
		167	* The sheet indices should be known and protected from intervention.
		168	*/
		169	public NameRecord(byte builtin, short index)
		170	{
		171	super();
		172	this.field_12_builtIn_name = builtin;
		173	this.setOptionFlag((short)(this.getOptionFlag() \| (short)0x20));
		174	this.setNameTextLength((byte)1);
		175	this.setIndexToSheet(index);
		176
		177	}
		178
113	/**	179	/**
114	* Constructs a Name record and sets its fields appropriately.	180	* Constructs a Name record and sets its fields appropriately.
115	*	181	*
Lines 246-251 Link Here
246	field_17_status_bar_text = text;	312	field_17_status_bar_text = text;
247	}	313	}
248		314
		315	/** Gets the Built In Name
		316	* @return the built in Name
		317	*/
		318	public byte getBuiltInName()
		319	{
		320	return this.field_12_builtIn_name;
		321	}
		322
249	/** gets the option flag	323	/** gets the option flag
250	* @return option flag	324	* @return option flag
251	*/	325	*/
Lines 278-284 Link Here
278	* @return index to extern sheet	352	* @return index to extern sheet
279	*/	353	*/
280	public short getIndexToSheet(){	354	public short getIndexToSheet(){
281	return field_5_index_to_sheet;	355	//return field_5_index_to_sheet;
		356	//field 5 is unused, 6 is the real offset
		357	return this.field_6_equals_to_index_to_sheet;
282	}	358	}
283		359
284	/** gets the custom menu length	360	/** gets the custom menu length
Lines 316-326 Link Here
316	return field_11_compressed_unicode_flag;	392	return field_11_compressed_unicode_flag;
317	}	393	}
318		394
		395	/**Creates a human readable name for built in types
		396	* @return Unknown if the built-in name cannot be translated
		397	*/
		398	protected String translateBuiltInName(byte name)
		399	{
		400	switch (name)
		401	{
		402	case NameRecord.BUILTIN_AUTO_ACTIVATE : return "Auto_Activate";
		403	case NameRecord.BUILTIN_AUTO_CLOSE : return "Auto_Close";
		404	case NameRecord.BUILTIN_AUTO_DEACTIVATE : return "Auto_Deactivate";
		405	case NameRecord.BUILTIN_AUTO_OPEN : return "Auto_Open";
		406	case NameRecord.BUILTIN_CONSOLIDATE_AREA : return "Consolidate_Area";
		407	case NameRecord.BUILTIN_CRITERIA : return "Criteria";
		408	case NameRecord.BUILTIN_DATABASE : return "Database";
		409	case NameRecord.BUILTIN_DATA_FORM : return "Data_Form";
		410	case NameRecord.BUILTIN_PRINT_AREA : return "Print_Area";
		411	case NameRecord.BUILTIN_PRINT_TITLE : return "Print_Titles";
		412	case NameRecord.BUILTIN_RECORDER : return "Recorder";
		413	case NameRecord.BUILTIN_SHEET_TITLE : return "Sheet_Title";
		414
		415	}
		416
		417	return "Unknown";
		418	}
		419
319	/** gets the name	420	/** gets the name
320	* @return name	421	* @return name
321	*/	422	*/
322	public String getNameText(){	423	public String getNameText(){
323	return field_12_name_text;	424	return this.isBuiltInName() ? this.translateBuiltInName(this.getBuiltInName()) : field_12_name_text;
324	}	425	}
325		426
326	/** gets the definition, reference (Formula)	427	/** gets the definition, reference (Formula)
Lines 386-422 Link Here
386	data[6 + offset] = getKeyboardShortcut();	487	data[6 + offset] = getKeyboardShortcut();
387	data[7 + offset] = getNameTextLength();	488	data[7 + offset] = getNameTextLength();
388	LittleEndian.putShort(data, 8 + offset, getDefinitionTextLength());	489	LittleEndian.putShort(data, 8 + offset, getDefinitionTextLength());
389	LittleEndian.putShort(data, 10 + offset, getIndexToSheet());	490	//LittleEndian.putShort(data, 10 + offset, getIndexToSheet());
		491	//offset 10 is unused
		492	LittleEndian.putShort(data, 10 + offset, (short)0);
390	LittleEndian.putShort(data, 12 + offset, getIndexToSheet());	493	LittleEndian.putShort(data, 12 + offset, getIndexToSheet());
		494
391	data [14 + offset] = getCustomMenuLength();	495	data [14 + offset] = getCustomMenuLength();
392	data [15 + offset] = getDescriptionTextLength();	496	data [15 + offset] = getDescriptionTextLength();
393	data [16 + offset] = getHelpTopicLength();	497	data [16 + offset] = getHelpTopicLength();
394	data [17 + offset] = getStatusBarLength();	498	data [17 + offset] = getStatusBarLength();
395	data [18 + offset] = getCompressedUnicodeFlag();	499	data [18 + offset] = getCompressedUnicodeFlag();
		500	LittleEndian.putShort(data, 2 + offset, (short)( 15 + getTextsLength() + getReferenceSize()));
396		501
397	if ( ( field_1_option_flag & (short)0x20 ) != 0 ) {	502	this.serializeNameReference(data, 19 + (int)this.getNameTextLength()+offset);
398	LittleEndian.putShort(data, 2 + offset, (short)( 16 + field_13_raw_name_definition.length ));	503
399		504	//no need to put other text into the array and set the name
400	data [19 + offset] = field_12_builtIn_name;	505	if (this.isBuiltInName()) {
401	System.arraycopy( field_13_raw_name_definition, 0, data, 20 + offset, field_13_raw_name_definition.length );	506	data [19 + offset] = this.getBuiltInName();
402		507	}
403	return 20 + field_13_raw_name_definition.length;	508	else
404	}	509	{
405	else {
406	LittleEndian.putShort(data, 2 + offset, (short)( 15 + getTextsLength()));
407
408
409	StringUtil.putCompressedUnicode(getNameText(), data , 19 + offset);	510	StringUtil.putCompressedUnicode(getNameText(), data , 19 + offset);
410		511
411	int start_of_name_definition = 19 + field_3_length_name_text;	512	int start_of_name_definition = 19 + field_3_length_name_text;
412	if (this.field_13_name_definition != null) {
413	serializePtgs(data, start_of_name_definition + offset);
414	} else {
415	System.arraycopy(field_13_raw_name_definition,0,data
416	,start_of_name_definition + offset,field_13_raw_name_definition.length);
417	}
418		513
419	int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition;	514	int start_of_custom_menu_text = start_of_name_definition + getReferenceSize();
420	StringUtil.putCompressedUnicode(getCustomMenuText(), data , start_of_custom_menu_text + offset);	515	StringUtil.putCompressedUnicode(getCustomMenuText(), data , start_of_custom_menu_text + offset);
421		516
422	int start_of_description_text = start_of_custom_menu_text + field_8_length_description_text;	517	int start_of_description_text = start_of_custom_menu_text + field_8_length_description_text;
Lines 427-472 Link Here
427		522
428	int start_of_status_bar_text = start_of_help_topic_text + field_10_length_status_bar_text;	523	int start_of_status_bar_text = start_of_help_topic_text + field_10_length_status_bar_text;
429	StringUtil.putCompressedUnicode(getStatusBarText(), data , start_of_status_bar_text + offset);	524	StringUtil.putCompressedUnicode(getStatusBarText(), data , start_of_status_bar_text + offset);
430		525	}
431	return getRecordSize();	526
432	}	527	return getRecordSize();
433	}	528	}
434		529
435	private void serializePtgs(byte [] data, int offset) {	530	/**Serialize Cell Range References to the array
		531	* @return the amount of bytes written
		532	*/
		533	private int serializePtgs(byte [] data, int offset) {
436	int pos = offset;	534	int pos = offset;
437		535
438	for (int k = 0; k < field_13_name_definition.size(); k++) {	536	for (int k = 0; k < this.getNameDefinition().size(); k++) {
439	Ptg ptg = ( Ptg ) field_13_name_definition.get(k);	537	Ptg ptg = ( Ptg ) this.getNameDefinition().get(k);
440		538
441	ptg.writeBytes(data, pos);	539	ptg.writeBytes(data, pos);
442	pos += ptg.getSize();	540	pos += ptg.getSize();
443	}	541	}
		542	return pos - offset;
444	}	543	}
445		544
446		545	/**Put the name reference into the byte array
		546	* @param data the byte array to place the data of this reference
		547	* @param offset the offset to start writing data
		548	* @return the number of bytes used to serialize the reference
		549	*/
		550	private int serializeNameReference(byte[] data, int offset)
		551	{
		552
		553	if (this.getNameDefinition() != null) {
		554	return this.serializePtgs(data, offset);
		555	}
		556	else
		557	{
		558	//no formula defined, put the raw definition in
		559	if (this.getRawNameDefinitionLength() == 0) {
		560	//no forumla, no raw name definition, what's the point of a name record?
		561	throw new IllegalArgumentException("No Cell Reference Found (PTG or Raw) for sheet indexed "+this.getIndexToSheet());
		562	}
		563	System.arraycopy( this.getRawNameDefinition(), 0, data, 19 + (int)this.getNameTextLength(), this.getRawNameDefinitionLength());
		564
		565	return this.getRawNameDefinitionLength();
		566	}
		567	}
		568
447	/** gets the length of all texts	569	/** gets the length of all texts
448	* @return total length	570	* @return total length
449	*/	571	*/
450	public int getTextsLength(){	572	public int getTextsLength(){
451	int result;	573	int result;
452		574
453	result = getNameTextLength() + getDefinitionTextLength() + getDescriptionTextLength() +	575	result = getNameTextLength() + getDescriptionTextLength() +
454	getHelpTopicLength() + getStatusBarLength();	576	getHelpTopicLength() + getStatusBarLength();
455		577
456		578
457	return result;	579	return result;
458	}	580	}
459		581
		582	/**@return The byte array representation of the cell range, a null is never returned
		583	*/
		584	public byte[] getRawNameDefinition()
		585	{
		586	return this.field_13_raw_name_definition == null ? new byte[0] : this.field_13_raw_name_definition;
		587	}
		588
		589	/**Convenience method to get the length of the byte array
		590	* @return number of bytes used in the raw byte array representation of the cell range
		591	*/
		592	public int getRawNameDefinitionLength()
		593	{
		594	return this.getRawNameDefinition().length;
		595	}
		596
460	/** returns the record size	597	/** returns the record size
461	*/	598	*/
462	public int getRecordSize(){	599	public int getRecordSize(){
463	int result;	600	int result;
464		601
465	result = 19 + getTextsLength();	602	result = 19 + getTextsLength()+getReferenceSize();
466		603
467	return result;	604	return result;
468	}	605	}
469		606
		607	/**Determines the size of the cell range
		608	* @return PtgSize gets precedences if available
		609	*/
		610	protected int getReferenceSize()
		611	{
		612	int ptgSize = getPtgSize();
		613	int rawDefinitionSize = this.getRawNameDefinitionLength();
		614
		615	return ptgSize == 0 ? rawDefinitionSize : ptgSize;
		616	}
		617
		618	/**Counts the number of bytes the field range reference utilizes
		619	* @return the size of the data used to reference a cell range
		620	*/
		621	protected int getPtgSize()
		622	{
		623	int result = 0;
		624	if (getNameDefinition() == null) return result;
		625
		626	for (int k = 0; k < this.getNameDefinition().size(); k++) {
		627	Ptg ptg = ( Ptg ) this.getNameDefinition().get(k);
		628
		629	result += ptg.getSize();
		630	}
		631	return result;
		632	}
		633
470	/** gets the extern sheet number	634	/** gets the extern sheet number
471	* @return extern sheet index	635	* @return extern sheet index
472	*/	636	*/
Lines 485-490 Link Here
485	return result;	649	return result;
486	}	650	}
487		651
		652	/**Convenience Function to determine if the name is a built-in name
		653	*/
		654	public boolean isBuiltInName()
		655	{
		656	return ((this.getOptionFlag() & (short)0x20) != 0);
		657	}
		658
488	/** sets the extern sheet number	659	/** sets the extern sheet number
489	* @param externSheetNumber extern sheet number	660	* @param externSheetNumber extern sheet number
490	*/	661	*/
Lines 559-567 Link Here
559	}	730	}
560		731
561	if (ra.hasRange()) {	732	if (ra.hasRange()) {
562	ptg = new Area3DPtg();	733	ptg = new Area3DPtg(ref, externSheetIndex);
563	((Area3DPtg) ptg).setExternSheetIndex(externSheetIndex);
564	((Area3DPtg) ptg).setArea(ref);
565	this.setDefinitionTextLength((short)ptg.getSize());	734	this.setDefinitionTextLength((short)ptg.getSize());
566	} else {	735	} else {
567	ptg = new Ref3DPtg();	736	ptg = new Ref3DPtg();
Lines 576-582 Link Here
576		745
577	/**	746	/**
578	* called by the constructor, should set class level fields. Should throw	747	* called by the constructor, should set class level fields. Should throw
579	* runtime exception for bad/icomplete data.	748	* runtime exception for bad/incomplete data.
580	*	749	*
581	* @param data raw data	750	* @param data raw data
582	* @param size size of data	751	* @param size size of data
Lines 593-629 Link Here
593	field_8_length_description_text = data [11 + offset];	762	field_8_length_description_text = data [11 + offset];
594	field_9_length_help_topic_text = data [12 + offset];	763	field_9_length_help_topic_text = data [12 + offset];
595	field_10_length_status_bar_text = data [13 + offset];	764	field_10_length_status_bar_text = data [13 + offset];
596
597		765
598	if ( ( field_1_option_flag & (short)0x20 ) != 0 ) {	766	int start_of_name_definition = 15 + field_3_length_name_text;
599	// DEBUG	767	field_13_name_definition = getParsedExpressionTokens(data, field_4_length_name_definition,
600	// System.out.println( "Built-in name" );	768	offset, start_of_name_definition);
		769
		770	if (this.isBuiltInName()) {
601		771
602	field_11_compressed_unicode_flag = data[ 14 + offset ];	772	field_11_compressed_unicode_flag = data[ 14 + offset ];
603	field_12_builtIn_name = data[ 15 + offset ];	773	field_12_builtIn_name = data[ 15 + offset ];
604		774
605	if ( (field_12_builtIn_name & (short)0x07) != 0 ) {	775	//no need to store the area, translated when the name is requested
606	field_12_name_text = "Print_Titles";	776	//field_12_name_text = "Print_Area";
607
608	// DEBUG
609	// System.out.println( field_12_name_text );
610		777
611	field_13_raw_name_definition = new byte[ field_4_length_name_definition ];	778	field_13_raw_name_definition = new byte[ field_4_length_name_definition ];
612	System.arraycopy( data, 16 + offset, field_13_raw_name_definition, 0, field_13_raw_name_definition.length );	779	System.arraycopy( data, 16 + offset, field_13_raw_name_definition, 0, field_13_raw_name_definition.length );
613		780
614	// DEBUG
615	// System.out.println( HexDump.toHex( field_13_raw_name_definition ) );
616	}
617	}	781	}
618	else {	782	else {
619		783
620	field_11_compressed_unicode_flag= data [14 + offset];	784	field_11_compressed_unicode_flag= data [14 + offset];
621	field_12_name_text = new String(data, 15 + offset,	785	field_12_name_text = new String(data, 15 + offset,
622	LittleEndian.ubyteToInt(field_3_length_name_text));	786	LittleEndian.ubyteToInt(field_3_length_name_text));
623
624	int start_of_name_definition = 15 + field_3_length_name_text;
625	field_13_name_definition = getParsedExpressionTokens(data, field_4_length_name_definition,
626	offset, start_of_name_definition);
627		787
628	int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition;	788	int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition;
629	field_14_custom_menu_text = new String(data, start_of_custom_menu_text + offset,	789	field_14_custom_menu_text = new String(data, start_of_custom_menu_text + offset,
Lines 753-759 Link Here
753	.append("\n");	913	.append("\n");
754	buffer.append(" .Name (Unicode flag) = ").append( field_11_compressed_unicode_flag )	914	buffer.append(" .Name (Unicode flag) = ").append( field_11_compressed_unicode_flag )
755	.append("\n");	915	.append("\n");
756	buffer.append(" .Name (Unicode text) = ").append( field_12_name_text )	916	buffer.append(" .Name (Unicode text) = ").append( this.getNameText() )
757	.append("\n");	917	.append("\n");
758	buffer.append(" .Formula data (RPN token array without size field) = ").append( HexDump.toHex(	918	buffer.append(" .Formula data (RPN token array without size field) = ").append( HexDump.toHex(
759	((field_13_raw_name_definition != null) ? field_13_raw_name_definition : new byte[0] ) ) )	919	((field_13_raw_name_definition != null) ? field_13_raw_name_definition : new byte[0] ) ) )





    public Area3DPtg( String arearef, short externIdx )
    {
    	setArea(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 void setArea( String ref )
    {
		AreaReference ar = new AreaReference( 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 ) );

		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 )



}





 */

public class HSSFName {
    protected Workbook         book;
    protected NameRecord       name;
    
    /** Creates new HSSFName   - called by HSSFWorkbook to create a Name from
     * scratch.
     *
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createName()

        return result;
    }
    
    /**
	 * Retrieves the index that this name refers (one-based)
	 * @return int
	 */
	public int getSheetIndex()
    {
        return (int)name.getIndexToSheet();        
    }
    
    /**
     * Retrieves the built-in name
     * @return byte
     */
    public byte getBuiltInName()
    {
		return name.getBuiltInName();    
    }
    
    /** 
     * sets the name of the named range
     * @param nameName named range name to set


    /** 
     * sets the sheet name which this named range referenced to
     * Names associated to specific sheets are assigned in the built-in names
     * @param sheetName the sheet name of the reference
     */    


  
    /** 
     * sets the reference of this named range
     * <p>
     * Excel Expects a Range, even for single cell references,
     * <p>
     * i.e. $A should be $A:$A
     * @param ref the reference to set
     */    


            setSheetName(sheetName);
        }

        
        name.setAreaReference(ref);
        } else {
            name.setAreaReference(ra.getFromCell());            
        }

    }





    private POIFSFileSystem poifs;
    
    private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
    
    /**
     * Creates new HSSFWorkbook from scratch (start here!)
     *

        }
        
        for (int i = 0 ; i < workbook.getNumNames() ; ++i){
        	NameRecord nameRecord = workbook.getNameRecord(i);
        	//if more types we should implement a factory for this in the
        	//org.apache.poi.hssf.usermodel package if it fits in there
        	//generate the correct class for storage because they are serialized/presented
        	//differently but are inherently names
        	
        	HSSFName name = null;
        	
        	if (!nameRecord.isBuiltInName()) 
				name = new HSSFName(workbook, nameRecord);
        	else {
				switch (nameRecord.getBuiltInName())
				{
					case NameRecord.BUILTIN_PRINT_TITLE:
							name = new HSSFPrintTitles(workbook, nameRecord);
							break;
					default :
							name = new HSSFName(workbook, nameRecord);        		
				}
        	}
             
            names.add(name);
        }
    }

    }

    /**
     * 
     * @param sheetIndex
     * @param reference
     */
    public void setPrintArea(int sheetIndex, String reference)
    {
        HSSFName retrievedName = this.getBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
        if (retrievedName == null) 
            retrievedName = this.createBuiltinName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
            
        retrievedName.setReference(reference);
    }
    
    /**
     * used internally to set the workbook properties.
     */


     * @return named range high level
     */    
    public HSSFName getNameAt(int index){
        if (index == -1) throw new IllegalArgumentException("Invalid Name Index");
        HSSFName result = (HSSFName) names.get(index);
        
        return result;

    }
    
    
    
    /**
	 * Method getBuiltInName.
	 * @param name 
	 * @param sheetIndex Zero-based index
	 * @return HSSFName
	 */
	protected HSSFName getBuiltInName(byte name, int sheetIndex)
    {
        Iterator iterator = this.names.iterator();
        
        while (iterator.hasNext())
        {
            HSSFName retrievedName = (HSSFName)iterator.next();
            if (retrievedName.getBuiltInName() == name && retrievedName.getSheetIndex()==sheetIndex+1)
            	return retrievedName;
            
        }
        
        return null;
    }
    
    /** Gets the PrintArea HSSFName object at the specified sheet index
     * @param sheetIndex zero-based sheet index
     * @return Null if no print area is defined
     */
    public String getPrintArea(int sheetIndex)
    {
        HSSFName retrievedName = 
            this.getBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
        
        if (retrievedName == null) return null;
        
        return retrievedName.getReference();
    }
 
    
    /** 
     * <p>
     * Set the repeating rows or columns when printing
     * @param sheetIndex zero-based sheet index
     * @return Null if no print titles is defined for the sheet
     */
    public HSSFPrintTitles getPrintTitles(int sheetIndex)
    {
        HSSFName name = this.getBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
        
        if (name != null && !(name instanceof HSSFPrintTitles))
        {
        	//for read support 
        }
        
        return (HSSFPrintTitles)name;
    }
    
    /** creates a new named range and add it to the model
     * @return named range high level
     */    

        
        return newName; 
    }

    /**Create a HSSFName object for the specified built-in name
     * @param name the byte representation of the built-in name to generate
     * @param index the zero based index for the sheet
     * @return new Name object if the printArea does not already exist
     */
    protected HSSFName createBuiltinName(byte name, int index)
    {
        NameRecord nameRecord = workbook.createBuiltInName(name, index);
        
        HSSFName newName = new HSSFName(workbook, nameRecord);        
        names.add(newName);
        
        return newName;
        
    }
    
    /**Create PrintTitles for the specified sheet.
     * Global PrintTitles do not make sense so a sheet is required.
     * @param index the zero based index for the sheet
     * @return new Name object if the PrintTitle does not already exist
     */
    public HSSFPrintTitles createPrintTitles(int index)
    {
        NameRecord titleRecord = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_TITLE, index);
            
        if (titleRecord == null) 
		titleRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, index);
        	
        HSSFPrintTitles  printTitles = new HSSFPrintTitles(workbook, titleRecord);
        
        names.add(printTitles);
           
        return printTitles;
        
    }
    
    
    /** gets the named range index by his name
     * @param name named range name

        UnknownRecord r = new UnknownRecord((short)0x00EB,(short)0x005a, data);
        workbook.getRecords().add(loc, r);
    }
   
}





public class AreaReference {
    
	/**
	 * Internal Representation used to represent the Maximum Column when dealing
	 * with ranges defining only rows
	 */
	public static final String MAXIMUM_COLUMN   = "IV";
	
	
	/**
	 * Internal Representation used to represent the Maximum Column when dealing
	 * with ranges defining only columns
	 */
	    
	public static final String MAXIMUM_CELL_REFERENCE_ROW      = "65536";
    
private CellReference [] cells;
private int dim;


	/** Create an area ref from a string representation
	 */
	public AreaReference(String reference) {
		reference = processRowColOnlyRefs(reference);
		String[] refs = seperateAreaRefs(reference);
		dim = refs.length;
		cells = new CellReference[dim];
		for (int i=0;i<dim;i++) {
			cells[i]=new CellReference(refs[i]);
		}
	}
	//not sure if we need to be flexible here!
	/** return the dimensions of this area
	 **/
	public int getDim() {
		return dim;
	}
	/** return the cell references that define this area */
	public CellReference[] getCells() {
		return cells;
	}
    
	public String toString() {
		StringBuffer retval = new StringBuffer();
		for (int i=0;i<dim;i++){
			retval.append(':');
			retval.append(cells[i].toString());
		}
		retval.deleteCharAt(0);
		return retval.toString();
	}
    
	/**
	 * seperates Area refs in two parts and returns them as seperate elements in a 
	 * String array
	 */
	private String[] seperateAreaRefs(String reference) {
		String retval[] = new String[2];
		int length = reference.length();
        
		int loc = reference.indexOf(':',0);
        
		retval[0] = reference.substring(0,loc);
		retval[1] = reference.substring(loc+1);        
		return retval;
	}
	
	/**Processes (absolute and relative) Row Only or Column Only references so the formula packages can understand them
	 * <p>
	 * eg. $1:$2 => $A$1:$IV$2
	 * <p>
	 * eg. $C:$C => $C$1:$C$65535
	 * <p>
	 * <b>Note : </b>One absolute reference converts the whole to an absolute reference
	 * @param reference the reference to check
	 * @param isRowCheck True indicates a row check, false indicates a column check
	 * @return Processed internal representation of the reference if it is a row-only
	 * reference, otherwise the same is returned
	 * 
	 */
	protected String processRowColOnlyRefs(final String reference)
	{

		boolean isRowCheck = false;
		boolean isRowOnly = isRowOnlyReference(reference);
		boolean isColOnly = isColOnlyReference(reference);
		boolean isAbsolute = (reference.indexOf("$") > -1);
        
		//not a row only or col only reference
		if (!isRowOnly && !isColOnly) return reference;        
                
		isRowCheck = isRowOnly;
        
		String[] parsedRefs = new String[0];
		StringBuffer returnValue = new StringBuffer();
		String sheet = null;
		int sheetIndex = reference.indexOf('!');
        
		if (sheetIndex > 0) {
			sheet = reference.substring(0, sheetIndex);
			returnValue.append(sheet).append("!");
		}
        
		parsedRefs = seperateAreaRefs((sheetIndex > 0) ? reference.substring(sheetIndex+1) : reference);
        
		if (parsedRefs.length == 2){
			for (int i = 0; i < parsedRefs.length; i++)
			{
				String cellRef = removeAbsolutes(parsedRefs[i]);                
				boolean isFirst = (i == 0);
                
				if (!isFirst) returnValue.append(":");
                
				if (isRowCheck) {
					returnValue.append(processRowReference(cellRef, isFirst, isAbsolute));
				} else {
					returnValue.append(processColReference(cellRef, isFirst, isAbsolute));
				}
			}
		}
        
		return returnValue.toString();
	}
        
	/**Determines if the cell reference points to a row only
	 * @return true if the reference (absolute or not) refers to a row only
	 */
	public static boolean isRowOnlyReference(final String reference)
	{
		int start = reference.indexOf("!") + 1;
		char[] chars = reference.toCharArray();
		int loc = start;
		if (chars[loc]=='$') loc++;
		for (; loc < chars.length; loc++) {
			if (Character.isDigit(chars[loc]) || chars[loc] == '$' || chars[loc] == ':') {
				continue;
			} else {
				//not a number or absolute reference
				return false;
			}
		}        
		return true;
	}

	/**Determines if the cell reference points to a Column only
	 * @return true if the reference (absolute or not) refers to a Column only
	 */
	public static boolean isColOnlyReference(final String reference)
	{
		int start = reference.indexOf("!") + 1;
		char[] chars = reference.toCharArray();
		int loc = start;
		if (chars[loc]=='$') loc++;
		for (; loc < chars.length; loc++) {
			if (Character.isLetter(chars[loc]) || chars[loc] == '$' || chars[loc] == ':') {
				continue;
			} else {
				//not a number or absolute reference
				return false;
			}
		}        
		return true;
	}
    
    
	/** Removes the absolute references for processing
	 * @return reference with the "$" characters
	 */
	public static String removeAbsolutes(String reference)
	{
		StringBuffer sb = new StringBuffer(reference);
        
		int index = 0;
        
		while ( index < sb.length())
		{
			char ch = sb.charAt(index);
			if (ch == '$') {
				sb.deleteCharAt(index);
			} else { 
				index++;
			}
		}
        
		return sb.toString();
	}
    
	/**Converts the row reference to a proper internal form
	 * <p>
	 * For a row only reference, the first reference should be the first column
	 * and the second reference, the maximum column in the first row.
	 * <p>
	 * eg. $1:$2 => $A$1:$IV$2
	 * @param rowReference number only row reference
	 * @param isFirst if the cell reference is the first in the range
	 * @param isAbsolute return an absolute reference
	 * @return reference
	 */
	protected String processRowReference(String rowReference, boolean isFirst, boolean isAbsolute)
	{
		String absolute = (isAbsolute) ? "$" : "";
        
		if (isFirst) return absolute+"A"+absolute+rowReference;
		else return absolute+AreaReference.MAXIMUM_COLUMN+absolute+rowReference;
	}
    
   /**Converts the col reference to a proper internal form
	 * <p>
	 * For a col only reference, the first reference should be the column, first row
	 * and the second reference, the column, maximum row
	 * <p>
	 * eg. $A:$A => $A$1:$A$65535
	 * @param rowReference number only row reference
	 * @param isFirst if the cell reference is the first in the range
	 * @param isAbsolute return an absolute reference
	 * @return an absolute reference
	 */
	protected String processColReference(String colReference, boolean isFirst, boolean isAbsolute)
	{
		String absolute = (isAbsolute) ? "$" : "";
        
		if (isFirst) return absolute+colReference+absolute+"1";
		else return absolute+colReference+absolute+AreaReference.MAXIMUM_CELL_REFERENCE_ROW;
	}    	
}




package org.apache.poi.hssf.util;

/* ====================================================================
 * 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
 * <http://www.apache.org/>.
 */


/**
 * Title:        Range Address <P>
 * Description:  provides connectivity utilities for ranges<P>
 *
 *
 * REFERENCE:  <P>
 * @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)
 * @version 1.0
 */

public class RangeAddress {
  final static int WRONG_POS  = -1;
  final static int MAX_HEIGHT = 66666;
  final static char SO_FORMNAME_ENCLOSURE =  '\'';
  String m_sheetName;
  String m_cellFrom;
  String m_cellTo;
  
  public RangeAddress (String _url) {
	init (_url);
  }
  
  public RangeAddress (int _startCol, int _startRow, int _endCol, int _endRow) {
	init (numTo26Sys (_startCol) + _startRow + ":"
	+ numTo26Sys (_endCol) + _endRow);
  }
  
  public String getAddress (){
	String result = "";
	if(m_sheetName != null)
	  result += m_sheetName;
	if(m_cellFrom != null){
	  result += m_cellFrom;
	  if(m_cellTo != null)
		result += ":" + m_cellTo;
	}
	return result;
  }
  
  public String getSheetName (){
	return m_sheetName;
  }
  
  public String getRange (){
	String result = "";
	if(m_cellFrom != null){
	  result += m_cellFrom;
	  if(m_cellTo != null)
		result += ":" + m_cellTo;
	}
	return result;
  }
  
  public  boolean isCellOk (String _cell){
	if (_cell != null){
	  if ( (getYPosition (_cell) != WRONG_POS) &&
	  (getXPosition (_cell) != WRONG_POS) )
		return true;
	  else
		return false;
	} else
	  return false;
  }
  
  public  boolean isSheetNameOk (){
	return isSheetNameOk (m_sheetName);
  }
  
  private  static boolean intern_isSheetNameOk (String _sheetName, boolean _canBeWaitSpace){
	for (int i = 0  ; i < _sheetName.length (); i++){
	  char ch = _sheetName.charAt (i);
	  if (! (Character.isLetterOrDigit (ch) || (ch == '_')||
	  _canBeWaitSpace&&(ch == ' '))){
		return false;
	  }
	}
	return true;
  }
  
  public  static boolean isSheetNameOk (String _sheetName){
	boolean res = false;
	if ( ( _sheetName != null) && !_sheetName.equals ("")){
	  res = intern_isSheetNameOk (_sheetName,true);
	}else
	  res = true;
	return res;
  }
  
  
  public String getFromCell (){
	return m_cellFrom;
  }
  
  public String getToCell (){
	return m_cellTo;
  }
  
  public int getWidth (){
	if(m_cellFrom != null && m_cellTo != null){
	  int toX    =  getXPosition (m_cellTo);
	  int fromX  =  getXPosition (m_cellFrom);
	  if ((toX == WRONG_POS) || (fromX == WRONG_POS)){
		return 0;
	  }else
		return toX - fromX + 1;
	}
	return 0;
  }
  
  public int getHeight (){
	if(m_cellFrom != null && m_cellTo != null){
	  int toY    =  getYPosition (m_cellTo);
	  int fromY  =  getYPosition (m_cellFrom);
	  if ((toY == WRONG_POS) || (fromY == WRONG_POS)){
		return 0;
	  }else
		return toY - fromY + 1;
	}
	return 0;
  }
  
  public void setSize (int _width, int _height){
	if(m_cellFrom == null)
	  m_cellFrom = "a1";
	int tlX, tlY, rbX, rbY;
	tlX = getXPosition (m_cellFrom);
	tlY = getYPosition (m_cellFrom);
	m_cellTo = numTo26Sys (tlX + _width - 1);
	m_cellTo += String.valueOf (tlY + _height - 1);
  }
  
  public boolean hasSheetName (){
	if(m_sheetName == null)
	  return false;
	return true;
  }
  
  public boolean hasRange (){
	if(m_cellFrom == null || m_cellTo == null)
	  return false;
	return true;
  }
  
  public boolean hasCell (){
	if(m_cellFrom == null)
	  return false;
	return true;
  }
  
  private void init (String _url){

	_url = removeString(_url, "$");
	_url = removeString(_url, "'");
    
	String[] urls = parseURL (_url);
	m_sheetName = urls[0];
	m_cellFrom = urls[1];
	m_cellTo = urls[2];

	//What if range is one celled ?
	if (m_cellTo == null){
	  m_cellTo = m_cellFrom;
	}
        
	//Removing noneeds characters
	m_cellTo    = removeString(m_cellTo,".");
    
    
  }
  
  private String[] parseURL (String _url){
	String[] result = new String[3];
	int index = _url.indexOf(':');
	if (index >= 0) {
	  String fromStr = _url.substring(0, index);
	  String toStr = _url.substring(index+1);
	  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('!');
	  if (index >= 0) {
		result[2] = toStr.substring(index+1); 
	  } else {
		result[2] = toStr; 
	  }     
	} else {
	  index = _url.indexOf('!');
	  if (index >= 0) {
		result[0] = _url.substring(0, index);
		result[1] = _url.substring(index+1);
	  } else {
		result[1] = _url;
	  }
	}
	return result;
  }
  
  public int getYPosition (String _subrange){
	int result = WRONG_POS;
	_subrange = _subrange.trim ();
	if (_subrange.length () != 0){
	  String digitstr = getDigitPart (_subrange);
	  try {
		result = Integer.parseInt (digitstr);
		if (result  > MAX_HEIGHT){
		  result = WRONG_POS;
		}
	  }
	  catch (Exception ex) {
        
		result = WRONG_POS;
	  }
	}
	return result;
  }
  
  private static boolean isLetter (String _str){
	boolean res = true;
	if ( !_str.equals ("") ){
	  for (int i = 0  ; i < _str.length (); i++){
		char ch = _str.charAt (i);
		if (! Character.isLetter (ch)){
		  res = false;
		  break;
		}
	  }
	}else
	  res = false;
	return res;
  }
  
  public int getXPosition (String _subrange){
	int result = WRONG_POS;
	String tmp = filter$ (_subrange);
	tmp = this.getCharPart (_subrange);
	// we will process only 2 letters ranges
	if (isLetter (tmp) && ((tmp.length () == 2)|| (tmp.length () == 1) )){
	  result =  get26Sys (tmp);
	}
	return result;
  }
  
  public String getDigitPart (String _value){
	String result = "";
	int digitpos = getFirstDigitPosition (_value);
	if(digitpos >= 0){
	  result = _value.substring (digitpos);
	}
	return result;
  }
  
  public String getCharPart (String _value){
	String result = "";
	int digitpos = getFirstDigitPosition (_value);
	if(digitpos >= 0){
	  result = _value.substring (0, digitpos);
	}
	return result;
  }
  
  private String filter$ (String _range){
	String res = "";
	for (int i = 0 ; i < _range.length () ; i++){
	  char ch = _range.charAt (i);
	  if  ( ch != '$' ){
		res = res + ch;
	  }
	}
	return res;
  }
  
  private int getFirstDigitPosition (String _value){
	int result = WRONG_POS;
	if(_value != null && _value.trim ().length () == 0){
	  return result;
	}
	_value = _value.trim ();
	int length = _value.length ();
	for(int i = 0; i < length; i++){
	  if(Character.isDigit (_value.charAt (i))){
		result = i;
		break;
	  }
	}
	return result;
  }
  
  public int get26Sys (String _s){
	int sum = 0;
	int multiplier = 1;
	if (_s != "") {
	  for (int i = _s.length ()-1 ; i >= 0 ; i--){
		char ch = _s.charAt (i);
		int val =  Character.getNumericValue (ch) - Character.getNumericValue ('A')+1;
		sum = sum + val * multiplier;
		multiplier = multiplier * 26;
	  }
	  return sum;
	}
	return WRONG_POS;
  }
  
  public String numTo26Sys (int _num){
	int sum = 0;
	int reminder;
	String s ="";
	do{
	  _num --;
	  reminder = _num % 26;
	  int val =  65 + reminder;
	  _num = _num / 26;
	  s = (char)val + s; // reverce
	}while(_num > 0);
	return s;
  }
  
	public String replaceString(String _source , String _oldPattern,
	String _newPattern){
		StringBuffer res = new StringBuffer(_source);
		int pos = -1;
        
		while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1){
			res.replace(pos, pos + _oldPattern.length(), _newPattern);
		}
        
		return res.toString();
	}
    
	public String removeString(String _source, String _match){
		return replaceString(_source, _match, "");
	}
  
}


Lines 50-55 Link Here

(-)src/testcases/org/apache/poi/hssf/HSSFTests.java (+4 lines)
50	import org.apache.poi.hssf.record.TestUnitsRecord;	50	import org.apache.poi.hssf.record.TestUnitsRecord;
51	import org.apache.poi.hssf.record.TestValueRangeRecord;	51	import org.apache.poi.hssf.record.TestValueRangeRecord;
52	import org.apache.poi.hssf.record.aggregates.TestRowRecordsAggregate;	52	import org.apache.poi.hssf.record.aggregates.TestRowRecordsAggregate;
		53	import org.apache.poi.hssf.record.formula.TestArea3DPtg;
53	import org.apache.poi.hssf.usermodel.TestCellStyle;	54	import org.apache.poi.hssf.usermodel.TestCellStyle;
54	import org.apache.poi.hssf.usermodel.TestFormulas;	55	import org.apache.poi.hssf.usermodel.TestFormulas;
55	import org.apache.poi.hssf.usermodel.TestHSSFCell;	56	import org.apache.poi.hssf.usermodel.TestHSSFCell;
Lines 146-151 Link Here
146	suite.addTest(new TestSuite(TestCellReference.class));	147	suite.addTest(new TestSuite(TestCellReference.class));
147	suite.addTest(new TestSuite(TestRKUtil.class));	148	suite.addTest(new TestSuite(TestRKUtil.class));
148	suite.addTest(new TestSuite(TestSheetReferences.class));	149	suite.addTest(new TestSuite(TestSheetReferences.class));
		150
		151	//adds test of formula ptgs
		152	suite.addTest(new TestSuite(TestArea3DPtg.class));
149		153
150	//$JUnit-END$	154	//$JUnit-END$
151	return suite;	155	return suite;

Lines 232-238 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java (-1 / +1 lines)
232	throws java.io.IOException {	232	throws java.io.IOException {
233	String readFilename = System.getProperty("HSSF.testdata.path");	233	String readFilename = System.getProperty("HSSF.testdata.path");
234		234
235	File file = File.createTempFile("testBoolErr",".xls");	235	File file = File.createTempFile("testFormulaStyle",".xls");
236	FileOutputStream out = new FileOutputStream(file);	236	FileOutputStream out = new FileOutputStream(file);
237	HSSFWorkbook wb = new HSSFWorkbook();	237	HSSFWorkbook wb = new HSSFWorkbook();
238	HSSFSheet s = wb.createSheet("Sheet1");	238	HSSFSheet s = wb.createSheet("Sheet1");





/* ====================================================================
 * 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
 * <http://www.apache.org/>.
 */

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.hssf.util.AreaReference;
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 {

        junit.textui.TestRunner.run(TestNamedRange.class);
    }
    
    
    /**
     * There was bug where the named reference was not being set properly
     * <p>
     * i.e. Sheet1!$1$2 did not translate to Sheet1!$A$1:$UV$2
     */
    public void testNameRowReference()
    {
    	HSSFWorkbook wb = new HSSFWorkbook();
    	wb.createSheet("Sheet 1");
    	HSSFName name = wb.createName();
    	
    	name.setNameName("test");
    	name.setReference("Sheet 1!$1:$2");
    	    	
    	assertEquals("Row references should be converted: ", "Sheet 1!$A$1:$"+AreaReference.MAXIMUM_COLUMN+"$2", name.getReference());
    	
    }
        
    /** Test of TestCase method, of class test.RangeTest. */
    public void testNamedRange() 
        throws IOException

        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();
            wb = new HSSFWorkbook(fs);
        
        //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();
                               
        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("Reference Matches",wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference());
        
        
    }

    /** Test that multiple named ranges can be added 
     */
    public void testMultipleNamedRange() 
        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();

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

        //Creating another new Named Range
        HSSFName newNamedRange2 = wb.createName();
        
        //Getting Sheet Name for the reference
        sheetName = wb.getSheetName(1);
        
        //Setting its name
        newNamedRange2.setNameName("AnotherTest");
        //Setting its reference
        newNamedRange2.setReference(sheetName + "!$F$1:$G$6");
        
        
        //Getting NAmed Range
        HSSFName namedRange1 = wb.getNameAt(0);
        //Getting it sheet name

        //Getting its reference
        String referece = namedRange1.getReference();
                               
        File             file = File.createTempFile("testMultiNamedRange",
                                        ".xls");

        FileOutputStream fileOut = new FileOutputStream(file);

        
        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()));        
        
        
    }    
    
    /**
     * Addresses Bug #16411
     */
    public void testNamedRead() throws IOException
    {
		FileInputStream fis = null;
		POIFSFileSystem fs  = null;
		HSSFWorkbook wb     = null;
        
		String filename = System.getProperty("HSSF.testdata.path");
		filename = filename + "/SimpleNamed.xls";
        
		fis = new FileInputStream(filename);
		fs = new POIFSFileSystem(fis);
		wb = new HSSFWorkbook(fs);
    
		HSSFName name = wb.getNameAt(0);
		String sheetName = wb.getSheetName(0);
		
		assertEquals("Retrieved name", "Test", name.getNameName());
		assertEquals("Reference",sheetName+"!$C$8", name.getReference());
		
		name.setReference(sheetName+"!$A$1:$C$36");
		assertEquals("Reference",sheetName+"!$A$1:$C$36", name.getReference());
    	
    	fis.close();
    }
    
    /**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);
        //HSSFName printArea = workbook.createPrintArea(0);
        //assertNotNull("Print Area is Null", printArea);        
        
        //printArea.setReference();
        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("References Match", reference, retrievedPrintArea);
        
    }
    
    /**
     * 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);
		//HSSFName printArea = workbook.createPrintArea(0);
		//assertNotNull("Print Area is Null", printArea);        
        
		//printArea.setReference();
		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 the multiple print areas can be retrieved/created in memory
     */
    public void testMultiplePrintArea()
    {
        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);
                
		String retrievedPrintArea = workbook.getPrintArea(0);        
		assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
		assertEquals("References Do Not Match (Sheet 1)", reference, retrievedPrintArea);
        
		String retrievedPrintArea2 = workbook.getPrintArea(1);        
		assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea2);
		assertEquals("References Do Not Match (Sheet 2)", reference2, retrievedPrintArea2);

		String retrievedPrintArea3 = workbook.getPrintArea(2);        
		assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea3);
		assertEquals("References Do Not Match (Sheet 3)", reference3, retrievedPrintArea3);
        
        
        
    }
    
    /**
     * 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("References Do Not Match (Sheet 1)", reference, retrievedPrintArea);
        
		String retrievedPrintArea2 = workbook.getPrintArea(1);        
        assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea2);
        assertEquals("References Do Not Match (Sheet 2)", reference2, retrievedPrintArea2);

		String retrievedPrintArea3 = workbook.getPrintArea(2);        
        assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea3);
        assertEquals("References Do Not Match (Sheet 3)", reference3, retrievedPrintArea3);
        
        
    }
    
   /**Test to see if the printTitles can be retrieved/created in memory
     */
    public void testSinglePrintTitles()
    {
        HSSFWorkbook workbook = new HSSFWorkbook();        
        HSSFSheet sheet = workbook.createSheet("Test Print Titles");                
        String sheetName = workbook.getSheetName(0);
        HSSFPrintTitles titles = workbook.createPrintTitles(0);
        assertNotNull("Print Title is Null", titles);        
        
		titles.setRowReference(sheetName+"!$1:$2");
        assertEquals("Sheetnames did not carry over.", titles.getSheetName(), sheetName);
        assertEquals("PrintTitles properly set.", titles.getNameName(), "Print_Titles");

        
        HSSFName retrievedName = workbook.getPrintTitles(0);
        assertEquals("PrintTitles properly set.", retrievedName.getNameName(), "Print_Titles");
        
        assertEquals("References Match", titles.getReference(), retrievedName.getReference());
        
    }
    
    /**
     * Test to see if the print_titles made it to the file
     */
    public void testPrintTitlesFile()
    throws IOException
    {
        HSSFWorkbook workbook = new HSSFWorkbook();        
        HSSFSheet sheet = workbook.createSheet("Test Print Titles");                
        String sheetName = workbook.getSheetName(0);
		HSSFPrintTitles titles = workbook.createPrintTitles(0);
        assertNotNull("Print Title is Null", titles);        
        
		titles.setRowReference(sheetName+"!$1:$2");
        assertEquals("Sheetnames did not carry over.", titles.getSheetName(), sheetName);
        assertEquals("PrintTitles properly set.", titles.getNameName(), "Print_Titles");
        
        File file = File.createTempFile("testPrintTitles",".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);
        HSSFPrintTitles retrievedTitles = workbook.getPrintTitles(0);
        assertNotNull("Print Area Not Found", retrievedTitles);
        assertEquals("References Do Not Match", titles.getReference(), retrievedTitles.getReference());
		assertEquals("Row References Do Not Match", titles.getRowReference(), retrievedTitles.getRowReference());
		assertEquals("Column References Do Not Match", titles.getColReference(), retrievedTitles.getColReference());		
        
    }

    /**Test to see if the multiple print_titles can be retrieved/created in memory
     */
    public void testMultiplePrintTitles()
    {
        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);
        
		HSSFPrintTitles titles = workbook.createPrintTitles(0);
        assertNotNull("Print Titles (Sheet 1) is Null", titles);        
        
        titles.setRowReference(sheetName+"!$1:$2");
        assertEquals("Sheetnames did not carry over.", titles.getSheetName(), sheetName);

        
        sheetName = workbook.getSheetName(1);
		HSSFPrintTitles titles2 = workbook.createPrintTitles(1);
        assertNotNull("Print Titles (Sheet 2) is Null", titles2);        
        
		titles2.setColReference(sheetName+"!$A:$A");
        assertNotNull("Print Titles (Sheet 2) is Null", titles2);                
        
        sheetName = workbook.getSheetName(2);
		HSSFPrintTitles titles3 = workbook.createPrintTitles(2);
        assertNotNull("Print Titles (Sheet 3) is Null", titles3);        
        
		titles3.setColReference(sheetName+"!$D:$E");
        assertNotNull("Print Titles (Sheet 3) is Null", titles3);                
        
        
        HSSFName retrievedTitles = workbook.getPrintTitles(0);
        assertEquals("References Match", titles.getReference(), retrievedTitles.getReference());

        HSSFName retrievedTitles2 = workbook.getPrintTitles(1);
        assertEquals("References Match (Sheet 2)", titles2.getReference(), retrievedTitles2.getReference());
        
        HSSFName retrievedTitles3 = workbook.getPrintTitles(2);
        assertEquals("References Match (Sheet 3)", titles3.getReference(), retrievedTitles3.getReference());
        
    }
    
    /**
     * Test to see if multiple print titles made it to the file
     */
    public void testMultiplePrintTitlesFile()
    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);
        
		HSSFPrintTitles titles = workbook.createPrintTitles(0);
		assertNotNull("Print Titles (Sheet 1) is Null", titles);        
        
		titles.setRowReference(sheetName+"!$1:$2");
		assertEquals("Sheetnames did not carry over.", titles.getSheetName(), sheetName);

        
		sheetName = workbook.getSheetName(1);
		HSSFPrintTitles titles2 = workbook.createPrintTitles(1);
		assertNotNull("Print Titles (Sheet 2) is Null", titles2);        
        
		titles2.setColReference(sheetName+"!$A:$A");
		assertNotNull("Print Titles (Sheet 2) is Null", titles2);                
        
		sheetName = workbook.getSheetName(2);
		HSSFPrintTitles titles3 = workbook.createPrintTitles(2);
		assertNotNull("Print Titles (Sheet 3) is Null", titles3);        
        
		titles3.setColReference(sheetName+"!$D:$E");
		assertNotNull("Print Titles (Sheet 3) is Null", titles3);                
        
		File file = File.createTempFile("testMultiPrintTitles",".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);

        
		HSSFName retrievedTitles = workbook.getPrintTitles(0);
		assertEquals("References Match", titles.getReference(), retrievedTitles.getReference());

		HSSFName retrievedTitles2 = workbook.getPrintTitles(1);
		assertEquals("References Match (Sheet 2)", titles2.getReference(), retrievedTitles2.getReference());
        
		HSSFName retrievedTitles3 = workbook.getPrintTitles(2);
		assertEquals("References Match (Sheet 3)", titles3.getReference(), retrievedTitles3.getReference());
        
    }    
    
}





     public TestAreaReference(String s) {
        super(s);
    }
    
     
	public static void main(String [] ignored_args)
	{
		String filename = System.getProperty("HSSF.testdata.path");

		System.out
			.println("Testing org.apache.poi.hssf.util.TestAreaReference");
		junit.textui.TestRunner.run(TestAreaReference.class);
	}     
     
	public void testRemoveAbsolutes()
	{
		assertEquals("Absolutes Removed", "A1:B2", AreaReference.removeAbsolutes("$A$1:$B$2")); 
		assertEquals("Absolutes Removed", "sheet!A1:B2", AreaReference.removeAbsolutes("sheet!$A$1:$B$2"));
	}
         
    
    public void testAreaRef1() {
        AreaReference ar = new AreaReference("$A$1:$B$2");
        assertTrue("Two cells expected",ar.getCells().length == 2);

        assertTrue("col is abs",cf.isColAbsolute());
        assertTrue("string is $B$2",cf.toString().equals("$B$2"));
    }
    
	/**Tests the static function to see if it correctly interprets the formula
	 */
	public void testIsRowOnly(){
		assertTrue("$1:$2 is a row only formula", AreaReference.isRowOnlyReference("$1:$2"));
		assertTrue("$A$1:$2 is not row only formula", !AreaReference.isRowOnlyReference("$A$1:$2"));
		assertTrue("$A$1:$B$2 is not row only formula", !AreaReference.isRowOnlyReference("$A$1:$B$2"));
        
		assertTrue("1:2 is a row only formula", AreaReference.isRowOnlyReference("1:2"));
		assertTrue("A1:2 is not row only formula", !AreaReference.isRowOnlyReference("A1:2"));
		assertTrue("A1:B2 is not row only formula", !AreaReference.isRowOnlyReference("A1:$B2"));
        
		assertTrue("$A:$B is not a row only forumla", !AreaReference.isRowOnlyReference("$A:$B"));
	}
    
	/**Tests the static function to see if it correctly interprets the formula
	 */
	public void testIsRowOnlyWithSheets(){
		assertTrue("Sheet1!$1:$2 is a row only formula", AreaReference.isRowOnlyReference("Sheet1!$1:$2"));
		assertTrue("Sheet!$A$1:$2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!$A$1:$2"));
		assertTrue("Sheet1!$A$1:$B$2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!$A$1:$B$2"));

		assertTrue("1:2 is a row only formula", AreaReference.isRowOnlyReference("Sheet1!1:2"));
		assertTrue("A1:2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!A1:2"));
		assertTrue("A1:B2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!A1:$B2"));
        
        
		assertTrue("$A:$B is not a row only forumla", !AreaReference.isRowOnlyReference("Sheet1!$A:$B"));
	}
    
	/**Tests the static function to see if it correctly interprets the formula
	 */
	public void testIsColOnly(){
		assertTrue("$A:$B is a col only formula", AreaReference.isColOnlyReference("$A:$A"));
        
		assertTrue("$A$1:$2 is not col only formula", !AreaReference.isColOnlyReference("$A$1:$2"));
		assertTrue("$A$1:$B$2 is not col only formula", !AreaReference.isColOnlyReference("$A$1:$B$2"));
        
		assertTrue("A:B is a col only formula", AreaReference.isColOnlyReference("A:B"));
		assertTrue("A1:2 is not col only formula", !AreaReference.isColOnlyReference("A1:2"));
		assertTrue("A1:B2 is not col only formula", !AreaReference.isColOnlyReference("A1:$B2"));
        
		assertTrue("$1:$2 is not a col only forumla", !AreaReference.isColOnlyReference("$1:$2"));
	}
    
	/**Tests the static function to see if it correctly interprets the formula
	 */
	public void testIsColOnlyWithSheets(){
		assertTrue("$A:$B is a col only formula", AreaReference.isColOnlyReference("Sheet 1!$A:$A"));
        
		assertTrue("$A$1:$2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!$A$1:$2"));
		assertTrue("$A$1:$B$2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!$A$1:$B$2"));
        
		assertTrue("A:B is a col only formula", AreaReference.isColOnlyReference("Sheet 1!A:B"));
		assertTrue("A1:2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!A1:2"));
		assertTrue("A1:B2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!A1:$B2"));
        
		assertTrue("$1:$2 is not a col only forumla", !AreaReference.isColOnlyReference("Sheet 1!$1:$2"));
	}

    
	/**Generate the correct references for row only references
	 */

	public void testRowReferences()
	{
		AreaReference ar = new AreaReference("$1:$1");
        
		assertTrue("Two cells expected",ar.getCells().length == 2);

		CellReference cf = ar.getCells()[0];
		assertTrue("row is 0",cf.getRow()==0);
		assertTrue("col is 0",cf.getCol()==0);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("string is $A$1",cf.toString().equals("$A$1"));
        
		cf = ar.getCells()[1];
		assertTrue("row is 1",cf.getRow()==0);
		assertTrue("col is 255",cf.getCol()==255);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("string is $IV$1",cf.toString().equals("$IV$1"));
        
        
	}

	/**Generate the correct references for row only references
	 */

	public void testMultipleRowReferences()
	{
		AreaReference ar = new AreaReference("$1:$2");
        
		assertTrue("Two cells expected",ar.getCells().length == 2);

		CellReference cf = ar.getCells()[0];
		assertTrue("row is 0",cf.getRow()==0);
		assertTrue("col is 0",cf.getCol()==0);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("string is $A$1",cf.toString().equals("$A$1"));
        
		cf = ar.getCells()[1];
		assertTrue("row is 1",cf.getRow()==1);
		assertTrue("col is 255",cf.getCol()==255);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("string is $IV$2",cf.toString().equals("$IV$2"));
        
        
	}
    
    
	/**Generate the correct references for column only references
	 */    
	public void testColReferences()
	{
		 AreaReference ar = new AreaReference("$A:$A");
        
		assertTrue("Two cells expected",ar.getCells().length == 2);

		CellReference cf = ar.getCells()[0];
		assertTrue("row is 0",cf.getRow()==0);
		assertTrue("col is 0",cf.getCol()==0);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("string is $A$1",cf.toString().equals("$A$1"));
        
		cf = ar.getCells()[1];
		//internal representation is 0 based
		assertEquals("row number",65535,cf.getRow());
		assertEquals("last col", 0, cf.getCol());
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertEquals("String Reference","$A$"+AreaReference.MAXIMUM_CELL_REFERENCE_ROW,cf.toString());
       
	}
    
	/**Generate the correct references for column only references
	 */    
	public void testMultipleColReferences()
	{
		 AreaReference ar = new AreaReference("$B:$D");
        
		assertTrue("Two cells expected",ar.getCells().length == 2);

		CellReference cf = ar.getCells()[0];
		assertTrue("row is 0",cf.getRow()==0);
		assertTrue("col is 0",cf.getCol()==1);
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertTrue("String Reference",cf.toString().equals("$B$1"));
        
		cf = ar.getCells()[1];
		//internal representation is 0 based
		assertEquals("last row number", 65535, cf.getRow());
		assertEquals("last col", 3, cf.getCol());
		assertTrue("row is abs",cf.isRowAbsolute());
		assertTrue("col is abs",cf.isColAbsolute());
		assertEquals("String Reference","$D$"+AreaReference.MAXIMUM_CELL_REFERENCE_ROW, cf.toString());
       
	}    
}

Return to bug 16557

Lines 93-108 Link Here

(-)src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java (-19 / +11 lines)
93		93
94	public Area3DPtg( String arearef, short externIdx )	94	public Area3DPtg( String arearef, short externIdx )
95	{	95	{
96	AreaReference ar = new AreaReference( arearef );	96	setArea(arearef);
97
98	setFirstRow( (short) ar.getCells()[0].getRow() );
99	setFirstColumn( (short) ar.getCells()[0].getCol() );
100	setLastRow( (short) ar.getCells()[1].getRow() );
101	setLastColumn( (short) ar.getCells()[1].getCol() );
102	setFirstColRelative( !ar.getCells()[0].isColAbsolute() );
103	setLastColRelative( !ar.getCells()[1].isColAbsolute() );
104	setFirstRowRelative( !ar.getCells()[0].isRowAbsolute() );
105	setLastRowRelative( !ar.getCells()[1].isRowAbsolute() );
106	setExternSheetIndex( externIdx );	97	setExternSheetIndex( externIdx );
107		98
108	}	99	}
Lines 287-302 Link Here
287		278
288	public void setArea( String ref )	279	public void setArea( String ref )
289	{	280	{
290	RangeAddress ra = new RangeAddress( ref );	281	AreaReference ar = new AreaReference( ref );
291
292	String from = ra.getFromCell();
293	String to = ra.getToCell();
294
295	setFirstColumn( (short) ( ra.getXPosition( from ) - 1 ) );
296	setFirstRow( (short) ( ra.getYPosition( from ) - 1 ) );
297	setLastColumn( (short) ( ra.getXPosition( to ) - 1 ) );
298	setLastRow( (short) ( ra.getYPosition( to ) - 1 ) );
299		282
		283	setFirstRow( (short) ar.getCells()[0].getRow() );
		284	setFirstColumn( (short) ar.getCells()[0].getCol() );
		285	setLastRow( (short) ar.getCells()[1].getRow() );
		286	setLastColumn( (short) ar.getCells()[1].getCol() );
		287	setFirstColRelative( !ar.getCells()[0].isColAbsolute() );
		288	setLastColRelative( !ar.getCells()[1].isColAbsolute() );
		289	setFirstRowRelative( !ar.getCells()[0].isRowAbsolute() );
		290	setLastRowRelative( !ar.getCells()[1].isRowAbsolute() );
300	}	291	}
301		292
302	public String toFormulaString( SheetReferences refs )	293	public String toFormulaString( SheetReferences refs )
Lines 359-361 Link Here
359		350
360		351
361	}	352	}
		353

Lines 66-75 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFName.java (-8 / +28 lines)
66	*/	66	*/
67		67
68	public class HSSFName {	68	public class HSSFName {
69	private Workbook book;	69	protected Workbook book;
70	private NameRecord name;	70	protected NameRecord name;
71		71
72	/** Creates new HSSFName - called by HSSFWorkbook to create a sheet from	72	/** Creates new HSSFName - called by HSSFWorkbook to create a Name from
73	* scratch.	73	* scratch.
74	*	74	*
75	* @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createName()	75	* @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createName()
Lines 106-111 Link Here
106	return result;	106	return result;
107	}	107	}
108		108
		109	/**
		110	* Retrieves the index that this name refers (one-based)
		111	* @return int
		112	*/
		113	public int getSheetIndex()
		114	{
		115	return (int)name.getIndexToSheet();
		116	}
		117
		118	/**
		119	* Retrieves the built-in name
		120	* @return byte
		121	*/
		122	public byte getBuiltInName()
		123	{
		124	return name.getBuiltInName();
		125	}
		126
109	/**	127	/**
110	* sets the name of the named range	128	* sets the name of the named range
111	* @param nameName named range name to set	129	* @param nameName named range name to set
Lines 133-138 Link Here
133		151
134	/**	152	/**
135	* sets the sheet name which this named range referenced to	153	* sets the sheet name which this named range referenced to
		154	* Names associated to specific sheets are assigned in the built-in names
136	* @param sheetName the sheet name of the reference	155	* @param sheetName the sheet name of the reference
137	*/	156	*/
138		157
Lines 148-153 Link Here
148		167
149	/**	168	/**
150	* sets the reference of this named range	169	* sets the reference of this named range
		170	* <p>
		171	* Excel Expects a Range, even for single cell references,
		172	* <p>
		173	* i.e. $A should be $A:$A
151	* @param ref the reference to set	174	* @param ref the reference to set
152	*/	175	*/
153		176
Lines 161-171 Link Here
161	setSheetName(sheetName);	184	setSheetName(sheetName);
162	}	185	}
163		186
164	if (ra.getFromCell().equals(ra.getToCell()) == false) {	187
165	name.setAreaReference(ra.getFromCell() + ":" + ra.getToCell());	188	name.setAreaReference(ref);
166	} else {
167	name.setAreaReference(ra.getFromCell());
168	}
169		189
170	}	190	}
171		191

Lines 137-143 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (-3 / +130 lines)
137	private POIFSFileSystem poifs;	137	private POIFSFileSystem poifs;
138		138
139	private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);	139	private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
140		140
141	/**	141	/**
142	* Creates new HSSFWorkbook from scratch (start here!)	142	* Creates new HSSFWorkbook from scratch (start here!)
143	*	143	*
Lines 206-212 Link Here
206	}	206	}
207		207
208	for (int i = 0 ; i < workbook.getNumNames() ; ++i){	208	for (int i = 0 ; i < workbook.getNumNames() ; ++i){
209	HSSFName name = new HSSFName(workbook, workbook.getNameRecord(i));	209	NameRecord nameRecord = workbook.getNameRecord(i);
		210	//if more types we should implement a factory for this in the
		211	//org.apache.poi.hssf.usermodel package if it fits in there
		212	//generate the correct class for storage because they are serialized/presented
		213	//differently but are inherently names
		214
		215	HSSFName name = null;
		216
		217	if (!nameRecord.isBuiltInName())
		218	name = new HSSFName(workbook, nameRecord);
		219	else {
		220	switch (nameRecord.getBuiltInName())
		221	{
		222	case NameRecord.BUILTIN_PRINT_TITLE:
		223	name = new HSSFPrintTitles(workbook, nameRecord);
		224	break;
		225	default :
		226	name = new HSSFName(workbook, nameRecord);
		227	}
		228	}
		229
210	names.add(name);	230	names.add(name);
211	}	231	}
212	}	232	}
Lines 235-240 Link Here
235	}	255	}
236		256
237	/**	257	/**
		258	*
		259	* @param sheetIndex
		260	* @param reference
		261	*/
		262	public void setPrintArea(int sheetIndex, String reference)
		263	{
		264	HSSFName retrievedName = this.getBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
		265	if (retrievedName == null)
		266	retrievedName = this.createBuiltinName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
		267
		268	retrievedName.setReference(reference);
		269	}
		270
		271	/**
238	* used internally to set the workbook properties.	272	* used internally to set the workbook properties.
239	*/	273	*/
240		274
Lines 649-654 Link Here
649	* @return named range high level	683	* @return named range high level
650	*/	684	*/
651	public HSSFName getNameAt(int index){	685	public HSSFName getNameAt(int index){
		686	if (index == -1) throw new IllegalArgumentException("Invalid Name Index");
652	HSSFName result = (HSSFName) names.get(index);	687	HSSFName result = (HSSFName) names.get(index);
653		688
654	return result;	689	return result;
Lines 665-670 Link Here
665	}	700	}
666		701
667		702
		703
		704	/**
		705	* Method getBuiltInName.
		706	* @param name
		707	* @param sheetIndex Zero-based index
		708	* @return HSSFName
		709	*/
		710	protected HSSFName getBuiltInName(byte name, int sheetIndex)
		711	{
		712	Iterator iterator = this.names.iterator();
		713
		714	while (iterator.hasNext())
		715	{
		716	HSSFName retrievedName = (HSSFName)iterator.next();
		717	if (retrievedName.getBuiltInName() == name && retrievedName.getSheetIndex()==sheetIndex+1)
		718	return retrievedName;
		719
		720	}
		721
		722	return null;
		723	}
		724
		725	/** Gets the PrintArea HSSFName object at the specified sheet index
		726	* @param sheetIndex zero-based sheet index
		727	* @return Null if no print area is defined
		728	*/
		729	public String getPrintArea(int sheetIndex)
		730	{
		731	HSSFName retrievedName =
		732	this.getBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex);
		733
		734	if (retrievedName == null) return null;
		735
		736	return retrievedName.getReference();
		737	}
		738
		739
		740	/**
		741	* <p>
		742	* Set the repeating rows or columns when printing
		743	* @param sheetIndex zero-based sheet index
		744	* @return Null if no print titles is defined for the sheet
		745	*/
		746	public HSSFPrintTitles getPrintTitles(int sheetIndex)
		747	{
		748	HSSFName name = this.getBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
		749
		750	if (name != null && !(name instanceof HSSFPrintTitles))
		751	{
		752	//for read support
		753	}
		754
		755	return (HSSFPrintTitles)name;
		756	}
		757
668	/** creates a new named range and add it to the model	758	/** creates a new named range and add it to the model
669	* @return named range high level	759	* @return named range high level
670	*/	760	*/
Lines 677-682 Link Here
677		767
678	return newName;	768	return newName;
679	}	769	}
		770
		771	/**Create a HSSFName object for the specified built-in name
		772	* @param name the byte representation of the built-in name to generate
		773	* @param index the zero based index for the sheet
		774	* @return new Name object if the printArea does not already exist
		775	*/
		776	protected HSSFName createBuiltinName(byte name, int index)
		777	{
		778	NameRecord nameRecord = workbook.createBuiltInName(name, index);
		779
		780	HSSFName newName = new HSSFName(workbook, nameRecord);
		781	names.add(newName);
		782
		783	return newName;
		784
		785	}
		786
		787	/**Create PrintTitles for the specified sheet.
		788	* Global PrintTitles do not make sense so a sheet is required.
		789	* @param index the zero based index for the sheet
		790	* @return new Name object if the PrintTitle does not already exist
		791	*/
		792	public HSSFPrintTitles createPrintTitles(int index)
		793	{
		794	NameRecord titleRecord = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_TITLE, index);
		795
		796	if (titleRecord == null)
		797	titleRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, index);
		798
		799	HSSFPrintTitles printTitles = new HSSFPrintTitles(workbook, titleRecord);
		800
		801	names.add(printTitles);
		802
		803	return printTitles;
		804
		805	}
		806
680		807
681	/** gets the named range index by his name	808	/** gets the named range index by his name
682	* @param name named range name	809	* @param name named range name
Lines 811-815 Link Here
811	UnknownRecord r = new UnknownRecord((short)0x00EB,(short)0x005a, data);	938	UnknownRecord r = new UnknownRecord((short)0x00EB,(short)0x005a, data);
812	workbook.getRecords().add(loc, r);	939	workbook.getRecords().add(loc, r);
813	}	940	}
814		941
815	}	942	}

Lines 56-108 Link Here

(-)src/java/org/apache/poi/hssf/util/AreaReference.java (-45 / +218 lines)
56		56
57	public class AreaReference {	57	public class AreaReference {
58		58
		59	/**
		60	* Internal Representation used to represent the Maximum Column when dealing
		61	* with ranges defining only rows
		62	*/
		63	public static final String MAXIMUM_COLUMN = "IV";
		64
		65
		66	/**
		67	* Internal Representation used to represent the Maximum Column when dealing
		68	* with ranges defining only columns
		69	*/
		70
		71	public static final String MAXIMUM_CELL_REFERENCE_ROW = "65536";
59		72
60	private CellReference [] cells;	73	private CellReference [] cells;
61	private int dim;	74	private int dim;
62		75
63	/** Create an area ref from a string representation	76
64	*/	77	/** Create an area ref from a string representation
65	public AreaReference(String reference) {	78	*/
66	String[] refs = seperateAreaRefs(reference);	79	public AreaReference(String reference) {
67	dim = refs.length;	80	reference = processRowColOnlyRefs(reference);
68	cells = new CellReference[dim];	81	String[] refs = seperateAreaRefs(reference);
69	for (int i=0;i<dim;i++) {	82	dim = refs.length;
70	cells[i]=new CellReference(refs[i]);	83	cells = new CellReference[dim];
71	}	84	for (int i=0;i<dim;i++) {
72	}	85	cells[i]=new CellReference(refs[i]);
73	//not sure if we need to be flexible here!	86	}
74	/** return the dimensions of this area	87	}
75	**/	88	//not sure if we need to be flexible here!
76	public int getDim() {	89	/** return the dimensions of this area
77	return dim;	90	**/
78	}	91	public int getDim() {
79	/** return the cell references that define this area */	92	return dim;
80	public CellReference[] getCells() {	93	}
81	return cells;	94	/** return the cell references that define this area */
82	}	95	public CellReference[] getCells() {
83		96	return cells;
84	public String toString() {	97	}
85	StringBuffer retval = new StringBuffer();	98
86	for (int i=0;i<dim;i++){	99	public String toString() {
87	retval.append(':');	100	StringBuffer retval = new StringBuffer();
88	retval.append(cells[i].toString());	101	for (int i=0;i<dim;i++){
89	}	102	retval.append(':');
90	retval.deleteCharAt(0);	103	retval.append(cells[i].toString());
91	return retval.toString();	104	}
92	}	105	retval.deleteCharAt(0);
93		106	return retval.toString();
94	/**	107	}
95	* seperates Area refs in two parts and returns them as seperate elements in a	108
96	* String array	109	/**
97	*/	110	* seperates Area refs in two parts and returns them as seperate elements in a
98	private String[] seperateAreaRefs(String reference) {	111	* String array
99	String retval[] = new String[2];	112	*/
100	int length = reference.length();	113	private String[] seperateAreaRefs(String reference) {
101		114	String retval[] = new String[2];
102	int loc = reference.indexOf(':',0);	115	int length = reference.length();
103		116
104	retval[0] = reference.substring(0,loc);	117	int loc = reference.indexOf(':',0);
105	retval[1] = reference.substring(loc+1);	118
106	return retval;	119	retval[0] = reference.substring(0,loc);
107	}	120	retval[1] = reference.substring(loc+1);
		121	return retval;
		122	}
		123
		124	/**Processes (absolute and relative) Row Only or Column Only references so the formula packages can understand them
		125	* <p>
		126	* eg. $1:$2 => $A$1:$IV$2
		127	* <p>
		128	* eg. $C:$C => $C$1:$C$65535
		129	* <p>
		130	* <b>Note : </b>One absolute reference converts the whole to an absolute reference
		131	* @param reference the reference to check
		132	* @param isRowCheck True indicates a row check, false indicates a column check
		133	* @return Processed internal representation of the reference if it is a row-only
		134	* reference, otherwise the same is returned
		135	*
		136	*/
		137	protected String processRowColOnlyRefs(final String reference)
		138	{
		139
		140	boolean isRowCheck = false;
		141	boolean isRowOnly = isRowOnlyReference(reference);
		142	boolean isColOnly = isColOnlyReference(reference);
		143	boolean isAbsolute = (reference.indexOf("$") > -1);
		144
		145	//not a row only or col only reference
		146	if (!isRowOnly && !isColOnly) return reference;
		147
		148	isRowCheck = isRowOnly;
		149
		150	String[] parsedRefs = new String[0];
		151	StringBuffer returnValue = new StringBuffer();
		152	String sheet = null;
		153	int sheetIndex = reference.indexOf('!');
		154
		155	if (sheetIndex > 0) {
		156	sheet = reference.substring(0, sheetIndex);
		157	returnValue.append(sheet).append("!");
		158	}
		159
		160	parsedRefs = seperateAreaRefs((sheetIndex > 0) ? reference.substring(sheetIndex+1) : reference);
		161
		162	if (parsedRefs.length == 2){
		163	for (int i = 0; i < parsedRefs.length; i++)
		164	{
		165	String cellRef = removeAbsolutes(parsedRefs[i]);
		166	boolean isFirst = (i == 0);
		167
		168	if (!isFirst) returnValue.append(":");
		169
		170	if (isRowCheck) {
		171	returnValue.append(processRowReference(cellRef, isFirst, isAbsolute));
		172	} else {
		173	returnValue.append(processColReference(cellRef, isFirst, isAbsolute));
		174	}
		175	}
		176	}
		177
		178	return returnValue.toString();
		179	}
		180
		181	/**Determines if the cell reference points to a row only
		182	* @return true if the reference (absolute or not) refers to a row only
		183	*/
		184	public static boolean isRowOnlyReference(final String reference)
185	{
186	int start = reference.indexOf("!") + 1;
187	char[] chars = reference.toCharArray();
188	int loc = start;
189	if (chars[loc]=='$') loc++;
190	for (; loc < chars.length; loc++) {
191	if (Character.isDigit(chars[loc]) \|\| chars[loc] == '$' \|\| chars[loc] == ':') {
192	continue;
193	} else {
194	//not a number or absolute reference
195	return false;
196	}
197	}
198	return true;
199	}
200
201	/**Determines if the cell reference points to a Column only
202	* @return true if the reference (absolute or not) refers to a Column only
203	*/
204	public static boolean isColOnlyReference(final String reference)
205	{
206	int start = reference.indexOf("!") + 1;
207	char[] chars = reference.toCharArray();
208	int loc = start;
209	if (chars[loc]=='$') loc++;
210	for (; loc < chars.length; loc++) {
211	if (Character.isLetter(chars[loc]) \|\| chars[loc] == '$' \|\| chars[loc] == ':') {
212	continue;
213	} else {
214	//not a number or absolute reference
215	return false;
216	}
217	}
218	return true;
219	}
220
221
222	/** Removes the absolute references for processing
223	* @return reference with the "$" characters
224	*/
225	public static String removeAbsolutes(String reference)
226	{
227	StringBuffer sb = new StringBuffer(reference);
228
229	int index = 0;
230
231	while ( index < sb.length())
232	{
233	char ch = sb.charAt(index);
234	if (ch == '$') {
235	sb.deleteCharAt(index);
236	} else {
237	index++;
238	}
239	}
240
241	return sb.toString();
242	}
243
244	/**Converts the row reference to a proper internal form
245	* <p>
246	* For a row only reference, the first reference should be the first column
247	* and the second reference, the maximum column in the first row.
248	* <p>
249	* eg. $1:$2 => $A$1:$IV$2
250	* @param rowReference number only row reference
251	* @param isFirst if the cell reference is the first in the range
252	* @param isAbsolute return an absolute reference
253	* @return reference
254	*/
255	protected String processRowReference(String rowReference, boolean isFirst, boolean isAbsolute)
256	{
257	String absolute = (isAbsolute) ? "$" : "";
258
259	if (isFirst) return absolute+"A"+absolute+rowReference;
260	else return absolute+AreaReference.MAXIMUM_COLUMN+absolute+rowReference;
261	}
262
263	/**Converts the col reference to a proper internal form
264	* <p>
265	* For a col only reference, the first reference should be the column, first row
266	* and the second reference, the column, maximum row
267	* <p>
268	* eg. $A:$A => $A$1:$A$65535
269	* @param rowReference number only row reference
270	* @param isFirst if the cell reference is the first in the range
271	* @param isAbsolute return an absolute reference
272	* @return an absolute reference
273	*/
274	protected String processColReference(String colReference, boolean isFirst, boolean isAbsolute)
275	{
276	String absolute = (isAbsolute) ? "$" : "";
277
278	if (isFirst) return absolute+colReference+absolute+"1";
279	else return absolute+colReference+absolute+AreaReference.MAXIMUM_CELL_REFERENCE_ROW;
280	}
108	}	281	}

Lines 1-394 Link Here

(-)src/java/org/apache/poi/hssf/util/RangeAddress.java (-394 / +395 lines)
1	package org.apache.poi.hssf.util;	1	package org.apache.poi.hssf.util;
2		2
3	/* ====================================================================	3	/* ====================================================================
4	* The Apache Software License, Version 1.1	4	* The Apache Software License, Version 1.1
5	*	5	*
6	* Copyright (c) 2002 The Apache Software Foundation. All rights	6	* Copyright (c) 2002 The Apache Software Foundation. All rights
7	* reserved.	7	* reserved.
8	*	8	*
9	* Redistribution and use in source and binary forms, with or without	9	* Redistribution and use in source and binary forms, with or without
10	* modification, are permitted provided that the following conditions	10	* modification, are permitted provided that the following conditions
11	* are met:	11	* are met:
12	*	12	*
13	* 1. Redistributions of source code must retain the above copyright	13	* 1. Redistributions of source code must retain the above copyright
14	* notice, this list of conditions and the following disclaimer.	14	* notice, this list of conditions and the following disclaimer.
15	*	15	*
16	* 2. Redistributions in binary form must reproduce the above copyright	16	* 2. Redistributions in binary form must reproduce the above copyright
17	* notice, this list of conditions and the following disclaimer in	17	* notice, this list of conditions and the following disclaimer in
18	* the documentation and/or other materials provided with the	18	* the documentation and/or other materials provided with the
19	* distribution.	19	* distribution.
20	*	20	*
21	* 3. The end-user documentation included with the redistribution,	21	* 3. The end-user documentation included with the redistribution,
22	* if any, must include the following acknowledgment:	22	* if any, must include the following acknowledgment:
23	* "This product includes software developed by the	23	* "This product includes software developed by the
24	* Apache Software Foundation (http://www.apache.org/)."	24	* Apache Software Foundation (http://www.apache.org/)."
25	* Alternately, this acknowledgment may appear in the software itself,	25	* Alternately, this acknowledgment may appear in the software itself,
26	* if and wherever such third-party acknowledgments normally appear.	26	* if and wherever such third-party acknowledgments normally appear.
27	*	27	*
28	* 4. The names "Apache" and "Apache Software Foundation" and	28	* 4. The names "Apache" and "Apache Software Foundation" and
29	* "Apache POI" must not be used to endorse or promote products	29	* "Apache POI" must not be used to endorse or promote products
30	* derived from this software without prior written permission. For	30	* derived from this software without prior written permission. For
31	* written permission, please contact apache@apache.org.	31	* written permission, please contact apache@apache.org.
32	*	32	*
33	* 5. Products derived from this software may not be called "Apache",	33	* 5. Products derived from this software may not be called "Apache",
34	* "Apache POI", nor may "Apache" appear in their name, without	34	* "Apache POI", nor may "Apache" appear in their name, without
35	* prior written permission of the Apache Software Foundation.	35	* prior written permission of the Apache Software Foundation.
36	*	36	*
37	* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED	37	* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
38	* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES	38	* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
39	* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE	39	* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
40	* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR	40	* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
41	* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,	41	* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
42	* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT	42	* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
43	* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF	43	* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
44	* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND	44	* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
45	* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,	45	* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
46	* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT	46	* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
47	* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF	47	* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
48	* SUCH DAMAGE.	48	* SUCH DAMAGE.
49	* ====================================================================	49	* ====================================================================
50	*	50	*
51	* This software consists of voluntary contributions made by many	51	* This software consists of voluntary contributions made by many
52	* individuals on behalf of the Apache Software Foundation. For more	52	* individuals on behalf of the Apache Software Foundation. For more
53	* information on the Apache Software Foundation, please see	53	* information on the Apache Software Foundation, please see
54	* <http://www.apache.org/>.	54	* <http://www.apache.org/>.
55	*/	55	*/
56		56
57		57
58	/**	58	/**
59	* Title: Range Address <P>	59	* Title: Range Address <P>
60	* Description: provides connectivity utilities for ranges<P>	60	* Description: provides connectivity utilities for ranges<P>
61	*	61	*
62	*	62	*
63	* REFERENCE: <P>	63	* REFERENCE: <P>
64	* @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)	64	* @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)
65	* @version 1.0	65	* @version 1.0
66	*/	66	*/
67		67
68	public class RangeAddress {	68	public class RangeAddress {
69	final static int WRONG_POS = -1;	69	final static int WRONG_POS = -1;
70	final static int MAX_HEIGHT = 66666;	70	final static int MAX_HEIGHT = 66666;
71	final static char SO_FORMNAME_ENCLOSURE = '\'';	71	final static char SO_FORMNAME_ENCLOSURE = '\'';
72	String m_sheetName;	72	String m_sheetName;
73	String m_cellFrom;	73	String m_cellFrom;
74	String m_cellTo;	74	String m_cellTo;
75		75
76	public RangeAddress (String _url) {	76	public RangeAddress (String _url) {
77	init (_url);	77	init (_url);
78	}	78	}
79		79
80	public RangeAddress (int _startCol, int _startRow, int _endCol, int _endRow) {	80	public RangeAddress (int _startCol, int _startRow, int _endCol, int _endRow) {
81	init (numTo26Sys (_startCol) + _startRow + ":"	81	init (numTo26Sys (_startCol) + _startRow + ":"
82	+ numTo26Sys (_endCol) + _endRow);	82	+ numTo26Sys (_endCol) + _endRow);
83	}	83	}
84		84
85	public String getAddress (){	85	public String getAddress (){
86	String result = "";	86	String result = "";
87	if(m_sheetName != null)	87	if(m_sheetName != null)
88	result += m_sheetName;	88	result += m_sheetName;
89	if(m_cellFrom != null){	89	if(m_cellFrom != null){
90	result += m_cellFrom;	90	result += m_cellFrom;
91	if(m_cellTo != null)	91	if(m_cellTo != null)
92	result += ":" + m_cellTo;	92	result += ":" + m_cellTo;
93	}	93	}
94	return result;	94	return result;
95	}	95	}
96		96
97	public String getSheetName (){	97	public String getSheetName (){
98	return m_sheetName;	98	return m_sheetName;
99	}	99	}
100		100
101	public String getRange (){	101	public String getRange (){
102	String result = "";	102	String result = "";
103	if(m_cellFrom != null){	103	if(m_cellFrom != null){
104	result += m_cellFrom;	104	result += m_cellFrom;
105	if(m_cellTo != null)	105	if(m_cellTo != null)
106	result += ":" + m_cellTo;	106	result += ":" + m_cellTo;
107	}	107	}
108	return result;	108	return result;
109	}	109	}
110		110
111	public boolean isCellOk (String _cell){	111	public boolean isCellOk (String _cell){
112	if (_cell != null){	112	if (_cell != null){
113	if ( (getYPosition (_cell) != WRONG_POS) &&	113	if ( (getYPosition (_cell) != WRONG_POS) &&
114	(getXPosition (_cell) != WRONG_POS) )	114	(getXPosition (_cell) != WRONG_POS) )
115	return true;	115	return true;
116	else	116	else
117	return false;	117	return false;
118	} else	118	} else
119	return false;	119	return false;
120	}	120	}
121		121
122	public boolean isSheetNameOk (){	122	public boolean isSheetNameOk (){
123	return isSheetNameOk (m_sheetName);	123	return isSheetNameOk (m_sheetName);
124	}	124	}
125		125
126	private static boolean intern_isSheetNameOk (String _sheetName, boolean _canBeWaitSpace){	126	private static boolean intern_isSheetNameOk (String _sheetName, boolean _canBeWaitSpace){
127	for (int i = 0 ; i < _sheetName.length (); i++){	127	for (int i = 0 ; i < _sheetName.length (); i++){
128	char ch = _sheetName.charAt (i);	128	char ch = _sheetName.charAt (i);
129	if (! (Character.isLetterOrDigit (ch) \|\| (ch == '_')\|\|	129	if (! (Character.isLetterOrDigit (ch) \|\| (ch == '_')\|\|
130	_canBeWaitSpace&&(ch == ' '))){	130	_canBeWaitSpace&&(ch == ' '))){
131	return false;	131	return false;
132	}	132	}
133	}	133	}
134	return true;	134	return true;
135	}	135	}
136		136
137	public static boolean isSheetNameOk (String _sheetName){	137	public static boolean isSheetNameOk (String _sheetName){
138	boolean res = false;	138	boolean res = false;
139	if ( ( _sheetName != null) && !_sheetName.equals ("")){	139	if ( ( _sheetName != null) && !_sheetName.equals ("")){
140	res = intern_isSheetNameOk (_sheetName,true);	140	res = intern_isSheetNameOk (_sheetName,true);
141	}else	141	}else
142	res = true;	142	res = true;
143	return res;	143	return res;
144	}	144	}
145		145
146		146
147	public String getFromCell (){	147	public String getFromCell (){
148	return m_cellFrom;	148	return m_cellFrom;
149	}	149	}
150		150
151	public String getToCell (){	151	public String getToCell (){
152	return m_cellTo;	152	return m_cellTo;
153	}	153	}
154		154
155	public int getWidth (){	155	public int getWidth (){
156	if(m_cellFrom != null && m_cellTo != null){	156	if(m_cellFrom != null && m_cellTo != null){
157	int toX = getXPosition (m_cellTo);	157	int toX = getXPosition (m_cellTo);
158	int fromX = getXPosition (m_cellFrom);	158	int fromX = getXPosition (m_cellFrom);
159	if ((toX == WRONG_POS) \|\| (fromX == WRONG_POS)){	159	if ((toX == WRONG_POS) \|\| (fromX == WRONG_POS)){
160	return 0;	160	return 0;
161	}else	161	}else
162	return toX - fromX + 1;	162	return toX - fromX + 1;
163	}	163	}
164	return 0;	164	return 0;
165	}	165	}
166		166
167	public int getHeight (){	167	public int getHeight (){
168	if(m_cellFrom != null && m_cellTo != null){	168	if(m_cellFrom != null && m_cellTo != null){
169	int toY = getYPosition (m_cellTo);	169	int toY = getYPosition (m_cellTo);
170	int fromY = getYPosition (m_cellFrom);	170	int fromY = getYPosition (m_cellFrom);
171	if ((toY == WRONG_POS) \|\| (fromY == WRONG_POS)){	171	if ((toY == WRONG_POS) \|\| (fromY == WRONG_POS)){
172	return 0;	172	return 0;
173	}else	173	}else
174	return toY - fromY + 1;	174	return toY - fromY + 1;
175	}	175	}
176	return 0;	176	return 0;
177	}	177	}
178		178
179	public void setSize (int _width, int _height){	179	public void setSize (int _width, int _height){
180	if(m_cellFrom == null)	180	if(m_cellFrom == null)
181	m_cellFrom = "a1";	181	m_cellFrom = "a1";
182	int tlX, tlY, rbX, rbY;	182	int tlX, tlY, rbX, rbY;
183	tlX = getXPosition (m_cellFrom);	183	tlX = getXPosition (m_cellFrom);
184	tlY = getYPosition (m_cellFrom);	184	tlY = getYPosition (m_cellFrom);
185	m_cellTo = numTo26Sys (tlX + _width - 1);	185	m_cellTo = numTo26Sys (tlX + _width - 1);
186	m_cellTo += String.valueOf (tlY + _height - 1);	186	m_cellTo += String.valueOf (tlY + _height - 1);
187	}	187	}
188		188
189	public boolean hasSheetName (){	189	public boolean hasSheetName (){
190	if(m_sheetName == null)	190	if(m_sheetName == null)
191	return false;	191	return false;
192	return true;	192	return true;
193	}	193	}
194		194
195	public boolean hasRange (){	195	public boolean hasRange (){
196	if(m_cellFrom == null \|\| m_cellTo == null)	196	if(m_cellFrom == null \|\| m_cellTo == null)
197	return false;	197	return false;
198	return true;	198	return true;
199	}	199	}
200		200
201	public boolean hasCell (){	201	public boolean hasCell (){
202	if(m_cellFrom == null)	202	if(m_cellFrom == null)
203	return false;	203	return false;
204	return true;	204	return true;
205	}	205	}
206		206
207	private void init (String _url){	207	private void init (String _url){
208		208
209	_url = removeString(_url, "$");	209	_url = removeString(_url, "$");
210	_url = removeString(_url, "'");	210	_url = removeString(_url, "'");
211		211
212	String[] urls = parseURL (_url);	212	String[] urls = parseURL (_url);
213	m_sheetName = urls[0];	213	m_sheetName = urls[0];
214	m_cellFrom = urls[1];	214	m_cellFrom = urls[1];
215	m_cellTo = urls[2];	215	m_cellTo = urls[2];
216		216
217	//What if range is one celled ?	217	//What if range is one celled ?
218	if (m_cellTo == null){	218	if (m_cellTo == null){
219	m_cellTo = m_cellFrom;	219	m_cellTo = m_cellFrom;
220	}	220	}
221		221
222	//Removing noneeds characters	222	//Removing noneeds characters
223	m_cellTo = removeString(m_cellTo,".");	223	m_cellTo = removeString(m_cellTo,".");
224		224
225		225
226	}	226	}
227		227
228	private String[] parseURL (String _url){	228	private String[] parseURL (String _url){
229	String[] result = new String[3];	229	String[] result = new String[3];
230	int index = _url.indexOf(':');	230	int index = _url.indexOf(':');
231	if (index >= 0) {	231	if (index >= 0) {
232	String fromStr = _url.substring(0, index);	232	String fromStr = _url.substring(0, index);
233	String toStr = _url.substring(index+1);	233	String toStr = _url.substring(index+1);
234	index = fromStr.indexOf('.');	234	index = fromStr.indexOf('!');
235	if (index >= 0) {	235	if (index >= 0) {
236	result[0] = fromStr.substring(0, index);	236	result[0] = fromStr.substring(0, index);
237	result[1] = fromStr.substring(index+1);	237	result[1] = fromStr.substring(index+1);
238	} else {	238	} else {
239	result[1] = fromStr;	239	result[1] = fromStr;
240	}	240	}
241	index = toStr.indexOf('.');	241	index = toStr.indexOf('!');
242	if (index >= 0) {	242	if (index >= 0) {
243	result[2] = toStr.substring(index+1);	243	result[2] = toStr.substring(index+1);
244	} else {	244	} else {
245	result[2] = toStr;	245	result[2] = toStr;
246	}	246	}
247	} else {	247	} else {
248	index = _url.indexOf('.');	248	index = _url.indexOf('!');
249	if (index >= 0) {	249	if (index >= 0) {
250	result[0] = _url.substring(0, index);	250	result[0] = _url.substring(0, index);
251	result[1] = _url.substring(index+1);	251	result[1] = _url.substring(index+1);
252	} else {	252	} else {
253	result[1] = _url;	253	result[1] = _url;
254	}	254	}
255	}	255	}
256	return result;	256	return result;
257	}	257	}
258		258
259	public int getYPosition (String _subrange){	259	public int getYPosition (String _subrange){
260	int result = WRONG_POS;	260	int result = WRONG_POS;
261	_subrange = _subrange.trim ();	261	_subrange = _subrange.trim ();
262	if (_subrange.length () != 0){	262	if (_subrange.length () != 0){
263	String digitstr = getDigitPart (_subrange);	263	String digitstr = getDigitPart (_subrange);
264	try {	264	try {
265	result = Integer.parseInt (digitstr);	265	result = Integer.parseInt (digitstr);
266	if (result > MAX_HEIGHT){	266	if (result > MAX_HEIGHT){
267	result = WRONG_POS;	267	result = WRONG_POS;
268	}	268	}
269	}	269	}
270	catch (Exception ex) {	270	catch (Exception ex) {
271		271
272	result = WRONG_POS;	272	result = WRONG_POS;
273	}	273	}
274	}	274	}
275	return result;	275	return result;
276	}	276	}
277		277
278	private static boolean isLetter (String _str){	278	private static boolean isLetter (String _str){
279	boolean res = true;	279	boolean res = true;
280	if ( !_str.equals ("") ){	280	if ( !_str.equals ("") ){
281	for (int i = 0 ; i < _str.length (); i++){	281	for (int i = 0 ; i < _str.length (); i++){
282	char ch = _str.charAt (i);	282	char ch = _str.charAt (i);
283	if (! Character.isLetter (ch)){	283	if (! Character.isLetter (ch)){
284	res = false;	284	res = false;
285	break;	285	break;
286	}	286	}
287	}	287	}
288	}else	288	}else
289	res = false;	289	res = false;
290	return res;	290	return res;
291	}	291	}
292		292
293	public int getXPosition (String _subrange){	293	public int getXPosition (String _subrange){
294	int result = WRONG_POS;	294	int result = WRONG_POS;
295	String tmp = filter$ (_subrange);	295	String tmp = filter$ (_subrange);
296	tmp = this.getCharPart (_subrange);	296	tmp = this.getCharPart (_subrange);
297	// we will process only 2 letters ranges	297	// we will process only 2 letters ranges
298	if (isLetter (tmp) && ((tmp.length () == 2)\|\| (tmp.length () == 1) )){	298	if (isLetter (tmp) && ((tmp.length () == 2)\|\| (tmp.length () == 1) )){
299	result = get26Sys (tmp);	299	result = get26Sys (tmp);
300	}	300	}
301	return result;	301	return result;
302	}	302	}
303		303
304	public String getDigitPart (String _value){	304	public String getDigitPart (String _value){
305	String result = "";	305	String result = "";
306	int digitpos = getFirstDigitPosition (_value);	306	int digitpos = getFirstDigitPosition (_value);
307	if(digitpos >= 0){	307	if(digitpos >= 0){
308	result = _value.substring (digitpos);	308	result = _value.substring (digitpos);
309	}	309	}
310	return result;	310	return result;
311	}	311	}
312		312
313	public String getCharPart (String _value){	313	public String getCharPart (String _value){
314	String result = "";	314	String result = "";
315	int digitpos = getFirstDigitPosition (_value);	315	int digitpos = getFirstDigitPosition (_value);
316	if(digitpos >= 0){	316	if(digitpos >= 0){
317	result = _value.substring (0, digitpos);	317	result = _value.substring (0, digitpos);
318	}	318	}
319	return result;	319	return result;
320	}	320	}
321		321
322	private String filter$ (String _range){	322	private String filter$ (String _range){
323	String res = "";	323	String res = "";
324	for (int i = 0 ; i < _range.length () ; i++){	324	for (int i = 0 ; i < _range.length () ; i++){
325	char ch = _range.charAt (i);	325	char ch = _range.charAt (i);
326	if ( ch != '$' ){	326	if ( ch != '$' ){
327	res = res + ch;	327	res = res + ch;
328	}	328	}
329	}	329	}
330	return res;	330	return res;
331	}	331	}
332		332
333	private int getFirstDigitPosition (String _value){	333	private int getFirstDigitPosition (String _value){
334	int result = WRONG_POS;	334	int result = WRONG_POS;
335	if(_value != null && _value.trim ().length () == 0){	335	if(_value != null && _value.trim ().length () == 0){
336	return result;	336	return result;
337	}	337	}
338	_value = _value.trim ();	338	_value = _value.trim ();
339	int length = _value.length ();	339	int length = _value.length ();
340	for(int i = 0; i < length; i++){	340	for(int i = 0; i < length; i++){
341	if(Character.isDigit (_value.charAt (i))){	341	if(Character.isDigit (_value.charAt (i))){
342	result = i;	342	result = i;
343	break;	343	break;
344	}	344	}
345	}	345	}
346	return result;	346	return result;
347	}	347	}
348		348
349	public int get26Sys (String _s){	349	public int get26Sys (String _s){
350	int sum = 0;	350	int sum = 0;
351	int multiplier = 1;	351	int multiplier = 1;
352	if (_s != "") {	352	if (_s != "") {
353	for (int i = _s.length ()-1 ; i >= 0 ; i--){	353	for (int i = _s.length ()-1 ; i >= 0 ; i--){
354	char ch = _s.charAt (i);	354	char ch = _s.charAt (i);
355	int val = Character.getNumericValue (ch) - Character.getNumericValue ('A')+1;	355	int val = Character.getNumericValue (ch) - Character.getNumericValue ('A')+1;
356	sum = sum + val * multiplier;	356	sum = sum + val * multiplier;
357	multiplier = multiplier * 26;	357	multiplier = multiplier * 26;
358	}	358	}
359	return sum;	359	return sum;
360	}	360	}
361	return WRONG_POS;	361	return WRONG_POS;
362	}	362	}
363		363
364	public String numTo26Sys (int _num){	364	public String numTo26Sys (int _num){
365	int sum = 0;	365	int sum = 0;
366	int reminder;	366	int reminder;
367	String s ="";	367	String s ="";
368	do{	368	do{
369	_num --;	369	_num --;
370	reminder = _num % 26;	370	reminder = _num % 26;
371	int val = 65 + reminder;	371	int val = 65 + reminder;
372	_num = _num / 26;	372	_num = _num / 26;
373	s = (char)val + s; // reverce	373	s = (char)val + s; // reverce
374	}while(_num > 0);	374	}while(_num > 0);
375	return s;	375	return s;
376	}	376	}
377		377
378	public String replaceString(String _source , String _oldPattern,	378	public String replaceString(String _source , String _oldPattern,
379	String _newPattern){	379	String _newPattern){
380	StringBuffer res = new StringBuffer(_source);	380	StringBuffer res = new StringBuffer(_source);
381	int pos = -1;	381	int pos = -1;
382		382
383	while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1){	383	while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1){
384	res.replace(pos, pos + _oldPattern.length(), _newPattern);	384	res.replace(pos, pos + _oldPattern.length(), _newPattern);
385	}	385	}
386		386
387	return res.toString();	387	return res.toString();
388	}	388	}
389		389
390	public String removeString(String _source, String _match){	390	public String removeString(String _source, String _match){
391	return replaceString(_source, _match, "");	391	return replaceString(_source, _match, "");
392	}	392	}
393		393
394	}	394	}
		395

Lines 60-65 Link Here

(-)src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (+181 lines)
60	public TestAreaReference(String s) {	60	public TestAreaReference(String s) {
61	super(s);	61	super(s);
62	}	62	}
		63
		64
		65	public static void main(String [] ignored_args)
		66	{
		67	String filename = System.getProperty("HSSF.testdata.path");
		68
		69	System.out
		70	.println("Testing org.apache.poi.hssf.util.TestAreaReference");
		71	junit.textui.TestRunner.run(TestAreaReference.class);
		72	}
		73
		74	public void testRemoveAbsolutes()
		75	{
		76	assertEquals("Absolutes Removed", "A1:B2", AreaReference.removeAbsolutes("$A$1:$B$2"));
		77	assertEquals("Absolutes Removed", "sheet!A1:B2", AreaReference.removeAbsolutes("sheet!$A$1:$B$2"));
		78	}
		79
		80
63	public void testAreaRef1() {	81	public void testAreaRef1() {
64	AreaReference ar = new AreaReference("$A$1:$B$2");	82	AreaReference ar = new AreaReference("$A$1:$B$2");
65	assertTrue("Two cells expected",ar.getCells().length == 2);	83	assertTrue("Two cells expected",ar.getCells().length == 2);
Lines 77-80 Link Here
77	assertTrue("col is abs",cf.isColAbsolute());	95	assertTrue("col is abs",cf.isColAbsolute());
78	assertTrue("string is $B$2",cf.toString().equals("$B$2"));	96	assertTrue("string is $B$2",cf.toString().equals("$B$2"));
79	}	97	}
		98
		99	/**Tests the static function to see if it correctly interprets the formula
		100	*/
		101	public void testIsRowOnly(){
		102	assertTrue("$1:$2 is a row only formula", AreaReference.isRowOnlyReference("$1:$2"));
		103	assertTrue("$A$1:$2 is not row only formula", !AreaReference.isRowOnlyReference("$A$1:$2"));
		104	assertTrue("$A$1:$B$2 is not row only formula", !AreaReference.isRowOnlyReference("$A$1:$B$2"));
		105
		106	assertTrue("1:2 is a row only formula", AreaReference.isRowOnlyReference("1:2"));
		107	assertTrue("A1:2 is not row only formula", !AreaReference.isRowOnlyReference("A1:2"));
		108	assertTrue("A1:B2 is not row only formula", !AreaReference.isRowOnlyReference("A1:$B2"));
		109
		110	assertTrue("$A:$B is not a row only forumla", !AreaReference.isRowOnlyReference("$A:$B"));
		111	}
		112
		113	/**Tests the static function to see if it correctly interprets the formula
		114	*/
		115	public void testIsRowOnlyWithSheets(){
		116	assertTrue("Sheet1!$1:$2 is a row only formula", AreaReference.isRowOnlyReference("Sheet1!$1:$2"));
		117	assertTrue("Sheet!$A$1:$2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!$A$1:$2"));
		118	assertTrue("Sheet1!$A$1:$B$2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!$A$1:$B$2"));
		119
		120	assertTrue("1:2 is a row only formula", AreaReference.isRowOnlyReference("Sheet1!1:2"));
		121	assertTrue("A1:2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!A1:2"));
		122	assertTrue("A1:B2 is not row only formula", !AreaReference.isRowOnlyReference("Sheet1!A1:$B2"));
		123
		124
		125	assertTrue("$A:$B is not a row only forumla", !AreaReference.isRowOnlyReference("Sheet1!$A:$B"));
		126	}
		127
		128	/**Tests the static function to see if it correctly interprets the formula
		129	*/
		130	public void testIsColOnly(){
		131	assertTrue("$A:$B is a col only formula", AreaReference.isColOnlyReference("$A:$A"));
		132
		133	assertTrue("$A$1:$2 is not col only formula", !AreaReference.isColOnlyReference("$A$1:$2"));
		134	assertTrue("$A$1:$B$2 is not col only formula", !AreaReference.isColOnlyReference("$A$1:$B$2"));
		135
		136	assertTrue("A:B is a col only formula", AreaReference.isColOnlyReference("A:B"));
		137	assertTrue("A1:2 is not col only formula", !AreaReference.isColOnlyReference("A1:2"));
		138	assertTrue("A1:B2 is not col only formula", !AreaReference.isColOnlyReference("A1:$B2"));
		139
		140	assertTrue("$1:$2 is not a col only forumla", !AreaReference.isColOnlyReference("$1:$2"));
		141	}
		142
		143	/**Tests the static function to see if it correctly interprets the formula
		144	*/
		145	public void testIsColOnlyWithSheets(){
		146	assertTrue("$A:$B is a col only formula", AreaReference.isColOnlyReference("Sheet 1!$A:$A"));
		147
		148	assertTrue("$A$1:$2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!$A$1:$2"));
		149	assertTrue("$A$1:$B$2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!$A$1:$B$2"));
		150
		151	assertTrue("A:B is a col only formula", AreaReference.isColOnlyReference("Sheet 1!A:B"));
		152	assertTrue("A1:2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!A1:2"));
		153	assertTrue("A1:B2 is not col only formula", !AreaReference.isColOnlyReference("Sheet 1!A1:$B2"));
		154
		155	assertTrue("$1:$2 is not a col only forumla", !AreaReference.isColOnlyReference("Sheet 1!$1:$2"));
		156	}
		157
		158
		159	/**Generate the correct references for row only references
		160	*/
		161
162	public void testRowReferences()
163	{
164	AreaReference ar = new AreaReference("$1:$1");
165
166	assertTrue("Two cells expected",ar.getCells().length == 2);
167
168	CellReference cf = ar.getCells()[0];
169	assertTrue("row is 0",cf.getRow()==0);
170	assertTrue("col is 0",cf.getCol()==0);
171	assertTrue("row is abs",cf.isRowAbsolute());
172	assertTrue("col is abs",cf.isColAbsolute());
173	assertTrue("string is $A$1",cf.toString().equals("$A$1"));
174
175	cf = ar.getCells()[1];
176	assertTrue("row is 1",cf.getRow()==0);
177	assertTrue("col is 255",cf.getCol()==255);
178	assertTrue("row is abs",cf.isRowAbsolute());
179	assertTrue("col is abs",cf.isColAbsolute());
180	assertTrue("string is $IV$1",cf.toString().equals("$IV$1"));
181
182
183	}
184
185	/**Generate the correct references for row only references
186	*/
187
188	public void testMultipleRowReferences()
189	{
190	AreaReference ar = new AreaReference("$1:$2");
191
192	assertTrue("Two cells expected",ar.getCells().length == 2);
193
194	CellReference cf = ar.getCells()[0];
195	assertTrue("row is 0",cf.getRow()==0);
196	assertTrue("col is 0",cf.getCol()==0);
197	assertTrue("row is abs",cf.isRowAbsolute());
198	assertTrue("col is abs",cf.isColAbsolute());
199	assertTrue("string is $A$1",cf.toString().equals("$A$1"));
200
201	cf = ar.getCells()[1];
202	assertTrue("row is 1",cf.getRow()==1);
203	assertTrue("col is 255",cf.getCol()==255);
204	assertTrue("row is abs",cf.isRowAbsolute());
205	assertTrue("col is abs",cf.isColAbsolute());
206	assertTrue("string is $IV$2",cf.toString().equals("$IV$2"));
207
208
209	}
210
211
212	/**Generate the correct references for column only references
213	*/
214	public void testColReferences()
215	{
216	AreaReference ar = new AreaReference("$A:$A");
217
218	assertTrue("Two cells expected",ar.getCells().length == 2);
219
220	CellReference cf = ar.getCells()[0];
221	assertTrue("row is 0",cf.getRow()==0);
222	assertTrue("col is 0",cf.getCol()==0);
223	assertTrue("row is abs",cf.isRowAbsolute());
224	assertTrue("col is abs",cf.isColAbsolute());
225	assertTrue("string is $A$1",cf.toString().equals("$A$1"));
226
227	cf = ar.getCells()[1];
228	//internal representation is 0 based
229	assertEquals("row number",65535,cf.getRow());
230	assertEquals("last col", 0, cf.getCol());
231	assertTrue("row is abs",cf.isRowAbsolute());
232	assertTrue("col is abs",cf.isColAbsolute());
233	assertEquals("String Reference","$A$"+AreaReference.MAXIMUM_CELL_REFERENCE_ROW,cf.toString());
234
235	}
236
237	/**Generate the correct references for column only references
238	*/
239	public void testMultipleColReferences()
240	{
241	AreaReference ar = new AreaReference("$B:$D");
242
243	assertTrue("Two cells expected",ar.getCells().length == 2);
244
245	CellReference cf = ar.getCells()[0];
246	assertTrue("row is 0",cf.getRow()==0);
247	assertTrue("col is 0",cf.getCol()==1);
248	assertTrue("row is abs",cf.isRowAbsolute());
249	assertTrue("col is abs",cf.isColAbsolute());
250	assertTrue("String Reference",cf.toString().equals("$B$1"));
251
252	cf = ar.getCells()[1];
253	//internal representation is 0 based
254	assertEquals("last row number", 65535, cf.getRow());
255	assertEquals("last col", 3, cf.getCol());
256	assertTrue("row is abs",cf.isRowAbsolute());
257	assertTrue("col is abs",cf.isColAbsolute());
258	assertEquals("String Reference","$D$"+AreaReference.MAXIMUM_CELL_REFERENCE_ROW, cf.toString());
259
260	}
80	}	261	}