Subversion Repositories eFlore/Applications.cel

Rev

Rev 2388 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 *	PHPExcel
4
 *
5
 *	Copyright (c) 2006 - 2013 PHPExcel
6
 *
7
 *	This library is free software; you can redistribute it and/or
8
 *	modify it under the terms of the GNU Lesser General Public
9
 *	License as published by the Free Software Foundation; either
10
 *	version 2.1 of the License, or (at your option) any later version.
11
 *
12
 *	This library is distributed in the hope that it will be useful,
13
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
14
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15
 *	Lesser General Public License for more details.
16
 *
17
 *	You should have received a copy of the GNU Lesser General Public
18
 *	License along with this library; if not, write to the Free Software
19
 *	Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
20
 *
21
 *	@category	PHPExcel
22
 *	@package	PHPExcel_Cell
23
 *	@copyright	Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 *	@license	http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 *	@version	##VERSION##, ##DATE##
26
 */
27
 
28
 
29
/**
30
 *	PHPExcel_Cell
31
 *
32
 *	@category   PHPExcel
33
 *	@package	PHPExcel_Cell
34
 *	@copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
35
 */
36
class PHPExcel_Cell
37
{
38
 
39
	/**
40
	 *  Default range variable constant
41
	 *
42
	 *  @var  string
43
	 */
44
	const DEFAULT_RANGE = 'A1:A1';
45
 
46
	/**
47
	 *	Value binder to use
48
	 *
49
	 *	@var	PHPExcel_Cell_IValueBinder
50
	 */
51
	private static $_valueBinder = NULL;
52
 
53
	/**
54
	 *	Value of the cell
55
	 *
56
	 *	@var	mixed
57
	 */
58
	private $_value;
59
 
60
	/**
61
	 *	Calculated value of the cell (used for caching)
62
	 *	This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
63
	 *		create the original spreadsheet file.
64
	 *	Note that this value is not guaranteed to reflect the actual calculated value because it is
65
	 *		possible that auto-calculation was disabled in the original spreadsheet, and underlying data
66
	 *		values used by the formula have changed since it was last calculated.
67
	 *
68
	 *	@var mixed
69
	 */
70
	private $_calculatedValue = NULL;
71
 
72
	/**
73
	 *	Type of the cell data
74
	 *
75
	 *	@var	string
76
	 */
77
	private $_dataType;
78
 
79
	/**
80
	 *	Parent worksheet
81
	 *
82
	 *	@var	PHPExcel_CachedObjectStorage_CacheBase
83
	 */
84
	private $_parent;
85
 
86
	/**
87
	 *	Index to cellXf
88
	 *
89
	 *	@var	int
90
	 */
91
	private $_xfIndex;
92
 
93
	/**
94
	 *	Attributes of the formula
95
	 *
96
	 */
97
	private $_formulaAttributes;
98
 
99
 
100
	/**
101
	 *	Send notification to the cache controller
102
	 *
103
	 *	@return void
104
	 **/
105
	public function notifyCacheController() {
106
		$this->_parent->updateCacheData($this);
107
 
108
		return $this;
109
	}
110
 
111
	public function detach() {
112
		$this->_parent = NULL;
113
	}
114
 
115
	public function attach(PHPExcel_CachedObjectStorage_CacheBase $parent) {
116
 
117
 
118
		$this->_parent = $parent;
119
	}
120
 
121
 
122
	/**
123
	 *	Create a new Cell
124
	 *
125
	 *	@param	mixed				$pValue
126
	 *	@param	string				$pDataType
127
	 *	@param	PHPExcel_Worksheet	$pSheet
128
	 *	@throws	PHPExcel_Exception
129
	 */
130
	public function __construct($pValue = NULL, $pDataType = NULL, PHPExcel_Worksheet $pSheet = NULL)
131
	{
132
		// Initialise cell value
133
		$this->_value = $pValue;
134
 
135
		// Set worksheet cache
136
		$this->_parent = $pSheet->getCellCacheController();
137
 
138
		// Set datatype?
139
		if ($pDataType !== NULL) {
140
			if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
141
				$pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
142
			$this->_dataType = $pDataType;
143
		} else {
144
			if (!self::getValueBinder()->bindValue($this, $pValue)) {
145
				throw new PHPExcel_Exception("Value could not be bound to cell.");
146
			}
147
		}
148
 
149
		// set default index to cellXf
150
		$this->_xfIndex = 0;
151
	}
152
 
153
	/**
154
	 *	Get cell coordinate column
155
	 *
156
	 *	@return	string
157
	 */
158
	public function getColumn()
159
	{
160
		return $this->_parent->getCurrentColumn();
161
	}
162
 
163
	/**
164
	 *	Get cell coordinate row
165
	 *
166
	 *	@return	int
167
	 */
168
	public function getRow()
169
	{
170
		return $this->_parent->getCurrentRow();
171
	}
172
 
173
	/**
174
	 *	Get cell coordinate
175
	 *
176
	 *	@return	string
177
	 */
178
	public function getCoordinate()
179
	{
180
		return $this->_parent->getCurrentAddress();
181
	}
182
 
183
	/**
184
	 *	Get cell value
185
	 *
186
	 *	@return	mixed
187
	 */
188
	public function getValue()
189
	{
190
		return $this->_value;
191
	}
192
 
193
	/**
194
	 *	Get cell value with formatting
195
	 *
196
	 *	@return	string
197
	 */
198
	public function getFormattedValue()
199
	{
200
		return (string) PHPExcel_Style_NumberFormat::toFormattedString(
201
				$this->getCalculatedValue(),
202
				$this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex())
203
					->getNumberFormat()->getFormatCode()
204
			);
205
	}
