Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | 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
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_ReferenceHelper (Singleton)
31
 *
32
 * @category   PHPExcel
33
 * @package	PHPExcel
34
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
35
 */
36
class PHPExcel_ReferenceHelper
37
{
38
	/**	Constants				*/
39
	/**	Regular Expressions		*/
40
	const REFHELPER_REGEXP_CELLREF		= '((\w*|\'[^!]*\')!)?(?<![:a-z\$])(\$?[a-z]{1,3}\$?\d+)(?=[^:!\d\'])';
41
	const REFHELPER_REGEXP_CELLRANGE	= '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}\$?\d+):(\$?[a-z]{1,3}\$?\d+)';
42
	const REFHELPER_REGEXP_ROWRANGE		= '((\w*|\'[^!]*\')!)?(\$?\d+):(\$?\d+)';
43
	const REFHELPER_REGEXP_COLRANGE		= '((\w*|\'[^!]*\')!)?(\$?[a-z]{1,3}):(\$?[a-z]{1,3})';
44
 
45
	/**
46
	 * Instance of this class
47
	 *
48
	 * @var PHPExcel_ReferenceHelper
49
	 */
50
	private static $_instance;
51
 
52
	/**
53
	 * Get an instance of this class
54
	 *
55
	 * @return PHPExcel_ReferenceHelper
56
	 */
57
	public static function getInstance() {
58
		if (!isset(self::$_instance) || (self::$_instance === NULL)) {
59
			self::$_instance = new PHPExcel_ReferenceHelper();
60
		}
61
 
62
		return self::$_instance;
63
	}
64
 
65
	/**
66
	 * Create a new PHPExcel_ReferenceHelper
67
	 */
68
	protected function __construct() {
69
	}
70
 
71
	/**
72
	 * Compare two column addresses
73
	 * Intended for use as a Callback function for sorting column addresses by column
74
	 *
75
	 * @param   string   $a  First column to test (e.g. 'AA')
76
	 * @param   string   $b  Second column to test (e.g. 'Z')
77
	 * @return  integer
78
	 */
79
	public static function columnSort($a, $b) {
80
		return strcasecmp(strlen($a) . $a, strlen($b) . $b);
81
	}
82
 
83
	/**
84
	 * Compare two column addresses
85
	 * Intended for use as a Callback function for reverse sorting column addresses by column
86
	 *
87
	 * @param   string   $a  First column to test (e.g. 'AA')
88
	 * @param   string   $b  Second column to test (e.g. 'Z')
89
	 * @return  integer
90
	 */
91
	public static function columnReverseSort($a, $b) {
92
		return 1 - strcasecmp(strlen($a) . $a, strlen($b) . $b);
93
	}
94
 
95
	/**
96
	 * Compare two cell addresses
97
	 * Intended for use as a Callback function for sorting cell addresses by column and row
98
	 *
99
	 * @param   string   $a  First cell to test (e.g. 'AA1')
100
	 * @param   string   $b  Second cell to test (e.g. 'Z1')
101
	 * @return  integer
102
	 */
103
	public static function cellSort($a, $b) {
104
		sscanf($a,'%[A-Z]%d', $ac, $ar);
105
		sscanf($b,'%[A-Z]%d', $bc, $br);
106
 
107
		if ($ar == $br) {
108
			return strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
109
		}
110
		return ($ar < $br) ? -1 : 1;
111
	}
112
 
113
	/**
114
	 * Compare two cell addresses
115
	 * Intended for use as a Callback function for sorting cell addresses by column and row
116
	 *
117
	 * @param   string   $a  First cell to test (e.g. 'AA1')
118
	 * @param   string   $b  Second cell to test (e.g. 'Z1')
119
	 * @return  integer
120
	 */
121
	public static function cellReverseSort($a, $b) {
122
		sscanf($a,'%[A-Z]%d', $ac, $ar);
123
		sscanf($b,'%[A-Z]%d', $bc, $br);
124
 
125
		if ($ar == $br) {
126
			return 1 - strcasecmp(strlen($ac) . $ac, strlen($bc) . $bc);
127
		}
128
		return ($ar < $br) ? 1 : -1;
129
	}
130
 
131
	/**
132
	 * Test whether a cell address falls within a defined range of cells
133
	 *
134
	 * @param   string     $cellAddress        Address of the cell we're testing
135
	 * @param   integer    $beforeRow          Number of the row we're inserting/deleting before
136
	 * @param   integer    $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
137
	 * @param   integer    $beforeColumnIndex  Index number of the column we're inserting/deleting before
138
	 * @param   integer    $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
139
	 * @return  boolean
140
	 */
141
	private static function cellAddressInDeleteRange($cellAddress, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols) {
142
		list($cellColumn, $cellRow) = PHPExcel_Cell::coordinateFromString($cellAddress);
143
		$cellColumnIndex = PHPExcel_Cell::columnIndexFromString($cellColumn);
144
		//	Is cell within the range of rows/columns if we're deleting
145
		if ($pNumRows < 0 &&
146
			($cellRow >= ($beforeRow + $pNumRows)) &&
147
			($cellRow < $beforeRow)) {
148
			return TRUE;
149
		} elseif ($pNumCols < 0 &&
150
			($cellColumnIndex >= ($beforeColumnIndex + $pNumCols)) &&
151
			($cellColumnIndex < $beforeColumnIndex)) {
152
			return TRUE;
153
		}
154
		return FALSE;
155
	}
156
 
157
	/**
158
	 * Update page breaks when inserting/deleting rows/columns
159
	 *
160
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
161
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
162
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
163
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
164
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
165
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
166
	 */
167
	protected function _adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
168
	{
169
		$aBreaks = $pSheet->getBreaks();
170
		($pNumCols > 0 || $pNumRows > 0) ?
171
			uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
172
			uksort($aBreaks, array('PHPExcel_ReferenceHelper','cellSort'));
173
 
174
		foreach ($aBreaks as $key => $value) {
175
			if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
176
				//	If we're deleting, then clear any defined breaks that are within the range
177
				//		of rows/columns that we're deleting
178
				$pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
179
			} else {
180
				//	Otherwise update any affected breaks by inserting a new break at the appropriate point
181
				//		and removing the old affected break
182
				$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
183
				if ($key != $newReference) {
184
					$pSheet->setBreak($newReference, $value)
185
					    ->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
186
				}
187
			}
188
		}
189
	}
