cellCollection = CachedObjectStorageFactory::getInstance($this); // Set parent and title $this->parent = $pParent; $this->setTitle($pTitle, false); // setTitle can change $pTitle $this->setCodeName($this->getTitle()); $this->setSheetState(Worksheet::SHEETSTATE_VISIBLE); // Set page setup $this->pageSetup = new PageSetup(); // Set sheet view $this->sheetView = new SheetView(); // Default row dimension $this->defaultRowDimension = new RowDimension(null); /* // Set page margins $this->pageMargins = new PHPExcel_Worksheet_PageMargins(); // Set page header/footer $this->headerFooter = new PHPExcel_Worksheet_HeaderFooter(); // Drawing collection $this->drawingCollection = new ArrayObject(); // Chart collection $this->chartCollection = new ArrayObject(); // Protection $this->protection = new PHPExcel_Worksheet_Protection(); */ // Default column dimension $this->defaultColumnDimension = new ColumnDimension(null); //$this->autoFilter = new PHPExcel_Worksheet_AutoFilter(null, $this); } /** * Get collection of cells * * @param boolean $pSorted Also sort the cell collection? * @return PHPExcel_Cell[] */ public function getCellCollection($pSorted = true) { if ($pSorted) { // Re-order cell collection return $this->sortCellCollection(); } if ($this->cellCollection !== null) { return $this->cellCollection->getCellList(); } return array(); } /** * Sort collection of cells * * @return PHPExcel_Worksheet */ public function sortCellCollection() { if ($this->cellCollection !== null) { return $this->cellCollection->getSortedCellList(); } return array(); } /** * Get title * * @return string */ public function getTitle() { return $this->title; } /** * Set title * * @param string $pValue String containing the dimension of this worksheet * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should * be updated to reflect the new sheet name. * This should be left as the default true, unless you are * certain that no formula cells on any worksheet contain * references to this worksheet * @return PHPExcel_Worksheet */ public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true) { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return $this; } // Syntax check self::checkSheetTitle($pValue); // Old title $oldTitle = $this->getTitle(); if ($this->parent) { // Is there already such sheet name? if ($this->parent->sheetNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcelString::CountCharacters($pValue) > 29) { $pValue = PHPExcelString::Substring($pValue, 0, 29); } $i = 1; while ($this->parent->sheetNameExists($pValue . ' ' . $i)) { ++$i; if ($i == 10) { if (PHPExcelString::CountCharacters($pValue) > 28) { $pValue = PHPExcelString::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcelString::CountCharacters($pValue) > 27) { $pValue = PHPExcelString::Substring($pValue, 0, 27); } } } $altTitle = $pValue . ' ' . $i; return $this->setTitle($altTitle, $updateFormulaCellReferences); } } // Set title $this->title = $pValue; $this->dirty = true; if ($this->parent && $this->parent->getCalculationEngine()) { // New title $newTitle = $this->getTitle(); $this->parent->getCalculationEngine() ->renameCalculationCacheForWorksheet($oldTitle, $newTitle); if ($updateFormulaCellReferences) { PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->parent, $oldTitle, $newTitle); } } return $this; } /** * Check sheet title for valid Excel syntax * * @param string $pValue The string to check * @return string The valid string * @throws */ private static function checkSheetTitle($pValue) { // Some of the printable ASCII characters are invalid: * : / \ ? [ ] if (str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) { throw new \Exception('Invalid character found in sheet title'); } // Maximum 31 characters allowed for sheet title if (PHPExcelString::CountCharacters($pValue) > 31) { throw new \Exception('Maximum 31 characters allowed in sheet title.'); } return $pValue; } /** * Get array of invalid characters for sheet title * * @return array */ public static function getInvalidCharacters() { return self::$invalidCharacters; } /** * Get parent * * @return PHPExcel */ public function getParent() { return $this->parent; } /** * Re-bind parent * * @param PHPExcel $parent * @return Worksheet */ public function rebindParent(PHPExcel $parent) { if ($this->parent !== null) { $namedRanges = $this->parent->getNamedRanges(); foreach ($namedRanges as $namedRange) { $parent->addNamedRange($namedRange); } $this->parent->removeSheetByIndex( $this->parent->getIndex($this) ); } $this->parent = $parent; return $this; } /** * Disconnect all cells from this PHPExcel_Worksheet object, * typically so that the worksheet object can be unset * */ public function disconnectCells() { if ($this->cellCollection !== null) { $this->cellCollection->unsetWorksheetCells(); $this->cellCollection = null; } // detach ourself from the workbook, so that it can then delete this worksheet successfully $this->parent = null; } /** * Get hash code * * @return string Hash code */ public function getHashCode() { if ($this->dirty) { $this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__); $this->dirty = false; } return $this->hash; } /** * Set sheet state * * @param string $value Sheet state (visible, hidden, veryHidden) * @return PHPExcel_Worksheet */ public function setSheetState($value = Worksheet::SHEETSTATE_VISIBLE) { $this->sheetState = $value; return $this; } /** * Get sheet state * * @return string Sheet state (visible, hidden, veryHidden) */ public function getSheetState() { return $this->sheetState; } /** * Get sheet view * * @return PHPExcel_Worksheet_SheetView */ public function getSheetView() { return $this->sheetView; } /** * Set sheet view * * @param PHPExcel_Worksheet_SheetView $pValue * @return PHPExcel_Worksheet */ public function setSheetView(SheetView $pValue) { $this->sheetView = $pValue; return $this; } /** * Get Freeze Pane * * @return string */ public function getFreezePane() { return $this->freezePane; } /** * Freeze Pane * * @param string $pCell Cell (i.e. A2) * Examples: * A2 will freeze the rows above cell A2 (i.e row 1) * B1 will freeze the columns to the left of cell B1 (i.e column A) * B2 will freeze the rows above and to the left of cell A2 * (i.e row 1 and column A) * @throws * @return PHPExcel_Worksheet */ public function freezePane($pCell = '') { // Uppercase coordinate $pCell = strtoupper($pCell); if (strpos($pCell, ':') === false && strpos($pCell, ',') === false) { $this->freezePane = $pCell; } else { throw new \Exception('Freeze pane can not be set on a range of cells.'); } return $this; } /** * Freeze Pane by using numeric cell coordinates * * @param int $pColumn Numeric column coordinate of the cell * @param int $pRow Numeric row coordinate of the cell * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1) { return $this->freezePane(Cell::stringFromColumnIndex($pColumn) . $pRow); } /** * Select a range of cells. * * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6' * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setSelectedCells($pCoordinate = 'A1') { // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); // Convert 'A' to 'A:A' $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); // Convert '1' to '1:1' $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate); // Convert 'A:C' to 'A1:C1048576' $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); // Convert '1:3' to 'A1:XFD3' $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { list($first, ) = Cell::splitRange($pCoordinate); $this->activeCell = $first[0]; } else { $this->activeCell = $pCoordinate; } $this->selectedCells = $pCoordinate; return $this; } /** * Get tab color * * @return PHPExcel_Style_Color */ public function getTabColor() { if ($this->tabColor === null) { $this->tabColor = new PHPExcel_Style_Color(); } return $this->tabColor; } /** * Reset tab color * * @return PHPExcel_Worksheet */ public function resetTabColor() { $this->tabColor = null; unset($this->tabColor); return $this; } /** * Define the code name of the sheet * * @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore) * @return objWorksheet * @throws PHPExcel_Exception */ public function setCodeName($pValue = null) { // Is this a 'rename' or not? if ($this->getCodeName() == $pValue) { return $this; } $pValue = str_replace(' ', '_', $pValue);//Excel does this automatically without flinching, we are doing the same // Syntax check // throw an exception if not valid self::checkSheetCodeName($pValue); // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_' if ($this->getParent()) { // Is there already such sheet name? if ($this->getParent()->sheetCodeNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcelString::CountCharacters($pValue) > 29) { $pValue = PHPExcelString::Substring($pValue, 0, 29); } $i = 1; while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) { ++$i; if ($i == 10) { if (PHPExcelString::CountCharacters($pValue) > 28) { $pValue = PHPExcelString::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcelString::CountCharacters($pValue) > 27) { $pValue = PHPExcelString::Substring($pValue, 0, 27); } } } $pValue = $pValue . '_' . $i;// ok, we have a valid name //codeName is'nt used in formula : no need to call for an update //return $this->setTitle($altTitle, $updateFormulaCellReferences); } } $this->codeName=$pValue; return $this; } /** * Return the code name of the sheet * * @return null|string */ public function getCodeName() { return $this->codeName; } /** * Sheet has a code name ? * @return boolean */ public function hasCodeName() { return !(is_null($this->codeName)); } /** * Check sheet code name for valid Excel syntax * * @param string $pValue The string to check * @return string The valid string * @throws */ private static function checkSheetCodeName($pValue) { $CharCount = PHPExcelString::CountCharacters($pValue); if ($CharCount == 0) { throw new \Exception('Sheet code name cannot be empty.'); } // Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'" if ((str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) || (PHPExcelString::Substring($pValue, -1, 1)=='\'') || (PHPExcelString::Substring($pValue, 0, 1)=='\'')) { throw new \Exception('Invalid character found in sheet code name'); } // Maximum 31 characters allowed for sheet title if ($CharCount > 31) { throw new \Exception('Maximum 31 characters allowed in sheet code name.'); } return $pValue; } /** * Show summary right? (Row/Column outlining) * * @return boolean */ public function getShowSummaryRight() { return $this->showSummaryRight; } /** * Set show summary right * * @param boolean $pValue Show summary right (true/false) * @return PHPExcel_Worksheet */ public function setShowSummaryRight($pValue = true) { $this->showSummaryRight = $pValue; return $this; } /** * Show summary below? (Row/Column outlining) * * @return boolean */ public function getShowSummaryBelow() { return $this->showSummaryBelow; } /** * Set show summary below * * @param boolean $pValue Show summary below (true/false) * @return PHPExcel_Worksheet */ public function setShowSummaryBelow($pValue = true) { $this->showSummaryBelow = $pValue; return $this; } /** * Get page setup * * @return PHPExcel_Worksheet_PageSetup */ public function getPageSetup() { return $this->pageSetup; } /** * Set page setup * * @param PHPExcel_Worksheet_PageSetup $pValue * @return PHPExcel_Worksheet */ public function setPageSetup(PageSetup $pValue) { $this->pageSetup = $pValue; return $this; } /** * Get default row dimension * * @return PHPExcel_Worksheet_RowDimension */ public function getDefaultRowDimension() { return $this->defaultRowDimension; } /** * Get collection of column dimensions * * @return PHPExcel_Worksheet_ColumnDimension[] */ public function getColumnDimensions() { return $this->columnDimensions; } /** * Get column dimension at a specific column * * @param string $pColumn String index of the column * @return PHPExcel_Worksheet_ColumnDimension */ public function getColumnDimension($pColumn = 'A', $create = true) { // Uppercase coordinate $pColumn = strtoupper($pColumn); // Fetch dimensions if (!isset($this->columnDimensions[$pColumn])) { if (!$create) { return null; } $this->columnDimensions[$pColumn] = new ColumnDimension($pColumn); if (Cell::columnIndexFromString($this->cachedHighestColumn) < Cell::columnIndexFromString($pColumn)) { $this->cachedHighestColumn = $pColumn; } } return $this->columnDimensions[$pColumn]; } /** * Show gridlines? * * @return boolean */ public function getShowGridlines() { return $this->showGridlines; } /** * Set show gridlines * * @param boolean $pValue Show gridlines (true/false) * @return PHPExcel_Worksheet */ public function setShowGridlines($pValue = false) { $this->showGridlines = $pValue; return $this; } /** * Print gridlines? * * @return boolean */ public function getPrintGridlines() { return $this->printGridlines; } /** * Set print gridlines * * @param boolean $pValue Print gridlines (true/false) * @return PHPExcel_Worksheet */ public function setPrintGridlines($pValue = false) { $this->printGridlines = $pValue; return $this; } /** * Get row dimension at a specific row * * @param int $pRow Numeric index of the row * @return PHPExcel_Worksheet_RowDimension */ public function getRowDimension($pRow = 1, $create = true) { // Found $found = null; // Get row dimension if (!isset($this->rowDimensions[$pRow])) { if (!$create) { return null; } $this->rowDimensions[$pRow] = new RowDimension($pRow); $this->cachedHighestRow = max($this->cachedHighestRow, $pRow); } return $this->rowDimensions[$pRow]; } /** * Get default column dimension * * @return PHPExcel_Worksheet_ColumnDimension */ public function getDefaultColumnDimension() { return $this->defaultColumnDimension; } /** * Get cell at a specific coordinate * * @param string $pCoordinate Coordinate of the cell * @param boolean $createIfNotExists Flag indicating whether a new cell should be created if it doesn't * already exist, or a null should be returned instead * @throws * @return null|PHPExcel_Cell Cell that was found/created or null */ public function getCell($pCoordinate = 'A1', $createIfNotExists = true) { // Check cell collection if ($this->cellCollection->isDataSet(strtoupper($pCoordinate))) { return $this->cellCollection->getCacheData($pCoordinate); } // Worksheet reference? if (strpos($pCoordinate, '!') !== false) { $worksheetReference = Worksheet::extractSheetTitle($pCoordinate, true); return $this->parent->getSheetByName($worksheetReference[0])->getCell(strtoupper($worksheetReference[1]), $createIfNotExists); } if ((!preg_match('/^'.Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) && (preg_match('/^'.Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) { $namedRange = NamedRange::resolveRange($pCoordinate, $this); if ($namedRange !== null) { $pCoordinate = $namedRange->getRange(); return $namedRange->getWorksheet()->getCell($pCoordinate, $createIfNotExists); } } // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { throw new \Exception('Cell coordinate can not be a range of cells.'); } elseif (strpos($pCoordinate, '$') !== false) { throw new \Exception('Cell coordinate must not be absolute.'); } return $createIfNotExists ? $this->createNewCell($pCoordinate) : null; } /** * Create a new cell at the specified coordinate * * @param string $pCoordinate Coordinate of the cell * @return PHPExcel_Cell Cell that was created */ private function createNewCell($pCoordinate) { $cell = $this->cellCollection->addCacheData( $pCoordinate, new Cell(null, DataType::TYPE_NULL, $this) ); $this->cellCollectionIsSorted = false; // Coordinates $aCoordinates = Cell::coordinateFromString($pCoordinate); if (Cell::columnIndexFromString($this->cachedHighestColumn) < Cell::columnIndexFromString($aCoordinates[0])) { $this->cachedHighestColumn = $aCoordinates[0]; } $this->cachedHighestRow = max($this->cachedHighestRow, $aCoordinates[1]); // Cell needs appropriate xfIndex from dimensions records // but don't create dimension records if they don't already exist $rowDimension = $this->getRowDimension($aCoordinates[1], false); $columnDimension = $this->getColumnDimension($aCoordinates[0], false); if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) { // then there is a row dimension with explicit style, assign it to the cell $cell->setXfIndex($rowDimension->getXfIndex()); } elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) { // then there is a column dimension, assign it to the cell $cell->setXfIndex($columnDimension->getXfIndex()); } return $cell; } /** * Return the cache controller for the cell collection * * @return PHPExcel_CachedObjectStorage_xxx */ public function getCellCacheController() { return $this->cellCollection; } /** * 获取工作表总列数 * * @param string $row Return the data highest column for the specified row, * or the highest column of any row if no row number is passed * @return string Highest column name */ public function getHighestColumn($row = null) { if ($row == null) { return $this->cachedHighestColumn; } return $this->getHighestDataColumn($row); } /** * 获取工作表指定行的总列数 * * @param string $row Return the highest data column for the specified row, * or the highest data column of any row if no row number is passed * @return string Highest column name that contains data */ public function getHighestDataColumn($row = null) { return $this->cellCollection->getHighestColumn($row); } /** * 获取工作表总行数 * * @param string $column Return the highest data row for the specified column, * or the highest row of any column if no column letter is passed * @return int Highest row number */ public function getHighestRow($column = null) { if ($column == null) { return $this->cachedHighestRow; } return $this->getHighestDataRow($column); } /** * 获取工作表指定列的总行数 * * @param string $column Return the highest data row for the specified column, * or the highest data row of any column if no column letter is passed * @return string Highest row number that contains data */ public function getHighestDataRow($column = null) { return $this->cellCollection->getHighestRow($column); } }