206
 
207
	/**
208
	 *	Set cell value
209
	 *
210
	 *	Sets the value for a cell, automatically determining the datatype using the value binder
211
	 *
212
	 *	@param	mixed	$pValue					Value
213
	 *	@return	PHPExcel_Cell
214
	 *	@throws	PHPExcel_Exception
215
	 */
216
	public function setValue($pValue = NULL)
217
	{
218
		if (!self::getValueBinder()->bindValue($this, $pValue)) {
219
			throw new PHPExcel_Exception("Value could not be bound to cell.");
220
		}
221
		return $this;
222
	}
223
 
224
	/**
225
	 *	Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder)
226
	 *
227
	 *	@param	mixed	$pValue			Value
228
	 *	@param	string	$pDataType		Explicit data type
229
	 *	@return	PHPExcel_Cell
230
	 *	@throws	PHPExcel_Exception
231
	 */
232
	public function setValueExplicit($pValue = NULL, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
233
	{
234
		// set the value according to data type
235
		switch ($pDataType) {
236
			case PHPExcel_Cell_DataType::TYPE_STRING2:
237
				$pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
238
			case PHPExcel_Cell_DataType::TYPE_STRING:
239
			case PHPExcel_Cell_DataType::TYPE_NULL:
240
			case PHPExcel_Cell_DataType::TYPE_INLINE:
241
				$this->_value = PHPExcel_Cell_DataType::checkString($pValue);
242
				break;
243
			case PHPExcel_Cell_DataType::TYPE_NUMERIC:
244
				$this->_value = (float)$pValue;
245
				break;
246
			case PHPExcel_Cell_DataType::TYPE_FORMULA:
247
				$this->_value = (string)$pValue;
248
				break;
249
			case PHPExcel_Cell_DataType::TYPE_BOOL:
250
				$this->_value = (bool)$pValue;
251
				break;
252
			case PHPExcel_Cell_DataType::TYPE_ERROR:
253
				$this->_value = PHPExcel_Cell_DataType::checkErrorCode($pValue);
254
				break;
255
			default:
256
				throw new PHPExcel_Exception('Invalid datatype: ' . $pDataType);
257
				break;
258
		}
259
 
260
		// set the datatype
261
		$this->_dataType = $pDataType;
262
 
263
		return $this->notifyCacheController();
264
	}
265
 
266
	/**
267
	 *	Get calculated cell value
268
	 *
269
	 *	@deprecated		Since version 1.7.8 for planned changes to cell for array formula handling
270
	 *
271
	 *	@param	boolean $resetLog  Whether the calculation engine logger should be reset or not
272
	 *	@return	mixed
273
	 *	@throws	PHPExcel_Exception
274
	 */
275
	public function getCalculatedValue($resetLog = TRUE)
276
	{
277
//echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().PHP_EOL;
278
		if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) {
279
			try {
280
//echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value'.PHP_EOL;
281
				$result = PHPExcel_Calculation::getInstance(
282
					$this->getWorksheet()->getParent()
283
				)->calculateCellValue($this,$resetLog);
284
//echo $this->getCoordinate().' calculation result is '.$result.PHP_EOL;
285
				//	We don't yet handle array returns
286
				if (is_array($result)) {
287
					while (is_array($result)) {
288
						$result = array_pop($result);
289
					}
290
				}
291
			} catch ( PHPExcel_Exception $ex ) {
292
				if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->_calculatedValue !== NULL)) {
293
//echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
294
					return $this->_calculatedValue; // Fallback for calculations referencing external files.
295
				}
296
//echo 'Calculation Exception: '.$ex->getMessage().PHP_EOL;
297
				$result = '#N/A';
298
				throw new PHPExcel_Calculation_Exception(
299
					$this->getWorksheet()->getTitle().'!'.$this->getCoordinate().' -> '.$ex->getMessage()
300
				);
301
			}