190
 
191
	/**
192
	 * Update cell comments when inserting/deleting rows/columns
193
	 *
194
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
195
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
196
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
197
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
198
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
199
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
200
	 */
201
	protected function _adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
202
	{
203
		$aComments = $pSheet->getComments();
204
		$aNewComments = array(); // the new array of all comments
205
 
206
		foreach ($aComments as $key => &$value) {
207
			// Any comments inside a deleted range will be ignored
208
			if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
209
				//	Otherwise build a new array of comments indexed by the adjusted cell reference
210
				$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
211
				$aNewComments[$newReference] = $value;
212
			}
213
		}
214
		//	Replace the comments array with the new set of comments
215
		$pSheet->setComments($aNewComments);
216
	}
217
 
218
	/**
219
	 * Update hyperlinks when inserting/deleting rows/columns
220
	 *
221
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
222
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
223
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
224
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
225
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
226
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
227
	 */
228
	protected function _adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
229
	{
230
		$aHyperlinkCollection = $pSheet->getHyperlinkCollection();
231
		($pNumCols > 0 || $pNumRows > 0) ?
232
			uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
233
			uksort($aHyperlinkCollection, array('PHPExcel_ReferenceHelper','cellSort'));
234
 
235
		foreach ($aHyperlinkCollection as $key => $value) {
236
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
237
			if ($key != $newReference) {
238
				$pSheet->setHyperlink( $newReference, $value );
239
				$pSheet->setHyperlink( $key, null );
240
			}
241
		}
242
	}
243
 
244
	/**
245
	 * Update data validations when inserting/deleting rows/columns
246
	 *
247
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
248
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
249
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
250
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
251
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
252
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
253
	 */
