PageRenderTime 61ms CodeModel.GetById 27ms RepoModel.GetById 1ms app.codeStats 0ms

/PHPExcel_1.7.8-with_documentation-msoffice_format/Classes/PHPExcel/Writer/Excel5/Workbook.php

https://bitbucket.org/izubizarreta/https-bitbucket.org-bityvip
PHP | 1450 lines | 1029 code | 114 blank | 307 comment | 47 complexity | f531a913116dbf2ccf52118a8020be42 MD5 | raw file
Possible License(s): LGPL-3.0, LGPL-2.0, JSON, GPL-2.0, BSD-3-Clause, LGPL-2.1, MIT
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.8, 2012-10-12
  26. */
  27. // Original file header of PEAR::Spreadsheet_Excel_Writer_Workbook (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_Workbook
  63. *
  64. * @category PHPExcel
  65. * @package PHPExcel_Writer_Excel5
  66. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  67. */
  68. class PHPExcel_Writer_Excel5_Workbook extends PHPExcel_Writer_Excel5_BIFFwriter
  69. {
  70. /**
  71. * Formula parser
  72. *
  73. * @var PHPExcel_Writer_Excel5_Parser
  74. */
  75. private $_parser;
  76. /**
  77. * The BIFF file size for the workbook.
  78. * @var integer
  79. * @see _calcSheetOffsets()
  80. */
  81. public $_biffsize;
  82. /**
  83. * XF Writers
  84. * @var PHPExcel_Writer_Excel5_Xf[]
  85. */
  86. private $_xfWriters = array();
  87. /**
  88. * Array containing the colour palette
  89. * @var array
  90. */
  91. public $_palette;
  92. /**
  93. * The codepage indicates the text encoding used for strings
  94. * @var integer
  95. */
  96. public $_codepage;
  97. /**
  98. * The country code used for localization
  99. * @var integer
  100. */
  101. public $_country_code;
  102. /**
  103. * Workbook
  104. * @var PHPExcel
  105. */
  106. private $_phpExcel;
  107. /**
  108. * Fonts writers
  109. *
  110. * @var PHPExcel_Writer_Excel5_Font[]
  111. */
  112. private $_fontWriters = array();
  113. /**
  114. * Added fonts. Maps from font's hash => index in workbook
  115. *
  116. * @var array
  117. */
  118. private $_addedFonts = array();
  119. /**
  120. * Shared number formats
  121. *
  122. * @var array
  123. */
  124. private $_numberFormats = array();
  125. /**
  126. * Added number formats. Maps from numberFormat's hash => index in workbook
  127. *
  128. * @var array
  129. */
  130. private $_addedNumberFormats = array();
  131. /**
  132. * Sizes of the binary worksheet streams
  133. *
  134. * @var array
  135. */
  136. private $_worksheetSizes = array();
  137. /**
  138. * Offsets of the binary worksheet streams relative to the start of the global workbook stream
  139. *
  140. * @var array
  141. */
  142. private $_worksheetOffsets = array();
  143. /**
  144. * Total number of shared strings in workbook
  145. *
  146. * @var int
  147. */
  148. private $_str_total;
  149. /**
  150. * Number of unique shared strings in workbook
  151. *
  152. * @var int
  153. */
  154. private $_str_unique;
  155. /**
  156. * Array of unique shared strings in workbook
  157. *
  158. * @var array
  159. */
  160. private $_str_table;
  161. /**
  162. * Color cache
  163. */
  164. private $_colors;
  165. /**
  166. * Escher object corresponding to MSODRAWINGGROUP
  167. *
  168. * @var PHPExcel_Shared_Escher
  169. */
  170. private $_escher;
  171. /**
  172. * Class constructor
  173. *
  174. * @param PHPExcel $phpExcel The Workbook
  175. * @param int &$str_total Total number of strings
  176. * @param int &$str_unique Total number of unique strings
  177. * @param array &$str_table String Table
  178. * @param array &$colors Colour Table
  179. * @param mixed $parser The formula parser created for the Workbook
  180. */
  181. public function __construct(PHPExcel $phpExcel = null,
  182. &$str_total, &$str_unique, &$str_table, &$colors,
  183. $parser )
  184. {
  185. // It needs to call its parent's constructor explicitly
  186. parent::__construct();
  187. $this->_parser = $parser;
  188. $this->_biffsize = 0;
  189. $this->_palette = array();
  190. $this->_country_code = -1;
  191. $this->_str_total = &$str_total;
  192. $this->_str_unique = &$str_unique;
  193. $this->_str_table = &$str_table;
  194. $this->_colors = &$colors;
  195. $this->_setPaletteXl97();
  196. $this->_phpExcel = $phpExcel;
  197. // set BIFFwriter limit for CONTINUE records
  198. // $this->_limit = 8224;
  199. $this->_codepage = 0x04B0;
  200. // Add empty sheets and Build color cache
  201. $countSheets = $phpExcel->getSheetCount();
  202. for ($i = 0; $i < $countSheets; ++$i) {
  203. $phpSheet = $phpExcel->getSheet($i);
  204. $this->_parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser
  205. $supbook_index = 0x00;
  206. $ref = pack('vvv', $supbook_index, $i, $i);
  207. $this->_parser->_references[] = $ref; // Register reference with parser
  208. // Sheet tab colors?
  209. if ($phpSheet->isTabColorSet()) {
  210. $this->_addColor($phpSheet->getTabColor()->getRGB());
  211. }
  212. }
  213. }
  214. /**
  215. * Add a new XF writer
  216. *
  217. * @param PHPExcel_Style
  218. * @param boolean Is it a style XF?
  219. * @return int Index to XF record
  220. */
  221. public function addXfWriter($style, $isStyleXf = false)
  222. {
  223. $xfWriter = new PHPExcel_Writer_Excel5_Xf($style);
  224. $xfWriter->setIsStyleXf($isStyleXf);
  225. // Add the font if not already added
  226. $fontIndex = $this->_addFont($style->getFont());
  227. // Assign the font index to the xf record
  228. $xfWriter->setFontIndex($fontIndex);
  229. // Background colors, best to treat these after the font so black will come after white in custom palette
  230. $xfWriter->setFgColor($this->_addColor($style->getFill()->getStartColor()->getRGB()));
  231. $xfWriter->setBgColor($this->_addColor($style->getFill()->getEndColor()->getRGB()));
  232. $xfWriter->setBottomColor($this->_addColor($style->getBorders()->getBottom()->getColor()->getRGB()));
  233. $xfWriter->setTopColor($this->_addColor($style->getBorders()->getTop()->getColor()->getRGB()));
  234. $xfWriter->setRightColor($this->_addColor($style->getBorders()->getRight()->getColor()->getRGB()));
  235. $xfWriter->setLeftColor($this->_addColor($style->getBorders()->getLeft()->getColor()->getRGB()));
  236. $xfWriter->setDiagColor($this->_addColor($style->getBorders()->getDiagonal()->getColor()->getRGB()));
  237. // Add the number format if it is not a built-in one and not already added
  238. if ($style->getNumberFormat()->getBuiltInFormatCode() === false) {
  239. $numberFormatHashCode = $style->getNumberFormat()->getHashCode();
  240. if (isset($this->_addedNumberFormats[$numberFormatHashCode])) {
  241. $numberFormatIndex = $this->_addedNumberFormats[$numberFormatHashCode];
  242. } else {
  243. $numberFormatIndex = 164 + count($this->_numberFormats);
  244. $this->_numberFormats[$numberFormatIndex] = $style->getNumberFormat();
  245. $this->_addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
  246. }
  247. } else {
  248. $numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode();
  249. }
  250. // Assign the number format index to xf record
  251. $xfWriter->setNumberFormatIndex($numberFormatIndex);
  252. $this->_xfWriters[] = $xfWriter;
  253. $xfIndex = count($this->_xfWriters) - 1;
  254. return $xfIndex;
  255. }
  256. /**
  257. * Add a font to added fonts
  258. *
  259. * @param PHPExcel_Style_Font $font
  260. * @return int Index to FONT record
  261. */
  262. public function _addFont(PHPExcel_Style_Font $font)
  263. {
  264. $fontHashCode = $font->getHashCode();
  265. if(isset($this->_addedFonts[$fontHashCode])){
  266. $fontIndex = $this->_addedFonts[$fontHashCode];
  267. } else {
  268. $countFonts = count($this->_fontWriters);
  269. $fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
  270. $fontWriter = new PHPExcel_Writer_Excel5_Font($font);
  271. $fontWriter->setColorIndex($this->_addColor($font->getColor()->getRGB()));
  272. $this->_fontWriters[] = $fontWriter;
  273. $this->_addedFonts[$fontHashCode] = $fontIndex;
  274. }
  275. return $fontIndex;
  276. }
  277. /**
  278. * Alter color palette adding a custom color
  279. *
  280. * @param string $rgb E.g. 'FF00AA'
  281. * @return int Color index
  282. */
  283. private function _addColor($rgb) {
  284. if (!isset($this->_colors[$rgb])) {
  285. if (count($this->_colors) < 57) {
  286. // then we add a custom color altering the palette
  287. $colorIndex = 8 + count($this->_colors);
  288. $this->_palette[$colorIndex] =
  289. array(
  290. hexdec(substr($rgb, 0, 2)),
  291. hexdec(substr($rgb, 2, 2)),
  292. hexdec(substr($rgb, 4)),
  293. 0
  294. );
  295. $this->_colors[$rgb] = $colorIndex;
  296. } else {
  297. // no room for more custom colors, just map to black
  298. $colorIndex = 0;
  299. }
  300. } else {
  301. // fetch already added custom color
  302. $colorIndex = $this->_colors[$rgb];
  303. }
  304. return $colorIndex;
  305. }
  306. /**
  307. * Sets the colour palette to the Excel 97+ default.
  308. *
  309. * @access private
  310. */
  311. function _setPaletteXl97()
  312. {
  313. $this->_palette = array(
  314. 0x08 => array(0x00, 0x00, 0x00, 0x00),
  315. 0x09 => array(0xff, 0xff, 0xff, 0x00),
  316. 0x0A => array(0xff, 0x00, 0x00, 0x00),
  317. 0x0B => array(0x00, 0xff, 0x00, 0x00),
  318. 0x0C => array(0x00, 0x00, 0xff, 0x00),
  319. 0x0D => array(0xff, 0xff, 0x00, 0x00),
  320. 0x0E => array(0xff, 0x00, 0xff, 0x00),
  321. 0x0F => array(0x00, 0xff, 0xff, 0x00),
  322. 0x10 => array(0x80, 0x00, 0x00, 0x00),
  323. 0x11 => array(0x00, 0x80, 0x00, 0x00),
  324. 0x12 => array(0x00, 0x00, 0x80, 0x00),
  325. 0x13 => array(0x80, 0x80, 0x00, 0x00),
  326. 0x14 => array(0x80, 0x00, 0x80, 0x00),
  327. 0x15 => array(0x00, 0x80, 0x80, 0x00),
  328. 0x16 => array(0xc0, 0xc0, 0xc0, 0x00),
  329. 0x17 => array(0x80, 0x80, 0x80, 0x00),
  330. 0x18 => array(0x99, 0x99, 0xff, 0x00),
  331. 0x19 => array(0x99, 0x33, 0x66, 0x00),
  332. 0x1A => array(0xff, 0xff, 0xcc, 0x00),
  333. 0x1B => array(0xcc, 0xff, 0xff, 0x00),
  334. 0x1C => array(0x66, 0x00, 0x66, 0x00),
  335. 0x1D => array(0xff, 0x80, 0x80, 0x00),
  336. 0x1E => array(0x00, 0x66, 0xcc, 0x00),
  337. 0x1F => array(0xcc, 0xcc, 0xff, 0x00),
  338. 0x20 => array(0x00, 0x00, 0x80, 0x00),
  339. 0x21 => array(0xff, 0x00, 0xff, 0x00),
  340. 0x22 => array(0xff, 0xff, 0x00, 0x00),
  341. 0x23 => array(0x00, 0xff, 0xff, 0x00),
  342. 0x24 => array(0x80, 0x00, 0x80, 0x00),
  343. 0x25 => array(0x80, 0x00, 0x00, 0x00),
  344. 0x26 => array(0x00, 0x80, 0x80, 0x00),
  345. 0x27 => array(0x00, 0x00, 0xff, 0x00),
  346. 0x28 => array(0x00, 0xcc, 0xff, 0x00),
  347. 0x29 => array(0xcc, 0xff, 0xff, 0x00),
  348. 0x2A => array(0xcc, 0xff, 0xcc, 0x00),
  349. 0x2B => array(0xff, 0xff, 0x99, 0x00),
  350. 0x2C => array(0x99, 0xcc, 0xff, 0x00),
  351. 0x2D => array(0xff, 0x99, 0xcc, 0x00),
  352. 0x2E => array(0xcc, 0x99, 0xff, 0x00),
  353. 0x2F => array(0xff, 0xcc, 0x99, 0x00),
  354. 0x30 => array(0x33, 0x66, 0xff, 0x00),
  355. 0x31 => array(0x33, 0xcc, 0xcc, 0x00),
  356. 0x32 => array(0x99, 0xcc, 0x00, 0x00),
  357. 0x33 => array(0xff, 0xcc, 0x00, 0x00),
  358. 0x34 => array(0xff, 0x99, 0x00, 0x00),
  359. 0x35 => array(0xff, 0x66, 0x00, 0x00),
  360. 0x36 => array(0x66, 0x66, 0x99, 0x00),
  361. 0x37 => array(0x96, 0x96, 0x96, 0x00),
  362. 0x38 => array(0x00, 0x33, 0x66, 0x00),
  363. 0x39 => array(0x33, 0x99, 0x66, 0x00),
  364. 0x3A => array(0x00, 0x33, 0x00, 0x00),
  365. 0x3B => array(0x33, 0x33, 0x00, 0x00),
  366. 0x3C => array(0x99, 0x33, 0x00, 0x00),
  367. 0x3D => array(0x99, 0x33, 0x66, 0x00),
  368. 0x3E => array(0x33, 0x33, 0x99, 0x00),
  369. 0x3F => array(0x33, 0x33, 0x33, 0x00),
  370. );
  371. }
  372. /**
  373. * Assemble worksheets into a workbook and send the BIFF data to an OLE
  374. * storage.
  375. *
  376. * @param array $pWorksheetSizes The sizes in bytes of the binary worksheet streams
  377. * @return string Binary data for workbook stream
  378. */
  379. public function writeWorkbook($pWorksheetSizes = null)
  380. {
  381. $this->_worksheetSizes = $pWorksheetSizes;
  382. // Calculate the number of selected worksheet tabs and call the finalization
  383. // methods for each worksheet
  384. $total_worksheets = $this->_phpExcel->getSheetCount();
  385. // Add part 1 of the Workbook globals, what goes before the SHEET records
  386. $this->_storeBof(0x0005);
  387. $this->_writeCodepage();
  388. $this->_writeWindow1();
  389. $this->_writeDatemode();
  390. $this->_writeAllFonts();
  391. $this->_writeAllNumFormats();
  392. $this->_writeAllXfs();
  393. $this->_writeAllStyles();
  394. $this->_writePalette();
  395. // Prepare part 3 of the workbook global stream, what goes after the SHEET records
  396. $part3 = '';
  397. if ($this->_country_code != -1) {
  398. $part3 .= $this->_writeCountry();
  399. }
  400. $part3 .= $this->_writeRecalcId();
  401. $part3 .= $this->_writeSupbookInternal();
  402. /* TODO: store external SUPBOOK records and XCT and CRN records
  403. in case of external references for BIFF8 */
  404. $part3 .= $this->_writeExternsheetBiff8();
  405. $part3 .= $this->_writeAllDefinedNamesBiff8();
  406. $part3 .= $this->_writeMsoDrawingGroup();
  407. $part3 .= $this->_writeSharedStringsTable();
  408. $part3 .= $this->writeEof();
  409. // Add part 2 of the Workbook globals, the SHEET records
  410. $this->_calcSheetOffsets();
  411. for ($i = 0; $i < $total_worksheets; ++$i) {
  412. $this->_writeBoundsheet($this->_phpExcel->getSheet($i), $this->_worksheetOffsets[$i]);
  413. }
  414. // Add part 3 of the Workbook globals
  415. $this->_data .= $part3;
  416. return $this->_data;
  417. }
  418. /**
  419. * Calculate offsets for Worksheet BOF records.
  420. *
  421. * @access private
  422. */
  423. function _calcSheetOffsets()
  424. {
  425. $boundsheet_length = 10; // fixed length for a BOUNDSHEET record
  426. // size of Workbook globals part 1 + 3
  427. $offset = $this->_datasize;
  428. // add size of Workbook globals part 2, the length of the SHEET records
  429. $total_worksheets = count($this->_phpExcel->getAllSheets());
  430. foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) {
  431. $offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
  432. }
  433. // add the sizes of each of the Sheet substreams, respectively
  434. for ($i = 0; $i < $total_worksheets; ++$i) {
  435. $this->_worksheetOffsets[$i] = $offset;
  436. $offset += $this->_worksheetSizes[$i];
  437. }
  438. $this->_biffsize = $offset;
  439. }
  440. /**
  441. * Store the Excel FONT records.
  442. */
  443. private function _writeAllFonts()
  444. {
  445. foreach ($this->_fontWriters as $fontWriter) {
  446. $this->_append($fontWriter->writeFont());
  447. }
  448. }
  449. /**
  450. * Store user defined numerical formats i.e. FORMAT records
  451. */
  452. private function _writeAllNumFormats()
  453. {
  454. foreach ($this->_numberFormats as $numberFormatIndex => $numberFormat) {
  455. $this->_writeNumFormat($numberFormat->getFormatCode(), $numberFormatIndex);
  456. }
  457. }
  458. /**
  459. * Write all XF records.
  460. */
  461. private function _writeAllXfs()
  462. {
  463. foreach ($this->_xfWriters as $xfWriter) {
  464. $this->_append($xfWriter->writeXf());
  465. }
  466. }
  467. /**
  468. * Write all STYLE records.
  469. */
  470. private function _writeAllStyles()
  471. {
  472. $this->_writeStyle();
  473. }
  474. /**
  475. * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
  476. * the NAME records.
  477. */
  478. private function _writeExterns()
  479. {
  480. $countSheets = $this->_phpExcel->getSheetCount();
  481. // Create EXTERNCOUNT with number of worksheets
  482. $this->_writeExterncount($countSheets);
  483. // Create EXTERNSHEET for each worksheet
  484. for ($i = 0; $i < $countSheets; ++$i) {
  485. $this->_writeExternsheet($this->_phpExcel->getSheet($i)->getTitle());
  486. }
  487. }
  488. /**
  489. * Write the NAME record to define the print area and the repeat rows and cols.
  490. */
  491. private function _writeNames()
  492. {
  493. // total number of sheets
  494. $total_worksheets = $this->_phpExcel->getSheetCount();
  495. // Create the print area NAME records
  496. for ($i = 0; $i < $total_worksheets; ++$i) {
  497. $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
  498. // Write a Name record if the print area has been defined
  499. if ($sheetSetup->isPrintAreaSet()) {
  500. // Print area
  501. $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
  502. $printArea = $printArea[0];
  503. $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
  504. $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
  505. $print_rowmin = $printArea[0][1] - 1;
  506. $print_rowmax = $printArea[1][1] - 1;
  507. $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
  508. $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
  509. $this->_writeNameShort(
  510. $i, // sheet index
  511. 0x06, // NAME type
  512. $print_rowmin,
  513. $print_rowmax,
  514. $print_colmin,
  515. $print_colmax
  516. );
  517. }
  518. }
  519. // Create the print title NAME records
  520. for ($i = 0; $i < $total_worksheets; ++$i) {
  521. $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
  522. // simultaneous repeatColumns repeatRows
  523. if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
  524. $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
  525. $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
  526. $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
  527. $repeat = $sheetSetup->getRowsToRepeatAtTop();
  528. $rowmin = $repeat[0] - 1;
  529. $rowmax = $repeat[1] - 1;
  530. $this->_writeNameLong(
  531. $i, // sheet index
  532. 0x07, // NAME type
  533. $rowmin,
  534. $rowmax,
  535. $colmin,
  536. $colmax
  537. );
  538. // (exclusive) either repeatColumns or repeatRows
  539. } else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
  540. // Columns to repeat
  541. if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
  542. $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
  543. $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
  544. $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
  545. } else {
  546. $colmin = 0;
  547. $colmax = 255;
  548. }
  549. // Rows to repeat
  550. if ($sheetSetup->isRowsToRepeatAtTopSet()) {
  551. $repeat = $sheetSetup->getRowsToRepeatAtTop();
  552. $rowmin = $repeat[0] - 1;
  553. $rowmax = $repeat[1] - 1;
  554. } else {
  555. $rowmin = 0;
  556. $rowmax = 16383;
  557. }
  558. $this->_writeNameShort(
  559. $i, // sheet index
  560. 0x07, // NAME type
  561. $rowmin,
  562. $rowmax,
  563. $colmin,
  564. $colmax
  565. );
  566. }
  567. }
  568. }
  569. /**
  570. * Writes all the DEFINEDNAME records (BIFF8).
  571. * So far this is only used for repeating rows/columns (print titles) and print areas
  572. */
  573. private function _writeAllDefinedNamesBiff8()
  574. {
  575. $chunk = '';
  576. // Named ranges
  577. if (count($this->_phpExcel->getNamedRanges()) > 0) {
  578. // Loop named ranges
  579. $namedRanges = $this->_phpExcel->getNamedRanges();
  580. foreach ($namedRanges as $namedRange) {
  581. // Create absolute coordinate
  582. $range = PHPExcel_Cell::splitRange($namedRange->getRange());
  583. for ($i = 0; $i < count($range); $i++) {
  584. $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
  585. if (isset($range[$i][1])) {
  586. $range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
  587. }
  588. }
  589. $range = PHPExcel_Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2
  590. // parse formula
  591. try {
  592. $error = $this->_parser->parse($range);
  593. $formulaData = $this->_parser->toReversePolish();
  594. // make sure tRef3d is of type tRef3dR (0x3A)
  595. if (isset($formulaData{0}) and ($formulaData{0} == "\x7A" or $formulaData{0} == "\x5A")) {
  596. $formulaData = "\x3A" . substr($formulaData, 1);
  597. }
  598. if ($namedRange->getLocalOnly()) {
  599. // local scope
  600. $scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1;
  601. } else {
  602. // global scope
  603. $scope = 0;
  604. }
  605. $chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
  606. } catch(Exception $e) {
  607. // do nothing
  608. }
  609. }
  610. }
  611. // total number of sheets
  612. $total_worksheets = $this->_phpExcel->getSheetCount();
  613. // write the print titles (repeating rows, columns), if any
  614. for ($i = 0; $i < $total_worksheets; ++$i) {
  615. $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
  616. // simultaneous repeatColumns repeatRows
  617. if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
  618. $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
  619. $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
  620. $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
  621. $repeat = $sheetSetup->getRowsToRepeatAtTop();
  622. $rowmin = $repeat[0] - 1;
  623. $rowmax = $repeat[1] - 1;
  624. // construct formula data manually
  625. $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc
  626. $formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d
  627. $formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d
  628. $formulaData .= pack('C', 0x10); // tList
  629. // store the DEFINEDNAME record
  630. $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
  631. // (exclusive) either repeatColumns or repeatRows
  632. } else if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
  633. // Columns to repeat
  634. if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
  635. $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
  636. $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
  637. $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
  638. } else {
  639. $colmin = 0;
  640. $colmax = 255;
  641. }
  642. // Rows to repeat
  643. if ($sheetSetup->isRowsToRepeatAtTopSet()) {
  644. $repeat = $sheetSetup->getRowsToRepeatAtTop();
  645. $rowmin = $repeat[0] - 1;
  646. $rowmax = $repeat[1] - 1;
  647. } else {
  648. $rowmin = 0;
  649. $rowmax = 65535;
  650. }
  651. // construct formula data manually because parser does not recognize absolute 3d cell references
  652. $formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax);
  653. // store the DEFINEDNAME record
  654. $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true));
  655. }
  656. }
  657. // write the print areas, if any
  658. for ($i = 0; $i < $total_worksheets; ++$i) {
  659. $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
  660. if ($sheetSetup->isPrintAreaSet()) {
  661. // Print area, e.g. A3:J6,H1:X20
  662. $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
  663. $countPrintArea = count($printArea);
  664. $formulaData = '';
  665. for ($j = 0; $j < $countPrintArea; ++$j) {
  666. $printAreaRect = $printArea[$j]; // e.g. A3:J6
  667. $printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]);
  668. $printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]);
  669. $print_rowmin = $printAreaRect[0][1] - 1;
  670. $print_rowmax = $printAreaRect[1][1] - 1;
  671. $print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
  672. $print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
  673. // construct formula data manually because parser does not recognize absolute 3d cell references
  674. $formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
  675. if ($j > 0) {
  676. $formulaData .= pack('C', 0x10); // list operator token ','
  677. }
  678. }
  679. // store the DEFINEDNAME record
  680. $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true));
  681. }
  682. }
  683. // write autofilters, if any
  684. for ($i = 0; $i < $total_worksheets; ++$i) {
  685. $sheetAutoFilter = $this->_phpExcel->getSheet($i)->getAutoFilter();
  686. $autoFilterRange = $sheetAutoFilter->getRange();
  687. if(!empty($autoFilterRange)) {
  688. $rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
  689. //Autofilter built in name
  690. $name = pack('C', 0x0D);
  691. $chunk .= $this->writeData($this->_writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
  692. }
  693. }
  694. return $chunk;
  695. }
  696. /**
  697. * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data
  698. *
  699. * @param string $name The name in UTF-8
  700. * @param string $formulaData The binary formula data
  701. * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global
  702. * @param boolean $isBuiltIn Built-in name?
  703. * @return string Complete binary record data
  704. */
  705. private function _writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false)
  706. {
  707. $record = 0x0018;
  708. // option flags
  709. $options = $isBuiltIn ? 0x20 : 0x00;
  710. // length of the name, character count
  711. $nlen = PHPExcel_Shared_String::CountCharacters($name);
  712. // name with stripped length field
  713. $name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2);
  714. // size of the formula (in bytes)
  715. $sz = strlen($formulaData);
  716. // combine the parts
  717. $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
  718. . $name . $formulaData;
  719. $length = strlen($data);
  720. $header = pack('vv', $record, $length);
  721. return $header . $data;
  722. }
  723. /**
  724. * Write a short NAME record
  725. *
  726. * @param string $name
  727. * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global
  728. * @param int[][] $range rangeboundaries
  729. * @param bool $isHidden
  730. * @return string Complete binary record data
  731. * */
  732. private function _writeShortNameBiff8($name, $sheetIndex = 0, $rangeBounds, $isHidden = false){
  733. $record = 0x0018;
  734. // option flags
  735. $options = ($isHidden ? 0x21 : 0x00);
  736. $extra = pack('Cvvvvv',
  737. 0x3B,
  738. $sheetIndex - 1,
  739. $rangeBounds[0][1] - 1,
  740. $rangeBounds[1][1] - 1,
  741. $rangeBounds[0][0] - 1,
  742. $rangeBounds[1][0] - 1);
  743. // size of the formula (in bytes)
  744. $sz = strlen($extra);
  745. // combine the parts
  746. $data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
  747. . $name . $extra;
  748. $length = strlen($data);
  749. $header = pack('vv', $record, $length);
  750. return $header . $data;
  751. }
  752. /**
  753. * Stores the CODEPAGE biff record.
  754. */
  755. private function _writeCodepage()
  756. {
  757. $record = 0x0042; // Record identifier
  758. $length = 0x0002; // Number of bytes to follow
  759. $cv = $this->_codepage; // The code page
  760. $header = pack('vv', $record, $length);
  761. $data = pack('v', $cv);
  762. $this->_append($header . $data);
  763. }
  764. /**
  765. * Write Excel BIFF WINDOW1 record.
  766. */
  767. private function _writeWindow1()
  768. {
  769. $record = 0x003D; // Record identifier
  770. $length = 0x0012; // Number of bytes to follow
  771. $xWn = 0x0000; // Horizontal position of window
  772. $yWn = 0x0000; // Vertical position of window
  773. $dxWn = 0x25BC; // Width of window
  774. $dyWn = 0x1572; // Height of window
  775. $grbit = 0x0038; // Option flags
  776. // not supported by PHPExcel, so there is only one selected sheet, the active
  777. $ctabsel = 1; // Number of workbook tabs selected
  778. $wTabRatio = 0x0258; // Tab to scrollbar ratio
  779. // not supported by PHPExcel, set to 0
  780. $itabFirst = 0; // 1st displayed worksheet
  781. $itabCur = $this->_phpExcel->getActiveSheetIndex(); // Active worksheet
  782. $header = pack("vv", $record, $length);
  783. $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
  784. $grbit,
  785. $itabCur, $itabFirst,
  786. $ctabsel, $wTabRatio);
  787. $this->_append($header . $data);
  788. }
  789. /**
  790. * Writes Excel BIFF BOUNDSHEET record.
  791. *
  792. * @param PHPExcel_Worksheet $sheet Worksheet name
  793. * @param integer $offset Location of worksheet BOF
  794. */
  795. private function _writeBoundsheet($sheet, $offset)
  796. {
  797. $sheetname = $sheet->getTitle();
  798. $record = 0x0085; // Record identifier
  799. // sheet state
  800. switch ($sheet->getSheetState()) {
  801. case PHPExcel_Worksheet::SHEETSTATE_VISIBLE: $ss = 0x00; break;
  802. case PHPExcel_Worksheet::SHEETSTATE_HIDDEN: $ss = 0x01; break;
  803. case PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN: $ss = 0x02; break;
  804. default: $ss = 0x00; break;
  805. }
  806. // sheet type
  807. $st = 0x00;
  808. $grbit = 0x0000; // Visibility and sheet type
  809. $data = pack("VCC", $offset, $ss, $st);
  810. $data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheetname);
  811. $length = strlen($data);
  812. $header = pack("vv", $record, $length);
  813. $this->_append($header . $data);
  814. }
  815. /**
  816. * Write Internal SUPBOOK record
  817. */
  818. private function _writeSupbookInternal()
  819. {
  820. $record = 0x01AE; // Record identifier
  821. $length = 0x0004; // Bytes to follow
  822. $header = pack("vv", $record, $length);
  823. $data = pack("vv", $this->_phpExcel->getSheetCount(), 0x0401);
  824. return $this->writeData($header . $data);
  825. }
  826. /**
  827. * Writes the Excel BIFF EXTERNSHEET record. These references are used by
  828. * formulas.
  829. *
  830. */
  831. private function _writeExternsheetBiff8()
  832. {
  833. $total_references = count($this->_parser->_references);
  834. $record = 0x0017; // Record identifier
  835. $length = 2 + 6 * $total_references; // Number of bytes to follow
  836. $supbook_index = 0; // FIXME: only using internal SUPBOOK record
  837. $header = pack("vv", $record, $length);
  838. $data = pack('v', $total_references);
  839. for ($i = 0; $i < $total_references; ++$i) {
  840. $data .= $this->_parser->_references[$i];
  841. }
  842. return $this->writeData($header . $data);
  843. }
  844. /**
  845. * Write Excel BIFF STYLE records.
  846. */
  847. private function _writeStyle()
  848. {
  849. $record = 0x0293; // Record identifier
  850. $length = 0x0004; // Bytes to follow
  851. $ixfe = 0x8000; // Index to cell style XF
  852. $BuiltIn = 0x00; // Built-in style
  853. $iLevel = 0xff; // Outline style level
  854. $header = pack("vv", $record, $length);
  855. $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
  856. $this->_append($header . $data);
  857. }
  858. /**
  859. * Writes Excel FORMAT record for non "built-in" numerical formats.
  860. *
  861. * @param string $format Custom format string
  862. * @param integer $ifmt Format index code
  863. */
  864. private function _writeNumFormat($format, $ifmt)
  865. {
  866. $record = 0x041E; // Record identifier
  867. $numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format);
  868. $length = 2 + strlen($numberFormatString); // Number of bytes to follow
  869. $header = pack("vv", $record, $length);
  870. $data = pack("v", $ifmt) . $numberFormatString;
  871. $this->_append($header . $data);
  872. }
  873. /**
  874. * Write DATEMODE record to indicate the date system in use (1904 or 1900).
  875. */
  876. private function _writeDatemode()
  877. {
  878. $record = 0x0022; // Record identifier
  879. $length = 0x0002; // Bytes to follow
  880. $f1904 = (PHPExcel_Shared_Date::getExcelCalendar() == PHPExcel_Shared_Date::CALENDAR_MAC_1904) ?
  881. 1 : 0; // Flag for 1904 date system
  882. $header = pack("vv", $record, $length);
  883. $data = pack("v", $f1904);
  884. $this->_append($header . $data);
  885. }
  886. /**
  887. * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
  888. * references in the workbook.
  889. *
  890. * Excel only stores references to external sheets that are used in NAME.
  891. * The workbook NAME record is required to define the print area and the repeat
  892. * rows and columns.
  893. *
  894. * A similar method is used in Worksheet.php for a slightly different purpose.
  895. *
  896. * @param integer $cxals Number of external references
  897. */
  898. private function _writeExterncount($cxals)
  899. {
  900. $record = 0x0016; // Record identifier
  901. $length = 0x0002; // Number of bytes to follow
  902. $header = pack("vv", $record, $length);
  903. $data = pack("v", $cxals);
  904. $this->_append($header . $data);
  905. }
  906. /**
  907. * Writes the Excel BIFF EXTERNSHEET record. These references are used by
  908. * formulas. NAME record is required to define the print area and the repeat
  909. * rows and columns.
  910. *
  911. * A similar method is used in Worksheet.php for a slightly different purpose.
  912. *
  913. * @param string $sheetname Worksheet name
  914. */
  915. private function _writeExternsheet($sheetname)
  916. {
  917. $record = 0x0017; // Record identifier
  918. $length = 0x02 + strlen($sheetname); // Number of bytes to follow
  919. $cch = strlen($sheetname); // Length of sheet name
  920. $rgch = 0x03; // Filename encoding
  921. $header = pack("vv", $record, $length);
  922. $data = pack("CC", $cch, $rgch);
  923. $this->_append($header . $data . $sheetname);
  924. }
  925. /**
  926. * Store the NAME record in the short format that is used for storing the print
  927. * area, repeat rows only and repeat columns only.
  928. *
  929. * @param integer $index Sheet index
  930. * @param integer $type Built-in name type
  931. * @param integer $rowmin Start row
  932. * @param integer $rowmax End row
  933. * @param integer $colmin Start colum
  934. * @param integer $colmax End column
  935. */
  936. private function _writeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax)
  937. {
  938. $record = 0x0018; // Record identifier
  939. $length = 0x0024; // Number of bytes to follow
  940. $grbit = 0x0020; // Option flags
  941. $chKey = 0x00; // Keyboard shortcut
  942. $cch = 0x01; // Length of text name
  943. $cce = 0x0015; // Length of text definition
  944. $ixals = $index + 1; // Sheet index
  945. $itab = $ixals; // Equal to ixals
  946. $cchCustMenu = 0x00; // Length of cust menu text
  947. $cchDescription = 0x00; // Length of description text
  948. $cchHelptopic = 0x00; // Length of help topic text
  949. $cchStatustext = 0x00; // Length of status bar text
  950. $rgch = $type; // Built-in name type
  951. $unknown03 = 0x3b;
  952. $unknown04 = 0xffff-$index;
  953. $unknown05 = 0x0000;
  954. $unknown06 = 0x0000;
  955. $unknown07 = 0x1087;
  956. $unknown08 = 0x8005;
  957. $header = pack("vv", $record, $length);
  958. $data = pack("v", $grbit);
  959. $data .= pack("C", $chKey);
  960. $data .= pack("C", $cch);
  961. $data .= pack("v", $cce);
  962. $data .= pack("v", $ixals);
  963. $data .= pack("v", $itab);
  964. $data .= pack("C", $cchCustMenu);
  965. $data .= pack("C", $cchDescription);
  966. $data .= pack("C", $cchHelptopic);
  967. $data .= pack("C", $cchStatustext);
  968. $data .= pack("C", $rgch);
  969. $data .= pack("C", $unknown03);
  970. $data .= pack("v", $unknown04);
  971. $data .= pack("v", $unknown05);
  972. $data .= pack("v", $unknown06);
  973. $data .= pack("v", $unknown07);
  974. $data .= pack("v", $unknown08);
  975. $data .= pack("v", $index);
  976. $data .= pack("v", $index);
  977. $data .= pack("v", $rowmin);
  978. $data .= pack("v", $rowmax);
  979. $data .= pack("C", $colmin);
  980. $data .= pack("C", $colmax);
  981. $this->_append($header . $data);
  982. }
  983. /**
  984. * Store the NAME record in the long format that is used for storing the repeat
  985. * rows and columns when both are specified. This shares a lot of code with
  986. * _writeNameShort() but we use a separate method to keep the code clean.
  987. * Code abstraction for reuse can be carried too far, and I should know. ;-)
  988. *
  989. * @param integer $index Sheet index
  990. * @param integer $type Built-in name type
  991. * @param integer $rowmin Start row
  992. * @param integer $rowmax End row
  993. * @param integer $colmin Start colum
  994. * @param integer $colmax End column
  995. */
  996. private function _writeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax)
  997. {
  998. $record = 0x0018; // Record identifier
  999. $length = 0x003d; // Number of bytes to follow
  1000. $grbit = 0x0020; // Option flags
  1001. $chKey = 0x00; // Keyboard shortcut
  1002. $cch = 0x01; // Length of text name
  1003. $cce = 0x002e; // Length of text definition
  1004. $ixals = $index + 1; // Sheet index
  1005. $itab = $ixals; // Equal to ixals
  1006. $cchCustMenu = 0x00; // Length of cust menu text
  1007. $cchDescription = 0x00; // Length of description text
  1008. $cchHelptopic = 0x00; // Length of help topic text
  1009. $cchStatustext = 0x00; // Length of status bar text
  1010. $rgch = $type; // Built-in name type
  1011. $unknown01 = 0x29;
  1012. $unknown02 = 0x002b;
  1013. $unknown03 = 0x3b;
  1014. $unknown04 = 0xffff-$index;
  1015. $unknown05 = 0x0000;
  1016. $unknown06 = 0x0000;
  1017. $unknown07 = 0x1087;
  1018. $unknown08 = 0x8008;
  1019. $header = pack("vv", $record, $length);
  1020. $data = pack("v", $grbit);
  1021. $data .= pack("C", $chKey);
  1022. $data .= pack("C", $cch);
  1023. $data .= pack("v", $cce);
  1024. $data .= pack("v", $ixals);
  1025. $data .= pack("v", $itab);
  1026. $data .= pack("C", $cchCustMenu);
  1027. $data .= pack("C", $cchDescription);
  1028. $data .= pack("C", $cchHelptopic);
  1029. $data .= pack("C", $cchStatustext);
  1030. $data .= pack("C", $rgch);
  1031. $data .= pack("C", $unknown01);
  1032. $data .= pack("v", $unknown02);
  1033. // Column definition
  1034. $data .= pack("C", $unknown03);
  1035. $data .= pack("v", $unknown04);
  1036. $data .= pack("v", $unknown05);
  1037. $data .= pack("v", $unknown06);
  1038. $data .= pack("v", $unknown07);
  1039. $data .= pack("v", $unknown08);
  1040. $data .= pack("v", $index);
  1041. $data .= pack("v", $index);
  1042. $data .= pack("v", 0x0000);
  1043. $data .= pack("v", 0x3fff);
  1044. $data .= pack("C", $colmin);
  1045. $data .= pack("C", $colmax);
  1046. // Row definition
  1047. $data .= pack("C", $unknown03);
  1048. $data .= pack("v", $unknown04);
  1049. $data .= pack("v", $unknown05);
  1050. $data .= pack("v", $unknown06);
  1051. $data .= pack("v", $unknown07);
  1052. $data .= pack("v", $unknown08);
  1053. $data .= pack("v", $index);
  1054. $data .= pack("v", $index);
  1055. $data .= pack("v", $rowmin);
  1056. $data .= pack("v", $rowmax);
  1057. $data .= pack("C", 0x00);
  1058. $data .= pack("C", 0xff);
  1059. // End of data
  1060. $data .= pack("C", 0x10);
  1061. $this->_append($header . $data);
  1062. }
  1063. /**
  1064. * Stores the COUNTRY record for localization
  1065. *
  1066. * @return string
  1067. */
  1068. private function _writeCountry()
  1069. {
  1070. $record = 0x008C; // Record identifier
  1071. $length = 4; // Number of bytes to follow
  1072. $header = pack('vv', $record, $length);
  1073. /* using the same country code always for simplicity */
  1074. $data = pack('vv', $this->_country_code, $this->_country_code);
  1075. //$this->_append($header . $data);
  1076. return $this->writeData($header . $data);
  1077. }
  1078. /**
  1079. * Write the RECALCID record
  1080. *
  1081. * @return string
  1082. */
  1083. private function _writeRecalcId()
  1084. {
  1085. $record = 0x01C1; // Record identifier
  1086. $length = 8; // Number of bytes to follow
  1087. $header = pack('vv', $record, $length);
  1088. // by inspection of real Excel files, MS Office Excel 2007 writes this
  1089. $data = pack('VV', 0x000001C1, 0x00001E667);
  1090. return $this->writeData($header . $data);
  1091. }
  1092. /**
  1093. * Stores the PALETTE biff record.
  1094. */
  1095. private function _writePalette()
  1096. {
  1097. $aref = $this->_palette;
  1098. $record = 0x0092; // Record identifier
  1099. $length = 2 + 4 * count($aref); // Number of bytes to follow
  1100. $ccv = count($aref); // Number of RGB values to follow
  1101. $data = ''; // The RGB data
  1102. // Pack the RGB data
  1103. foreach ($aref as $color) {
  1104. foreach ($color as $byte) {
  1105. $data .= pack("C",$byte);
  1106. }
  1107. }
  1108. $header = pack("vvv", $record, $length, $ccv);
  1109. $this->_append($header . $data);
  1110. }
  1111. /**
  1112. * Handling of the SST continue blocks is complicated by the need to include an
  1113. * additional continuation byte depending on whether the string is split between
  1114. * blocks or whether it starts at the beginning of the block. (There are also
  1115. * additional complications that will arise later when/if Rich Strings are
  1116. * supported).
  1117. *
  1118. * The Excel documentation says that the SST record should be followed by an
  1119. * EXTSST record. The EXTSST record is a hash table that is used to optimise
  1120. * access to SST. However, despite the documentation it doesn't seem to be
  1121. * required so we will ignore it.
  1122. *
  1123. * @return string Binary data
  1124. */
  1125. private function _writeSharedStringsTable()
  1126. {
  1127. // maximum size of record data (excluding record header)
  1128. $continue_limit = 8224;
  1129. // initialize array of record data blocks
  1130. $recordDatas = array();
  1131. // start SST record data block with total number of strings, total number of unique strings
  1132. $recordData = pack("VV", $this->_str_total, $this->_str_unique);
  1133. // loop through all (unique) strings in shared strings table
  1134. foreach (array_keys($this->_str_table) as $string) {
  1135. // here $string is a BIFF8 encoded string
  1136. // length = character count
  1137. $headerinfo = unpack("vlength/Cencoding", $string);
  1138. // currently, this is always 1 = uncompressed
  1139. $encoding = $headerinfo["encoding"];
  1140. // initialize finished writing current $string
  1141. $finished = false;
  1142. while ($finished === false) {
  1143. // normally, there will be only one cycle, but if string cannot immediately be written as is
  1144. // there will be need for more than one cylcle, if string longer than one record data block, there
  1145. // may be need for even more cycles
  1146. if (strlen($recordData) + strlen($string) <= $continue_limit) {
  1147. // then we can write the string (or remainder of string) without any problems
  1148. $recordData .= $string;
  1149. if (strlen($recordData) + strlen($string) == $continue_limit) {
  1150. // we close the record data block, and initialize a new one
  1151. $recordDatas[] = $recordData;
  1152. $recordData = '';
  1153. }
  1154. // we are finished writing this string
  1155. $finished = true;
  1156. } else {
  1157. // special treatment writing the string (or remainder of the string)
  1158. // If the string is very long it may need to be written in more than one CONTINUE record.
  1159. // check how many bytes more there is room for in the current record
  1160. $space_remaining = $continue_limit - strlen($recordData);
  1161. // minimum space needed
  1162. // uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character
  1163. // compressed: 2 byte string length length field + 1 byte option flags + 1 byte character
  1164. $min_space_needed = ($encoding == 1) ? 5 : 4;
  1165. // We have two cases
  1166. // 1. space remaining is less than minimum space needed
  1167. // here we must waste the space remaining and move to next record data block
  1168. // 2. space remaining is greater than or equal to minimum space needed
  1169. // here we write as much as we can in the current block, then move to next record data block
  1170. // 1. space remaining is less than minimum space needed
  1171. if ($space_remaining < $min_space_needed) {
  1172. // we close the block, store the block data
  1173. $recordDatas[] = $recordData;
  1174. // and start new record data block where we start writing the string
  1175. $recordData = '';
  1176. // 2. space remaining is greater than or equal to minimum space needed
  1177. } else {
  1178. // initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below
  1179. $effective_space_remaining = $space_remaining;
  1180. // for uncompressed strings, sometimes effective space remaining is reduced by 1
  1181. if ( $encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1 ) {
  1182. --$effective_space_remaining;
  1183. }
  1184. // one block fininshed, store the block data
  1185. $recordData .= substr($string, 0, $effective_space_remaining);
  1186. $string = substr($string, $effective_space_remaining); // for next cycle in while loop
  1187. $recordDatas[] = $recordData;
  1188. // start new record data block with the repeated option flags
  1189. $recordData = pack('C', $encoding);
  1190. }
  1191. }
  1192. }
  1193. }
  1194. // Store the last record data block unless it is empty
  1195. // if there was no need for any continue records, this will be the for SST record data block itself
  1196. if (strlen($recordData) > 0) {
  1197. $recordDatas[] = $recordData;
  1198. }
  1199. // combine into one chunk with all the blocks SST, CONTINUE,...
  1200. $chunk = '';
  1201. foreach ($recordDatas as $i => $recordData) {
  1202. // first block should have the SST record header, remaing should have CONTINUE header
  1203. $record = ($i == 0) ? 0x00FC : 0x003C;
  1204. $header = pack("vv", $record, strlen($recordData));
  1205. $data = $header . $recordData;
  1206. $chunk .= $this->writeData($data);
  1207. }
  1208. return $chunk;
  1209. }
  1210. /**
  1211. * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records.
  1212. */
  1213. private function _writeMsoDrawingGroup()
  1214. {
  1215. // write the Escher stream if necessary
  1216. if (isset($this->_escher)) {
  1217. $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
  1218. $data = $writer->close();
  1219. $record = 0x00EB;
  1220. $length = strlen($data);
  1221. $header = pack("vv", $record, $length);
  1222. return $this->writeData($header . $data);
  1223. } else {
  1224. return '';
  1225. }
  1226. }
  1227. /**
  1228. * Get Escher object
  1229. *
  1230. * @return PHPExcel_Shared_Escher
  1231. */
  1232. public function getEscher()
  1233. {
  1234. return $this->_escher;
  1235. }
  1236. /**
  1237. * Set Escher object
  1238. *
  1239. * @param PHPExcel_Shared_Escher $pValue
  1240. */
  1241. public function setEscher(PHPExcel_Shared_Escher $pValue = null)
  1242. {
  1243. $this->_escher = $pValue;
  1244. }
  1245. }