302
 
303
			if ($result === '#Not Yet Implemented') {
304
//echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
305
				return $this->_calculatedValue; // Fallback if calculation engine does not support the formula.
306
			}
307
//echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().PHP_EOL;
308
			return $result;
309
		} elseif($this->_value instanceof PHPExcel_RichText) {
310
//		echo 'Cell value for '.$this->getCoordinate().' is rich text: Returning data value of '.$this->_value.'<br />';
311
			return $this->_value->getPlainText();
312
		}
313
//		echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />';
314
		return $this->_value;
315
	}
316
 
317
	/**
318
	 *	Set old calculated value (cached)
319
	 *
320
	 *	@param	mixed $pValue	Value
321
	 *	@return	PHPExcel_Cell
322
	 */
323
	public function setCalculatedValue($pValue = NULL)
324
	{
325
		if ($pValue !== NULL) {
326
			$this->_calculatedValue = (is_numeric($pValue)) ? (float) $pValue : $pValue;
327
		}
328
 
329
		return $this->notifyCacheController();
330
	}
331
 
332
	/**
333
	 *	Get old calculated value (cached)
334
	 *	This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
335
	 *		create the original spreadsheet file.
336
	 *	Note that this value is not guaranteed to refelect the actual calculated value because it is
337
	 *		possible that auto-calculation was disabled in the original spreadsheet, and underlying data
338
	 *		values used by the formula have changed since it was last calculated.
339
	 *
340
	 *	@return	mixed
341
	 */
342
	public function getOldCalculatedValue()
343
	{
344
		return $this->_calculatedValue;
345
	}
346
 
347
	/**
348
	 *	Get cell data type
349
	 *
350
	 *	@return string
351
	 */
352
	public function getDataType()
353
	{
354
		return $this->_dataType;
355
	}
356
 
357
	/**
358
	 *	Set cell data type
359
	 *
360
	 *	@param	string $pDataType
361
	 *	@return	PHPExcel_Cell
362
	 */
363
	public function setDataType($pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
364
	{
365
		if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING2)
366
			$pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
367
 
368
		$this->_dataType = $pDataType;
369
 
370
		return $this->notifyCacheController();
371
	}
372
 
373
	/**
374
	 *	Does this cell contain Data validation rules?
375
	 *
376
	 *	@return	boolean
377
	 *	@throws	PHPExcel_Exception
378
	 */
379
	public function hasDataValidation()
380
	{
381
		if (!isset($this->_parent)) {
382
			throw new PHPExcel_Exception('Cannot check for data validation when cell is not bound to a worksheet');
383
		}
384
 
385
		return $this->getWorksheet()->dataValidationExists($this->getCoordinate());
386
	}
387
 
388
	/**
389
	 *	Get Data validation rules
390
	 *
391
	 *	@return	PHPExcel_Cell_DataValidation
392
	 *	@throws	PHPExcel_Exception
393
	 */
394
	public function getDataValidation()
395
	{
396
		if (!isset($this->_parent)) {
397
			throw new PHPExcel_Exception('Cannot get data validation for cell that is not bound to a worksheet');
398
		}
399
 
400
		return $this->getWorksheet()->getDataValidation($this->getCoordinate());
401
	}
402
 
403
	/**
404
	 *	Set Data validation rules
405
	 *
406
	 *	@param	PHPExcel_Cell_DataValidation	$pDataValidation
407
	 *	@return	PHPExcel_Cell
408
	 *	@throws	PHPExcel_Exception
409
	 */