254
	protected function _adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
255
	{
256
		$aDataValidationCollection = $pSheet->getDataValidationCollection();
257
		($pNumCols > 0 || $pNumRows > 0) ?
258
			uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
259
			uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper','cellSort'));
260
		foreach ($aDataValidationCollection as $key => $value) {
261
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
262
			if ($key != $newReference) {
263
				$pSheet->setDataValidation( $newReference, $value );
264
				$pSheet->setDataValidation( $key, null );
265
			}
266
		}
267
	}
268
 
269
	/**
270
	 * Update merged cells when inserting/deleting rows/columns
271
	 *
272
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
273
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
274
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
275
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
276
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
277
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
278
	 */
279
	protected function _adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
280
	{
281
		$aMergeCells = $pSheet->getMergeCells();
282
		$aNewMergeCells = array(); // the new array of all merge cells
283
		foreach ($aMergeCells as $key => &$value) {
284
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
285
			$aNewMergeCells[$newReference] = $newReference;
286
		}
287
		$pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array
288
	}
289
 
290
	/**
291
	 * Update protected cells when inserting/deleting rows/columns
292
	 *
293
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
294
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
295
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
296
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
297
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
298
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
299
	 */
300
	protected function _adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
301
	{
302
		$aProtectedCells = $pSheet->getProtectedCells();
303
		($pNumCols > 0 || $pNumRows > 0) ?
304
			uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellReverseSort')) :
305
			uksort($aProtectedCells, array('PHPExcel_ReferenceHelper','cellSort'));
306
		foreach ($aProtectedCells as $key => $value) {
307
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
308
			if ($key != $newReference) {
309
				$pSheet->protectCells( $newReference, $value, true );
310
				$pSheet->unprotectCells( $key );
311
			}
312
		}
313
	}
314
 
315
	/**
316
	 * Update column dimensions when inserting/deleting rows/columns
317
	 *
318
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
319
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
320
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
321
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
322
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
323
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
324
	 */
325
	protected function _adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
326
	{
327
		$aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
328
		if (!empty($aColumnDimensions)) {
329
			foreach ($aColumnDimensions as $objColumnDimension) {
330
				$newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
331
				list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
332
				if ($objColumnDimension->getColumnIndex() != $newReference) {
333
					$objColumnDimension->setColumnIndex($newReference);
334
				}
335
			}
336
			$pSheet->refreshColumnDimensions();
337
		}
338
	}
339
 
340
	/**
341
	 * Update row dimensions when inserting/deleting rows/columns
342
	 *
343
	 * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
344
	 * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
345
	 * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
346
	 * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
347
	 * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
348
	 * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
349
	 */
350
	protected function _adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
351
	{
352
		$aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
353
		if (!empty($aRowDimensions)) {
354
			foreach ($aRowDimensions as $objRowDimension) {
355
				$newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
356
				list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
357
				if ($objRowDimension->getRowIndex() != $newReference) {
358
					$objRowDimension->setRowIndex($newReference);
359
				}
360
			}
361
			$pSheet->refreshRowDimensions();
362
 
363
			$copyDimension = $pSheet->getRowDimension($beforeRow - 1);
364
			for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
365
				$newDimension = $pSheet->getRowDimension($i);
366
				$newDimension->setRowHeight($copyDimension->getRowHeight());
367
				$newDimension->setVisible($copyDimension->getVisible());
368
				$newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
369
				$newDimension->setCollapsed($copyDimension->getCollapsed());
370
			}
371
		}
372
	}
373
 
374
	/**
375
	 * Insert a new column or row, updating all possible related data
376
	 *
377
	 * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
378
	 * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
379
	 * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
380
	 * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
381
	 * @throws  PHPExcel_Exception
382
	 */
383
	public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL)
