Worksheet.Class.php 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973
  1. <?php
  2. /**
  3. * Created by PhpStorm.
  4. * User: phperstar
  5. * Date: 2020/8/10
  6. * Time: 5:17 PM
  7. */
  8. namespace Util\PHPExcel;
  9. use Util\PHPExcel\Cell;
  10. use Util\PHPExcel\CachedObjectStorageFactory;
  11. use Util\PHPExcel\Shared\PHPExcelString;
  12. use Util\PHPExcel\Worksheet\SheetView;
  13. use Util\PHPExcel\Worksheet\PageSetup;
  14. use Util\PHPExcel\Worksheet\RowDimension;
  15. use Util\PHPExcel\Worksheet\ColumnDimension;
  16. use Util\PHPExcel\Cell\DataType;
  17. class Worksheet
  18. {
  19. /**
  20. * Cacheable collection of cells
  21. *
  22. * @var PHPExcel_CachedObjectStorage_xxx
  23. */
  24. private $cellCollection;
  25. /**
  26. * Worksheet title
  27. *
  28. * @var string
  29. */
  30. private $title;
  31. /**
  32. * Invalid characters in sheet title
  33. *
  34. * @var array
  35. */
  36. private static $invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
  37. /**
  38. * Parent spreadsheet
  39. *
  40. * @var PHPExcel
  41. */
  42. private $parent;
  43. /**
  44. * Dirty flag
  45. *
  46. * @var boolean
  47. */
  48. private $dirty = true;
  49. /* Sheet state */
  50. const SHEETSTATE_VISIBLE = 'visible';
  51. const SHEETSTATE_HIDDEN = 'hidden';
  52. const SHEETSTATE_VERYHIDDEN = 'veryHidden';
  53. /**
  54. * Freeze pane
  55. *
  56. * @var string
  57. */
  58. private $freezePane = '';
  59. /**
  60. * CodeName
  61. *
  62. * @var string
  63. */
  64. private $codeName = null;
  65. /**
  66. * Default row dimension
  67. *
  68. * @var RowDimension
  69. */
  70. private $defaultRowDimension;
  71. /**
  72. * Page setup
  73. *
  74. * @var PageSetup
  75. */
  76. private $pageSetup;
  77. /**
  78. * Collection of column dimensions
  79. *
  80. * @var ColumnDimension[]
  81. */
  82. private $columnDimensions = array();
  83. /**
  84. * Show gridlines?
  85. *
  86. * @var boolean
  87. */
  88. private $showGridlines = true;
  89. /**
  90. * Print gridlines?
  91. *
  92. * @var boolean
  93. */
  94. private $printGridlines = false;
  95. /**
  96. * Collection of row dimensions
  97. *
  98. * @var RowDimension[]
  99. */
  100. private $rowDimensions = array();
  101. /**
  102. * Default column dimension
  103. *
  104. * @var ColumnDimension
  105. */
  106. private $defaultColumnDimension = null;
  107. /**
  108. * Cached highest column
  109. *
  110. * @var string
  111. */
  112. private $cachedHighestColumn = 'A';
  113. /**
  114. * Cached highest row
  115. *
  116. * @var int
  117. */
  118. private $cachedHighestRow = 1;
  119. /**
  120. * Create a new worksheet
  121. *
  122. * @param PHPExcel $pParent
  123. * @param string $pTitle
  124. */
  125. public function __construct(PHPExcel $pParent = null, $pTitle = 'Worksheet')
  126. {
  127. $this->cellCollection = CachedObjectStorageFactory::getInstance($this);
  128. // Set parent and title
  129. $this->parent = $pParent;
  130. $this->setTitle($pTitle, false);
  131. // setTitle can change $pTitle
  132. $this->setCodeName($this->getTitle());
  133. $this->setSheetState(Worksheet::SHEETSTATE_VISIBLE);
  134. // Set page setup
  135. $this->pageSetup = new PageSetup();
  136. // Set sheet view
  137. $this->sheetView = new SheetView();
  138. // Default row dimension
  139. $this->defaultRowDimension = new RowDimension(null);
  140. /*
  141. // Set page margins
  142. $this->pageMargins = new PHPExcel_Worksheet_PageMargins();
  143. // Set page header/footer
  144. $this->headerFooter = new PHPExcel_Worksheet_HeaderFooter();
  145. // Drawing collection
  146. $this->drawingCollection = new ArrayObject();
  147. // Chart collection
  148. $this->chartCollection = new ArrayObject();
  149. // Protection
  150. $this->protection = new PHPExcel_Worksheet_Protection(); */
  151. // Default column dimension
  152. $this->defaultColumnDimension = new ColumnDimension(null);
  153. //$this->autoFilter = new PHPExcel_Worksheet_AutoFilter(null, $this);
  154. }
  155. /**
  156. * Get collection of cells
  157. *
  158. * @param boolean $pSorted Also sort the cell collection?
  159. * @return PHPExcel_Cell[]
  160. */
  161. public function getCellCollection($pSorted = true)
  162. {
  163. if ($pSorted) {
  164. // Re-order cell collection
  165. return $this->sortCellCollection();
  166. }
  167. if ($this->cellCollection !== null) {
  168. return $this->cellCollection->getCellList();
  169. }
  170. return array();
  171. }
  172. /**
  173. * Sort collection of cells
  174. *
  175. * @return PHPExcel_Worksheet
  176. */
  177. public function sortCellCollection()
  178. {
  179. if ($this->cellCollection !== null) {
  180. return $this->cellCollection->getSortedCellList();
  181. }
  182. return array();
  183. }
  184. /**
  185. * Get title
  186. *
  187. * @return string
  188. */
  189. public function getTitle()
  190. {
  191. return $this->title;
  192. }
  193. /**
  194. * Set title
  195. *
  196. * @param string $pValue String containing the dimension of this worksheet
  197. * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should
  198. * be updated to reflect the new sheet name.
  199. * This should be left as the default true, unless you are
  200. * certain that no formula cells on any worksheet contain
  201. * references to this worksheet
  202. * @return PHPExcel_Worksheet
  203. */
  204. public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
  205. {
  206. // Is this a 'rename' or not?
  207. if ($this->getTitle() == $pValue) {
  208. return $this;
  209. }
  210. // Syntax check
  211. self::checkSheetTitle($pValue);
  212. // Old title
  213. $oldTitle = $this->getTitle();
  214. if ($this->parent) {
  215. // Is there already such sheet name?
  216. if ($this->parent->sheetNameExists($pValue)) {
  217. // Use name, but append with lowest possible integer
  218. if (PHPExcelString::CountCharacters($pValue) > 29) {
  219. $pValue = PHPExcelString::Substring($pValue, 0, 29);
  220. }
  221. $i = 1;
  222. while ($this->parent->sheetNameExists($pValue . ' ' . $i)) {
  223. ++$i;
  224. if ($i == 10) {
  225. if (PHPExcelString::CountCharacters($pValue) > 28) {
  226. $pValue = PHPExcelString::Substring($pValue, 0, 28);
  227. }
  228. } elseif ($i == 100) {
  229. if (PHPExcelString::CountCharacters($pValue) > 27) {
  230. $pValue = PHPExcelString::Substring($pValue, 0, 27);
  231. }
  232. }
  233. }
  234. $altTitle = $pValue . ' ' . $i;
  235. return $this->setTitle($altTitle, $updateFormulaCellReferences);
  236. }
  237. }
  238. // Set title
  239. $this->title = $pValue;
  240. $this->dirty = true;
  241. if ($this->parent && $this->parent->getCalculationEngine()) {
  242. // New title
  243. $newTitle = $this->getTitle();
  244. $this->parent->getCalculationEngine()
  245. ->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
  246. if ($updateFormulaCellReferences) {
  247. PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->parent, $oldTitle, $newTitle);
  248. }
  249. }
  250. return $this;
  251. }
  252. /**
  253. * Check sheet title for valid Excel syntax
  254. *
  255. * @param string $pValue The string to check
  256. * @return string The valid string
  257. * @throws
  258. */
  259. private static function checkSheetTitle($pValue)
  260. {
  261. // Some of the printable ASCII characters are invalid: * : / \ ? [ ]
  262. if (str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) {
  263. throw new \Exception('Invalid character found in sheet title');
  264. }
  265. // Maximum 31 characters allowed for sheet title
  266. if (PHPExcelString::CountCharacters($pValue) > 31) {
  267. throw new \Exception('Maximum 31 characters allowed in sheet title.');
  268. }
  269. return $pValue;
  270. }
  271. /**
  272. * Get array of invalid characters for sheet title
  273. *
  274. * @return array
  275. */
  276. public static function getInvalidCharacters()
  277. {
  278. return self::$invalidCharacters;
  279. }
  280. /**
  281. * Get parent
  282. *
  283. * @return PHPExcel
  284. */
  285. public function getParent()
  286. {
  287. return $this->parent;
  288. }
  289. /**
  290. * Re-bind parent
  291. *
  292. * @param PHPExcel $parent
  293. * @return Worksheet
  294. */
  295. public function rebindParent(PHPExcel $parent)
  296. {
  297. if ($this->parent !== null) {
  298. $namedRanges = $this->parent->getNamedRanges();
  299. foreach ($namedRanges as $namedRange) {
  300. $parent->addNamedRange($namedRange);
  301. }
  302. $this->parent->removeSheetByIndex(
  303. $this->parent->getIndex($this)
  304. );
  305. }
  306. $this->parent = $parent;
  307. return $this;
  308. }
  309. /**
  310. * Disconnect all cells from this PHPExcel_Worksheet object,
  311. * typically so that the worksheet object can be unset
  312. *
  313. */
  314. public function disconnectCells()
  315. {
  316. if ($this->cellCollection !== null) {
  317. $this->cellCollection->unsetWorksheetCells();
  318. $this->cellCollection = null;
  319. }
  320. // detach ourself from the workbook, so that it can then delete this worksheet successfully
  321. $this->parent = null;
  322. }
  323. /**
  324. * Get hash code
  325. *
  326. * @return string Hash code
  327. */
  328. public function getHashCode()
  329. {
  330. if ($this->dirty) {
  331. $this->hash = md5($this->title . $this->autoFilter . ($this->protection->isProtectionEnabled() ? 't' : 'f') . __CLASS__);
  332. $this->dirty = false;
  333. }
  334. return $this->hash;
  335. }
  336. /**
  337. * Set sheet state
  338. *
  339. * @param string $value Sheet state (visible, hidden, veryHidden)
  340. * @return PHPExcel_Worksheet
  341. */
  342. public function setSheetState($value = Worksheet::SHEETSTATE_VISIBLE)
  343. {
  344. $this->sheetState = $value;
  345. return $this;
  346. }
  347. /**
  348. * Get sheet state
  349. *
  350. * @return string Sheet state (visible, hidden, veryHidden)
  351. */
  352. public function getSheetState()
  353. {
  354. return $this->sheetState;
  355. }
  356. /**
  357. * Get sheet view
  358. *
  359. * @return PHPExcel_Worksheet_SheetView
  360. */
  361. public function getSheetView()
  362. {
  363. return $this->sheetView;
  364. }
  365. /**
  366. * Set sheet view
  367. *
  368. * @param PHPExcel_Worksheet_SheetView $pValue
  369. * @return PHPExcel_Worksheet
  370. */
  371. public function setSheetView(SheetView $pValue)
  372. {
  373. $this->sheetView = $pValue;
  374. return $this;
  375. }
  376. /**
  377. * Get Freeze Pane
  378. *
  379. * @return string
  380. */
  381. public function getFreezePane()
  382. {
  383. return $this->freezePane;
  384. }
  385. /**
  386. * Freeze Pane
  387. *
  388. * @param string $pCell Cell (i.e. A2)
  389. * Examples:
  390. * A2 will freeze the rows above cell A2 (i.e row 1)
  391. * B1 will freeze the columns to the left of cell B1 (i.e column A)
  392. * B2 will freeze the rows above and to the left of cell A2
  393. * (i.e row 1 and column A)
  394. * @throws
  395. * @return PHPExcel_Worksheet
  396. */
  397. public function freezePane($pCell = '')
  398. {
  399. // Uppercase coordinate
  400. $pCell = strtoupper($pCell);
  401. if (strpos($pCell, ':') === false && strpos($pCell, ',') === false) {
  402. $this->freezePane = $pCell;
  403. } else {
  404. throw new \Exception('Freeze pane can not be set on a range of cells.');
  405. }
  406. return $this;
  407. }
  408. /**
  409. * Freeze Pane by using numeric cell coordinates
  410. *
  411. * @param int $pColumn Numeric column coordinate of the cell
  412. * @param int $pRow Numeric row coordinate of the cell
  413. * @throws PHPExcel_Exception
  414. * @return PHPExcel_Worksheet
  415. */
  416. public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 1)
  417. {
  418. return $this->freezePane(Cell::stringFromColumnIndex($pColumn) . $pRow);
  419. }
  420. /**
  421. * Select a range of cells.
  422. *
  423. * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
  424. * @throws PHPExcel_Exception
  425. * @return PHPExcel_Worksheet
  426. */
  427. public function setSelectedCells($pCoordinate = 'A1')
  428. {
  429. // Uppercase coordinate
  430. $pCoordinate = strtoupper($pCoordinate);
  431. // Convert 'A' to 'A:A'
  432. $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
  433. // Convert '1' to '1:1'
  434. $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
  435. // Convert 'A:C' to 'A1:C1048576'
  436. $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
  437. // Convert '1:3' to 'A1:XFD3'
  438. $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
  439. if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
  440. list($first, ) = Cell::splitRange($pCoordinate);
  441. $this->activeCell = $first[0];
  442. } else {
  443. $this->activeCell = $pCoordinate;
  444. }
  445. $this->selectedCells = $pCoordinate;
  446. return $this;
  447. }
  448. /**
  449. * Get tab color
  450. *
  451. * @return PHPExcel_Style_Color
  452. */
  453. public function getTabColor()
  454. {
  455. if ($this->tabColor === null) {
  456. $this->tabColor = new PHPExcel_Style_Color();
  457. }
  458. return $this->tabColor;
  459. }
  460. /**
  461. * Reset tab color
  462. *
  463. * @return PHPExcel_Worksheet
  464. */
  465. public function resetTabColor()
  466. {
  467. $this->tabColor = null;
  468. unset($this->tabColor);
  469. return $this;
  470. }
  471. /**
  472. * Define the code name of the sheet
  473. *
  474. * @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore)
  475. * @return objWorksheet
  476. * @throws PHPExcel_Exception
  477. */
  478. public function setCodeName($pValue = null)
  479. {
  480. // Is this a 'rename' or not?
  481. if ($this->getCodeName() == $pValue) {
  482. return $this;
  483. }
  484. $pValue = str_replace(' ', '_', $pValue);//Excel does this automatically without flinching, we are doing the same
  485. // Syntax check
  486. // throw an exception if not valid
  487. self::checkSheetCodeName($pValue);
  488. // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'
  489. if ($this->getParent()) {
  490. // Is there already such sheet name?
  491. if ($this->getParent()->sheetCodeNameExists($pValue)) {
  492. // Use name, but append with lowest possible integer
  493. if (PHPExcelString::CountCharacters($pValue) > 29) {
  494. $pValue = PHPExcelString::Substring($pValue, 0, 29);
  495. }
  496. $i = 1;
  497. while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) {
  498. ++$i;
  499. if ($i == 10) {
  500. if (PHPExcelString::CountCharacters($pValue) > 28) {
  501. $pValue = PHPExcelString::Substring($pValue, 0, 28);
  502. }
  503. } elseif ($i == 100) {
  504. if (PHPExcelString::CountCharacters($pValue) > 27) {
  505. $pValue = PHPExcelString::Substring($pValue, 0, 27);
  506. }
  507. }
  508. }
  509. $pValue = $pValue . '_' . $i;// ok, we have a valid name
  510. //codeName is'nt used in formula : no need to call for an update
  511. //return $this->setTitle($altTitle, $updateFormulaCellReferences);
  512. }
  513. }
  514. $this->codeName=$pValue;
  515. return $this;
  516. }
  517. /**
  518. * Return the code name of the sheet
  519. *
  520. * @return null|string
  521. */
  522. public function getCodeName()
  523. {
  524. return $this->codeName;
  525. }
  526. /**
  527. * Sheet has a code name ?
  528. * @return boolean
  529. */
  530. public function hasCodeName()
  531. {
  532. return !(is_null($this->codeName));
  533. }
  534. /**
  535. * Check sheet code name for valid Excel syntax
  536. *
  537. * @param string $pValue The string to check
  538. * @return string The valid string
  539. * @throws
  540. */
  541. private static function checkSheetCodeName($pValue)
  542. {
  543. $CharCount = PHPExcelString::CountCharacters($pValue);
  544. if ($CharCount == 0) {
  545. throw new \Exception('Sheet code name cannot be empty.');
  546. }
  547. // Some of the printable ASCII characters are invalid: * : / \ ? [ ] and first and last characters cannot be a "'"
  548. if ((str_replace(self::$invalidCharacters, '', $pValue) !== $pValue) ||
  549. (PHPExcelString::Substring($pValue, -1, 1)=='\'') ||
  550. (PHPExcelString::Substring($pValue, 0, 1)=='\'')) {
  551. throw new \Exception('Invalid character found in sheet code name');
  552. }
  553. // Maximum 31 characters allowed for sheet title
  554. if ($CharCount > 31) {
  555. throw new \Exception('Maximum 31 characters allowed in sheet code name.');
  556. }
  557. return $pValue;
  558. }
  559. /**
  560. * Show summary right? (Row/Column outlining)
  561. *
  562. * @return boolean
  563. */
  564. public function getShowSummaryRight()
  565. {
  566. return $this->showSummaryRight;
  567. }
  568. /**
  569. * Set show summary right
  570. *
  571. * @param boolean $pValue Show summary right (true/false)
  572. * @return PHPExcel_Worksheet
  573. */
  574. public function setShowSummaryRight($pValue = true)
  575. {
  576. $this->showSummaryRight = $pValue;
  577. return $this;
  578. }
  579. /**
  580. * Show summary below? (Row/Column outlining)
  581. *
  582. * @return boolean
  583. */
  584. public function getShowSummaryBelow()
  585. {
  586. return $this->showSummaryBelow;
  587. }
  588. /**
  589. * Set show summary below
  590. *
  591. * @param boolean $pValue Show summary below (true/false)
  592. * @return PHPExcel_Worksheet
  593. */
  594. public function setShowSummaryBelow($pValue = true)
  595. {
  596. $this->showSummaryBelow = $pValue;
  597. return $this;
  598. }
  599. /**
  600. * Get page setup
  601. *
  602. * @return PHPExcel_Worksheet_PageSetup
  603. */
  604. public function getPageSetup()
  605. {
  606. return $this->pageSetup;
  607. }
  608. /**
  609. * Set page setup
  610. *
  611. * @param PHPExcel_Worksheet_PageSetup $pValue
  612. * @return PHPExcel_Worksheet
  613. */
  614. public function setPageSetup(PageSetup $pValue)
  615. {
  616. $this->pageSetup = $pValue;
  617. return $this;
  618. }
  619. /**
  620. * Get default row dimension
  621. *
  622. * @return PHPExcel_Worksheet_RowDimension
  623. */
  624. public function getDefaultRowDimension()
  625. {
  626. return $this->defaultRowDimension;
  627. }
  628. /**
  629. * Get collection of column dimensions
  630. *
  631. * @return PHPExcel_Worksheet_ColumnDimension[]
  632. */
  633. public function getColumnDimensions()
  634. {
  635. return $this->columnDimensions;
  636. }
  637. /**
  638. * Get column dimension at a specific column
  639. *
  640. * @param string $pColumn String index of the column
  641. * @return PHPExcel_Worksheet_ColumnDimension
  642. */
  643. public function getColumnDimension($pColumn = 'A', $create = true)
  644. {
  645. // Uppercase coordinate
  646. $pColumn = strtoupper($pColumn);
  647. // Fetch dimensions
  648. if (!isset($this->columnDimensions[$pColumn])) {
  649. if (!$create) {
  650. return null;
  651. }
  652. $this->columnDimensions[$pColumn] = new ColumnDimension($pColumn);
  653. if (Cell::columnIndexFromString($this->cachedHighestColumn) < Cell::columnIndexFromString($pColumn)) {
  654. $this->cachedHighestColumn = $pColumn;
  655. }
  656. }
  657. return $this->columnDimensions[$pColumn];
  658. }
  659. /**
  660. * Show gridlines?
  661. *
  662. * @return boolean
  663. */
  664. public function getShowGridlines()
  665. {
  666. return $this->showGridlines;
  667. }
  668. /**
  669. * Set show gridlines
  670. *
  671. * @param boolean $pValue Show gridlines (true/false)
  672. * @return PHPExcel_Worksheet
  673. */
  674. public function setShowGridlines($pValue = false)
  675. {
  676. $this->showGridlines = $pValue;
  677. return $this;
  678. }
  679. /**
  680. * Print gridlines?
  681. *
  682. * @return boolean
  683. */
  684. public function getPrintGridlines()
  685. {
  686. return $this->printGridlines;
  687. }
  688. /**
  689. * Set print gridlines
  690. *
  691. * @param boolean $pValue Print gridlines (true/false)
  692. * @return PHPExcel_Worksheet
  693. */
  694. public function setPrintGridlines($pValue = false)
  695. {
  696. $this->printGridlines = $pValue;
  697. return $this;
  698. }
  699. /**
  700. * Get row dimension at a specific row
  701. *
  702. * @param int $pRow Numeric index of the row
  703. * @return PHPExcel_Worksheet_RowDimension
  704. */
  705. public function getRowDimension($pRow = 1, $create = true)
  706. {
  707. // Found
  708. $found = null;
  709. // Get row dimension
  710. if (!isset($this->rowDimensions[$pRow])) {
  711. if (!$create) {
  712. return null;
  713. }
  714. $this->rowDimensions[$pRow] = new RowDimension($pRow);
  715. $this->cachedHighestRow = max($this->cachedHighestRow, $pRow);
  716. }
  717. return $this->rowDimensions[$pRow];
  718. }
  719. /**
  720. * Get default column dimension
  721. *
  722. * @return PHPExcel_Worksheet_ColumnDimension
  723. */
  724. public function getDefaultColumnDimension()
  725. {
  726. return $this->defaultColumnDimension;
  727. }
  728. /**
  729. * Get cell at a specific coordinate
  730. *
  731. * @param string $pCoordinate Coordinate of the cell
  732. * @param boolean $createIfNotExists Flag indicating whether a new cell should be created if it doesn't
  733. * already exist, or a null should be returned instead
  734. * @throws
  735. * @return null|PHPExcel_Cell Cell that was found/created or null
  736. */
  737. public function getCell($pCoordinate = 'A1', $createIfNotExists = true)
  738. {
  739. // Check cell collection
  740. if ($this->cellCollection->isDataSet(strtoupper($pCoordinate))) {
  741. return $this->cellCollection->getCacheData($pCoordinate);
  742. }
  743. // Worksheet reference?
  744. if (strpos($pCoordinate, '!') !== false) {
  745. $worksheetReference = Worksheet::extractSheetTitle($pCoordinate, true);
  746. return $this->parent->getSheetByName($worksheetReference[0])->getCell(strtoupper($worksheetReference[1]), $createIfNotExists);
  747. }
  748. if ((!preg_match('/^'.Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
  749. (preg_match('/^'.Calculation::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $pCoordinate, $matches))) {
  750. $namedRange = NamedRange::resolveRange($pCoordinate, $this);
  751. if ($namedRange !== null) {
  752. $pCoordinate = $namedRange->getRange();
  753. return $namedRange->getWorksheet()->getCell($pCoordinate, $createIfNotExists);
  754. }
  755. }
  756. // Uppercase coordinate
  757. $pCoordinate = strtoupper($pCoordinate);
  758. if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
  759. throw new \Exception('Cell coordinate can not be a range of cells.');
  760. } elseif (strpos($pCoordinate, '$') !== false) {
  761. throw new \Exception('Cell coordinate must not be absolute.');
  762. }
  763. return $createIfNotExists ? $this->createNewCell($pCoordinate) : null;
  764. }
  765. /**
  766. * Create a new cell at the specified coordinate
  767. *
  768. * @param string $pCoordinate Coordinate of the cell
  769. * @return PHPExcel_Cell Cell that was created
  770. */
  771. private function createNewCell($pCoordinate)
  772. {
  773. $cell = $this->cellCollection->addCacheData(
  774. $pCoordinate,
  775. new Cell(null, DataType::TYPE_NULL, $this)
  776. );
  777. $this->cellCollectionIsSorted = false;
  778. // Coordinates
  779. $aCoordinates = Cell::coordinateFromString($pCoordinate);
  780. if (Cell::columnIndexFromString($this->cachedHighestColumn) < Cell::columnIndexFromString($aCoordinates[0])) {
  781. $this->cachedHighestColumn = $aCoordinates[0];
  782. }
  783. $this->cachedHighestRow = max($this->cachedHighestRow, $aCoordinates[1]);
  784. // Cell needs appropriate xfIndex from dimensions records
  785. // but don't create dimension records if they don't already exist
  786. $rowDimension = $this->getRowDimension($aCoordinates[1], false);
  787. $columnDimension = $this->getColumnDimension($aCoordinates[0], false);
  788. if ($rowDimension !== null && $rowDimension->getXfIndex() > 0) {
  789. // then there is a row dimension with explicit style, assign it to the cell
  790. $cell->setXfIndex($rowDimension->getXfIndex());
  791. } elseif ($columnDimension !== null && $columnDimension->getXfIndex() > 0) {
  792. // then there is a column dimension, assign it to the cell
  793. $cell->setXfIndex($columnDimension->getXfIndex());
  794. }
  795. return $cell;
  796. }
  797. /**
  798. * Return the cache controller for the cell collection
  799. *
  800. * @return PHPExcel_CachedObjectStorage_xxx
  801. */
  802. public function getCellCacheController()
  803. {
  804. return $this->cellCollection;
  805. }
  806. /**
  807. * 获取工作表总列数
  808. *
  809. * @param string $row Return the data highest column for the specified row,
  810. * or the highest column of any row if no row number is passed
  811. * @return string Highest column name
  812. */
  813. public function getHighestColumn($row = null)
  814. {
  815. if ($row == null) {
  816. return $this->cachedHighestColumn;
  817. }
  818. return $this->getHighestDataColumn($row);
  819. }
  820. /**
  821. * 获取工作表指定行的总列数
  822. *
  823. * @param string $row Return the highest data column for the specified row,
  824. * or the highest data column of any row if no row number is passed
  825. * @return string Highest column name that contains data
  826. */
  827. public function getHighestDataColumn($row = null)
  828. {
  829. return $this->cellCollection->getHighestColumn($row);
  830. }
  831. /**
  832. * 获取工作表总行数
  833. *
  834. * @param string $column Return the highest data row for the specified column,
  835. * or the highest row of any column if no column letter is passed
  836. * @return int Highest row number
  837. */
  838. public function getHighestRow($column = null)
  839. {
  840. if ($column == null) {
  841. return $this->cachedHighestRow;
  842. }
  843. return $this->getHighestDataRow($column);
  844. }
  845. /**
  846. * 获取工作表指定列的总行数
  847. *
  848. * @param string $column Return the highest data row for the specified column,
  849. * or the highest data row of any column if no column letter is passed
  850. * @return string Highest row number that contains data
  851. */
  852. public function getHighestDataRow($column = null)
  853. {
  854. return $this->cellCollection->getHighestRow($column);
  855. }
  856. }