410
	public function setDataValidation(PHPExcel_Cell_DataValidation $pDataValidation = NULL)
411
	{
412
		if (!isset($this->_parent)) {
413
			throw new PHPExcel_Exception('Cannot set data validation for cell that is not bound to a worksheet');
414
		}
415
 
416
		$this->getWorksheet()->setDataValidation($this->getCoordinate(), $pDataValidation);
417
 
418
		return $this->notifyCacheController();
419
	}
420
 
421
	/**
422
	 *	Does this cell contain a Hyperlink?
423
	 *
424
	 *	@return boolean
425
	 *	@throws	PHPExcel_Exception
426
	 */
427
	public function hasHyperlink()
428
	{
429
		if (!isset($this->_parent)) {
430
			throw new PHPExcel_Exception('Cannot check for hyperlink when cell is not bound to a worksheet');
431
		}
432
 
433
		return $this->getWorksheet()->hyperlinkExists($this->getCoordinate());
434
	}
435
 
436
	/**
437
	 *	Get Hyperlink
438
	 *
439
	 *	@return	PHPExcel_Cell_Hyperlink
440
	 *	@throws	PHPExcel_Exception
441
	 */
442
	public function getHyperlink()
443
	{
444
		if (!isset($this->_parent)) {
445
			throw new PHPExcel_Exception('Cannot get hyperlink for cell that is not bound to a worksheet');
446
		}
447
 
448
		return $this->getWorksheet()->getHyperlink($this->getCoordinate());
449
	}
450
 
451
	/**
452
	 *	Set Hyperlink
453
	 *
454
	 *	@param	PHPExcel_Cell_Hyperlink	$pHyperlink
455
	 *	@return	PHPExcel_Cell
456
	 *	@throws	PHPExcel_Exception
457
	 */
458
	public function setHyperlink(PHPExcel_Cell_Hyperlink $pHyperlink = NULL)
459
	{
460
		if (!isset($this->_parent)) {
461
			throw new PHPExcel_Exception('Cannot set hyperlink for cell that is not bound to a worksheet');
462
		}
463
 
464
		$this->getWorksheet()->setHyperlink($this->getCoordinate(), $pHyperlink);
465
 
466
		return $this->notifyCacheController();
467
	}
468
 
469
	/**
470
	 *	Get parent worksheet
471
	 *
472
	 *	@return PHPExcel_Worksheet
473
	 */
474
	public function getParent() {
475
		return $this->_parent;
476
	}
477
 
478
	/**
479
	 *	Get parent worksheet
480
	 *
481
	 *	@return PHPExcel_Worksheet
482
	 */
483
	public function getWorksheet() {
484
		return $this->_parent->getParent();
485
	}
486
 
487
	/**
488
	 *	Get cell style
489
	 *
490
	 *	@return	PHPExcel_Style
491
	 */
492
	public function getStyle()
493
	{
494
		return $this->getWorksheet()->getParent()->getCellXfByIndex($this->getXfIndex());
495
	}
496
 
497
	/**
498
	 *	Re-bind parent
499
	 *
500
	 *	@param	PHPExcel_Worksheet $parent
501
	 *	@return	PHPExcel_Cell
502
	 */
503
	public function rebindParent(PHPExcel_Worksheet $parent) {
504
		$this->_parent = $parent->getCellCacheController();
505
 
506
		return $this->notifyCacheController();
507
	}
508
 
509
	/**
510
	 *	Is cell in a specific range?
511
	 *
512
	 *	@param	string	$pRange		Cell range (e.g. A1:A1)
513
	 *	@return	boolean
514
	 */
515
	public function isInRange($pRange = 'A1:A1')
516
	{
517
		list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
518
 
519
		// Translate properties
520
		$myColumn	= self::columnIndexFromString($this->getColumn());
521
		$myRow		= $this->getRow();
522
 
523
		// Verify if cell is in range
524
		return (($rangeStart[0] <= $myColumn) && ($rangeEnd[0] >= $myColumn) &&
525
				($rangeStart[1] <= $myRow) && ($rangeEnd[1] >= $myRow)
526
			   );
527
	}
528
 