384
	{
385
		$remove = ($pNumCols < 0 || $pNumRows < 0);
386
		$aCellCollection = $pSheet->getCellCollection();
387
 
388
		// Get coordinates of $pBefore
389
		$beforeColumn	= 'A';
390
		$beforeRow		= 1;
391
		list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
392
		$beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
393
 
394
		// Clear cells if we are removing columns or rows
395
		$highestColumn	= $pSheet->getHighestColumn();
396
		$highestRow	= $pSheet->getHighestRow();
397
 
398
		// 1. Clear column strips if we are removing columns
399
		if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
400
			for ($i = 1; $i <= $highestRow - 1; ++$i) {
401
				for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
402
					$coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
403
					$pSheet->removeConditionalStyles($coordinate);
404
					if ($pSheet->cellExists($coordinate)) {
405
						$pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
406
						$pSheet->getCell($coordinate)->setXfIndex(0);
407
					}
408
				}
409
			}
410
		}
411
 
412
		// 2. Clear row strips if we are removing rows
413
		if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
414
			for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
415
				for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
416
					$coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
417
					$pSheet->removeConditionalStyles($coordinate);
418
					if ($pSheet->cellExists($coordinate)) {
419
						$pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
420
						$pSheet->getCell($coordinate)->setXfIndex(0);
421
					}
422
				}
423
			}
424
		}
425
 
426
		// Loop through cells, bottom-up, and change cell coordinates
427
		while (($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection))) {
428
			$cell = $pSheet->getCell($cellID);
429
			$cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
430
			if ($cellIndex-1 + $pNumCols < 0) {
431
				continue;
432
			}
433
 
434
			// New coordinates
435
			$newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex-1 + $pNumCols) . ($cell->getRow() + $pNumRows);
436
 
437
			// Should the cell be updated? Move value and cellXf index from one cell to another.
438
			if (($cellIndex >= $beforeColumnIndex) &&
439
				($cell->getRow() >= $beforeRow)) {
440
 
441
				// Update cell styles
442
				$pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
443
 
444
				// Insert this cell at its new location
445
				if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
446
					// Formula should be adjusted
447
					$pSheet->getCell($newCoordinates)
448
						   ->setValue($this->updateFormulaReferences($cell->getValue(),
449
						   					$pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
450
				} else {
451
					// Formula should not be adjusted
452
					$pSheet->getCell($newCoordinates)->setValue($cell->getValue());
453
				}
454
 
455
				// Clear the original cell
456
				$pSheet->getCellCacheController()->deleteCacheData($cellID);
457
 
458
			} else {
459
				/*	We don't need to update styles for rows/columns before our insertion position,
460
						but we do still need to adjust any formulae	in those cells					*/
461
				if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
462
					// Formula should be adjusted
463
					$cell->setValue($this->updateFormulaReferences($cell->getValue(),
464
										$pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
465
				}
466
 
467
			}
468
		}
469
 
470
		// Duplicate styles for the newly inserted cells
471
		$highestColumn	= $pSheet->getHighestColumn();
472
		$highestRow	= $pSheet->getHighestRow();
473
 
474
		if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
475
			for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
476
 
477
				// Style
478
				$coordinate = PHPExcel_Cell::stringFromColumnIndex( $beforeColumnIndex - 2 ) . $i;
479
				if ($pSheet->cellExists($coordinate)) {
480
					$xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
481
					$conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
482
						$pSheet->getConditionalStyles($coordinate) : false;
483
					for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
484
						$pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
485
						if ($conditionalStyles) {
486
							$cloned = array();
487
							foreach ($conditionalStyles as $conditionalStyle) {
488
								$cloned[] = clone $conditionalStyle;
489
							}
490
							$pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
491
						}
492
					}
493
				}
494
 
495
			}
496
		}
497
 
498
		if ($pNumRows > 0 && $beforeRow - 1 > 0) {
499
			for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
500
 
501
				// Style
502
				$coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
503
				if ($pSheet->cellExists($coordinate)) {
504
					$xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
505
					$conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ?
506
						$pSheet->getConditionalStyles($coordinate) : false;
507
					for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
508
						$pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
509
						if ($conditionalStyles) {
510
							$cloned = array();
511
							foreach ($conditionalStyles as $conditionalStyle) {
512
								$cloned[] = clone $conditionalStyle;
513
							}
514
							$pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
515
						}
516
					}
517
				}
