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