529
	/**
530
	 *	Coordinate from string
531
	 *
532
	 *	@param	string	$pCoordinateString
533
	 *	@return	array	Array containing column and row (indexes 0 and 1)
534
	 *	@throws	PHPExcel_Exception
535
	 */
536
	public static function coordinateFromString($pCoordinateString = 'A1')
537
	{
538
		if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
539
			return array($matches[1],$matches[2]);
540
		} elseif ((strpos($pCoordinateString,':') !== FALSE) || (strpos($pCoordinateString,',') !== FALSE)) {
541
			throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
542
		} elseif ($pCoordinateString == '') {
543
			throw new PHPExcel_Exception('Cell coordinate can not be zero-length string');
544
		}
545
 
546
		throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString);
547
	}
548
 
549
	/**
550
	 *	Make string row, column or cell coordinate absolute
551
	 *
552
	 *	@param	string	$pCoordinateString		e.g. 'A' or '1' or 'A1'
553
	 *					Note that this value can be a row or column reference as well as a cell reference
554
	 *	@return	string	Absolute coordinate		e.g. '$A' or '$1' or '$A$1'
555
	 *	@throws	PHPExcel_Exception
556
	 */
557
	public static function absoluteReference($pCoordinateString = 'A1')
558
	{
559
		if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
560
			// Split out any worksheet name from the reference
561
			$worksheet = '';
562
			$cellAddress = explode('!',$pCoordinateString);
563
			if (count($cellAddress) > 1) {
564
				list($worksheet,$pCoordinateString) = $cellAddress;
565
			}
566
			if ($worksheet > '')	$worksheet .= '!';
567
 
568
			// Create absolute coordinate
569
			if (ctype_digit($pCoordinateString)) {
570
				return $worksheet . '$' . $pCoordinateString;
571
			} elseif (ctype_alpha($pCoordinateString)) {
572
				return $worksheet . '$' . strtoupper($pCoordinateString);
573
			}
574
			return $worksheet . self::absoluteCoordinate($pCoordinateString);
575
		}
576
 
577
		throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
578
	}
579
 
580
	/**
581
	 *	Make string coordinate absolute
582
	 *
583
	 *	@param	string	$pCoordinateString		e.g. 'A1'
584
	 *	@return	string	Absolute coordinate		e.g. '$A$1'
585
	 *	@throws	PHPExcel_Exception
586
	 */
587
	public static function absoluteCoordinate($pCoordinateString = 'A1')
588
	{
589
		if (strpos($pCoordinateString,':') === FALSE && strpos($pCoordinateString,',') === FALSE) {
590
			// Split out any worksheet name from the coordinate
591
			$worksheet = '';
592
			$cellAddress = explode('!',$pCoordinateString);
593
			if (count($cellAddress) > 1) {
594
				list($worksheet,$pCoordinateString) = $cellAddress;
595
			}
596
			if ($worksheet > '')	$worksheet .= '!';
597
 
598
			// Create absolute coordinate
599
			list($column, $row) = self::coordinateFromString($pCoordinateString);
600
			$column = ltrim($column,'$');
601
			$row = ltrim($row,'$');
602
			return $worksheet . '$' . $column . '$' . $row;
603
		}
604
 
605
		throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
606
	}
607
 
608
	/**
609
	 *	Split range into coordinate strings
610
	 *
611
	 *	@param	string	$pRange		e.g. 'B4:D9' or 'B4:D9,H2:O11' or 'B4'
612
	 *	@return	array	Array containg one or more arrays containing one or two coordinate strings
613
	 *								e.g. array('B4','D9') or array(array('B4','D9'),array('H2','O11'))
614
	 *										or array('B4')
615
	 */
616
	public static function splitRange($pRange = 'A1:A1')
617
	{
618
		// Ensure $pRange is a valid range
619
		if(empty($pRange)) {
620
			$pRange = self::DEFAULT_RANGE;
621
		}
622
 
623
		$exploded = explode(',', $pRange);
624
		$counter = count($exploded);
625
		for ($i = 0; $i < $counter; ++$i) {
626
			$exploded[$i] = explode(':', $exploded[$i]);
627
		}
628
		return $exploded;
629
	}
630
 
631
	/**
632
	 *	Build range from coordinate strings
633
	 *
634
	 *	@param	array	$pRange	Array containg one or more arrays containing one or two coordinate strings
635
	 *	@return	string	String representation of $pRange
636
	 *	@throws	PHPExcel_Exception
637
	 */
