Context Navigation

source: trunk/sources/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelAddress.cs @ 14544

Visit:

Last change on this file since 14544 was 12074, checked in by sraggl, 10 years ago
#2341: Added EPPlus-4.0.3 to ExtLibs
File size: 49.1 KB

Rev	Line
[12074]	1	/*******************************************************************************
	2	* You may amend and distribute as you like, but don't remove this header!
	3	*
	4	* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
	5	* See http://www.codeplex.com/EPPlus for details.
	6	*
	7	* Copyright (C) 2011 Jan Källman
	8	*
	9	* This library is free software; you can redistribute it and/or
	10	* modify it under the terms of the GNU Lesser General Public
	11	* License as published by the Free Software Foundation; either
	12	* version 2.1 of the License, or (at your option) any later version.
	13
	14	* This library is distributed in the hope that it will be useful,
	15	* but WITHOUT ANY WARRANTY; without even the implied warranty of
	16	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
	17	* See the GNU Lesser General Public License for more details.
	18	*
	19	* The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
	20	* If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
	21	*
	22	* All code and executables are provided "as is" with no warranty either express or implied.
	23	* The author accepts no liability for any damage or loss of business that this product may cause.
	24	*
	25	* Code change notes:
	26	*
	27	* Author Change Date
	28	*******************************************************************************
	29	* Jan Källman Added 18-MAR-2010
	30	* Jan Källman License changed GPL-->LGPL 2011-12-16
	31	*******************************************************************************/
	32	using System;
	33	using System.Collections.Generic;
	34	using System.Globalization;
	35	using System.Text;
	36	using System.Text.RegularExpressions;
	37
	38	namespace OfficeOpenXml
	39	{
	40	public class ExcelTableAddress
	41	{
	42	public string Name { get; set; }
	43	public string ColumnSpan { get; set; }
	44	public bool IsAll { get; set; }
	45	public bool IsHeader { get; set; }
	46	public bool IsData { get; set; }
	47	public bool IsTotals { get; set; }
	48	public bool IsThisRow { get; set; }
	49	}
	50	/// <summary>
	51	/// A range address
	52	/// </summary>
	53	/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
	54	public class ExcelAddressBase : ExcelCellBase
	55	{
	56	internal protected int _fromRow=-1, _toRow, _fromCol, _toCol;
	57	protected internal bool _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed;
	58	internal protected string _wb;
	59	internal protected string _ws;
	60	internal protected string _address;
	61	internal protected event EventHandler AddressChange;
	62
	63	internal enum eAddressCollition
	64	{
	65	No,
	66	Partly,
	67	Inside,
	68	Equal
	69	}
	70	internal enum eShiftType
	71	{
	72	Right,
	73	Down,
	74	EntireRow,
	75	EntireColumn
	76	}
	77	#region "Constructors"
	78	internal ExcelAddressBase()
	79	{
	80	}
	81	/// <summary>
	82	/// Creates an Address object
	83	/// </summary>
	84	/// <param name="fromRow">start row</param>
	85	/// <param name="fromCol">start column</param>
	86	/// <param name="toRow">End row</param>
	87	/// <param name="toColumn">End column</param>
	88	public ExcelAddressBase(int fromRow, int fromCol, int toRow, int toColumn)
	89	{
	90	_fromRow = fromRow;
	91	_toRow = toRow;
	92	_fromCol = fromCol;
	93	_toCol = toColumn;
	94	Validate();
	95
	96	_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
	97	}
	98	/// <summary>
	99	/// Creates an Address object
	100	/// </summary>
	101	/// <param name="fromRow">start row</param>
	102	/// <param name="fromCol">start column</param>
	103	/// <param name="toRow">End row</param>
	104	/// <param name="toColumn">End column</param>
	105	/// <param name="fromRowFixed">start row fixed</param>
	106	/// <param name="fromColFixed">start column fixed</param>
	107	/// <param name="toRowFixed">End row fixed</param>
	108	/// <param name="toColFixed">End column fixed</param>
	109	public ExcelAddressBase(int fromRow, int fromCol, int toRow, int toColumn, bool fromRowFixed, bool fromColFixed, bool toRowFixed, bool toColFixed)
	110	{
	111	_fromRow = fromRow;
	112	_toRow = toRow;
	113	_fromCol = fromCol;
	114	_toCol = toColumn;
	115	_fromRowFixed = fromRowFixed;
	116	_fromColFixed = fromColFixed;
	117	_toRowFixed = toRowFixed;
	118	_toColFixed = toColFixed;
	119	Validate();
	120
	121	_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol, _fromRowFixed, fromColFixed, _toRowFixed, _toColFixed );
	122	}
	123	/// <summary>
	124	/// Creates an Address object
	125	/// </summary>
	126	/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
	127	/// <param name="address">The Excel Address</param>
	128	public ExcelAddressBase(string address)
	129	{
	130	SetAddress(address);
	131	}
	132	/// <summary>
	133	/// Creates an Address object
	134	/// </summary>
	135	/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
	136	/// <param name="address">The Excel Address</param>
	137	/// <param name="pck">Reference to the package to find information about tables and names</param>
	138	/// <param name="referenceAddress">The address</param>
	139	public ExcelAddressBase(string address, ExcelPackage pck, ExcelAddressBase referenceAddress)
	140	{
	141	SetAddress(address);
	142	SetRCFromTable(pck, referenceAddress);
	143	}
	144
	145	internal void SetRCFromTable(ExcelPackage pck, ExcelAddressBase referenceAddress)
	146	{
	147	if (string.IsNullOrEmpty(_wb) && Table != null)
	148	{
	149	foreach (var ws in pck.Workbook.Worksheets)
	150	{
	151	foreach (var t in ws.Tables)
	152	{
	153	if (t.Name.Equals(Table.Name, StringComparison.InvariantCultureIgnoreCase))
	154	{
	155	_ws = ws.Name;
	156	if (Table.IsAll)
	157	{
	158	_fromRow = t.Address._fromRow;
	159	_toRow = t.Address._toRow;
	160	}
	161	else
	162	{
	163	if (Table.IsThisRow)
	164	{
	165	if (referenceAddress == null)
	166	{
	167	_fromRow = -1;
	168	_toRow = -1;
	169	}
	170	else
	171	{
	172	_fromRow = referenceAddress._fromRow;
	173	_toRow = _fromRow;
	174	}
	175	}
	176	else if (Table.IsHeader && Table.IsData)
	177	{
	178	_fromRow = t.Address._fromRow;
	179	_toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
	180	}
	181	else if (Table.IsData && Table.IsTotals)
	182	{
	183	_fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
	184	_toRow = t.Address._toRow;
	185	}
	186	else if (Table.IsHeader)
	187	{
	188	_fromRow = t.ShowHeader ? t.Address._fromRow : -1;
	189	_toRow = t.ShowHeader ? t.Address._fromRow : -1;
	190	}
	191	else if (Table.IsTotals)
	192	{
	193	_fromRow = t.ShowTotal ? t.Address._toRow : -1;
	194	_toRow = t.ShowTotal ? t.Address._toRow : -1;
	195	}
	196	else
	197	{
	198	_fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
	199	_toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
	200	}
	201	}
	202
	203	if (string.IsNullOrEmpty(Table.ColumnSpan))
	204	{
	205	_fromCol = t.Address._fromCol;
	206	_toCol = t.Address._toCol;
	207	return;
	208	}
	209	else
	210	{
	211	var col = t.Address._fromCol;
	212	var cols = Table.ColumnSpan.Split(':');
	213	foreach (var c in t.Columns)
	214	{
	215	if (_fromCol <= 0 && cols[0].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
	216	{
	217	_fromCol = col;
	218	if (cols.Length == 1)
	219	{
	220	_toCol = _fromCol;
	221	return;
	222	}
	223	}
	224	else if (cols.Length > 1 && _fromCol > 0 && cols[1].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
	225	{
	226	_toCol = col;
	227	return;
	228	}
	229
	230	col++;
	231	}
	232	}
	233	}
	234	}
	235	}
	236	}
	237	}
	238
	239	/// <summary>
	240	/// Address is an defined name
	241	/// </summary>
	242	/// <param name="address">the name</param>
	243	/// <param name="isName">Should always be true</param>
	244	internal ExcelAddressBase(string address, bool isName)
	245	{
	246	if (isName)
	247	{
	248	_address = address;
	249	_fromRow = -1;
	250	_fromCol = -1;
	251	_toRow = -1;
	252	_toCol = -1;
	253	_start = null;
	254	_end = null;
	255	}
	256	else
	257	{
	258	SetAddress(address);
	259	}
	260	}
	261
	262	protected internal void SetAddress(string address)
	263	{
	264	if(address.StartsWith("'"))
	265	{
	266	int pos = address.IndexOf("'", 1);
	267	while (pos < address.Length && address[pos + 1] == '\'')
	268	{
	269	pos = address.IndexOf("'", pos+2);
	270	}
	271	var wbws = address.Substring(1,pos-1).Replace("''","'");
	272	SetWbWs(wbws);
	273	_address = address.Substring(pos + 2);
	274	}
	275	else if (address.StartsWith("[")) //Remove any external reference
	276	{
	277	SetWbWs(address);
	278	}
	279	else
	280	{
	281	_address = address;
	282	}
	283	if(_address.IndexOfAny(new char[] {',','!', '['}) > -1)
	284	{
	285	//Advanced address. Including Sheet or multi or table.
	286	ExtractAddress(_address);
	287	}
	288	else
	289	{
	290	//Simple address
	291	GetRowColFromAddress(_address, out _fromRow, out _fromCol, out _toRow, out _toCol, out _fromRowFixed, out _fromColFixed, out _toRowFixed, out _toColFixed);
	292	_addresses = null;
	293	_start = null;
	294	_end = null;
	295	}
	296	_address = address;
	297	Validate();
	298	}
	299	internal void ChangeAddress()
	300	{
	301	if (AddressChange != null)
	302	{
	303	AddressChange(this, new EventArgs());
	304	}
	305	}
	306	private void SetWbWs(string address)
	307	{
	308	int pos;
	309	if (address[0] == '[')
	310	{
	311	pos = address.IndexOf("]");
	312	_wb = address.Substring(1, pos - 1);
	313	_ws = address.Substring(pos + 1);
	314	}
	315	else
	316	{
	317	_wb = "";
	318	_ws = address;
	319	}
	320	pos = _ws.IndexOf("!");
	321	if (pos > -1)
	322	{
	323	_address = _ws.Substring(pos + 1);
	324	_ws = _ws.Substring(0, pos);
	325	}
	326	}
	327	internal void ChangeWorksheet(string wsName, string newWs)
	328	{
	329	if (_ws == wsName) _ws = newWs;
	330	var fullAddress = GetAddress();
	331
	332	if (Addresses != null)
	333	{
	334	foreach (var a in Addresses)
	335	{
	336	if (a._ws == wsName)
	337	{
	338	a._ws = newWs;
	339	fullAddress += "," + a.GetAddress();
	340	}
	341	else
	342	{
	343	fullAddress += "," + a._address;
	344	}
	345	}
	346	}
	347	_address = fullAddress;
	348	}
	349
	350	private string GetAddress()
	351	{
	352	var adr = "";
	353	if (string.IsNullOrEmpty(_wb))
	354	{
	355	adr = "[" + _wb + "]";
	356	}
	357
	358	if (string.IsNullOrEmpty(_ws))
	359	{
	360	adr += string.Format("'{0}'!", _ws);
	361	}
	362	adr += GetAddress(_fromRow, _fromCol, _toRow, _toCol);
	363	return adr;
	364	}
	365
	366	ExcelCellAddress _start = null;
	367	#endregion
	368	/// <summary>
	369	/// Gets the row and column of the top left cell.
	370	/// </summary>
	371	/// <value>The start row column.</value>
	372	public ExcelCellAddress Start
	373	{
	374	get
	375	{
	376	if (_start == null)
	377	{
	378	_start = new ExcelCellAddress(_fromRow, _fromCol);
	379	}
	380	return _start;
	381	}
	382	}
	383	ExcelCellAddress _end = null;
	384	/// <summary>
	385	/// Gets the row and column of the bottom right cell.
	386	/// </summary>
	387	/// <value>The end row column.</value>
	388	public ExcelCellAddress End
	389	{
	390	get
	391	{
	392	if (_end == null)
	393	{
	394	_end = new ExcelCellAddress(_toRow, _toCol);
	395	}
	396	return _end;
	397	}
	398	}
	399	ExcelTableAddress _table=null;
	400	public ExcelTableAddress Table
	401	{
	402	get
	403	{
	404	return _table;
	405	}
	406	}
	407
	408	/// <summary>
	409	/// The address for the range
	410	/// </summary>
	411	public virtual string Address
	412	{
	413	get
	414	{
	415	return _address;
	416	}
	417	}
	418	/// <summary>
	419	/// If the address is a defined name
	420	/// </summary>
	421	public bool IsName
	422	{
	423	get
	424	{
	425	return _fromRow < 0;
	426	}
	427	}
	428	/// <summary>
	429	/// Returns the address text
	430	/// </summary>
	431	/// <returns></returns>
	432	public override string ToString()
	433	{
	434	return _address;
	435	}
	436	string _firstAddress;
	437	/// <summary>
	438	/// returns the first address if the address is a multi address.
	439	/// A1:A2,B1:B2 returns A1:A2
	440	/// </summary>
	441	internal string FirstAddress
	442	{
	443	get
	444	{
	445	if (string.IsNullOrEmpty(_firstAddress))
	446	{
	447	return _address;
	448	}
	449	else
	450	{
	451	return _firstAddress;
	452	}
	453	}
	454	}
	455	internal string AddressSpaceSeparated
	456	{
	457	get
	458	{
	459	return _address.Replace(',', ' '); //Conditional formatting and a few other places use space as separator for mulit addresses.
	460	}
	461	}
	462	/// <summary>
	463	/// Validate the address
	464	/// </summary>
	465	protected void Validate()
	466	{
	467	if (_fromRow > _toRow \|\| _fromCol > _toCol)
	468	{
	469	throw new ArgumentOutOfRangeException("Start cell Address must be less or equal to End cell address");
	470	}
	471	}
	472	internal string WorkSheet
	473	{
	474	get
	475	{
	476	return _ws;
	477	}
	478	}
	479	internal protected List<ExcelAddress> _addresses = null;
	480	internal virtual List<ExcelAddress> Addresses
	481	{
	482	get
	483	{
	484	return _addresses;
	485	}
	486	}
	487
	488	private bool ExtractAddress(string fullAddress)
	489	{
	490	var brackPos=new Stack<int>();
	491	var bracketParts=new List<string>();
	492	string first="", second="";
	493	bool isText=false, hasSheet=false;
	494	try
	495	{
	496	if (fullAddress == "#REF!")
	497	{
	498	SetAddress(ref fullAddress, ref second, ref hasSheet);
	499	return true;
	500	}
	501	for (int i = 0; i < fullAddress.Length; i++)
	502	{
	503	var c = fullAddress[i];
	504	if (c == '\'')
	505	{
	506	if (isText && i + 1 < fullAddress.Length && fullAddress[i] == '\'')
	507	{
	508	if (hasSheet)
	509	{
	510	second += c;
	511	}
	512	else
	513	{
	514	first += c;
	515	}
	516	}
	517	isText = !isText;
	518	}
	519	else
	520	{
	521	if (brackPos.Count > 0)
	522	{
	523	if (c == '[' && !isText)
	524	{
	525	brackPos.Push(i);
	526	}
	527	else if (c == ']' && !isText)
	528	{
	529	if (brackPos.Count > 0)
	530	{
	531	var from = brackPos.Pop();
	532	bracketParts.Add(fullAddress.Substring(from + 1, i - from - 1));
	533
	534	if (brackPos.Count == 0)
	535	{
	536	HandleBrackets(first, second, bracketParts);
	537	}
	538	}
	539	else
	540	{
	541	//Invalid address!
	542	return false;
	543	}
	544	}
	545	}
	546	else if (c == '[' && !isText)
	547	{
	548	brackPos.Push(i);
	549	}
	550	else if (c == '!' && !isText && !first.EndsWith("#REF") && !second.EndsWith("#REF"))
	551	{
	552	hasSheet = true;
	553	}
	554	else if (c == ',' && !isText)
	555	{
	556	SetAddress(ref first, ref second, ref hasSheet);
	557	}
	558	else
	559	{
	560	if (hasSheet)
	561	{
	562	second += c;
	563	}
	564	else
	565	{
	566	first += c;
	567	}
	568	}
	569	}
	570	}
	571	if (Table == null)
	572	{
	573	SetAddress(ref first, ref second, ref hasSheet);
	574	}
	575	return true;
	576	}
	577	catch
	578	{
	579	return false;
	580	}
	581	}
	582
	583	private void HandleBrackets(string first, string second, List<string> bracketParts)
	584	{
	585	if(!string.IsNullOrEmpty(first))
	586	{
	587	_table = new ExcelTableAddress();
	588	Table.Name = first;
	589	foreach (var s in bracketParts)
	590	{
	591	if(s.IndexOf("[")<0)
	592	{
	593	switch(s.ToLower(CultureInfo.InvariantCulture))
	594	{
	595	case "#all":
	596	_table.IsAll = true;
	597	break;
	598	case "#headers":
	599	_table.IsHeader = true;
	600	break;
	601	case "#data":
	602	_table.IsData = true;
	603	break;
	604	case "#totals":
	605	_table.IsTotals = true;
	606	break;
	607	case "#this row":
	608	_table.IsThisRow = true;
	609	break;
	610	default:
	611	if(string.IsNullOrEmpty(_table.ColumnSpan))
	612	{
	613	_table.ColumnSpan=s;
	614	}
	615	else
	616	{
	617	_table.ColumnSpan += ":" + s;
	618	}
	619	break;
	620	}
	621	}
	622	}
	623	}
	624	}
	625	#region Address manipulation methods
	626	internal eAddressCollition Collide(ExcelAddressBase address)
	627	{
	628	if (address.WorkSheet != WorkSheet && address.WorkSheet!=null)
	629	{
	630	return eAddressCollition.No;
	631	}
	632
	633	if (address._fromRow > _toRow \|\| address._fromCol > _toCol
	634	\|\|
	635	_fromRow > address._toRow \|\| _fromCol > address._toCol)
	636	{
	637	return eAddressCollition.No;
	638	}
	639	else if (address._fromRow == _fromRow && address._fromCol == _fromCol &&
	640	address._toRow == _toRow && address._toCol == _toCol)
	641	{
	642	return eAddressCollition.Equal;
	643	}
	644	else if (address._fromRow >= _fromRow && address._toRow <= _toRow &&
	645	address._fromCol >= _fromCol && address._toCol <= _toCol)
	646	{
	647	return eAddressCollition.Inside;
	648	}
	649	else
	650	return eAddressCollition.Partly;
	651	}
	652	internal ExcelAddressBase AddRow(int row, int rows, bool setFixed=false)
	653	{
	654	if (row > _toRow)
	655	{
	656	return this;
	657	}
	658	else if (row <= _fromRow)
	659	{
	660	return new ExcelAddressBase((setFixed && _fromRowFixed ? _fromRow : _fromRow + rows), _fromCol, (setFixed && _toRowFixed ? _toRow : _toRow + rows), _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	661	}
	662	else
	663	{
	664	return new ExcelAddressBase(_fromRow, _fromCol, (setFixed && _toRowFixed ? _toRow : _toRow + rows), _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	665	}
	666	}
	667	internal ExcelAddressBase DeleteRow(int row, int rows, bool setFixed = false)
	668	{
	669	if (row > _toRow) //After
	670	{
	671	return this;
	672	}
	673	else if (row+rows <= _fromRow) //Before
	674	{
	675	return new ExcelAddressBase((setFixed && _fromRowFixed ? _fromRow : _fromRow - rows), _fromCol, (setFixed && _toRowFixed ? _toRow : _toRow - rows), _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	676	}
	677	else if (row <= _fromRow && row + rows > _toRow) //Inside
	678	{
	679	return null;
	680	}
	681	else //Partly
	682	{
	683	if (row <= _fromRow)
	684	{
	685	return new ExcelAddressBase(row, _fromCol, (setFixed && _toRowFixed ? _toRow : _toRow - rows), _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	686	}
	687	else
	688	{
	689	return new ExcelAddressBase(_fromRow, _fromCol, (setFixed && _toRowFixed ? _toRow : _toRow - rows < row ? row - 1 : _toRow - rows), _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	690	}
	691	}
	692	}
	693	internal ExcelAddressBase AddColumn(int col, int cols, bool setFixed = false)
	694	{
	695	if (col > _toCol)
	696	{
	697	return this;
	698	}
	699	else if (col <= _fromCol)
	700	{
	701	return new ExcelAddressBase(_fromRow, (setFixed && _fromColFixed ? _fromCol : _fromCol + cols), _toRow, (setFixed && _toColFixed ? _toCol : _toCol + cols), _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	702	}
	703	else
	704	{
	705	return new ExcelAddressBase(_fromRow, _fromCol, _toRow, (setFixed && _toColFixed ? _toCol : _toCol + cols), _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	706	}
	707	}
	708	internal ExcelAddressBase DeleteColumn(int col, int cols, bool setFixed = false)
	709	{
	710	if (col > _toCol) //After
	711	{
	712	return this;
	713	}
	714	else if (col + cols <= _fromCol) //Before
	715	{
	716	return new ExcelAddressBase(_fromRow, (setFixed && _fromColFixed ? _fromCol : _fromCol - cols), _toRow, (setFixed && _toColFixed ? _toCol :_toCol - cols), _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	717	}
	718	else if (col <= _fromCol && col + cols > _toCol) //Inside
	719	{
	720	return null;
	721	}
	722	else //Partly
	723	{
	724	if (col <= _fromCol)
	725	{
	726	return new ExcelAddressBase(_fromRow, col, _toRow, (setFixed && _toColFixed ? _toCol : _toCol - cols), _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	727	}
	728	else
	729	{
	730	return new ExcelAddressBase(_fromRow, _fromCol, _toRow, (setFixed && _toColFixed ? _toCol :_toCol - cols < col ? col - 1 : _toCol - cols), _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	731	}
	732	}
	733	}
	734	internal ExcelAddressBase Insert(ExcelAddressBase address, eShiftType Shift/, out ExcelAddressBase topAddress, out ExcelAddressBase leftAddress, out ExcelAddressBase rightAddress, out ExcelAddressBase bottomAddress/)
	735	{
	736	//Before or after, no change
	737	//if ((_toRow > address._fromRow && _toCol > address.column) \|\|
	738	// (_fromRow > address._toRow && column > address._toCol))
	739	if(_toRow < address._fromRow \|\| _toCol < address._fromCol \|\| (_fromRow > address._toRow && _fromCol > address._toCol))
	740	{
	741	//topAddress = null;
	742	//leftAddress = null;
	743	//rightAddress = null;
	744	//bottomAddress = null;
	745	return this;
	746	}
	747
	748	int rows = address.Rows;
	749	int cols = address.Columns;
	750	string retAddress = "";
	751	if (Shift==eShiftType.Right)
	752	{
	753	if (address._fromRow > _fromRow)
	754	{
	755	retAddress = GetAddress(_fromRow, _fromCol, address._fromRow, _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	756	}
	757	if(address._fromCol > _fromCol)
	758	{
	759	retAddress = GetAddress(_fromRow < address._fromRow ? _fromRow : address._fromRow, _fromCol, address._fromRow, _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	760	}
	761	}
	762	if (_toRow < address._fromRow)
	763	{
	764	if (_fromRow < address._fromRow)
	765	{
	766
	767	}
	768	else
	769	{
	770	}
	771	}
	772	return null;
	773	}
	774	#endregion
	775	private void SetAddress(ref string first, ref string second, ref bool hasSheet)
	776	{
	777	string ws, address;
	778	if (hasSheet)
	779	{
	780	ws = first;
	781	address = second;
	782	first = "";
	783	second = "";
	784	}
	785	else
	786	{
	787	address = first;
	788	ws = "";
	789	first = "";
	790	}
	791	hasSheet = false;
	792	if (string.IsNullOrEmpty(_firstAddress))
	793	{
	794	if(string.IsNullOrEmpty(_ws) \|\| !string.IsNullOrEmpty(ws)) _ws = ws;
	795	_firstAddress = address;
	796	GetRowColFromAddress(address, out _fromRow, out _fromCol, out _toRow, out _toCol, out _fromRowFixed, out _fromColFixed, out _toRowFixed, out _toColFixed);
	797	}
	798	else
	799	{
	800	if (_addresses == null) _addresses = new List<ExcelAddress>();
	801	_addresses.Add(new ExcelAddress(_ws, address));
	802	}
	803	}
	804	internal enum AddressType
	805	{
	806	Invalid,
	807	InternalAddress,
	808	ExternalAddress,
	809	InternalName,
	810	ExternalName,
	811	Formula
	812	}
	813
	814	internal static AddressType IsValid(string Address)
	815	{
	816	double d;
	817	if (Address == "#REF!")
	818	{
	819	return AddressType.Invalid;
	820	}
	821	else if(double.TryParse(Address, NumberStyles.Any, CultureInfo.InvariantCulture, out d)) //A double, no valid address
	822	{
	823	return AddressType.Invalid;
	824	}
	825	else if (IsFormula(Address))
	826	{
	827	return AddressType.Formula;
	828	}
	829	else
	830	{
	831	string wb, ws, intAddress;
	832	if(SplitAddress(Address, out wb, out ws, out intAddress))
	833	{
	834	if(intAddress.Contains("[")) //Table reference
	835	{
	836	return string.IsNullOrEmpty(wb) ? AddressType.InternalAddress : AddressType.ExternalAddress;
	837	}
	838	else if(intAddress.Contains(","))
	839	{
	840	intAddress=intAddress.Substring(0, intAddress.IndexOf(','));
	841	}
	842	if(IsAddress(intAddress))
	843	{
	844	return string.IsNullOrEmpty(wb) ? AddressType.InternalAddress : AddressType.ExternalAddress;
	845	}
	846	else
	847	{
	848	return string.IsNullOrEmpty(wb) ? AddressType.InternalName : AddressType.ExternalName;
	849	}
	850	}
	851	else
	852	{
	853	return AddressType.Invalid;
	854	}
	855
	856	//if(string.IsNullOrEmpty(wb));
	857
	858	}
	859	//ExcelAddress a = new ExcelAddress(Address);
	860	//if (Address.IndexOf('!') > 0)
	861	//{
	862	// string[] split = Address.Split('!');
	863	// if (split.Length == 2)
	864	// {
	865	// ws = split[0];
	866	// Address = split[1];
	867	// }
	868	// else if (split.Length == 3 && split[1] == "#REF" && split[2] == "")
	869	// {
	870	// ws = split[0];
	871	// Address = "#REF!";
	872	// if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
	873	// {
	874	// return AddressType.ExternalAddress;
	875	// }
	876	// else
	877	// {
	878	// return AddressType.InternalAddress;
	879	// }
	880	// }
	881	// else
	882	// {
	883	// return AddressType.Invalid;
	884	// }
	885	//}
	886	//int _fromRow, column, _toRow, _toCol;
	887	//if (ExcelAddressBase.GetRowColFromAddress(Address, out _fromRow, out column, out _toRow, out _toCol))
	888	//{
	889	// if (_fromRow > 0 && column > 0 && _toRow <= ExcelPackage.MaxRows && _toCol <= ExcelPackage.MaxColumns)
	890	// {
	891	// if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
	892	// {
	893	// return AddressType.ExternalAddress;
	894	// }
	895	// else
	896	// {
	897	// return AddressType.InternalAddress;
	898	// }
	899	// }
	900	// else
	901	// {
	902	// return AddressType.Invalid;
	903	// }
	904	//}
	905	//else
	906	//{
	907	// if(IsValidName(Address))
	908	// {
	909	// if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
	910	// {
	911	// return AddressType.ExternalName;
	912	// }
	913	// else
	914	// {
	915	// return AddressType.InternalName;
	916	// }
	917	// }
	918	// else
	919	// {
	920	// return AddressType.Invalid;
	921	// }
	922	//}
	923
	924	}
	925
	926	private static bool IsAddress(string intAddress)
	927	{
	928	if(string.IsNullOrEmpty(intAddress)) return false;
	929	var cells = intAddress.Split(':');
	930	int fromRow,toRow, fromCol, toCol;
	931
	932	if(!GetRowCol(cells[0], out fromRow, out fromCol, false))
	933	{
	934	return false;
	935	}
	936	if (cells.Length > 1)
	937	{
	938	if (!GetRowCol(cells[1], out toRow, out toCol, false))
	939	{
	940	return false;
	941	}
	942	}
	943	else
	944	{
	945	toRow = fromRow;
	946	toCol = fromCol;
	947	}
	948	if( fromRow <= toRow &&
	949	fromCol <= toCol &&
	950	fromCol > -1 &&
	951	toCol <= ExcelPackage.MaxColumns &&
	952	fromRow > -1 &&
	953	toRow <= ExcelPackage.MaxRows)
	954	{
	955	return true;
	956	}
	957	else
	958	{
	959	return false;
	960	}
	961	}
	962
	963	private static bool SplitAddress(string Address, out string wb, out string ws, out string intAddress)
	964	{
	965	wb = "";
	966	ws = "";
	967	intAddress = "";
	968	var text = "";
	969	bool isText = false;
	970	var brackPos=-1;
	971	for (int i = 0; i < Address.Length; i++)
	972	{
	973	if (Address[i] == '\'')
	974	{
	975	isText = !isText;
	976	if(i>0 && Address[i-1]=='\'')
	977	{
	978	text += "'";
	979	}
	980	}
	981	else
	982	{
	983	if(Address[i]=='!' && !isText)
	984	{
	985	if (text.Length>0 && text[0] == '[')
	986	{
	987	wb = text.Substring(1, text.IndexOf("]") - 1);
	988	ws = text.Substring(text.IndexOf("]") + 1);
	989	}
	990	else
	991	{
	992	ws=text;
	993	}
	994	intAddress=Address.Substring(i+1);
	995	return true;
	996	}
	997	else
	998	{
	999	if(Address[i]=='[' && !isText)
	1000	{
	1001	if (i > 0) //Table reference return full address;
	1002	{
	1003	intAddress=Address;
	1004	return true;
	1005	}
	1006	brackPos=i;
	1007	}
	1008	else if(Address[i]==']' && !isText)
	1009	{
	1010	if (brackPos > -1)
	1011	{
	1012	wb = text;
	1013	text = "";
	1014	}
	1015	else
	1016	{
	1017	return false;
	1018	}
	1019	}
	1020	else
	1021	{
	1022	text+=Address[i];
	1023	}
	1024	}
	1025	}
	1026	}
	1027	intAddress = text;
	1028	return true;
	1029	}
	1030
	1031	private static bool IsFormula(string address)
	1032	{
	1033	var isText = false;
	1034	for (int i = 0; i < address.Length; i++)
	1035	{
	1036	if (address[i] == '\'')
	1037	{
	1038	isText = !isText;
	1039	}
	1040	else
	1041	{
	1042	if (isText==false && address.Substring(i, 1).IndexOfAny(new char[] { '(', ')', '+', '-', '*', '/', '.', '=', '^', '&', '%', '\"' }) > -1)
	1043	{
	1044	return true;
	1045	}
	1046	}
	1047	}
	1048	return false;
	1049	}
	1050
	1051	private static bool IsValidName(string address)
	1052	{
	1053	if (Regex.IsMatch(address, "[^0-9./-+,½!\"@#£%&/{}()\\[\\]=?`^~':;<>\|][^/-+,½!\"@#£%&/{}()\\[\\]=?`^~':;<>\|]*"))
	1054	{
	1055	return true;
	1056	}
	1057	else
	1058	{
	1059	return false;
	1060	}
	1061	}
	1062
	1063	public int Rows
	1064	{
	1065	get
	1066	{
	1067	return _toRow - _fromRow+1;
	1068	}
	1069	}
	1070	public int Columns
	1071	{
	1072	get
	1073	{
	1074	return _toCol - _fromCol + 1;
	1075	}
	1076	}
	1077
	1078	internal bool IsMultiCell()
	1079	{
	1080	return (_fromRow < _fromCol \|\| _fromCol < _toCol);
	1081	}
	1082	internal static String GetWorkbookPart(string address)
	1083	{
	1084	var ix = 0;
	1085	if (address[0] == '[')
	1086	{
	1087	ix = address.IndexOf(']') + 1;
	1088	if (ix > 0)
	1089	{
	1090	return address.Substring(1, ix - 2);
	1091	}
	1092	}
	1093	return "";
	1094	}
	1095	internal static string GetWorksheetPart(string address, string defaultWorkSheet)
	1096	{
	1097	int ix=0;
	1098	return GetWorksheetPart(address, defaultWorkSheet, ref ix);
	1099	}
	1100	internal static string GetWorksheetPart(string address, string defaultWorkSheet, ref int endIx)
	1101	{
	1102	if(address=="") return defaultWorkSheet;
	1103	var ix = 0;
	1104	if (address[0] == '[')
	1105	{
	1106	ix = address.IndexOf(']')+1;
	1107	}
	1108	if (ix > 0 && ix < address.Length)
	1109	{
	1110	if (address[ix] == '\'')
	1111	{
	1112	return GetString(address, ix, out endIx);
	1113	}
	1114	else
	1115	{
	1116	var ixEnd = address.IndexOf('!',ix);
	1117	if(ixEnd>ix)
	1118	{
	1119	return address.Substring(ix, ixEnd-ix);
	1120	}
	1121	else
	1122	{
	1123	return defaultWorkSheet;
	1124	}
	1125	}
	1126	}
	1127	else
	1128	{
	1129	return defaultWorkSheet;
	1130	}
	1131	}
	1132	internal static string GetAddressPart(string address)
	1133	{
	1134	var ix=0;
	1135	GetWorksheetPart(address, "", ref ix);
	1136	if(ix<address.Length)
	1137	{
	1138	if (address[ix] == '!')
	1139	{
	1140	return address.Substring(ix + 1);
	1141	}
	1142	else
	1143	{
	1144	return "";
	1145	}
	1146	}
	1147	else
	1148	{
	1149	return "";
	1150	}
	1151
	1152	}
	1153	internal static void SplitAddress(string fullAddress, out string wb, out string ws, out string address, string defaultWorksheet="")
	1154	{
	1155	wb = GetWorkbookPart(fullAddress);
	1156	int ix=0;
	1157	ws = GetWorksheetPart(fullAddress, defaultWorksheet, ref ix);
	1158	if (ix < fullAddress.Length)
	1159	{
	1160	if (fullAddress[ix] == '!')
	1161	{
	1162	address = fullAddress.Substring(ix + 1);
	1163	}
	1164	else
	1165	{
	1166	address = fullAddress.Substring(ix);
	1167	}
	1168	}
	1169	else
	1170	{
	1171	address="";
	1172	}
	1173	}
	1174	private static string GetString(string address, int ix, out int endIx)
	1175	{
	1176	var strIx = address.IndexOf("''");
	1177	var prevStrIx = ix;
	1178	while(strIx > -1)
	1179	{
	1180	prevStrIx = strIx;
	1181	strIx = address.IndexOf("''");
	1182	}
	1183	endIx = address.IndexOf("'");
	1184	return address.Substring(ix, endIx - ix).Replace("''","'");
	1185	}
	1186
	1187	internal bool IsValidRowCol()
	1188	{
	1189	return !(_fromRow > _toRow \|\|
	1190	_fromCol > _toCol \|\|
	1191	_fromRow < 1 \|\|
	1192	_fromCol < 1 \|\|
	1193	_toRow > ExcelPackage.MaxRows \|\|
	1194	_toCol > ExcelPackage.MaxColumns);
	1195	}
	1196	}
	1197	/// <summary>
	1198	/// Range address with the address property readonly
	1199	/// </summary>
	1200	public class ExcelAddress : ExcelAddressBase
	1201	{
	1202	internal ExcelAddress()
	1203	: base()
	1204	{
	1205
	1206	}
	1207
	1208	public ExcelAddress(int fromRow, int fromCol, int toRow, int toColumn)
	1209	: base(fromRow, fromCol, toRow, toColumn)
	1210	{
	1211	_ws = "";
	1212	}
	1213	public ExcelAddress(string address)
	1214	: base(address)
	1215	{
	1216	}
	1217
	1218	internal ExcelAddress(string ws, string address)
	1219	: base(address)
	1220	{
	1221	if (string.IsNullOrEmpty(_ws)) _ws = ws;
	1222	}
	1223	internal ExcelAddress(string ws, string address, bool isName)
	1224	: base(address, isName)
	1225	{
	1226	if (string.IsNullOrEmpty(_ws)) _ws = ws;
	1227	}
	1228
	1229	public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress) :
	1230	base(Address, package, referenceAddress)
	1231	{
	1232
	1233	}
	1234	/// <summary>
	1235	/// The address for the range
	1236	/// </summary>
	1237	/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
	1238	public new string Address
	1239	{
	1240	get
	1241	{
	1242	if (string.IsNullOrEmpty(_address) && _fromRow>0)
	1243	{
	1244	_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
	1245	}
	1246	return _address;
	1247	}
	1248	set
	1249	{
	1250	SetAddress(value);
	1251	base.ChangeAddress();
	1252	}
	1253	}
	1254	}
	1255	public class ExcelFormulaAddress : ExcelAddressBase
	1256	{
	1257	bool _fromRowFixed, _toRowFixed, _fromColFixed, _toColFixed;
	1258	internal ExcelFormulaAddress()
	1259	: base()
	1260	{
	1261	}
	1262
	1263	public ExcelFormulaAddress(int fromRow, int fromCol, int toRow, int toColumn)
	1264	: base(fromRow, fromCol, toRow, toColumn)
	1265	{
	1266	_ws = "";
	1267	}
	1268	public ExcelFormulaAddress(string address)
	1269	: base(address)
	1270	{
	1271	SetFixed();
	1272	}
	1273
	1274	internal ExcelFormulaAddress(string ws, string address)
	1275	: base(address)
	1276	{
	1277	if (string.IsNullOrEmpty(_ws)) _ws = ws;
	1278	SetFixed();
	1279	}
	1280	internal ExcelFormulaAddress(string ws, string address, bool isName)
	1281	: base(address, isName)
	1282	{
	1283	if (string.IsNullOrEmpty(_ws)) _ws = ws;
	1284	if(!isName)
	1285	SetFixed();
	1286	}
	1287
	1288	private void SetFixed()
	1289	{
	1290	if (Address.IndexOf("[") >= 0) return;
	1291	var address=FirstAddress;
	1292	if(_fromRow==_toRow && _fromCol==_toCol)
	1293	{
	1294	GetFixed(address, out _fromRowFixed, out _fromColFixed);
	1295	}
	1296	else
	1297	{
	1298	var cells = address.Split(':');
	1299	GetFixed(cells[0], out _fromRowFixed, out _fromColFixed);
	1300	GetFixed(cells[1], out _toRowFixed, out _toColFixed);
	1301	}
	1302	}
	1303
	1304	private void GetFixed(string address, out bool rowFixed, out bool colFixed)
	1305	{
	1306	rowFixed=colFixed=false;
	1307	var ix=address.IndexOf('$');
	1308	while(ix>-1)
	1309	{
	1310	ix++;
	1311	if(ix < address.Length)
	1312	{
	1313	if(address[ix]>='0' && address[ix]<='9')
	1314	{
	1315	rowFixed=true;
	1316	break;
	1317	}
	1318	else
	1319	{
	1320	colFixed=true;
	1321	}
	1322	}
	1323	ix = address.IndexOf('$', ix);
	1324	}
	1325	}
	1326	/// <summary>
	1327	/// The address for the range
	1328	/// </summary>
	1329	/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
	1330	public new string Address
	1331	{
	1332	get
	1333	{
	1334	if (string.IsNullOrEmpty(_address) && _fromRow>0)
	1335	{
	1336	_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol, _fromRowFixed, _toRowFixed, _fromColFixed, _toColFixed);
	1337	}
	1338	return _address;
	1339	}
	1340	set
	1341	{
	1342	SetAddress(value);
	1343	base.ChangeAddress();
	1344	SetFixed();
	1345	}
	1346	}
	1347	internal new List<ExcelFormulaAddress> _addresses;
	1348	public new List<ExcelFormulaAddress> Addresses
	1349	{
	1350	get
	1351	{
	1352	if (_addresses == null)
	1353	{
	1354	_addresses = new List<ExcelFormulaAddress>();
	1355	}
	1356	return _addresses;
	1357
	1358	}
	1359	}
	1360	internal string GetOffset(int row, int column)
	1361	{
	1362	int fromRow = _fromRow, fromCol = _fromCol, toRow = _toRow, tocol = _toCol;
	1363	var isMulti = (fromRow != toRow \|\| fromCol != tocol);
	1364	if (!_fromRowFixed)
	1365	{
	1366	fromRow += row;
	1367	}
	1368	if (!_fromColFixed)
	1369	{
	1370	fromCol += column;
	1371	}
	1372	if (isMulti)
	1373	{
	1374	if (!_toRowFixed)
	1375	{
	1376	toRow += row;
	1377	}
	1378	if (!_toColFixed)
	1379	{
	1380	tocol += column;
	1381	}
	1382	}
	1383	else
	1384	{
	1385	toRow = fromRow;
	1386	tocol = fromCol;
	1387	}
	1388	string a = GetAddress(fromRow, fromCol, toRow, tocol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
	1389	if (Addresses != null)
	1390	{
	1391	foreach (var sa in Addresses)
	1392	{
	1393	a+="," + sa.GetOffset(row, column);
	1394	}
	1395	}
	1396	return a;
	1397	}
	1398	}
	1399	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats:

Update cookies preferences