518
			}
519
		}
520
 
521
		// Update worksheet: column dimensions
522
		$this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
523
 
524
		// Update worksheet: row dimensions
525
		$this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
526
 
527
		//	Update worksheet: page breaks
528
		$this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
529
 
530
		//	Update worksheet: comments
531
		$this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
532
 
533
		// Update worksheet: hyperlinks
534
		$this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
535
 
536
		// Update worksheet: data validations
537
		$this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
538
 
539
		// Update worksheet: merge cells
540
		$this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
541
 
542
		// Update worksheet: protected cells
543
		$this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
544
 
545
		// Update worksheet: autofilter
546
		$autoFilter = $pSheet->getAutoFilter();
547
		$autoFilterRange = $autoFilter->getRange();
548
		if (!empty($autoFilterRange)) {
549
			if ($pNumCols != 0) {
550
				$autoFilterColumns = array_keys($autoFilter->getColumns());
551
				if (count($autoFilterColumns) > 0) {
552
					sscanf($pBefore,'%[A-Z]%d', $column, $row);
553
					$columnIndex = PHPExcel_Cell::columnIndexFromString($column);
554
					list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
555
					if ($columnIndex <= $rangeEnd[0]) {
556
						if ($pNumCols < 0) {
557
							//	If we're actually deleting any columns that fall within the autofilter range,
558
							//		then we delete any rules for those columns
559
							$deleteColumn = $columnIndex + $pNumCols - 1;
560
							$deleteCount = abs($pNumCols);
561
							for ($i = 1; $i <= $deleteCount; ++$i) {
562
								if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn),$autoFilterColumns)) {
563
									$autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
564
								}
565
								++$deleteColumn;
566
							}
567
						}
568
						$startCol = ($columnIndex > $rangeStart[0]) ? $columnIndex : $rangeStart[0];
569
 
570
						//	Shuffle columns in autofilter range
571
						if ($pNumCols > 0) {
572
							//	For insert, we shuffle from end to beginning to avoid overwriting
573
							$startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
574
							$toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
575
							$endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
576
 
577
							$startColRef = $startCol;
578
							$endColRef = $rangeEnd[0];
579
							$toColRef = $rangeEnd[0]+$pNumCols;
580
 
581
							do {
582
								$autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef-1),PHPExcel_Cell::stringFromColumnIndex($toColRef-1));
583
								--$endColRef;
584
								--$toColRef;
585
							} while ($startColRef <= $endColRef);
586
						} else {
587
							//	For delete, we shuffle from beginning to end to avoid overwriting
588
							$startColID = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
589
							$toColID = PHPExcel_Cell::stringFromColumnIndex($startCol+$pNumCols-1);
590
							$endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
591
							do {
592
								$autoFilter->shiftColumn($startColID,$toColID);
593
								++$startColID;
594
								++$toColID;
595
							} while ($startColID != $endColID);
596
						}
597
					}
598
				}
599
			}
600
			$pSheet->setAutoFilter( $this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows) );
601
		}
602
 
603
		// Update worksheet: freeze pane
604
		if ($pSheet->getFreezePane() != '') {
605
			$pSheet->freezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) );
606
		}
607
 
608
		// Page setup
609
		if ($pSheet->getPageSetup()->isPrintAreaSet()) {
610
			$pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) );
611
		}
612
 
613
		// Update worksheet: drawings
614
		$aDrawings = $pSheet->getDrawingCollection();
615
		foreach ($aDrawings as $objDrawing) {
616
			$newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
617
			if ($objDrawing->getCoordinates() != $newReference) {
618
				$objDrawing->setCoordinates($newReference);
619
			}
620
		}
621
 
622
		// Update workbook: named ranges
623
		if (count($pSheet->getParent()->getNamedRanges()) > 0) {
624
			foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
625
				if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
626
					$namedRange->setRange(
627
						$this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)
628
					);
629
				}
630
			}
