<?php
/**
* Created by PhpStorm.
* User: phperstar
* Date: 2020/8/10
* Time: 5:17 PM
*/
namespace Util\PHPExcel;
use Util\PHPExcel\Cell;
use Util\PHPExcel\CachedObjectStorageFactory;
use Util\PHPExcel\Shared\PHPExcelString;
use Util\PHPExcel\Worksheet\SheetView;
use Util\PHPExcel\Worksheet\PageSetup;
use Util\PHPExcel\Worksheet\RowDimension;
use Util\PHPExcel\Worksheet\ColumnDimension;
use Util\PHPExcel\Cell\DataType;
class Worksheet
{
/**
* Cacheable collection of cells
*
* @var PHPExcel_CachedObjectStorage_xxx
*/
private $cellCollection;
/**
* Worksheet title
*
* @var string
*/
private $title;
/**
* Invalid characters in sheet title
*
* @var array
*/
private static $invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
/**
* Parent spreadsheet
*
* @var PHPExcel
*/
private $parent;
/**
* Dirty flag
*
* @var boolean
*/
private $dirty = true;
/* Sheet state */
const SHEETSTATE_VISIBLE = 'visible';
const SHEETSTATE_HIDDEN = 'hidden';
const SHEETSTATE_VERYHIDDEN = 'veryHidden';
/**
* Freeze pane
*
* @var string
*/
private $freezePane = '';
/**
* CodeName
*
* @var string
*/
private $codeName = null;
/**
* Default row dimension
*
* @var RowDimension
*/
private $defaultRowDimension;
/**
* Page setup
*
* @var PageSetup
*/
private $pageSetup;
/**
* Collection of column dimensions
*
* @var ColumnDimension[]
*/
private $columnDimensions = array();
/**
* Show gridlines?
*
* @var boolean
*/
private $showGridlines = true;
/**
* Print gridlines?
*
* @var boolean
*/
private $printGridlines = false;
/**
* Collection of row dimensions
*
* @var RowDimension[]
*/
private $rowDimensions = array();
/**
* Default column dimension
*
* @var ColumnDimension
*/
private $defaultColumnDimension = null;
/**
* Cached highest column
*
* @var string
*/
private $cachedHighestColumn = 'A';
/**
* Cached highest row
*
* @var int
*/
private $cachedHighestRow = 1;
/**
* Create a new worksheet
*
* @param PHPExcel $pParent
* @param string $pTitle
*/
public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
{
$this->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);
}
}