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