Context Navigation

← Previous Revision
Next Revision →
Blame
Revision Log

source: branches/2708_ScopedAlgorithms/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelAddress.cs

Visit:

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

Line
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