1 | <?php |
||
2 | |||
3 | namespace PhpOffice\PhpSpreadsheet\Worksheet; |
||
4 | |||
5 | use ArrayObject; |
||
6 | use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
||
7 | use PhpOffice\PhpSpreadsheet\Cell\Cell; |
||
8 | use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
||
9 | use PhpOffice\PhpSpreadsheet\Cell\DataType; |
||
10 | use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
||
11 | use PhpOffice\PhpSpreadsheet\Cell\Hyperlink; |
||
12 | use PhpOffice\PhpSpreadsheet\Chart\Chart; |
||
13 | use PhpOffice\PhpSpreadsheet\Collection\Cells; |
||
14 | use PhpOffice\PhpSpreadsheet\Collection\CellsFactory; |
||
15 | use PhpOffice\PhpSpreadsheet\Comment; |
||
16 | use PhpOffice\PhpSpreadsheet\Exception; |
||
17 | use PhpOffice\PhpSpreadsheet\IComparable; |
||
18 | use PhpOffice\PhpSpreadsheet\NamedRange; |
||
19 | use PhpOffice\PhpSpreadsheet\ReferenceHelper; |
||
20 | use PhpOffice\PhpSpreadsheet\RichText\RichText; |
||
21 | use PhpOffice\PhpSpreadsheet\Shared; |
||
22 | use PhpOffice\PhpSpreadsheet\Spreadsheet; |
||
23 | use PhpOffice\PhpSpreadsheet\Style\Color; |
||
24 | use PhpOffice\PhpSpreadsheet\Style\Conditional; |
||
25 | use PhpOffice\PhpSpreadsheet\Style\NumberFormat; |
||
26 | use PhpOffice\PhpSpreadsheet\Style\Style; |
||
27 | |||
28 | class Worksheet implements IComparable |
||
29 | { |
||
30 | // Break types |
||
31 | const BREAK_NONE = 0; |
||
32 | const BREAK_ROW = 1; |
||
33 | const BREAK_COLUMN = 2; |
||
34 | |||
35 | // Sheet state |
||
36 | const SHEETSTATE_VISIBLE = 'visible'; |
||
37 | const SHEETSTATE_HIDDEN = 'hidden'; |
||
38 | const SHEETSTATE_VERYHIDDEN = 'veryHidden'; |
||
39 | |||
40 | /** |
||
41 | * Maximum 31 characters allowed for sheet title. |
||
42 | * |
||
43 | * @var int |
||
44 | */ |
||
45 | const SHEET_TITLE_MAXIMUM_LENGTH = 31; |
||
46 | |||
47 | /** |
||
48 | * Invalid characters in sheet title. |
||
49 | * |
||
50 | * @var array |
||
51 | */ |
||
52 | private static $invalidCharacters = ['*', ':', '/', '\\', '?', '[', ']']; |
||
53 | |||
54 | /** |
||
55 | * Parent spreadsheet. |
||
56 | * |
||
57 | * @var Spreadsheet |
||
58 | */ |
||
59 | private $parent; |
||
60 | |||
61 | /** |
||
62 | * Collection of cells. |
||
63 | * |
||
64 | * @var Cells |
||
65 | */ |
||
66 | private $cellCollection; |
||
67 | |||
68 | /** |
||
69 | * Collection of row dimensions. |
||
70 | * |
||
71 | * @var RowDimension[] |
||
72 | */ |
||
73 | private $rowDimensions = []; |
||
74 | |||
75 | /** |
||
76 | * Default row dimension. |
||
77 | * |
||
78 | * @var RowDimension |
||
79 | */ |
||
80 | private $defaultRowDimension; |
||
81 | |||
82 | /** |
||
83 | * Collection of column dimensions. |
||
84 | * |
||
85 | * @var ColumnDimension[] |
||
86 | */ |
||
87 | private $columnDimensions = []; |
||
88 | |||
89 | /** |
||
90 | * Default column dimension. |
||
91 | * |
||
92 | * @var ColumnDimension |
||
93 | */ |
||
94 | private $defaultColumnDimension; |
||
95 | |||
96 | /** |
||
97 | * Collection of drawings. |
||
98 | * |
||
99 | * @var BaseDrawing[] |
||
100 | */ |
||
101 | private $drawingCollection; |
||
102 | |||
103 | /** |
||
104 | * Collection of Chart objects. |
||
105 | * |
||
106 | * @var Chart[] |
||
107 | */ |
||
108 | private $chartCollection = []; |
||
109 | |||
110 | /** |
||
111 | * Worksheet title. |
||
112 | * |
||
113 | * @var string |
||
114 | */ |
||
115 | private $title; |
||
116 | |||
117 | /** |
||
118 | * Sheet state. |
||
119 | * |
||
120 | * @var string |
||
121 | */ |
||
122 | private $sheetState; |
||
123 | |||
124 | /** |
||
125 | * Page setup. |
||
126 | * |
||
127 | * @var PageSetup |
||
128 | */ |
||
129 | private $pageSetup; |
||
130 | |||
131 | /** |
||
132 | * Page margins. |
||
133 | * |
||
134 | * @var PageMargins |
||
135 | */ |
||
136 | private $pageMargins; |
||
137 | |||
138 | /** |
||
139 | * Page header/footer. |
||
140 | * |
||
141 | * @var HeaderFooter |
||
142 | */ |
||
143 | private $headerFooter; |
||
144 | |||
145 | /** |
||
146 | * Sheet view. |
||
147 | * |
||
148 | * @var SheetView |
||
149 | */ |
||
150 | private $sheetView; |
||
151 | |||
152 | /** |
||
153 | * Protection. |
||
154 | * |
||
155 | * @var Protection |
||
156 | */ |
||
157 | private $protection; |
||
158 | |||
159 | /** |
||
160 | * Collection of styles. |
||
161 | * |
||
162 | * @var Style[] |
||
163 | */ |
||
164 | private $styles = []; |
||
165 | |||
166 | /** |
||
167 | * Conditional styles. Indexed by cell coordinate, e.g. 'A1'. |
||
168 | * |
||
169 | * @var array |
||
170 | */ |
||
171 | private $conditionalStylesCollection = []; |
||
172 | |||
173 | /** |
||
174 | * Is the current cell collection sorted already? |
||
175 | * |
||
176 | * @var bool |
||
177 | */ |
||
178 | private $cellCollectionIsSorted = false; |
||
179 | |||
180 | /** |
||
181 | * Collection of breaks. |
||
182 | * |
||
183 | * @var array |
||
184 | */ |
||
185 | private $breaks = []; |
||
186 | |||
187 | /** |
||
188 | * Collection of merged cell ranges. |
||
189 | * |
||
190 | * @var array |
||
191 | */ |
||
192 | private $mergeCells = []; |
||
193 | |||
194 | /** |
||
195 | * Collection of protected cell ranges. |
||
196 | * |
||
197 | * @var array |
||
198 | */ |
||
199 | private $protectedCells = []; |
||
200 | |||
201 | /** |
||
202 | * Autofilter Range and selection. |
||
203 | * |
||
204 | * @var AutoFilter |
||
205 | */ |
||
206 | private $autoFilter; |
||
207 | |||
208 | /** |
||
209 | * Freeze pane. |
||
210 | * |
||
211 | * @var null|string |
||
212 | */ |
||
213 | private $freezePane; |
||
214 | |||
215 | /** |
||
216 | * Default position of the right bottom pane. |
||
217 | * |
||
218 | * @var null|string |
||
219 | */ |
||
220 | private $topLeftCell; |
||
221 | |||
222 | /** |
||
223 | * Show gridlines? |
||
224 | * |
||
225 | * @var bool |
||
226 | */ |
||
227 | private $showGridlines = true; |
||
228 | |||
229 | /** |
||
230 | * Print gridlines? |
||
231 | * |
||
232 | * @var bool |
||
233 | */ |
||
234 | private $printGridlines = false; |
||
235 | |||
236 | /** |
||
237 | * Show row and column headers? |
||
238 | * |
||
239 | * @var bool |
||
240 | */ |
||
241 | private $showRowColHeaders = true; |
||
242 | |||
243 | /** |
||
244 | * Show summary below? (Row/Column outline). |
||
245 | * |
||
246 | * @var bool |
||
247 | */ |
||
248 | private $showSummaryBelow = true; |
||
249 | |||
250 | /** |
||
251 | * Show summary right? (Row/Column outline). |
||
252 | * |
||
253 | * @var bool |
||
254 | */ |
||
255 | private $showSummaryRight = true; |
||
256 | |||
257 | /** |
||
258 | * Collection of comments. |
||
259 | * |
||
260 | * @var Comment[] |
||
261 | */ |
||
262 | private $comments = []; |
||
263 | |||
264 | /** |
||
265 | * Active cell. (Only one!). |
||
266 | * |
||
267 | * @var string |
||
268 | */ |
||
269 | private $activeCell = 'A1'; |
||
270 | |||
271 | /** |
||
272 | * Selected cells. |
||
273 | * |
||
274 | * @var string |
||
275 | */ |
||
276 | private $selectedCells = 'A1'; |
||
277 | |||
278 | /** |
||
279 | * Cached highest column. |
||
280 | * |
||
281 | * @var string |
||
282 | */ |
||
283 | private $cachedHighestColumn = 'A'; |
||
284 | |||
285 | /** |
||
286 | * Cached highest row. |
||
287 | * |
||
288 | * @var int |
||
289 | */ |
||
290 | private $cachedHighestRow = 1; |
||
291 | |||
292 | /** |
||
293 | * Right-to-left? |
||
294 | * |
||
295 | * @var bool |
||
296 | */ |
||
297 | private $rightToLeft = false; |
||
298 | |||
299 | /** |
||
300 | * Hyperlinks. Indexed by cell coordinate, e.g. 'A1'. |
||
301 | * |
||
302 | * @var array |
||
303 | */ |
||
304 | private $hyperlinkCollection = []; |
||
305 | |||
306 | /** |
||
307 | * Data validation objects. Indexed by cell coordinate, e.g. 'A1'. |
||
308 | * |
||
309 | * @var array |
||
310 | */ |
||
311 | private $dataValidationCollection = []; |
||
312 | |||
313 | /** |
||
314 | * Tab color. |
||
315 | * |
||
316 | * @var Color |
||
317 | */ |
||
318 | private $tabColor; |
||
319 | |||
320 | /** |
||
321 | * Dirty flag. |
||
322 | * |
||
323 | * @var bool |
||
324 | */ |
||
325 | private $dirty = true; |
||
326 | |||
327 | /** |
||
328 | * Hash. |
||
329 | * |
||
330 | * @var string |
||
331 | */ |
||
332 | private $hash; |
||
333 | |||
334 | /** |
||
335 | * CodeName. |
||
336 | * |
||
337 | * @var string |
||
338 | */ |
||
339 | private $codeName; |
||
340 | |||
341 | /** |
||
342 | * Create a new worksheet. |
||
343 | * |
||
344 | * @param Spreadsheet $parent |
||
345 | * @param string $pTitle |
||
346 | */ |
||
347 | 225 | public function __construct(Spreadsheet $parent = null, $pTitle = 'Worksheet') |
|
348 | { |
||
349 | // Set parent and title |
||
350 | 225 | $this->parent = $parent; |
|
351 | 225 | $this->setTitle($pTitle, false); |
|
352 | // setTitle can change $pTitle |
||
353 | 225 | $this->setCodeName($this->getTitle()); |
|
354 | 225 | $this->setSheetState(self::SHEETSTATE_VISIBLE); |
|
355 | |||
356 | 225 | $this->cellCollection = CellsFactory::getInstance($this); |
|
357 | // Set page setup |
||
358 | 225 | $this->pageSetup = new PageSetup(); |
|
359 | // Set page margins |
||
360 | 225 | $this->pageMargins = new PageMargins(); |
|
361 | // Set page header/footer |
||
362 | 225 | $this->headerFooter = new HeaderFooter(); |
|
363 | // Set sheet view |
||
364 | 225 | $this->sheetView = new SheetView(); |
|
365 | // Drawing collection |
||
366 | 225 | $this->drawingCollection = new \ArrayObject(); |
|
367 | // Chart collection |
||
368 | 225 | $this->chartCollection = new \ArrayObject(); |
|
369 | // Protection |
||
370 | 225 | $this->protection = new Protection(); |
|
371 | // Default row dimension |
||
372 | 225 | $this->defaultRowDimension = new RowDimension(null); |
|
373 | // Default column dimension |
||
374 | 225 | $this->defaultColumnDimension = new ColumnDimension(null); |
|
375 | 225 | $this->autoFilter = new AutoFilter(null, $this); |
|
376 | 225 | } |
|
377 | |||
378 | /** |
||
379 | * Disconnect all cells from this Worksheet object, |
||
380 | * typically so that the worksheet object can be unset. |
||
381 | */ |
||
382 | 26 | public function disconnectCells() |
|
383 | { |
||
384 | 26 | if ($this->cellCollection !== null) { |
|
385 | 3 | $this->cellCollection->unsetWorksheetCells(); |
|
386 | 3 | $this->cellCollection = null; |
|
387 | } |
||
388 | // detach ourself from the workbook, so that it can then delete this worksheet successfully |
||
389 | 26 | $this->parent = null; |
|
390 | 26 | } |
|
391 | |||
392 | /** |
||
393 | * Code to execute when this worksheet is unset(). |
||
394 | */ |
||
395 | 26 | public function __destruct() |
|
396 | { |
||
397 | 26 | Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title); |
|
398 | |||
399 | 26 | $this->disconnectCells(); |
|
400 | 26 | } |
|
401 | |||
402 | /** |
||
403 | * Return the cell collection. |
||
404 | * |
||
405 | * @return Cells |
||
406 | */ |
||
407 | 182 | public function getCellCollection() |
|
408 | { |
||
409 | 182 | return $this->cellCollection; |
|
410 | } |
||
411 | |||
412 | /** |
||
413 | * Get array of invalid characters for sheet title. |
||
414 | * |
||
415 | * @return array |
||
416 | */ |
||
417 | public static function getInvalidCharacters() |
||
418 | { |
||
419 | return self::$invalidCharacters; |
||
420 | } |
||
421 | |||
422 | /** |
||
423 | * Check sheet code name for valid Excel syntax. |
||
424 | * |
||
425 | * @param string $pValue The string to check |
||
426 | * |
||
427 | * @throws Exception |
||
428 | * |
||
429 | * @return string The valid string |
||
430 | */ |
||
431 | 225 | private static function checkSheetCodeName($pValue) |
|
432 | { |
||
433 | 225 | $CharCount = Shared\StringHelper::countCharacters($pValue); |
|
434 | 225 | if ($CharCount == 0) { |
|
435 | throw new Exception('Sheet code name cannot be empty.'); |
||
436 | } |
||
437 | // Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'" |
||
438 | 225 | if ((str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) || |
|
439 | 225 | (Shared\StringHelper::substring($pValue, -1, 1) == '\'') || |
|
440 | 225 | (Shared\StringHelper::substring($pValue, 0, 1) == '\'')) { |
|
441 | 1 | throw new Exception('Invalid character found in sheet code name'); |
|
442 | } |
||
443 | |||
444 | // Enforce maximum characters allowed for sheet title |
||
445 | 225 | if ($CharCount > self::SHEET_TITLE_MAXIMUM_LENGTH) { |
|
446 | 1 | throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet code name.'); |
|
447 | } |
||
448 | |||
449 | 225 | return $pValue; |
|
450 | } |
||
451 | |||
452 | /** |
||
453 | * Check sheet title for valid Excel syntax. |
||
454 | * |
||
455 | * @param string $pValue The string to check |
||
456 | * |
||
457 | * @throws Exception |
||
458 | * |
||
459 | * @return string The valid string |
||
460 | */ |
||
461 | 225 | private static function checkSheetTitle($pValue) |
|
462 | { |
||
463 | // Some of the printable ASCII characters are invalid: * : / \ ? [ ] |
||
464 | 225 | if (str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) { |
|
465 | 1 | throw new Exception('Invalid character found in sheet title'); |
|
466 | } |
||
467 | |||
468 | // Enforce maximum characters allowed for sheet title |
||
469 | 225 | if (Shared\StringHelper::countCharacters($pValue) > self::SHEET_TITLE_MAXIMUM_LENGTH) { |
|
470 | 1 | throw new Exception('Maximum ' . self::SHEET_TITLE_MAXIMUM_LENGTH . ' characters allowed in sheet title.'); |
|
471 | } |
||
472 | |||
473 | 225 | return $pValue; |
|
474 | } |
||
475 | |||
476 | /** |
||
477 | * Get a sorted list of all cell coordinates currently held in the collection by row and column. |
||
478 | * |
||
479 | * @param bool $sorted Also sort the cell collection? |
||
480 | * |
||
481 | * @return string[] |
||
482 | */ |
||
483 | 112 | public function getCoordinates($sorted = true) |
|
484 | { |
||
485 | 112 | if ($this->cellCollection == null) { |
|
486 | return []; |
||
487 | } |
||
488 | |||
489 | 112 | if ($sorted) { |
|
490 | 93 | return $this->cellCollection->getSortedCoordinates(); |
|
491 | } |
||
492 | |||
493 | 111 | return $this->cellCollection->getCoordinates(); |
|
494 | } |
||
495 | |||
496 | /** |
||
497 | * Get collection of row dimensions. |
||
498 | * |
||
499 | * @return RowDimension[] |
||
500 | */ |
||
501 | 105 | public function getRowDimensions() |
|
502 | { |
||
503 | 105 | return $this->rowDimensions; |
|
504 | } |
||
505 | |||
506 | /** |
||
507 | * Get default row dimension. |
||
508 | * |
||
509 | * @return RowDimension |
||
510 | */ |
||
511 | 116 | public function getDefaultRowDimension() |
|
512 | { |
||
513 | 116 | return $this->defaultRowDimension; |
|
514 | } |
||
515 | |||
516 | /** |
||
517 | * Get collection of column dimensions. |
||
518 | * |
||
519 | * @return ColumnDimension[] |
||
520 | */ |
||
521 | 105 | public function getColumnDimensions() |
|
522 | { |
||
523 | 105 | return $this->columnDimensions; |
|
524 | } |
||
525 | |||
526 | /** |
||
527 | * Get default column dimension. |
||
528 | * |
||
529 | * @return ColumnDimension |
||
530 | */ |
||
531 | 91 | public function getDefaultColumnDimension() |
|
532 | { |
||
533 | 91 | return $this->defaultColumnDimension; |
|
534 | } |
||
535 | |||
536 | /** |
||
537 | * Get collection of drawings. |
||
538 | * |
||
539 | * @return BaseDrawing[] |
||
540 | */ |
||
541 | 103 | public function getDrawingCollection() |
|
542 | { |
||
543 | 103 | return $this->drawingCollection; |
|
544 | } |
||
545 | |||
546 | /** |
||
547 | * Get collection of charts. |
||
548 | * |
||
549 | * @return Chart[] |
||
550 | */ |
||
551 | 15 | public function getChartCollection() |
|
552 | { |
||
553 | 15 | return $this->chartCollection; |
|
554 | } |
||
555 | |||
556 | /** |
||
557 | * Add chart. |
||
558 | * |
||
559 | * @param Chart $pChart |
||
560 | * @param null|int $iChartIndex Index where chart should go (0,1,..., or null for last) |
||
561 | * |
||
562 | * @return Chart |
||
563 | */ |
||
564 | 16 | public function addChart(Chart $pChart, $iChartIndex = null) |
|
565 | { |
||
566 | 16 | $pChart->setWorksheet($this); |
|
567 | 16 | if ($iChartIndex === null) { |
|
568 | 16 | $this->chartCollection[] = $pChart; |
|
569 | } else { |
||
570 | // Insert the chart at the requested index |
||
571 | array_splice($this->chartCollection, $iChartIndex, 0, [$pChart]); |
||
572 | } |
||
573 | |||
574 | 16 | return $pChart; |
|
575 | } |
||
576 | |||
577 | /** |
||
578 | * Return the count of charts on this worksheet. |
||
579 | * |
||
580 | * @return int The number of charts |
||
581 | */ |
||
582 | 15 | public function getChartCount() |
|
583 | { |
||
584 | 15 | return count($this->chartCollection); |
|
585 | } |
||
586 | |||
587 | /** |
||
588 | * Get a chart by its index position. |
||
589 | * |
||
590 | * @param string $index Chart index position |
||
591 | * |
||
592 | * @return Chart|false |
||
593 | */ |
||
594 | 14 | public function getChartByIndex($index) |
|
595 | { |
||
596 | 14 | $chartCount = count($this->chartCollection); |
|
597 | 14 | if ($chartCount == 0) { |
|
598 | return false; |
||
599 | } |
||
600 | 14 | if ($index === null) { |
|
601 | $index = --$chartCount; |
||
602 | } |
||
603 | 14 | if (!isset($this->chartCollection[$index])) { |
|
604 | return false; |
||
605 | } |
||
606 | |||
607 | 14 | return $this->chartCollection[$index]; |
|
608 | } |
||
609 | |||
610 | /** |
||
611 | * Return an array of the names of charts on this worksheet. |
||
612 | * |
||
613 | * @return string[] The names of charts |
||
614 | */ |
||
615 | 2 | public function getChartNames() |
|
616 | { |
||
617 | 2 | $chartNames = []; |
|
618 | 2 | foreach ($this->chartCollection as $chart) { |
|
619 | 2 | $chartNames[] = $chart->getName(); |
|
620 | } |
||
621 | |||
622 | 2 | return $chartNames; |
|
623 | } |
||
624 | |||
625 | /** |
||
626 | * Get a chart by name. |
||
627 | * |
||
628 | * @param string $chartName Chart name |
||
629 | * |
||
630 | * @return Chart|false |
||
631 | */ |
||
632 | 2 | public function getChartByName($chartName) |
|
633 | { |
||
634 | 2 | $chartCount = count($this->chartCollection); |
|
635 | 2 | if ($chartCount == 0) { |
|
636 | return false; |
||
637 | } |
||
638 | 2 | foreach ($this->chartCollection as $index => $chart) { |
|
639 | 2 | if ($chart->getName() == $chartName) { |
|
640 | 2 | return $this->chartCollection[$index]; |
|
641 | } |
||
642 | } |
||
643 | |||
644 | return false; |
||
645 | } |
||
646 | |||
647 | /** |
||
648 | * Refresh column dimensions. |
||
649 | * |
||
650 | * @return Worksheet |
||
651 | */ |
||
652 | 14 | public function refreshColumnDimensions() |
|
653 | { |
||
654 | 14 | $currentColumnDimensions = $this->getColumnDimensions(); |
|
655 | 14 | $newColumnDimensions = []; |
|
656 | |||
657 | 14 | foreach ($currentColumnDimensions as $objColumnDimension) { |
|
658 | 14 | $newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension; |
|
659 | } |
||
660 | |||
661 | 14 | $this->columnDimensions = $newColumnDimensions; |
|
662 | |||
663 | 14 | return $this; |
|
664 | } |
||
665 | |||
666 | /** |
||
667 | * Refresh row dimensions. |
||
668 | * |
||
669 | * @return Worksheet |
||
670 | */ |
||
671 | 2 | public function refreshRowDimensions() |
|
672 | { |
||
673 | 2 | $currentRowDimensions = $this->getRowDimensions(); |
|
674 | 2 | $newRowDimensions = []; |
|
675 | |||
676 | 2 | foreach ($currentRowDimensions as $objRowDimension) { |
|
677 | 2 | $newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension; |
|
678 | } |
||
679 | |||
680 | 2 | $this->rowDimensions = $newRowDimensions; |
|
681 | |||
682 | 2 | return $this; |
|
683 | } |
||
684 | |||
685 | /** |
||
686 | * Calculate worksheet dimension. |
||
687 | * |
||
688 | * @return string String containing the dimension of this worksheet |
||
689 | */ |
||
690 | 95 | public function calculateWorksheetDimension() |
|
691 | { |
||
692 | // Return |
||
693 | 95 | return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow(); |
|
694 | } |
||
695 | |||
696 | /** |
||
697 | * Calculate worksheet data dimension. |
||
698 | * |
||
699 | * @return string String containing the dimension of this worksheet that actually contain data |
||
700 | */ |
||
701 | public function calculateWorksheetDataDimension() |
||
702 | { |
||
703 | // Return |
||
704 | return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow(); |
||
705 | } |
||
706 | |||
707 | /** |
||
708 | * Calculate widths for auto-size columns. |
||
709 | * |
||
710 | * @return Worksheet; |
||
711 | */ |
||
712 | 60 | public function calculateColumnWidths() |
|
713 | { |
||
714 | // initialize $autoSizes array |
||
715 | 60 | $autoSizes = []; |
|
716 | 60 | foreach ($this->getColumnDimensions() as $colDimension) { |
|
717 | 27 | if ($colDimension->getAutoSize()) { |
|
718 | 13 | $autoSizes[$colDimension->getColumnIndex()] = -1; |
|
719 | } |
||
720 | } |
||
721 | |||
722 | // There is only something to do if there are some auto-size columns |
||
723 | 60 | if (!empty($autoSizes)) { |
|
724 | // build list of cells references that participate in a merge |
||
725 | 13 | $isMergeCell = []; |
|
726 | 13 | foreach ($this->getMergeCells() as $cells) { |
|
727 | 10 | foreach (Coordinate::extractAllCellReferencesInRange($cells) as $cellReference) { |
|
728 | 10 | $isMergeCell[$cellReference] = true; |
|
729 | } |
||
730 | } |
||
731 | |||
732 | // loop through all cells in the worksheet |
||
733 | 13 | foreach ($this->getCoordinates(false) as $coordinate) { |
|
734 | 13 | $cell = $this->getCell($coordinate, false); |
|
735 | 13 | if ($cell !== null && isset($autoSizes[$this->cellCollection->getCurrentColumn()])) { |
|
736 | //Determine if cell is in merge range |
||
737 | 13 | $isMerged = isset($isMergeCell[$this->cellCollection->getCurrentCoordinate()]); |
|
738 | |||
739 | //By default merged cells should be ignored |
||
740 | 13 | $isMergedButProceed = false; |
|
741 | |||
742 | //The only exception is if it's a merge range value cell of a 'vertical' randge (1 column wide) |
||
743 | 13 | if ($isMerged && $cell->isMergeRangeValueCell()) { |
|
744 | $range = $cell->getMergeRange(); |
||
745 | $rangeBoundaries = Coordinate::rangeDimension($range); |
||
746 | if ($rangeBoundaries[0] == 1) { |
||
747 | $isMergedButProceed = true; |
||
748 | } |
||
749 | } |
||
750 | |||
751 | // Determine width if cell does not participate in a merge or does and is a value cell of 1-column wide range |
||
752 | 13 | if (!$isMerged || $isMergedButProceed) { |
|
753 | // Calculated value |
||
754 | // To formatted string |
||
755 | 13 | $cellValue = NumberFormat::toFormattedString( |
|
756 | 13 | $cell->getCalculatedValue(), |
|
757 | 13 | $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode() |
|
758 | ); |
||
759 | |||
760 | 13 | $autoSizes[$this->cellCollection->getCurrentColumn()] = max( |
|
761 | 13 | (float) $autoSizes[$this->cellCollection->getCurrentColumn()], |
|
762 | 13 | (float) Shared\Font::calculateColumnWidth( |
|
763 | 13 | $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(), |
|
764 | 13 | $cellValue, |
|
765 | 13 | $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(), |
|
766 | 13 | $this->getParent()->getDefaultStyle()->getFont() |
|
767 | ) |
||
768 | ); |
||
769 | } |
||
770 | } |
||
771 | } |
||
772 | |||
773 | // adjust column widths |
||
774 | 13 | foreach ($autoSizes as $columnIndex => $width) { |
|
775 | 13 | if ($width == -1) { |
|
776 | $width = $this->getDefaultColumnDimension()->getWidth(); |
||
777 | } |
||
778 | 13 | $this->getColumnDimension($columnIndex)->setWidth($width); |
|
779 | } |
||
780 | } |
||
781 | |||
782 | 60 | return $this; |
|
783 | } |
||
784 | |||
785 | /** |
||
786 | * Get parent. |
||
787 | * |
||
788 | * @return Spreadsheet |
||
789 | */ |
||
790 | 225 | public function getParent() |
|
791 | { |
||
792 | 225 | return $this->parent; |
|
793 | } |
||
794 | |||
795 | /** |
||
796 | * Re-bind parent. |
||
797 | * |
||
798 | * @param Spreadsheet $parent |
||
799 | * |
||
800 | * @return Worksheet |
||
801 | */ |
||
802 | 1 | public function rebindParent(Spreadsheet $parent) |
|
803 | { |
||
804 | 1 | if ($this->parent !== null) { |
|
805 | 1 | $namedRanges = $this->parent->getNamedRanges(); |
|
806 | 1 | foreach ($namedRanges as $namedRange) { |
|
807 | $parent->addNamedRange($namedRange); |
||
808 | } |
||
809 | |||
810 | 1 | $this->parent->removeSheetByIndex( |
|
811 | 1 | $this->parent->getIndex($this) |
|
812 | ); |
||
813 | } |
||
814 | 1 | $this->parent = $parent; |
|
815 | |||
816 | 1 | return $this; |
|
817 | } |
||
818 | |||
819 | /** |
||
820 | * Get title. |
||
821 | * |
||
822 | * @return string |
||
823 | */ |
||
824 | 225 | public function getTitle() |
|
825 | { |
||
826 | 225 | return $this->title; |
|
827 | } |
||
828 | |||
829 | /** |
||
830 | * Set title. |
||
831 | * |
||
832 | * @param string $pValue String containing the dimension of this worksheet |
||
833 | * @param bool $updateFormulaCellReferences Flag indicating whether cell references in formulae should |
||
834 | * be updated to reflect the new sheet name. |
||
835 | * This should be left as the default true, unless you are |
||
836 | * certain that no formula cells on any worksheet contain |
||
837 | * references to this worksheet |
||
838 | * @param bool $validate False to skip validation of new title. WARNING: This should only be set |
||
839 | * at parse time (by Readers), where titles can be assumed to be valid. |
||
840 | * |
||
841 | * @return Worksheet |
||
842 | */ |
||
843 | 225 | public function setTitle($pValue, $updateFormulaCellReferences = true, $validate = true) |
|
844 | { |
||
845 | // Is this a 'rename' or not? |
||
846 | 225 | if ($this->getTitle() == $pValue) { |
|
847 | 38 | return $this; |
|
848 | } |
||
849 | |||
850 | // Old title |
||
851 | 225 | $oldTitle = $this->getTitle(); |
|
852 | |||
853 | 225 | if ($validate) { |
|
854 | // Syntax check |
||
855 | 225 | self::checkSheetTitle($pValue); |
|
856 | |||
857 | 225 | if ($this->parent) { |
|
858 | // Is there already such sheet name? |
||
859 | 208 | if ($this->parent->sheetNameExists($pValue)) { |
|
860 | // Use name, but append with lowest possible integer |
||
861 | |||
862 | 6 | if (Shared\StringHelper::countCharacters($pValue) > 29) { |
|
863 | $pValue = Shared\StringHelper::substring($pValue, 0, 29); |
||
864 | } |
||
865 | 6 | $i = 1; |
|
866 | 6 | while ($this->parent->sheetNameExists($pValue . ' ' . $i)) { |
|
867 | 3 | ++$i; |
|
868 | 3 | if ($i == 10) { |
|
869 | if (Shared\StringHelper::countCharacters($pValue) > 28) { |
||
870 | $pValue = Shared\StringHelper::substring($pValue, 0, 28); |
||
871 | } |
||
872 | 3 | } elseif ($i == 100) { |
|
873 | if (Shared\StringHelper::countCharacters($pValue) > 27) { |
||
874 | $pValue = Shared\StringHelper::substring($pValue, 0, 27); |
||
875 | } |
||
876 | } |
||
877 | } |
||
878 | |||
879 | 6 | $pValue .= " $i"; |
|
880 | } |
||
881 | } |
||
882 | } |
||
883 | |||
884 | // Set title |
||
885 | 225 | $this->title = $pValue; |
|
886 | 225 | $this->dirty = true; |
|
887 | |||
888 | 225 | if ($this->parent && $this->parent->getCalculationEngine()) { |
|
889 | // New title |
||
890 | 208 | $newTitle = $this->getTitle(); |
|
891 | 208 | $this->parent->getCalculationEngine() |
|
892 | 208 | ->renameCalculationCacheForWorksheet($oldTitle, $newTitle); |
|
893 | 208 | if ($updateFormulaCellReferences) { |
|
894 | 42 | ReferenceHelper::getInstance()->updateNamedFormulas($this->parent, $oldTitle, $newTitle); |
|
895 | } |
||
896 | } |
||
897 | |||
898 | 225 | return $this; |
|
899 | } |
||
900 | |||
901 | /** |
||
902 | * Get sheet state. |
||
903 | * |
||
904 | * @return string Sheet state (visible, hidden, veryHidden) |
||
905 | */ |
||
906 | 89 | public function getSheetState() |
|
907 | { |
||
908 | 89 | return $this->sheetState; |
|
909 | } |
||
910 | |||
911 | /** |
||
912 | * Set sheet state. |
||
913 | * |
||
914 | * @param string $value Sheet state (visible, hidden, veryHidden) |
||
915 | * |
||
916 | * @return Worksheet |
||
917 | */ |
||
918 | 225 | public function setSheetState($value) |
|
919 | { |
||
920 | 225 | $this->sheetState = $value; |
|
921 | |||
922 | 225 | return $this; |
|
923 | } |
||
924 | |||
925 | /** |
||
926 | * Get page setup. |
||
927 | * |
||
928 | * @return PageSetup |
||
929 | */ |
||
930 | 116 | public function getPageSetup() |
|
931 | { |
||
932 | 116 | return $this->pageSetup; |
|
933 | } |
||
934 | |||
935 | /** |
||
936 | * Set page setup. |
||
937 | * |
||
938 | * @param PageSetup $pValue |
||
939 | * |
||
940 | * @return Worksheet |
||
941 | */ |
||
942 | public function setPageSetup(PageSetup $pValue) |
||
943 | { |
||
944 | $this->pageSetup = $pValue; |
||
945 | |||
946 | return $this; |
||
947 | } |
||
948 | |||
949 | /** |
||
950 | * Get page margins. |
||
951 | * |
||
952 | * @return PageMargins |
||
953 | */ |
||
954 | 116 | public function getPageMargins() |
|
955 | { |
||
956 | 116 | return $this->pageMargins; |
|
957 | } |
||
958 | |||
959 | /** |
||
960 | * Set page margins. |
||
961 | * |
||
962 | * @param PageMargins $pValue |
||
963 | * |
||
964 | * @return Worksheet |
||
965 | */ |
||
966 | public function setPageMargins(PageMargins $pValue) |
||
967 | { |
||
968 | $this->pageMargins = $pValue; |
||
969 | |||
970 | return $this; |
||
971 | } |
||
972 | |||
973 | /** |
||
974 | * Get page header/footer. |
||
975 | * |
||
976 | * @return HeaderFooter |
||
977 | */ |
||
978 | 94 | public function getHeaderFooter() |
|
979 | { |
||
980 | 94 | return $this->headerFooter; |
|
981 | } |
||
982 | |||
983 | /** |
||
984 | * Set page header/footer. |
||
985 | * |
||
986 | * @param HeaderFooter $pValue |
||
987 | * |
||
988 | * @return Worksheet |
||
989 | */ |
||
990 | public function setHeaderFooter(HeaderFooter $pValue) |
||
991 | { |
||
992 | $this->headerFooter = $pValue; |
||
993 | |||
994 | return $this; |
||
995 | } |
||
996 | |||
997 | /** |
||
998 | * Get sheet view. |
||
999 | * |
||
1000 | * @return SheetView |
||
1001 | */ |
||
1002 | 104 | public function getSheetView() |
|
1003 | { |
||
1004 | 104 | return $this->sheetView; |
|
1005 | } |
||
1006 | |||
1007 | /** |
||
1008 | * Set sheet view. |
||
1009 | * |
||
1010 | * @param SheetView $pValue |
||
1011 | * |
||
1012 | * @return Worksheet |
||
1013 | */ |
||
1014 | public function setSheetView(SheetView $pValue) |
||
1015 | { |
||
1016 | $this->sheetView = $pValue; |
||
1017 | |||
1018 | return $this; |
||
1019 | } |
||
1020 | |||
1021 | /** |
||
1022 | * Get Protection. |
||
1023 | * |
||
1024 | * @return Protection |
||
1025 | */ |
||
1026 | 95 | public function getProtection() |
|
1027 | { |
||
1028 | 95 | return $this->protection; |
|
1029 | } |
||
1030 | |||
1031 | /** |
||
1032 | * Set Protection. |
||
1033 | * |
||
1034 | * @param Protection $pValue |
||
1035 | * |
||
1036 | * @return Worksheet |
||
1037 | */ |
||
1038 | public function setProtection(Protection $pValue) |
||
1039 | { |
||
1040 | $this->protection = $pValue; |
||
1041 | $this->dirty = true; |
||
1042 | |||
1043 | return $this; |
||
1044 | } |
||
1045 | |||
1046 | /** |
||
1047 | * Get highest worksheet column. |
||
1048 | * |
||
1049 | * @param string $row Return the data highest column for the specified row, |
||
1050 | * or the highest column of any row if no row number is passed |
||
1051 | * |
||
1052 | * @return string Highest column name |
||
1053 | */ |
||
1054 | 124 | public function getHighestColumn($row = null) |
|
1055 | { |
||
1056 | 124 | if ($row == null) { |
|
1057 | 124 | return $this->cachedHighestColumn; |
|
1058 | } |
||
1059 | |||
1060 | return $this->getHighestDataColumn($row); |
||
1061 | } |
||
1062 | |||
1063 | /** |
||
1064 | * Get highest worksheet column that contains data. |
||
1065 | * |
||
1066 | * @param string $row Return the highest data column for the specified row, |
||
1067 | * or the highest data column of any row if no row number is passed |
||
1068 | * |
||
1069 | * @return string Highest column name that contains data |
||
1070 | */ |
||
1071 | 13 | public function getHighestDataColumn($row = null) |
|
1072 | { |
||
1073 | 13 | return $this->cellCollection->getHighestColumn($row); |
|
1074 | } |
||
1075 | |||
1076 | /** |
||
1077 | * Get highest worksheet row. |
||
1078 | * |
||
1079 | * @param string $column Return the highest data row for the specified column, |
||
1080 | * or the highest row of any column if no column letter is passed |
||
1081 | * |
||
1082 | * @return int Highest row number |
||
1083 | */ |
||
1084 | 124 | public function getHighestRow($column = null) |
|
1085 | { |
||
1086 | 124 | if ($column == null) { |
|
1087 | 124 | return $this->cachedHighestRow; |
|
1088 | } |
||
1089 | |||
1090 | return $this->getHighestDataRow($column); |
||
1091 | } |
||
1092 | |||
1093 | /** |
||
1094 | * Get highest worksheet row that contains data. |
||
1095 | * |
||
1096 | * @param string $column Return the highest data row for the specified column, |
||
1097 | * or the highest data row of any column if no column letter is passed |
||
1098 | * |
||
1099 | * @return string Highest row number that contains data |
||
1100 | */ |
||
1101 | 15 | public function getHighestDataRow($column = null) |
|
1102 | { |
||
1103 | 15 | return $this->cellCollection->getHighestRow($column); |
|
1104 | } |
||
1105 | |||
1106 | /** |
||
1107 | * Get highest worksheet column and highest row that have cell records. |
||
1108 | * |
||
1109 | * @return array Highest column name and highest row number |
||
1110 | */ |
||
1111 | public function getHighestRowAndColumn() |
||
1112 | { |
||
1113 | return $this->cellCollection->getHighestRowAndColumn(); |
||
1114 | } |
||
1115 | |||
1116 | /** |
||
1117 | * Set a cell value. |
||
1118 | * |
||
1119 | * @param string $pCoordinate Coordinate of the cell, eg: 'A1' |
||
1120 | * @param mixed $pValue Value of the cell |
||
1121 | * |
||
1122 | * @return Worksheet |
||
1123 | */ |
||
1124 | 75 | public function setCellValue($pCoordinate, $pValue) |
|
1125 | { |
||
1126 | 75 | $this->getCell($pCoordinate)->setValue($pValue); |
|
1127 | |||
1128 | 75 | return $this; |
|
1129 | } |
||
1130 | |||
1131 | /** |
||
1132 | * Set a cell value by using numeric cell coordinates. |
||
1133 | * |
||
1134 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1135 | * @param int $row Numeric row coordinate of the cell |
||
1136 | * @param mixed $value Value of the cell |
||
1137 | * |
||
1138 | * @return Worksheet |
||
1139 | */ |
||
1140 | public function setCellValueByColumnAndRow($columnIndex, $row, $value) |
||
1141 | { |
||
1142 | $this->getCellByColumnAndRow($columnIndex, $row)->setValue($value); |
||
1143 | |||
1144 | return $this; |
||
1145 | } |
||
1146 | |||
1147 | /** |
||
1148 | * Set a cell value. |
||
1149 | * |
||
1150 | * @param string $pCoordinate Coordinate of the cell, eg: 'A1' |
||
1151 | * @param mixed $pValue Value of the cell |
||
1152 | * @param string $pDataType Explicit data type, see DataType::TYPE_* |
||
1153 | * |
||
1154 | * @return Worksheet |
||
1155 | */ |
||
1156 | 1 | public function setCellValueExplicit($pCoordinate, $pValue, $pDataType) |
|
1157 | { |
||
1158 | // Set value |
||
1159 | 1 | $this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType); |
|
1160 | |||
1161 | 1 | return $this; |
|
1162 | } |
||
1163 | |||
1164 | /** |
||
1165 | * Set a cell value by using numeric cell coordinates. |
||
1166 | * |
||
1167 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1168 | * @param int $row Numeric row coordinate of the cell |
||
1169 | * @param mixed $value Value of the cell |
||
1170 | * @param string $dataType Explicit data type, see DataType::TYPE_* |
||
1171 | * |
||
1172 | * @return Worksheet |
||
1173 | */ |
||
1174 | public function setCellValueExplicitByColumnAndRow($columnIndex, $row, $value, $dataType) |
||
1175 | { |
||
1176 | $this->getCellByColumnAndRow($columnIndex, $row)->setValueExplicit($value, $dataType); |
||
1177 | |||
1178 | return $this; |
||
1179 | } |
||
1180 | |||
1181 | /** |
||
1182 | * Get cell at a specific coordinate. |
||
1183 | * |
||
1184 | * @param string $pCoordinate Coordinate of the cell, eg: 'A1' |
||
1185 | * @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't |
||
1186 | * already exist, or a null should be returned instead |
||
1187 | * |
||
1188 | * @throws Exception |
||
1189 | * |
||
1190 | * @return null|Cell Cell that was found/created or null |
||
1191 | */ |
||
1192 | 180 | public function getCell($pCoordinate, $createIfNotExists = true) |
|
1193 | { |
||
1194 | // Uppercase coordinate |
||
1195 | 180 | $pCoordinateUpper = strtoupper($pCoordinate); |
|
1196 | |||
1197 | // Check cell collection |
||
1198 | 180 | if ($this->cellCollection->has($pCoordinateUpper)) { |
|
1199 | 162 | return $this->cellCollection->get($pCoordinateUpper); |
|
1200 | } |
||
1201 | |||
1202 | // Worksheet reference? |
||
1203 | 174 | if (strpos($pCoordinate, '!') !== false) { |
|
1204 | 1 | $worksheetReference = self::extractSheetTitle($pCoordinate, true); |
|
1205 | |||
1206 | 1 | return $this->parent->getSheetByName($worksheetReference[0])->getCell(strtoupper($worksheetReference[1]), $createIfNotExists); |
|
1207 | } |
||
1208 | |||
1209 | // Named range? |
||
1210 | 174 | if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) && |
|
1211 | 174 | (preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) { |
|
1212 | $namedRange = NamedRange::resolveRange($pCoordinate, $this); |
||
1213 | if ($namedRange !== null) { |
||
1214 | $pCoordinate = $namedRange->getRange(); |
||
1215 | |||
1216 | return $namedRange->getWorksheet()->getCell($pCoordinate, $createIfNotExists); |
||
1217 | } |
||
1218 | } |
||
1219 | |||
1220 | 174 | if (Coordinate::coordinateIsRange($pCoordinate)) { |
|
1221 | throw new Exception('Cell coordinate can not be a range of cells.'); |
||
1222 | 174 | } elseif (strpos($pCoordinate, '$') !== false) { |
|
1223 | throw new Exception('Cell coordinate must not be absolute.'); |
||
1224 | } |
||
1225 | |||
1226 | // Create new cell object, if required |
||
1227 | 174 | return $createIfNotExists ? $this->createNewCell($pCoordinateUpper) : null; |
|
1228 | } |
||
1229 | |||
1230 | /** |
||
1231 | * Get cell at a specific coordinate by using numeric cell coordinates. |
||
1232 | * |
||
1233 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1234 | * @param int $row Numeric row coordinate of the cell |
||
1235 | * @param bool $createIfNotExists Flag indicating whether a new cell should be created if it doesn't |
||
1236 | * already exist, or a null should be returned instead |
||
1237 | * |
||
1238 | * @return null|Cell Cell that was found/created or null |
||
1239 | */ |
||
1240 | 59 | public function getCellByColumnAndRow($columnIndex, $row, $createIfNotExists = true) |
|
1241 | { |
||
1242 | 59 | $columnLetter = Coordinate::stringFromColumnIndex($columnIndex); |
|
1243 | 59 | $coordinate = $columnLetter . $row; |
|
1244 | |||
1245 | 59 | if ($this->cellCollection->has($coordinate)) { |
|
1246 | 58 | return $this->cellCollection->get($coordinate); |
|
1247 | } |
||
1248 | |||
1249 | // Create new cell object, if required |
||
1250 | 32 | return $createIfNotExists ? $this->createNewCell($coordinate) : null; |
|
1251 | } |
||
1252 | |||
1253 | /** |
||
1254 | * Create a new cell at the specified coordinate. |
||
1255 | * |
||
1256 | * @param string $pCoordinate Coordinate of the cell |
||
1257 | * |
||
1258 | * @return Cell Cell that was created |
||
1259 | */ |
||
1260 | 182 | private function createNewCell($pCoordinate) |
|
1261 | { |
||
1262 | 182 | $cell = new Cell(null, DataType::TYPE_NULL, $this); |
|
1263 | 182 | $this->cellCollection->add($pCoordinate, $cell); |
|
1264 | 182 | $this->cellCollectionIsSorted = false; |
|
1265 | |||
1266 | // Coordinates |
||
1267 | 182 | $aCoordinates = Coordinate::coordinateFromString($pCoordinate); |
|
1268 | 182 | if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($aCoordinates[0])) { |
|
1269 | 128 | $this->cachedHighestColumn = $aCoordinates[0]; |
|
1270 | } |
||
1271 | 182 | if ($aCoordinates[1] > $this->cachedHighestRow) { |
|
1272 | 116 | $this->cachedHighestRow = $aCoordinates[1]; |
|
1273 | } |
||
1274 | |||
1275 | // Cell needs appropriate xfIndex from dimensions records |
||
1276 | // but don't create dimension records if they don't already exist |
||
1277 | 182 | $rowDimension = $this->getRowDimension($aCoordinates[1], false); |
|
1278 | 182 | $columnDimension = $this->getColumnDimension($aCoordinates[0], false); |
|
1279 | |||
1280 | 182 | if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) { |
|
1281 | // then there is a row dimension with explicit style, assign it to the cell |
||
1282 | 2 | $cell->setXfIndex($rowDimension->getXfIndex()); |
|
1283 | 182 | } elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) { |
|
1284 | // then there is a column dimension, assign it to the cell |
||
1285 | $cell->setXfIndex($columnDimension->getXfIndex()); |
||
1286 | } |
||
1287 | |||
1288 | 182 | return $cell; |
|
1289 | } |
||
1290 | |||
1291 | /** |
||
1292 | * Does the cell at a specific coordinate exist? |
||
1293 | * |
||
1294 | * @param string $pCoordinate Coordinate of the cell eg: 'A1' |
||
1295 | * |
||
1296 | * @throws Exception |
||
1297 | * |
||
1298 | * @return bool |
||
1299 | */ |
||
1300 | 99 | public function cellExists($pCoordinate) |
|
1301 | { |
||
1302 | // Worksheet reference? |
||
1303 | 99 | if (strpos($pCoordinate, '!') !== false) { |
|
1304 | $worksheetReference = self::extractSheetTitle($pCoordinate, true); |
||
1305 | |||
1306 | return $this->parent->getSheetByName($worksheetReference[0])->cellExists(strtoupper($worksheetReference[1])); |
||
1307 | } |
||
1308 | |||
1309 | // Named range? |
||
1310 | 99 | if ((!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches)) && |
|
1311 | 99 | (preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches))) { |
|
1312 | $namedRange = NamedRange::resolveRange($pCoordinate, $this); |
||
1313 | if ($namedRange !== null) { |
||
1314 | $pCoordinate = $namedRange->getRange(); |
||
1315 | if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) { |
||
1316 | if (!$namedRange->getLocalOnly()) { |
||
1317 | return $namedRange->getWorksheet()->cellExists($pCoordinate); |
||
1318 | } |
||
1319 | |||
1320 | throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle()); |
||
1321 | } |
||
1322 | } else { |
||
1323 | return false; |
||
1324 | } |
||
1325 | } |
||
1326 | |||
1327 | // Uppercase coordinate |
||
1328 | 99 | $pCoordinate = strtoupper($pCoordinate); |
|
1329 | |||
1330 | 99 | if (Coordinate::coordinateIsRange($pCoordinate)) { |
|
1331 | throw new Exception('Cell coordinate can not be a range of cells.'); |
||
1332 | 99 | } elseif (strpos($pCoordinate, '$') !== false) { |
|
1333 | throw new Exception('Cell coordinate must not be absolute.'); |
||
1334 | } |
||
1335 | |||
1336 | // Cell exists? |
||
1337 | 99 | return $this->cellCollection->has($pCoordinate); |
|
1338 | } |
||
1339 | |||
1340 | /** |
||
1341 | * Cell at a specific coordinate by using numeric cell coordinates exists? |
||
1342 | * |
||
1343 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1344 | * @param int $row Numeric row coordinate of the cell |
||
1345 | * |
||
1346 | * @return bool |
||
1347 | */ |
||
1348 | 13 | public function cellExistsByColumnAndRow($columnIndex, $row) |
|
1349 | { |
||
1350 | 13 | return $this->cellExists(Coordinate::stringFromColumnIndex($columnIndex) . $row); |
|
1351 | } |
||
1352 | |||
1353 | /** |
||
1354 | * Get row dimension at a specific row. |
||
1355 | * |
||
1356 | * @param int $pRow Numeric index of the row |
||
1357 | * @param bool $create |
||
1358 | * |
||
1359 | * @return RowDimension |
||
1360 | */ |
||
1361 | 185 | public function getRowDimension($pRow, $create = true) |
|
1362 | { |
||
1363 | // Found |
||
1364 | 185 | $found = null; |
|
0 ignored issues
–
show
Unused Code
introduced
by
Loading history...
|
|||
1365 | |||
1366 | // Get row dimension |
||
1367 | 185 | if (!isset($this->rowDimensions[$pRow])) { |
|
1368 | 185 | if (!$create) { |
|
1369 | 167 | return null; |
|
1370 | } |
||
1371 | 104 | $this->rowDimensions[$pRow] = new RowDimension($pRow); |
|
1372 | |||
1373 | 104 | $this->cachedHighestRow = max($this->cachedHighestRow, $pRow); |
|
1374 | } |
||
1375 | |||
1376 | 104 | return $this->rowDimensions[$pRow]; |
|
1377 | } |
||
1378 | |||
1379 | /** |
||
1380 | * Get column dimension at a specific column. |
||
1381 | * |
||
1382 | * @param string $pColumn String index of the column eg: 'A' |
||
1383 | * @param bool $create |
||
1384 | * |
||
1385 | * @return ColumnDimension |
||
1386 | */ |
||
1387 | 184 | public function getColumnDimension($pColumn, $create = true) |
|
1388 | { |
||
1389 | // Uppercase coordinate |
||
1390 | 184 | $pColumn = strtoupper($pColumn); |
|
1391 | |||
1392 | // Fetch dimensions |
||
1393 | 184 | if (!isset($this->columnDimensions[$pColumn])) { |
|
1394 | 184 | if (!$create) { |
|
1395 | 175 | return null; |
|
1396 | } |
||
1397 | 51 | $this->columnDimensions[$pColumn] = new ColumnDimension($pColumn); |
|
1398 | |||
1399 | 51 | if (Coordinate::columnIndexFromString($this->cachedHighestColumn) < Coordinate::columnIndexFromString($pColumn)) { |
|
1400 | 29 | $this->cachedHighestColumn = $pColumn; |
|
1401 | } |
||
1402 | } |
||
1403 | |||
1404 | 51 | return $this->columnDimensions[$pColumn]; |
|
1405 | } |
||
1406 | |||
1407 | /** |
||
1408 | * Get column dimension at a specific column by using numeric cell coordinates. |
||
1409 | * |
||
1410 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1411 | * |
||
1412 | * @return ColumnDimension |
||
1413 | */ |
||
1414 | 17 | public function getColumnDimensionByColumn($columnIndex) |
|
1415 | { |
||
1416 | 17 | return $this->getColumnDimension(Coordinate::stringFromColumnIndex($columnIndex)); |
|
1417 | } |
||
1418 | |||
1419 | /** |
||
1420 | * Get styles. |
||
1421 | * |
||
1422 | * @return Style[] |
||
1423 | */ |
||
1424 | public function getStyles() |
||
1425 | { |
||
1426 | return $this->styles; |
||
1427 | } |
||
1428 | |||
1429 | /** |
||
1430 | * Get style for cell. |
||
1431 | * |
||
1432 | * @param string $pCellCoordinate Cell coordinate (or range) to get style for, eg: 'A1' |
||
1433 | * |
||
1434 | * @throws Exception |
||
1435 | * |
||
1436 | * @return Style |
||
1437 | */ |
||
1438 | 102 | public function getStyle($pCellCoordinate) |
|
1439 | { |
||
1440 | // set this sheet as active |
||
1441 | 102 | $this->parent->setActiveSheetIndex($this->parent->getIndex($this)); |
|
1442 | |||
1443 | // set cell coordinate as active |
||
1444 | 102 | $this->setSelectedCells(strtoupper($pCellCoordinate)); |
|
1445 | |||
1446 | 102 | return $this->parent->getCellXfSupervisor(); |
|
1447 | } |
||
1448 | |||
1449 | /** |
||
1450 | * Get conditional styles for a cell. |
||
1451 | * |
||
1452 | * @param string $pCoordinate eg: 'A1' |
||
1453 | * |
||
1454 | * @return Conditional[] |
||
1455 | */ |
||
1456 | 3 | public function getConditionalStyles($pCoordinate) |
|
1457 | { |
||
1458 | 3 | $pCoordinate = strtoupper($pCoordinate); |
|
1459 | 3 | if (!isset($this->conditionalStylesCollection[$pCoordinate])) { |
|
1460 | 2 | $this->conditionalStylesCollection[$pCoordinate] = []; |
|
1461 | } |
||
1462 | |||
1463 | 3 | return $this->conditionalStylesCollection[$pCoordinate]; |
|
1464 | } |
||
1465 | |||
1466 | /** |
||
1467 | * Do conditional styles exist for this cell? |
||
1468 | * |
||
1469 | * @param string $pCoordinate eg: 'A1' |
||
1470 | * |
||
1471 | * @return bool |
||
1472 | */ |
||
1473 | 12 | public function conditionalStylesExists($pCoordinate) |
|
1474 | { |
||
1475 | 12 | return isset($this->conditionalStylesCollection[strtoupper($pCoordinate)]); |
|
1476 | } |
||
1477 | |||
1478 | /** |
||
1479 | * Removes conditional styles for a cell. |
||
1480 | * |
||
1481 | * @param string $pCoordinate eg: 'A1' |
||
1482 | * |
||
1483 | * @return Worksheet |
||
1484 | */ |
||
1485 | 13 | public function removeConditionalStyles($pCoordinate) |
|
1486 | { |
||
1487 | 13 | unset($this->conditionalStylesCollection[strtoupper($pCoordinate)]); |
|
1488 | |||
1489 | 13 | return $this; |
|
1490 | } |
||
1491 | |||
1492 | /** |
||
1493 | * Get collection of conditional styles. |
||
1494 | * |
||
1495 | * @return array |
||
1496 | */ |
||
1497 | 89 | public function getConditionalStylesCollection() |
|
1498 | { |
||
1499 | 89 | return $this->conditionalStylesCollection; |
|
1500 | } |
||
1501 | |||
1502 | /** |
||
1503 | * Set conditional styles. |
||
1504 | * |
||
1505 | * @param string $pCoordinate eg: 'A1' |
||
1506 | * @param $pValue Conditional[] |
||
1507 | * |
||
1508 | * @return Worksheet |
||
1509 | */ |
||
1510 | 3 | public function setConditionalStyles($pCoordinate, $pValue) |
|
1511 | { |
||
1512 | 3 | $this->conditionalStylesCollection[strtoupper($pCoordinate)] = $pValue; |
|
1513 | |||
1514 | 3 | return $this; |
|
1515 | } |
||
1516 | |||
1517 | /** |
||
1518 | * Get style for cell by using numeric cell coordinates. |
||
1519 | * |
||
1520 | * @param int $columnIndex1 Numeric column coordinate of the cell |
||
1521 | * @param int $row1 Numeric row coordinate of the cell |
||
1522 | * @param null|int $columnIndex2 Numeric column coordinate of the range cell |
||
1523 | * @param null|int $row2 Numeric row coordinate of the range cell |
||
1524 | * |
||
1525 | * @return Style |
||
1526 | */ |
||
1527 | public function getStyleByColumnAndRow($columnIndex1, $row1, $columnIndex2 = null, $row2 = null) |
||
1528 | { |
||
1529 | if ($columnIndex2 !== null && $row2 !== null) { |
||
1530 | $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; |
||
1531 | |||
1532 | return $this->getStyle($cellRange); |
||
1533 | } |
||
1534 | |||
1535 | return $this->getStyle(Coordinate::stringFromColumnIndex($columnIndex1) . $row1); |
||
1536 | } |
||
1537 | |||
1538 | /** |
||
1539 | * Duplicate cell style to a range of cells. |
||
1540 | * |
||
1541 | * Please note that this will overwrite existing cell styles for cells in range! |
||
1542 | * |
||
1543 | * @param Style $pCellStyle Cell style to duplicate |
||
1544 | * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") |
||
1545 | * |
||
1546 | * @throws Exception |
||
1547 | * |
||
1548 | * @return Worksheet |
||
1549 | */ |
||
1550 | 2 | public function duplicateStyle(Style $pCellStyle, $pRange) |
|
1551 | { |
||
1552 | // Add the style to the workbook if necessary |
||
1553 | 2 | $workbook = $this->parent; |
|
1554 | 2 | if ($existingStyle = $this->parent->getCellXfByHashCode($pCellStyle->getHashCode())) { |
|
1555 | // there is already such cell Xf in our collection |
||
1556 | 1 | $xfIndex = $existingStyle->getIndex(); |
|
1557 | } else { |
||
1558 | // we don't have such a cell Xf, need to add |
||
1559 | 2 | $workbook->addCellXf($pCellStyle); |
|
1560 | 2 | $xfIndex = $pCellStyle->getIndex(); |
|
1561 | } |
||
1562 | |||
1563 | // Calculate range outer borders |
||
1564 | 2 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange . ':' . $pRange); |
|
1565 | |||
1566 | // Make sure we can loop upwards on rows and columns |
||
1567 | 2 | if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { |
|
1568 | $tmp = $rangeStart; |
||
1569 | $rangeStart = $rangeEnd; |
||
1570 | $rangeEnd = $tmp; |
||
1571 | } |
||
1572 | |||
1573 | // Loop through cells and apply styles |
||
1574 | 2 | for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { |
|
1575 | 2 | for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) { |
|
1576 | 2 | $this->getCell(Coordinate::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex); |
|
1577 | } |
||
1578 | } |
||
1579 | |||
1580 | 2 | return $this; |
|
1581 | } |
||
1582 | |||
1583 | /** |
||
1584 | * Duplicate conditional style to a range of cells. |
||
1585 | * |
||
1586 | * Please note that this will overwrite existing cell styles for cells in range! |
||
1587 | * |
||
1588 | * @param Conditional[] $pCellStyle Cell style to duplicate |
||
1589 | * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") |
||
1590 | * |
||
1591 | * @throws Exception |
||
1592 | * |
||
1593 | * @return Worksheet |
||
1594 | */ |
||
1595 | 2 | public function duplicateConditionalStyle(array $pCellStyle, $pRange = '') |
|
1596 | { |
||
1597 | 2 | foreach ($pCellStyle as $cellStyle) { |
|
1598 | 2 | if (!($cellStyle instanceof Conditional)) { |
|
1599 | throw new Exception('Style is not a conditional style'); |
||
1600 | } |
||
1601 | } |
||
1602 | |||
1603 | // Calculate range outer borders |
||
1604 | 2 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange . ':' . $pRange); |
|
1605 | |||
1606 | // Make sure we can loop upwards on rows and columns |
||
1607 | 2 | if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) { |
|
1608 | $tmp = $rangeStart; |
||
1609 | $rangeStart = $rangeEnd; |
||
1610 | $rangeEnd = $tmp; |
||
1611 | } |
||
1612 | |||
1613 | // Loop through cells and apply styles |
||
1614 | 2 | for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) { |
|
1615 | 2 | for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) { |
|
1616 | 2 | $this->setConditionalStyles(Coordinate::stringFromColumnIndex($col) . $row, $pCellStyle); |
|
1617 | } |
||
1618 | } |
||
1619 | |||
1620 | 2 | return $this; |
|
1621 | } |
||
1622 | |||
1623 | /** |
||
1624 | * Set break on a cell. |
||
1625 | * |
||
1626 | * @param string $pCoordinate Cell coordinate (e.g. A1) |
||
1627 | * @param int $pBreak Break type (type of Worksheet::BREAK_*) |
||
1628 | * |
||
1629 | * @throws Exception |
||
1630 | * |
||
1631 | * @return Worksheet |
||
1632 | */ |
||
1633 | 1 | public function setBreak($pCoordinate, $pBreak) |
|
1634 | { |
||
1635 | // Uppercase coordinate |
||
1636 | 1 | $pCoordinate = strtoupper($pCoordinate); |
|
1637 | |||
1638 | 1 | if ($pCoordinate != '') { |
|
1639 | 1 | if ($pBreak == self::BREAK_NONE) { |
|
1640 | if (isset($this->breaks[$pCoordinate])) { |
||
1641 | unset($this->breaks[$pCoordinate]); |
||
1642 | } |
||
1643 | } else { |
||
1644 | 1 | $this->breaks[$pCoordinate] = $pBreak; |
|
1645 | } |
||
1646 | } else { |
||
1647 | throw new Exception('No cell coordinate specified.'); |
||
1648 | } |
||
1649 | |||
1650 | 1 | return $this; |
|
1651 | } |
||
1652 | |||
1653 | /** |
||
1654 | * Set break on a cell by using numeric cell coordinates. |
||
1655 | * |
||
1656 | * @param int $columnIndex Numeric column coordinate of the cell |
||
1657 | * @param int $row Numeric row coordinate of the cell |
||
1658 | * @param int $break Break type (type of Worksheet::BREAK_*) |
||
1659 | * |
||
1660 | * @return Worksheet |
||
1661 | */ |
||
1662 | public function setBreakByColumnAndRow($columnIndex, $row, $break) |
||
1663 | { |
||
1664 | return $this->setBreak(Coordinate::stringFromColumnIndex($columnIndex) . $row, $break); |
||
1665 | } |
||
1666 | |||
1667 | /** |
||
1668 | * Get breaks. |
||
1669 | * |
||
1670 | * @return array[] |
||
1671 | */ |
||
1672 | 92 | public function getBreaks() |
|
1673 | { |
||
1674 | 92 | return $this->breaks; |
|
1675 | } |
||
1676 | |||
1677 | /** |
||
1678 | * Set merge on a cell range. |
||
1679 | * |
||
1680 | * @param string $pRange Cell range (e.g. A1:E1) |
||
1681 | * |
||
1682 | * @throws Exception |
||
1683 | * |
||
1684 | * @return Worksheet |
||
1685 | */ |
||
1686 | 36 | public function mergeCells($pRange) |
|
1687 | { |
||
1688 | // Uppercase coordinate |
||
1689 | 36 | $pRange = strtoupper($pRange); |
|
1690 | |||
1691 | 36 | if (strpos($pRange, ':') !== false) { |
|
1692 | 36 | $this->mergeCells[$pRange] = $pRange; |
|
1693 | |||
1694 | // make sure cells are created |
||
1695 | |||
1696 | // get the cells in the range |
||
1697 | 36 | $aReferences = Coordinate::extractAllCellReferencesInRange($pRange); |
|
1698 | |||
1699 | // create upper left cell if it does not already exist |
||
1700 | 36 | $upperLeft = $aReferences[0]; |
|
1701 | 36 | if (!$this->cellExists($upperLeft)) { |
|
1702 | 13 | $this->getCell($upperLeft)->setValueExplicit(null, DataType::TYPE_NULL); |
|
1703 | } |
||
1704 | |||
1705 | // Blank out the rest of the cells in the range (if they exist) |
||
1706 | 36 | $count = count($aReferences); |
|
1707 | 36 | for ($i = 1; $i < $count; ++$i) { |
|
1708 | 36 | if ($this->cellExists($aReferences[$i])) { |
|
1709 | 14 | $this->getCell($aReferences[$i])->setValueExplicit(null, DataType::TYPE_NULL); |
|
1710 | } |
||
1711 | } |
||
1712 | } else { |
||
1713 | throw new Exception('Merge must be set on a range of cells.'); |
||
1714 | } |
||
1715 | |||
1716 | 36 | return $this; |
|
1717 | } |
||
1718 | |||
1719 | /** |
||
1720 | * Set merge on a cell range by using numeric cell coordinates. |
||
1721 | * |
||
1722 | * @param int $columnIndex1 Numeric column coordinate of the first cell |
||
1723 | * @param int $row1 Numeric row coordinate of the first cell |
||
1724 | * @param int $columnIndex2 Numeric column coordinate of the last cell |
||
1725 | * @param int $row2 Numeric row coordinate of the last cell |
||
1726 | * |
||
1727 | * @throws Exception |
||
1728 | * |
||
1729 | * @return Worksheet |
||
1730 | */ |
||
1731 | public function mergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) |
||
1732 | { |
||
1733 | $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; |
||
1734 | |||
1735 | return $this->mergeCells($cellRange); |
||
1736 | } |
||
1737 | |||
1738 | /** |
||
1739 | * Remove merge on a cell range. |
||
1740 | * |
||
1741 | * @param string $pRange Cell range (e.g. A1:E1) |
||
1742 | * |
||
1743 | * @throws Exception |
||
1744 | * |
||
1745 | * @return Worksheet |
||
1746 | */ |
||
1747 | 11 | public function unmergeCells($pRange) |
|
1748 | { |
||
1749 | // Uppercase coordinate |
||
1750 | 11 | $pRange = strtoupper($pRange); |
|
1751 | |||
1752 | 11 | if (strpos($pRange, ':') !== false) { |
|
1753 | 11 | if (isset($this->mergeCells[$pRange])) { |
|
1754 | 11 | unset($this->mergeCells[$pRange]); |
|
1755 | } else { |
||
1756 | 11 | throw new Exception('Cell range ' . $pRange . ' not known as merged.'); |
|
1757 | } |
||
1758 | } else { |
||
1759 | throw new Exception('Merge can only be removed from a range of cells.'); |
||
1760 | } |
||
1761 | |||
1762 | 11 | return $this; |
|
1763 | } |
||
1764 | |||
1765 | /** |
||
1766 | * Remove merge on a cell range by using numeric cell coordinates. |
||
1767 | * |
||
1768 | * @param int $columnIndex1 Numeric column coordinate of the first cell |
||
1769 | * @param int $row1 Numeric row coordinate of the first cell |
||
1770 | * @param int $columnIndex2 Numeric column coordinate of the last cell |
||
1771 | * @param int $row2 Numeric row coordinate of the last cell |
||
1772 | * |
||
1773 | * @throws Exception |
||
1774 | * |
||
1775 | * @return Worksheet |
||
1776 | */ |
||
1777 | public function unmergeCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) |
||
1778 | { |
||
1779 | $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; |
||
1780 | |||
1781 | return $this->unmergeCells($cellRange); |
||
1782 | } |
||
1783 | |||
1784 | /** |
||
1785 | * Get merge cells array. |
||
1786 | * |
||
1787 | * @return array[] |
||
1788 | */ |
||
1789 | 107 | public function getMergeCells() |
|
1790 | { |
||
1791 | 107 | return $this->mergeCells; |
|
1792 | } |
||
1793 | |||
1794 | /** |
||
1795 | * Set merge cells array for the entire sheet. Use instead mergeCells() to merge |
||
1796 | * a single cell range. |
||
1797 | * |
||
1798 | * @param array $pValue |
||
1799 | * |
||
1800 | * @return Worksheet |
||
1801 | */ |
||
1802 | 14 | public function setMergeCells(array $pValue) |
|
1803 | { |
||
1804 | 14 | $this->mergeCells = $pValue; |
|
1805 | |||
1806 | 14 | return $this; |
|
1807 | } |
||
1808 | |||
1809 | /** |
||
1810 | * Set protection on a cell range. |
||
1811 | * |
||
1812 | * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) |
||
1813 | * @param string $pPassword Password to unlock the protection |
||
1814 | * @param bool $pAlreadyHashed If the password has already been hashed, set this to true |
||
1815 | * |
||
1816 | * @return Worksheet |
||
1817 | */ |
||
1818 | 11 | public function protectCells($pRange, $pPassword, $pAlreadyHashed = false) |
|
1819 | { |
||
1820 | // Uppercase coordinate |
||
1821 | 11 | $pRange = strtoupper($pRange); |
|
1822 | |||
1823 | 11 | if (!$pAlreadyHashed) { |
|
1824 | 11 | $pPassword = Shared\PasswordHasher::hashPassword($pPassword); |
|
1825 | } |
||
1826 | 11 | $this->protectedCells[$pRange] = $pPassword; |
|
1827 | |||
1828 | 11 | return $this; |
|
1829 | } |
||
1830 | |||
1831 | /** |
||
1832 | * Set protection on a cell range by using numeric cell coordinates. |
||
1833 | * |
||
1834 | * @param int $columnIndex1 Numeric column coordinate of the first cell |
||
1835 | * @param int $row1 Numeric row coordinate of the first cell |
||
1836 | * @param int $columnIndex2 Numeric column coordinate of the last cell |
||
1837 | * @param int $row2 Numeric row coordinate of the last cell |
||
1838 | * @param string $password Password to unlock the protection |
||
1839 | * @param bool $alreadyHashed If the password has already been hashed, set this to true |
||
1840 | * |
||
1841 | * @return Worksheet |
||
1842 | */ |
||
1843 | public function protectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2, $password, $alreadyHashed = false) |
||
1844 | { |
||
1845 | $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; |
||
1846 | |||
1847 | return $this->protectCells($cellRange, $password, $alreadyHashed); |
||
1848 | } |
||
1849 | |||
1850 | /** |
||
1851 | * Remove protection on a cell range. |
||
1852 | * |
||
1853 | * @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1) |
||
1854 | * |
||
1855 | * @throws Exception |
||
1856 | * |
||
1857 | * @return Worksheet |
||
1858 | */ |
||
1859 | 11 | public function unprotectCells($pRange) |
|
1860 | { |
||
1861 | // Uppercase coordinate |
||
1862 | 11 | $pRange = strtoupper($pRange); |
|
1863 | |||
1864 | 11 | if (isset($this->protectedCells[$pRange])) { |
|
1865 | 11 | unset($this->protectedCells[$pRange]); |
|
1866 | } else { |
||
1867 | throw new Exception('Cell range ' . $pRange . ' not known as protected.'); |
||
1868 | } |
||
1869 | |||
1870 | 11 | return $this; |
|
1871 | } |
||
1872 | |||
1873 | /** |
||
1874 | * Remove protection on a cell range by using numeric cell coordinates. |
||
1875 | * |
||
1876 | * @param int $columnIndex1 Numeric column coordinate of the first cell |
||
1877 | * @param int $row1 Numeric row coordinate of the first cell |
||
1878 | * @param int $columnIndex2 Numeric column coordinate of the last cell |
||
1879 | * @param int $row2 Numeric row coordinate of the last cell |
||
1880 | * |
||
1881 | * @throws Exception |
||
1882 | * |
||
1883 | * @return Worksheet |
||
1884 | */ |
||
1885 | public function unprotectCellsByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) |
||
1886 | { |
||
1887 | $cellRange = Coordinate::stringFromColumnIndex($columnIndex1) . $row1 . ':' . Coordinate::stringFromColumnIndex($columnIndex2) . $row2; |
||
1888 | |||
1889 | return $this->unprotectCells($cellRange); |
||
1890 | } |
||
1891 | |||
1892 | /** |
||
1893 | * Get protected cells. |
||
1894 | * |
||
1895 | * @return array[] |
||
1896 | */ |
||
1897 | 92 | public function getProtectedCells() |
|
1898 | { |
||
1899 | 92 | return $this->protectedCells; |
|
1900 | } |
||
1901 | |||
1902 | /** |
||
1903 | * Get Autofilter. |
||
1904 | * |
||
1905 | * @return AutoFilter |
||
1906 | */ |
||
1907 | 93 | public function getAutoFilter() |
|
1908 | { |
||
1909 | 93 | return $this->autoFilter; |
|
1910 | } |
||
1911 | |||
1912 | /** |
||
1913 | * Set AutoFilter. |
||
1914 | * |
||
1915 | * @param AutoFilter|string $pValue |
||
1916 | * A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility |
||
1917 | * |
||
1918 | * @throws Exception |
||
1919 | * |
||
1920 | * @return Worksheet |
||
1921 | */ |
||
1922 | 4 | public function setAutoFilter($pValue) |
|
1923 | { |
||
1924 | 4 | if (is_string($pValue)) { |
|
1925 | 4 | $this->autoFilter->setRange($pValue); |
|
1926 | } elseif (is_object($pValue) && ($pValue instanceof AutoFilter)) { |
||
1927 | $this->autoFilter = $pValue; |
||
1928 | } |
||
1929 | |||
1930 | 4 | return $this; |
|
1931 | } |
||
1932 | |||
1933 | /** |
||
1934 | * Set Autofilter Range by using numeric cell coordinates. |
||
1935 | * |
||
1936 | * @param int $columnIndex1 Numeric column coordinate of the first cell |
||
1937 | * @param int $row1 Numeric row coordinate of the first cell |
||
1938 | * @param int $columnIndex2 Numeric column coordinate of the second cell |
||
1939 | * @param int $row2 Numeric row coordinate of the second cell |
||
1940 | * |
||
1941 | * @throws Exception |
||
1942 | * |
||
1943 | * @return Worksheet |
||
1944 | */ |
||
1945 | public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2) |
||
1946 | { |
||
1947 | return $this->setAutoFilter( |
||
1948 | Coordinate::stringFromColumnIndex($columnIndex1) . $row1 |
||
1949 | . ':' . |
||
1950 | Coordinate::stringFromColumnIndex($columnIndex2) . $row2 |
||
1951 | ); |
||
1952 | } |
||
1953 | |||
1954 | /** |
||
1955 | * Remove autofilter. |
||
1956 | * |
||
1957 | * @return Worksheet |
||
1958 | */ |
||
1959 | public function removeAutoFilter() |
||
1960 | { |
||
1961 | $this->autoFilter->setRange(null); |
||
1962 | |||
1963 | return $this; |
||
1964 | } |
||
1965 | |||
1966 | /** |
||
1967 | * Get Freeze Pane. |
||
1968 | * |
||
1969 | * @return string |
||
1970 | */ |
||
1971 | 92 | public function getFreezePane() |
|
1972 | { |
||
1973 | 92 | return $this->freezePane; |
|
1974 | } |
||
1975 | |||
1976 | /** |
||
1977 | * Freeze Pane. |
||
1978 | * |
||
1979 | * Examples: |
||
1980 | * |
||
1981 | * - A2 will freeze the rows above cell A2 (i.e row 1) |
||
1982 | * - B1 will freeze the columns to the left of cell B1 (i.e column A) |
||
1983 | * - B2 will freeze the rows above and to the left of cell B2 (i.e row 1 and column A) |
||
1984 | * |
||
1985 | * @param null|string $cell Position of the split |
||
1986 | * @param null|string $topLeftCell default position of the right bottom pane |
||
1987 | * |
||
1988 | * @throws Exception |
||
1989 | * |
||
1990 | * @return Worksheet |
||
1991 | */ |
||
1992 | 8 | public function freezePane($cell, $topLeftCell = null) |
|
1993 | { |
||
1994 | 8 | if (is_string($cell) && Coordinate::coordinateIsRange($cell)) { |
|
1995 | throw new Exception('Freeze pane can not be set on a range of cells.'); |
||
1996 | } |
||
1997 | |||
1998 | 8 | if ($cell !== null && $topLeftCell === null) { |
|
1999 | 5 | $coordinate = Coordinate::coordinateFromString($cell); |
|
2000 | 5 | $topLeftCell = $coordinate[0] . $coordinate[1]; |
|
2001 | } |
||
2002 | |||
2003 | 8 | $this->freezePane = $cell; |
|
2004 | 8 | $this->topLeftCell = $topLeftCell; |
|
2005 | |||
2006 | 8 | return $this; |
|
2007 | } |
||
2008 | |||
2009 | /** |
||
2010 | * Freeze Pane by using numeric cell coordinates. |
||
2011 | * |
||
2012 | * @param int $columnIndex Numeric column coordinate of the cell |
||
2013 | * @param int $row Numeric row coordinate of the cell |
||
2014 | * |
||
2015 | * @return Worksheet |
||
2016 | */ |
||
2017 | public function freezePaneByColumnAndRow($columnIndex, $row) |
||
2018 | { |
||
2019 | return $this->freezePane(Coordinate::stringFromColumnIndex($columnIndex) . $row); |
||
2020 | } |
||
2021 | |||
2022 | /** |
||
2023 | * Unfreeze Pane. |
||
2024 | * |
||
2025 | * @return Worksheet |
||
2026 | */ |
||
2027 | public function unfreezePane() |
||
2028 | { |
||
2029 | return $this->freezePane(null); |
||
2030 | } |
||
2031 | |||
2032 | /** |
||
2033 | * Get the default position of the right bottom pane. |
||
2034 | * |
||
2035 | * @return int |
||
2036 | */ |
||
2037 | 7 | public function getTopLeftCell() |
|
2038 | { |
||
2039 | 7 | return $this->topLeftCell; |
|
2040 | } |
||
2041 | |||
2042 | /** |
||
2043 | * Insert a new row, updating all possible related data. |
||
2044 | * |
||
2045 | * @param int $pBefore Insert before this one |
||
2046 | * @param int $pNumRows Number of rows to insert |
||
2047 | * |
||
2048 | * @throws Exception |
||
2049 | * |
||
2050 | * @return Worksheet |
||
2051 | */ |
||
2052 | 12 | public function insertNewRowBefore($pBefore, $pNumRows = 1) |
|
2053 | { |
||
2054 | 12 | if ($pBefore >= 1) { |
|
2055 | 12 | $objReferenceHelper = ReferenceHelper::getInstance(); |
|
2056 | 12 | $objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this); |
|
2057 | } else { |
||
2058 | throw new Exception('Rows can only be inserted before at least row 1.'); |
||
2059 | } |
||
2060 | |||
2061 | 12 | return $this; |
|
2062 | } |
||
2063 | |||
2064 | /** |
||
2065 | * Insert a new column, updating all possible related data. |
||
2066 | * |
||
2067 | * @param int $pBefore Insert before this one, eg: 'A' |
||
2068 | * @param int $pNumCols Number of columns to insert |
||
2069 | * |
||
2070 | * @throws Exception |
||
2071 | * |
||
2072 | * @return Worksheet |
||
2073 | */ |
||
2074 | 11 | public function insertNewColumnBefore($pBefore, $pNumCols = 1) |
|
2075 | { |
||
2076 | 11 | if (!is_numeric($pBefore)) { |
|
0 ignored issues
–
show
|
|||
2077 | 11 | $objReferenceHelper = ReferenceHelper::getInstance(); |
|
2078 | 11 | $objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this); |
|
2079 | } else { |
||
2080 | throw new Exception('Column references should not be numeric.'); |
||
2081 | } |
||
2082 | |||
2083 | 11 | return $this; |
|
2084 | } |
||
2085 | |||
2086 | /** |
||
2087 | * Insert a new column, updating all possible related data. |
||
2088 | * |
||
2089 | * @param int $beforeColumnIndex Insert before this one (numeric column coordinate of the cell) |
||
2090 | * @param int $pNumCols Number of columns to insert |
||
2091 | * |
||
2092 | * @throws Exception |
||
2093 | * |
||
2094 | * @return Worksheet |
||
2095 | */ |
||
2096 | public function insertNewColumnBeforeByIndex($beforeColumnIndex, $pNumCols = 1) |
||
2097 | { |
||
2098 | if ($beforeColumnIndex >= 1) { |
||
2099 | return $this->insertNewColumnBefore(Coordinate::stringFromColumnIndex($beforeColumnIndex), $pNumCols); |
||
2100 | } |
||
2101 | |||
2102 | throw new Exception('Columns can only be inserted before at least column A (1).'); |
||
2103 | } |
||
2104 | |||
2105 | /** |
||
2106 | * Delete a row, updating all possible related data. |
||
2107 | * |
||
2108 | * @param int $pRow Remove starting with this one |
||
2109 | * @param int $pNumRows Number of rows to remove |
||
2110 | * |
||
2111 | * @throws Exception |
||
2112 | * |
||
2113 | * @return Worksheet |
||
2114 | */ |
||
2115 | 14 | public function removeRow($pRow, $pNumRows = 1) |
|
2116 | { |
||
2117 | 14 | if ($pRow >= 1) { |
|
2118 | 14 | $highestRow = $this->getHighestDataRow(); |
|
2119 | 14 | $objReferenceHelper = ReferenceHelper::getInstance(); |
|
2120 | 14 | $objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this); |
|
2121 | 14 | for ($r = 0; $r < $pNumRows; ++$r) { |
|
2122 | 14 | $this->getCellCollection()->removeRow($highestRow); |
|
2123 | 14 | --$highestRow; |
|
2124 | } |
||
2125 | } else { |
||
2126 | throw new Exception('Rows to be deleted should at least start from row 1.'); |
||
2127 | } |
||
2128 | |||
2129 | 14 | return $this; |
|
2130 | } |
||
2131 | |||
2132 | /** |
||
2133 | * Remove a column, updating all possible related data. |
||
2134 | * |
||
2135 | * @param string $pColumn Remove starting with this one, eg: 'A' |
||
2136 | * @param int $pNumCols Number of columns to remove |
||
2137 | * |
||
2138 | * @throws Exception |
||
2139 | * |
||
2140 | * @return Worksheet |
||
2141 | */ |
||
2142 | 11 | public function removeColumn($pColumn, $pNumCols = 1) |
|
2143 | { |
||
2144 | 11 | if (!is_numeric($pColumn)) { |
|
2145 | 11 | $highestColumn = $this->getHighestDataColumn(); |
|
2146 | 11 | $pColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($pColumn) + $pNumCols); |
|
2147 | 11 | $objReferenceHelper = ReferenceHelper::getInstance(); |
|
2148 | 11 | $objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this); |
|
2149 | 11 | for ($c = 0; $c < $pNumCols; ++$c) { |
|
2150 | 11 | $this->getCellCollection()->removeColumn($highestColumn); |
|
2151 | 11 | $highestColumn = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($highestColumn) - 1); |
|
2152 | } |
||
2153 | } else { |
||
2154 | throw new Exception('Column references should not be numeric.'); |
||
2155 | } |
||
2156 | |||
2157 | 11 | return $this; |
|
2158 | } |
||
2159 | |||
2160 | /** |
||
2161 | * Remove a column, updating all possible related data. |
||
2162 | * |
||
2163 | * @param int $columnIndex Remove starting with this one (numeric column coordinate of the cell) |
||
2164 | * @param int $numColumns Number of columns to remove |
||
2165 | * |
||
2166 | * @throws Exception |
||
2167 | * |
||
2168 | * @return Worksheet |
||
2169 | */ |
||
2170 | public function removeColumnByIndex($columnIndex, $numColumns = 1) |
||
2171 | { |
||
2172 | if ($columnIndex >= 1) { |
||
2173 | return $this->removeColumn(Coordinate::stringFromColumnIndex($columnIndex), $numColumns); |
||
2174 | } |
||
2175 | |||
2176 | throw new Exception('Columns to be deleted should at least start from column A (1)'); |
||
2177 | } |
||
2178 | |||
2179 | /** |
||
2180 | * Show gridlines? |
||
2181 | * |
||
2182 | * @return bool |
||
2183 | */ |
||
2184 | 100 | public function getShowGridlines() |
|
2185 | { |
||
2186 | 100 | return $this->showGridlines; |
|
2187 | } |
||
2188 | |||
2189 | /** |
||
2190 | * Set show gridlines. |
||
2191 | * |
||
2192 | * @param bool $pValue Show gridlines (true/false) |
||
2193 | * |
||
2194 | * @return Worksheet |
||
2195 | */ |
||
2196 | 55 | public function setShowGridlines($pValue) |
|
2197 | { |
||
2198 | 55 | $this->showGridlines = $pValue; |
|
2199 | |||
2200 | 55 | return $this; |
|
2201 | } |
||
2202 | |||
2203 | /** |
||
2204 | * Print gridlines? |
||
2205 | * |
||
2206 | * @return bool |
||
2207 | */ |
||
2208 | 89 | public function getPrintGridlines() |
|
2209 | { |
||
2210 | 89 | return $this->printGridlines; |
|
2211 | } |
||
2212 | |||
2213 | /** |
||
2214 | * Set print gridlines. |
||
2215 | * |
||
2216 | * @param bool $pValue Print gridlines (true/false) |
||
2217 | * |
||
2218 | * @return Worksheet |
||
2219 | */ |
||
2220 | 21 | public function setPrintGridlines($pValue) |
|
2221 | { |
||
2222 | 21 | $this->printGridlines = $pValue; |
|
2223 | |||
2224 | 21 | return $this; |
|
2225 | } |
||
2226 | |||
2227 | /** |
||
2228 | * Show row and column headers? |
||
2229 | * |
||
2230 | * @return bool |
||
2231 | */ |
||
2232 | 89 | public function getShowRowColHeaders() |
|
2233 | { |
||
2234 | 89 | return $this->showRowColHeaders; |
|
2235 | } |
||
2236 | |||
2237 | /** |
||
2238 | * Set show row and column headers. |
||
2239 | * |
||
2240 | * @param bool $pValue Show row and column headers (true/false) |
||
2241 | * |
||
2242 | * @return Worksheet |
||
2243 | */ |
||
2244 | 53 | public function setShowRowColHeaders($pValue) |
|
2245 | { |
||
2246 | 53 | $this->showRowColHeaders = $pValue; |
|
2247 | |||
2248 | 53 | return $this; |
|
2249 | } |
||
2250 | |||
2251 | /** |
||
2252 | * Show summary below? (Row/Column outlining). |
||
2253 | * |
||
2254 | * @return bool |
||
2255 | */ |
||
2256 | 89 | public function getShowSummaryBelow() |
|
2257 | { |
||
2258 | 89 | return $this->showSummaryBelow; |
|
2259 | } |
||
2260 | |||
2261 | /** |
||
2262 | * Set show summary below. |
||
2263 | * |
||
2264 | * @param bool $pValue Show summary below (true/false) |
||
2265 | * |
||
2266 | * @return Worksheet |
||
2267 | */ |
||
2268 | 52 | public function setShowSummaryBelow($pValue) |
|
2269 | { |
||
2270 | 52 | $this->showSummaryBelow = $pValue; |
|
2271 | |||
2272 | 52 | return $this; |
|
2273 | } |
||
2274 | |||
2275 | /** |
||
2276 | * Show summary right? (Row/Column outlining). |
||
2277 | * |
||
2278 | * @return bool |
||
2279 | */ |
||
2280 | 89 | public function getShowSummaryRight() |
|
2281 | { |
||
2282 | 89 | return $this->showSummaryRight; |
|
2283 | } |
||
2284 | |||
2285 | /** |
||
2286 | * Set show summary right. |
||
2287 | * |
||
2288 | * @param bool $pValue Show summary right (true/false) |
||
2289 | * |
||
2290 | * @return Worksheet |
||
2291 | */ |
||
2292 | 52 | public function setShowSummaryRight($pValue) |
|
2293 | { |
||
2294 | 52 | $this->showSummaryRight = $pValue; |
|
2295 | |||
2296 | 52 | return $this; |
|
2297 | } |
||
2298 | |||
2299 | /** |
||
2300 | * Get comments. |
||
2301 | * |
||
2302 | * @return Comment[] |
||
2303 | */ |
||
2304 | 102 | public function getComments() |
|
2305 | { |
||
2306 | 102 | return $this->comments; |
|
2307 | } |
||
2308 | |||
2309 | /** |
||
2310 | * Set comments array for the entire sheet. |
||
2311 | * |
||
2312 | * @param Comment[] $pValue |
||
2313 | * |
||
2314 | * @return Worksheet |
||
2315 | */ |
||
2316 | 14 | public function setComments(array $pValue) |
|
2317 | { |
||
2318 | 14 | $this->comments = $pValue; |
|
2319 | |||
2320 | 14 | return $this; |
|
2321 | } |
||
2322 | |||
2323 | /** |
||
2324 | * Get comment for cell. |
||
2325 | * |
||
2326 | * @param string $pCellCoordinate Cell coordinate to get comment for, eg: 'A1' |
||
2327 | * |
||
2328 | * @throws Exception |
||
2329 | * |
||
2330 | * @return Comment |
||
2331 | */ |
||
2332 | 29 | public function getComment($pCellCoordinate) |
|
2333 | { |
||
2334 | // Uppercase coordinate |
||
2335 | 29 | $pCellCoordinate = strtoupper($pCellCoordinate); |
|
2336 | |||
2337 | 29 | if (Coordinate::coordinateIsRange($pCellCoordinate)) { |
|
2338 | throw new Exception('Cell coordinate string can not be a range of cells.'); |
||
2339 | 29 | } elseif (strpos($pCellCoordinate, '$') !== false) { |
|
2340 | throw new Exception('Cell coordinate string must not be absolute.'); |
||
2341 | 29 | } elseif ($pCellCoordinate == '') { |
|
2342 | throw new Exception('Cell coordinate can not be zero-length string.'); |
||
2343 | } |
||
2344 | |||
2345 | // Check if we already have a comment for this cell. |
||
2346 | 29 | if (isset($this->comments[$pCellCoordinate])) { |
|
2347 | 19 | return $this->comments[$pCellCoordinate]; |
|
2348 | } |
||
2349 | |||
2350 | // If not, create a new comment. |
||
2351 | 29 | $newComment = new Comment(); |
|
2352 | 29 | $this->comments[$pCellCoordinate] = $newComment; |
|
2353 | |||
2354 | 29 | return $newComment; |
|
2355 | } |
||
2356 | |||
2357 | /** |
||
2358 | * Get comment for cell by using numeric cell coordinates. |
||
2359 | * |
||
2360 | * @param int $columnIndex Numeric column coordinate of the cell |
||
2361 | * @param int $row Numeric row coordinate of the cell |
||
2362 | * |
||
2363 | * @return Comment |
||
2364 | */ |
||
2365 | 3 | public function getCommentByColumnAndRow($columnIndex, $row) |
|
2366 | { |
||
2367 | 3 | return $this->getComment(Coordinate::stringFromColumnIndex($columnIndex) . $row); |
|
2368 | } |
||
2369 | |||
2370 | /** |
||
2371 | * Get active cell. |
||
2372 | * |
||
2373 | * @return string Example: 'A1' |
||
2374 | */ |
||
2375 | 124 | public function getActiveCell() |
|
2376 | { |
||
2377 | 124 | return $this->activeCell; |
|
2378 | } |
||
2379 | |||
2380 | /** |
||
2381 | * Get selected cells. |
||
2382 | * |
||
2383 | * @return string |
||
2384 | */ |
||
2385 | 120 | public function getSelectedCells() |
|
2386 | { |
||
2387 | 120 | return $this->selectedCells; |
|
2388 | } |
||
2389 | |||
2390 | /** |
||
2391 | * Selected cell. |
||
2392 | * |
||
2393 | * @param string $pCoordinate Cell (i.e. A1) |
||
2394 | * |
||
2395 | * @return Worksheet |
||
2396 | */ |
||
2397 | public function setSelectedCell($pCoordinate) |
||
2398 | { |
||
2399 | return $this->setSelectedCells($pCoordinate); |
||
2400 | } |
||
2401 | |||
2402 | /** |
||
2403 | * Select a range of cells. |
||
2404 | * |
||
2405 | * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6' |
||
2406 | * |
||
2407 | * @return Worksheet |
||
2408 | */ |
||
2409 | 145 | public function setSelectedCells($pCoordinate) |
|
2410 | { |
||
2411 | // Uppercase coordinate |
||
2412 | 145 | $pCoordinate = strtoupper($pCoordinate); |
|
2413 | |||
2414 | // Convert 'A' to 'A:A' |
||
2415 | 145 | $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); |
|
2416 | |||
2417 | // Convert '1' to '1:1' |
||
2418 | 145 | $pCoordinate = preg_replace('/^(\d+)$/', '${1}:${1}', $pCoordinate); |
|
2419 | |||
2420 | // Convert 'A:C' to 'A1:C1048576' |
||
2421 | 145 | $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); |
|
2422 | |||
2423 | // Convert '1:3' to 'A1:XFD3' |
||
2424 | 145 | $pCoordinate = preg_replace('/^(\d+):(\d+)$/', 'A${1}:XFD${2}', $pCoordinate); |
|
2425 | |||
2426 | 145 | if (Coordinate::coordinateIsRange($pCoordinate)) { |
|
2427 | 50 | list($first) = Coordinate::splitRange($pCoordinate); |
|
2428 | 50 | $this->activeCell = $first[0]; |
|
2429 | } else { |
||
2430 | 133 | $this->activeCell = $pCoordinate; |
|
2431 | } |
||
2432 | 145 | $this->selectedCells = $pCoordinate; |
|
2433 | |||
2434 | 145 | return $this; |
|
2435 | } |
||
2436 | |||
2437 | /** |
||
2438 | * Selected cell by using numeric cell coordinates. |
||
2439 | * |
||
2440 | * @param int $columnIndex Numeric column coordinate of the cell |
||
2441 | * @param int $row Numeric row coordinate of the cell |
||
2442 | * |
||
2443 | * @throws Exception |
||
2444 | * |
||
2445 | * @return Worksheet |
||
2446 | */ |
||
2447 | public function setSelectedCellByColumnAndRow($columnIndex, $row) |
||
2448 | { |
||
2449 | return $this->setSelectedCells(Coordinate::stringFromColumnIndex($columnIndex) . $row); |
||
2450 | } |
||
2451 | |||
2452 | /** |
||
2453 | * Get right-to-left. |
||
2454 | * |
||
2455 | * @return bool |
||
2456 | */ |
||
2457 | 89 | public function getRightToLeft() |
|
2458 | { |
||
2459 | 89 | return $this->rightToLeft; |
|
2460 | } |
||
2461 | |||
2462 | /** |
||
2463 | * Set right-to-left. |
||
2464 | * |
||
2465 | * @param bool $value Right-to-left true/false |
||
2466 | * |
||
2467 | * @return Worksheet |
||
2468 | */ |
||
2469 | 23 | public function setRightToLeft($value) |
|
2470 | { |
||
2471 | 23 | $this->rightToLeft = $value; |
|
2472 | |||
2473 | 23 | return $this; |
|
2474 | } |
||
2475 | |||
2476 | /** |
||
2477 | * Fill worksheet from values in array. |
||
2478 | * |
||
2479 | * @param array $source Source array |
||
2480 | * @param mixed $nullValue Value in source array that stands for blank cell |
||
2481 | * @param string $startCell Insert array starting from this cell address as the top left coordinate |
||
2482 | * @param bool $strictNullComparison Apply strict comparison when testing for null values in the array |
||
2483 | * |
||
2484 | * @throws Exception |
||
2485 | * |
||
2486 | * @return Worksheet |
||
2487 | */ |
||
2488 | 48 | public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) |
|
2489 | { |
||
2490 | // Convert a 1-D array to 2-D (for ease of looping) |
||
2491 | 48 | if (!is_array(end($source))) { |
|
2492 | 3 | $source = [$source]; |
|
2493 | } |
||
2494 | |||
2495 | // start coordinate |
||
2496 | 48 | list($startColumn, $startRow) = Coordinate::coordinateFromString($startCell); |
|
2497 | |||
2498 | // Loop through $source |
||
2499 | 48 | foreach ($source as $rowData) { |
|
2500 | 48 | $currentColumn = $startColumn; |
|
2501 | 48 | foreach ($rowData as $cellValue) { |
|
2502 | 48 | if ($strictNullComparison) { |
|
2503 | 3 | if ($cellValue !== $nullValue) { |
|
2504 | // Set cell value |
||
2505 | 3 | $this->getCell($currentColumn . $startRow)->setValue($cellValue); |
|
2506 | } |
||
2507 | } else { |
||
2508 | 45 | if ($cellValue != $nullValue) { |
|
2509 | // Set cell value |
||
2510 | 42 | $this->getCell($currentColumn . $startRow)->setValue($cellValue); |
|
2511 | } |
||
2512 | } |
||
2513 | 48 | ++$currentColumn; |
|
2514 | } |
||
2515 | 48 | ++$startRow; |
|
2516 | } |
||
2517 | |||
2518 | 48 | return $this; |
|
2519 | } |
||
2520 | |||
2521 | /** |
||
2522 | * Create array from a range of cells. |
||
2523 | * |
||
2524 | * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") |
||
2525 | * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist |
||
2526 | * @param bool $calculateFormulas Should formulas be calculated? |
||
2527 | * @param bool $formatData Should formatting be applied to cell values? |
||
2528 | * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero |
||
2529 | * True - Return rows and columns indexed by their actual row and column IDs |
||
2530 | * |
||
2531 | * @return array |
||
2532 | */ |
||
2533 | 28 | public function rangeToArray($pRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) |
|
2534 | { |
||
2535 | // Returnvalue |
||
2536 | 28 | $returnValue = []; |
|
2537 | // Identify the range that we need to extract from the worksheet |
||
2538 | 28 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange); |
|
2539 | 28 | $minCol = Coordinate::stringFromColumnIndex($rangeStart[0]); |
|
2540 | 28 | $minRow = $rangeStart[1]; |
|
2541 | 28 | $maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]); |
|
2542 | 28 | $maxRow = $rangeEnd[1]; |
|
2543 | |||
2544 | 28 | ++$maxCol; |
|
2545 | // Loop through rows |
||
2546 | 28 | $r = -1; |
|
2547 | 28 | for ($row = $minRow; $row <= $maxRow; ++$row) { |
|
2548 | 28 | $rRef = ($returnCellRef) ? $row : ++$r; |
|
2549 | 28 | $c = -1; |
|
2550 | // Loop through columns in the current row |
||
2551 | 28 | for ($col = $minCol; $col != $maxCol; ++$col) { |
|
2552 | 28 | $cRef = ($returnCellRef) ? $col : ++$c; |
|
2553 | // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen |
||
2554 | // so we test and retrieve directly against cellCollection |
||
2555 | 28 | if ($this->cellCollection->has($col . $row)) { |
|
2556 | // Cell exists |
||
2557 | 28 | $cell = $this->cellCollection->get($col . $row); |
|
2558 | 28 | if ($cell->getValue() !== null) { |
|
2559 | 28 | if ($cell->getValue() instanceof RichText) { |
|
2560 | 2 | $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); |
|
2561 | } else { |
||
2562 | 28 | if ($calculateFormulas) { |
|
2563 | 27 | $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); |
|
2564 | } else { |
||
2565 | 2 | $returnValue[$rRef][$cRef] = $cell->getValue(); |
|
2566 | } |
||
2567 | } |
||
2568 | |||
2569 | 28 | if ($formatData) { |
|
2570 | 27 | $style = $this->parent->getCellXfByIndex($cell->getXfIndex()); |
|
2571 | 27 | $returnValue[$rRef][$cRef] = NumberFormat::toFormattedString( |
|
2572 | 27 | $returnValue[$rRef][$cRef], |
|
2573 | 28 | ($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL |
|
2574 | ); |
||
2575 | } |
||
2576 | } else { |
||
2577 | // Cell holds a NULL |
||
2578 | 28 | $returnValue[$rRef][$cRef] = $nullValue; |
|
2579 | } |
||
2580 | } else { |
||
2581 | // Cell doesn't exist |
||
2582 | 13 | $returnValue[$rRef][$cRef] = $nullValue; |
|
2583 | } |
||
2584 | } |
||
2585 | } |
||
2586 | |||
2587 | // Return |
||
2588 | 28 | return $returnValue; |
|
2589 | } |
||
2590 | |||
2591 | /** |
||
2592 | * Create array from a range of cells. |
||
2593 | * |
||
2594 | * @param string $pNamedRange Name of the Named Range |
||
2595 | * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist |
||
2596 | * @param bool $calculateFormulas Should formulas be calculated? |
||
2597 | * @param bool $formatData Should formatting be applied to cell values? |
||
2598 | * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero |
||
2599 | * True - Return rows and columns indexed by their actual row and column IDs |
||
2600 | * |
||
2601 | * @throws Exception |
||
2602 | * |
||
2603 | * @return array |
||
2604 | */ |
||
2605 | public function namedRangeToArray($pNamedRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) |
||
2606 | { |
||
2607 | $namedRange = NamedRange::resolveRange($pNamedRange, $this); |
||
2608 | if ($namedRange !== null) { |
||
2609 | $pWorkSheet = $namedRange->getWorksheet(); |
||
2610 | $pCellRange = $namedRange->getRange(); |
||
2611 | |||
2612 | return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef); |
||
2613 | } |
||
2614 | |||
2615 | throw new Exception('Named Range ' . $pNamedRange . ' does not exist.'); |
||
2616 | } |
||
2617 | |||
2618 | /** |
||
2619 | * Create array from worksheet. |
||
2620 | * |
||
2621 | * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist |
||
2622 | * @param bool $calculateFormulas Should formulas be calculated? |
||
2623 | * @param bool $formatData Should formatting be applied to cell values? |
||
2624 | * @param bool $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero |
||
2625 | * True - Return rows and columns indexed by their actual row and column IDs |
||
2626 | * |
||
2627 | * @return array |
||
2628 | */ |
||
2629 | 15 | public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) |
|
2630 | { |
||
2631 | // Garbage collect... |
||
2632 | 15 | $this->garbageCollect(); |
|
2633 | |||
2634 | // Identify the range that we need to extract from the worksheet |
||
2635 | 15 | $maxCol = $this->getHighestColumn(); |
|
2636 | 15 | $maxRow = $this->getHighestRow(); |
|
2637 | |||
2638 | // Return |
||
2639 | 15 | return $this->rangeToArray('A1:' . $maxCol . $maxRow, $nullValue, $calculateFormulas, $formatData, $returnCellRef); |
|
2640 | } |
||
2641 | |||
2642 | /** |
||
2643 | * Get row iterator. |
||
2644 | * |
||
2645 | * @param int $startRow The row number at which to start iterating |
||
2646 | * @param int $endRow The row number at which to stop iterating |
||
2647 | * |
||
2648 | * @return RowIterator |
||
2649 | */ |
||
2650 | 9 | public function getRowIterator($startRow = 1, $endRow = null) |
|
2651 | { |
||
2652 | 9 | return new RowIterator($this, $startRow, $endRow); |
|
2653 | } |
||
2654 | |||
2655 | /** |
||
2656 | * Get column iterator. |
||
2657 | * |
||
2658 | * @param string $startColumn The column address at which to start iterating |
||
2659 | * @param string $endColumn The column address at which to stop iterating |
||
2660 | * |
||
2661 | * @return ColumnIterator |
||
2662 | */ |
||
2663 | public function getColumnIterator($startColumn = 'A', $endColumn = null) |
||
2664 | { |
||
2665 | return new ColumnIterator($this, $startColumn, $endColumn); |
||
2666 | } |
||
2667 | |||
2668 | /** |
||
2669 | * Run PhpSpreadsheet garbage collector. |
||
2670 | * |
||
2671 | * @return Worksheet |
||
2672 | */ |
||
2673 | 120 | public function garbageCollect() |
|
2674 | { |
||
2675 | // Flush cache |
||
2676 | 120 | $this->cellCollection->get('A1'); |
|
2677 | |||
2678 | // Lookup highest column and highest row if cells are cleaned |
||
2679 | 120 | $colRow = $this->cellCollection->getHighestRowAndColumn(); |
|
2680 | 120 | $highestRow = $colRow['row']; |
|
2681 | 120 | $highestColumn = Coordinate::columnIndexFromString($colRow['column']); |
|
2682 | |||
2683 | // Loop through column dimensions |
||
2684 | 120 | foreach ($this->columnDimensions as $dimension) { |
|
2685 | 35 | $highestColumn = max($highestColumn, Coordinate::columnIndexFromString($dimension->getColumnIndex())); |
|
2686 | } |
||
2687 | |||
2688 | // Loop through row dimensions |
||
2689 | 120 | foreach ($this->rowDimensions as $dimension) { |
|
2690 | 48 | $highestRow = max($highestRow, $dimension->getRowIndex()); |
|
2691 | } |
||
2692 | |||
2693 | // Cache values |
||
2694 | 120 | if ($highestColumn < 1) { |
|
2695 | $this->cachedHighestColumn = 'A'; |
||
2696 | } else { |
||
2697 | 120 | $this->cachedHighestColumn = Coordinate::stringFromColumnIndex($highestColumn); |
|
2698 | } |
||
2699 | 120 | $this->cachedHighestRow = $highestRow; |
|
2700 | |||
2701 | // Return |
||
2702 | 120 | return $this; |
|
2703 | } |
||
2704 | |||
2705 | /** |
||
2706 | * Get hash code. |
||
2707 | * |
||
2708 | * @return string Hash code |
||
2709 | */ |
||
2710 | 160 | public function getHashCode() |
|
2711 | { |
||
2712 | 160 | if ($this->dirty) { |
|
2713 | 160 | $this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__); |
|
2714 | 160 | $this->dirty = false; |
|
2715 | } |
||
2716 | |||
2717 | 160 | return $this->hash; |
|
2718 | } |
||
2719 | |||
2720 | /** |
||
2721 | * Extract worksheet title from range. |
||
2722 | * |
||
2723 | * Example: extractSheetTitle("testSheet!A1") ==> 'A1' |
||
2724 | * Example: extractSheetTitle("'testSheet 1'!A1", true) ==> ['testSheet 1', 'A1']; |
||
2725 | * |
||
2726 | * @param string $pRange Range to extract title from |
||
2727 | * @param bool $returnRange Return range? (see example) |
||
2728 | * |
||
2729 | * @return mixed |
||
2730 | */ |
||
2731 | 97 | public static function extractSheetTitle($pRange, $returnRange = false) |
|
2732 | { |
||
2733 | // Sheet title included? |
||
2734 | 97 | if (($sep = strrpos($pRange, '!')) === false) { |
|
2735 | 46 | return $returnRange ? ['', $pRange] : ''; |
|
2736 | } |
||
2737 | |||
2738 | 73 | if ($returnRange) { |
|
2739 | 73 | return [substr($pRange, 0, $sep), substr($pRange, $sep + 1)]; |
|
2740 | } |
||
2741 | |||
2742 | 2 | return substr($pRange, $sep + 1); |
|
2743 | } |
||
2744 | |||
2745 | /** |
||
2746 | * Get hyperlink. |
||
2747 | * |
||
2748 | * @param string $pCellCoordinate Cell coordinate to get hyperlink for, eg: 'A1' |
||
2749 | * |
||
2750 | * @return Hyperlink |
||
2751 | */ |
||
2752 | 20 | public function getHyperlink($pCellCoordinate) |
|
2753 | { |
||
2754 | // return hyperlink if we already have one |
||
2755 | 20 | if (isset($this->hyperlinkCollection[$pCellCoordinate])) { |
|
2756 | 14 | return $this->hyperlinkCollection[$pCellCoordinate]; |
|
2757 | } |
||
2758 | |||
2759 | // else create hyperlink |
||
2760 | 20 | $this->hyperlinkCollection[$pCellCoordinate] = new Hyperlink(); |
|
2761 | |||
2762 | 20 | return $this->hyperlinkCollection[$pCellCoordinate]; |
|
2763 | } |
||
2764 | |||
2765 | /** |
||
2766 | * Set hyperlink. |
||
2767 | * |
||
2768 | * @param string $pCellCoordinate Cell coordinate to insert hyperlink, eg: 'A1' |
||
2769 | * @param null|Hyperlink $pHyperlink |
||
2770 | * |
||
2771 | * @return Worksheet |
||
2772 | */ |
||
2773 | 12 | public function setHyperlink($pCellCoordinate, Hyperlink $pHyperlink = null) |
|
2774 | { |
||
2775 | 12 | if ($pHyperlink === null) { |
|
2776 | 12 | unset($this->hyperlinkCollection[$pCellCoordinate]); |
|
2777 | } else { |
||
2778 | 12 | $this->hyperlinkCollection[$pCellCoordinate] = $pHyperlink; |
|
2779 | } |
||
2780 | |||
2781 | 12 | return $this; |
|
2782 | } |
||
2783 | |||
2784 | /** |
||
2785 | * Hyperlink at a specific coordinate exists? |
||
2786 | * |
||
2787 | * @param string $pCoordinate eg: 'A1' |
||
2788 | * |
||
2789 | * @return bool |
||
2790 | */ |
||
2791 | 13 | public function hyperlinkExists($pCoordinate) |
|
2792 | { |
||
2793 | 13 | return isset($this->hyperlinkCollection[$pCoordinate]); |
|
2794 | } |
||
2795 | |||
2796 | /** |
||
2797 | * Get collection of hyperlinks. |
||
2798 | * |
||
2799 | * @return Hyperlink[] |
||
2800 | */ |
||
2801 | 92 | public function getHyperlinkCollection() |
|
2802 | { |
||
2803 | 92 | return $this->hyperlinkCollection; |
|
2804 | } |
||
2805 | |||
2806 | /** |
||
2807 | * Get data validation. |
||
2808 | * |
||
2809 | * @param string $pCellCoordinate Cell coordinate to get data validation for, eg: 'A1' |
||
2810 | * |
||
2811 | * @return DataValidation |
||
2812 | */ |
||
2813 | 4 | public function getDataValidation($pCellCoordinate) |
|
2814 | { |
||
2815 | // return data validation if we already have one |
||
2816 | 4 | if (isset($this->dataValidationCollection[$pCellCoordinate])) { |
|
2817 | 2 | return $this->dataValidationCollection[$pCellCoordinate]; |
|
2818 | } |
||
2819 | |||
2820 | // else create data validation |
||
2821 | 4 | $this->dataValidationCollection[$pCellCoordinate] = new DataValidation(); |
|
2822 | |||
2823 | 4 | return $this->dataValidationCollection[$pCellCoordinate]; |
|
2824 | } |
||
2825 | |||
2826 | /** |
||
2827 | * Set data validation. |
||
2828 | * |
||
2829 | * @param string $pCellCoordinate Cell coordinate to insert data validation, eg: 'A1' |
||
2830 | * @param null|DataValidation $pDataValidation |
||
2831 | * |
||
2832 | * @return Worksheet |
||
2833 | */ |
||
2834 | public function setDataValidation($pCellCoordinate, DataValidation $pDataValidation = null) |
||
2835 | { |
||
2836 | if ($pDataValidation === null) { |
||
2837 | unset($this->dataValidationCollection[$pCellCoordinate]); |
||
2838 | } else { |
||
2839 | $this->dataValidationCollection[$pCellCoordinate] = $pDataValidation; |
||
2840 | } |
||
2841 | |||
2842 | return $this; |
||
2843 | } |
||
2844 | |||
2845 | /** |
||
2846 | * Data validation at a specific coordinate exists? |
||
2847 | * |
||
2848 | * @param string $pCoordinate eg: 'A1' |
||
2849 | * |
||
2850 | * @return bool |
||
2851 | */ |
||
2852 | 3 | public function dataValidationExists($pCoordinate) |
|
2853 | { |
||
2854 | 3 | return isset($this->dataValidationCollection[$pCoordinate]); |
|
2855 | } |
||
2856 | |||
2857 | /** |
||
2858 | * Get collection of data validations. |
||
2859 | * |
||
2860 | * @return DataValidation[] |
||
2861 | */ |
||
2862 | 92 | public function getDataValidationCollection() |
|
2863 | { |
||
2864 | 92 | return $this->dataValidationCollection; |
|
2865 | } |
||
2866 | |||
2867 | /** |
||
2868 | * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet. |
||
2869 | * |
||
2870 | * @param string $range |
||
2871 | * |
||
2872 | * @return string Adjusted range value |
||
2873 | */ |
||
2874 | public function shrinkRangeToFit($range) |
||
2875 | { |
||
2876 | $maxCol = $this->getHighestColumn(); |
||
2877 | $maxRow = $this->getHighestRow(); |
||
2878 | $maxCol = Coordinate::columnIndexFromString($maxCol); |
||
2879 | |||
2880 | $rangeBlocks = explode(' ', $range); |
||
2881 | foreach ($rangeBlocks as &$rangeSet) { |
||
2882 | $rangeBoundaries = Coordinate::getRangeBoundaries($rangeSet); |
||
2883 | |||
2884 | if (Coordinate::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { |
||
2885 | $rangeBoundaries[0][0] = Coordinate::stringFromColumnIndex($maxCol); |
||
2886 | } |
||
2887 | if ($rangeBoundaries[0][1] > $maxRow) { |
||
2888 | $rangeBoundaries[0][1] = $maxRow; |
||
2889 | } |
||
2890 | if (Coordinate::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { |
||
2891 | $rangeBoundaries[1][0] = Coordinate::stringFromColumnIndex($maxCol); |
||
2892 | } |
||
2893 | if ($rangeBoundaries[1][1] > $maxRow) { |
||
2894 | $rangeBoundaries[1][1] = $maxRow; |
||
2895 | } |
||
2896 | $rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1]; |
||
2897 | } |
||
2898 | unset($rangeSet); |
||
2899 | $stRange = implode(' ', $rangeBlocks); |
||
2900 | |||
2901 | return $stRange; |
||
2902 | } |
||
2903 | |||
2904 | /** |
||
2905 | * Get tab color. |
||
2906 | * |
||
2907 | * @return Color |
||
2908 | */ |
||
2909 | 11 | public function getTabColor() |
|
2910 | { |
||
2911 | 11 | if ($this->tabColor === null) { |
|
2912 | 11 | $this->tabColor = new Color(); |
|
2913 | } |
||
2914 | |||
2915 | 11 | return $this->tabColor; |
|
2916 | } |
||
2917 | |||
2918 | /** |
||
2919 | * Reset tab color. |
||
2920 | * |
||
2921 | * @return Worksheet |
||
2922 | */ |
||
2923 | public function resetTabColor() |
||
2924 | { |
||
2925 | $this->tabColor = null; |
||
2926 | unset($this->tabColor); |
||
2927 | |||
2928 | return $this; |
||
2929 | } |
||
2930 | |||
2931 | /** |
||
2932 | * Tab color set? |
||
2933 | * |
||
2934 | * @return bool |
||
2935 | */ |
||
2936 | 98 | public function isTabColorSet() |
|
2937 | { |
||
2938 | 98 | return $this->tabColor !== null; |
|
2939 | } |
||
2940 | |||
2941 | /** |
||
2942 | * Copy worksheet (!= clone!). |
||
2943 | * |
||
2944 | * @return Worksheet |
||
2945 | */ |
||
2946 | public function copy() |
||
2947 | { |
||
2948 | $copied = clone $this; |
||
2949 | |||
2950 | return $copied; |
||
2951 | } |
||
2952 | |||
2953 | /** |
||
2954 | * Implement PHP __clone to create a deep clone, not just a shallow copy. |
||
2955 | */ |
||
2956 | 2 | public function __clone() |
|
2957 | { |
||
2958 | 2 | foreach ($this as $key => $val) { |
|
2959 | 2 | if ($key == 'parent') { |
|
2960 | 2 | continue; |
|
2961 | } |
||
2962 | |||
2963 | 2 | if (is_object($val) || (is_array($val))) { |
|
2964 | 2 | if ($key == 'cellCollection') { |
|
2965 | 2 | $newCollection = $this->cellCollection->cloneCellCollection($this); |
|
2966 | 2 | $this->cellCollection = $newCollection; |
|
2967 | 2 | } elseif ($key == 'drawingCollection') { |
|
2968 | 2 | $currentCollection = $this->drawingCollection; |
|
2969 | 2 | $this->drawingCollection = new ArrayObject(); |
|
2970 | 2 | foreach ($currentCollection as $item) { |
|
2971 | 1 | if (is_object($item)) { |
|
2972 | 1 | $newDrawing = clone $item; |
|
2973 | 1 | $newDrawing->setWorksheet($this); |
|
2974 | } |
||
2975 | } |
||
2976 | 2 | } elseif (($key == 'autoFilter') && ($this->autoFilter instanceof AutoFilter)) { |
|
2977 | 2 | $newAutoFilter = clone $this->autoFilter; |
|
2978 | 2 | $this->autoFilter = $newAutoFilter; |
|
2979 | 2 | $this->autoFilter->setParent($this); |
|
2980 | } else { |
||
2981 | 2 | $this->{$key} = unserialize(serialize($val)); |
|
2982 | } |
||
2983 | } |
||
2984 | } |
||
2985 | 2 | } |
|
2986 | |||
2987 | /** |
||
2988 | * Define the code name of the sheet. |
||
2989 | * |
||
2990 | * @param string $pValue Same rule as Title minus space not allowed (but, like Excel, change |
||
2991 | * silently space to underscore) |
||
2992 | * @param bool $validate False to skip validation of new title. WARNING: This should only be set |
||
2993 | * at parse time (by Readers), where titles can be assumed to be valid. |
||
2994 | * |
||
2995 | * @throws Exception |
||
2996 | * |
||
2997 | * @return Worksheet |
||
2998 | */ |
||
2999 | 225 | public function setCodeName($pValue, $validate = true) |
|
3000 | { |
||
3001 | // Is this a 'rename' or not? |
||
3002 | 225 | if ($this->getCodeName() == $pValue) { |
|
3003 | return $this; |
||
3004 | } |
||
3005 | |||
3006 | 225 | if ($validate) { |
|
3007 | 225 | $pValue = str_replace(' ', '_', $pValue); //Excel does this automatically without flinching, we are doing the same |
|
3008 | |||
3009 | // Syntax check |
||
3010 | // throw an exception if not valid |
||
3011 | 225 | self::checkSheetCodeName($pValue); |
|
3012 | |||
3013 | // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_' |
||
3014 | |||
3015 | 225 | if ($this->getParent()) { |
|
3016 | // Is there already such sheet name? |
||
3017 | 208 | if ($this->getParent()->sheetCodeNameExists($pValue)) { |
|
3018 | // Use name, but append with lowest possible integer |
||
3019 | |||
3020 | 41 | if (Shared\StringHelper::countCharacters($pValue) > 29) { |
|
3021 | $pValue = Shared\StringHelper::substring($pValue, 0, 29); |
||
3022 | } |
||
3023 | 41 | $i = 1; |
|
3024 | 41 | while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) { |
|
3025 | 12 | ++$i; |
|
3026 | 12 | if ($i == 10) { |
|
3027 | if (Shared\StringHelper::countCharacters($pValue) > 28) { |
||
3028 | $pValue = Shared\StringHelper::substring($pValue, 0, 28); |
||
3029 | } |
||
3030 | 12 | } elseif ($i == 100) { |
|
3031 | if (Shared\StringHelper::countCharacters($pValue) > 27) { |
||
3032 | $pValue = Shared\StringHelper::substring($pValue, 0, 27); |
||
3033 | } |
||
3034 | } |
||
3035 | } |
||
3036 | |||
3037 | 41 | $pValue = $pValue . '_' . $i; // ok, we have a valid name |
|
3038 | } |
||
3039 | } |
||
3040 | } |
||
3041 | |||
3042 | 225 | $this->codeName = $pValue; |
|
3043 | |||
3044 | 225 | return $this; |
|
3045 | } |
||
3046 | |||
3047 | /** |
||
3048 | * Return the code name of the sheet. |
||
3049 | * |
||
3050 | * @return null|string |
||
3051 | */ |
||
3052 | 225 | public function getCodeName() |
|
3053 | { |
||
3054 | 225 | return $this->codeName; |
|
3055 | } |
||
3056 | |||
3057 | /** |
||
3058 | * Sheet has a code name ? |
||
3059 | * |
||
3060 | * @return bool |
||
3061 | */ |
||
3062 | 1 | public function hasCodeName() |
|
3063 | { |
||
3064 | 1 | return $this->codeName !== null; |
|
3065 | } |
||
3066 | } |
||
3067 |