ReferenceHelper.php 43 KB

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