PageRenderTime 71ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/protected/extensions/PHPExcel/vendor/PHPExcel/Writer/Excel5/Worksheet.php

https://bitbucket.org/y_widyatama/ijepa2
PHP | 3681 lines | 2219 code | 465 blank | 997 comment | 382 complexity | af84df798788e8ff19cbd5db2ddc6d16 MD5 | raw file
Possible License(s): LGPL-2.1, BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Writer_Excel5
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. // Original file header of PEAR::Spreadsheet_Excel_Writer_Worksheet (used as the base for this class):
  28. // -----------------------------------------------------------------------------------------
  29. // /*
  30. // * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
  31. // *
  32. // * The majority of this is _NOT_ my code. I simply ported it from the
  33. // * PERL Spreadsheet::WriteExcel module.
  34. // *
  35. // * The author of the Spreadsheet::WriteExcel module is John McNamara
  36. // * <jmcnamara@cpan.org>
  37. // *
  38. // * I _DO_ maintain this code, and John McNamara has nothing to do with the
  39. // * porting of this code to PHP. Any questions directly related to this
  40. // * class library should be directed to me.
  41. // *
  42. // * License Information:
  43. // *
  44. // * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
  45. // * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
  46. // *
  47. // * This library is free software; you can redistribute it and/or
  48. // * modify it under the terms of the GNU Lesser General Public
  49. // * License as published by the Free Software Foundation; either
  50. // * version 2.1 of the License, or (at your option) any later version.
  51. // *
  52. // * This library is distributed in the hope that it will be useful,
  53. // * but WITHOUT ANY WARRANTY; without even the implied warranty of
  54. // * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  55. // * Lesser General Public License for more details.
  56. // *
  57. // * You should have received a copy of the GNU Lesser General Public
  58. // * License along with this library; if not, write to the Free Software
  59. // * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
  60. // */
  61. /**
  62. * PHPExcel_Writer_Excel5_Worksheet
  63. *
  64. * @category PHPExcel
  65. * @package PHPExcel_Writer_Excel5
  66. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  67. */
  68. class PHPExcel_Writer_Excel5_Worksheet extends PHPExcel_Writer_Excel5_BIFFwriter
  69. {
  70. /**
  71. * Formula parser
  72. *
  73. * @var PHPExcel_Writer_Excel5_Parser
  74. */
  75. private $_parser;
  76. /**
  77. * Maximum number of characters for a string (LABEL record in BIFF5)
  78. * @var integer
  79. */
  80. public $_xls_strmax;
  81. /**
  82. * Array containing format information for columns
  83. * @var array
  84. */
  85. public $_colinfo;
  86. /**
  87. * Array containing the selected area for the worksheet
  88. * @var array
  89. */
  90. public $_selection;
  91. /**
  92. * The active pane for the worksheet
  93. * @var integer
  94. */
  95. public $_active_pane;
  96. /**
  97. * Whether to use outline.
  98. * @var integer
  99. */
  100. public $_outline_on;
  101. /**
  102. * Auto outline styles.
  103. * @var bool
  104. */
  105. public $_outline_style;
  106. /**
  107. * Whether to have outline summary below.
  108. * @var bool
  109. */
  110. public $_outline_below;
  111. /**
  112. * Whether to have outline summary at the right.
  113. * @var bool
  114. */
  115. public $_outline_right;
  116. /**
  117. * Reference to the total number of strings in the workbook
  118. * @var integer
  119. */
  120. public $_str_total;
  121. /**
  122. * Reference to the number of unique strings in the workbook
  123. * @var integer
  124. */
  125. public $_str_unique;
  126. /**
  127. * Reference to the array containing all the unique strings in the workbook
  128. * @var array
  129. */
  130. public $_str_table;
  131. /**
  132. * Color cache
  133. */
  134. private $_colors;
  135. /**
  136. * Index of first used row (at least 0)
  137. * @var int
  138. */
  139. private $_firstRowIndex;
  140. /**
  141. * Index of last used row. (no used rows means -1)
  142. * @var int
  143. */
  144. private $_lastRowIndex;
  145. /**
  146. * Index of first used column (at least 0)
  147. * @var int
  148. */
  149. private $_firstColumnIndex;
  150. /**
  151. * Index of last used column (no used columns means -1)
  152. * @var int
  153. */
  154. private $_lastColumnIndex;
  155. /**
  156. * Sheet object
  157. * @var PHPExcel_Worksheet
  158. */
  159. public $_phpSheet;
  160. /**
  161. * Count cell style Xfs
  162. *
  163. * @var int
  164. */
  165. private $_countCellStyleXfs;
  166. /**
  167. * Escher object corresponding to MSODRAWING
  168. *
  169. * @var PHPExcel_Shared_Escher
  170. */
  171. private $_escher;
  172. /**
  173. * Array of font hashes associated to FONT records index
  174. *
  175. * @var array
  176. */
  177. public $_fntHashIndex;
  178. /**
  179. * Constructor
  180. *
  181. * @param int &$str_total Total number of strings
  182. * @param int &$str_unique Total number of unique strings
  183. * @param array &$str_table String Table
  184. * @param array &$colors Colour Table
  185. * @param mixed $parser The formula parser created for the Workbook
  186. * @param boolean $preCalculateFormulas Flag indicating whether formulas should be calculated or just written
  187. * @param string $phpSheet The worksheet to write
  188. * @param PHPExcel_Worksheet $phpSheet
  189. */
  190. public function __construct(&$str_total, &$str_unique, &$str_table, &$colors,
  191. $parser, $preCalculateFormulas, $phpSheet)
  192. {
  193. // It needs to call its parent's constructor explicitly
  194. parent::__construct();
  195. // change BIFFwriter limit for CONTINUE records
  196. // $this->_limit = 8224;
  197. $this->_preCalculateFormulas = $preCalculateFormulas;
  198. $this->_str_total = &$str_total;
  199. $this->_str_unique = &$str_unique;
  200. $this->_str_table = &$str_table;
  201. $this->_colors = &$colors;
  202. $this->_parser = $parser;
  203. $this->_phpSheet = $phpSheet;
  204. //$this->ext_sheets = array();
  205. //$this->offset = 0;
  206. $this->_xls_strmax = 255;
  207. $this->_colinfo = array();
  208. $this->_selection = array(0,0,0,0);
  209. $this->_active_pane = 3;
  210. $this->_print_headers = 0;
  211. $this->_outline_style = 0;
  212. $this->_outline_below = 1;
  213. $this->_outline_right = 1;
  214. $this->_outline_on = 1;
  215. $this->_fntHashIndex = array();
  216. // calculate values for DIMENSIONS record
  217. $minR = 1;
  218. $minC = 'A';
  219. $maxR = $this->_phpSheet->getHighestRow();
  220. $maxC = $this->_phpSheet->getHighestColumn();
  221. // Determine lowest and highest column and row
  222. // $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR;
  223. $this->_lastRowIndex = ($maxR > 65535) ? 65535 : $maxR ;
  224. $this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC);
  225. $this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC);
  226. // if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255;
  227. if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255;
  228. $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
  229. }
  230. /**
  231. * Add data to the beginning of the workbook (note the reverse order)
  232. * and to the end of the workbook.
  233. *
  234. * @access public
  235. * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
  236. */
  237. function close()
  238. {
  239. $_phpSheet = $this->_phpSheet;
  240. $num_sheets = $_phpSheet->getParent()->getSheetCount();
  241. // Write BOF record
  242. $this->_storeBof(0x0010);
  243. // Write PRINTHEADERS
  244. $this->_writePrintHeaders();
  245. // Write PRINTGRIDLINES
  246. $this->_writePrintGridlines();
  247. // Write GRIDSET
  248. $this->_writeGridset();
  249. // Calculate column widths
  250. $_phpSheet->calculateColumnWidths();
  251. // Column dimensions
  252. if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
  253. $defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont());
  254. }
  255. $columnDimensions = $_phpSheet->getColumnDimensions();
  256. $maxCol = $this->_lastColumnIndex -1;
  257. for ($i = 0; $i <= $maxCol; ++$i) {
  258. $hidden = 0;
  259. $level = 0;
  260. $xfIndex = 15; // there are 15 cell style Xfs
  261. $width = $defaultWidth;
  262. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($i);
  263. if (isset($columnDimensions[$columnLetter])) {
  264. $columnDimension = $columnDimensions[$columnLetter];
  265. if ($columnDimension->getWidth() >= 0) {
  266. $width = $columnDimension->getWidth();
  267. }
  268. $hidden = $columnDimension->getVisible() ? 0 : 1;
  269. $level = $columnDimension->getOutlineLevel();
  270. $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
  271. }
  272. // Components of _colinfo:
  273. // $firstcol first column on the range
  274. // $lastcol last column on the range
  275. // $width width to set
  276. // $xfIndex The optional cell style Xf index to apply to the columns
  277. // $hidden The optional hidden atribute
  278. // $level The optional outline level
  279. $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
  280. }
  281. // Write GUTS
  282. $this->_writeGuts();
  283. // Write DEFAULTROWHEIGHT
  284. $this->_writeDefaultRowHeight();
  285. // Write WSBOOL
  286. $this->_writeWsbool();
  287. // Write horizontal and vertical page breaks
  288. $this->_writeBreaks();
  289. // Write page header
  290. $this->_writeHeader();
  291. // Write page footer
  292. $this->_writeFooter();
  293. // Write page horizontal centering
  294. $this->_writeHcenter();
  295. // Write page vertical centering
  296. $this->_writeVcenter();
  297. // Write left margin
  298. $this->_writeMarginLeft();
  299. // Write right margin
  300. $this->_writeMarginRight();
  301. // Write top margin
  302. $this->_writeMarginTop();
  303. // Write bottom margin
  304. $this->_writeMarginBottom();
  305. // Write page setup
  306. $this->_writeSetup();
  307. // Write sheet protection
  308. $this->_writeProtect();
  309. // Write SCENPROTECT
  310. $this->_writeScenProtect();
  311. // Write OBJECTPROTECT
  312. $this->_writeObjectProtect();
  313. // Write sheet password
  314. $this->_writePassword();
  315. // Write DEFCOLWIDTH record
  316. $this->_writeDefcol();
  317. // Write the COLINFO records if they exist
  318. if (!empty($this->_colinfo)) {
  319. $colcount = count($this->_colinfo);
  320. for ($i = 0; $i < $colcount; ++$i) {
  321. $this->_writeColinfo($this->_colinfo[$i]);
  322. }
  323. }
  324. $autoFilterRange = $_phpSheet->getAutoFilter()->getRange();
  325. if (!empty($autoFilterRange)) {
  326. // Write AUTOFILTERINFO
  327. $this->_writeAutoFilterInfo();
  328. }
  329. // Write sheet dimensions
  330. $this->_writeDimensions();
  331. // Row dimensions
  332. foreach ($_phpSheet->getRowDimensions() as $rowDimension) {
  333. $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
  334. $this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
  335. }
  336. // Write Cells
  337. foreach ($_phpSheet->getCellCollection() as $cellID) {
  338. $cell = $_phpSheet->getCell($cellID);
  339. $row = $cell->getRow() - 1;
  340. $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
  341. // Don't break Excel!
  342. // if ($row + 1 > 65536 or $column + 1 > 256) {
  343. if ($row > 65535 || $column > 255) {
  344. break;
  345. }
  346. // Write cell value
  347. $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
  348. $cVal = $cell->getValue();
  349. if ($cVal instanceof PHPExcel_RichText) {
  350. // $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex);
  351. $arrcRun = array();
  352. $str_len = PHPExcel_Shared_String::CountCharacters($cVal->getPlainText(), 'UTF-8');
  353. $str_pos = 0;
  354. $elements = $cVal->getRichTextElements();
  355. foreach ($elements as $element) {
  356. // FONT Index
  357. if ($element instanceof PHPExcel_RichText_Run) {
  358. $str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()];
  359. }
  360. else {
  361. $str_fontidx = 0;
  362. }
  363. $arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
  364. // Position FROM
  365. $str_pos += PHPExcel_Shared_String::CountCharacters($element->getText(), 'UTF-8');
  366. }
  367. $this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
  368. } else {
  369. switch ($cell->getDatatype()) {
  370. case PHPExcel_Cell_DataType::TYPE_STRING:
  371. case PHPExcel_Cell_DataType::TYPE_NULL:
  372. if ($cVal === '' || $cVal === null) {
  373. $this->_writeBlank($row, $column, $xfIndex);
  374. } else {
  375. $this->_writeString($row, $column, $cVal, $xfIndex);
  376. }
  377. break;
  378. case PHPExcel_Cell_DataType::TYPE_NUMERIC:
  379. $this->_writeNumber($row, $column, $cVal, $xfIndex);
  380. break;
  381. case PHPExcel_Cell_DataType::TYPE_FORMULA:
  382. $calculatedValue = $this->_preCalculateFormulas ?
  383. $cell->getCalculatedValue() : null;
  384. $this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue);
  385. break;
  386. case PHPExcel_Cell_DataType::TYPE_BOOL:
  387. $this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex);
  388. break;
  389. case PHPExcel_Cell_DataType::TYPE_ERROR:
  390. $this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex);
  391. break;
  392. }
  393. }
  394. }
  395. // Append
  396. $this->_writeMsoDrawing();
  397. // Write WINDOW2 record
  398. $this->_writeWindow2();
  399. // Write PLV record
  400. $this->_writePageLayoutView();
  401. // Write ZOOM record
  402. $this->_writeZoom();
  403. if ($_phpSheet->getFreezePane()) {
  404. $this->_writePanes();
  405. }
  406. // Write SELECTION record
  407. $this->_writeSelection();
  408. // Write MergedCellsTable Record
  409. $this->_writeMergedCells();
  410. // Hyperlinks
  411. foreach ($_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
  412. list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate);
  413. $url = $hyperlink->getUrl();
  414. if ( strpos($url, 'sheet://') !== false ) {
  415. // internal to current workbook
  416. $url = str_replace('sheet://', 'internal:', $url);
  417. } else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) {
  418. // URL
  419. // $url = $url;
  420. } else {
  421. // external (local file)
  422. $url = 'external:' . $url;
  423. }
  424. $this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url);
  425. }
  426. $this->_writeDataValidity();
  427. $this->_writeSheetLayout();
  428. // Write SHEETPROTECTION record
  429. $this->_writeSheetProtection();
  430. $this->_writeRangeProtection();
  431. $arrConditionalStyles = $_phpSheet->getConditionalStylesCollection();
  432. if(!empty($arrConditionalStyles)){
  433. $arrConditional = array();
  434. // @todo CFRule & CFHeader
  435. // Write CFHEADER record
  436. $this->_writeCFHeader();
  437. // Write ConditionalFormattingTable records
  438. foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
  439. foreach ($conditionalStyles as $conditional) {
  440. if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
  441. || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
  442. if(!in_array($conditional->getHashCode(), $arrConditional)){
  443. $arrConditional[] = $conditional->getHashCode();
  444. // Write CFRULE record
  445. $this->_writeCFRule($conditional);
  446. }
  447. }
  448. }
  449. }
  450. }
  451. $this->_storeEof();
  452. }
  453. /**
  454. * Write a cell range address in BIFF8
  455. * always fixed range
  456. * See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format
  457. *
  458. * @param string $range E.g. 'A1' or 'A1:B6'
  459. * @return string Binary data
  460. */
  461. private function _writeBIFF8CellRangeAddressFixed($range = 'A1')
  462. {
  463. $explodes = explode(':', $range);
  464. // extract first cell, e.g. 'A1'
  465. $firstCell = $explodes[0];
  466. // extract last cell, e.g. 'B6'
  467. if (count($explodes) == 1) {
  468. $lastCell = $firstCell;
  469. } else {
  470. $lastCell = $explodes[1];
  471. }
  472. $firstCellCoordinates = PHPExcel_Cell::coordinateFromString($firstCell); // e.g. array(0, 1)
  473. $lastCellCoordinates = PHPExcel_Cell::coordinateFromString($lastCell); // e.g. array(1, 6)
  474. return(pack('vvvv',
  475. $firstCellCoordinates[1] - 1,
  476. $lastCellCoordinates[1] - 1,
  477. PHPExcel_Cell::columnIndexFromString($firstCellCoordinates[0]) - 1,
  478. PHPExcel_Cell::columnIndexFromString($lastCellCoordinates[0]) - 1
  479. ));
  480. }
  481. /**
  482. * Retrieves data from memory in one chunk, or from disk in $buffer
  483. * sized chunks.
  484. *
  485. * @return string The data
  486. */
  487. function getData()
  488. {
  489. $buffer = 4096;
  490. // Return data stored in memory
  491. if (isset($this->_data)) {
  492. $tmp = $this->_data;
  493. unset($this->_data);
  494. return $tmp;
  495. }
  496. // No data to return
  497. return false;
  498. }
  499. /**
  500. * Set the option to print the row and column headers on the printed page.
  501. *
  502. * @access public
  503. * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
  504. */
  505. function printRowColHeaders($print = 1)
  506. {
  507. $this->_print_headers = $print;
  508. }
  509. /**
  510. * This method sets the properties for outlining and grouping. The defaults
  511. * correspond to Excel's defaults.
  512. *
  513. * @param bool $visible
  514. * @param bool $symbols_below
  515. * @param bool $symbols_right
  516. * @param bool $auto_style
  517. */
  518. function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
  519. {
  520. $this->_outline_on = $visible;
  521. $this->_outline_below = $symbols_below;
  522. $this->_outline_right = $symbols_right;
  523. $this->_outline_style = $auto_style;
  524. // Ensure this is a boolean vale for Window2
  525. if ($this->_outline_on) {
  526. $this->_outline_on = 1;
  527. }
  528. }
  529. /**
  530. * Write a double to the specified row and column (zero indexed).
  531. * An integer can be written as a double. Excel will display an
  532. * integer. $format is optional.
  533. *
  534. * Returns 0 : normal termination
  535. * -2 : row or column out of range
  536. *
  537. * @param integer $row Zero indexed row
  538. * @param integer $col Zero indexed column
  539. * @param float $num The number to write
  540. * @param mixed $xfIndex The optional XF format
  541. * @return integer
  542. */
  543. private function _writeNumber($row, $col, $num, $xfIndex)
  544. {
  545. $record = 0x0203; // Record identifier
  546. $length = 0x000E; // Number of bytes to follow
  547. $header = pack("vv", $record, $length);
  548. $data = pack("vvv", $row, $col, $xfIndex);
  549. $xl_double = pack("d", $num);
  550. if (self::getByteOrder()) { // if it's Big Endian
  551. $xl_double = strrev($xl_double);
  552. }
  553. $this->_append($header.$data.$xl_double);
  554. return(0);
  555. }
  556. /**
  557. * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
  558. *
  559. * @param int $row Row index (0-based)
  560. * @param int $col Column index (0-based)
  561. * @param string $str The string
  562. * @param int $xfIndex Index to XF record
  563. */
  564. private function _writeString($row, $col, $str, $xfIndex)
  565. {
  566. $this->_writeLabelSst($row, $col, $str, $xfIndex);
  567. }
  568. /**
  569. * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
  570. * It differs from _writeString by the writing of rich text strings.
  571. * @param int $row Row index (0-based)
  572. * @param int $col Column index (0-based)
  573. * @param string $str The string
  574. * @param mixed $xfIndex The XF format index for the cell
  575. * @param array $arrcRun Index to Font record and characters beginning
  576. */
  577. private function _writeRichTextString($row, $col, $str, $xfIndex, $arrcRun){
  578. $record = 0x00FD; // Record identifier
  579. $length = 0x000A; // Bytes to follow
  580. $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($str, $arrcRun);
  581. /* check if string is already present */
  582. if (!isset($this->_str_table[$str])) {
  583. $this->_str_table[$str] = $this->_str_unique++;
  584. }
  585. $this->_str_total++;
  586. $header = pack('vv', $record, $length);
  587. $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
  588. $this->_append($header.$data);
  589. }
  590. /**
  591. * Write a string to the specified row and column (zero indexed).
  592. * NOTE: there is an Excel 5 defined limit of 255 characters.
  593. * $format is optional.
  594. * Returns 0 : normal termination
  595. * -2 : row or column out of range
  596. * -3 : long string truncated to 255 chars
  597. *
  598. * @access public
  599. * @param integer $row Zero indexed row
  600. * @param integer $col Zero indexed column
  601. * @param string $str The string to write
  602. * @param mixed $xfIndex The XF format index for the cell
  603. * @return integer
  604. */
  605. private function _writeLabel($row, $col, $str, $xfIndex)
  606. {
  607. $strlen = strlen($str);
  608. $record = 0x0204; // Record identifier
  609. $length = 0x0008 + $strlen; // Bytes to follow
  610. $str_error = 0;
  611. if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
  612. $str = substr($str, 0, $this->_xls_strmax);
  613. $length = 0x0008 + $this->_xls_strmax;
  614. $strlen = $this->_xls_strmax;
  615. $str_error = -3;
  616. }
  617. $header = pack("vv", $record, $length);
  618. $data = pack("vvvv", $row, $col, $xfIndex, $strlen);
  619. $this->_append($header . $data . $str);
  620. return($str_error);
  621. }
  622. /**
  623. * Write a string to the specified row and column (zero indexed).
  624. * This is the BIFF8 version (no 255 chars limit).
  625. * $format is optional.
  626. * Returns 0 : normal termination
  627. * -2 : row or column out of range
  628. * -3 : long string truncated to 255 chars
  629. *
  630. * @access public
  631. * @param integer $row Zero indexed row
  632. * @param integer $col Zero indexed column
  633. * @param string $str The string to write
  634. * @param mixed $xfIndex The XF format index for the cell
  635. * @return integer
  636. */
  637. private function _writeLabelSst($row, $col, $str, $xfIndex)
  638. {
  639. $record = 0x00FD; // Record identifier
  640. $length = 0x000A; // Bytes to follow
  641. $str = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($str);
  642. /* check if string is already present */
  643. if (!isset($this->_str_table[$str])) {
  644. $this->_str_table[$str] = $this->_str_unique++;
  645. }
  646. $this->_str_total++;
  647. $header = pack('vv', $record, $length);
  648. $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
  649. $this->_append($header.$data);
  650. }
  651. /**
  652. * Writes a note associated with the cell given by the row and column.
  653. * NOTE records don't have a length limit.
  654. *
  655. * @param integer $row Zero indexed row
  656. * @param integer $col Zero indexed column
  657. * @param string $note The note to write
  658. */
  659. private function _writeNote($row, $col, $note)
  660. {
  661. $note_length = strlen($note);
  662. $record = 0x001C; // Record identifier
  663. $max_length = 2048; // Maximun length for a NOTE record
  664. // Length for this record is no more than 2048 + 6
  665. $length = 0x0006 + min($note_length, 2048);
  666. $header = pack("vv", $record, $length);
  667. $data = pack("vvv", $row, $col, $note_length);
  668. $this->_append($header . $data . substr($note, 0, 2048));
  669. for ($i = $max_length; $i < $note_length; $i += $max_length) {
  670. $chunk = substr($note, $i, $max_length);
  671. $length = 0x0006 + strlen($chunk);
  672. $header = pack("vv", $record, $length);
  673. $data = pack("vvv", -1, 0, strlen($chunk));
  674. $this->_append($header.$data.$chunk);
  675. }
  676. return(0);
  677. }
  678. /**
  679. * Write a blank cell to the specified row and column (zero indexed).
  680. * A blank cell is used to specify formatting without adding a string
  681. * or a number.
  682. *
  683. * A blank cell without a format serves no purpose. Therefore, we don't write
  684. * a BLANK record unless a format is specified.
  685. *
  686. * Returns 0 : normal termination (including no format)
  687. * -1 : insufficient number of arguments
  688. * -2 : row or column out of range
  689. *
  690. * @param integer $row Zero indexed row
  691. * @param integer $col Zero indexed column
  692. * @param mixed $xfIndex The XF format index
  693. */
  694. function _writeBlank($row, $col, $xfIndex)
  695. {
  696. $record = 0x0201; // Record identifier
  697. $length = 0x0006; // Number of bytes to follow
  698. $header = pack("vv", $record, $length);
  699. $data = pack("vvv", $row, $col, $xfIndex);
  700. $this->_append($header . $data);
  701. return 0;
  702. }
  703. /**
  704. * Write a boolean or an error type to the specified row and column (zero indexed)
  705. *
  706. * @param int $row Row index (0-based)
  707. * @param int $col Column index (0-based)
  708. * @param int $value
  709. * @param boolean $isError Error or Boolean?
  710. * @param int $xfIndex
  711. */
  712. private function _writeBoolErr($row, $col, $value, $isError, $xfIndex)
  713. {
  714. $record = 0x0205;
  715. $length = 8;
  716. $header = pack("vv", $record, $length);
  717. $data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError);
  718. $this->_append($header . $data);
  719. return 0;
  720. }
  721. /**
  722. * Write a formula to the specified row and column (zero indexed).
  723. * The textual representation of the formula is passed to the parser in
  724. * Parser.php which returns a packed binary string.
  725. *
  726. * Returns 0 : normal termination
  727. * -1 : formula errors (bad formula)
  728. * -2 : row or column out of range
  729. *
  730. * @param integer $row Zero indexed row
  731. * @param integer $col Zero indexed column
  732. * @param string $formula The formula text string
  733. * @param mixed $xfIndex The XF format index
  734. * @param mixed $calculatedValue Calculated value
  735. * @return integer
  736. */
  737. private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
  738. {
  739. $record = 0x0006; // Record identifier
  740. // Initialize possible additional value for STRING record that should be written after the FORMULA record?
  741. $stringValue = null;
  742. // calculated value
  743. if (isset($calculatedValue)) {
  744. // Since we can't yet get the data type of the calculated value,
  745. // we use best effort to determine data type
  746. if (is_bool($calculatedValue)) {
  747. // Boolean value
  748. $num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF);
  749. } elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
  750. // Numeric value
  751. $num = pack('d', $calculatedValue);
  752. } elseif (is_string($calculatedValue)) {
  753. if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
  754. // Error value
  755. $num = pack('CCCvCv', 0x02, 0x00, self::_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
  756. } elseif ($calculatedValue === '') {
  757. // Empty string (and BIFF8)
  758. $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
  759. } else {
  760. // Non-empty string value (or empty string BIFF5)
  761. $stringValue = $calculatedValue;
  762. $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
  763. }
  764. } else {
  765. // We are really not supposed to reach here
  766. $num = pack('d', 0x00);
  767. }
  768. } else {
  769. $num = pack('d', 0x00);
  770. }
  771. $grbit = 0x03; // Option flags
  772. $unknown = 0x0000; // Must be zero
  773. // Strip the '=' or '@' sign at the beginning of the formula string
  774. if ($formula{0} == '=') {
  775. $formula = substr($formula,1);
  776. } else {
  777. // Error handling
  778. $this->_writeString($row, $col, 'Unrecognised character for formula');
  779. return -1;
  780. }
  781. // Parse the formula using the parser in Parser.php
  782. try {
  783. $error = $this->_parser->parse($formula);
  784. $formula = $this->_parser->toReversePolish();
  785. $formlen = strlen($formula); // Length of the binary string
  786. $length = 0x16 + $formlen; // Length of the record data
  787. $header = pack("vv", $record, $length);
  788. $data = pack("vvv", $row, $col, $xfIndex)
  789. . $num
  790. . pack("vVv", $grbit, $unknown, $formlen);
  791. $this->_append($header . $data . $formula);
  792. // Append also a STRING record if necessary
  793. if ($stringValue !== null) {
  794. $this->_writeStringRecord($stringValue);
  795. }
  796. return 0;
  797. } catch (PHPExcel_Exception $e) {
  798. // do nothing
  799. }
  800. }
  801. /**
  802. * Write a STRING record. This
  803. *
  804. * @param string $stringValue
  805. */
  806. private function _writeStringRecord($stringValue)
  807. {
  808. $record = 0x0207; // Record identifier
  809. $data = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($stringValue);
  810. $length = strlen($data);
  811. $header = pack('vv', $record, $length);
  812. $this->_append($header . $data);
  813. }
  814. /**
  815. * Write a hyperlink.
  816. * This is comprised of two elements: the visible label and
  817. * the invisible link. The visible label is the same as the link unless an
  818. * alternative string is specified. The label is written using the
  819. * _writeString() method. Therefore the 255 characters string limit applies.
  820. * $string and $format are optional.
  821. *
  822. * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
  823. * directory url.
  824. *
  825. * Returns 0 : normal termination
  826. * -2 : row or column out of range
  827. * -3 : long string truncated to 255 chars
  828. *
  829. * @param integer $row Row
  830. * @param integer $col Column
  831. * @param string $url URL string
  832. * @return integer
  833. */
  834. private function _writeUrl($row, $col, $url)
  835. {
  836. // Add start row and col to arg list
  837. return($this->_writeUrlRange($row, $col, $row, $col, $url));
  838. }
  839. /**
  840. * This is the more general form of _writeUrl(). It allows a hyperlink to be
  841. * written to a range of cells. This function also decides the type of hyperlink
  842. * to be written. These are either, Web (http, ftp, mailto), Internal
  843. * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
  844. *
  845. * @access private
  846. * @see _writeUrl()
  847. * @param integer $row1 Start row
  848. * @param integer $col1 Start column
  849. * @param integer $row2 End row
  850. * @param integer $col2 End column
  851. * @param string $url URL string
  852. * @return integer
  853. */
  854. function _writeUrlRange($row1, $col1, $row2, $col2, $url)
  855. {
  856. // Check for internal/external sheet links or default to web link
  857. if (preg_match('[^internal:]', $url)) {
  858. return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
  859. }
  860. if (preg_match('[^external:]', $url)) {
  861. return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
  862. }
  863. return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
  864. }
  865. /**
  866. * Used to write http, ftp and mailto hyperlinks.
  867. * The link type ($options) is 0x03 is the same as absolute dir ref without
  868. * sheet. However it is differentiated by the $unknown2 data stream.
  869. *
  870. * @access private
  871. * @see _writeUrl()
  872. * @param integer $row1 Start row
  873. * @param integer $col1 Start column
  874. * @param integer $row2 End row
  875. * @param integer $col2 End column
  876. * @param string $url URL string
  877. * @return integer
  878. */
  879. function _writeUrlWeb($row1, $col1, $row2, $col2, $url)
  880. {
  881. $record = 0x01B8; // Record identifier
  882. $length = 0x00000; // Bytes to follow
  883. // Pack the undocumented parts of the hyperlink stream
  884. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  885. $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
  886. // Pack the option flags
  887. $options = pack("V", 0x03);
  888. // Convert URL to a null terminated wchar string
  889. $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
  890. $url = $url . "\0\0\0";
  891. // Pack the length of the URL
  892. $url_len = pack("V", strlen($url));
  893. // Calculate the data length
  894. $length = 0x34 + strlen($url);
  895. // Pack the header data
  896. $header = pack("vv", $record, $length);
  897. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  898. // Write the packed data
  899. $this->_append($header . $data .
  900. $unknown1 . $options .
  901. $unknown2 . $url_len . $url);
  902. return 0;
  903. }
  904. /**
  905. * Used to write internal reference hyperlinks such as "Sheet1!A1".
  906. *
  907. * @access private
  908. * @see _writeUrl()
  909. * @param integer $row1 Start row
  910. * @param integer $col1 Start column
  911. * @param integer $row2 End row
  912. * @param integer $col2 End column
  913. * @param string $url URL string
  914. * @return integer
  915. */
  916. function _writeUrlInternal($row1, $col1, $row2, $col2, $url)
  917. {
  918. $record = 0x01B8; // Record identifier
  919. $length = 0x00000; // Bytes to follow
  920. // Strip URL type
  921. $url = preg_replace('/^internal:/', '', $url);
  922. // Pack the undocumented parts of the hyperlink stream
  923. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
  924. // Pack the option flags
  925. $options = pack("V", 0x08);
  926. // Convert the URL type and to a null terminated wchar string
  927. $url .= "\0";
  928. // character count
  929. $url_len = PHPExcel_Shared_String::CountCharacters($url);
  930. $url_len = pack('V', $url_len);
  931. $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8');
  932. // Calculate the data length
  933. $length = 0x24 + strlen($url);
  934. // Pack the header data
  935. $header = pack("vv", $record, $length);
  936. $data = pack("vvvv", $row1, $row2, $col1, $col2);
  937. // Write the packed data
  938. $this->_append($header . $data .
  939. $unknown1 . $options .
  940. $url_len . $url);
  941. return 0;
  942. }
  943. /**
  944. * Write links to external directory names such as 'c:\foo.xls',
  945. * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
  946. *
  947. * Note: Excel writes some relative links with the $dir_long string. We ignore
  948. * these cases for the sake of simpler code.
  949. *
  950. * @access private
  951. * @see _writeUrl()
  952. * @param integer $row1 Start row
  953. * @param integer $col1 Start column
  954. * @param integer $row2 End row
  955. * @param integer $col2 End column
  956. * @param string $url URL string
  957. * @return integer
  958. */
  959. function _writeUrlExternal($row1, $col1, $row2, $col2, $url)
  960. {
  961. // Network drives are different. We will handle them separately
  962. // MS/Novell network drives and shares start with \\
  963. if (preg_match('[^external:\\\\]', $url)) {
  964. return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
  965. }
  966. $record = 0x01B8; // Record identifier
  967. $length = 0x00000; // Bytes to follow
  968. // Strip URL type and change Unix dir separator to Dos style (if needed)
  969. //
  970. $url = preg_replace('/^external:/', '', $url);
  971. $url = preg_replace('/\//', "\\", $url);
  972. // Determine if the link is relative or absolute:
  973. // relative if link contains no dir separator, "somefile.xls"
  974. // relative if link starts with up-dir, "..\..\somefile.xls"
  975. // otherwise, absolute
  976. $absolute = 0x00; // relative path
  977. if ( preg_match('/^[A-Z]:/', $url) ) {
  978. $absolute = 0x02; // absolute path on Windows, e.g. C:\...
  979. }
  980. $link_type = 0x01 | $absolute;
  981. // Determine if the link contains a sheet reference and change some of the
  982. // parameters accordingly.
  983. // Split the dir name and sheet name (if it exists)
  984. $dir_long = $url;
  985. if (preg_match("/\#/", $url)) {
  986. $link_type |= 0x08;
  987. }
  988. // Pack the link type
  989. $link_type = pack("V", $link_type);
  990. // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
  991. $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
  992. $up_count = pack("v", $up_count);
  993. // Store the short dos dir name (null terminated)
  994. $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
  995. // Store the long dir name as a wchar string (non-null terminated)
  996. $dir_long = $dir_long . "\0";
  997. // Pack the lengths of the dir strings
  998. $dir_short_len = pack("V", strlen($dir_short) );
  999. $dir_long_len = pack("V", strlen($dir_long) );
  1000. $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
  1001. // Pack the undocumented parts of the hyperlink stream
  1002. $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
  1003. $unknown2 = pack("H*",'0303000000000000C000000000000046' );
  1004. $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
  1005. $unknown4 = pack("v", 0x03 );
  1006. // Pack the main data stream
  1007. $data = pack("vvvv", $row1, $row2, $col1, $col2) .
  1008. $unknown1 .
  1009. $link_type .
  1010. $unknown2 .
  1011. $up_count .
  1012. $dir_short_len.
  1013. $dir_short .
  1014. $unknown3 .
  1015. $stream_len ;/*.
  1016. $dir_long_len .
  1017. $unknown4 .
  1018. $dir_long .
  1019. $sheet_len .
  1020. $sheet ;*/
  1021. // Pack the header data
  1022. $length = strlen($data);
  1023. $header = pack("vv", $record, $length);
  1024. // Write the packed data
  1025. $this->_append($header. $data);
  1026. return 0;
  1027. }
  1028. /**
  1029. * This method is used to set the height and format for a row.
  1030. *
  1031. * @param integer $row The row to set
  1032. * @param integer $height Height we are giving to the row.
  1033. * Use null to set XF without setting height
  1034. * @param integer $xfIndex The optional cell style Xf index to apply to the columns
  1035. * @param bool $hidden The optional hidden attribute
  1036. * @param integer $level The optional outline level for row, in range [0,7]
  1037. */
  1038. private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
  1039. {
  1040. $record = 0x0208; // Record identifier
  1041. $length = 0x0010; // Number of bytes to follow
  1042. $colMic = 0x0000; // First defined column
  1043. $colMac = 0x0000; // Last defined column
  1044. $irwMac = 0x0000; // Used by Excel to optimise loading
  1045. $reserved = 0x0000; // Reserved
  1046. $grbit = 0x0000; // Option flags
  1047. $ixfe = $xfIndex;
  1048. if ( $height < 0 ){
  1049. $height = null;
  1050. }
  1051. // Use _writeRow($row, null, $XF) to set XF format without setting height
  1052. if ($height != null) {
  1053. $miyRw = $height * 20; // row height
  1054. } else {
  1055. $miyRw = 0xff; // default row height is 256
  1056. }
  1057. // Set the options flags. fUnsynced is used to show that the font and row
  1058. // heights are not compatible. This is usually the case for WriteExcel.
  1059. // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
  1060. // is collapsed. Instead it is used to indicate that the previous row is
  1061. // collapsed. The zero height flag, 0x20, is used to collapse a row.
  1062. $grbit |= $level;
  1063. if ($hidden) {
  1064. $grbit |= 0x0020;
  1065. }
  1066. if ($height !== null) {
  1067. $grbit |= 0x0040; // fUnsynced
  1068. }
  1069. if ($xfIndex !== 0xF) {
  1070. $grbit |= 0x0080;
  1071. }
  1072. $grbit |= 0x0100;
  1073. $header = pack("vv", $record, $length);
  1074. $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
  1075. $irwMac,$reserved, $grbit, $ixfe);
  1076. $this->_append($header.$data);
  1077. }
  1078. /**
  1079. * Writes Excel DIMENSIONS to define the area in which there is data.
  1080. */
  1081. private function _writeDimensions()
  1082. {
  1083. $record = 0x0200; // Record identifier
  1084. $length = 0x000E;
  1085. $data = pack('VVvvv'
  1086. , $this->_firstRowIndex
  1087. , $this->_lastRowIndex + 1
  1088. , $this->_firstColumnIndex
  1089. , $this->_lastColumnIndex + 1
  1090. , 0x0000 // reserved
  1091. );
  1092. $header = pack("vv", $record, $length);
  1093. $this->_append($header.$data);
  1094. }
  1095. /**
  1096. * Write BIFF record Window2.
  1097. */
  1098. private function _writeWindow2()
  1099. {
  1100. $record = 0x023E; // Record identifier
  1101. $length = 0x0012;
  1102. $grbit = 0x00B6; // Option flags
  1103. $rwTop = 0x0000; // Top row visible in window
  1104. $colLeft = 0x0000; // Leftmost column visible in window
  1105. // The options flags that comprise $grbit
  1106. $fDspFmla = 0; // 0 - bit
  1107. $fDspGrid = $this->_phpSheet->getShowGridlines() ? 1 : 0; // 1
  1108. $fDspRwCol = $this->_phpSheet->getShowRowColHeaders() ? 1 : 0; // 2
  1109. $fFrozen = $this->_phpSheet->getFreezePane() ? 1 : 0; // 3
  1110. $fDspZeros = 1; // 4
  1111. $fDefaultHdr = 1; // 5
  1112. $fArabic = $this->_phpSheet->getRightToLeft() ? 1 : 0; // 6
  1113. $fDspGuts = $this->_outline_on; // 7
  1114. $fFrozenNoSplit = 0; // 0 - bit
  1115. // no support in PHPExcel for selected sheet, therefore sheet is only selected if it is the active sheet
  1116. $fSelected = ($this->_phpSheet === $this->_phpSheet->getParent()->getActiveSheet()) ? 1 : 0;
  1117. $fPaged = 1; // 2
  1118. $fPageBreakPreview = $this->_phpSheet->getSheetView()->getView() === PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW;
  1119. $grbit = $fDspFmla;
  1120. $grbit |= $fDspGrid << 1;
  1121. $grbit |= $fDspRwCol << 2;
  1122. $grbit |= $fFrozen << 3;
  1123. $grbit |= $fDspZeros << 4;
  1124. $grbit |= $fDefaultHdr << 5;
  1125. $grbit |= $fArabic << 6;
  1126. $grbit |= $fDspGuts << 7;
  1127. $grbit |= $fFrozenNoSplit << 8;
  1128. $grbit |= $fSelected << 9;
  1129. $grbit |= $fPaged << 10;
  1130. $grbit |= $fPageBreakPreview << 11;
  1131. $header = pack("vv", $record, $length);
  1132. $data = pack("vvv", $grbit, $rwTop, $colLeft);
  1133. // FIXME !!!
  1134. $rgbHdr = 0x0040; // Row/column heading and gridline color index
  1135. $zoom_factor_page_break = ($fPageBreakPreview? $this->_phpSheet->getSheetView()->getZoomScale() : 0x0000);
  1136. $zoom_factor_normal = $this->_phpSheet->getSheetView()->getZoomScaleNormal();
  1137. $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
  1138. $this->_append($header.$data);
  1139. }
  1140. /**
  1141. * Write BIFF record DEFAULTROWHEIGHT.
  1142. */
  1143. private function _writeDefaultRowHeight()
  1144. {
  1145. $defaultRowHeight = $this->_phpSheet->getDefaultRowDimension()->getRowHeight();
  1146. if ($defaultRowHeight < 0) {
  1147. return;
  1148. }
  1149. // convert to twips
  1150. $defaultRowHeight = (int) 20 * $defaultRowHeight;
  1151. $record = 0x0225; // Record identifier
  1152. $length = 0x0004; // Number of bytes to follow
  1153. $header = pack("vv", $record, $length);
  1154. $data = pack("vv", 1, $defaultRowHeight);
  1155. $this->_append($header . $data);
  1156. }
  1157. /**
  1158. * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
  1159. */
  1160. private function _writeDefcol()
  1161. {
  1162. $defaultColWidth = 8;
  1163. $record = 0x0055; // Record identifier
  1164. $length = 0x0002; // Number of bytes to follow
  1165. $header = pack("vv", $record, $length);
  1166. $data = pack("v", $defaultColWidth);
  1167. $this->_append($header . $data);
  1168. }
  1169. /**
  1170. * Write BIFF record COLINFO to define column widths
  1171. *
  1172. * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
  1173. * length record.
  1174. *
  1175. * @param array $col_array This is the only parameter received and is composed of the following:
  1176. * 0 => First formatted column,
  1177. * 1 => Last formatted column,
  1178. * 2 => Col width (8.43 is Excel default),
  1179. * 3 => The optional XF format of the column,
  1180. * 4 => Option flags.
  1181. * 5 => Optional outline level
  1182. */
  1183. private function _writeColinfo($col_array)
  1184. {
  1185. if (isset($col_array[0])) {
  1186. $colFirst = $col_array[0];
  1187. }
  1188. if (isset($col_array[1])) {
  1189. $colLast = $col_array[1];
  1190. }
  1191. if (isset($col_array[2])) {
  1192. $coldx = $col_array[2];
  1193. } else {
  1194. $coldx = 8.43;
  1195. }
  1196. if (isset($col_array[3])) {
  1197. $xfIndex = $col_array[3];
  1198. } else {
  1199. $xfIndex = 15;
  1200. }
  1201. if (isset($col_array[4])) {
  1202. $grbit = $col_array[4];
  1203. } else {
  1204. $grbit = 0;
  1205. }
  1206. if (isset($col_array[5])) {
  1207. $level = $col_array[5];
  1208. } else {
  1209. $level = 0;
  1210. }
  1211. $record = 0x007D; // Record identifier
  1212. $length = 0x000C; // Number of bytes to follow
  1213. $coldx *= 256; // Convert to units of 1/256 of a char
  1214. $ixfe = $xfIndex;
  1215. $reserved = 0x0000; // Reserved
  1216. $level = max(0, min($level, 7));
  1217. $grbit |= $level << 8;
  1218. $header = pack("vv", $record, $length);
  1219. $data = pack("vvvvvv", $colFirst, $colLast, $coldx,
  1220. $ixfe, $grbit, $reserved);
  1221. $this->_append($header.$data);
  1222. }
  1223. /**
  1224. * Write BIFF record SELECTION.
  1225. */
  1226. private function _writeSelection()
  1227. {
  1228. // look up the selected cell range
  1229. $selectedCells = $this->_phpSheet->getSelectedCells();
  1230. $selectedCells = PHPExcel_Cell::splitRange($this->_phpSheet->getSelectedCells());
  1231. $selectedCells = $selectedCells[0];
  1232. if (count($selectedCells) == 2) {
  1233. list($first, $last) = $selectedCells;
  1234. } else {
  1235. $first = $selectedCells[0];
  1236. $last = $selectedCells[0];
  1237. }
  1238. list($colFirst, $rwFirst) = PHPExcel_Cell::coordinateFromString($first);
  1239. $colFirst = PHPExcel_Cell::columnIndexFromString($colFirst) - 1; // base 0 column index
  1240. --$rwFirst; // base 0 row index
  1241. list($colLast, $rwLast) = PHPExcel_Cell::coordinateFromString($last);
  1242. $colLast = PHPExcel_Cell::columnIndexFromString($colLast) - 1; // base 0 column index
  1243. --$rwLast; // base 0 row index
  1244. // make sure we are not out of bounds
  1245. $colFirst = min($colFirst, 255);
  1246. $colLast = min($colLast, 255);
  1247. $rwFirst = min($rwFirst, 65535);
  1248. $rwLast = min($rwLast, 65535);
  1249. $record = 0x001D; // Record identifier
  1250. $length = 0x000F; // Number of bytes to follow
  1251. $pnn = $this->_active_pane; // Pane position
  1252. $rwAct = $rwFirst; // Active row
  1253. $colAct = $colFirst; // Active column
  1254. $irefAct = 0; // Active cell ref
  1255. $cref = 1; // Number of refs
  1256. if (!isset($rwLast)) {
  1257. $rwLast = $rwFirst; // Last row in reference
  1258. }
  1259. if (!isset($colLast)) {
  1260. $colLast = $colFirst; // Last col in reference
  1261. }
  1262. // Swap last row/col for first row/col as necessary
  1263. if ($rwFirst > $rwLast) {
  1264. list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
  1265. }
  1266. if ($colFirst > $colLast) {
  1267. list($colFirst, $colLast) = array($colLast, $colFirst);
  1268. }
  1269. $header = pack("vv", $record, $length);
  1270. $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
  1271. $irefAct, $cref,
  1272. $rwFirst, $rwLast,
  1273. $colFirst, $colLast);
  1274. $this->_append($header . $data);
  1275. }
  1276. /**
  1277. * Store the MERGEDCELLS records for all ranges of merged cells
  1278. */
  1279. private function _writeMergedCells()
  1280. {
  1281. $mergeCells = $this->_phpSheet->getMergeCells();
  1282. $countMergeCells = count($mergeCells);
  1283. if ($countMergeCells == 0) {
  1284. return;
  1285. }
  1286. // maximum allowed number of merged cells per record
  1287. $maxCountMergeCellsPerRecord = 1027;
  1288. // record identifier
  1289. $record = 0x00E5;
  1290. // counter for total number of merged cells treated so far by the writer
  1291. $i = 0;
  1292. // counter for number of merged cells written in record currently being written
  1293. $j = 0;
  1294. // initialize record data
  1295. $recordData = '';
  1296. // loop through the merged cells
  1297. foreach ($mergeCells as $mergeCell) {
  1298. ++$i;
  1299. ++$j;
  1300. // extract the row and column indexes
  1301. $range = PHPExcel_Cell::splitRange($mergeCell);
  1302. list($first, $last) = $range[0];
  1303. list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
  1304. list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
  1305. $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
  1306. // flush record if we have reached limit for number of merged cells, or reached final merged cell
  1307. if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
  1308. $recordData = pack('v', $j) . $recordData;
  1309. $length = strlen($recordData);
  1310. $header = pack('vv', $record, $length);
  1311. $this->_append($header . $recordData);
  1312. // initialize for next record, if any
  1313. $recordData = '';
  1314. $j = 0;
  1315. }
  1316. }
  1317. }
  1318. /**
  1319. * Write SHEETLAYOUT record
  1320. */
  1321. private function _writeSheetLayout()
  1322. {
  1323. if (!$this->_phpSheet->isTabColorSet()) {
  1324. return;
  1325. }
  1326. $recordData = pack(
  1327. 'vvVVVvv'
  1328. , 0x0862
  1329. , 0x0000 // unused
  1330. , 0x00000000 // unused
  1331. , 0x00000000 // unused
  1332. , 0x00000014 // size of record data
  1333. , $this->_colors[$this->_phpSheet->getTabColor()->getRGB()] // color index
  1334. , 0x0000 // unused
  1335. );
  1336. $length = strlen($recordData);
  1337. $record = 0x0862; // Record identifier
  1338. $header = pack('vv', $record, $length);
  1339. $this->_append($header . $recordData);
  1340. }
  1341. /**
  1342. * Write SHEETPROTECTION
  1343. */
  1344. private function _writeSheetProtection()
  1345. {
  1346. // record identifier
  1347. $record = 0x0867;
  1348. // prepare options
  1349. $options = (int) !$this->_phpSheet->getProtection()->getObjects()
  1350. | (int) !$this->_phpSheet->getProtection()->getScenarios() << 1
  1351. | (int) !$this->_phpSheet->getProtection()->getFormatCells() << 2
  1352. | (int) !$this->_phpSheet->getProtection()->getFormatColumns() << 3
  1353. | (int) !$this->_phpSheet->getProtection()->getFormatRows() << 4
  1354. | (int) !$this->_phpSheet->getProtection()->getInsertColumns() << 5
  1355. | (int) !$this->_phpSheet->getProtection()->getInsertRows() << 6
  1356. | (int) !$this->_phpSheet->getProtection()->getInsertHyperlinks() << 7
  1357. | (int) !$this->_phpSheet->getProtection()->getDeleteColumns() << 8
  1358. | (int) !$this->_phpSheet->getProtection()->getDeleteRows() << 9
  1359. | (int) !$this->_phpSheet->getProtection()->getSelectLockedCells() << 10
  1360. | (int) !$this->_phpSheet->getProtection()->getSort() << 11
  1361. | (int) !$this->_phpSheet->getProtection()->getAutoFilter() << 12
  1362. | (int) !$this->_phpSheet->getProtection()->getPivotTables() << 13
  1363. | (int) !$this->_phpSheet->getProtection()->getSelectUnlockedCells() << 14 ;
  1364. // record data
  1365. $recordData = pack(
  1366. 'vVVCVVvv'
  1367. , 0x0867 // repeated record identifier
  1368. , 0x0000 // not used
  1369. , 0x0000 // not used
  1370. , 0x00 // not used
  1371. , 0x01000200 // unknown data
  1372. , 0xFFFFFFFF // unknown data
  1373. , $options // options
  1374. , 0x0000 // not used
  1375. );
  1376. $length = strlen($recordData);
  1377. $header = pack('vv', $record, $length);
  1378. $this->_append($header . $recordData);
  1379. }
  1380. /**
  1381. * Write BIFF record RANGEPROTECTION
  1382. *
  1383. * Openoffice.org's Documentaion of the Microsoft Excel File Format uses term RANGEPROTECTION for these records
  1384. * Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records
  1385. */
  1386. private function _writeRangeProtection()
  1387. {
  1388. foreach ($this->_phpSheet->getProtectedCells() as $range => $password) {
  1389. // number of ranges, e.g. 'A1:B3 C20:D25'
  1390. $cellRanges = explode(' ', $range);
  1391. $cref = count($cellRanges);
  1392. $recordData = pack(
  1393. 'vvVVvCVvVv',
  1394. 0x0868,
  1395. 0x00,
  1396. 0x0000,
  1397. 0x0000,
  1398. 0x02,
  1399. 0x0,
  1400. 0x0000,
  1401. $cref,
  1402. 0x0000,
  1403. 0x00
  1404. );
  1405. foreach ($cellRanges as $cellRange) {
  1406. $recordData .= $this->_writeBIFF8CellRangeAddressFixed($cellRange);
  1407. }
  1408. // the rgbFeat structure
  1409. $recordData .= pack(
  1410. 'VV',
  1411. 0x0000,
  1412. hexdec($password)
  1413. );
  1414. $recordData .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong('p' . md5($recordData));
  1415. $length = strlen($recordData);
  1416. $record = 0x0868; // Record identifier
  1417. $header = pack("vv", $record, $length);
  1418. $this->_append($header . $recordData);
  1419. }
  1420. }
  1421. /**
  1422. * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
  1423. * references in a worksheet.
  1424. *
  1425. * Excel only stores references to external sheets that are used in formulas.
  1426. * For simplicity we store references to all the sheets in the workbook
  1427. * regardless of whether they are used or not. This reduces the overall
  1428. * complexity and eliminates the need for a two way dialogue between the formula
  1429. * parser the worksheet objects.
  1430. *
  1431. * @param integer $count The number of external sheet references in this worksheet
  1432. */
  1433. private function _writeExterncount($count)
  1434. {
  1435. $record = 0x0016; // Record identifier
  1436. $length = 0x0002; // Number of bytes to foll…

Large files files are truncated, but you can click here to view the full file