638
	public static function buildRange($pRange)
639
	{
640
		// Verify range
641
		if (!is_array($pRange) || empty($pRange) || !is_array($pRange[0])) {
642
			throw new PHPExcel_Exception('Range does not contain any information');
643
		}
644
 
645
		// Build range
646
		$imploded = array();
647
		$counter = count($pRange);
648
		for ($i = 0; $i < $counter; ++$i) {
649
			$pRange[$i] = implode(':', $pRange[$i]);
650
		}
651
		$imploded = implode(',', $pRange);
652
 
653
		return $imploded;
654
	}
655
 
656
	/**
657
	 *	Calculate range boundaries
658
	 *
659
	 *	@param	string	$pRange		Cell range (e.g. A1:A1)
660
	 *	@return	array	Range coordinates array(Start Cell, End Cell)
661
	 *					where Start Cell and End Cell are arrays (Column Number, Row Number)
662
	 */
663
	public static function rangeBoundaries($pRange = 'A1:A1')
664
	{
665
		// Ensure $pRange is a valid range
666
		if(empty($pRange)) {
667
			$pRange = self::DEFAULT_RANGE;
668
		}
669
 
670
		// Uppercase coordinate
671
		$pRange = strtoupper($pRange);
672
 
673
		// Extract range
674
		if (strpos($pRange, ':') === FALSE) {
675
			$rangeA = $rangeB = $pRange;
676
		} else {
677
			list($rangeA, $rangeB) = explode(':', $pRange);
678
		}
679
 
680
		// Calculate range outer borders
681
		$rangeStart = self::coordinateFromString($rangeA);
682
		$rangeEnd	= self::coordinateFromString($rangeB);
683
 
684
		// Translate column into index
685
		$rangeStart[0]	= self::columnIndexFromString($rangeStart[0]);
686
		$rangeEnd[0]	= self::columnIndexFromString($rangeEnd[0]);
687
 
688
		return array($rangeStart, $rangeEnd);
689
	}
690
 
691
	/**
692
	 *	Calculate range dimension
693
	 *
694
	 *	@param	string	$pRange		Cell range (e.g. A1:A1)
695
	 *	@return	array	Range dimension (width, height)
696
	 */
697
	public static function rangeDimension($pRange = 'A1:A1')
698
	{
699
		// Calculate range outer borders
700
		list($rangeStart,$rangeEnd) = self::rangeBoundaries($pRange);
701
 
702
		return array( ($rangeEnd[0] - $rangeStart[0] + 1), ($rangeEnd[1] - $rangeStart[1] + 1) );
703
	}
704
 
705
	/**
706
	 *	Calculate range boundaries
707
	 *
708
	 *	@param	string	$pRange		Cell range (e.g. A1:A1)
709
	 *	@return	array	Range coordinates array(Start Cell, End Cell)
710
	 *					where Start Cell and End Cell are arrays (Column ID, Row Number)
711
	 */
712
	public static function getRangeBoundaries($pRange = 'A1:A1')
713
	{
714
		// Ensure $pRange is a valid range
715
		if(empty($pRange)) {
716
			$pRange = self::DEFAULT_RANGE;
717
		}
718
 
719
		// Uppercase coordinate
720
		$pRange = strtoupper($pRange);
721
 
722
		// Extract range
723
		if (strpos($pRange, ':') === FALSE) {
724
			$rangeA = $rangeB = $pRange;
725
		} else {
726
			list($rangeA, $rangeB) = explode(':', $pRange);
727
		}
728
 
729
		return array( self::coordinateFromString($rangeA), self::coordinateFromString($rangeB));
730
	}
731
 
732
	/**
733
	 *	Column index from string
734
	 *
735
	 *	@param	string $pString
736
	 *	@return	int Column index (base 1 !!!)
737
	 */
738
	public static function columnIndexFromString($pString = 'A')