631
		}
632
 
633
		// Garbage collect
634
		$pSheet->garbageCollect();
635
	}
636
 
637
	/**
638
	 * Update references within formulas
639
	 *
640
	 * @param	string	$pFormula	Formula to update
641
	 * @param	int		$pBefore	Insert before this one
642
	 * @param	int		$pNumCols	Number of columns to insert
643
	 * @param	int		$pNumRows	Number of rows to insert
644
	 * @param   string  $sheetName  Worksheet name/title
645
	 * @return	string	Updated formula
646
	 * @throws	PHPExcel_Exception
647
	 */
648
	public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, $sheetName = '') {
649
		//	Update cell references in the formula
650
		$formulaBlocks = explode('"',$pFormula);
651
		$i = false;
652
		foreach($formulaBlocks as &$formulaBlock) {
653
			//	Ignore blocks that were enclosed in quotes (alternating entries in the $formulaBlocks array after the explode)
654
			if ($i = !$i) {
655
				$adjustCount = 0;
656
				$newCellTokens = $cellTokens = array();
657
				//	Search for row ranges (e.g. 'Sheet1'!3:5 or 3:5) with or without $ absolutes (e.g. $3:5)
658
				$matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_ROWRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
659
				if ($matchCount > 0) {
660
					foreach($matches as $match) {
661
						$fromString = ($match[2] > '') ? $match[2].'!' : '';
662
						$fromString .= $match[3].':'.$match[4];
663
						$modified3 = substr($this->updateCellReference('$A'.$match[3],$pBefore,$pNumCols,$pNumRows),2);
664
						$modified4 = substr($this->updateCellReference('$A'.$match[4],$pBefore,$pNumCols,$pNumRows),2);
665
 
666
						if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
667
							if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
668
								$toString = ($match[2] > '') ? $match[2].'!' : '';
669
								$toString .= $modified3.':'.$modified4;
670
								//	Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
671
								$column = 100000;
672
								$row = 10000000+trim($match[3],'$');
673
								$cellIndex = $column.$row;
674
 
675
								$newCellTokens[$cellIndex] = preg_quote($toString);
676
								$cellTokens[$cellIndex] = '/(?<!\d)'.preg_quote($fromString).'(?!\d)/i';
677
								++$adjustCount;
678
							}
679
						}
680
					}
681
				}
682
				//	Search for column ranges (e.g. 'Sheet1'!C:E or C:E) with or without $ absolutes (e.g. $C:E)
683
				$matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_COLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
684
				if ($matchCount > 0) {
685
					foreach($matches as $match) {
686
						$fromString = ($match[2] > '') ? $match[2].'!' : '';
687
						$fromString .= $match[3].':'.$match[4];
688
						$modified3 = substr($this->updateCellReference($match[3].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
689
						$modified4 = substr($this->updateCellReference($match[4].'$1',$pBefore,$pNumCols,$pNumRows),0,-2);
690
 
691
						if ($match[3].':'.$match[4] !== $modified3.':'.$modified4) {
692
							if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
693
								$toString = ($match[2] > '') ? $match[2].'!' : '';
694
								$toString .= $modified3.':'.$modified4;
695
								//	Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
696
								$column = PHPExcel_Cell::columnIndexFromString(trim($match[3],'$')) + 100000;
697
								$row = 10000000;
698
								$cellIndex = $column.$row;
699
 
700
								$newCellTokens[$cellIndex] = preg_quote($toString);
701
								$cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?![A-Z])/i';
702
								++$adjustCount;
703
							}
704
						}
705
					}
706
				}
707
				//	Search for cell ranges (e.g. 'Sheet1'!A3:C5 or A3:C5) with or without $ absolutes (e.g. $A1:C$5)
708
				$matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLRANGE.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
709
				if ($matchCount > 0) {
710
					foreach($matches as $match) {
711
						$fromString = ($match[2] > '') ? $match[2].'!' : '';
712
						$fromString .= $match[3].':'.$match[4];
713
						$modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
714
						$modified4 = $this->updateCellReference($match[4],$pBefore,$pNumCols,$pNumRows);
715
 
716
						if ($match[3].$match[4] !== $modified3.$modified4) {
717
							if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
718
								$toString = ($match[2] > '') ? $match[2].'!' : '';
719
								$toString .= $modified3.':'.$modified4;
720
								list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
721
								//	Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
722
								$column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
723
								$row = trim($row,'$') + 10000000;
724
								$cellIndex = $column.$row;
725
 
726
								$newCellTokens[$cellIndex] = preg_quote($toString);
727
								$cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?!\d)/i';
728
								++$adjustCount;
729
							}
730
						}
731
					}
