File Source for Worksheet.php

PHPExcel_Writer_Excel5

# Source for file Worksheet.php

Documentation is available at Worksheet.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
28. // Original file header of PEAR::Spreadsheet_Excel_Writer_Worksheet (used as the base for this class):
29. // -----------------------------------------------------------------------------------------
30. // /*
31. // * Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
32. // *
33. // * The majority of this is _NOT_ my code. I simply ported it from the
34. // * PERL Spreadsheet::WriteExcel module.
35. // *
36. // * The author of the Spreadsheet::WriteExcel module is John McNamara
37. // * <jmcnamara@cpan.org>
38. // *
39. // * I _DO_ maintain this code, and John McNamara has nothing to do with the
40. // * porting of this code to PHP. Any questions directly related to this
41. // * class library should be directed to me.
42. // *
43. // * License Information:
44. // *
45. // * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
46. // * Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
47. // *
48. // * This library is free software; you can redistribute it and/or
49. // * modify it under the terms of the GNU Lesser General Public
50. // * License as published by the Free Software Foundation; either
51. // * version 2.1 of the License, or (at your option) any later version.
52. // *
53. // * This library is distributed in the hope that it will be useful,
54. // * but WITHOUT ANY WARRANTY; without even the implied warranty of
55. // * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
56. // * Lesser General Public License for more details.
57. // *
58. // * You should have received a copy of the GNU Lesser General Public
59. // * License along with this library; if not, write to the Free Software
60. // * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
61. // */
64. /**
65. * PHPExcel_Writer_Excel5_Worksheet
66. *
67. * @category PHPExcel
68. * @package PHPExcel_Writer_Excel5
69. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
70. */
71. class PHPExcel_Writer_Excel5_Worksheet extends PHPExcel_Writer_Excel5_BIFFwriter
72. {
73. /**
74. * Formula parser
75. *
76. * @var PHPExcel_Writer_Excel5_Parser
77. */
78. private $_parser;
80. /**
81. * Maximum number of characters for a string (LABEL record in BIFF5)
82. * @var integer
83. */
84. public $_xls_strmax;
86. /**
87. * Array containing format information for columns
88. * @var array
89. */
90. public $_colinfo;
92. /**
93. * Array containing the selected area for the worksheet
94. * @var array
95. */
96. public $_selection;
98. /**
99. * The active pane for the worksheet
100. * @var integer
101. */
102. public $_active_pane;
104. /**
105. * Whether to use outline.
106. * @var integer
107. */
108. public $_outline_on;
110. /**
111. * Auto outline styles.
112. * @var bool
113. */
114. public $_outline_style;
116. /**
117. * Whether to have outline summary below.
118. * @var bool
119. */
120. public $_outline_below;
122. /**
123. * Whether to have outline summary at the right.
124. * @var bool
125. */
126. public $_outline_right;
128. /**
129. * Reference to the total number of strings in the workbook
130. * @var integer
131. */
132. public $_str_total;
134. /**
135. * Reference to the number of unique strings in the workbook
136. * @var integer
137. */
138. public $_str_unique;
140. /**
141. * Reference to the array containing all the unique strings in the workbook
142. * @var array
143. */
144. public $_str_table;
146. /**
147. * Color cache
148. */
149. private $_colors;
151. /**
152. * Index of first used row (at least 0)
153. * @var int
154. */
155. private $_firstRowIndex;
157. /**
158. * Index of last used row. (no used rows means -1)
159. * @var int
160. */
161. private $_lastRowIndex;
163. /**
164. * Index of first used column (at least 0)
165. * @var int
166. */
167. private $_firstColumnIndex;
169. /**
170. * Index of last used column (no used columns means -1)
171. * @var int
172. */
173. private $_lastColumnIndex;
175. /**
176. * Sheet object
177. * @var PHPExcel_Worksheet
178. */
179. private $_phpSheet;
181. /**
182. * Count cell style Xfs
183. *
184. * @var int
185. */
186. private $_countCellStyleXfs;
188. /**
189. * Escher object corresponding to MSODRAWING
190. *
191. * @var PHPExcel_Shared_Escher
192. */
193. private $_escher;
195. /**
196. * Constructor
197. *
198. * @param int $BIFF_version BIFF version
199. * @param int $str_total Total number of strings
200. * @param int $str_unique Total number of unique strings
201. * @param array $str_table
202. * @param mixed $parser The formula parser created for the Workbook
203. * @param string $tempDir The temporary directory to be used
204. * @param PHPExcel_Worksheet $phpSheet
205. */
206. public function __construct($BIFF_version,
207. &$str_total,
208. &$str_unique, &$str_table, &$colors,
209. $parser, $preCalculateFormulas, $phpSheet)
210. {
211. // It needs to call its parent's constructor explicitly
212. parent::__construct();
214. $this->_BIFF_version = $BIFF_version;
215. if ($BIFF_version == 0x0600) {
216. // change BIFFwriter limit for CONTINUE records
217. $this->_limit = 8224;
218. }
221. $this->_preCalculateFormulas = $preCalculateFormulas;
222. $this->_str_total = &$str_total;
223. $this->_str_unique = &$str_unique;
224. $this->_str_table = &$str_table;
225. $this->_colors = &$colors;
226. $this->_parser = $parser;
228. $this->_phpSheet = $phpSheet;
230. //$this->ext_sheets = array();
231. //$this->offset = 0;
232. $this->_xls_strmax = 255;
233. $this->_colinfo = array();
234. $this->_selection = array(0,0,0,0);
235. $this->_active_pane = 3;
237. $this->_print_headers = 0;
239. $this->_outline_style = 0;
240. $this->_outline_below = 1;
241. $this->_outline_right = 1;
242. $this->_outline_on = 1;
244. // calculate values for DIMENSIONS record
245. $col = $row = array();
246. foreach ($this->_phpSheet->getCellCollection(false) as $cellID) {
247. list($c,$r) = sscanf($cellID,'%[A-Z]%d');
248. $row[$r] = $r;
249. $col[$c] = strlen($c).$c;
250. }
251. // Determine lowest and highest column and row
252. $this->_firstRowIndex = ( count($row) > 0) ? min($row) : 1;
253. $this->_lastRowIndex = ( count($row) > 0) ? max($row) : 1;
254. if ($this->_firstRowIndex > 65535) $this->_firstRowIndex = 65535;
255. if ($this->_lastRowIndex > 65535) $this->_lastRowIndex = 65535;
257. $this->_firstColumnIndex = ( count($col) > 0) ? PHPExcel_Cell:: columnIndexFromString( substr( min($col),1)) : 1;
258. $this->_lastColumnIndex = ( count($col) > 0) ? PHPExcel_Cell:: columnIndexFromString( substr( max($col),1)) : 1;
260. if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255;
261. if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255;
263. $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
264. }
266. /**
267. * Add data to the beginning of the workbook (note the reverse order)
268. * and to the end of the workbook.
269. *
270. * @access public
271. * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
272. */
273. function close()
274. {
275. $num_sheets = $this->_phpSheet->getParent()->getSheetCount();
277. // Write BOF record
278. $this->_storeBof(0x0010);
280. // Write PRINTHEADERS
281. $this->_writePrintHeaders();
283. // Write PRINTGRIDLINES
284. $this->_writePrintGridlines();
286. // Write GRIDSET
287. $this->_writeGridset();
289. // Calculate column widths
290. $this->_phpSheet->calculateColumnWidths();
292. // Column dimensions
293. $maxCol = PHPExcel_Cell:: columnIndexFromString($this->_phpSheet->getHighestColumn()) -1;
294. $columnDimensions = $this->_phpSheet->getColumnDimensions();
295. for ($i = 0; $i <= $maxCol; ++$i) {
296. $hidden = 0;
297. $level = 0;
298. $xfIndex = 15; // there are 15 cell style Xfs
300. if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
301. $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
302. } else {
303. $width = PHPExcel_Shared_Font:: getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont());
304. }
306. $columnLetter = PHPExcel_Cell:: stringFromColumnIndex($i);
307. if (isset($columnDimensions[$columnLetter])) {
308. $columnDimension = $columnDimensions[$columnLetter];
309. if ($columnDimension->getWidth() >= 0) {
310. $width = $columnDimension->getWidth();
311. }
312. $hidden = $columnDimension->getVisible() ? 0 : 1;
313. $level = $columnDimension->getOutlineLevel();
314. $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
315. }
317. // Components of _colinfo:
318. // $firstcol first column on the range
319. // $lastcol last column on the range
320. // $width width to set
321. // $xfIndex The optional cell style Xf index to apply to the columns
322. // $hidden The optional hidden atribute
323. // $level The optional outline level
324. $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
325. }
327. // Write GUTS
328. $this->_writeGuts();
330. // Write DEFAULTROWHEIGHT
331. if ($this->_BIFF_version == 0x0600) {
332. $this->_writeDefaultRowHeight();
333. }
335. // Write WSBOOL
336. $this->_writeWsbool();
338. // Write horizontal and vertical page breaks
339. $this->_writeBreaks();
341. // Write page header
342. $this->_writeHeader();
344. // Write page footer
345. $this->_writeFooter();
347. // Write page horizontal centering
348. $this->_writeHcenter();
350. // Write page vertical centering
351. $this->_writeVcenter();
353. // Write left margin
354. $this->_writeMarginLeft();
356. // Write right margin
357. $this->_writeMarginRight();
359. // Write top margin
360. $this->_writeMarginTop();
362. // Write bottom margin
363. $this->_writeMarginBottom();
365. // Write page setup
366. $this->_writeSetup();
368. // Write sheet protection
369. $this->_writeProtect();
371. // Write SCENPROTECT
372. $this->_writeScenProtect();
374. // Write OBJECTPROTECT
375. $this->_writeObjectProtect();
377. // Write sheet password
378. $this->_writePassword();
380. // Write DEFCOLWIDTH record
381. $this->_writeDefcol();
383. // Write the COLINFO records if they exist
384. if (! empty($this->_colinfo)) {
385. $colcount = count($this->_colinfo);
386. for ($i = 0; $i < $colcount; ++$i) {
387. $this->_writeColinfo($this->_colinfo[$i]);
388. }
389. }
391. // Write EXTERNCOUNT of external references
392. if ($this->_BIFF_version == 0x0500) {
393. $this->_writeExterncount($num_sheets);
394. }
396. // Write EXTERNSHEET references
397. if ($this->_BIFF_version == 0x0500) {
398. for ($i = 0; $i < $num_sheets; ++$i) {
399. $this->_writeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
400. }
401. }
403. // Write sheet dimensions
404. $this->_writeDimensions();
406. // Row dimensions
407. foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
408. $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
409. $this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
410. }
412. // Write Cells
413. foreach ($this->_phpSheet->getCellCollection() as $cellID) {
414. $cell = $this->_phpSheet->getCell($cellID);
415. $row = $cell->getRow() - 1;
416. $column = PHPExcel_Cell:: columnIndexFromString($cell->getColumn()) - 1;
418. // Don't break Excel!
419. if ($row + 1 > 65536 or $column + 1 > 256) {
420. break;
421. }
423. // Write cell value
424. $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
426. if ($cell->getValue() instanceof PHPExcel_RichText) {
427. $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex);
428. } else {
429. switch ($cell->getDatatype()) {
431. case PHPExcel_Cell_DataType:: TYPE_STRING:
432. if ($cell->getValue() === '' or $cell->getValue() === null) {
433. $this->_writeBlank($row, $column, $xfIndex);
434. } else {
435. $this->_writeString($row, $column, $cell->getValue(), $xfIndex);
436. }
437. break;
439. case PHPExcel_Cell_DataType:: TYPE_FORMULA:
440. $calculatedValue = $this->_preCalculateFormulas ?
441. $cell->getCalculatedValue() : null;
442. $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue);
443. break;
445. case PHPExcel_Cell_DataType:: TYPE_BOOL:
446. $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex);
447. break;
449. case PHPExcel_Cell_DataType:: TYPE_ERROR:
450. $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex);
451. break;
453. case PHPExcel_Cell_DataType:: TYPE_NUMERIC:
454. $this->_writeNumber($row, $column, $cell->getValue(), $xfIndex);
455. break;
456. }
457. }
458. }
460. // Append
461. if ($this->_BIFF_version == 0x0600) {
462. $this->_writeMsoDrawing();
463. }
464. $this->_writeWindow2();
465. $this->_writeZoom();
466. if ($this->_phpSheet->getFreezePane()) {
467. $this->_writePanes();
468. }
469. $this->_writeSelection();
470. $this->_writeMergedCells();
472. // Hyperlinks
473. if ($this->_BIFF_version == 0x0600) {
474. foreach ($this->_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
475. list($column, $row) = PHPExcel_Cell:: coordinateFromString($coordinate);
477. $url = $hyperlink->getUrl();
479. if ( strpos($url, 'sheet://') !== false ) {
480. // internal to current workbook
481. $url = str_replace('sheet://', 'internal:', $url);
483. } else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) {
484. // URL
485. // $url = $url;
487. } else {
488. // external (local file)
489. $url = 'external:' . $url;
490. }
492. $this->_writeUrl($row - 1, PHPExcel_Cell:: columnIndexFromString($column) - 1, $url);
493. }
494. }
496. if ($this->_BIFF_version == 0x0600) {
497. $this->_writeDataValidity();
498. $this->_writeSheetLayout();
499. $this->_writeSheetProtection();
500. $this->_writeRangeProtection();
501. }
503. $this->_storeEof();
504. }
506. /**
507. * Write a cell range address in BIFF8
508. * always fixed range
509. * See section 2.5.14 in OpenOffice.org's Documentation of the Microsoft Excel File Format
510. *
511. * @param string $range E.g. 'A1' or 'A1:B6'
512. * @return string Binary data
513. */
514. private function _writeBIFF8CellRangeAddressFixed($range = 'A1')
515. {
516. $explodes = explode(':', $range);
518. // extract first cell, e.g. 'A1'
519. $firstCell = $explodes[0];
521. // extract last cell, e.g. 'B6'
522. if ( count($explodes) == 1) {
523. $lastCell = $firstCell;
524. } else {
525. $lastCell = $explodes[1];
526. }
528. $firstCellCoordinates = PHPExcel_Cell:: coordinateFromString($firstCell); // e.g. array(0, 1)
529. $lastCellCoordinates = PHPExcel_Cell:: coordinateFromString($lastCell); // e.g. array(1, 6)
531. return( pack('vvvv',
532. $firstCellCoordinates[1] - 1,
533. $lastCellCoordinates[1] - 1,
534. PHPExcel_Cell:: columnIndexFromString($firstCellCoordinates[0]) - 1,
535. PHPExcel_Cell:: columnIndexFromString($lastCellCoordinates[0]) - 1
536. ));
537. }
539. /**
540. * Retrieves data from memory in one chunk, or from disk in $buffer
541. * sized chunks.
542. *
543. * @return string The data
544. */
545. function getData()
546. {
547. $buffer = 4096;
549. // Return data stored in memory
550. if (isset($this->_data)) {
551. $tmp = $this->_data;
552. unset($this->_data);
553. return $tmp;
554. }
555. // No data to return
556. return false;
557. }
559. /**
560. * Set the option to print the row and column headers on the printed page.
561. *
562. * @access public
563. * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
564. */
565. function printRowColHeaders($print = 1)
566. {
567. $this->_print_headers = $print;
568. }
570. /**
571. * This method sets the properties for outlining and grouping. The defaults
572. * correspond to Excel's defaults.
573. *
574. * @param bool $visible
575. * @param bool $symbols_below
576. * @param bool $symbols_right
577. * @param bool $auto_style
578. */
579. function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
580. {
581. $this->_outline_on = $visible;
582. $this->_outline_below = $symbols_below;
583. $this->_outline_right = $symbols_right;
584. $this->_outline_style = $auto_style;
586. // Ensure this is a boolean vale for Window2
587. if ($this->_outline_on) {
588. $this->_outline_on = 1;
589. }
590. }
592. /**
593. * Write a double to the specified row and column (zero indexed).
594. * An integer can be written as a double. Excel will display an
595. * integer. $format is optional.
596. *
597. * Returns 0 : normal termination
598. * -2 : row or column out of range
599. *
600. * @param integer $row Zero indexed row
601. * @param integer $col Zero indexed column
602. * @param float $num The number to write
603. * @param mixed $format The optional XF format
604. * @return integer
605. */
606. private function _writeNumber($row, $col, $num, $xfIndex)
607. {
608. $record = 0x0203; // Record identifier
609. $length = 0x000E; // Number of bytes to follow
611. $header = pack("vv", $record, $length);
612. $data = pack("vvv", $row, $col, $xfIndex);
613. $xl_double = pack("d", $num);
614. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
615. $xl_double = strrev($xl_double);
616. }
618. $this->_append($header.$data.$xl_double);
619. return(0);
620. }
622. /**
623. * Write a LABELSST record or a LABEL record. Which one depends on BIFF version
624. *
625. * @param int $row Row index (0-based)
626. * @param int $col Column index (0-based)
627. * @param string $str The string
628. * @param int $xfIndex Index to XF record
629. */
630. private function _writeString($row, $col, $str, $xfIndex)
631. {
632. if ($this->_BIFF_version == 0x0600) {
633. $this->_writeLabelSst($row, $col, $str, $xfIndex);
634. } else {
635. $this->_writeLabel($row, $col, $str, $xfIndex);
636. }
637. }
638. /**
639. * Write a string to the specified row and column (zero indexed).
640. * NOTE: there is an Excel 5 defined limit of 255 characters.
641. * $format is optional.
642. * Returns 0 : normal termination
643. * -2 : row or column out of range
644. * -3 : long string truncated to 255 chars
645. *
646. * @access public
647. * @param integer $row Zero indexed row
648. * @param integer $col Zero indexed column
649. * @param string $str The string to write
650. * @param mixed $format The XF format for the cell
651. * @return integer
652. */
653. private function _writeLabel($row, $col, $str, $xfIndex)
654. {
655. $strlen = strlen($str);
656. $record = 0x0204; // Record identifier
657. $length = 0x0008 + $strlen; // Bytes to follow
659. $str_error = 0;
661. if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
662. $str = substr($str, 0, $this->_xls_strmax);
663. $length = 0x0008 + $this->_xls_strmax;
664. $strlen = $this->_xls_strmax;
665. $str_error = -3;
666. }
668. $header = pack("vv", $record, $length);
669. $data = pack("vvvv", $row, $col, $xfIndex, $strlen);
670. $this->_append($header . $data . $str);
671. return($str_error);
672. }
674. /**
675. * Write a string to the specified row and column (zero indexed).
676. * This is the BIFF8 version (no 255 chars limit).
677. * $format is optional.
678. * Returns 0 : normal termination
679. * -2 : row or column out of range
680. * -3 : long string truncated to 255 chars
681. *
682. * @access public
683. * @param integer $row Zero indexed row
684. * @param integer $col Zero indexed column
685. * @param string $str The string to write
686. * @param mixed $format The XF format for the cell
687. * @return integer
688. */
689. private function _writeLabelSst($row, $col, $str, $xfIndex)
690. {
691. $record = 0x00FD; // Record identifier
692. $length = 0x000A; // Bytes to follow
694. $str = PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($str);
696. /* check if string is already present */
697. if (!isset($this->_str_table[$str])) {
698. $this->_str_table[$str] = $this->_str_unique++;
699. }
700. $this->_str_total++;
702. $header = pack('vv', $record, $length);
703. $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
704. $this->_append($header.$data);
705. }
707. /**
708. * Writes a note associated with the cell given by the row and column.
709. * NOTE records don't have a length limit.
710. *
711. * @param integer $row Zero indexed row
712. * @param integer $col Zero indexed column
713. * @param string $note The note to write
714. */
715. private function _writeNote($row, $col, $note)
716. {
717. $note_length = strlen($note);
718. $record = 0x001C; // Record identifier
719. $max_length = 2048; // Maximun length for a NOTE record
720. //$length = 0x0006 + $note_length; // Bytes to follow
722. // Length for this record is no more than 2048 + 6
723. $length = 0x0006 + min($note_length, 2048);
724. $header = pack("vv", $record, $length);
725. $data = pack("vvv", $row, $col, $note_length);
726. $this->_append($header . $data . substr($note, 0, 2048));
728. for ($i = $max_length; $i < $note_length; $i += $max_length) {
729. $chunk = substr($note, $i, $max_length);
730. $length = 0x0006 + strlen($chunk);
731. $header = pack("vv", $record, $length);
732. $data = pack("vvv", -1, 0, strlen($chunk));
733. $this->_append($header.$data.$chunk);
734. }
735. return(0);
736. }
738. /**
739. * Write a blank cell to the specified row and column (zero indexed).
740. * A blank cell is used to specify formatting without adding a string
741. * or a number.
742. *
743. * A blank cell without a format serves no purpose. Therefore, we don't write
744. * a BLANK record unless a format is specified.
745. *
746. * Returns 0 : normal termination (including no format)
747. * -1 : insufficient number of arguments
748. * -2 : row or column out of range
749. *
750. * @param integer $row Zero indexed row
751. * @param integer $col Zero indexed column
752. * @param mixed $format The XF format
753. */
754. function _writeBlank($row, $col, $xfIndex)
755. {
756. $record = 0x0201; // Record identifier
757. $length = 0x0006; // Number of bytes to follow
759. $header = pack("vv", $record, $length);
760. $data = pack("vvv", $row, $col, $xfIndex);
761. $this->_append($header . $data);
762. return 0;
763. }
765. /**
766. * Write a boolean or an error type to the specified row and column (zero indexed)
767. *
768. * @param int $row Row index (0-based)
769. * @param int $col Column index (0-based)
770. * @param int $value
771. * @param boolean $isError Error or Boolean?
772. * @param int $xfIndex
773. */
774. private function _writeBoolErr($row, $col, $value, $isError, $xfIndex)
775. {
776. $record = 0x0205;
777. $length = 8;
779. $header = pack("vv", $record, $length);
780. $data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError);
781. $this->_append($header . $data);
782. return 0;
783. }
785. /**
786. * Write a formula to the specified row and column (zero indexed).
787. * The textual representation of the formula is passed to the parser in
788. * Parser.php which returns a packed binary string.
789. *
790. * Returns 0 : normal termination
791. * -1 : formula errors (bad formula)
792. * -2 : row or column out of range
793. *
794. * @param integer $row Zero indexed row
795. * @param integer $col Zero indexed column
796. * @param string $formula The formula text string
797. * @param mixed $format The optional XF format
798. * @param mixed $calculatedValue Calculated value
799. * @return integer
800. */
801. private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
802. {
803. $record = 0x0006; // Record identifier
805. // Initialize possible additional value for STRING record that should be written after the FORMULA record?
806. $stringValue = null;
808. // calculated value
809. if (isset($calculatedValue)) {
811. // Since we can't yet get the data type of the calculated value,
812. // we use best effort to determine data type
814. if ( is_bool($calculatedValue)) {
815. // Boolean value
816. $num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF);
818. } elseif ( is_int($calculatedValue) || is_float($calculatedValue)) {
819. // Numeric value
820. $num = pack('d', $calculatedValue);
822. } elseif ( is_string($calculatedValue)) {
823. if ( array_key_exists($calculatedValue, PHPExcel_Cell_DataType:: getErrorCodes())) {
824. // Error value
825. $num = pack('CCCvCv', 0x02, 0x00, $this->_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
827. } elseif ($calculatedValue === '' && $this->_BIFF_version == 0x0600) {
828. // Empty string (and BIFF8)
829. $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
831. } else {
832. // Non-empty string value (or empty string BIFF5)
833. $stringValue = $calculatedValue;
834. $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
836. }
838. } else {
839. // We are really not supposed to reach here
840. $num = pack('d', 0x00);
842. }
844. } else {
845. $num = pack('d', 0x00);
846. }
848. $grbit = 0x03; // Option flags
849. $unknown = 0x0000; // Must be zero
851. // Strip the '=' or '@' sign at the beginning of the formula string
852. if ($formula{0} == '=') {
853. $formula = substr($formula,1);
854. } else {
855. // Error handling
856. $this->_writeString($row, $col, 'Unrecognised character for formula');
857. return -1;
858. }
860. // Parse the formula using the parser in Parser.php
861. try {
862. $error = $this->_parser-> parse($formula);
863. $formula = $this->_parser-> toReversePolish();
865. $formlen = strlen($formula); // Length of the binary string
866. $length = 0x16 + $formlen; // Length of the record data
868. $header = pack("vv", $record, $length);
870. $data = pack("vvv", $row, $col, $xfIndex)
871. . $num
872. . pack("vVv", $grbit, $unknown, $formlen);
873. $this->_append($header . $data . $formula);
875. // Append also a STRING record if necessary
876. if ($stringValue !== null) {
877. $this->_writeStringRecord($stringValue);
878. }
880. return 0;
882. } catch (Exception $e) {
883. // do nothing
884. }
886. }
888. /**
889. * Write a STRING record. This
890. *
891. * @param string $stringValue
892. */
893. private function _writeStringRecord($stringValue)
894. {
895. $record = 0x0207; // Record identifier
896. $data = PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($stringValue);
898. $length = strlen($data);
899. $header = pack('vv', $record, $length);
901. $this->_append($header . $data);
902. }
904. /**
905. * Write a hyperlink.
906. * This is comprised of two elements: the visible label and
907. * the invisible link. The visible label is the same as the link unless an
908. * alternative string is specified. The label is written using the
909. * _writeString() method. Therefore the 255 characters string limit applies.
910. * $string and $format are optional.
911. *
912. * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
913. * directory url.
914. *
915. * Returns 0 : normal termination
916. * -2 : row or column out of range
917. * -3 : long string truncated to 255 chars
918. *
919. * @param integer $row Row
920. * @param integer $col Column
921. * @param string $url URL string
922. * @return integer
923. */
924. private function _writeUrl($row, $col, $url)
925. {
926. // Add start row and col to arg list
927. return($this->_writeUrlRange($row, $col, $row, $col, $url));
928. }
930. /**
931. * This is the more general form of _writeUrl(). It allows a hyperlink to be
932. * written to a range of cells. This function also decides the type of hyperlink
933. * to be written. These are either, Web (http, ftp, mailto), Internal
934. * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
935. *
936. * @access private
937. * @see _writeUrl()
938. * @param integer $row1 Start row
939. * @param integer $col1 Start column
940. * @param integer $row2 End row
941. * @param integer $col2 End column
942. * @param string $url URL string
943. * @return integer
944. */
946. function _writeUrlRange($row1, $col1, $row2, $col2, $url)
947. {
949. // Check for internal/external sheet links or default to web link
950. if ( preg_match('[^internal:]', $url)) {
951. return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
952. }
953. if ( preg_match('[^external:]', $url)) {
954. return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
955. }
956. return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
957. }
960. /**
961. * Used to write http, ftp and mailto hyperlinks.
962. * The link type ($options) is 0x03 is the same as absolute dir ref without
963. * sheet. However it is differentiated by the $unknown2 data stream.
964. *
965. * @access private
966. * @see _writeUrl()
967. * @param integer $row1 Start row
968. * @param integer $col1 Start column
969. * @param integer $row2 End row
970. * @param integer $col2 End column
971. * @param string $url URL string
972. * @return integer
973. */
974. function _writeUrlWeb($row1, $col1, $row2, $col2, $url)
975. {
976. $record = 0x01B8; // Record identifier
977. $length = 0x00000; // Bytes to follow
979. // Pack the undocumented parts of the hyperlink stream
980. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
981. $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
983. // Pack the option flags
984. $options = pack("V", 0x03);
986. // Convert URL to a null terminated wchar string
987. $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
988. $url = $url . "\0\0\0";
990. // Pack the length of the URL
991. $url_len = pack("V", strlen($url));
993. // Calculate the data length
994. $length = 0x34 + strlen($url);
996. // Pack the header data
997. $header = pack("vv", $record, $length);
998. $data = pack("vvvv", $row1, $row2, $col1, $col2);
1000. // Write the packed data
1001. $this->_append($header . $data .
1002. $unknown1 . $options .
1003. $unknown2 . $url_len . $url);
1004. return 0;
1005. }
1007. /**
1008. * Used to write internal reference hyperlinks such as "Sheet1!A1".
1009. *
1010. * @access private
1011. * @see _writeUrl()
1012. * @param integer $row1 Start row
1013. * @param integer $col1 Start column
1014. * @param integer $row2 End row
1015. * @param integer $col2 End column
1016. * @param string $url URL string
1017. * @return integer
1018. */
1019. function _writeUrlInternal($row1, $col1, $row2, $col2, $url)
1020. {
1021. $record = 0x01B8; // Record identifier
1022. $length = 0x00000; // Bytes to follow
1024. // Strip URL type
1025. $url = preg_replace('/^internal:/', '', $url);
1027. // Pack the undocumented parts of the hyperlink stream
1028. $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1030. // Pack the option flags
1031. $options = pack("V", 0x08);
1033. // Convert the URL type and to a null terminated wchar string
1034. $url .= "\0";
1036. // character count
1037. $url_len = PHPExcel_Shared_String:: CountCharacters($url);
1038. $url_len = pack('V', $url_len);
1040. $url = PHPExcel_Shared_String:: ConvertEncoding($url, 'UTF-16LE', 'UTF-8');
1042. // Calculate the data length
1043. $length = 0x24 + strlen($url);
1045. // Pack the header data
1046. $header = pack("vv", $record, $length);
1047. $data = pack("vvvv", $row1, $row2, $col1, $col2);
1049. // Write the packed data
1050. $this->_append($header . $data .
1051. $unknown1 . $options .
1052. $url_len . $url);
1053. return 0;
1054. }
1056. /**
1057. * Write links to external directory names such as 'c:\foo.xls',
1058. * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
1059. *
1060. * Note: Excel writes some relative links with the $dir_long string. We ignore
1061. * these cases for the sake of simpler code.
1062. *
1063. * @access private
1064. * @see _writeUrl()
1065. * @param integer $row1 Start row
1066. * @param integer $col1 Start column
1067. * @param integer $row2 End row
1068. * @param integer $col2 End column
1069. * @param string $url URL string
1070. * @return integer
1071. */
1072. function _writeUrlExternal($row1, $col1, $row2, $col2, $url)
1073. {
1074. // Network drives are different. We will handle them separately
1075. // MS/Novell network drives and shares start with \\
1076. if ( preg_match('[^external:\\\\]', $url)) {
1077. return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
1078. }
1080. $record = 0x01B8; // Record identifier
1081. $length = 0x00000; // Bytes to follow
1083. // Strip URL type and change Unix dir separator to Dos style (if needed)
1084. //
1085. $url = preg_replace('/^external:/', '', $url);
1086. $url = preg_replace('/\//', "\\", $url);
1088. // Determine if the link is relative or absolute:
1089. // relative if link contains no dir separator, "somefile.xls"
1090. // relative if link starts with up-dir, "..\..\somefile.xls"
1091. // otherwise, absolute
1093. $absolute = 0x00; // relative path
1094. if ( preg_match('/^[A-Z]:/', $url) ) {
1095. $absolute = 0x02; // absolute path on Windows, e.g. C:\...
1096. }
1097. $link_type = 0x01 | $absolute;
1099. // Determine if the link contains a sheet reference and change some of the
1100. // parameters accordingly.
1101. // Split the dir name and sheet name (if it exists)
1102. $dir_long = $url;
1103. if ( preg_match("/\#/", $url)) {
1104. $link_type |= 0x08;
1105. }
1108. // Pack the link type
1109. $link_type = pack("V", $link_type);
1111. // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1112. $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
1113. $up_count = pack("v", $up_count);
1115. // Store the short dos dir name (null terminated)
1116. $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
1118. // Store the long dir name as a wchar string (non-null terminated)
1119. $dir_long = $dir_long . "\0";
1121. // Pack the lengths of the dir strings
1122. $dir_short_len = pack("V", strlen($dir_short) );
1123. $dir_long_len = pack("V", strlen($dir_long) );
1124. $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
1126. // Pack the undocumented parts of the hyperlink stream
1127. $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
1128. $unknown2 = pack("H*",'0303000000000000C000000000000046' );
1129. $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
1130. $unknown4 = pack("v", 0x03 );
1132. // Pack the main data stream
1133. $data = pack("vvvv", $row1, $row2, $col1, $col2) .
1134. $unknown1 .
1135. $link_type .
1136. $unknown2 .
1137. $up_count .
1138. $dir_short_len.
1139. $dir_short .
1140. $unknown3 .
1141. $stream_len ;/*.
1142. $dir_long_len .
1143. $unknown4 .
1144. $dir_long .
1145. $sheet_len .
1146. $sheet ;*/
1148. // Pack the header data
1149. $length = strlen($data);
1150. $header = pack("vv", $record, $length);
1152. // Write the packed data
1153. $this->_append($header. $data);
1154. return 0;
1155. }
1157. /**
1158. * This method is used to set the height and format for a row.
1159. *
1160. * @param integer $row The row to set
1161. * @param integer $height Height we are giving to the row.
1162. * Use null to set XF without setting height
1163. * @param integer $xfIndex The optional cell style Xf index to apply to the columns
1164. * @param bool $hidden The optional hidden attribute
1165. * @param integer $level The optional outline level for row, in range [0,7]
1166. */
1167. private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
1168. {
1169. $record = 0x0208; // Record identifier
1170. $length = 0x0010; // Number of bytes to follow
1172. $colMic = 0x0000; // First defined column
1173. $colMac = 0x0000; // Last defined column
1174. $irwMac = 0x0000; // Used by Excel to optimise loading
1175. $reserved = 0x0000; // Reserved
1176. $grbit = 0x0000; // Option flags
1177. $ixfe = $xfIndex;
1179. if ( $height < 0 ){
1180. $height = null;
1181. }
1183. // Use _writeRow($row, null, $XF) to set XF format without setting height
1184. if ($height != null) {
1185. $miyRw = $height * 20; // row height
1186. } else {
1187. $miyRw = 0xff; // default row height is 256
1188. }
1190. // Set the options flags. fUnsynced is used to show that the font and row
1191. // heights are not compatible. This is usually the case for WriteExcel.
1192. // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
1193. // is collapsed. Instead it is used to indicate that the previous row is
1194. // collapsed. The zero height flag, 0x20, is used to collapse a row.
1196. $grbit |= $level;
1197. if ($hidden) {
1198. $grbit |= 0x0020;
1199. }
1200. if ($height !== null) {
1201. $grbit |= 0x0040; // fUnsynced
1202. }
1203. if ($xfIndex !== 0xF) {
1204. $grbit |= 0x0080;
1205. }
1206. $grbit |= 0x0100;
1208. $header = pack("vv", $record, $length);
1209. $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
1210. $irwMac,$reserved, $grbit, $ixfe);
1211. $this->_append($header.$data);
1212. }
1214. /**
1215. * Writes Excel DIMENSIONS to define the area in which there is data.
1216. */
1217. private function _writeDimensions()
1218. {
1219. $record = 0x0200; // Record identifier
1221. if ($this->_BIFF_version == 0x0500) {
1222. $length = 0x000A; // Number of bytes to follow
1223. $data = pack("vvvvv"
1224. , $this->_firstRowIndex
1225. , $this->_lastRowIndex + 1
1226. , $this->_firstColumnIndex
1227. , $this->_lastColumnIndex + 1
1228. , 0x0000 // reserved
1229. );
1231. } elseif ($this->_BIFF_version == 0x0600) {
1232. $length = 0x000E;
1233. $data = pack('VVvvv'
1234. , $this->_firstRowIndex
1235. , $this->_lastRowIndex + 1
1236. , $this->_firstColumnIndex
1237. , $this->_lastColumnIndex + 1
1238. , 0x0000 // reserved
1239. );
1240. }
1242. $header = pack("vv", $record, $length);
1243. $this->_append($header.$data);
1244. }
1246. /**
1247. * Write BIFF record Window2.
1248. */
1249. private function _writeWindow2()
1250. {
1251. $record = 0x023E; // Record identifier
1252. if ($this->_BIFF_version == 0x0500) {
1253. $length = 0x000A; // Number of bytes to follow
1254. } elseif ($this->_BIFF_version == 0x0600) {
1255. $length = 0x0012;
1256. }
1258. $grbit = 0x00B6; // Option flags
1259. $rwTop = 0x0000; // Top row visible in window
1260. $colLeft = 0x0000; // Leftmost column visible in window
1263. // The options flags that comprise $grbit
1264. $fDspFmla = 0; // 0 - bit
1265. $fDspGrid = $this->_phpSheet->getShowGridlines() ? 1 : 0; // 1
1266. $fDspRwCol = $this->_phpSheet->getShowRowColHeaders() ? 1 : 0; // 2
1267. $fFrozen = $this->_phpSheet->getFreezePane() ? 1 : 0; // 3
1268. $fDspZeros = 1; // 4
1269. $fDefaultHdr = 1; // 5
1270. $fArabic = $this->_phpSheet->getRightToLeft() ? 1 : 0; // 6
1271. $fDspGuts = $this->_outline_on; // 7
1272. $fFrozenNoSplit = 0; // 0 - bit
1273. // no support in PHPExcel for selected sheet, therefore sheet is only selected if it is the active sheet
1274. $fSelected = ($this->_phpSheet === $this->_phpSheet->getParent()->getActiveSheet()) ? 1 : 0;
1275. $fPaged = 1; // 2
1277. $grbit = $fDspFmla;
1278. $grbit |= $fDspGrid << 1;
1279. $grbit |= $fDspRwCol << 2;
1280. $grbit |= $fFrozen << 3;
1281. $grbit |= $fDspZeros << 4;
1282. $grbit |= $fDefaultHdr << 5;
1283. $grbit |= $fArabic << 6;
1284. $grbit |= $fDspGuts << 7;
1285. $grbit |= $fFrozenNoSplit << 8;
1286. $grbit |= $fSelected << 9;
1287. $grbit |= $fPaged << 10;
1289. $header = pack("vv", $record, $length);
1290. $data = pack("vvv", $grbit, $rwTop, $colLeft);
1291. // FIXME !!!
1292. if ($this->_BIFF_version == 0x0500) {
1293. $rgbHdr = 0x00000000; // Row/column heading and gridline color
1294. $data .= pack("V", $rgbHdr);
1295. } elseif ($this->_BIFF_version == 0x0600) {
1296. $rgbHdr = 0x0040; // Row/column heading and gridline color index
1297. $zoom_factor_page_break = 0x0000;
1298. $zoom_factor_normal = 0x0000;
1299. $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
1300. }
1301. $this->_append($header.$data);
1302. }
1304. /**
1305. * Write BIFF record DEFAULTROWHEIGHT.
1306. */
1307. private function _writeDefaultRowHeight()
1308. {
1309. $defaultRowHeight = $this->_phpSheet->getDefaultRowDimension()->getRowHeight();
1311. if ($defaultRowHeight < 0) {
1312. return;
1313. }
1315. // convert to twips
1316. $defaultRowHeight = (int) 20 * $defaultRowHeight;
1318. $record = 0x0225; // Record identifier
1319. $length = 0x0004; // Number of bytes to follow
1321. $header = pack("vv", $record, $length);
1322. $data = pack("vv", 1, $defaultRowHeight);
1323. $this->_append($header . $data);
1324. }
1326. /**
1327. * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
1328. */
1329. private function _writeDefcol()
1330. {
1331. $defaultColWidth = 8;
1333. $record = 0x0055; // Record identifier
1334. $length = 0x0002; // Number of bytes to follow
1336. $header = pack("vv", $record, $length);
1337. $data = pack("v", $defaultColWidth);
1338. $this->_append($header . $data);
1339. }
1341. /**
1342. * Write BIFF record COLINFO to define column widths
1343. *
1344. * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
1345. * length record.
1346. *
1347. * @param array $col_array This is the only parameter received and is composed of the following:
1348. * 0 => First formatted column,
1349. * 1 => Last formatted column,
1350. * 2 => Col width (8.43 is Excel default),
1351. * 3 => The optional XF format of the column,
1352. * 4 => Option flags.
1353. * 5 => Optional outline level
1354. */
1355. private function _writeColinfo($col_array)
1356. {
1357. if (isset($col_array[0])) {
1358. $colFirst = $col_array[0];
1359. }
1360. if (isset($col_array[1])) {
1361. $colLast = $col_array[1];
1362. }
1363. if (isset($col_array[2])) {
1364. $coldx = $col_array[2];
1365. } else {
1366. $coldx = 8.43;
1367. }
1368. if (isset($col_array[3])) {
1369. $xfIndex = $col_array[3];
1370. } else {
1371. $xfIndex = 15;
1372. }
1373. if (isset($col_array[4])) {
1374. $grbit = $col_array[4];
1375. } else {
1376. $grbit = 0;
1377. }
1378. if (isset($col_array[5])) {
1379. $level = $col_array[5];
1380. } else {
1381. $level = 0;
1382. }
1383. $record = 0x007D; // Record identifier
1384. $length = 0x000C; // Number of bytes to follow
1386. $coldx *= 256; // Convert to units of 1/256 of a char
1388. $ixfe = $xfIndex;
1389. $reserved = 0x0000; // Reserved
1391. $level = max(0, min($level, 7));
1392. $grbit |= $level << 8;
1394. $header = pack("vv", $record, $length);
1395. $data = pack("vvvvvv", $colFirst, $colLast, $coldx,
1396. $ixfe, $grbit, $reserved);
1397. $this->_append($header.$data);
1398. }
1400. /**
1401. * Write BIFF record SELECTION.
1402. */
1403. private function _writeSelection()
1404. {
1405. // look up the selected cell range
1406. $selectedCells = $this->_phpSheet->getSelectedCells();
1407. $selectedCells = PHPExcel_Cell:: splitRange($this->_phpSheet->getSelectedCells());
1408. $selectedCells = $selectedCells[0];
1409. if ( count($selectedCells) == 2) {
1410. list($first, $last) = $selectedCells;
1411. } else {
1412. $first = $selectedCells[0];
1413. $last = $selectedCells[0];
1414. }
1416. list($colFirst, $rwFirst) = PHPExcel_Cell:: coordinateFromString($first);
1417. $colFirst = PHPExcel_Cell:: columnIndexFromString($colFirst) - 1; // base 0 column index
1418. --$rwFirst; // base 0 row index
1420. list($colLast, $rwLast) = PHPExcel_Cell:: coordinateFromString($last);
1421. $colLast = PHPExcel_Cell:: columnIndexFromString($colLast) - 1; // base 0 column index
1422. --$rwLast; // base 0 row index
1424. // make sure we are not out of bounds
1425. $colFirst = min($colFirst, 255);
1426. $colLast = min($colLast, 255);
1427. if ($this->_BIFF_version == 0x0600) {
1428. $rwFirst = min($rwFirst, 65535);
1429. $rwLast = min($rwLast, 65535);
1430. } else {
1431. $rwFirst = min($rwFirst, 16383);
1432. $rwLast = min($rwLast, 16383);
1433. }
1435. $record = 0x001D; // Record identifier
1436. $length = 0x000F; // Number of bytes to follow
1438. $pnn = $this->_active_pane; // Pane position
1439. $rwAct = $rwFirst; // Active row
1440. $colAct = $colFirst; // Active column
1441. $irefAct = 0; // Active cell ref
1442. $cref = 1; // Number of refs
1444. if (!isset($rwLast)) {
1445. $rwLast = $rwFirst; // Last row in reference
1446. }
1447. if (!isset($colLast)) {
1448. $colLast = $colFirst; // Last col in reference
1449. }
1451. // Swap last row/col for first row/col as necessary
1452. if ($rwFirst > $rwLast) {
1453. list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
1454. }
1456. if ($colFirst > $colLast) {
1457. list($colFirst, $colLast) = array($colLast, $colFirst);
1458. }
1460. $header = pack("vv", $record, $length);
1461. $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
1462. $irefAct, $cref,
1463. $rwFirst, $rwLast,
1464. $colFirst, $colLast);
1465. $this->_append($header . $data);
1466. }
1468. /**
1469. * Store the MERGEDCELLS records for all ranges of merged cells
1470. */
1471. private function _writeMergedCells()
1472. {
1473. $mergeCells = $this->_phpSheet->getMergeCells();
1474. $countMergeCells = count($mergeCells);
1476. if ($countMergeCells == 0) {
1477. return;
1478. }
1480. // maximum allowed number of merged cells per record
1481. if ($this->_BIFF_version == 0x0600) {
1482. $maxCountMergeCellsPerRecord = 1027;
1483. } else {
1484. $maxCountMergeCellsPerRecord = 259;
1485. }
1487. // record identifier
1488. $record = 0x00E5;
1490. // counter for total number of merged cells treated so far by the writer
1491. $i = 0;
1493. // counter for number of merged cells written in record currently being written
1494. $j = 0;
1496. // initialize record data
1497. $recordData = '';
1499. // loop through the merged cells
1500. foreach ($mergeCells as $mergeCell) {
1501. ++$i;
1502. ++$j;
1504. // extract the row and column indexes
1505. $range = PHPExcel_Cell:: splitRange($mergeCell);
1506. list($first, $last) = $range[0];
1507. list($firstColumn, $firstRow) = PHPExcel_Cell:: coordinateFromString($first);
1508. list($lastColumn, $lastRow) = PHPExcel_Cell:: coordinateFromString($last);
1510. $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell:: columnIndexFromString($firstColumn) - 1, PHPExcel_Cell:: columnIndexFromString($lastColumn) - 1);
1512. // flush record if we have reached limit for number of merged cells, or reached final merged cell
1513. if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
1514. $recordData = pack('v', $j) . $recordData;
1515. $length = strlen($recordData);
1516. $header = pack('vv', $record, $length);
1517. $this->_append($header . $recordData);
1519. // initialize for next record, if any
1520. $recordData = '';
1521. $j = 0;
1522. }
1523. }
1524. }
1526. /**
1527. * Write SHEETLAYOUT record
1528. */
1529. private function _writeSheetLayout()
1530. {
1531. if (!$this->_phpSheet->isTabColorSet()) {
1532. return;
1533. }
1535. $recordData = pack(
1536. 'vvVVVvv'
1537. , 0x0862
1538. , 0x0000 // unused
1539. , 0x00000000 // unused
1540. , 0x00000000 // unused
1541. , 0x00000014 // size of record data
1542. , $this->_colors[$this->_phpSheet->getTabColor()->getRGB()] // color index
1543. , 0x0000 // unused
1544. );
1546. $length = strlen($recordData);
1548. $record = 0x0862; // Record identifier
1549. $header = pack('vv', $record, $length);
1550. $this->_append($header . $recordData);
1551. }
1553. /**
1554. * Write SHEETPROTECTION
1555. */
1556. private function _writeSheetProtection()
1557. {
1558. // record identifier
1559. $record = 0x0867;
1561. // prepare options
1562. $options = (int) !$this->_phpSheet->getProtection()->getObjects()
1563. | (int) !$this->_phpSheet->getProtection()->getScenarios() << 1
1564. | (int) !$this->_phpSheet->getProtection()->getFormatCells() << 2
1565. | (int) !$this->_phpSheet->getProtection()->getFormatColumns() << 3
1566. | (int) !$this->_phpSheet->getProtection()->getFormatRows() << 4
1567. | (int) !$this->_phpSheet->getProtection()->getInsertColumns() << 5
1568. | (int) !$this->_phpSheet->getProtection()->getInsertRows() << 6
1569. | (int) !$this->_phpSheet->getProtection()->getInsertHyperlinks() << 7
1570. | (int) !$this->_phpSheet->getProtection()->getDeleteColumns() << 8
1571. | (int) !$this->_phpSheet->getProtection()->getDeleteRows() << 9
1572. | (int) !$this->_phpSheet->getProtection()->getSelectLockedCells() << 10
1573. | (int) !$this->_phpSheet->getProtection()->getSort() << 11
1574. | (int) !$this->_phpSheet->getProtection()->getAutoFilter() << 12
1575. | (int) !$this->_phpSheet->getProtection()->getPivotTables() << 13
1576. | (int) !$this->_phpSheet->getProtection()->getSelectUnlockedCells() << 14 ;
1578. // record data
1579. $recordData = pack(
1580. 'vVVCVVvv'
1581. , 0x0867 // repeated record identifier
1582. , 0x0000 // not used
1583. , 0x0000 // not used
1584. , 0x00 // not used
1585. , 0x01000200 // unknown data
1586. , 0xFFFFFFFF // unknown data
1587. , $options // options
1588. , 0x0000 // not used
1589. );
1591. $length = strlen($recordData);
1592. $header = pack('vv', $record, $length);
1594. $this->_append($header . $recordData);
1595. }
1597. /**
1598. * Write BIFF record RANGEPROTECTION
1599. *
1600. * Openoffice.org's Documentaion of the Microsoft Excel File Format uses term RANGEPROTECTION for these records
1601. * Microsoft Office Excel 97-2007 Binary File Format Specification uses term FEAT for these records
1602. */
1603. private function _writeRangeProtection()
1604. {
1605. foreach ($this->_phpSheet->getProtectedCells() as $range => $password) {
1606. // number of ranges, e.g. 'A1:B3 C20:D25'
1607. $cellRanges = explode(' ', $range);
1608. $cref = count($cellRanges);
1610. $recordData = pack(
1611. 'vvVVvCVvVv',
1612. 0x0868,
1613. 0x00,
1614. 0x0000,
1615. 0x0000,
1616. 0x02,
1617. 0x0,
1618. 0x0000,
1619. $cref,
1620. 0x0000,
1621. 0x00
1622. );
1624. foreach ($cellRanges as $cellRange) {
1625. $recordData .= $this->_writeBIFF8CellRangeAddressFixed($cellRange);
1626. }
1628. // the rgbFeat structure
1629. $recordData .= pack(
1630. 'VV',
1631. 0x0000,
1632. hexdec($password)
1633. );
1635. $recordData .= PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong('p' . md5($recordData));
1637. $length = strlen($recordData);
1639. $record = 0x0868; // Record identifier
1640. $header = pack("vv", $record, $length);
1641. $this->_append($header . $recordData);
1642. }
1643. }
1645. /**
1646. * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
1647. * references in a worksheet.
1648. *
1649. * Excel only stores references to external sheets that are used in formulas.
1650. * For simplicity we store references to all the sheets in the workbook
1651. * regardless of whether they are used or not. This reduces the overall
1652. * complexity and eliminates the need for a two way dialogue between the formula
1653. * parser the worksheet objects.
1654. *
1655. * @param integer $count The number of external sheet references in this worksheet
1656. */
1657. private function _writeExterncount($count)
1658. {
1659. $record = 0x0016; // Record identifier
1660. $length = 0x0002; // Number of bytes to follow
1662. $header = pack("vv", $record, $length);
1663. $data = pack("v", $count);
1664. $this->_append($header . $data);
1665. }
1667. /**
1668. * Writes the Excel BIFF EXTERNSHEET record. These references are used by
1669. * formulas. A formula references a sheet name via an index. Since we store a
1670. * reference to all of the external worksheets the EXTERNSHEET index is the same
1671. * as the worksheet index.
1672. *
1673. * @param string $sheetname The name of a external worksheet
1674. */
1675. private function _writeExternsheet($sheetname)
1676. {
1677. $record = 0x0017; // Record identifier
1679. // References to the current sheet are encoded differently to references to
1680. // external sheets.
1681. //
1682. if ($this->_phpSheet->getTitle() == $sheetname) {
1683. $sheetname = '';
1684. $length = 0x02; // The following 2 bytes
1685. $cch = 1; // The following byte
1686. $rgch = 0x02; // Self reference
1687. } else {
1688. $length = 0x02 + strlen($sheetname);
1689. $cch = strlen($sheetname);
1690. $rgch = 0x03; // Reference to a sheet in the current workbook
1691. }
1693. $header = pack("vv", $record, $length);
1694. $data = pack("CC", $cch, $rgch);
1695. $this->_append($header . $data . $sheetname);
1696. }
1698. /**
1699. * Writes the Excel BIFF PANE record.
1700. * The panes can either be frozen or thawed (unfrozen).
1701. * Frozen panes are specified in terms of an integer number of rows and columns.
1702. * Thawed panes are specified in terms of Excel's units for rows and columns.
1703. */
1704. private function _writePanes()
1705. {
1706. $panes = array();
1707. if ($freezePane = $this->_phpSheet->getFreezePane()) {
1708. list($column, $row) = PHPExcel_Cell:: coordinateFromString($freezePane);
1709. $panes[0] = $row - 1;
1710. $panes[1] = PHPExcel_Cell:: columnIndexFromString($column) - 1;
1711. } else {
1712. // thaw panes
1713. return;
1714. }
1716. $y = isset($panes[0]) ? $panes[0] : null;
1717. $x = isset($panes[1]) ? $panes[1] : null;
1718. $rwTop = isset($panes[2]) ? $panes[2] : null;
1719. $colLeft = isset($panes[3]) ? $panes[3] : null;
1720. if ( count($panes) > 4) { // if Active pane was received
1721. $pnnAct = $panes[4];
1722. } else {
1723. $pnnAct = null;
1724. }
1725. $record = 0x0041; // Record identifier
1726. $length = 0x000A; // Number of bytes to follow
1728. // Code specific to frozen or thawed panes.
1729. if ($this->_phpSheet->getFreezePane()) {
1730. // Set default values for $rwTop and $colLeft
1731. if (!isset($rwTop)) {
1732. $rwTop = $y;
1733. }
1734. if (!isset($colLeft)) {
1735. $colLeft = $x;
1736. }
1737. } else {
1738. // Set default values for $rwTop and $colLeft
1739. if (!isset($rwTop)) {
1740. $rwTop = 0;
1741. }
1742. if (!isset($colLeft)) {
1743. $colLeft = 0;
1744. }
1746. // Convert Excel's row and column units to the internal units.
1747. // The default row height is 12.75
1748. // The default column width is 8.43
1749. // The following slope and intersection values were interpolated.
1750. //
1751. $y = 20*$y + 255;
1752. $x = 113.879*$x + 390;
1753. }
1756. // Determine which pane should be active. There is also the undocumented
1757. // option to override this should it be necessary: may be removed later.
1758. //
1759. if (!isset($pnnAct)) {
1760. if ($x != 0 && $y != 0) {
1761. $pnnAct = 0; // Bottom right
1762. }
1763. if ($x != 0 && $y == 0) {
1764. $pnnAct = 1; // Top right
1765. }
1766. if ($x == 0 && $y != 0) {
1767. $pnnAct = 2; // Bottom left
1768. }
1769. if ($x == 0 && $y == 0) {
1770. $pnnAct = 3; // Top left
1771. }
1772. }
1774. $this->_active_pane = $pnnAct; // Used in _writeSelection
1776. $header = pack("vv", $record, $length);
1777. $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
1778. $this->_append($header . $data);
1779. }
1781. /**
1782. * Store the page setup SETUP BIFF record.
1783. */
1784. private function _writeSetup()
1785. {
1786. $record = 0x00A1; // Record identifier
1787. $length = 0x0022; // Number of bytes to follow
1789. $iPaperSize = $this->_phpSheet->getPageSetup()->getPaperSize(); // Paper size
1791. $iScale = $this->_phpSheet->getPageSetup()->getScale() ?
1792. $this->_phpSheet->getPageSetup()->getScale() : 100; // Print scaling factor
1794. $iPageStart = 0x01; // Starting page number
1795. $iFitWidth = (int) $this->_phpSheet->getPageSetup()->getFitToWidth(); // Fit to number of pages wide
1796. $iFitHeight = (int) $this->_phpSheet->getPageSetup()->getFitToHeight(); // Fit to number of pages high
1797. $grbit = 0x00; // Option flags
1798. $iRes = 0x0258; // Print resolution
1799. $iVRes = 0x0258; // Vertical print resolution
1801. $numHdr = $this->_phpSheet->getPageMargins()->getHeader(); // Header Margin
1803. $numFtr = $this->_phpSheet->getPageMargins()->getFooter(); // Footer Margin
1804. $iCopies = 0x01; // Number of copies
1806. $fLeftToRight = 0x0; // Print over then down
1808. // Page orientation
1809. $fLandscape = ($this->_phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE) ?
1810. 0x0 : 0x1;
1812. $fNoPls = 0x0; // Setup not read from printer
1813. $fNoColor = 0x0; // Print black and white
1814. $fDraft = 0x0; // Print draft quality
1815. $fNotes = 0x0; // Print notes
1816. $fNoOrient = 0x0; // Orientation not set
1817. $fUsePage = 0x0; // Use custom starting page
1819. $grbit = $fLeftToRight;
1820. $grbit |= $fLandscape << 1;
1821. $grbit |= $fNoPls << 2;
1822. $grbit |= $fNoColor << 3;
1823. $grbit |= $fDraft << 4;
1824. $grbit |= $fNotes << 5;
1825. $grbit |= $fNoOrient << 6;
1826. $grbit |= $fUsePage << 7;
1828. $numHdr = pack("d", $numHdr);
1829. $numFtr = pack("d", $numFtr);
1830. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
1831. $numHdr = strrev($numHdr);
1832. $numFtr = strrev($numFtr);
1833. }
1835. $header = pack("vv", $record, $length);
1836. $data1 = pack("vvvvvvvv", $iPaperSize,
1837. $iScale,
1838. $iPageStart,
1839. $iFitWidth,
1840. $iFitHeight,
1841. $grbit,
1842. $iRes,
1843. $iVRes);
1844. $data2 = $numHdr.$numFtr;
1845. $data3 = pack("v", $iCopies);
1846. $this->_append($header . $data1 . $data2 . $data3);
1847. }
1849. /**
1850. * Store the header caption BIFF record.
1851. */
1852. private function _writeHeader()
1853. {
1854. $record = 0x0014; // Record identifier
1856. /* removing for now
1857. // need to fix character count (multibyte!)
1858. if (strlen($this->_phpSheet->getHeaderFooter()->getOddHeader()) <= 255) {
1859. $str = $this->_phpSheet->getHeaderFooter()->getOddHeader(); // header string
1860. } else {
1861. $str = '';
1862. }
1863. */
1865. if ($this->_BIFF_version == 0x0600) {
1866. $recordData = PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddHeader());
1867. $length = strlen($recordData);
1868. } else {
1869. $cch = strlen($this->_phpSheet->getHeaderFooter()->getOddHeader()); // Length of header string
1870. $length = 1 + $cch; // Bytes to follow
1871. $data = pack("C", $cch);
1872. $recordData = $data . $this->_phpSheet->getHeaderFooter()->getOddHeader();
1873. }
1875. $header = pack("vv", $record, $length);
1877. $this->_append($header . $recordData);
1878. }
1880. /**
1881. * Store the footer caption BIFF record.
1882. */
1883. private function _writeFooter()
1884. {
1885. $record = 0x0015; // Record identifier
1887. /* removing for now
1888. // need to fix character count (multibyte!)
1889. if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) {
1890. $str = $this->_phpSheet->getHeaderFooter()->getOddFooter();
1891. } else {
1892. $str = '';
1893. }
1894. */
1896. if ($this->_BIFF_version == 0x0600) {
1897. $recordData = PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter());
1898. $length = strlen($recordData);
1899. } else {
1900. $cch = strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()); // Length of footer string
1901. $length = 1 + $cch;
1902. $data = pack("C", $cch);
1903. $recordData = $data . $this->_phpSheet->getHeaderFooter()->getOddFooter();
1904. }
1906. $header = pack("vv", $record, $length);
1908. $this->_append($header . $recordData);
1909. }
1911. /**
1912. * Store the horizontal centering HCENTER BIFF record.
1913. *
1914. * @access private
1915. */
1916. private function _writeHcenter()
1917. {
1918. $record = 0x0083; // Record identifier
1919. $length = 0x0002; // Bytes to follow
1921. $fHCenter = $this->_phpSheet->getPageSetup()->getHorizontalCentered() ? 1 : 0; // Horizontal centering
1923. $header = pack("vv", $record, $length);
1924. $data = pack("v", $fHCenter);
1926. $this->_append($header.$data);
1927. }
1929. /**
1930. * Store the vertical centering VCENTER BIFF record.
1931. */
1932. private function _writeVcenter()
1933. {
1934. $record = 0x0084; // Record identifier
1935. $length = 0x0002; // Bytes to follow
1937. $fVCenter = $this->_phpSheet->getPageSetup()->getVerticalCentered() ? 1 : 0; // Horizontal centering
1939. $header = pack("vv", $record, $length);
1940. $data = pack("v", $fVCenter);
1941. $this->_append($header . $data);
1942. }
1944. /**
1945. * Store the LEFTMARGIN BIFF record.
1946. */
1947. private function _writeMarginLeft()
1948. {
1949. $record = 0x0026; // Record identifier
1950. $length = 0x0008; // Bytes to follow
1952. $margin = $this->_phpSheet->getPageMargins()->getLeft(); // Margin in inches
1954. $header = pack("vv", $record, $length);
1955. $data = pack("d", $margin);
1956. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
1957. $data = strrev($data);
1958. }
1960. $this->_append($header . $data);
1961. }
1963. /**
1964. * Store the RIGHTMARGIN BIFF record.
1965. */
1966. private function _writeMarginRight()
1967. {
1968. $record = 0x0027; // Record identifier
1969. $length = 0x0008; // Bytes to follow
1971. $margin = $this->_phpSheet->getPageMargins()->getRight(); // Margin in inches
1973. $header = pack("vv", $record, $length);
1974. $data = pack("d", $margin);
1975. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
1976. $data = strrev($data);
1977. }
1979. $this->_append($header . $data);
1980. }
1982. /**
1983. * Store the TOPMARGIN BIFF record.
1984. */
1985. private function _writeMarginTop()
1986. {
1987. $record = 0x0028; // Record identifier
1988. $length = 0x0008; // Bytes to follow
1990. $margin = $this->_phpSheet->getPageMargins()->getTop(); // Margin in inches
1992. $header = pack("vv", $record, $length);
1993. $data = pack("d", $margin);
1994. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
1995. $data = strrev($data);
1996. }
1998. $this->_append($header . $data);
1999. }
2001. /**
2002. * Store the BOTTOMMARGIN BIFF record.
2003. */
2004. private function _writeMarginBottom()
2005. {
2006. $record = 0x0029; // Record identifier
2007. $length = 0x0008; // Bytes to follow
2009. $margin = $this->_phpSheet->getPageMargins()->getBottom(); // Margin in inches
2011. $header = pack("vv", $record, $length);
2012. $data = pack("d", $margin);
2013. if ( PHPExcel_Writer_Excel5_BIFFwriter:: getByteOrder()) { // if it's Big Endian
2014. $data = strrev($data);
2015. }
2017. $this->_append($header . $data);
2018. }
2020. /**
2021. * Write the PRINTHEADERS BIFF record.
2022. */
2023. private function _writePrintHeaders()
2024. {
2025. $record = 0x002a; // Record identifier
2026. $length = 0x0002; // Bytes to follow
2028. $fPrintRwCol = $this->_print_headers; // Boolean flag
2030. $header = pack("vv", $record, $length);
2031. $data = pack("v", $fPrintRwCol);
2032. $this->_append($header . $data);
2033. }
2035. /**
2036. * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
2037. * GRIDSET record.
2038. */
2039. private function _writePrintGridlines()
2040. {
2041. $record = 0x002b; // Record identifier
2042. $length = 0x0002; // Bytes to follow
2044. $fPrintGrid = $this->_phpSheet->getPrintGridlines() ? 1 : 0; // Boolean flag
2046. $header = pack("vv", $record, $length);
2047. $data = pack("v", $fPrintGrid);
2048. $this->_append($header . $data);
2049. }
2051. /**
2052. * Write the GRIDSET BIFF record. Must be used in conjunction with the
2053. * PRINTGRIDLINES record.
2054. */
2055. private function _writeGridset()
2056. {
2057. $record = 0x0082; // Record identifier
2058. $length = 0x0002; // Bytes to follow
2060. $fGridSet = !$this->_phpSheet->getPrintGridlines(); // Boolean flag
2062. $header = pack("vv", $record, $length);
2063. $data = pack("v", $fGridSet);
2064. $this->_append($header . $data);
2065. }
2067. /**
2068. * Write the GUTS BIFF record. This is used to configure the gutter margins
2069. * where Excel outline symbols are displayed. The visibility of the gutters is
2070. * controlled by a flag in WSBOOL.
2071. *
2072. * @see _writeWsbool()
2073. */
2074. private function _writeGuts()
2075. {
2076. $record = 0x0080; // Record identifier
2077. $length = 0x0008; // Bytes to follow
2079. $dxRwGut = 0x0000; // Size of row gutter
2080. $dxColGut = 0x0000; // Size of col gutter
2082. // determine maximum row outline level
2083. $maxRowOutlineLevel = 0;
2084. foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
2085. $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel());
2086. }
2088. $col_level = 0;
2090. // Calculate the maximum column outline level. The equivalent calculation
2091. // for the row outline level is carried out in _writeRow().
2092. $colcount = count($this->_colinfo);
2093. for ($i = 0; $i < $colcount; ++$i) {
2094. $col_level = max($this->_colinfo[$i][5], $col_level);
2095. }
2097. // Set the limits for the outline levels (0 <= x <= 7).
2098. $col_level = max(0, min($col_level, 7));
2100. // The displayed level is one greater than the max outline levels
2101. if ($maxRowOutlineLevel) {
2102. ++$maxRowOutlineLevel;
2103. }
2104. if ($col_level) {
2105. ++$col_level;
2106. }
2108. $header = pack("vv", $record, $length);
2109. $data = pack("vvvv", $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level);
2111. $this->_append($header.$data);
2112. }
2115. /**
2116. * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
2117. * with the SETUP record.
2118. */
2119. private function _writeWsbool()
2120. {
2121. $record = 0x0081; // Record identifier
2122. $length = 0x0002; // Bytes to follow
2123. $grbit = 0x0000;
2125. // The only option that is of interest is the flag for fit to page. So we
2126. // set all the options in one go.
2127. //
2128. // Set the option flags
2129. $grbit |= 0x0001; // Auto page breaks visible
2130. if ($this->_outline_style) {
2131. $grbit |= 0x0020; // Auto outline styles
2132. }
2133. if ($this->_phpSheet->getShowSummaryBelow()) {
2134. $grbit |= 0x0040; // Outline summary below
2135. }
2136. if ($this->_phpSheet->getShowSummaryRight()) {
2137. $grbit |= 0x0080; // Outline summary right
2138. }
2139. if ($this->_phpSheet->getPageSetup()->getFitToPage()) {
2140. $grbit |= 0x0100; // Page setup fit to page
2141. }
2142. if ($this->_outline_on) {
2143. $grbit |= 0x0400; // Outline symbols displayed
2144. }
2146. $header = pack("vv", $record, $length);
2147. $data = pack("v", $grbit);
2148. $this->_append($header . $data);
2149. }
2151. /**
2152. * Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
2153. */
2154. private function _writeBreaks()
2155. {
2156. // initialize
2157. $vbreaks = array();
2158. $hbreaks = array();
2160. foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) {
2161. // Fetch coordinates
2162. $coordinates = PHPExcel_Cell:: coordinateFromString($cell);
2164. // Decide what to do by the type of break
2165. switch ($breakType) {
2166. case PHPExcel_Worksheet:: BREAK_COLUMN:
2167. // Add to list of vertical breaks
2168. $vbreaks[] = PHPExcel_Cell:: columnIndexFromString($coordinates[0]) - 1;
2169. break;
2171. case PHPExcel_Worksheet:: BREAK_ROW:
2172. // Add to list of horizontal breaks
2173. $hbreaks[] = $coordinates[1];
2174. break;
2176. case PHPExcel_Worksheet:: BREAK_NONE:
2177. default:
2178. // Nothing to do
2179. break;
2180. }
2181. }
2183. //horizontal page breaks
2184. if ( count($hbreaks) > 0) {
2186. // Sort and filter array of page breaks
2187. sort($hbreaks, SORT_NUMERIC);
2188. if ($hbreaks[0] == 0) { // don't use first break if it's 0
2189. array_shift($hbreaks);
2190. }
2192. $record = 0x001b; // Record identifier
2193. $cbrk = count($hbreaks); // Number of page breaks
2194. if ($this->_BIFF_version == 0x0600) {
2195. $length = 2 + 6 * $cbrk; // Bytes to follow
2196. } else {
2197. $length = 2 + 2 * $cbrk; // Bytes to follow
2198. }
2200. $header = pack("vv", $record, $length);
2201. $data = pack("v", $cbrk);
2203. // Append each page break
2204. foreach ($hbreaks as $hbreak) {
2205. if ($this->_BIFF_version == 0x0600) {
2206. $data .= pack("vvv", $hbreak, 0x0000, 0x00ff);
2207. } else {
2208. $data .= pack("v", $hbreak);
2209. }
2210. }
2212. $this->_append($header . $data);
2213. }
2215. // vertical page breaks
2216. if ( count($vbreaks) > 0) {
2218. // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
2219. // It is slightly higher in Excel 97/200, approx. 1026
2220. $vbreaks = array_slice($vbreaks, 0, 1000);
2222. // Sort and filter array of page breaks
2223. sort($vbreaks, SORT_NUMERIC);
2224. if ($vbreaks[0] == 0) { // don't use first break if it's 0
2225. array_shift($vbreaks);
2226. }
2228. $record = 0x001a; // Record identifier
2229. $cbrk = count($vbreaks); // Number of page breaks
2230. if ($this->_BIFF_version == 0x0600) {
2231. $length = 2 + 6 * $cbrk; // Bytes to follow
2232. } else {
2233. $length = 2 + 2 * $cbrk; // Bytes to follow
2234. }
2236. $header = pack("vv", $record, $length);
2237. $data = pack("v", $cbrk);
2239. // Append each page break
2240. foreach ($vbreaks as $vbreak) {
2241. if ($this->_BIFF_version == 0x0600) {
2242. $data .= pack("vvv", $vbreak, 0x0000, 0xffff);
2243. } else {
2244. $data .= pack("v", $vbreak);
2245. }
2246. }
2248. $this->_append($header . $data);
2249. }
2250. }
2252. /**
2253. * Set the Biff PROTECT record to indicate that the worksheet is protected.
2254. */
2255. private function _writeProtect()
2256. {
2257. // Exit unless sheet protection has been specified
2258. if (!$this->_phpSheet->getProtection()->getSheet()) {
2259. return;
2260. }
2262. $record = 0x0012; // Record identifier
2263. $length = 0x0002; // Bytes to follow
2265. $fLock = 1; // Worksheet is protected
2267. $header = pack("vv", $record, $length);
2268. $data = pack("v", $fLock);
2270. $this->_append($header.$data);
2271. }
2273. /**
2274. * Write SCENPROTECT
2275. */
2276. private function _writeScenProtect()
2277. {
2278. // Exit if sheet protection is not active
2279. if (!$this->_phpSheet->getProtection()->getSheet()) {
2280. return;
2281. }
2283. // Exit if scenarios are not protected
2284. if (!$this->_phpSheet->getProtection()->getScenarios()) {
2285. return;
2286. }
2288. $record = 0x00DD; // Record identifier
2289. $length = 0x0002; // Bytes to follow
2291. $header = pack('vv', $record, $length);
2292. $data = pack('v', 1);
2294. $this->_append($header . $data);
2295. }
2297. /**
2298. * Write OBJECTPROTECT
2299. */
2300. private function _writeObjectProtect()
2301. {
2302. // Exit if sheet protection is not active
2303. if (!$this->_phpSheet->getProtection()->getSheet()) {
2304. return;
2305. }
2307. // Exit if objects are not protected
2308. if (!$this->_phpSheet->getProtection()->getObjects()) {
2309. return;
2310. }
2312. $record = 0x0063; // Record identifier
2313. $length = 0x0002; // Bytes to follow
2315. $header = pack('vv', $record, $length);
2316. $data = pack('v', 1);
2318. $this->_append($header . $data);
2319. }
2321. /**
2322. * Write the worksheet PASSWORD record.
2323. */
2324. private function _writePassword()
2325. {
2326. // Exit unless sheet protection and password have been specified
2327. if (!$this->_phpSheet->getProtection()->getSheet() || !$this->_phpSheet->getProtection()->getPassword()) {
2328. return;
2329. }
2331. $record = 0x0013; // Record identifier
2332. $length = 0x0002; // Bytes to follow
2334. $wPassword = hexdec($this->_phpSheet->getProtection()->getPassword()); // Encoded password
2336. $header = pack("vv", $record, $length);
2337. $data = pack("v", $wPassword);
2339. $this->_append($header . $data);
2340. }
2343. /**
2344. * Insert a 24bit bitmap image in a worksheet.
2345. *
2346. * @access public
2347. * @param integer $row The row we are going to insert the bitmap into
2348. * @param integer $col The column we are going to insert the bitmap into
2349. * @param mixed $bitmap The bitmap filename or GD-image resource
2350. * @param integer $x The horizontal position (offset) of the image inside the cell.
2351. * @param integer $y The vertical position (offset) of the image inside the cell.
2352. * @param float $scale_x The horizontal scale
2353. * @param float $scale_y The vertical scale
2354. */
2355. function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
2356. {
2357. $bitmap_array = ( is_resource($bitmap) ? $this->_processBitmapGd($bitmap) : $this->_processBitmap($bitmap));
2358. list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
2360. // Scale the frame of the image.
2361. $width *= $scale_x;
2362. $height *= $scale_y;
2364. // Calculate the vertices of the image and write the OBJ record
2365. $this->_positionImage($col, $row, $x, $y, $width, $height);
2367. // Write the IMDATA record to store the bitmap data
2368. $record = 0x007f;
2369. $length = 8 + $size;
2370. $cf = 0x09;
2371. $env = 0x01;
2372. $lcb = $size;
2374. $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
2375. $this->_append($header.$data);
2376. }
2378. /**
2379. * Calculate the vertices that define the position of the image as required by
2380. * the OBJ record.
2381. *
2382. * +------------+------------+
2383. * | A | B |
2384. * +-----+------------+------------+
2385. * | |(x1,y1) | |
2386. * | 1 |(A1)._______|______ |
2387. * | | | | |
2388. * | | | | |
2389. * +-----+----| BITMAP |-----+
2390. * | | | | |
2391. * | 2 | |______________. |
2392. * | | | (B2)|
2393. * | | | (x2,y2)|
2394. * +---- +------------+------------+
2395. *
2396. * Example of a bitmap that covers some of the area from cell A1 to cell B2.
2397. *
2398. * Based on the width and height of the bitmap we need to calculate 8 vars:
2399. * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
2400. * The width and height of the cells are also variable and have to be taken into
2401. * account.
2402. * The values of $col_start and $row_start are passed in from the calling
2403. * function. The values of $col_end and $row_end are calculated by subtracting
2404. * the width and height of the bitmap from the width and height of the
2405. * underlying cells.
2406. * The vertices are expressed as a percentage of the underlying cell width as
2407. * follows (rhs values are in pixels):
2408. *
2409. * x1 = X / W *1024
2410. * y1 = Y / H *256
2411. * x2 = (X-1) / W *1024
2412. * y2 = (Y-1) / H *256
2413. *
2414. * Where: X is distance from the left side of the underlying cell
2415. * Y is distance from the top of the underlying cell
2416. * W is the width of the cell
2417. * H is the height of the cell
2418. * The SDK incorrectly states that the height should be expressed as a
2419. * percentage of 1024.
2420. *
2421. * @access private
2422. * @param integer $col_start Col containing upper left corner of object
2423. * @param integer $row_start Row containing top left corner of object
2424. * @param integer $x1 Distance to left side of object
2425. * @param integer $y1 Distance to top of object
2426. * @param integer $width Width of image frame
2427. * @param integer $height Height of image frame
2428. */
2429. function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
2430. {
2431. // Initialise end cell to the same as the start cell
2432. $col_end = $col_start; // Col containing lower right corner of object
2433. $row_end = $row_start; // Row containing bottom right corner of object
2435. // Zero the specified offset if greater than the cell dimensions
2436. if ($x1 >= PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_start))) {
2437. $x1 = 0;
2438. }
2439. if ($y1 >= PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_start + 1)) {
2440. $y1 = 0;
2441. }
2443. $width = $width + $x1 -1;
2444. $height = $height + $y1 -1;
2446. // Subtract the underlying cell widths to find the end cell of the image
2447. while ($width >= PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_end))) {
2448. $width -= PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_end));
2449. ++$col_end;
2450. }
2452. // Subtract the underlying cell heights to find the end cell of the image
2453. while ($height >= PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_end + 1)) {
2454. $height -= PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_end + 1);
2455. ++$row_end;
2456. }
2458. // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
2459. // with zero eight or width.
2460. //
2461. if ( PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_start)) == 0) {
2462. return;
2463. }
2464. if ( PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_end)) == 0) {
2465. return;
2466. }
2467. if ( PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_start + 1) == 0) {
2468. return;
2469. }
2470. if ( PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_end + 1) == 0) {
2471. return;
2472. }
2474. // Convert the pixel values to the percentage value expected by Excel
2475. $x1 = $x1 / PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_start)) * 1024;
2476. $y1 = $y1 / PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_start + 1) * 256;
2477. $x2 = $width / PHPExcel_Shared_Excel5:: sizeCol($this->_phpSheet, PHPExcel_Cell:: stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
2478. $y2 = $height / PHPExcel_Shared_Excel5:: sizeRow($this->_phpSheet, $row_end + 1) * 256; // Distance to bottom of object
2480. $this->_writeObjPicture($col_start, $x1,
2481. $row_start, $y1,
2482. $col_end, $x2,
2483. $row_end, $y2);
2484. }
2486. /**
2487. * Store the OBJ record that precedes an IMDATA record. This could be generalise
2488. * to support other Excel objects.
2489. *
2490. * @param integer $colL Column containing upper left corner of object
2491. * @param integer $dxL Distance from left side of cell
2492. * @param integer $rwT Row containing top left corner of object
2493. * @param integer $dyT Distance from top of cell
2494. * @param integer $colR Column containing lower right corner of object
2495. * @param integer $dxR Distance from right of cell
2496. * @param integer $rwB Row containing bottom right corner of object
2497. * @param integer $dyB Distance from bottom of cell
2498. */
2499. private function _writeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
2500. {
2501. $record = 0x005d; // Record identifier
2502. $length = 0x003c; // Bytes to follow
2504. $cObj = 0x0001; // Count of objects in file (set to 1)
2505. $OT = 0x0008; // Object type. 8 = Picture
2506. $id = 0x0001; // Object ID
2507. $grbit = 0x0614; // Option flags
2509. $cbMacro = 0x0000; // Length of FMLA structure
2510. $Reserved1 = 0x0000; // Reserved
2511. $Reserved2 = 0x0000; // Reserved
2513. $icvBack = 0x09; // Background colour
2514. $icvFore = 0x09; // Foreground colour
2515. $fls = 0x00; // Fill pattern
2516. $fAuto = 0x00; // Automatic fill
2517. $icv = 0x08; // Line colour
2518. $lns = 0xff; // Line style
2519. $lnw = 0x01; // Line weight
2520. $fAutoB = 0x00; // Automatic border
2521. $frs = 0x0000; // Frame style
2522. $cf = 0x0009; // Image format, 9 = bitmap
2523. $Reserved3 = 0x0000; // Reserved
2524. $cbPictFmla = 0x0000; // Length of FMLA structure
2525. $Reserved4 = 0x0000; // Reserved
2526. $grbit2 = 0x0001; // Option flags
2527. $Reserved5 = 0x0000; // Reserved
2530. $header = pack("vv", $record, $length);
2531. $data = pack("V", $cObj);
2532. $data .= pack("v", $OT);
2533. $data .= pack("v", $id);
2534. $data .= pack("v", $grbit);
2535. $data .= pack("v", $colL);
2536. $data .= pack("v", $dxL);
2537. $data .= pack("v", $rwT);
2538. $data .= pack("v", $dyT);
2539. $data .= pack("v", $colR);
2540. $data .= pack("v", $dxR);
2541. $data .= pack("v", $rwB);
2542. $data .= pack("v", $dyB);
2543. $data .= pack("v", $cbMacro);
2544. $data .= pack("V", $Reserved1);
2545. $data .= pack("v", $Reserved2);
2546. $data .= pack("C", $icvBack);
2547. $data .= pack("C", $icvFore);
2548. $data .= pack("C", $fls);
2549. $data .= pack("C", $fAuto);
2550. $data .= pack("C", $icv);
2551. $data .= pack("C", $lns);
2552. $data .= pack("C", $lnw);
2553. $data .= pack("C", $fAutoB);
2554. $data .= pack("v", $frs);
2555. $data .= pack("V", $cf);
2556. $data .= pack("v", $Reserved3);
2557. $data .= pack("v", $cbPictFmla);
2558. $data .= pack("v", $Reserved4);
2559. $data .= pack("v", $grbit2);
2560. $data .= pack("V", $Reserved5);
2562. $this->_append($header . $data);
2563. }
2565. /**
2566. * Convert a GD-image into the internal format.
2567. *
2568. * @access private
2569. * @param resource $image The image to process
2570. * @return array Array with data and properties of the bitmap
2571. */
2572. function _processBitmapGd($image) {
2573. $width = imagesx($image);
2574. $height = imagesy($image);
2576. $data = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
2577. for ($j=$height; $j--; ) {
2578. for ($i=0; $i < $width; ++$i) {
2579. $color = imagecolorsforindex($image, imagecolorat($image, $i, $j));
2580. foreach (array("red", "green", "blue") as $key) {
2581. $color[$key] = $color[$key] + round((255 - $color[$key]) * $color["alpha"] / 127);
2582. }
2583. $data .= chr($color["blue"]) . chr($color["green"]) . chr($color["red"]);
2584. }
2585. if (3*$width % 4) {
2586. $data .= str_repeat("\x00", 4 - 3*$width % 4);
2587. }
2588. }
2590. return array($width, $height, strlen($data), $data);
2591. }
2593. /**
2594. * Convert a 24 bit bitmap into the modified internal format used by Windows.
2595. * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
2596. * MSDN library.
2597. *
2598. * @access private
2599. * @param string $bitmap The bitmap to process
2600. * @return array Array with data and properties of the bitmap
2601. */
2602. function _processBitmap($bitmap)
2603. {
2604. // Open file.
2605. $bmp_fd = @ fopen($bitmap,"rb");
2606. if (!$bmp_fd) {
2607. throw new Exception("Couldn't import $bitmap");
2608. }
2610. // Slurp the file into a string.
2611. $data = fread($bmp_fd, filesize($bitmap));
2613. // Check that the file is big enough to be a bitmap.
2614. if ( strlen($data) <= 0x36) {
2615. throw new Exception("$bitmap doesn't contain enough data.\n");
2616. }
2618. // The first 2 bytes are used to identify the bitmap.
2619. $identity = unpack("A2ident", $data);
2620. if ($identity['ident'] != "BM") {
2621. throw new Exception("$bitmap doesn't appear to be a valid bitmap image.\n");
2622. }
2624. // Remove bitmap data: ID.
2625. $data = substr($data, 2);
2627. // Read and remove the bitmap size. This is more reliable than reading
2628. // the data size at offset 0x22.
2629. //
2630. $size_array = unpack("Vsa", substr($data, 0, 4));
2631. $size = $size_array['sa'];
2632. $data = substr($data, 4);
2633. $size -= 0x36; // Subtract size of bitmap header.
2634. $size += 0x0C; // Add size of BIFF header.
2636. // Remove bitmap data: reserved, offset, header length.
2637. $data = substr($data, 12);
2639. // Read and remove the bitmap width and height. Verify the sizes.
2640. $width_and_height = unpack("V2", substr($data, 0, 8));
2641. $width = $width_and_height[1];
2642. $height = $width_and_height[2];
2643. $data = substr($data, 8);
2644. if ($width > 0xFFFF) {
2645. throw new Exception("$bitmap: largest image width supported is 65k.\n");
2646. }
2647. if ($height > 0xFFFF) {
2648. throw new Exception("$bitmap: largest image height supported is 65k.\n");
2649. }
2651. // Read and remove the bitmap planes and bpp data. Verify them.
2652. $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
2653. $data = substr($data, 4);
2654. if ($planes_and_bitcount[2] != 24) { // Bitcount
2655. throw new Exception("$bitmap isn't a 24bit true color bitmap.\n");
2656. }
2657. if ($planes_and_bitcount[1] != 1) {
2658. throw new Exception("$bitmap: only 1 plane supported in bitmap image.\n");
2659. }
2661. // Read and remove the bitmap compression. Verify compression.
2662. $compression = unpack("Vcomp", substr($data, 0, 4));
2663. $data = substr($data, 4);
2665. //$compression = 0;
2666. if ($compression['comp'] != 0) {
2667. throw new Exception("$bitmap: compression not supported in bitmap image.\n");
2668. }
2670. // Remove bitmap data: data size, hres, vres, colours, imp. colours.
2671. $data = substr($data, 20);
2673. // Add the BITMAPCOREHEADER data
2674. $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
2675. $data = $header . $data;
2677. return (array($width, $height, $size, $data));
2678. }
2680. /**
2681. * Store the window zoom factor. This should be a reduced fraction but for
2682. * simplicity we will store all fractions with a numerator of 100.
2683. */
2684. private function _writeZoom()
2685. {
2686. // If scale is 100 we don't need to write a record
2687. if ($this->_phpSheet->getSheetView()->getZoomScale() == 100) {
2688. return;
2689. }
2691. $record = 0x00A0; // Record identifier
2692. $length = 0x0004; // Bytes to follow
2694. $header = pack("vv", $record, $length);
2695. $data = pack("vv", $this->_phpSheet->getSheetView()->getZoomScale(), 100);
2696. $this->_append($header . $data);
2697. }
2699. /**
2700. * Get Escher object
2701. *
2702. * @return PHPExcel_Shared_Escher
2703. */
2704. public function getEscher()
2705. {
2706. return $this->_escher;
2707. }
2709. /**
2710. * Set Escher object
2711. *
2712. * @param PHPExcel_Shared_Escher $pValue
2713. */
2714. public function setEscher(PHPExcel_Shared_Escher $pValue = null)
2715. {
2716. $this->_escher = $pValue;
2717. }
2719. /**
2720. * Write MSODRAWING record
2721. */
2722. private function _writeMsoDrawing()
2723. {
2724. // write the Escher stream if necessary
2725. if (isset($this->_escher)) {
2726. $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
2727. $data = $writer->close();
2728. $spOffsets = $writer->getSpOffsets();
2730. // write the neccesary MSODRAWING, OBJ records
2732. // split the Escher stream
2733. $spOffsets[0] = 0;
2734. $nm = count($spOffsets) - 1; // number of shapes excluding first shape
2735. for ($i = 1; $i <= $nm; ++$i) {
2736. // MSODRAWING record
2737. $record = 0x00EC; // Record identifier
2739. // chunk of Escher stream for one shape
2741. $dataChunk = substr($data, $spOffsets[$i -1], $spOffsets[$i] - $spOffsets[$i - 1]);
2743. $length = strlen($dataChunk);
2744. $header = pack("vv", $record, $length);
2746. $this->_append($header . $dataChunk);
2748. // OBJ record
2749. $record = 0x005D; // record identifier
2750. $objData = '';
2752. // ftCmo
2753. $objData .=
2754. pack('vvvvvVVV'
2755. , 0x0015 // 0x0015 = ftCmo
2756. , 0x0012 // length of ftCmo data
2757. , 0x0008 // object type, 0x0008 = picture
2758. , $i // object id number, Excel seems to use 1-based index, local for the sheet
2759. , 0x6011 // option flags, 0x6011 is what OpenOffice.org uses
2760. , 0 // reserved
2761. , 0 // reserved
2762. , 0 // reserved
2763. );
2764. // ftEnd
2765. $objData .=
2766. pack('vv'
2767. , 0x0000 // 0x0000 = ftEnd
2768. , 0x0000 // length of ftEnd data
2769. );
2771. $length = strlen($objData);
2772. $header = pack('vv', $record, $length);
2773. $this->_append($header . $objData);
2774. }
2775. }
2776. }
2778. /**
2779. * Store the DATAVALIDATIONS and DATAVALIDATION records.
2780. */
2781. private function _writeDataValidity()
2782. {
2783. // Datavalidation collection
2784. $dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
2786. // Write data validations?
2787. if ( count($dataValidationCollection) > 0) {
2789. // DATAVALIDATIONS record
2790. $record = 0x01B2; // Record identifier
2791. $length = 0x0012; // Bytes to follow
2793. $grbit = 0x0000; // Prompt box at cell, no cached validity data at DV records
2794. $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
2795. $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
2796. $objId = 0xFFFFFFFF; // Object identifier of drop down arrow object, or -1 if not visible
2798. $header = pack('vv', $record, $length);
2799. $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
2800. count($dataValidationCollection));
2801. $this->_append($header.$data);
2803. // DATAVALIDATION records
2804. $record = 0x01BE; // Record identifier
2806. foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
2807. // initialize record data
2808. $data = '';
2810. // options
2811. $options = 0x00000000;
2813. // data type
2814. $type = $dataValidation->getType();
2815. switch ($type) {
2816. case PHPExcel_Cell_DataValidation:: TYPE_NONE: $type = 0x00; break;
2817. case PHPExcel_Cell_DataValidation:: TYPE_WHOLE: $type = 0x01; break;
2818. case PHPExcel_Cell_DataValidation:: TYPE_DECIMAL: $type = 0x02; break;
2819. case PHPExcel_Cell_DataValidation:: TYPE_LIST: $type = 0x03; break;
2820. case PHPExcel_Cell_DataValidation:: TYPE_DATE: $type = 0x04; break;
2821. case PHPExcel_Cell_DataValidation:: TYPE_TIME: $type = 0x05; break;
2822. case PHPExcel_Cell_DataValidation:: TYPE_TEXTLENGTH: $type = 0x06; break;
2823. case PHPExcel_Cell_DataValidation:: TYPE_CUSTOM: $type = 0x07; break;
2824. }
2825. $options |= $type << 0;
2827. // error style
2828. $errorStyle = $dataValidation->getType();
2829. switch ($errorStyle) {
2830. case PHPExcel_Cell_DataValidation:: STYLE_STOP: $errorStyle = 0x00; break;
2831. case PHPExcel_Cell_DataValidation:: STYLE_WARNING: $errorStyle = 0x01; break;
2832. case PHPExcel_Cell_DataValidation:: STYLE_INFORMATION: $errorStyle = 0x02; break;
2833. }
2834. $options |= $errorStyle << 4;
2836. // explicit formula?
2837. if ($type == 0x03 && preg_match('/^\".*\"$/', $dataValidation->getFormula1())) {
2838. $options |= 0x01 << 7;
2839. }
2841. // empty cells allowed
2842. $options |= $dataValidation->getAllowBlank() << 8;
2844. // show drop down
2845. $options |= (!$dataValidation->getShowDropDown()) << 9;
2847. // show input message
2848. $options |= $dataValidation->getShowInputMessage() << 18;
2850. // show error message
2851. $options |= $dataValidation->getShowErrorMessage() << 19;
2853. // condition operator
2854. $operator = $dataValidation->getOperator();
2855. switch ($operator) {
2856. case PHPExcel_Cell_DataValidation:: OPERATOR_BETWEEN: $operator = 0x00 ; break;
2857. case PHPExcel_Cell_DataValidation:: OPERATOR_NOTBETWEEN: $operator = 0x01 ; break;
2858. case PHPExcel_Cell_DataValidation:: OPERATOR_EQUAL: $operator = 0x02 ; break;
2859. case PHPExcel_Cell_DataValidation:: OPERATOR_NOTEQUAL: $operator = 0x03 ; break;
2860. case PHPExcel_Cell_DataValidation:: OPERATOR_GREATERTHAN: $operator = 0x04 ; break;
2861. case PHPExcel_Cell_DataValidation:: OPERATOR_LESSTHAN: $operator = 0x05 ; break;
2862. case PHPExcel_Cell_DataValidation:: OPERATOR_GREATERTHANOREQUAL: $operator = 0x06; break;
2863. case PHPExcel_Cell_DataValidation:: OPERATOR_LESSTHANOREQUAL: $operator = 0x07 ; break;
2864. }
2865. $options |= $operator << 20;
2867. $data = pack('V', $options);
2869. // prompt title
2870. $promptTitle = $dataValidation->getPromptTitle() !== '' ?
2871. $dataValidation->getPromptTitle() : chr(0);
2872. $data .= PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($promptTitle);
2874. // error title
2875. $errorTitle = $dataValidation->getErrorTitle() !== '' ?
2876. $dataValidation->getErrorTitle() : chr(0);
2877. $data .= PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($errorTitle);
2879. // prompt text
2880. $prompt = $dataValidation->getPrompt() !== '' ?
2881. $dataValidation->getPrompt() : chr(0);
2882. $data .= PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($prompt);
2884. // error text
2885. $error = $dataValidation->getError() !== '' ?
2886. $dataValidation->getError() : chr(0);
2887. $data .= PHPExcel_Shared_String:: UTF8toBIFF8UnicodeLong($error);
2889. // formula 1
2890. try {
2891. $formula1 = $dataValidation->getFormula1();
2892. if ($type == 0x03) { // list type
2893. $formula1 = str_replace(',', chr(0), $formula1);
2894. }
2895. $this->_parser-> parse($formula1);
2896. $formula1 = $this->_parser-> toReversePolish();
2897. $sz1 = strlen($formula1);
2899. } catch(Exception $e) {
2900. $sz1 = 0;
2901. $formula1 = '';
2902. }
2903. $data .= pack('vv', $sz1, 0x0000);
2904. $data .= $formula1;
2906. // formula 2
2907. try {
2908. $formula2 = $dataValidation->getFormula2();
2909. if ($formula2 === '') {
2910. throw new Exception('No formula2');
2911. }
2912. $this->_parser-> parse($formula2);
2913. $formula2 = $this->_parser-> toReversePolish();
2914. $sz2 = strlen($formula2);
2916. } catch(Exception $e) {
2917. $sz2 = 0;
2918. $formula2 = '';
2919. }
2920. $data .= pack('vv', $sz2, 0x0000);
2921. $data .= $formula2;
2923. // cell range address list
2924. $data .= pack('v', 0x0001);
2925. $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate);
2927. $length = strlen($data);
2928. $header = pack("vv", $record, $length);
2930. $this->_append($header . $data);
2931. }
2932. }
2933. }
2935. /**
2936. * Map Error code
2937. */
2938. private function _mapErrorCode($errorCode) {
2939. switch ($errorCode) {
2940. case '#NULL!': return 0x00;
2941. case '#DIV/0!': return 0x07;
2942. case '#VALUE!': return 0x0F;
2943. case '#REF!': return 0x17;
2944. case '#NAME?': return 0x1D;
2945. case '#NUM!': return 0x24;
2946. case '#N/A': return 0x2A;
2947. }
2949. return 0;
2950. }
2952. }

Documentation generated on Sun, 27 Feb 2011 16:37:24 -0800 by phpDocumentor 1.4.3