739
	{
740
		//	Using a lookup cache adds a slight memory overhead, but boosts speed
741
		//	caching using a static within the method is faster than a class static,
742
		//		though it's additional memory overhead
743
		static $_indexCache = array();
744
 
745
		if (isset($_indexCache[$pString]))
746
			return $_indexCache[$pString];
747
 
748
		//	It's surprising how costly the strtoupper() and ord() calls actually are, so we use a lookup array rather than use ord()
749
		//		and make it case insensitive to get rid of the strtoupper() as well. Because it's a static, there's no significant
750
		//		memory overhead either
751
		static $_columnLookup = array(
752
			'A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12, 'M' => 13,
753
			'N' => 14, 'O' => 15, 'P' => 16, 'Q' => 17, 'R' => 18, 'S' => 19, 'T' => 20, 'U' => 21, 'V' => 22, 'W' => 23, 'X' => 24, 'Y' => 25, 'Z' => 26,
754
			'a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5, 'f' => 6, 'g' => 7, 'h' => 8, 'i' => 9, 'j' => 10, 'k' => 11, 'l' => 12, 'm' => 13,
755
			'n' => 14, 'o' => 15, 'p' => 16, 'q' => 17, 'r' => 18, 's' => 19, 't' => 20, 'u' => 21, 'v' => 22, 'w' => 23, 'x' => 24, 'y' => 25, 'z' => 26
756
		);
757
 
758
		//	We also use the language construct isset() rather than the more costly strlen() function to match the length of $pString
759
		//		for improved performance
760
		if (isset($pString{0})) {
761
			if (!isset($pString{1})) {
762
				$_indexCache[$pString] = $_columnLookup[$pString];
763
				return $_indexCache[$pString];
764
			} elseif(!isset($pString{2})) {
765
				$_indexCache[$pString] = $_columnLookup[$pString{0}] * 26 + $_columnLookup[$pString{1}];
766
				return $_indexCache[$pString];
767
			} elseif(!isset($pString{3})) {
768
				$_indexCache[$pString] = $_columnLookup[$pString{0}] * 676 + $_columnLookup[$pString{1}] * 26 + $_columnLookup[$pString{2}];
769
				return $_indexCache[$pString];
770
			}
771
		}
772
		throw new PHPExcel_Exception("Column string index can not be " . ((isset($pString{0})) ? "longer than 3 characters" : "empty"));
773
	}
774
 
775
	/**
776
	 *	String from columnindex
777
	 *
778
	 *	@param	int $pColumnIndex Column index (base 0 !!!)
779
	 *	@return	string
780
	 */
781
	public static function stringFromColumnIndex($pColumnIndex = 0)
782
	{
783
		//	Using a lookup cache adds a slight memory overhead, but boosts speed
784
		//	caching using a static within the method is faster than a class static,
785
		//		though it's additional memory overhead
786
		static $_indexCache = array();
787
 
788
		if (!isset($_indexCache[$pColumnIndex])) {
789
			// Determine column string
790
			if ($pColumnIndex < 26) {
791
				$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
792
			} elseif ($pColumnIndex < 702) {
793
				$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
794
											  chr(65 + $pColumnIndex % 26);
795
			} else {
796
				$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
797
											  chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
798
											  chr(65 + $pColumnIndex % 26);
799
			}
800
		}
801
		return $_indexCache[$pColumnIndex];
802
	}
803
 
804
	/**
805
	 *	Extract all cell references in range
806
	 *
807
	 *	@param	string	$pRange		Range (e.g. A1 or A1:C10 or A1:E10 A20:E25)
808
	 *	@return	array	Array containing single cell references
809
	 */
810
	public static function extractAllCellReferencesInRange($pRange = 'A1') {
811
		// Returnvalue
812
		$returnValue = array();
813
 
814
		// Explode spaces
815
		$cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
816
		foreach ($cellBlocks as $cellBlock) {
817
			// Single cell?
818
			if (strpos($cellBlock,':') === FALSE && strpos($cellBlock,',') === FALSE) {
819
				$returnValue[] = $cellBlock;
820
				continue;
821
			}
822
 
823
			// Range...
824
			$ranges = self::splitRange($cellBlock);
825
			foreach($ranges as $range) {
826
				// Single cell?
827
				if (!isset($range[1])) {
828
					$returnValue[] = $range[0];
829
					continue;
830
				}
831
 
832
				// Range...
833
				list($rangeStart, $rangeEnd)	= $range;
834
				sscanf($rangeStart,'%[A-Z]%d', $startCol, $startRow);
835
				sscanf($rangeEnd,'%[A-Z]%d', $endCol, $endRow);
836
				$endCol++;
837
 
838
				// Current data
839
				$currentCol	= $startCol;
840
				$currentRow	= $startRow;
841
 
842
				// Loop cells
843
				while ($currentCol != $endCol) {
844
					while ($currentRow <= $endRow) {
845
						$returnValue[] = $currentCol.$currentRow;
846
						++$currentRow;
847
					}
848
					++$currentCol;
849
					$currentRow = $startRow;
850
				}
851
			}
852
		}
853
 
854
		//	Sort the result by column and row
855
		$sortKeys = array();
856
		foreach (array_unique($returnValue) as $coord) {
857
			sscanf($coord,'%[A-Z]%d', $column, $row);
858
			$sortKeys[sprintf('%3s%09d',$column,$row)] = $coord;
859
		}
860
		ksort($sortKeys);
861
 
862
		// Return value
863
		return array_values($sortKeys);
864
	}