732
				}
733
				//	Search for cell references (e.g. 'Sheet1'!A3 or C5) with or without $ absolutes (e.g. $A1 or C$5)
734
				$matchCount = preg_match_all('/'.self::REFHELPER_REGEXP_CELLREF.'/i', ' '.$formulaBlock.' ', $matches, PREG_SET_ORDER);
735
				if ($matchCount > 0) {
736
					foreach($matches as $match) {
737
						$fromString = ($match[2] > '') ? $match[2].'!' : '';
738
						$fromString .= $match[3];
739
						$modified3 = $this->updateCellReference($match[3],$pBefore,$pNumCols,$pNumRows);
740
 
741
						if ($match[3] !== $modified3) {
742
							if (($match[2] == '') || (trim($match[2],"'") == $sheetName)) {
743
								$toString = ($match[2] > '') ? $match[2].'!' : '';
744
								$toString .= $modified3;
745
								list($column,$row) = PHPExcel_Cell::coordinateFromString($match[3]);
746
								//	Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
747
								$column = PHPExcel_Cell::columnIndexFromString(trim($column,'$')) + 100000;
748
								$row = trim($row,'$') + 10000000;
749
								$cellIndex = $column.$row;
750
 
751
								$newCellTokens[$cellIndex] = preg_quote($toString);
752
								$cellTokens[$cellIndex] = '/(?<![A-Z])'.preg_quote($fromString).'(?!\d)/i';
753
								++$adjustCount;
754
							}
755
						}
756
					}
757
				}
758
				if ($adjustCount > 0) {
759
					if ($pNumCols > 0) {
760
						krsort($cellTokens);
761
						krsort($newCellTokens);
762
					} else {
763
						ksort($cellTokens);
764
						ksort($newCellTokens);
765
					}
766
					//	Update cell references in the formula
767
					$formulaBlock = str_replace('\\','',preg_replace($cellTokens,$newCellTokens,$formulaBlock));
768
				}
769
			}
770
		}
771
		unset($formulaBlock);
772
 
773
		//	Then rebuild the formula string
774
		return implode('"',$formulaBlocks);
775
	}
776
 
777
	/**
778
	 * Update cell reference
779
	 *
780
	 * @param	string	$pCellRange			Cell range
781
	 * @param	int		$pBefore			Insert before this one
782
	 * @param	int		$pNumCols			Number of columns to increment
783
	 * @param	int		$pNumRows			Number of rows to increment
784
	 * @return	string	Updated cell range
785
	 * @throws	PHPExcel_Exception
786
	 */
787
	public function updateCellReference($pCellRange = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
788
		// Is it in another worksheet? Will not have to update anything.
789
		if (strpos($pCellRange, "!") !== false) {
790
			return $pCellRange;
791
		// Is it a range or a single cell?
792
		} elseif (strpos($pCellRange, ':') === false && strpos($pCellRange, ',') === false) {
793
			// Single cell
794
			return $this->_updateSingleCellReference($pCellRange, $pBefore, $pNumCols, $pNumRows);
795
		} elseif (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
796
			// Range
797
			return $this->_updateCellRange($pCellRange, $pBefore, $pNumCols, $pNumRows);
798
		} else {
799
			// Return original
800
			return $pCellRange;
801
		}
802
	}
803
 
