5
* Copyright (c) 2006 - 2012 PHPExcel
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.
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.
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
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 ##VERSION##, ##DATE##
33
* @package PHPExcel_Worksheet
34
* @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
36
class PHPExcel_Worksheet implements PHPExcel_IComparable
41
const BREAK_COLUMN = 2;
44
const SHEETSTATE_VISIBLE = 'visible';
45
const SHEETSTATE_HIDDEN = 'hidden';
46
const SHEETSTATE_VERYHIDDEN = 'veryHidden';
49
* Invalid characters in sheet title
53
private static $_invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
63
* Cacheable collection of cells
65
* @var PHPExcel_CachedObjectStorage_xxx
67
private $_cellCollection = null;
70
* Collection of row dimensions
72
* @var PHPExcel_Worksheet_RowDimension[]
74
private $_rowDimensions = array();
77
* Default row dimension
79
* @var PHPExcel_Worksheet_RowDimension
81
private $_defaultRowDimension = null;
84
* Collection of column dimensions
86
* @var PHPExcel_Worksheet_ColumnDimension[]
88
private $_columnDimensions = array();
91
* Default column dimension
93
* @var PHPExcel_Worksheet_ColumnDimension
95
private $_defaultColumnDimension = null;
98
* Collection of drawings
100
* @var PHPExcel_Worksheet_BaseDrawing[]
102
private $_drawingCollection = null;
105
* Collection of Chart objects
107
* @var PHPExcel_Chart[]
109
private $_chartCollection = array();
123
private $_sheetState;
128
* @var PHPExcel_Worksheet_PageSetup
135
* @var PHPExcel_Worksheet_PageMargins
137
private $_pageMargins;
142
* @var PHPExcel_Worksheet_HeaderFooter
144
private $_headerFooter;
149
* @var PHPExcel_Worksheet_SheetView
156
* @var PHPExcel_Worksheet_Protection
158
private $_protection;
161
* Collection of styles
163
* @var PHPExcel_Style[]
165
private $_styles = array();
168
* Conditional styles. Indexed by cell coordinate, e.g. 'A1'
172
private $_conditionalStylesCollection = array();
175
* Is the current cell collection sorted already?
179
private $_cellCollectionIsSorted = false;
182
* Collection of breaks
186
private $_breaks = array();
189
* Collection of merged cell ranges
193
private $_mergeCells = array();
196
* Collection of protected cell ranges
200
private $_protectedCells = array();
203
* Autofilter Range and selection
205
* @var PHPExcel_Worksheet_AutoFilter
207
private $_autoFilter = NULL;
214
private $_freezePane = '';
221
private $_showGridlines = true;
228
private $_printGridlines = false;
231
* Show row and column headers?
235
private $_showRowColHeaders = true;
238
* Show summary below? (Row/Column outline)
242
private $_showSummaryBelow = true;
245
* Show summary right? (Row/Column outline)
249
private $_showSummaryRight = true;
252
* Collection of comments
254
* @var PHPExcel_Comment[]
256
private $_comments = array();
259
* Active cell. (Only one!)
263
private $_activeCell = 'A1';
270
private $_selectedCells = 'A1';
273
* Cached highest column
277
private $_cachedHighestColumn = 'A';
284
private $_cachedHighestRow = 1;
291
private $_rightToLeft = false;
294
* Hyperlinks. Indexed by cell coordinate, e.g. 'A1'
298
private $_hyperlinkCollection = array();
301
* Data validation objects. Indexed by cell coordinate, e.g. 'A1'
305
private $_dataValidationCollection = array();
310
* @var PHPExcel_Style_Color
319
private $_dirty = true;
326
private $_hash = null;
329
* Create a new worksheet
331
* @param PHPExcel $pParent
332
* @param string $pTitle
334
public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
336
// Set parent and title
337
$this->_parent = $pParent;
338
$this->setTitle($pTitle, FALSE);
339
$this->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VISIBLE);
341
$this->_cellCollection = PHPExcel_CachedObjectStorageFactory::getInstance($this);
344
$this->_pageSetup = new PHPExcel_Worksheet_PageSetup();
347
$this->_pageMargins = new PHPExcel_Worksheet_PageMargins();
349
// Set page header/footer
350
$this->_headerFooter = new PHPExcel_Worksheet_HeaderFooter();
353
$this->_sheetView = new PHPExcel_Worksheet_SheetView();
355
// Drawing collection
356
$this->_drawingCollection = new ArrayObject();
359
$this->_chartCollection = new ArrayObject();
362
$this->_protection = new PHPExcel_Worksheet_Protection();
364
// Default row dimension
365
$this->_defaultRowDimension = new PHPExcel_Worksheet_RowDimension(NULL);
367
// Default column dimension
368
$this->_defaultColumnDimension = new PHPExcel_Worksheet_ColumnDimension(NULL);
370
$this->_autoFilter = new PHPExcel_Worksheet_AutoFilter(NULL, $this);
375
* Disconnect all cells from this PHPExcel_Worksheet object,
376
* typically so that the worksheet object can be unset
379
public function disconnectCells() {
380
$this->_cellCollection->unsetWorksheetCells();
381
$this->_cellCollection = null;
383
// detach ourself from the workbook, so that it can then delete this worksheet successfully
384
$this->_parent = null;
388
* Return the cache controller for the cell collection
390
* @return PHPExcel_CachedObjectStorage_xxx
392
public function getCellCacheController() {
393
return $this->_cellCollection;
394
} // function getCellCacheController()
398
* Get array of invalid characters for sheet title
402
public static function getInvalidCharacters()
404
return self::$_invalidCharacters;
408
* Check sheet title for valid Excel syntax
410
* @param string $pValue The string to check
411
* @return string The valid string
414
private static function _checkSheetTitle($pValue)
416
// Some of the printable ASCII characters are invalid: * : / \ ? [ ]
417
if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
418
throw new Exception('Invalid character found in sheet title');
421
// Maximum 31 characters allowed for sheet title
422
if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) {
423
throw new Exception('Maximum 31 characters allowed in sheet title.');
430
* Get collection of cells
432
* @param boolean $pSorted Also sort the cell collection?
433
* @return PHPExcel_Cell[]
435
public function getCellCollection($pSorted = true)
438
// Re-order cell collection
439
return $this->sortCellCollection();
441
if ($this->_cellCollection !== NULL) {
442
return $this->_cellCollection->getCellList();
448
* Sort collection of cells
450
* @return PHPExcel_Worksheet
452
public function sortCellCollection()
454
if ($this->_cellCollection !== NULL) {
455
return $this->_cellCollection->getSortedCellList();
461
* Get collection of row dimensions
463
* @return PHPExcel_Worksheet_RowDimension[]
465
public function getRowDimensions()
467
return $this->_rowDimensions;
471
* Get default row dimension
473
* @return PHPExcel_Worksheet_RowDimension
475
public function getDefaultRowDimension()
477
return $this->_defaultRowDimension;
481
* Get collection of column dimensions
483
* @return PHPExcel_Worksheet_ColumnDimension[]
485
public function getColumnDimensions()
487
return $this->_columnDimensions;
491
* Get default column dimension
493
* @return PHPExcel_Worksheet_ColumnDimension
495
public function getDefaultColumnDimension()
497
return $this->_defaultColumnDimension;
501
* Get collection of drawings
503
* @return PHPExcel_Worksheet_BaseDrawing[]
505
public function getDrawingCollection()
507
return $this->_drawingCollection;
511
* Get collection of charts
513
* @return PHPExcel_Chart[]
515
public function getChartCollection()
517
return $this->_chartCollection;
523
* @param PHPExcel_Chart $pChart
524
* @param int|null $iChartIndex Index where chart should go (0,1,..., or null for last)
525
* @return PHPExcel_Chart
528
public function addChart(PHPExcel_Chart $pChart = null, $iChartIndex = null)
530
$pChart->setWorksheet($this);
531
if (is_null($iChartIndex)) {
532
$this->_chartCollection[] = $pChart;
534
// Insert the chart at the requested index
535
array_splice($this->_chartCollection, $iChartIndex, 0, array($pChart));
542
* Return the count of charts on this worksheet
544
* @return int The number of charts
547
public function getChartCount()
549
return count($this->_chartCollection);
553
* Get a chart by its index position
555
* @param string $index Chart index position
556
* @return false|PHPExcel_Chart
559
public function getChartByIndex($index = null)
561
$chartCount = count($this->_chartCollection);
562
if ($chartCount == 0) {
565
if (is_null($index)) {
566
$index = --$chartCount;
568
if (!isset($this->_chartCollection[$index])) {
572
return $this->_chartCollection[$index];
576
* Return an array of the names of charts on this worksheet
578
* @return string[] The names of charts
581
public function getChartNames()
583
$chartNames = array();
584
foreach($this->_chartCollection as $chart) {
585
$chartNames[] = $chart->getName();
591
* Get a chart by name
593
* @param string $chartName Chart name
594
* @return false|PHPExcel_Chart
597
public function getChartByName($chartName = '')
599
$chartCount = count($this->_chartCollection);
600
if ($chartCount == 0) {
603
foreach($this->_chartCollection as $index => $chart) {
604
if ($chart->getName() == $chartName) {
605
return $this->_chartCollection[$index];
612
* Refresh column dimensions
614
* @return PHPExcel_Worksheet
616
public function refreshColumnDimensions()
618
$currentColumnDimensions = $this->getColumnDimensions();
619
$newColumnDimensions = array();
621
foreach ($currentColumnDimensions as $objColumnDimension) {
622
$newColumnDimensions[$objColumnDimension->getColumnIndex()] = $objColumnDimension;
625
$this->_columnDimensions = $newColumnDimensions;
631
* Refresh row dimensions
633
* @return PHPExcel_Worksheet
635
public function refreshRowDimensions()
637
$currentRowDimensions = $this->getRowDimensions();
638
$newRowDimensions = array();
640
foreach ($currentRowDimensions as $objRowDimension) {
641
$newRowDimensions[$objRowDimension->getRowIndex()] = $objRowDimension;
644
$this->_rowDimensions = $newRowDimensions;
650
* Calculate worksheet dimension
652
* @return string String containing the dimension of this worksheet
654
public function calculateWorksheetDimension()
657
return 'A1' . ':' . $this->getHighestColumn() . $this->getHighestRow();
661
* Calculate worksheet data dimension
663
* @return string String containing the dimension of this worksheet that actually contain data
665
public function calculateWorksheetDataDimension()
668
return 'A1' . ':' . $this->getHighestDataColumn() . $this->getHighestDataRow();
672
* Calculate widths for auto-size columns
674
* @param boolean $calculateMergeCells Calculate merge cell width
675
* @return PHPExcel_Worksheet;
677
public function calculateColumnWidths($calculateMergeCells = false)
679
// initialize $autoSizes array
680
$autoSizes = array();
681
foreach ($this->getColumnDimensions() as $colDimension) {
682
if ($colDimension->getAutoSize()) {
683
$autoSizes[$colDimension->getColumnIndex()] = -1;
687
// There is only something to do if there are some auto-size columns
688
if (!empty($autoSizes)) {
690
// build list of cells references that participate in a merge
691
$isMergeCell = array();
692
foreach ($this->getMergeCells() as $cells) {
693
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
694
$isMergeCell[$cellReference] = true;
698
// loop through all cells in the worksheet
699
foreach ($this->getCellCollection(false) as $cellID) {
700
$cell = $this->getCell($cellID);
701
if (isset($autoSizes[$cell->getColumn()])) {
702
// Determine width if cell does not participate in a merge
703
if (!isset($isMergeCell[$cell->getCoordinate()])) {
705
$cellValue = $cell->getCalculatedValue();
707
// To formatted string
708
$cellValue = PHPExcel_Style_NumberFormat::toFormattedString($cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
710
$autoSizes[$cell->getColumn()] = max(
711
(float)$autoSizes[$cell->getColumn()],
712
(float)PHPExcel_Shared_Font::calculateColumnWidth(
713
$this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
715
$this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
716
$this->getDefaultStyle()->getFont()
723
// adjust column widths
724
foreach ($autoSizes as $columnIndex => $width) {
725
if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth();
726
$this->getColumnDimension($columnIndex)->setWidth($width);
738
public function getParent() {
739
return $this->_parent;
745
* @param PHPExcel $parent
746
* @return PHPExcel_Worksheet
748
public function rebindParent(PHPExcel $parent) {
749
$namedRanges = $this->_parent->getNamedRanges();
750
foreach ($namedRanges as $namedRange) {
751
$parent->addNamedRange($namedRange);
754
$this->_parent->removeSheetByIndex(
755
$this->_parent->getIndex($this)
757
$this->_parent = $parent;
767
public function getTitle()
769
return $this->_title;
775
* @param string $pValue String containing the dimension of this worksheet
776
* @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should
777
* be updated to reflect the new sheet name.
778
* This should be left as the default true, unless you are
779
* certain that no formula cells on any worksheet contain
780
* references to this worksheet
781
* @return PHPExcel_Worksheet
783
public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
785
// Is this a 'rename' or not?
786
if ($this->getTitle() == $pValue) {
791
self::_checkSheetTitle($pValue);
794
$oldTitle = $this->getTitle();
796
if ($this->getParent()) {
797
// Is there already such sheet name?
798
if ($this->getParent()->sheetNameExists($pValue)) {
799
// Use name, but append with lowest possible integer
801
if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
802
$pValue = PHPExcel_Shared_String::Substring($pValue,0,29);
805
while ($this->getParent()->sheetNameExists($pValue . ' ' . $i)) {
808
if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
809
$pValue = PHPExcel_Shared_String::Substring($pValue,0,28);
811
} elseif ($i == 100) {
812
if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
813
$pValue = PHPExcel_Shared_String::Substring($pValue,0,27);
818
$altTitle = $pValue . ' ' . $i;
819
return $this->setTitle($altTitle,$updateFormulaCellReferences);
824
$this->_title = $pValue;
825
$this->_dirty = true;
827
if ($this->getParent()) {
829
$newTitle = $this->getTitle();
830
if ($updateFormulaCellReferences)
831
PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->getParent(), $oldTitle, $newTitle);
840
* @return string Sheet state (visible, hidden, veryHidden)
842
public function getSheetState() {
843
return $this->_sheetState;
849
* @param string $value Sheet state (visible, hidden, veryHidden)
850
* @return PHPExcel_Worksheet
852
public function setSheetState($value = PHPExcel_Worksheet::SHEETSTATE_VISIBLE) {
853
$this->_sheetState = $value;
860
* @return PHPExcel_Worksheet_PageSetup
862
public function getPageSetup()
864
return $this->_pageSetup;
870
* @param PHPExcel_Worksheet_PageSetup $pValue
871
* @return PHPExcel_Worksheet
873
public function setPageSetup(PHPExcel_Worksheet_PageSetup $pValue)
875
$this->_pageSetup = $pValue;
882
* @return PHPExcel_Worksheet_PageMargins
884
public function getPageMargins()
886
return $this->_pageMargins;
892
* @param PHPExcel_Worksheet_PageMargins $pValue
893
* @return PHPExcel_Worksheet
895
public function setPageMargins(PHPExcel_Worksheet_PageMargins $pValue)
897
$this->_pageMargins = $pValue;
902
* Get page header/footer
904
* @return PHPExcel_Worksheet_HeaderFooter
906
public function getHeaderFooter()
908
return $this->_headerFooter;
912
* Set page header/footer
914
* @param PHPExcel_Worksheet_HeaderFooter $pValue
915
* @return PHPExcel_Worksheet
917
public function setHeaderFooter(PHPExcel_Worksheet_HeaderFooter $pValue)
919
$this->_headerFooter = $pValue;
926
* @return PHPExcel_Worksheet_HeaderFooter
928
public function getSheetView()
930
return $this->_sheetView;
936
* @param PHPExcel_Worksheet_SheetView $pValue
937
* @return PHPExcel_Worksheet
939
public function setSheetView(PHPExcel_Worksheet_SheetView $pValue)
941
$this->_sheetView = $pValue;
948
* @return PHPExcel_Worksheet_Protection
950
public function getProtection()
952
return $this->_protection;
958
* @param PHPExcel_Worksheet_Protection $pValue
959
* @return PHPExcel_Worksheet
961
public function setProtection(PHPExcel_Worksheet_Protection $pValue)
963
$this->_protection = $pValue;
964
$this->_dirty = true;
970
* Get highest worksheet column
972
* @return string Highest column name
974
public function getHighestColumn()
976
return $this->_cachedHighestColumn;
980
* Get highest worksheet column that contains data
982
* @return string Highest column name that contains data
984
public function getHighestDataColumn()
986
return $this->_cellCollection->getHighestColumn();
990
* Get highest worksheet row
992
* @return int Highest row number
994
public function getHighestRow()
996
return $this->_cachedHighestRow;
1000
* Get highest worksheet row that contains data
1002
* @return string Highest row number that contains data
1004
public function getHighestDataRow()
1006
return $this->_cellCollection->getHighestRow();
1010
* Get highest worksheet column and highest row that have cell records
1012
* @return array Highest column name and highest row number
1014
public function getHighestRowAndColumn()
1016
return $this->_cellCollection->getHighestRowAndColumn();
1022
* @param string $pCoordinate Coordinate of the cell
1023
* @param mixed $pValue Value of the cell
1024
* @param bool $returnCell Return the worksheet (false, default) or the cell (true)
1025
* @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
1027
public function setCellValue($pCoordinate = 'A1', $pValue = null, $returnCell = false)
1029
$cell = $this->getCell($pCoordinate)->setValue($pValue);
1030
return ($returnCell) ? $cell : $this;
1034
* Set a cell value by using numeric cell coordinates
1036
* @param string $pColumn Numeric column coordinate of the cell
1037
* @param string $pRow Numeric row coordinate of the cell
1038
* @param mixed $pValue Value of the cell
1039
* @param bool $returnCell Return the worksheet (false, default) or the cell (true)
1040
* @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
1042
public function setCellValueByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $returnCell = false)
1044
$cell = $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow)->setValue($pValue);
1045
return ($returnCell) ? $cell : $this;
1051
* @param string $pCoordinate Coordinate of the cell
1052
* @param mixed $pValue Value of the cell
1053
* @param string $pDataType Explicit data type
1054
* @param bool $returnCell Return the worksheet (false, default) or the cell (true)
1055
* @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
1057
public function setCellValueExplicit($pCoordinate = 'A1', $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
1060
$cell = $this->getCell($pCoordinate)->setValueExplicit($pValue, $pDataType);
1061
return ($returnCell) ? $cell : $this;
1065
* Set a cell value by using numeric cell coordinates
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
* @param bool $returnCell Return the worksheet (false, default) or the cell (true)
1072
* @return PHPExcel_Worksheet|PHPExcel_Cell Depending on the last parameter being specified
1074
public function setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $returnCell = false)
1076
$cell = $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow)->setValueExplicit($pValue, $pDataType);
1077
return ($returnCell) ? $cell : $this;
1081
* Get cell at a specific coordinate
1083
* @param string $pCoordinate Coordinate of the cell
1085
* @return PHPExcel_Cell Cell that was found
1087
public function getCell($pCoordinate = 'A1')
1089
// Check cell collection
1090
if ($this->_cellCollection->isDataSet($pCoordinate)) {
1091
return $this->_cellCollection->getCacheData($pCoordinate);
1094
// Worksheet reference?
1095
if (strpos($pCoordinate, '!') !== false) {
1096
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
1097
return $this->getParent()->getSheetByName($worksheetReference[0])->getCell($worksheetReference[1]);
1101
if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
1102
(preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
1103
$namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
1104
if ($namedRange !== NULL) {
1105
$pCoordinate = $namedRange->getRange();
1106
return $namedRange->getWorksheet()->getCell($pCoordinate);
1110
// Uppercase coordinate
1111
$pCoordinate = strtoupper($pCoordinate);
1113
if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
1114
throw new Exception('Cell coordinate can not be a range of cells.');
1115
} elseif (strpos($pCoordinate,'$') !== false) {
1116
throw new Exception('Cell coordinate must not be absolute.');
1118
// Create new cell object
1121
$aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
1123
$cell = $this->_cellCollection->addCacheData($pCoordinate,new PHPExcel_Cell($aCoordinates[0], $aCoordinates[1], null, PHPExcel_Cell_DataType::TYPE_NULL, $this));
1124
$this->_cellCollectionIsSorted = false;
1126
if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($aCoordinates[0]))
1127
$this->_cachedHighestColumn = $aCoordinates[0];
1129
$this->_cachedHighestRow = max($this->_cachedHighestRow,$aCoordinates[1]);
1131
// Cell needs appropriate xfIndex
1132
$rowDimensions = $this->getRowDimensions();
1133
$columnDimensions = $this->getColumnDimensions();
1135
if ( isset($rowDimensions[$aCoordinates[1]]) && $rowDimensions[$aCoordinates[1]]->getXfIndex() !== null ) {
1136
// then there is a row dimension with explicit style, assign it to the cell
1137
$cell->setXfIndex($rowDimensions[$aCoordinates[1]]->getXfIndex());
1138
} else if ( isset($columnDimensions[$aCoordinates[0]]) ) {
1139
// then there is a column dimension, assign it to the cell
1140
$cell->setXfIndex($columnDimensions[$aCoordinates[0]]->getXfIndex());
1142
// set to default index
1143
$cell->setXfIndex(0);
1151
* Get cell at a specific coordinate by using numeric cell coordinates
1153
* @param string $pColumn Numeric column coordinate of the cell
1154
* @param string $pRow Numeric row coordinate of the cell
1155
* @return PHPExcel_Cell Cell that was found
1157
public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
1159
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
1160
$coordinate = $columnLetter . $pRow;
1162
if (!$this->_cellCollection->isDataSet($coordinate)) {
1163
$cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType::TYPE_NULL, $this));
1164
$this->_cellCollectionIsSorted = false;
1166
if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
1167
$this->_cachedHighestColumn = $columnLetter;
1169
$this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
1174
return $this->_cellCollection->getCacheData($coordinate);
1178
* Cell at a specific coordinate exists?
1180
* @param string $pCoordinate Coordinate of the cell
1184
public function cellExists($pCoordinate = 'A1')
1186
// Worksheet reference?
1187
if (strpos($pCoordinate, '!') !== false) {
1188
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
1189
return $this->getParent()->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
1193
if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
1194
(preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
1195
$namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
1196
if ($namedRange !== NULL) {
1197
$pCoordinate = $namedRange->getRange();
1198
if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
1199
if (!$namedRange->getLocalOnly()) {
1200
return $namedRange->getWorksheet()->cellExists($pCoordinate);
1202
throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
1208
// Uppercase coordinate
1209
$pCoordinate = strtoupper($pCoordinate);
1211
if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
1212
throw new Exception('Cell coordinate can not be a range of cells.');
1213
} elseif (strpos($pCoordinate,'$') !== false) {
1214
throw new Exception('Cell coordinate must not be absolute.');
1217
$aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
1220
return $this->_cellCollection->isDataSet($pCoordinate);
1225
* Cell at a specific coordinate by using numeric cell coordinates exists?
1227
* @param string $pColumn Numeric column coordinate of the cell
1228
* @param string $pRow Numeric row coordinate of the cell
1231
public function cellExistsByColumnAndRow($pColumn = 0, $pRow = 1)
1233
return $this->cellExists(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1237
* Get row dimension at a specific row
1239
* @param int $pRow Numeric index of the row
1240
* @return PHPExcel_Worksheet_RowDimension
1242
public function getRowDimension($pRow = 1)
1247
// Get row dimension
1248
if (!isset($this->_rowDimensions[$pRow])) {
1249
$this->_rowDimensions[$pRow] = new PHPExcel_Worksheet_RowDimension($pRow);
1251
$this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);
1253
return $this->_rowDimensions[$pRow];
1257
* Get column dimension at a specific column
1259
* @param string $pColumn String index of the column
1260
* @return PHPExcel_Worksheet_ColumnDimension
1262
public function getColumnDimension($pColumn = 'A')
1264
// Uppercase coordinate
1265
$pColumn = strtoupper($pColumn);
1268
if (!isset($this->_columnDimensions[$pColumn])) {
1269
$this->_columnDimensions[$pColumn] = new PHPExcel_Worksheet_ColumnDimension($pColumn);
1271
if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < PHPExcel_Cell::columnIndexFromString($pColumn))
1272
$this->_cachedHighestColumn = $pColumn;
1274
return $this->_columnDimensions[$pColumn];
1278
* Get column dimension at a specific column by using numeric cell coordinates
1280
* @param string $pColumn Numeric column coordinate of the cell
1281
* @return PHPExcel_Worksheet_ColumnDimension
1283
public function getColumnDimensionByColumn($pColumn = 0)
1285
return $this->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($pColumn));
1291
* @return PHPExcel_Style[]
1293
public function getStyles()
1295
return $this->_styles;
1299
* Get default style of workbork.
1302
* @return PHPExcel_Style
1305
public function getDefaultStyle()
1307
return $this->_parent->getDefaultStyle();
1311
* Set default style - should only be used by PHPExcel_IReader implementations!
1314
* @param PHPExcel_Style $pValue
1316
* @return PHPExcel_Worksheet
1318
public function setDefaultStyle(PHPExcel_Style $pValue)
1320
$this->_parent->getDefaultStyle()->applyFromArray(array(
1322
'name' => $pValue->getFont()->getName(),
1323
'size' => $pValue->getFont()->getSize(),
1330
* Get style for cell
1332
* @param string $pCellCoordinate Cell coordinate to get style for
1333
* @return PHPExcel_Style
1336
public function getStyle($pCellCoordinate = 'A1')
1338
// set this sheet as active
1339
$this->_parent->setActiveSheetIndex($this->_parent->getIndex($this));
1341
// set cell coordinate as active
1342
$this->setSelectedCells($pCellCoordinate);
1344
return $this->_parent->getCellXfSupervisor();
1348
* Get conditional styles for a cell
1350
* @param string $pCoordinate
1351
* @return PHPExcel_Style_Conditional[]
1353
public function getConditionalStyles($pCoordinate = 'A1')
1355
if (!isset($this->_conditionalStylesCollection[$pCoordinate])) {
1356
$this->_conditionalStylesCollection[$pCoordinate] = array();
1358
return $this->_conditionalStylesCollection[$pCoordinate];
1362
* Do conditional styles exist for this cell?
1364
* @param string $pCoordinate
1367
public function conditionalStylesExists($pCoordinate = 'A1')
1369
if (isset($this->_conditionalStylesCollection[$pCoordinate])) {
1376
* Removes conditional styles for a cell
1378
* @param string $pCoordinate
1379
* @return PHPExcel_Worksheet
1381
public function removeConditionalStyles($pCoordinate = 'A1')
1383
unset($this->_conditionalStylesCollection[$pCoordinate]);
1388
* Get collection of conditional styles
1392
public function getConditionalStylesCollection()
1394
return $this->_conditionalStylesCollection;
1398
* Set conditional styles
1400
* @param $pCoordinate string E.g. 'A1'
1401
* @param $pValue PHPExcel_Style_Conditional[]
1402
* @return PHPExcel_Worksheet
1404
public function setConditionalStyles($pCoordinate = 'A1', $pValue)
1406
$this->_conditionalStylesCollection[$pCoordinate] = $pValue;
1411
* Get style for cell by using numeric cell coordinates
1413
* @param int $pColumn Numeric column coordinate of the cell
1414
* @param int $pRow Numeric row coordinate of the cell
1415
* @return PHPExcel_Style
1417
public function getStyleByColumnAndRow($pColumn = 0, $pRow = 1)
1419
return $this->getStyle(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1423
* Set shared cell style to a range of cells
1425
* Please note that this will overwrite existing cell styles for cells in range!
1428
* @param PHPExcel_Style $pSharedCellStyle Cell style to share
1429
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1431
* @return PHPExcel_Worksheet
1433
public function setSharedStyle(PHPExcel_Style $pSharedCellStyle = null, $pRange = '')
1435
$this->duplicateStyle($pSharedCellStyle, $pRange);
1440
* Duplicate cell style to a range of cells
1442
* Please note that this will overwrite existing cell styles for cells in range!
1444
* @param PHPExcel_Style $pCellStyle Cell style to duplicate
1445
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1447
* @return PHPExcel_Worksheet
1449
public function duplicateStyle(PHPExcel_Style $pCellStyle = null, $pRange = '')
1451
// make sure we have a real style and not supervisor
1452
$style = $pCellStyle->getIsSupervisor() ? $pCellStyle->getSharedComponent() : $pCellStyle;
1454
// Add the style to the workbook if necessary
1455
$workbook = $this->_parent;
1456
if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
1457
// there is already such cell Xf in our collection
1458
$xfIndex = $existingStyle->getIndex();
1460
// we don't have such a cell Xf, need to add
1461
$workbook->addCellXf($pCellStyle);
1462
$xfIndex = $pCellStyle->getIndex();
1465
// Uppercase coordinate
1466
$pRange = strtoupper($pRange);
1468
// Is it a cell range or a single cell?
1471
if (strpos($pRange, ':') === false) {
1475
list($rangeA, $rangeB) = explode(':', $pRange);
1478
// Calculate range outer borders
1479
$rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
1480
$rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
1482
// Translate column into index
1483
$rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]) - 1;
1484
$rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]) - 1;
1486
// Make sure we can loop upwards on rows and columns
1487
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1489
$rangeStart = $rangeEnd;
1493
// Loop through cells and apply styles
1494
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1495
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1496
$this->getCell(PHPExcel_Cell::stringFromColumnIndex($col) . $row)->setXfIndex($xfIndex);
1504
* Duplicate conditional style to a range of cells
1506
* Please note that this will overwrite existing cell styles for cells in range!
1508
* @param array of PHPExcel_Style_Conditional $pCellStyle Cell style to duplicate
1509
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1511
* @return PHPExcel_Worksheet
1513
public function duplicateConditionalStyle(array $pCellStyle = null, $pRange = '')
1515
foreach($pCellStyle as $cellStyle) {
1516
if (!is_a($cellStyle,'PHPExcel_Style_Conditional')) {
1517
throw new Exception('Style is not a conditional style');
1521
// Uppercase coordinate
1522
$pRange = strtoupper($pRange);
1524
// Is it a cell range or a single cell?
1527
if (strpos($pRange, ':') === false) {
1531
list($rangeA, $rangeB) = explode(':', $pRange);
1534
// Calculate range outer borders
1535
$rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
1536
$rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
1538
// Translate column into index
1539
$rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]) - 1;
1540
$rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]) - 1;
1542
// Make sure we can loop upwards on rows and columns
1543
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
1545
$rangeStart = $rangeEnd;
1549
// Loop through cells and apply styles
1550
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
1551
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
1552
$this->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($col) . $row, $pCellStyle);
1560
* Duplicate cell style array to a range of cells
1562
* Please note that this will overwrite existing cell styles for cells in range,
1563
* if they are in the styles array. For example, if you decide to set a range of
1564
* cells to font bold, only include font bold in the styles array.
1567
* @param array $pStyles Array containing style information
1568
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
1569
* @param boolean $pAdvanced Advanced mode for setting borders.
1571
* @return PHPExcel_Worksheet
1573
public function duplicateStyleArray($pStyles = null, $pRange = '', $pAdvanced = true)
1575
$this->getStyle($pRange)->applyFromArray($pStyles, $pAdvanced);
1580
* Set break on a cell
1582
* @param string $pCell Cell coordinate (e.g. A1)
1583
* @param int $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
1585
* @return PHPExcel_Worksheet
1587
public function setBreak($pCell = 'A1', $pBreak = PHPExcel_Worksheet::BREAK_NONE)
1589
// Uppercase coordinate
1590
$pCell = strtoupper($pCell);
1593
$this->_breaks[$pCell] = $pBreak;
1595
throw new Exception('No cell coordinate specified.');
1602
* Set break on a cell by using numeric cell coordinates
1604
* @param integer $pColumn Numeric column coordinate of the cell
1605
* @param integer $pRow Numeric row coordinate of the cell
1606
* @param integer $pBreak Break type (type of PHPExcel_Worksheet::BREAK_*)
1608
* @return PHPExcel_Worksheet
1610
public function setBreakByColumnAndRow($pColumn = 0, $pRow = 1, $pBreak = PHPExcel_Worksheet::BREAK_NONE)
1612
return $this->setBreak(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow, $pBreak);
1620
public function getBreaks()
1622
return $this->_breaks;
1626
* Set merge on a cell range
1628
* @param string $pRange Cell range (e.g. A1:E1)
1630
* @return PHPExcel_Worksheet
1632
public function mergeCells($pRange = 'A1:A1')
1634
// Uppercase coordinate
1635
$pRange = strtoupper($pRange);
1637
if (strpos($pRange,':') !== false) {
1638
$this->_mergeCells[$pRange] = $pRange;
1640
// make sure cells are created
1642
// get the cells in the range
1643
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
1645
// create upper left cell if it does not already exist
1646
$upperLeft = $aReferences[0];
1647
if (!$this->cellExists($upperLeft)) {
1648
$this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
1651
// create or blank out the rest of the cells in the range
1652
$count = count($aReferences);
1653
for ($i = 1; $i < $count; $i++) {
1654
$this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
1658
throw new Exception('Merge must be set on a range of cells.');
1665
* Set merge on a cell range by using numeric cell coordinates
1667
* @param int $pColumn1 Numeric column coordinate of the first cell
1668
* @param int $pRow1 Numeric row coordinate of the first cell
1669
* @param int $pColumn2 Numeric column coordinate of the last cell
1670
* @param int $pRow2 Numeric row coordinate of the last cell
1672
* @return PHPExcel_Worksheet
1674
public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1676
$cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1677
return $this->mergeCells($cellRange);
1681
* Remove merge on a cell range
1683
* @param string $pRange Cell range (e.g. A1:E1)
1685
* @return PHPExcel_Worksheet
1687
public function unmergeCells($pRange = 'A1:A1')
1689
// Uppercase coordinate
1690
$pRange = strtoupper($pRange);
1692
if (strpos($pRange,':') !== false) {
1693
if (isset($this->_mergeCells[$pRange])) {
1694
unset($this->_mergeCells[$pRange]);
1696
throw new Exception('Cell range ' . $pRange . ' not known as merged.');
1699
throw new Exception('Merge can only be removed from a range of cells.');
1706
* Remove merge on a cell range by using numeric cell coordinates
1708
* @param int $pColumn1 Numeric column coordinate of the first cell
1709
* @param int $pRow1 Numeric row coordinate of the first cell
1710
* @param int $pColumn2 Numeric column coordinate of the last cell
1711
* @param int $pRow2 Numeric row coordinate of the last cell
1713
* @return PHPExcel_Worksheet
1715
public function unmergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1717
$cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1718
return $this->unmergeCells($cellRange);
1722
* Get merge cells array.
1726
public function getMergeCells()
1728
return $this->_mergeCells;
1732
* Set merge cells array for the entire sheet. Use instead mergeCells() to merge
1733
* a single cell range.
1737
public function setMergeCells($pValue = array())
1739
$this->_mergeCells = $pValue;
1745
* Set protection on a cell range
1747
* @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1748
* @param string $pPassword Password to unlock the protection
1749
* @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1751
* @return PHPExcel_Worksheet
1753
public function protectCells($pRange = 'A1', $pPassword = '', $pAlreadyHashed = false)
1755
// Uppercase coordinate
1756
$pRange = strtoupper($pRange);
1758
if (!$pAlreadyHashed) {
1759
$pPassword = PHPExcel_Shared_PasswordHasher::hashPassword($pPassword);
1761
$this->_protectedCells[$pRange] = $pPassword;
1767
* Set protection on a cell range by using numeric cell coordinates
1769
* @param int $pColumn1 Numeric column coordinate of the first cell
1770
* @param int $pRow1 Numeric row coordinate of the first cell
1771
* @param int $pColumn2 Numeric column coordinate of the last cell
1772
* @param int $pRow2 Numeric row coordinate of the last cell
1773
* @param string $pPassword Password to unlock the protection
1774
* @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1776
* @return PHPExcel_Worksheet
1778
public function protectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1780
$cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1781
return $this->protectCells($cellRange, $pPassword, $pAlreadyHashed);
1785
* Remove protection on a cell range
1787
* @param string $pRange Cell (e.g. A1) or cell range (e.g. A1:E1)
1789
* @return PHPExcel_Worksheet
1791
public function unprotectCells($pRange = 'A1')
1793
// Uppercase coordinate
1794
$pRange = strtoupper($pRange);
1796
if (isset($this->_protectedCells[$pRange])) {
1797
unset($this->_protectedCells[$pRange]);
1799
throw new Exception('Cell range ' . $pRange . ' not known as protected.');
1805
* Remove protection on a cell range by using numeric cell coordinates
1807
* @param int $pColumn1 Numeric column coordinate of the first cell
1808
* @param int $pRow1 Numeric row coordinate of the first cell
1809
* @param int $pColumn2 Numeric column coordinate of the last cell
1810
* @param int $pRow2 Numeric row coordinate of the last cell
1811
* @param string $pPassword Password to unlock the protection
1812
* @param boolean $pAlreadyHashed If the password has already been hashed, set this to true
1814
* @return PHPExcel_Worksheet
1816
public function unprotectCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1, $pPassword = '', $pAlreadyHashed = false)
1818
$cellRange = PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1 . ':' . PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2;
1819
return $this->unprotectCells($cellRange, $pPassword, $pAlreadyHashed);
1823
* Get protected cells
1827
public function getProtectedCells()
1829
return $this->_protectedCells;
1835
* @return PHPExcel_Worksheet_AutoFilter
1837
public function getAutoFilter()
1839
return $this->_autoFilter;
1845
* @param PHPExcel_Worksheet_AutoFilter|string $pValue
1846
* A simple string containing a Cell range like 'A1:E10' is permitted for backward compatibility
1848
* @return PHPExcel_Worksheet
1850
public function setAutoFilter($pValue)
1852
if (is_string($pValue)) {
1853
$this->_autoFilter->setRange($pValue);
1854
} elseif(is_object($pValue) && ($pValue instanceof PHPExcel_Worksheet_AutoFilter)) {
1855
$this->_autoFilter = $pValue;
1861
* Set Autofilter Range by using numeric cell coordinates
1863
* @param int $pColumn1 Numeric column coordinate of the first cell
1864
* @param int $pRow1 Numeric row coordinate of the first cell
1865
* @param int $pColumn2 Numeric column coordinate of the second cell
1866
* @param int $pRow2 Numeric row coordinate of the second cell
1868
* @return PHPExcel_Worksheet
1870
public function setAutoFilterByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)
1872
return $this->setAutoFilter(
1873
PHPExcel_Cell::stringFromColumnIndex($pColumn1) . $pRow1
1875
PHPExcel_Cell::stringFromColumnIndex($pColumn2) . $pRow2
1882
* @return PHPExcel_Worksheet
1884
public function removeAutoFilter()
1886
$this->_autoFilter->setRange(NULL);
1895
public function getFreezePane()
1897
return $this->_freezePane;
1903
* @param string $pCell Cell (i.e. A2)
1905
* A2 will freeze the rows above cell A2 (i.e row 1)
1906
* B1 will freeze the columns to the left of cell B1 (i.e column A)
1907
* B2 will freeze the rows above and to the left of cell A2
1908
* (i.e row 1 and column A)
1910
* @return PHPExcel_Worksheet
1912
public function freezePane($pCell = '')
1914
// Uppercase coordinate
1915
$pCell = strtoupper($pCell);
1917
if (strpos($pCell,':') === false && strpos($pCell,',') === false) {
1918
$this->_freezePane = $pCell;
1920
throw new Exception('Freeze pane can not be set on a range of cells.');
1926
* Freeze Pane by using numeric cell coordinates
1928
* @param int $pColumn Numeric column coordinate of the cell
1929
* @param int $pRow Numeric row coordinate of the cell
1931
* @return PHPExcel_Worksheet
1933
public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1)
1935
return $this->freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
1941
* @return PHPExcel_Worksheet
1943
public function unfreezePane()
1945
return $this->freezePane('');
1949
* Insert a new row, updating all possible related data
1951
* @param int $pBefore Insert before this one
1952
* @param int $pNumRows Number of rows to insert
1954
* @return PHPExcel_Worksheet
1956
public function insertNewRowBefore($pBefore = 1, $pNumRows = 1) {
1957
if ($pBefore >= 1) {
1958
$objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
1959
$objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);
1961
throw new Exception("Rows can only be inserted before at least row 1.");
1967
* Insert a new column, updating all possible related data
1969
* @param int $pBefore Insert before this one
1970
* @param int $pNumCols Number of columns to insert
1972
* @return PHPExcel_Worksheet
1974
public function insertNewColumnBefore($pBefore = 'A', $pNumCols = 1) {
1975
if (!is_numeric($pBefore)) {
1976
$objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
1977
$objReferenceHelper->insertNewBefore($pBefore . '1', $pNumCols, 0, $this);
1979
throw new Exception("Column references should not be numeric.");
1985
* Insert a new column, updating all possible related data
1987
* @param int $pBefore Insert before this one (numeric column coordinate of the cell)
1988
* @param int $pNumCols Number of columns to insert
1990
* @return PHPExcel_Worksheet
1992
public function insertNewColumnBeforeByIndex($pBefore = 0, $pNumCols = 1) {
1993
if ($pBefore >= 0) {
1994
return $this->insertNewColumnBefore(PHPExcel_Cell::stringFromColumnIndex($pBefore), $pNumCols);
1996
throw new Exception("Columns can only be inserted before at least column A (0).");
2001
* Delete a row, updating all possible related data
2003
* @param int $pRow Remove starting with this one
2004
* @param int $pNumRows Number of rows to remove
2006
* @return PHPExcel_Worksheet
2008
public function removeRow($pRow = 1, $pNumRows = 1) {
2010
$objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2011
$objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this);
2013
throw new Exception("Rows to be deleted should at least start from row 1.");
2019
* Remove a column, updating all possible related data
2021
* @param int $pColumn Remove starting with this one
2022
* @param int $pNumCols Number of columns to remove
2024
* @return PHPExcel_Worksheet
2026
public function removeColumn($pColumn = 'A', $pNumCols = 1) {
2027
if (!is_numeric($pColumn)) {
2028
$pColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($pColumn) - 1 + $pNumCols);
2029
$objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
2030
$objReferenceHelper->insertNewBefore($pColumn . '1', -$pNumCols, 0, $this);
2032
throw new Exception("Column references should not be numeric.");
2038
* Remove a column, updating all possible related data
2040
* @param int $pColumn Remove starting with this one (numeric column coordinate of the cell)
2041
* @param int $pNumCols Number of columns to remove
2043
* @return PHPExcel_Worksheet
2045
public function removeColumnByIndex($pColumn = 0, $pNumCols = 1) {
2046
if ($pColumn >= 0) {
2047
return $this->removeColumn(PHPExcel_Cell::stringFromColumnIndex($pColumn), $pNumCols);
2049
throw new Exception("Columns to be deleted should at least start from column 0");
2058
public function getShowGridlines() {
2059
return $this->_showGridlines;
2063
* Set show gridlines
2065
* @param boolean $pValue Show gridlines (true/false)
2066
* @return PHPExcel_Worksheet
2068
public function setShowGridlines($pValue = false) {
2069
$this->_showGridlines = $pValue;
2078
public function getPrintGridlines() {
2079
return $this->_printGridlines;
2083
* Set print gridlines
2085
* @param boolean $pValue Print gridlines (true/false)
2086
* @return PHPExcel_Worksheet
2088
public function setPrintGridlines($pValue = false) {
2089
$this->_printGridlines = $pValue;
2094
* Show row and column headers?
2098
public function getShowRowColHeaders() {
2099
return $this->_showRowColHeaders;
2103
* Set show row and column headers
2105
* @param boolean $pValue Show row and column headers (true/false)
2106
* @return PHPExcel_Worksheet
2108
public function setShowRowColHeaders($pValue = false) {
2109
$this->_showRowColHeaders = $pValue;
2114
* Show summary below? (Row/Column outlining)
2118
public function getShowSummaryBelow() {
2119
return $this->_showSummaryBelow;
2123
* Set show summary below
2125
* @param boolean $pValue Show summary below (true/false)
2126
* @return PHPExcel_Worksheet
2128
public function setShowSummaryBelow($pValue = true) {
2129
$this->_showSummaryBelow = $pValue;
2134
* Show summary right? (Row/Column outlining)
2138
public function getShowSummaryRight() {
2139
return $this->_showSummaryRight;
2143
* Set show summary right
2145
* @param boolean $pValue Show summary right (true/false)
2146
* @return PHPExcel_Worksheet
2148
public function setShowSummaryRight($pValue = true) {
2149
$this->_showSummaryRight = $pValue;
2156
* @return PHPExcel_Comment[]
2158
public function getComments()
2160
return $this->_comments;
2164
* Set comments array for the entire sheet.
2166
* @param array of PHPExcel_Comment
2167
* @return PHPExcel_Worksheet
2169
public function setComments($pValue = array())
2171
$this->_comments = $pValue;
2177
* Get comment for cell
2179
* @param string $pCellCoordinate Cell coordinate to get comment for
2180
* @return PHPExcel_Comment
2183
public function getComment($pCellCoordinate = 'A1')
2185
// Uppercase coordinate
2186
$pCellCoordinate = strtoupper($pCellCoordinate);
2188
if (strpos($pCellCoordinate,':') !== false || strpos($pCellCoordinate,',') !== false) {
2189
throw new Exception('Cell coordinate string can not be a range of cells.');
2190
} else if (strpos($pCellCoordinate,'$') !== false) {
2191
throw new Exception('Cell coordinate string must not be absolute.');
2192
} else if ($pCellCoordinate == '') {
2193
throw new Exception('Cell coordinate can not be zero-length string.');
2195
// Check if we already have a comment for this cell.
2196
// If not, create a new comment.
2197
if (isset($this->_comments[$pCellCoordinate])) {
2198
return $this->_comments[$pCellCoordinate];
2200
$newComment = new PHPExcel_Comment();
2201
$this->_comments[$pCellCoordinate] = $newComment;
2208
* Get comment for cell by using numeric cell coordinates
2210
* @param int $pColumn Numeric column coordinate of the cell
2211
* @param int $pRow Numeric row coordinate of the cell
2212
* @return PHPExcel_Comment
2214
public function getCommentByColumnAndRow($pColumn = 0, $pRow = 1)
2216
return $this->getComment(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
2225
public function getSelectedCell()
2227
return $this->getSelectedCells();
2233
* @return string Example: 'A1'
2235
public function getActiveCell()
2237
return $this->_activeCell;
2241
* Get selected cells
2245
public function getSelectedCells()
2247
return $this->_selectedCells;
2253
* @param string $pCoordinate Cell (i.e. A1)
2254
* @return PHPExcel_Worksheet
2256
public function setSelectedCell($pCoordinate = 'A1')
2258
return $this->setSelectedCells($pCoordinate);
2262
* Select a range of cells.
2264
* @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
2266
* @return PHPExcel_Worksheet
2268
public function setSelectedCells($pCoordinate = 'A1')
2270
// Uppercase coordinate
2271
$pCoordinate = strtoupper($pCoordinate);
2273
// Convert 'A' to 'A:A'
2274
$pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
2276
// Convert '1' to '1:1'
2277
$pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
2279
// Convert 'A:C' to 'A1:C1048576'
2280
$pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
2282
// Convert '1:3' to 'A1:XFD3'
2283
$pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
2285
if (strpos($pCoordinate,':') !== false || strpos($pCoordinate,',') !== false) {
2286
list($first, ) = PHPExcel_Cell::splitRange($pCoordinate);
2287
$this->_activeCell = $first[0];
2289
$this->_activeCell = $pCoordinate;
2291
$this->_selectedCells = $pCoordinate;
2296
* Selected cell by using numeric cell coordinates
2298
* @param int $pColumn Numeric column coordinate of the cell
2299
* @param int $pRow Numeric row coordinate of the cell
2301
* @return PHPExcel_Worksheet
2303
public function setSelectedCellByColumnAndRow($pColumn = 0, $pRow = 1)
2305
return $this->setSelectedCells(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
2313
public function getRightToLeft() {
2314
return $this->_rightToLeft;
2320
* @param boolean $value Right-to-left true/false
2321
* @return PHPExcel_Worksheet
2323
public function setRightToLeft($value = false) {
2324
$this->_rightToLeft = $value;
2329
* Fill worksheet from values in array
2331
* @param array $source Source array
2332
* @param mixed $nullValue Value in source array that stands for blank cell
2333
* @param string $startCell Insert array starting from this cell address as the top left coordinate
2334
* @param boolean $strictNullComparison Apply strict comparison when testing for null values in the array
2336
* @return PHPExcel_Worksheet
2338
public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false) {
2339
if (is_array($source)) {
2340
// Convert a 1-D array to 2-D (for ease of looping)
2341
if (!is_array(end($source))) {
2342
$source = array($source);
2346
list ($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($startCell);
2348
// Loop through $source
2349
foreach ($source as $rowData) {
2350
$currentColumn = $startColumn;
2351
foreach($rowData as $cellValue) {
2352
if ($strictNullComparison) {
2353
if ($cellValue !== $nullValue) {
2355
$this->getCell($currentColumn . $startRow)->setValue($cellValue);
2358
if ($cellValue != $nullValue) {
2360
$this->getCell($currentColumn . $startRow)->setValue($cellValue);
2368
throw new Exception("Parameter \$source should be an array.");
2374
* Create array from a range of cells
2376
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
2377
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2378
* @param boolean $calculateFormulas Should formulas be calculated?
2379
* @param boolean $formatData Should formatting be applied to cell values?
2380
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2381
* True - Return rows and columns indexed by their actual row and column IDs
2384
public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2386
$returnValue = array();
2388
// Identify the range that we need to extract from the worksheet
2389
list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
2390
$minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
2391
$minRow = $rangeStart[1];
2392
$maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
2393
$maxRow = $rangeEnd[1];
2397
// Loop through rows
2399
for ($row = $minRow; $row <= $maxRow; ++$row) {
2400
$rRef = ($returnCellRef) ? $row : ++$r;
2402
// Loop through columns in the current row
2403
for ($col = $minCol; $col != $maxCol; ++$col) {
2404
$cRef = ($returnCellRef) ? $col : ++$c;
2405
// Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
2406
// so we test and retrieve directly against _cellCollection
2407
if ($this->_cellCollection->isDataSet($col.$row)) {
2409
$cell = $this->_cellCollection->getCacheData($col.$row);
2410
if ($cell->getValue() !== null) {
2411
if ($cell->getValue() instanceof PHPExcel_RichText) {
2412
$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
2414
if ($calculateFormulas) {
2415
$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
2417
$returnValue[$rRef][$cRef] = $cell->getValue();
2422
$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
2423
$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
2426
// Cell holds a NULL
2427
$returnValue[$rRef][$cRef] = $nullValue;
2430
// Cell doesn't exist
2431
$returnValue[$rRef][$cRef] = $nullValue;
2437
return $returnValue;
2442
* Create array from a range of cells
2444
* @param string $pNamedRange Name of the Named Range
2445
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2446
* @param boolean $calculateFormulas Should formulas be calculated?
2447
* @param boolean $formatData Should formatting be applied to cell values?
2448
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2449
* True - Return rows and columns indexed by their actual row and column IDs
2453
public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2454
$namedRange = PHPExcel_NamedRange::resolveRange($pNamedRange, $this);
2455
if ($namedRange !== NULL) {
2456
$pWorkSheet = $namedRange->getWorksheet();
2457
$pCellRange = $namedRange->getRange();
2459
return $pWorkSheet->rangeToArray( $pCellRange,
2460
$nullValue, $calculateFormulas, $formatData, $returnCellRef);
2463
throw new Exception('Named Range '.$pNamedRange.' does not exist.');
2468
* Create array from worksheet
2470
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
2471
* @param boolean $calculateFormulas Should formulas be calculated?
2472
* @param boolean $formatData Should formatting be applied to cell values?
2473
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
2474
* True - Return rows and columns indexed by their actual row and column IDs
2477
public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
2478
// Garbage collect...
2479
$this->garbageCollect();
2481
// Identify the range that we need to extract from the worksheet
2482
$maxCol = $this->getHighestColumn();
2483
$maxRow = $this->getHighestRow();
2485
return $this->rangeToArray( 'A1:'.$maxCol.$maxRow,
2486
$nullValue, $calculateFormulas, $formatData, $returnCellRef);
2492
* @param integer $startRow The row number at which to start iterating
2493
* @return PHPExcel_Worksheet_RowIterator
2495
public function getRowIterator($startRow = 1) {
2496
return new PHPExcel_Worksheet_RowIterator($this,$startRow);
2500
* Run PHPExcel garabage collector.
2502
* @return PHPExcel_Worksheet
2504
public function garbageCollect() {
2505
// Build a reference table from images
2506
// $imageCoordinates = array();
2507
// $iterator = $this->getDrawingCollection()->getIterator();
2508
// while ($iterator->valid()) {
2509
// $imageCoordinates[$iterator->current()->getCoordinates()] = true;
2511
// $iterator->next();
2514
// Lookup highest column and highest row if cells are cleaned
2515
$colRow = $this->_cellCollection->getHighestRowAndColumn();
2516
$highestRow = $colRow['row'];
2517
$highestColumn = PHPExcel_Cell::columnIndexFromString($colRow['column']);
2519
// Loop through column dimensions
2520
foreach ($this->_columnDimensions as $dimension) {
2521
$highestColumn = max($highestColumn,PHPExcel_Cell::columnIndexFromString($dimension->getColumnIndex()));
2524
// Loop through row dimensions
2525
foreach ($this->_rowDimensions as $dimension) {
2526
$highestRow = max($highestRow,$dimension->getRowIndex());
2530
if ($highestColumn < 0) {
2531
$this->_cachedHighestColumn = 'A';
2533
$this->_cachedHighestColumn = PHPExcel_Cell::stringFromColumnIndex(--$highestColumn);
2535
$this->_cachedHighestRow = $highestRow;
2544
* @return string Hash code
2546
public function getHashCode() {
2547
if ($this->_dirty) {
2548
$this->_hash = md5( $this->_title .
2549
$this->_autoFilter .
2550
($this->_protection->isProtectionEnabled() ? 't' : 'f') .
2553
$this->_dirty = false;
2555
return $this->_hash;
2559
* Extract worksheet title from range.
2561
* Example: extractSheetTitle("testSheet!A1") ==> 'A1'
2562
* Example: extractSheetTitle("'testSheet 1'!A1", true) ==> array('testSheet 1', 'A1');
2564
* @param string $pRange Range to extract title from
2565
* @param bool $returnRange Return range? (see example)
2568
public static function extractSheetTitle($pRange, $returnRange = false) {
2569
// Sheet title included?
2570
if (($sep = strpos($pRange, '!')) === false) {
2575
return array( trim(substr($pRange, 0, $sep),"'"),
2576
substr($pRange, $sep + 1)
2580
return substr($pRange, $sep + 1);
2586
* @param string $pCellCoordinate Cell coordinate to get hyperlink for
2588
public function getHyperlink($pCellCoordinate = 'A1')
2590
// return hyperlink if we already have one
2591
if (isset($this->_hyperlinkCollection[$pCellCoordinate])) {
2592
return $this->_hyperlinkCollection[$pCellCoordinate];
2595
// else create hyperlink
2596
$this->_hyperlinkCollection[$pCellCoordinate] = new PHPExcel_Cell_Hyperlink();
2597
return $this->_hyperlinkCollection[$pCellCoordinate];
2603
* @param string $pCellCoordinate Cell coordinate to insert hyperlink
2604
* @param PHPExcel_Cell_Hyperlink $pHyperlink
2605
* @return PHPExcel_Worksheet
2607
public function setHyperlink($pCellCoordinate = 'A1', PHPExcel_Cell_Hyperlink $pHyperlink = null)
2609
if ($pHyperlink === null) {
2610
unset($this->_hyperlinkCollection[$pCellCoordinate]);
2612
$this->_hyperlinkCollection[$pCellCoordinate] = $pHyperlink;
2618
* Hyperlink at a specific coordinate exists?
2620
* @param string $pCoordinate
2623
public function hyperlinkExists($pCoordinate = 'A1')
2625
return isset($this->_hyperlinkCollection[$pCoordinate]);
2629
* Get collection of hyperlinks
2631
* @return PHPExcel_Cell_Hyperlink[]
2633
public function getHyperlinkCollection()
2635
return $this->_hyperlinkCollection;
2639
* Get data validation
2641
* @param string $pCellCoordinate Cell coordinate to get data validation for
2643
public function getDataValidation($pCellCoordinate = 'A1')
2645
// return data validation if we already have one
2646
if (isset($this->_dataValidationCollection[$pCellCoordinate])) {
2647
return $this->_dataValidationCollection[$pCellCoordinate];
2650
// else create data validation
2651
$this->_dataValidationCollection[$pCellCoordinate] = new PHPExcel_Cell_DataValidation();
2652
return $this->_dataValidationCollection[$pCellCoordinate];
2656
* Set data validation
2658
* @param string $pCellCoordinate Cell coordinate to insert data validation
2659
* @param PHPExcel_Cell_DataValidation $pDataValidation
2660
* @return PHPExcel_Worksheet
2662
public function setDataValidation($pCellCoordinate = 'A1', PHPExcel_Cell_DataValidation $pDataValidation = null)
2664
if ($pDataValidation === null) {
2665
unset($this->_dataValidationCollection[$pCellCoordinate]);
2667
$this->_dataValidationCollection[$pCellCoordinate] = $pDataValidation;
2673
* Data validation at a specific coordinate exists?
2675
* @param string $pCoordinate
2678
public function dataValidationExists($pCoordinate = 'A1')
2680
return isset($this->_dataValidationCollection[$pCoordinate]);
2684
* Get collection of data validations
2686
* @return PHPExcel_Cell_DataValidation[]
2688
public function getDataValidationCollection()
2690
return $this->_dataValidationCollection;
2694
* Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet
2696
* @param string $range
2697
* @return string Adjusted range value
2699
public function shrinkRangeToFit($range) {
2700
$maxCol = $this->getHighestColumn();
2701
$maxRow = $this->getHighestRow();
2702
$maxCol = PHPExcel_Cell::columnIndexFromString($maxCol);
2704
$rangeBlocks = explode(' ',$range);
2705
foreach ($rangeBlocks as &$rangeSet) {
2706
$rangeBoundaries = PHPExcel_Cell::getRangeBoundaries($rangeSet);
2708
if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) { $rangeBoundaries[0][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); }
2709
if ($rangeBoundaries[0][1] > $maxRow) { $rangeBoundaries[0][1] = $maxRow; }
2710
if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) { $rangeBoundaries[1][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol); }
2711
if ($rangeBoundaries[1][1] > $maxRow) { $rangeBoundaries[1][1] = $maxRow; }
2712
$rangeSet = $rangeBoundaries[0][0].$rangeBoundaries[0][1].':'.$rangeBoundaries[1][0].$rangeBoundaries[1][1];
2715
$stRange = implode(' ',$rangeBlocks);
2724
* @return PHPExcel_Style_Color
2726
public function getTabColor()
2728
if ($this->_tabColor === NULL)
2729
$this->_tabColor = new PHPExcel_Style_Color();
2731
return $this->_tabColor;
2737
* @return PHPExcel_Worksheet
2739
public function resetTabColor()
2741
$this->_tabColor = null;
2742
unset($this->_tabColor);
2752
public function isTabColorSet()
2754
return ($this->_tabColor !== NULL);
2758
* Copy worksheet (!= clone!)
2760
* @return PHPExcel_Worksheet
2762
public function copy() {
2763
$copied = clone $this;
2769
* Implement PHP __clone to create a deep clone, not just a shallow copy.
2771
public function __clone() {
2772
foreach ($this as $key => $val) {
2773
if ($key == '_parent') {
2777
if (is_object($val) || (is_array($val))) {
2778
if ($key == '_cellCollection') {
2779
$newCollection = clone $this->_cellCollection;
2780
$newCollection->copyCellCollection($this);
2781
$this->_cellCollection = $newCollection;
2782
} elseif ($key == '_drawingCollection') {
2783
$newCollection = clone $this->_drawingCollection;
2784
$this->_drawingCollection = $newCollection;
2785
} elseif (($key == '_autoFilter') && (is_a($this->_autoFilter,'PHPExcel_Worksheet_AutoFilter'))) {
2786
$newAutoFilter = clone $this->_autoFilter;
2787
$this->_autoFilter = $newAutoFilter;
2788
$this->_autoFilter->setParent($this);
2790
$this->{$key} = unserialize(serialize($val));