865
 
866
	/**
867
	 * Compare 2 cells
868
	 *
869
	 * @param	PHPExcel_Cell	$a	Cell a
870
	 * @param	PHPExcel_Cell	$b	Cell b
871
	 * @return	int		Result of comparison (always -1 or 1, never zero!)
872
	 */
873
	public static function compareCells(PHPExcel_Cell $a, PHPExcel_Cell $b)
874
	{
875
		if ($a->getRow() < $b->getRow()) {
876
			return -1;
877
		} elseif ($a->getRow() > $b->getRow()) {
878
			return 1;
879
		} elseif (self::columnIndexFromString($a->getColumn()) < self::columnIndexFromString($b->getColumn())) {
880
			return -1;
881
		} else {
882
			return 1;
883
		}
884
	}
885
 
886
	/**
887
	 * Get value binder to use
888
	 *
889
	 * @return PHPExcel_Cell_IValueBinder
890
	 */
891
	public static function getValueBinder() {
892
		if (self::$_valueBinder === NULL) {
893
			self::$_valueBinder = new PHPExcel_Cell_DefaultValueBinder();
894
		}
895
 
896
		return self::$_valueBinder;
897
	}
898
 
899
	/**
900
	 * Set value binder to use
901
	 *
902
	 * @param PHPExcel_Cell_IValueBinder $binder
903
	 * @throws PHPExcel_Exception
904
	 */
905
	public static function setValueBinder(PHPExcel_Cell_IValueBinder $binder = NULL) {
906
		if ($binder === NULL) {
907
			throw new PHPExcel_Exception("A PHPExcel_Cell_IValueBinder is required for PHPExcel to function correctly.");
908
		}
909
 
910
		self::$_valueBinder = $binder;
911
	}
912
 
913
	/**
914
	 * Implement PHP __clone to create a deep clone, not just a shallow copy.
915
	 */
916
	public function __clone() {
917
		$vars = get_object_vars($this);
918
		foreach ($vars as $key => $value) {
919
			if ((is_object($value)) && ($key != '_parent')) {
920
				$this->$key = clone $value;
921
			} else {
922
				$this->$key = $value;
923
			}
924
		}
925
	}
926
 
927
	/**
928
	 * Get index to cellXf
929
	 *
930
	 * @return int
931
	 */
932
	public function getXfIndex()
933
	{
934
		return $this->_xfIndex;
935
	}
936
 
937
	/**
938
	 * Set index to cellXf
939
	 *
940
	 * @param int $pValue
941
	 * @return PHPExcel_Cell
942
	 */
943
	public function setXfIndex($pValue = 0)
944
	{
945
		$this->_xfIndex = $pValue;
946
 
947
		return $this->notifyCacheController();
948
	}
949
 
950
	/**
951
	 *	@deprecated		Since version 1.7.8 for planned changes to cell for array formula handling
952
	 */
953
	public function setFormulaAttributes($pAttributes)
954
	{
955
		$this->_formulaAttributes = $pAttributes;
956
		return $this;
957
	}
958
 
959
	/**
960
	 *	@deprecated		Since version 1.7.8 for planned changes to cell for array formula handling
961
	 */
962
	public function getFormulaAttributes()
963
	{
964
		return $this->_formulaAttributes;
965
	}
966
 
967
    /**
968
     * Convert to string
969
     *
970
     * @return string
971
     */
972
	public function __toString()
973
	{
974
		return (string) $this->getValue();
975
	}
976
 
977
}
978