804
	/**
805
	 * Update named formulas (i.e. containing worksheet references / named ranges)
806
	 *
807
	 * @param PHPExcel $pPhpExcel	Object to update
808
	 * @param string $oldName		Old name (name to replace)
809
	 * @param string $newName		New name
810
	 */
811
	public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '') {
812
		if ($oldName == '') {
813
			return;
814
		}
815
 
816
		foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
817
			foreach ($sheet->getCellCollection(false) as $cellID) {
818
				$cell = $sheet->getCell($cellID);
819
				if (($cell !== NULL) && ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA)) {
820
					$formula = $cell->getValue();
821
					if (strpos($formula, $oldName) !== false) {
822
						$formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
823
						$formula = str_replace($oldName . "!", $newName . "!", $formula);
824
						$cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
825
					}
826
				}
827
			}
828
		}
829
	}
830
 
831
	/**
832
	 * Update cell range
833
	 *
834
	 * @param	string	$pCellRange			Cell range	(e.g. 'B2:D4', 'B:C' or '2:3')
835
	 * @param	int		$pBefore			Insert before this one
836
	 * @param	int		$pNumCols			Number of columns to increment
837
	 * @param	int		$pNumRows			Number of rows to increment
838
	 * @return	string	Updated cell range
839
	 * @throws	PHPExcel_Exception
840
	 */
841
	private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
842
		if (strpos($pCellRange,':') !== false || strpos($pCellRange, ',') !== false) {
843
			// Update range
844
			$range = PHPExcel_Cell::splitRange($pCellRange);
845
			$ic = count($range);
846
			for ($i = 0; $i < $ic; ++$i) {
847
				$jc = count($range[$i]);
848
				for ($j = 0; $j < $jc; ++$j) {
849
					if (ctype_alpha($range[$i][$j])) {
850
						$r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j].'1', $pBefore, $pNumCols, $pNumRows));
851
						$range[$i][$j] = $r[0];
852
					} elseif(ctype_digit($range[$i][$j])) {
853
						$r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A'.$range[$i][$j], $pBefore, $pNumCols, $pNumRows));
854
						$range[$i][$j] = $r[1];
855
					} else {
856
						$range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
857
					}
858
				}
859
			}
860
 
861
			// Recreate range string
862
			return PHPExcel_Cell::buildRange($range);
863
		} else {
864
			throw new PHPExcel_Exception("Only cell ranges may be passed to this method.");
865
		}
866
	}
867
 
868
	/**
869
	 * Update single cell reference
870
	 *
871
	 * @param	string	$pCellReference		Single cell reference
872
	 * @param	int		$pBefore			Insert before this one
873
	 * @param	int		$pNumCols			Number of columns to increment
874
	 * @param	int		$pNumRows			Number of rows to increment
875
	 * @return	string	Updated cell reference
876
	 * @throws	PHPExcel_Exception
877
	 */
878
	private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
879
		if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
880
			// Get coordinates of $pBefore
881
			list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );
882
 
883
			// Get coordinates of $pCellReference
884
			list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString( $pCellReference );
885
 
886
			// Verify which parts should be updated
887
			$updateColumn = (($newColumn{0} != '$') && ($beforeColumn{0} != '$') &&
888
							 PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn));
889
 
890
			$updateRow = (($newRow{0} != '$') && ($beforeRow{0} != '$') &&
891
						  $newRow >= $beforeRow);
892
 
893
			// Create new column reference
894
			if ($updateColumn) {
895
				$newColumn	= PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols );
896
			}
897
 
898
			// Create new row reference
899
			if ($updateRow) {
900
				$newRow	= $newRow + $pNumRows;
901
			}
902
 
903
			// Return new reference
904
			return $newColumn . $newRow;
905
		} else {
906
			throw new PHPExcel_Exception("Only single cell references may be passed to this method.");
907
		}
908
	}
909
 
910
	/**
911
	 * __clone implementation. Cloning should not be allowed in a Singleton!
912
	 *
913
	 * @throws	PHPExcel_Exception
914
	 */
915
	public final function __clone() {
916
		throw new PHPExcel_Exception("Cloning a Singleton is not allowed!");
917
	}
918
}