ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
Worksheet.php
Go to the documentation of this file.
1<?php
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// */
62
63
72{
78 private $_parser;
79
85
90 public $_colinfo;
91
97
103
109
115
121
127
133
139
145
149 private $_colors;
150
156
162
168
174
180
187
193 private $_escher;
194
201
214 public function __construct(&$str_total, &$str_unique, &$str_table, &$colors,
215 $parser, $preCalculateFormulas, $phpSheet)
216 {
217 // It needs to call its parent's constructor explicitly
218 parent::__construct();
219
220 // change BIFFwriter limit for CONTINUE records
221// $this->_limit = 8224;
222
223
224 $this->_preCalculateFormulas = $preCalculateFormulas;
225 $this->_str_total = &$str_total;
226 $this->_str_unique = &$str_unique;
227 $this->_str_table = &$str_table;
228 $this->_colors = &$colors;
229 $this->_parser = $parser;
230
231 $this->_phpSheet = $phpSheet;
232
233 //$this->ext_sheets = array();
234 //$this->offset = 0;
235 $this->_xls_strmax = 255;
236 $this->_colinfo = array();
237 $this->_selection = array(0,0,0,0);
238 $this->_active_pane = 3;
239
240 $this->_print_headers = 0;
241
242 $this->_outline_style = 0;
243 $this->_outline_below = 1;
244 $this->_outline_right = 1;
245 $this->_outline_on = 1;
246
247 $this->_fntHashIndex = array();
248
249 // calculate values for DIMENSIONS record
250 $minR = 1;
251 $minC = 'A';
252
253 $maxR = $this->_phpSheet->getHighestRow();
254 $maxC = $this->_phpSheet->getHighestColumn();
255
256 // Determine lowest and highest column and row
257// $this->_firstRowIndex = ($minR > 65535) ? 65535 : $minR;
258 $this->_lastRowIndex = ($maxR > 65535) ? 65535 : $maxR ;
259
260 $this->_firstColumnIndex = PHPExcel_Cell::columnIndexFromString($minC);
261 $this->_lastColumnIndex = PHPExcel_Cell::columnIndexFromString($maxC);
262
263// if ($this->_firstColumnIndex > 255) $this->_firstColumnIndex = 255;
264 if ($this->_lastColumnIndex > 255) $this->_lastColumnIndex = 255;
265
266 $this->_countCellStyleXfs = count($phpSheet->getParent()->getCellStyleXfCollection());
267 }
268
276 function close()
277 {
279
280 $num_sheets = $_phpSheet->getParent()->getSheetCount();
281
282 // Write BOF record
283 $this->_storeBof(0x0010);
284
285 // Write PRINTHEADERS
286 $this->_writePrintHeaders();
287
288 // Write PRINTGRIDLINES
289 $this->_writePrintGridlines();
290
291 // Write GRIDSET
292 $this->_writeGridset();
293
294 // Calculate column widths
295 $_phpSheet->calculateColumnWidths();
296
297 // Column dimensions
298 if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
299 $defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont());
300 }
301
302 $columnDimensions = $_phpSheet->getColumnDimensions();
303 $maxCol = $this->_lastColumnIndex -1;
304 for ($i = 0; $i <= $maxCol; ++$i) {
305 $hidden = 0;
306 $level = 0;
307 $xfIndex = 15; // there are 15 cell style Xfs
308
309 $width = $defaultWidth;
310
312 if (isset($columnDimensions[$columnLetter])) {
313 $columnDimension = $columnDimensions[$columnLetter];
314 if ($columnDimension->getWidth() >= 0) {
315 $width = $columnDimension->getWidth();
316 }
317 $hidden = $columnDimension->getVisible() ? 0 : 1;
318 $level = $columnDimension->getOutlineLevel();
319 $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs
320 }
321
322 // Components of _colinfo:
323 // $firstcol first column on the range
324 // $lastcol last column on the range
325 // $width width to set
326 // $xfIndex The optional cell style Xf index to apply to the columns
327 // $hidden The optional hidden atribute
328 // $level The optional outline level
329 $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
330 }
331
332 // Write GUTS
333 $this->_writeGuts();
334
335 // Write DEFAULTROWHEIGHT
336 $this->_writeDefaultRowHeight();
337
338 // Write WSBOOL
339 $this->_writeWsbool();
340
341 // Write horizontal and vertical page breaks
342 $this->_writeBreaks();
343
344 // Write page header
345 $this->_writeHeader();
346
347 // Write page footer
348 $this->_writeFooter();
349
350 // Write page horizontal centering
351 $this->_writeHcenter();
352
353 // Write page vertical centering
354 $this->_writeVcenter();
355
356 // Write left margin
357 $this->_writeMarginLeft();
358
359 // Write right margin
360 $this->_writeMarginRight();
361
362 // Write top margin
363 $this->_writeMarginTop();
364
365 // Write bottom margin
366 $this->_writeMarginBottom();
367
368 // Write page setup
369 $this->_writeSetup();
370
371 // Write sheet protection
372 $this->_writeProtect();
373
374 // Write SCENPROTECT
375 $this->_writeScenProtect();
376
377 // Write OBJECTPROTECT
378 $this->_writeObjectProtect();
379
380 // Write sheet password
381 $this->_writePassword();
382
383 // Write DEFCOLWIDTH record
384 $this->_writeDefcol();
385
386 // Write the COLINFO records if they exist
387 if (!empty($this->_colinfo)) {
388 $colcount = count($this->_colinfo);
389 for ($i = 0; $i < $colcount; ++$i) {
390 $this->_writeColinfo($this->_colinfo[$i]);
391 }
392 }
393 $autoFilterRange = $_phpSheet->getAutoFilter()->getRange();
394 if (!empty($autoFilterRange)) {
395 // Write AUTOFILTERINFO
396 $this->_writeAutoFilterInfo();
397 }
398
399 // Write sheet dimensions
400 $this->_writeDimensions();
401
402 // Row dimensions
403 foreach ($_phpSheet->getRowDimensions() as $rowDimension) {
404 $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs
405 $this->_writeRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
406 }
407
408 // Write Cells
409 foreach ($_phpSheet->getCellCollection() as $cellID) {
410 $cell = $_phpSheet->getCell($cellID);
411 $row = $cell->getRow() - 1;
412 $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
413
414 // Don't break Excel!
415// if ($row + 1 > 65536 or $column + 1 > 256) {
416 if ($row > 65535 || $column > 255) {
417 break;
418 }
419
420 // Write cell value
421 $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs
422
423 $cVal = $cell->getValue();
424 if ($cVal instanceof PHPExcel_RichText) {
425 // $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex);
426 $arrcRun = array();
427 $str_len = PHPExcel_Shared_String::CountCharacters($cVal->getPlainText(), 'UTF-8');
428 $str_pos = 0;
429 $elements = $cVal->getRichTextElements();
430 foreach ($elements as $element) {
431 // FONT Index
432 if ($element instanceof PHPExcel_RichText_Run) {
433 $str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()];
434 }
435 else {
436 $str_fontidx = 0;
437 }
438 $arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
439 // Position FROM
440 $str_pos += PHPExcel_Shared_String::CountCharacters($element->getText(), 'UTF-8');
441 }
442 $this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
443 } else {
444 switch ($cell->getDatatype()) {
447 if ($cVal === '' || $cVal === null) {
448 $this->_writeBlank($row, $column, $xfIndex);
449 } else {
450 $this->_writeString($row, $column, $cVal, $xfIndex);
451 }
452 break;
453
455 $this->_writeNumber($row, $column, $cVal, $xfIndex);
456 break;
457
459 $calculatedValue = $this->_preCalculateFormulas ?
460 $cell->getCalculatedValue() : null;
461 $this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue);
462 break;
463
465 $this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex);
466 break;
467
469 $this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex);
470 break;
471
472 }
473 }
474 }
475
476 // Append
477 $this->_writeMsoDrawing();
478
479 // Write WINDOW2 record
480 $this->_writeWindow2();
481
482 // Write PLV record
483 $this->_writePageLayoutView();
484
485 // Write ZOOM record
486 $this->_writeZoom();
487 if ($_phpSheet->getFreezePane()) {
488 $this->_writePanes();
489 }
490
491 // Write SELECTION record
492 $this->_writeSelection();
493
494 // Write MergedCellsTable Record
495 $this->_writeMergedCells();
496
497 // Hyperlinks
498 foreach ($_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
500
501 $url = $hyperlink->getUrl();
502
503 if ( strpos($url, 'sheet://') !== false ) {
504 // internal to current workbook
505 $url = str_replace('sheet://', 'internal:', $url);
506
507 } else if ( preg_match('/^(http:|https:|ftp:|mailto:)/', $url) ) {
508 // URL
509 // $url = $url;
510
511 } else {
512 // external (local file)
513 $url = 'external:' . $url;
514 }
515
517 }
518
519 $this->_writeDataValidity();
520 $this->_writeSheetLayout();
521
522 // Write SHEETPROTECTION record
523 $this->_writeSheetProtection();
524 $this->_writeRangeProtection();
525
526 $arrConditionalStyles = $_phpSheet->getConditionalStylesCollection();
527 if(!empty($arrConditionalStyles)){
528 $arrConditional = array();
529 // @todo CFRule & CFHeader
530 // Write CFHEADER record
531 $this->_writeCFHeader();
532 // Write ConditionalFormattingTable records
533 foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
534 foreach ($conditionalStyles as $conditional) {
535 if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
536 || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
537 if(!in_array($conditional->getHashCode(), $arrConditional)){
538 $arrConditional[] = $conditional->getHashCode();
539 // Write CFRULE record
540 $this->_writeCFRule($conditional);
541 }
542 }
543 }
544 }
545 }
546
547 $this->_storeEof();
548 }
549
558 private function _writeBIFF8CellRangeAddressFixed($range = 'A1')
559 {
560 $explodes = explode(':', $range);
561
562 // extract first cell, e.g. 'A1'
563 $firstCell = $explodes[0];
564
565 // extract last cell, e.g. 'B6'
566 if (count($explodes) == 1) {
567 $lastCell = $firstCell;
568 } else {
569 $lastCell = $explodes[1];
570 }
571
572 $firstCellCoordinates = PHPExcel_Cell::coordinateFromString($firstCell); // e.g. array(0, 1)
573 $lastCellCoordinates = PHPExcel_Cell::coordinateFromString($lastCell); // e.g. array(1, 6)
574
575 return(pack('vvvv',
576 $firstCellCoordinates[1] - 1,
577 $lastCellCoordinates[1] - 1,
578 PHPExcel_Cell::columnIndexFromString($firstCellCoordinates[0]) - 1,
579 PHPExcel_Cell::columnIndexFromString($lastCellCoordinates[0]) - 1
580 ));
581 }
582
589 function getData()
590 {
591 $buffer = 4096;
592
593 // Return data stored in memory
594 if (isset($this->_data)) {
595 $tmp = $this->_data;
596 unset($this->_data);
597 return $tmp;
598 }
599 // No data to return
600 return false;
601 }
602
609 function printRowColHeaders($print = 1)
610 {
611 $this->_print_headers = $print;
612 }
613
623 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
624 {
625 $this->_outline_on = $visible;
626 $this->_outline_below = $symbols_below;
627 $this->_outline_right = $symbols_right;
628 $this->_outline_style = $auto_style;
629
630 // Ensure this is a boolean vale for Window2
631 if ($this->_outline_on) {
632 $this->_outline_on = 1;
633 }
634 }
635
650 private function _writeNumber($row, $col, $num, $xfIndex)
651 {
652 $record = 0x0203; // Record identifier
653 $length = 0x000E; // Number of bytes to follow
654
655 $header = pack("vv", $record, $length);
656 $data = pack("vvv", $row, $col, $xfIndex);
657 $xl_double = pack("d", $num);
658 if (self::getByteOrder()) { // if it's Big Endian
659 $xl_double = strrev($xl_double);
660 }
661
662 $this->_append($header.$data.$xl_double);
663 return(0);
664 }
665
674 private function _writeString($row, $col, $str, $xfIndex)
675 {
676 $this->_writeLabelSst($row, $col, $str, $xfIndex);
677 }
678
688 private function _writeRichTextString($row, $col, $str, $xfIndex, $arrcRun){
689 $record = 0x00FD; // Record identifier
690 $length = 0x000A; // Bytes to follow
692
693 /* check if string is already present */
694 if (!isset($this->_str_table[$str])) {
695 $this->_str_table[$str] = $this->_str_unique++;
696 }
697 $this->_str_total++;
698
699 $header = pack('vv', $record, $length);
700 $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
701 $this->_append($header.$data);
702 }
703
719 private function _writeLabel($row, $col, $str, $xfIndex)
720 {
721 $strlen = strlen($str);
722 $record = 0x0204; // Record identifier
723 $length = 0x0008 + $strlen; // Bytes to follow
724
725 $str_error = 0;
726
727 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
728 $str = substr($str, 0, $this->_xls_strmax);
729 $length = 0x0008 + $this->_xls_strmax;
730 $strlen = $this->_xls_strmax;
731 $str_error = -3;
732 }
733
734 $header = pack("vv", $record, $length);
735 $data = pack("vvvv", $row, $col, $xfIndex, $strlen);
736 $this->_append($header . $data . $str);
737 return($str_error);
738 }
739
755 private function _writeLabelSst($row, $col, $str, $xfIndex)
756 {
757 $record = 0x00FD; // Record identifier
758 $length = 0x000A; // Bytes to follow
759
761
762 /* check if string is already present */
763 if (!isset($this->_str_table[$str])) {
764 $this->_str_table[$str] = $this->_str_unique++;
765 }
766 $this->_str_total++;
767
768 $header = pack('vv', $record, $length);
769 $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]);
770 $this->_append($header.$data);
771 }
772
781 private function _writeNote($row, $col, $note)
782 {
783 $note_length = strlen($note);
784 $record = 0x001C; // Record identifier
785 $max_length = 2048; // Maximun length for a NOTE record
786
787 // Length for this record is no more than 2048 + 6
788 $length = 0x0006 + min($note_length, 2048);
789 $header = pack("vv", $record, $length);
790 $data = pack("vvv", $row, $col, $note_length);
791 $this->_append($header . $data . substr($note, 0, 2048));
792
793 for ($i = $max_length; $i < $note_length; $i += $max_length) {
794 $chunk = substr($note, $i, $max_length);
795 $length = 0x0006 + strlen($chunk);
796 $header = pack("vv", $record, $length);
797 $data = pack("vvv", -1, 0, strlen($chunk));
798 $this->_append($header.$data.$chunk);
799 }
800 return(0);
801 }
802
819 function _writeBlank($row, $col, $xfIndex)
820 {
821 $record = 0x0201; // Record identifier
822 $length = 0x0006; // Number of bytes to follow
823
824 $header = pack("vv", $record, $length);
825 $data = pack("vvv", $row, $col, $xfIndex);
826 $this->_append($header . $data);
827 return 0;
828 }
829
839 private function _writeBoolErr($row, $col, $value, $isError, $xfIndex)
840 {
841 $record = 0x0205;
842 $length = 8;
843
844 $header = pack("vv", $record, $length);
845 $data = pack("vvvCC", $row, $col, $xfIndex, $value, $isError);
846 $this->_append($header . $data);
847 return 0;
848 }
849
866 private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
867 {
868 $record = 0x0006; // Record identifier
869
870 // Initialize possible additional value for STRING record that should be written after the FORMULA record?
871 $stringValue = null;
872
873 // calculated value
874 if (isset($calculatedValue)) {
875 // Since we can't yet get the data type of the calculated value,
876 // we use best effort to determine data type
877 if (is_bool($calculatedValue)) {
878 // Boolean value
879 $num = pack('CCCvCv', 0x01, 0x00, (int)$calculatedValue, 0x00, 0x00, 0xFFFF);
880 } elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
881 // Numeric value
882 $num = pack('d', $calculatedValue);
883 } elseif (is_string($calculatedValue)) {
884 if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
885 // Error value
886 $num = pack('CCCvCv', 0x02, 0x00, self::_mapErrorCode($calculatedValue), 0x00, 0x00, 0xFFFF);
887 } elseif ($calculatedValue === '') {
888 // Empty string (and BIFF8)
889 $num = pack('CCCvCv', 0x03, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
890 } else {
891 // Non-empty string value (or empty string BIFF5)
892 $stringValue = $calculatedValue;
893 $num = pack('CCCvCv', 0x00, 0x00, 0x00, 0x00, 0x00, 0xFFFF);
894 }
895 } else {
896 // We are really not supposed to reach here
897 $num = pack('d', 0x00);
898 }
899 } else {
900 $num = pack('d', 0x00);
901 }
902
903 $grbit = 0x03; // Option flags
904 $unknown = 0x0000; // Must be zero
905
906 // Strip the '=' or '@' sign at the beginning of the formula string
907 if ($formula{0} == '=') {
908 $formula = substr($formula,1);
909 } else {
910 // Error handling
911 $this->_writeString($row, $col, 'Unrecognised character for formula');
912 return -1;
913 }
914
915 // Parse the formula using the parser in Parser.php
916 try {
917 $error = $this->_parser->parse($formula);
918 $formula = $this->_parser->toReversePolish();
919
920 $formlen = strlen($formula); // Length of the binary string
921 $length = 0x16 + $formlen; // Length of the record data
922
923 $header = pack("vv", $record, $length);
924
925 $data = pack("vvv", $row, $col, $xfIndex)
926 . $num
927 . pack("vVv", $grbit, $unknown, $formlen);
928 $this->_append($header . $data . $formula);
929
930 // Append also a STRING record if necessary
931 if ($stringValue !== null) {
932 $this->_writeStringRecord($stringValue);
933 }
934
935 return 0;
936
937 } catch (PHPExcel_Exception $e) {
938 // do nothing
939 }
940
941 }
942
948 private function _writeStringRecord($stringValue)
949 {
950 $record = 0x0207; // Record identifier
952
953 $length = strlen($data);
954 $header = pack('vv', $record, $length);
955
956 $this->_append($header . $data);
957 }
958
979 private function _writeUrl($row, $col, $url)
980 {
981 // Add start row and col to arg list
982 return($this->_writeUrlRange($row, $col, $row, $col, $url));
983 }
984
1000 function _writeUrlRange($row1, $col1, $row2, $col2, $url)
1001 {
1002 // Check for internal/external sheet links or default to web link
1003 if (preg_match('[^internal:]', $url)) {
1004 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url));
1005 }
1006 if (preg_match('[^external:]', $url)) {
1007 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url));
1008 }
1009 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url));
1010 }
1011
1026 function _writeUrlWeb($row1, $col1, $row2, $col2, $url)
1027 {
1028 $record = 0x01B8; // Record identifier
1029 $length = 0x00000; // Bytes to follow
1030
1031 // Pack the undocumented parts of the hyperlink stream
1032 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1033 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
1034
1035 // Pack the option flags
1036 $options = pack("V", 0x03);
1037
1038 // Convert URL to a null terminated wchar string
1039 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1040 $url = $url . "\0\0\0";
1041
1042 // Pack the length of the URL
1043 $url_len = pack("V", strlen($url));
1044
1045 // Calculate the data length
1046 $length = 0x34 + strlen($url);
1047
1048 // Pack the header data
1049 $header = pack("vv", $record, $length);
1050 $data = pack("vvvv", $row1, $row2, $col1, $col2);
1051
1052 // Write the packed data
1053 $this->_append($header . $data .
1055 $unknown2 . $url_len . $url);
1056 return 0;
1057 }
1058
1071 function _writeUrlInternal($row1, $col1, $row2, $col2, $url)
1072 {
1073 $record = 0x01B8; // Record identifier
1074 $length = 0x00000; // Bytes to follow
1075
1076 // Strip URL type
1077 $url = preg_replace('/^internal:/', '', $url);
1078
1079 // Pack the undocumented parts of the hyperlink stream
1080 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1081
1082 // Pack the option flags
1083 $options = pack("V", 0x08);
1084
1085 // Convert the URL type and to a null terminated wchar string
1086 $url .= "\0";
1087
1088 // character count
1090 $url_len = pack('V', $url_len);
1091
1092 $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8');
1093
1094 // Calculate the data length
1095 $length = 0x24 + strlen($url);
1096
1097 // Pack the header data
1098 $header = pack("vv", $record, $length);
1099 $data = pack("vvvv", $row1, $row2, $col1, $col2);
1100
1101 // Write the packed data
1102 $this->_append($header . $data .
1104 $url_len . $url);
1105 return 0;
1106 }
1107
1124 function _writeUrlExternal($row1, $col1, $row2, $col2, $url)
1125 {
1126 // Network drives are different. We will handle them separately
1127 // MS/Novell network drives and shares start with \\
1128 if (preg_match('[^external:\\\\]', $url)) {
1129 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
1130 }
1131
1132 $record = 0x01B8; // Record identifier
1133 $length = 0x00000; // Bytes to follow
1134
1135 // Strip URL type and change Unix dir separator to Dos style (if needed)
1136 //
1137 $url = preg_replace('/^external:/', '', $url);
1138 $url = preg_replace('/\//', "\\", $url);
1139
1140 // Determine if the link is relative or absolute:
1141 // relative if link contains no dir separator, "somefile.xls"
1142 // relative if link starts with up-dir, "..\..\somefile.xls"
1143 // otherwise, absolute
1144
1145 $absolute = 0x00; // relative path
1146 if ( preg_match('/^[A-Z]:/', $url) ) {
1147 $absolute = 0x02; // absolute path on Windows, e.g. C:\...
1148 }
1150
1151 // Determine if the link contains a sheet reference and change some of the
1152 // parameters accordingly.
1153 // Split the dir name and sheet name (if it exists)
1155 if (preg_match("/\#/", $url)) {
1156 $link_type |= 0x08;
1157 }
1158
1159
1160 // Pack the link type
1162
1163 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1164 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
1165 $up_count = pack("v", $up_count);
1166
1167 // Store the short dos dir name (null terminated)
1168 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
1169
1170 // Store the long dir name as a wchar string (non-null terminated)
1171 $dir_long = $dir_long . "\0";
1172
1173 // Pack the lengths of the dir strings
1174 $dir_short_len = pack("V", strlen($dir_short) );
1175 $dir_long_len = pack("V", strlen($dir_long) );
1176 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
1177
1178 // Pack the undocumented parts of the hyperlink stream
1179 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
1180 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
1181 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
1182 $unknown4 = pack("v", 0x03 );
1183
1184 // Pack the main data stream
1185 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
1186 $unknown1 .
1187 $link_type .
1188 $unknown2 .
1189 $up_count .
1191 $dir_short .
1192 $unknown3 .
1193 $stream_len ;/*.
1194 $dir_long_len .
1195 $unknown4 .
1196 $dir_long .
1197 $sheet_len .
1198 $sheet ;*/
1199
1200 // Pack the header data
1201 $length = strlen($data);
1202 $header = pack("vv", $record, $length);
1203
1204 // Write the packed data
1205 $this->_append($header. $data);
1206 return 0;
1207 }
1208
1219 private function _writeRow($row, $height, $xfIndex, $hidden = false, $level = 0)
1220 {
1221 $record = 0x0208; // Record identifier
1222 $length = 0x0010; // Number of bytes to follow
1223
1224 $colMic = 0x0000; // First defined column
1225 $colMac = 0x0000; // Last defined column
1226 $irwMac = 0x0000; // Used by Excel to optimise loading
1227 $reserved = 0x0000; // Reserved
1228 $grbit = 0x0000; // Option flags
1229 $ixfe = $xfIndex;
1230
1231 if ( $height < 0 ){
1232 $height = null;
1233 }
1234
1235 // Use _writeRow($row, null, $XF) to set XF format without setting height
1236 if ($height != null) {
1237 $miyRw = $height * 20; // row height
1238 } else {
1239 $miyRw = 0xff; // default row height is 256
1240 }
1241
1242 // Set the options flags. fUnsynced is used to show that the font and row
1243 // heights are not compatible. This is usually the case for WriteExcel.
1244 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
1245 // is collapsed. Instead it is used to indicate that the previous row is
1246 // collapsed. The zero height flag, 0x20, is used to collapse a row.
1247
1248 $grbit |= $level;
1249 if ($hidden) {
1250 $grbit |= 0x0030;
1251 }
1252 if ($height !== null) {
1253 $grbit |= 0x0040; // fUnsynced
1254 }
1255 if ($xfIndex !== 0xF) {
1256 $grbit |= 0x0080;
1257 }
1258 $grbit |= 0x0100;
1259
1260 $header = pack("vv", $record, $length);
1261 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
1262 $irwMac,$reserved, $grbit, $ixfe);
1263 $this->_append($header.$data);
1264 }
1265
1269 private function _writeDimensions()
1270 {
1271 $record = 0x0200; // Record identifier
1272
1273 $length = 0x000E;
1274 $data = pack('VVvvv'
1275 , $this->_firstRowIndex
1276 , $this->_lastRowIndex + 1
1277 , $this->_firstColumnIndex
1278 , $this->_lastColumnIndex + 1
1279 , 0x0000 // reserved
1280 );
1281
1282 $header = pack("vv", $record, $length);
1283 $this->_append($header.$data);
1284 }
1285
1289 private function _writeWindow2()
1290 {
1291 $record = 0x023E; // Record identifier
1292 $length = 0x0012;
1293
1294 $grbit = 0x00B6; // Option flags
1295 $rwTop = 0x0000; // Top row visible in window
1296 $colLeft = 0x0000; // Leftmost column visible in window
1297
1298
1299 // The options flags that comprise $grbit
1300 $fDspFmla = 0; // 0 - bit
1301 $fDspGrid = $this->_phpSheet->getShowGridlines() ? 1 : 0; // 1
1302 $fDspRwCol = $this->_phpSheet->getShowRowColHeaders() ? 1 : 0; // 2
1303 $fFrozen = $this->_phpSheet->getFreezePane() ? 1 : 0; // 3
1304 $fDspZeros = 1; // 4
1305 $fDefaultHdr = 1; // 5
1306 $fArabic = $this->_phpSheet->getRightToLeft() ? 1 : 0; // 6
1307 $fDspGuts = $this->_outline_on; // 7
1308 $fFrozenNoSplit = 0; // 0 - bit
1309 // no support in PHPExcel for selected sheet, therefore sheet is only selected if it is the active sheet
1310 $fSelected = ($this->_phpSheet === $this->_phpSheet->getParent()->getActiveSheet()) ? 1 : 0;
1311 $fPaged = 1; // 2
1312 $fPageBreakPreview = $this->_phpSheet->getSheetView()->getView() === PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW;
1313
1314 $grbit = $fDspFmla;
1315 $grbit |= $fDspGrid << 1;
1316 $grbit |= $fDspRwCol << 2;
1317 $grbit |= $fFrozen << 3;
1318 $grbit |= $fDspZeros << 4;
1319 $grbit |= $fDefaultHdr << 5;
1320 $grbit |= $fArabic << 6;
1321 $grbit |= $fDspGuts << 7;
1322 $grbit |= $fFrozenNoSplit << 8;
1323 $grbit |= $fSelected << 9;
1324 $grbit |= $fPaged << 10;
1325 $grbit |= $fPageBreakPreview << 11;
1326
1327 $header = pack("vv", $record, $length);
1328 $data = pack("vvv", $grbit, $rwTop, $colLeft);
1329
1330 // FIXME !!!
1331 $rgbHdr = 0x0040; // Row/column heading and gridline color index
1332 $zoom_factor_page_break = ($fPageBreakPreview? $this->_phpSheet->getSheetView()->getZoomScale() : 0x0000);
1333 $zoom_factor_normal = $this->_phpSheet->getSheetView()->getZoomScaleNormal();
1334
1335 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
1336
1337 $this->_append($header.$data);
1338 }
1339
1343 private function _writeDefaultRowHeight()
1344 {
1345 $defaultRowHeight = $this->_phpSheet->getDefaultRowDimension()->getRowHeight();
1346
1347 if ($defaultRowHeight < 0) {
1348 return;
1349 }
1350
1351 // convert to twips
1352 $defaultRowHeight = (int) 20 * $defaultRowHeight;
1353
1354 $record = 0x0225; // Record identifier
1355 $length = 0x0004; // Number of bytes to follow
1356
1357 $header = pack("vv", $record, $length);
1358 $data = pack("vv", 1, $defaultRowHeight);
1359 $this->_append($header . $data);
1360 }
1361
1365 private function _writeDefcol()
1366 {
1367 $defaultColWidth = 8;
1368
1369 $record = 0x0055; // Record identifier
1370 $length = 0x0002; // Number of bytes to follow
1371
1372 $header = pack("vv", $record, $length);
1373 $data = pack("v", $defaultColWidth);
1374 $this->_append($header . $data);
1375 }
1376
1391 private function _writeColinfo($col_array)
1392 {
1393 if (isset($col_array[0])) {
1394 $colFirst = $col_array[0];
1395 }
1396 if (isset($col_array[1])) {
1397 $colLast = $col_array[1];
1398 }
1399 if (isset($col_array[2])) {
1400 $coldx = $col_array[2];
1401 } else {
1402 $coldx = 8.43;
1403 }
1404 if (isset($col_array[3])) {
1405 $xfIndex = $col_array[3];
1406 } else {
1407 $xfIndex = 15;
1408 }
1409 if (isset($col_array[4])) {
1410 $grbit = $col_array[4];
1411 } else {
1412 $grbit = 0;
1413 }
1414 if (isset($col_array[5])) {
1415 $level = $col_array[5];
1416 } else {
1417 $level = 0;
1418 }
1419 $record = 0x007D; // Record identifier
1420 $length = 0x000C; // Number of bytes to follow
1421
1422 $coldx *= 256; // Convert to units of 1/256 of a char
1423
1424 $ixfe = $xfIndex;
1425 $reserved = 0x0000; // Reserved
1426
1427 $level = max(0, min($level, 7));
1428 $grbit |= $level << 8;
1429
1430 $header = pack("vv", $record, $length);
1431 $data = pack("vvvvvv", $colFirst, $colLast, $coldx,
1432 $ixfe, $grbit, $reserved);
1433 $this->_append($header.$data);
1434 }
1435
1439 private function _writeSelection()
1440 {
1441 // look up the selected cell range
1442 $selectedCells = $this->_phpSheet->getSelectedCells();
1443 $selectedCells = PHPExcel_Cell::splitRange($this->_phpSheet->getSelectedCells());
1444 $selectedCells = $selectedCells[0];
1445 if (count($selectedCells) == 2) {
1446 list($first, $last) = $selectedCells;
1447 } else {
1448 $first = $selectedCells[0];
1449 $last = $selectedCells[0];
1450 }
1451
1452 list($colFirst, $rwFirst) = PHPExcel_Cell::coordinateFromString($first);
1453 $colFirst = PHPExcel_Cell::columnIndexFromString($colFirst) - 1; // base 0 column index
1454 --$rwFirst; // base 0 row index
1455
1456 list($colLast, $rwLast) = PHPExcel_Cell::coordinateFromString($last);
1457 $colLast = PHPExcel_Cell::columnIndexFromString($colLast) - 1; // base 0 column index
1458 --$rwLast; // base 0 row index
1459
1460 // make sure we are not out of bounds
1461 $colFirst = min($colFirst, 255);
1462 $colLast = min($colLast, 255);
1463
1464 $rwFirst = min($rwFirst, 65535);
1465 $rwLast = min($rwLast, 65535);
1466
1467 $record = 0x001D; // Record identifier
1468 $length = 0x000F; // Number of bytes to follow
1469
1470 $pnn = $this->_active_pane; // Pane position
1471 $rwAct = $rwFirst; // Active row
1472 $colAct = $colFirst; // Active column
1473 $irefAct = 0; // Active cell ref
1474 $cref = 1; // Number of refs
1475
1476 if (!isset($rwLast)) {
1477 $rwLast = $rwFirst; // Last row in reference
1478 }
1479 if (!isset($colLast)) {
1480 $colLast = $colFirst; // Last col in reference
1481 }
1482
1483 // Swap last row/col for first row/col as necessary
1484 if ($rwFirst > $rwLast) {
1485 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
1486 }
1487
1488 if ($colFirst > $colLast) {
1489 list($colFirst, $colLast) = array($colLast, $colFirst);
1490 }
1491
1492 $header = pack("vv", $record, $length);
1493 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
1494 $irefAct, $cref,
1495 $rwFirst, $rwLast,
1496 $colFirst, $colLast);
1497 $this->_append($header . $data);
1498 }
1499
1503 private function _writeMergedCells()
1504 {
1505 $mergeCells = $this->_phpSheet->getMergeCells();
1506 $countMergeCells = count($mergeCells);
1507
1508 if ($countMergeCells == 0) {
1509 return;
1510 }
1511
1512 // maximum allowed number of merged cells per record
1513 $maxCountMergeCellsPerRecord = 1027;
1514
1515 // record identifier
1516 $record = 0x00E5;
1517
1518 // counter for total number of merged cells treated so far by the writer
1519 $i = 0;
1520
1521 // counter for number of merged cells written in record currently being written
1522 $j = 0;
1523
1524 // initialize record data
1525 $recordData = '';
1526
1527 // loop through the merged cells
1528 foreach ($mergeCells as $mergeCell) {
1529 ++$i;
1530 ++$j;
1531
1532 // extract the row and column indexes
1533 $range = PHPExcel_Cell::splitRange($mergeCell);
1534 list($first, $last) = $range[0];
1535 list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
1536 list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
1537
1538 $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
1539
1540 // flush record if we have reached limit for number of merged cells, or reached final merged cell
1541 if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
1542 $recordData = pack('v', $j) . $recordData;
1543 $length = strlen($recordData);
1544 $header = pack('vv', $record, $length);
1545 $this->_append($header . $recordData);
1546
1547 // initialize for next record, if any
1548 $recordData = '';
1549 $j = 0;
1550 }
1551 }
1552 }
1553
1557 private function _writeSheetLayout()
1558 {
1559 if (!$this->_phpSheet->isTabColorSet()) {
1560 return;
1561 }
1562
1563 $recordData = pack(
1564 'vvVVVvv'
1565 , 0x0862
1566 , 0x0000 // unused
1567 , 0x00000000 // unused
1568 , 0x00000000 // unused
1569 , 0x00000014 // size of record data
1570 , $this->_colors[$this->_phpSheet->getTabColor()->getRGB()] // color index
1571 , 0x0000 // unused
1572 );
1573
1574 $length = strlen($recordData);
1575
1576 $record = 0x0862; // Record identifier
1577 $header = pack('vv', $record, $length);
1578 $this->_append($header . $recordData);
1579 }
1580
1584 private function _writeSheetProtection()
1585 {
1586 // record identifier
1587 $record = 0x0867;
1588
1589 // prepare options
1590 $options = (int) !$this->_phpSheet->getProtection()->getObjects()
1591 | (int) !$this->_phpSheet->getProtection()->getScenarios() << 1
1592 | (int) !$this->_phpSheet->getProtection()->getFormatCells() << 2
1593 | (int) !$this->_phpSheet->getProtection()->getFormatColumns() << 3
1594 | (int) !$this->_phpSheet->getProtection()->getFormatRows() << 4
1595 | (int) !$this->_phpSheet->getProtection()->getInsertColumns() << 5
1596 | (int) !$this->_phpSheet->getProtection()->getInsertRows() << 6
1597 | (int) !$this->_phpSheet->getProtection()->getInsertHyperlinks() << 7
1598 | (int) !$this->_phpSheet->getProtection()->getDeleteColumns() << 8
1599 | (int) !$this->_phpSheet->getProtection()->getDeleteRows() << 9
1600 | (int) !$this->_phpSheet->getProtection()->getSelectLockedCells() << 10
1601 | (int) !$this->_phpSheet->getProtection()->getSort() << 11
1602 | (int) !$this->_phpSheet->getProtection()->getAutoFilter() << 12
1603 | (int) !$this->_phpSheet->getProtection()->getPivotTables() << 13
1604 | (int) !$this->_phpSheet->getProtection()->getSelectUnlockedCells() << 14 ;
1605
1606 // record data
1607 $recordData = pack(
1608 'vVVCVVvv'
1609 , 0x0867 // repeated record identifier
1610 , 0x0000 // not used
1611 , 0x0000 // not used
1612 , 0x00 // not used
1613 , 0x01000200 // unknown data
1614 , 0xFFFFFFFF // unknown data
1615 , $options // options
1616 , 0x0000 // not used
1617 );
1618
1619 $length = strlen($recordData);
1620 $header = pack('vv', $record, $length);
1621
1622 $this->_append($header . $recordData);
1623 }
1624
1631 private function _writeRangeProtection()
1632 {
1633 foreach ($this->_phpSheet->getProtectedCells() as $range => $password) {
1634 // number of ranges, e.g. 'A1:B3 C20:D25'
1635 $cellRanges = explode(' ', $range);
1636 $cref = count($cellRanges);
1637
1638 $recordData = pack(
1639 'vvVVvCVvVv',
1640 0x0868,
1641 0x00,
1642 0x0000,
1643 0x0000,
1644 0x02,
1645 0x0,
1646 0x0000,
1647 $cref,
1648 0x0000,
1649 0x00
1650 );
1651
1652 foreach ($cellRanges as $cellRange) {
1653 $recordData .= $this->_writeBIFF8CellRangeAddressFixed($cellRange);
1654 }
1655
1656 // the rgbFeat structure
1657 $recordData .= pack(
1658 'VV',
1659 0x0000,
1660 hexdec($password)
1661 );
1662
1663 $recordData .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong('p' . md5($recordData));
1664
1665 $length = strlen($recordData);
1666
1667 $record = 0x0868; // Record identifier
1668 $header = pack("vv", $record, $length);
1669 $this->_append($header . $recordData);
1670 }
1671 }
1672
1685 private function _writeExterncount($count)
1686 {
1687 $record = 0x0016; // Record identifier
1688 $length = 0x0002; // Number of bytes to follow
1689
1690 $header = pack("vv", $record, $length);
1691 $data = pack("v", $count);
1692 $this->_append($header . $data);
1693 }
1694
1703 private function _writeExternsheet($sheetname)
1704 {
1705 $record = 0x0017; // Record identifier
1706
1707 // References to the current sheet are encoded differently to references to
1708 // external sheets.
1709 //
1710 if ($this->_phpSheet->getTitle() == $sheetname) {
1711 $sheetname = '';
1712 $length = 0x02; // The following 2 bytes
1713 $cch = 1; // The following byte
1714 $rgch = 0x02; // Self reference
1715 } else {
1716 $length = 0x02 + strlen($sheetname);
1717 $cch = strlen($sheetname);
1718 $rgch = 0x03; // Reference to a sheet in the current workbook
1719 }
1720
1721 $header = pack("vv", $record, $length);
1722 $data = pack("CC", $cch, $rgch);
1723 $this->_append($header . $data . $sheetname);
1724 }
1725
1732 private function _writePanes()
1733 {
1734 $panes = array();
1735 if ($freezePane = $this->_phpSheet->getFreezePane()) {
1736 list($column, $row) = PHPExcel_Cell::coordinateFromString($freezePane);
1737 $panes[0] = $row - 1;
1739 } else {
1740 // thaw panes
1741 return;
1742 }
1743
1744 $y = isset($panes[0]) ? $panes[0] : null;
1745 $x = isset($panes[1]) ? $panes[1] : null;
1746 $rwTop = isset($panes[2]) ? $panes[2] : null;
1747 $colLeft = isset($panes[3]) ? $panes[3] : null;
1748 if (count($panes) > 4) { // if Active pane was received
1749 $pnnAct = $panes[4];
1750 } else {
1751 $pnnAct = null;
1752 }
1753 $record = 0x0041; // Record identifier
1754 $length = 0x000A; // Number of bytes to follow
1755
1756 // Code specific to frozen or thawed panes.
1757 if ($this->_phpSheet->getFreezePane()) {
1758 // Set default values for $rwTop and $colLeft
1759 if (!isset($rwTop)) {
1760 $rwTop = $y;
1761 }
1762 if (!isset($colLeft)) {
1763 $colLeft = $x;
1764 }
1765 } else {
1766 // Set default values for $rwTop and $colLeft
1767 if (!isset($rwTop)) {
1768 $rwTop = 0;
1769 }
1770 if (!isset($colLeft)) {
1771 $colLeft = 0;
1772 }
1773
1774 // Convert Excel's row and column units to the internal units.
1775 // The default row height is 12.75
1776 // The default column width is 8.43
1777 // The following slope and intersection values were interpolated.
1778 //
1779 $y = 20*$y + 255;
1780 $x = 113.879*$x + 390;
1781 }
1782
1783
1784 // Determine which pane should be active. There is also the undocumented
1785 // option to override this should it be necessary: may be removed later.
1786 //
1787 if (!isset($pnnAct)) {
1788 if ($x != 0 && $y != 0) {
1789 $pnnAct = 0; // Bottom right
1790 }
1791 if ($x != 0 && $y == 0) {
1792 $pnnAct = 1; // Top right
1793 }
1794 if ($x == 0 && $y != 0) {
1795 $pnnAct = 2; // Bottom left
1796 }
1797 if ($x == 0 && $y == 0) {
1798 $pnnAct = 3; // Top left
1799 }
1800 }
1801
1802 $this->_active_pane = $pnnAct; // Used in _writeSelection
1803
1804 $header = pack("vv", $record, $length);
1805 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
1806 $this->_append($header . $data);
1807 }
1808
1812 private function _writeSetup()
1813 {
1814 $record = 0x00A1; // Record identifier
1815 $length = 0x0022; // Number of bytes to follow
1816
1817 $iPaperSize = $this->_phpSheet->getPageSetup()->getPaperSize(); // Paper size
1818
1819 $iScale = $this->_phpSheet->getPageSetup()->getScale() ?
1820 $this->_phpSheet->getPageSetup()->getScale() : 100; // Print scaling factor
1821
1822 $iPageStart = 0x01; // Starting page number
1823 $iFitWidth = (int) $this->_phpSheet->getPageSetup()->getFitToWidth(); // Fit to number of pages wide
1824 $iFitHeight = (int) $this->_phpSheet->getPageSetup()->getFitToHeight(); // Fit to number of pages high
1825 $grbit = 0x00; // Option flags
1826 $iRes = 0x0258; // Print resolution
1827 $iVRes = 0x0258; // Vertical print resolution
1828
1829 $numHdr = $this->_phpSheet->getPageMargins()->getHeader(); // Header Margin
1830
1831 $numFtr = $this->_phpSheet->getPageMargins()->getFooter(); // Footer Margin
1832 $iCopies = 0x01; // Number of copies
1833
1834 $fLeftToRight = 0x0; // Print over then down
1835
1836 // Page orientation
1837 $fLandscape = ($this->_phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) ?
1838 0x0 : 0x1;
1839
1840 $fNoPls = 0x0; // Setup not read from printer
1841 $fNoColor = 0x0; // Print black and white
1842 $fDraft = 0x0; // Print draft quality
1843 $fNotes = 0x0; // Print notes
1844 $fNoOrient = 0x0; // Orientation not set
1845 $fUsePage = 0x0; // Use custom starting page
1846
1847 $grbit = $fLeftToRight;
1848 $grbit |= $fLandscape << 1;
1849 $grbit |= $fNoPls << 2;
1850 $grbit |= $fNoColor << 3;
1851 $grbit |= $fDraft << 4;
1852 $grbit |= $fNotes << 5;
1853 $grbit |= $fNoOrient << 6;
1854 $grbit |= $fUsePage << 7;
1855
1856 $numHdr = pack("d", $numHdr);
1857 $numFtr = pack("d", $numFtr);
1858 if (self::getByteOrder()) { // if it's Big Endian
1859 $numHdr = strrev($numHdr);
1860 $numFtr = strrev($numFtr);
1861 }
1862
1863 $header = pack("vv", $record, $length);
1864 $data1 = pack("vvvvvvvv", $iPaperSize,
1865 $iScale,
1866 $iPageStart,
1867 $iFitWidth,
1868 $iFitHeight,
1869 $grbit,
1870 $iRes,
1871 $iVRes);
1872 $data2 = $numHdr.$numFtr;
1873 $data3 = pack("v", $iCopies);
1874 $this->_append($header . $data1 . $data2 . $data3);
1875 }
1876
1880 private function _writeHeader()
1881 {
1882 $record = 0x0014; // Record identifier
1883
1884 /* removing for now
1885 // need to fix character count (multibyte!)
1886 if (strlen($this->_phpSheet->getHeaderFooter()->getOddHeader()) <= 255) {
1887 $str = $this->_phpSheet->getHeaderFooter()->getOddHeader(); // header string
1888 } else {
1889 $str = '';
1890 }
1891 */
1892
1893 $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddHeader());
1894 $length = strlen($recordData);
1895
1896 $header = pack("vv", $record, $length);
1897
1898 $this->_append($header . $recordData);
1899 }
1900
1904 private function _writeFooter()
1905 {
1906 $record = 0x0015; // Record identifier
1907
1908 /* removing for now
1909 // need to fix character count (multibyte!)
1910 if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) {
1911 $str = $this->_phpSheet->getHeaderFooter()->getOddFooter();
1912 } else {
1913 $str = '';
1914 }
1915 */
1916
1917 $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter());
1918 $length = strlen($recordData);
1919
1920 $header = pack("vv", $record, $length);
1921
1922 $this->_append($header . $recordData);
1923 }
1924
1930 private function _writeHcenter()
1931 {
1932 $record = 0x0083; // Record identifier
1933 $length = 0x0002; // Bytes to follow
1934
1935 $fHCenter = $this->_phpSheet->getPageSetup()->getHorizontalCentered() ? 1 : 0; // Horizontal centering
1936
1937 $header = pack("vv", $record, $length);
1938 $data = pack("v", $fHCenter);
1939
1940 $this->_append($header.$data);
1941 }
1942
1946 private function _writeVcenter()
1947 {
1948 $record = 0x0084; // Record identifier
1949 $length = 0x0002; // Bytes to follow
1950
1951 $fVCenter = $this->_phpSheet->getPageSetup()->getVerticalCentered() ? 1 : 0; // Horizontal centering
1952
1953 $header = pack("vv", $record, $length);
1954 $data = pack("v", $fVCenter);
1955 $this->_append($header . $data);
1956 }
1957
1961 private function _writeMarginLeft()
1962 {
1963 $record = 0x0026; // Record identifier
1964 $length = 0x0008; // Bytes to follow
1965
1966 $margin = $this->_phpSheet->getPageMargins()->getLeft(); // Margin in inches
1967
1968 $header = pack("vv", $record, $length);
1969 $data = pack("d", $margin);
1970 if (self::getByteOrder()) { // if it's Big Endian
1971 $data = strrev($data);
1972 }
1973
1974 $this->_append($header . $data);
1975 }
1976
1980 private function _writeMarginRight()
1981 {
1982 $record = 0x0027; // Record identifier
1983 $length = 0x0008; // Bytes to follow
1984
1985 $margin = $this->_phpSheet->getPageMargins()->getRight(); // Margin in inches
1986
1987 $header = pack("vv", $record, $length);
1988 $data = pack("d", $margin);
1989 if (self::getByteOrder()) { // if it's Big Endian
1990 $data = strrev($data);
1991 }
1992
1993 $this->_append($header . $data);
1994 }
1995
1999 private function _writeMarginTop()
2000 {
2001 $record = 0x0028; // Record identifier
2002 $length = 0x0008; // Bytes to follow
2003
2004 $margin = $this->_phpSheet->getPageMargins()->getTop(); // Margin in inches
2005
2006 $header = pack("vv", $record, $length);
2007 $data = pack("d", $margin);
2008 if (self::getByteOrder()) { // if it's Big Endian
2009 $data = strrev($data);
2010 }
2011
2012 $this->_append($header . $data);
2013 }
2014
2018 private function _writeMarginBottom()
2019 {
2020 $record = 0x0029; // Record identifier
2021 $length = 0x0008; // Bytes to follow
2022
2023 $margin = $this->_phpSheet->getPageMargins()->getBottom(); // Margin in inches
2024
2025 $header = pack("vv", $record, $length);
2026 $data = pack("d", $margin);
2027 if (self::getByteOrder()) { // if it's Big Endian
2028 $data = strrev($data);
2029 }
2030
2031 $this->_append($header . $data);
2032 }
2033
2037 private function _writePrintHeaders()
2038 {
2039 $record = 0x002a; // Record identifier
2040 $length = 0x0002; // Bytes to follow
2041
2042 $fPrintRwCol = $this->_print_headers; // Boolean flag
2043
2044 $header = pack("vv", $record, $length);
2045 $data = pack("v", $fPrintRwCol);
2046 $this->_append($header . $data);
2047 }
2048
2053 private function _writePrintGridlines()
2054 {
2055 $record = 0x002b; // Record identifier
2056 $length = 0x0002; // Bytes to follow
2057
2058 $fPrintGrid = $this->_phpSheet->getPrintGridlines() ? 1 : 0; // Boolean flag
2059
2060 $header = pack("vv", $record, $length);
2061 $data = pack("v", $fPrintGrid);
2062 $this->_append($header . $data);
2063 }
2064
2069 private function _writeGridset()
2070 {
2071 $record = 0x0082; // Record identifier
2072 $length = 0x0002; // Bytes to follow
2073
2074 $fGridSet = !$this->_phpSheet->getPrintGridlines(); // Boolean flag
2075
2076 $header = pack("vv", $record, $length);
2077 $data = pack("v", $fGridSet);
2078 $this->_append($header . $data);
2079 }
2080
2084 private function _writeAutoFilterInfo(){
2085 $record = 0x009D; // Record identifier
2086 $length = 0x0002; // Bytes to follow
2087
2088 $rangeBounds = PHPExcel_Cell::rangeBoundaries($this->_phpSheet->getAutoFilter()->getRange());
2089 $iNumFilters = 1 + $rangeBounds[1][0] - $rangeBounds[0][0];
2090
2091 $header = pack("vv", $record, $length);
2092 $data = pack("v", $iNumFilters);
2093 $this->_append($header . $data);
2094 }
2095
2103 private function _writeGuts()
2104 {
2105 $record = 0x0080; // Record identifier
2106 $length = 0x0008; // Bytes to follow
2107
2108 $dxRwGut = 0x0000; // Size of row gutter
2109 $dxColGut = 0x0000; // Size of col gutter
2110
2111 // determine maximum row outline level
2112 $maxRowOutlineLevel = 0;
2113 foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
2114 $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel());
2115 }
2116
2117 $col_level = 0;
2118
2119 // Calculate the maximum column outline level. The equivalent calculation
2120 // for the row outline level is carried out in _writeRow().
2121 $colcount = count($this->_colinfo);
2122 for ($i = 0; $i < $colcount; ++$i) {
2123 $col_level = max($this->_colinfo[$i][5], $col_level);
2124 }
2125
2126 // Set the limits for the outline levels (0 <= x <= 7).
2127 $col_level = max(0, min($col_level, 7));
2128
2129 // The displayed level is one greater than the max outline levels
2130 if ($maxRowOutlineLevel) {
2131 ++$maxRowOutlineLevel;
2132 }
2133 if ($col_level) {
2134 ++$col_level;
2135 }
2136
2137 $header = pack("vv", $record, $length);
2138 $data = pack("vvvv", $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level);
2139
2140 $this->_append($header.$data);
2141 }
2142
2147 private function _writeWsbool()
2148 {
2149 $record = 0x0081; // Record identifier
2150 $length = 0x0002; // Bytes to follow
2151 $grbit = 0x0000;
2152
2153 // The only option that is of interest is the flag for fit to page. So we
2154 // set all the options in one go.
2155 //
2156 // Set the option flags
2157 $grbit |= 0x0001; // Auto page breaks visible
2158 if ($this->_outline_style) {
2159 $grbit |= 0x0020; // Auto outline styles
2160 }
2161 if ($this->_phpSheet->getShowSummaryBelow()) {
2162 $grbit |= 0x0040; // Outline summary below
2163 }
2164 if ($this->_phpSheet->getShowSummaryRight()) {
2165 $grbit |= 0x0080; // Outline summary right
2166 }
2167 if ($this->_phpSheet->getPageSetup()->getFitToPage()) {
2168 $grbit |= 0x0100; // Page setup fit to page
2169 }
2170 if ($this->_outline_on) {
2171 $grbit |= 0x0400; // Outline symbols displayed
2172 }
2173
2174 $header = pack("vv", $record, $length);
2175 $data = pack("v", $grbit);
2176 $this->_append($header . $data);
2177 }
2178
2182 private function _writeBreaks()
2183 {
2184 // initialize
2185 $vbreaks = array();
2186 $hbreaks = array();
2187
2188 foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) {
2189 // Fetch coordinates
2190 $coordinates = PHPExcel_Cell::coordinateFromString($cell);
2191
2192 // Decide what to do by the type of break
2193 switch ($breakType) {
2195 // Add to list of vertical breaks
2196 $vbreaks[] = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1;
2197 break;
2198
2200 // Add to list of horizontal breaks
2201 $hbreaks[] = $coordinates[1];
2202 break;
2203
2205 default:
2206 // Nothing to do
2207 break;
2208 }
2209 }
2210
2211 //horizontal page breaks
2212 if (!empty($hbreaks)) {
2213
2214 // Sort and filter array of page breaks
2215 sort($hbreaks, SORT_NUMERIC);
2216 if ($hbreaks[0] == 0) { // don't use first break if it's 0
2217 array_shift($hbreaks);
2218 }
2219
2220 $record = 0x001b; // Record identifier
2221 $cbrk = count($hbreaks); // Number of page breaks
2222 $length = 2 + 6 * $cbrk; // Bytes to follow
2223
2224 $header = pack("vv", $record, $length);
2225 $data = pack("v", $cbrk);
2226
2227 // Append each page break
2228 foreach ($hbreaks as $hbreak) {
2229 $data .= pack("vvv", $hbreak, 0x0000, 0x00ff);
2230 }
2231
2232 $this->_append($header . $data);
2233 }
2234
2235 // vertical page breaks
2236 if (!empty($vbreaks)) {
2237
2238 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
2239 // It is slightly higher in Excel 97/200, approx. 1026
2240 $vbreaks = array_slice($vbreaks, 0, 1000);
2241
2242 // Sort and filter array of page breaks
2243 sort($vbreaks, SORT_NUMERIC);
2244 if ($vbreaks[0] == 0) { // don't use first break if it's 0
2245 array_shift($vbreaks);
2246 }
2247
2248 $record = 0x001a; // Record identifier
2249 $cbrk = count($vbreaks); // Number of page breaks
2250 $length = 2 + 6 * $cbrk; // Bytes to follow
2251
2252 $header = pack("vv", $record, $length);
2253 $data = pack("v", $cbrk);
2254
2255 // Append each page break
2256 foreach ($vbreaks as $vbreak) {
2257 $data .= pack("vvv", $vbreak, 0x0000, 0xffff);
2258 }
2259
2260 $this->_append($header . $data);
2261 }
2262 }
2263
2267 private function _writeProtect()
2268 {
2269 // Exit unless sheet protection has been specified
2270 if (!$this->_phpSheet->getProtection()->getSheet()) {
2271 return;
2272 }
2273
2274 $record = 0x0012; // Record identifier
2275 $length = 0x0002; // Bytes to follow
2276
2277 $fLock = 1; // Worksheet is protected
2278
2279 $header = pack("vv", $record, $length);
2280 $data = pack("v", $fLock);
2281
2282 $this->_append($header.$data);
2283 }
2284
2288 private function _writeScenProtect()
2289 {
2290 // Exit if sheet protection is not active
2291 if (!$this->_phpSheet->getProtection()->getSheet()) {
2292 return;
2293 }
2294
2295 // Exit if scenarios are not protected
2296 if (!$this->_phpSheet->getProtection()->getScenarios()) {
2297 return;
2298 }
2299
2300 $record = 0x00DD; // Record identifier
2301 $length = 0x0002; // Bytes to follow
2302
2303 $header = pack('vv', $record, $length);
2304 $data = pack('v', 1);
2305
2306 $this->_append($header . $data);
2307 }
2308
2312 private function _writeObjectProtect()
2313 {
2314 // Exit if sheet protection is not active
2315 if (!$this->_phpSheet->getProtection()->getSheet()) {
2316 return;
2317 }
2318
2319 // Exit if objects are not protected
2320 if (!$this->_phpSheet->getProtection()->getObjects()) {
2321 return;
2322 }
2323
2324 $record = 0x0063; // Record identifier
2325 $length = 0x0002; // Bytes to follow
2326
2327 $header = pack('vv', $record, $length);
2328 $data = pack('v', 1);
2329
2330 $this->_append($header . $data);
2331 }
2332
2336 private function _writePassword()
2337 {
2338 // Exit unless sheet protection and password have been specified
2339 if (!$this->_phpSheet->getProtection()->getSheet() || !$this->_phpSheet->getProtection()->getPassword()) {
2340 return;
2341 }
2342
2343 $record = 0x0013; // Record identifier
2344 $length = 0x0002; // Bytes to follow
2345
2346 $wPassword = hexdec($this->_phpSheet->getProtection()->getPassword()); // Encoded password
2347
2348 $header = pack("vv", $record, $length);
2349 $data = pack("v", $wPassword);
2350
2351 $this->_append($header . $data);
2352 }
2353
2366 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
2367 {
2368 $bitmap_array = (is_resource($bitmap) ? $this->_processBitmapGd($bitmap) : $this->_processBitmap($bitmap));
2369 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
2370
2371 // Scale the frame of the image.
2372 $width *= $scale_x;
2373 $height *= $scale_y;
2374
2375 // Calculate the vertices of the image and write the OBJ record
2376 $this->_positionImage($col, $row, $x, $y, $width, $height);
2377
2378 // Write the IMDATA record to store the bitmap data
2379 $record = 0x007f;
2380 $length = 8 + $size;
2381 $cf = 0x09;
2382 $env = 0x01;
2383 $lcb = $size;
2384
2385 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
2386 $this->_append($header.$data);
2387 }
2388
2440 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
2441 {
2442 // Initialise end cell to the same as the start cell
2443 $col_end = $col_start; // Col containing lower right corner of object
2444 $row_end = $row_start; // Row containing bottom right corner of object
2445
2446 // Zero the specified offset if greater than the cell dimensions
2447 if ($x1 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
2448 $x1 = 0;
2449 }
2450 if ($y1 >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1)) {
2451 $y1 = 0;
2452 }
2453
2454 $width = $width + $x1 -1;
2455 $height = $height + $y1 -1;
2456
2457 // Subtract the underlying cell widths to find the end cell of the image
2458 while ($width >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
2459 $width -= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
2460 ++$col_end;
2461 }
2462
2463 // Subtract the underlying cell heights to find the end cell of the image
2464 while ($height >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) {
2465 $height -= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1);
2466 ++$row_end;
2467 }
2468
2469 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
2470 // with zero eight or width.
2471 //
2472 if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
2473 return;
2474 }
2475 if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
2476 return;
2477 }
2478 if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) {
2479 return;
2480 }
2481 if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) {
2482 return;
2483 }
2484
2485 // Convert the pixel values to the percentage value expected by Excel
2486 $x1 = $x1 / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
2487 $y1 = $y1 / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256;
2488 $x2 = $width / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
2489 $y2 = $height / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256; // Distance to bottom of object
2490
2491 $this->_writeObjPicture($col_start, $x1,
2492 $row_start, $y1,
2493 $col_end, $x2,
2494 $row_end, $y2);
2495 }
2496
2510 private function _writeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
2511 {
2512 $record = 0x005d; // Record identifier
2513 $length = 0x003c; // Bytes to follow
2514
2515 $cObj = 0x0001; // Count of objects in file (set to 1)
2516 $OT = 0x0008; // Object type. 8 = Picture
2517 $id = 0x0001; // Object ID
2518 $grbit = 0x0614; // Option flags
2519
2520 $cbMacro = 0x0000; // Length of FMLA structure
2521 $Reserved1 = 0x0000; // Reserved
2522 $Reserved2 = 0x0000; // Reserved
2523
2524 $icvBack = 0x09; // Background colour
2525 $icvFore = 0x09; // Foreground colour
2526 $fls = 0x00; // Fill pattern
2527 $fAuto = 0x00; // Automatic fill
2528 $icv = 0x08; // Line colour
2529 $lns = 0xff; // Line style
2530 $lnw = 0x01; // Line weight
2531 $fAutoB = 0x00; // Automatic border
2532 $frs = 0x0000; // Frame style
2533 $cf = 0x0009; // Image format, 9 = bitmap
2534 $Reserved3 = 0x0000; // Reserved
2535 $cbPictFmla = 0x0000; // Length of FMLA structure
2536 $Reserved4 = 0x0000; // Reserved
2537 $grbit2 = 0x0001; // Option flags
2538 $Reserved5 = 0x0000; // Reserved
2539
2540
2541 $header = pack("vv", $record, $length);
2542 $data = pack("V", $cObj);
2543 $data .= pack("v", $OT);
2544 $data .= pack("v", $id);
2545 $data .= pack("v", $grbit);
2546 $data .= pack("v", $colL);
2547 $data .= pack("v", $dxL);
2548 $data .= pack("v", $rwT);
2549 $data .= pack("v", $dyT);
2550 $data .= pack("v", $colR);
2551 $data .= pack("v", $dxR);
2552 $data .= pack("v", $rwB);
2553 $data .= pack("v", $dyB);
2554 $data .= pack("v", $cbMacro);
2555 $data .= pack("V", $Reserved1);
2556 $data .= pack("v", $Reserved2);
2557 $data .= pack("C", $icvBack);
2558 $data .= pack("C", $icvFore);
2559 $data .= pack("C", $fls);
2560 $data .= pack("C", $fAuto);
2561 $data .= pack("C", $icv);
2562 $data .= pack("C", $lns);
2563 $data .= pack("C", $lnw);
2564 $data .= pack("C", $fAutoB);
2565 $data .= pack("v", $frs);
2566 $data .= pack("V", $cf);
2567 $data .= pack("v", $Reserved3);
2568 $data .= pack("v", $cbPictFmla);
2569 $data .= pack("v", $Reserved4);
2570 $data .= pack("v", $grbit2);
2571 $data .= pack("V", $Reserved5);
2572
2573 $this->_append($header . $data);
2574 }
2575
2583 function _processBitmapGd($image) {
2584 $width = imagesx($image);
2585 $height = imagesy($image);
2586
2587 $data = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
2588 for ($j=$height; $j--; ) {
2589 for ($i=0; $i < $width; ++$i) {
2590 $color = imagecolorsforindex($image, imagecolorat($image, $i, $j));
2591 foreach (array("red", "green", "blue") as $key) {
2592 $color[$key] = $color[$key] + round((255 - $color[$key]) * $color["alpha"] / 127);
2593 }
2594 $data .= chr($color["blue"]) . chr($color["green"]) . chr($color["red"]);
2595 }
2596 if (3*$width % 4) {
2597 $data .= str_repeat("\x00", 4 - 3*$width % 4);
2598 }
2599 }
2600
2601 return array($width, $height, strlen($data), $data);
2602 }
2603
2613 function _processBitmap($bitmap)
2614 {
2615 // Open file.
2616 $bmp_fd = @fopen($bitmap,"rb");
2617 if (!$bmp_fd) {
2618 throw new PHPExcel_Writer_Exception("Couldn't import $bitmap");
2619 }
2620
2621 // Slurp the file into a string.
2622 $data = fread($bmp_fd, filesize($bitmap));
2623
2624 // Check that the file is big enough to be a bitmap.
2625 if (strlen($data) <= 0x36) {
2626 throw new PHPExcel_Writer_Exception("$bitmap doesn't contain enough data.\n");
2627 }
2628
2629 // The first 2 bytes are used to identify the bitmap.
2630 $identity = unpack("A2ident", $data);
2631 if ($identity['ident'] != "BM") {
2632 throw new PHPExcel_Writer_Exception("$bitmap doesn't appear to be a valid bitmap image.\n");
2633 }
2634
2635 // Remove bitmap data: ID.
2636 $data = substr($data, 2);
2637
2638 // Read and remove the bitmap size. This is more reliable than reading
2639 // the data size at offset 0x22.
2640 //
2641 $size_array = unpack("Vsa", substr($data, 0, 4));
2642 $size = $size_array['sa'];
2643 $data = substr($data, 4);
2644 $size -= 0x36; // Subtract size of bitmap header.
2645 $size += 0x0C; // Add size of BIFF header.
2646
2647 // Remove bitmap data: reserved, offset, header length.
2648 $data = substr($data, 12);
2649
2650 // Read and remove the bitmap width and height. Verify the sizes.
2651 $width_and_height = unpack("V2", substr($data, 0, 8));
2652 $width = $width_and_height[1];
2653 $height = $width_and_height[2];
2654 $data = substr($data, 8);
2655 if ($width > 0xFFFF) {
2656 throw new PHPExcel_Writer_Exception("$bitmap: largest image width supported is 65k.\n");
2657 }
2658 if ($height > 0xFFFF) {
2659 throw new PHPExcel_Writer_Exception("$bitmap: largest image height supported is 65k.\n");
2660 }
2661
2662 // Read and remove the bitmap planes and bpp data. Verify them.
2663 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
2664 $data = substr($data, 4);
2665 if ($planes_and_bitcount[2] != 24) { // Bitcount
2666 throw new PHPExcel_Writer_Exception("$bitmap isn't a 24bit true color bitmap.\n");
2667 }
2668 if ($planes_and_bitcount[1] != 1) {
2669 throw new PHPExcel_Writer_Exception("$bitmap: only 1 plane supported in bitmap image.\n");
2670 }
2671
2672 // Read and remove the bitmap compression. Verify compression.
2673 $compression = unpack("Vcomp", substr($data, 0, 4));
2674 $data = substr($data, 4);
2675
2676 //$compression = 0;
2677 if ($compression['comp'] != 0) {
2678 throw new PHPExcel_Writer_Exception("$bitmap: compression not supported in bitmap image.\n");
2679 }
2680
2681 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
2682 $data = substr($data, 20);
2683
2684 // Add the BITMAPCOREHEADER data
2685 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
2686 $data = $header . $data;
2687
2688 return (array($width, $height, $size, $data));
2689 }
2690
2695 private function _writeZoom()
2696 {
2697 // If scale is 100 we don't need to write a record
2698 if ($this->_phpSheet->getSheetView()->getZoomScale() == 100) {
2699 return;
2700 }
2701
2702 $record = 0x00A0; // Record identifier
2703 $length = 0x0004; // Bytes to follow
2704
2705 $header = pack("vv", $record, $length);
2706 $data = pack("vv", $this->_phpSheet->getSheetView()->getZoomScale(), 100);
2707 $this->_append($header . $data);
2708 }
2709
2715 public function getEscher()
2716 {
2717 return $this->_escher;
2718 }
2719
2725 public function setEscher(PHPExcel_Shared_Escher $pValue = null)
2726 {
2727 $this->_escher = $pValue;
2728 }
2729
2733 private function _writeMsoDrawing()
2734 {
2735 // write the Escher stream if necessary
2736 if (isset($this->_escher)) {
2737 $writer = new PHPExcel_Writer_Excel5_Escher($this->_escher);
2738 $data = $writer->close();
2739 $spOffsets = $writer->getSpOffsets();
2740 $spTypes = $writer->getSpTypes();
2741 // write the neccesary MSODRAWING, OBJ records
2742
2743 // split the Escher stream
2744 $spOffsets[0] = 0;
2745 $nm = count($spOffsets) - 1; // number of shapes excluding first shape
2746 for ($i = 1; $i <= $nm; ++$i) {
2747 // MSODRAWING record
2748 $record = 0x00EC; // Record identifier
2749
2750 // chunk of Escher stream for one shape
2751 $dataChunk = substr($data, $spOffsets[$i -1], $spOffsets[$i] - $spOffsets[$i - 1]);
2752
2753 $length = strlen($dataChunk);
2754 $header = pack("vv", $record, $length);
2755
2756 $this->_append($header . $dataChunk);
2757
2758 // OBJ record
2759 $record = 0x005D; // record identifier
2760 $objData = '';
2761
2762 // ftCmo
2763 if($spTypes[$i] == 0x00C9){
2764 // Add ftCmo (common object data) subobject
2765 $objData .=
2766 pack('vvvvvVVV'
2767 , 0x0015 // 0x0015 = ftCmo
2768 , 0x0012 // length of ftCmo data
2769 , 0x0014 // object type, 0x0014 = filter
2770 , $i // object id number, Excel seems to use 1-based index, local for the sheet
2771 , 0x2101 // option flags, 0x2001 is what OpenOffice.org uses
2772 , 0 // reserved
2773 , 0 // reserved
2774 , 0 // reserved
2775 );
2776
2777 // Add ftSbs Scroll bar subobject
2778 $objData .= pack('vv', 0x00C, 0x0014);
2779 $objData .= pack('H*', '0000000000000000640001000A00000010000100');
2780 // Add ftLbsData (List box data) subobject
2781 $objData .= pack('vv', 0x0013, 0x1FEE);
2782 $objData .= pack('H*', '00000000010001030000020008005700');
2783 }
2784 else {
2785 // Add ftCmo (common object data) subobject
2786 $objData .=
2787 pack('vvvvvVVV'
2788 , 0x0015 // 0x0015 = ftCmo
2789 , 0x0012 // length of ftCmo data
2790 , 0x0008 // object type, 0x0008 = picture
2791 , $i // object id number, Excel seems to use 1-based index, local for the sheet
2792 , 0x6011 // option flags, 0x6011 is what OpenOffice.org uses
2793 , 0 // reserved
2794 , 0 // reserved
2795 , 0 // reserved
2796 );
2797 }
2798
2799 // ftEnd
2800 $objData .=
2801 pack('vv'
2802 , 0x0000 // 0x0000 = ftEnd
2803 , 0x0000 // length of ftEnd data
2804 );
2805
2806 $length = strlen($objData);
2807 $header = pack('vv', $record, $length);
2808 $this->_append($header . $objData);
2809 }
2810 }
2811 }
2812
2816 private function _writeDataValidity()
2817 {
2818 // Datavalidation collection
2819 $dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
2820
2821 // Write data validations?
2822 if (!empty($dataValidationCollection)) {
2823
2824 // DATAVALIDATIONS record
2825 $record = 0x01B2; // Record identifier
2826 $length = 0x0012; // Bytes to follow
2827
2828 $grbit = 0x0000; // Prompt box at cell, no cached validity data at DV records
2829 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
2830 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
2831 $objId = 0xFFFFFFFF; // Object identifier of drop down arrow object, or -1 if not visible
2832
2833 $header = pack('vv', $record, $length);
2834 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
2835 count($dataValidationCollection));
2836 $this->_append($header.$data);
2837
2838 // DATAVALIDATION records
2839 $record = 0x01BE; // Record identifier
2840
2841 foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
2842 // initialize record data
2843 $data = '';
2844
2845 // options
2846 $options = 0x00000000;
2847
2848 // data type
2849 $type = $dataValidation->getType();
2850 switch ($type) {
2859 }
2860 $options |= $type << 0;
2861
2862 // error style
2863 $errorStyle = $dataValidation->getType();
2864 switch ($errorStyle) {
2865 case PHPExcel_Cell_DataValidation::STYLE_STOP: $errorStyle = 0x00; break;
2866 case PHPExcel_Cell_DataValidation::STYLE_WARNING: $errorStyle = 0x01; break;
2867 case PHPExcel_Cell_DataValidation::STYLE_INFORMATION: $errorStyle = 0x02; break;
2868 }
2869 $options |= $errorStyle << 4;
2870
2871 // explicit formula?
2872 if ($type == 0x03 && preg_match('/^\".*\"$/', $dataValidation->getFormula1())) {
2873 $options |= 0x01 << 7;
2874 }
2875
2876 // empty cells allowed
2877 $options |= $dataValidation->getAllowBlank() << 8;
2878
2879 // show drop down
2880 $options |= (!$dataValidation->getShowDropDown()) << 9;
2881
2882 // show input message
2883 $options |= $dataValidation->getShowInputMessage() << 18;
2884
2885 // show error message
2886 $options |= $dataValidation->getShowErrorMessage() << 19;
2887
2888 // condition operator
2889 $operator = $dataValidation->getOperator();
2890 switch ($operator) {
2891 case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN: $operator = 0x00 ; break;
2892 case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN: $operator = 0x01 ; break;
2893 case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL: $operator = 0x02 ; break;
2894 case PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL: $operator = 0x03 ; break;
2895 case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN: $operator = 0x04 ; break;
2896 case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN: $operator = 0x05 ; break;
2898 case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL: $operator = 0x07 ; break;
2899 }
2900 $options |= $operator << 20;
2901
2902 $data = pack('V', $options);
2903
2904 // prompt title
2905 $promptTitle = $dataValidation->getPromptTitle() !== '' ?
2906 $dataValidation->getPromptTitle() : chr(0);
2908
2909 // error title
2910 $errorTitle = $dataValidation->getErrorTitle() !== '' ?
2911 $dataValidation->getErrorTitle() : chr(0);
2913
2914 // prompt text
2915 $prompt = $dataValidation->getPrompt() !== '' ?
2916 $dataValidation->getPrompt() : chr(0);
2918
2919 // error text
2920 $error = $dataValidation->getError() !== '' ?
2921 $dataValidation->getError() : chr(0);
2923
2924 // formula 1
2925 try {
2926 $formula1 = $dataValidation->getFormula1();
2927 if ($type == 0x03) { // list type
2928 $formula1 = str_replace(',', chr(0), $formula1);
2929 }
2930 $this->_parser->parse($formula1);
2931 $formula1 = $this->_parser->toReversePolish();
2932 $sz1 = strlen($formula1);
2933
2934 } catch(PHPExcel_Exception $e) {
2935 $sz1 = 0;
2936 $formula1 = '';
2937 }
2938 $data .= pack('vv', $sz1, 0x0000);
2939 $data .= $formula1;
2940
2941 // formula 2
2942 try {
2943 $formula2 = $dataValidation->getFormula2();
2944 if ($formula2 === '') {
2945 throw new PHPExcel_Writer_Exception('No formula2');
2946 }
2947 $this->_parser->parse($formula2);
2948 $formula2 = $this->_parser->toReversePolish();
2949 $sz2 = strlen($formula2);
2950
2951 } catch(PHPExcel_Exception $e) {
2952 $sz2 = 0;
2953 $formula2 = '';
2954 }
2955 $data .= pack('vv', $sz2, 0x0000);
2956 $data .= $formula2;
2957
2958 // cell range address list
2959 $data .= pack('v', 0x0001);
2960 $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate);
2961
2962 $length = strlen($data);
2963 $header = pack("vv", $record, $length);
2964
2965 $this->_append($header . $data);
2966 }
2967 }
2968 }
2969
2976 private static function _mapErrorCode($errorCode) {
2977 switch ($errorCode) {
2978 case '#NULL!': return 0x00;
2979 case '#DIV/0!': return 0x07;
2980 case '#VALUE!': return 0x0F;
2981 case '#REF!': return 0x17;
2982 case '#NAME?': return 0x1D;
2983 case '#NUM!': return 0x24;
2984 case '#N/A': return 0x2A;
2985 }
2986
2987 return 0;
2988 }
2989
2993 private function _writePageLayoutView(){
2994 $record = 0x088B; // Record identifier
2995 $length = 0x0010; // Bytes to follow
2996
2997 $rt = 0x088B; // 2
2998 $grbitFrt = 0x0000; // 2
2999 $reserved = 0x0000000000000000; // 8
3000 $wScalvePLV = $this->_phpSheet->getSheetView()->getZoomScale(); // 2
3001
3002 // The options flags that comprise $grbit
3003 if($this->_phpSheet->getSheetView()->getView() == PHPExcel_Worksheet_SheetView::SHEETVIEW_PAGE_LAYOUT){
3004 $fPageLayoutView = 1;
3005 } else {
3006 $fPageLayoutView = 0;
3007 }
3008 $fRulerVisible = 0;
3009 $fWhitespaceHidden = 0;
3010
3011 $grbit = $fPageLayoutView; // 2
3012 $grbit |= $fRulerVisible << 1;
3013 $grbit |= $fWhitespaceHidden << 3;
3014
3015 $header = pack("vv", $record, $length);
3016 $data = pack("vvVVvv", $rt, $grbitFrt, 0x00000000, 0x00000000, $wScalvePLV, $grbit);
3017 $this->_append($header . $data);
3018 }
3019
3024 private function _writeCFRule(PHPExcel_Style_Conditional $conditional){
3025 $record = 0x01B1; // Record identifier
3026
3027 // $type : Type of the CF
3028 // $operatorType : Comparison operator
3030 $type = 0x02;
3031 $operatorType = 0x00;
3033 $type = 0x01;
3034
3035 switch ($conditional->getOperatorType()){
3037 $operatorType = 0x00;
3038 break;
3040 $operatorType = 0x03;
3041 break;
3043 $operatorType = 0x05;
3044 break;
3046 $operatorType = 0x07;
3047 break;
3049 $operatorType = 0x06;
3050 break;
3052 $operatorType = 0x08;
3053 break;
3055 $operatorType = 0x04;
3056 break;
3058 $operatorType = 0x01;
3059 break;
3060 // not OPERATOR_NOTBETWEEN 0x02
3061 }
3062 }
3063
3064 // $szValue1 : size of the formula data for first value or formula
3065 // $szValue2 : size of the formula data for second value or formula
3066 $arrConditions = $conditional->getConditions();
3067 $numConditions = sizeof($arrConditions);
3068 if($numConditions == 1){
3069 $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
3070 $szValue2 = 0x0000;
3071 $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
3072 $operand2 = null;
3073 } else if($numConditions == 2 && ($conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_BETWEEN)){
3074 $szValue1 = ($arrConditions[0] <= 65535 ? 3 : 0x0000);
3075 $szValue2 = ($arrConditions[1] <= 65535 ? 3 : 0x0000);
3076 $operand1 = pack('Cv', 0x1E, $arrConditions[0]);
3077 $operand2 = pack('Cv', 0x1E, $arrConditions[1]);
3078 } else {
3079 $szValue1 = 0x0000;
3080 $szValue2 = 0x0000;
3081 $operand1 = null;
3082 $operand2 = null;
3083 }
3084
3085 // $flags : Option flags
3086 // Alignment
3087 $bAlignHz = ($conditional->getStyle()->getAlignment()->getHorizontal() == null ? 1 : 0);
3088 $bAlignVt = ($conditional->getStyle()->getAlignment()->getVertical() == null ? 1 : 0);
3089 $bAlignWrapTx = ($conditional->getStyle()->getAlignment()->getWrapText() == false ? 1 : 0);
3090 $bTxRotation = ($conditional->getStyle()->getAlignment()->getTextRotation() == null ? 1 : 0);
3091 $bIndent = ($conditional->getStyle()->getAlignment()->getIndent() == 0 ? 1 : 0);
3092 $bShrinkToFit = ($conditional->getStyle()->getAlignment()->getShrinkToFit() == false ? 1 : 0);
3093 if($bAlignHz == 0 || $bAlignVt == 0 || $bAlignWrapTx == 0 || $bTxRotation == 0 || $bIndent == 0 || $bShrinkToFit == 0){
3094 $bFormatAlign = 1;
3095 } else {
3096 $bFormatAlign = 0;
3097 }
3098 // Protection
3099 $bProtLocked = ($conditional->getStyle()->getProtection()->getLocked() == null ? 1 : 0);
3100 $bProtHidden = ($conditional->getStyle()->getProtection()->getHidden() == null ? 1 : 0);
3101 if($bProtLocked == 0 || $bProtHidden == 0){
3102 $bFormatProt = 1;
3103 } else {
3104 $bFormatProt = 0;
3105 }
3106 // Border
3107 $bBorderLeft = ($conditional->getStyle()->getBorders()->getLeft()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
3108 && $conditional->getStyle()->getBorders()->getLeft()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
3109 $bBorderRight = ($conditional->getStyle()->getBorders()->getRight()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
3110 && $conditional->getStyle()->getBorders()->getRight()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
3111 $bBorderTop = ($conditional->getStyle()->getBorders()->getTop()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
3112 && $conditional->getStyle()->getBorders()->getTop()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
3113 $bBorderBottom = ($conditional->getStyle()->getBorders()->getBottom()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK
3114 && $conditional->getStyle()->getBorders()->getBottom()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0);
3115 if($bBorderLeft == 0 || $bBorderRight == 0 || $bBorderTop == 0 || $bBorderBottom == 0){
3116 $bFormatBorder = 1;
3117 } else {
3118 $bFormatBorder = 0;
3119 }
3120 // Pattern
3121 $bFillStyle = ($conditional->getStyle()->getFill()->getFillType() == null ? 0 : 1);
3122 $bFillColor = ($conditional->getStyle()->getFill()->getStartColor()->getARGB() == null ? 0 : 1);
3123 $bFillColorBg = ($conditional->getStyle()->getFill()->getEndColor()->getARGB() == null ? 0 : 1);
3124 if($bFillStyle == 0 || $bFillColor == 0 || $bFillColorBg == 0){
3125 $bFormatFill = 1;
3126 } else {
3127 $bFormatFill = 0;
3128 }
3129 // Font
3130 if($conditional->getStyle()->getFont()->getName() != null
3131 || $conditional->getStyle()->getFont()->getSize() != null
3132 || $conditional->getStyle()->getFont()->getBold() != null
3133 || $conditional->getStyle()->getFont()->getItalic() != null
3134 || $conditional->getStyle()->getFont()->getSuperScript() != null
3135 || $conditional->getStyle()->getFont()->getSubScript() != null
3136 || $conditional->getStyle()->getFont()->getUnderline() != null
3137 || $conditional->getStyle()->getFont()->getStrikethrough() != null
3138 || $conditional->getStyle()->getFont()->getColor()->getARGB() != null){
3139 $bFormatFont = 1;
3140 } else {
3141 $bFormatFont = 0;
3142 }
3143 // Alignment
3144 $flags = 0;
3145 $flags |= (1 == $bAlignHz ? 0x00000001 : 0);
3146 $flags |= (1 == $bAlignVt ? 0x00000002 : 0);
3147 $flags |= (1 == $bAlignWrapTx ? 0x00000004 : 0);
3148 $flags |= (1 == $bTxRotation ? 0x00000008 : 0);
3149 // Justify last line flag
3150 $flags |= (1 == 1 ? 0x00000010 : 0);
3151 $flags |= (1 == $bIndent ? 0x00000020 : 0);
3152 $flags |= (1 == $bShrinkToFit ? 0x00000040 : 0);
3153 // Default
3154 $flags |= (1 == 1 ? 0x00000080 : 0);
3155 // Protection
3156 $flags |= (1 == $bProtLocked ? 0x00000100 : 0);
3157 $flags |= (1 == $bProtHidden ? 0x00000200 : 0);
3158 // Border
3159 $flags |= (1 == $bBorderLeft ? 0x00000400 : 0);
3160 $flags |= (1 == $bBorderRight ? 0x00000800 : 0);
3161 $flags |= (1 == $bBorderTop ? 0x00001000 : 0);
3162 $flags |= (1 == $bBorderBottom ? 0x00002000 : 0);
3163 $flags |= (1 == 1 ? 0x00004000 : 0); // Top left to Bottom right border
3164 $flags |= (1 == 1 ? 0x00008000 : 0); // Bottom left to Top right border
3165 // Pattern
3166 $flags |= (1 == $bFillStyle ? 0x00010000 : 0);
3167 $flags |= (1 == $bFillColor ? 0x00020000 : 0);
3168 $flags |= (1 == $bFillColorBg ? 0x00040000 : 0);
3169 $flags |= (1 == 1 ? 0x00380000 : 0);
3170 // Font
3171 $flags |= (1 == $bFormatFont ? 0x04000000 : 0);
3172 // Alignment :
3173 $flags |= (1 == $bFormatAlign ? 0x08000000 : 0);
3174 // Border
3175 $flags |= (1 == $bFormatBorder ? 0x10000000 : 0);
3176 // Pattern
3177 $flags |= (1 == $bFormatFill ? 0x20000000 : 0);
3178 // Protection
3179 $flags |= (1 == $bFormatProt ? 0x40000000 : 0);
3180 // Text direction
3181 $flags |= (1 == 0 ? 0x80000000 : 0);
3182
3183 // Data Blocks
3184 if($bFormatFont == 1){
3185 // Font Name
3186 if($conditional->getStyle()->getFont()->getName() == null){
3187 $dataBlockFont = pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
3188 $dataBlockFont .= pack('VVVVVVVV', 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000, 0x00000000);
3189 } else {
3190 $dataBlockFont = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($conditional->getStyle()->getFont()->getName());
3191 }
3192 // Font Size
3193 if($conditional->getStyle()->getFont()->getSize() == null){
3194 $dataBlockFont .= pack('V', 20 * 11);
3195 } else {
3196 $dataBlockFont .= pack('V', 20 * $conditional->getStyle()->getFont()->getSize());
3197 }
3198 // Font Options
3199 $dataBlockFont .= pack('V', 0);
3200 // Font weight
3201 if($conditional->getStyle()->getFont()->getBold() == true){
3202 $dataBlockFont .= pack('v', 0x02BC);
3203 } else {
3204 $dataBlockFont .= pack('v', 0x0190);
3205 }
3206 // Escapement type
3207 if($conditional->getStyle()->getFont()->getSubScript() == true){
3208 $dataBlockFont .= pack('v', 0x02);
3209 $fontEscapement = 0;
3210 } else if($conditional->getStyle()->getFont()->getSuperScript() == true){
3211 $dataBlockFont .= pack('v', 0x01);
3212 $fontEscapement = 0;
3213 } else {
3214 $dataBlockFont .= pack('v', 0x00);
3215 $fontEscapement = 1;
3216 }
3217 // Underline type
3218 switch ($conditional->getStyle()->getFont()->getUnderline()){
3219 case PHPExcel_Style_Font::UNDERLINE_NONE : $dataBlockFont .= pack('C', 0x00); $fontUnderline = 0; break;
3220 case PHPExcel_Style_Font::UNDERLINE_DOUBLE : $dataBlockFont .= pack('C', 0x02); $fontUnderline = 0; break;
3221 case PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING : $dataBlockFont .= pack('C', 0x22); $fontUnderline = 0; break;
3222 case PHPExcel_Style_Font::UNDERLINE_SINGLE : $dataBlockFont .= pack('C', 0x01); $fontUnderline = 0; break;
3223 case PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING : $dataBlockFont .= pack('C', 0x21); $fontUnderline = 0; break;
3224 default : $dataBlockFont .= pack('C', 0x00); $fontUnderline = 1; break;
3225 }
3226 // Not used (3)
3227 $dataBlockFont .= pack('vC', 0x0000, 0x00);
3228 // Font color index
3229 switch ($conditional->getStyle()->getFont()->getColor()->getRGB()) {
3230 case '000000': $colorIdx = 0x08; break;
3231 case 'FFFFFF': $colorIdx = 0x09; break;
3232 case 'FF0000': $colorIdx = 0x0A; break;
3233 case '00FF00': $colorIdx = 0x0B; break;
3234 case '0000FF': $colorIdx = 0x0C; break;
3235 case 'FFFF00': $colorIdx = 0x0D; break;
3236 case 'FF00FF': $colorIdx = 0x0E; break;
3237 case '00FFFF': $colorIdx = 0x0F; break;
3238 case '800000': $colorIdx = 0x10; break;
3239 case '008000': $colorIdx = 0x11; break;
3240 case '000080': $colorIdx = 0x12; break;
3241 case '808000': $colorIdx = 0x13; break;
3242 case '800080': $colorIdx = 0x14; break;
3243 case '008080': $colorIdx = 0x15; break;
3244 case 'C0C0C0': $colorIdx = 0x16; break;
3245 case '808080': $colorIdx = 0x17; break;
3246 case '9999FF': $colorIdx = 0x18; break;
3247 case '993366': $colorIdx = 0x19; break;
3248 case 'FFFFCC': $colorIdx = 0x1A; break;
3249 case 'CCFFFF': $colorIdx = 0x1B; break;
3250 case '660066': $colorIdx = 0x1C; break;
3251 case 'FF8080': $colorIdx = 0x1D; break;
3252 case '0066CC': $colorIdx = 0x1E; break;
3253 case 'CCCCFF': $colorIdx = 0x1F; break;
3254 case '000080': $colorIdx = 0x20; break;
3255 case 'FF00FF': $colorIdx = 0x21; break;
3256 case 'FFFF00': $colorIdx = 0x22; break;
3257 case '00FFFF': $colorIdx = 0x23; break;
3258 case '800080': $colorIdx = 0x24; break;
3259 case '800000': $colorIdx = 0x25; break;
3260 case '008080': $colorIdx = 0x26; break;
3261 case '0000FF': $colorIdx = 0x27; break;
3262 case '00CCFF': $colorIdx = 0x28; break;
3263 case 'CCFFFF': $colorIdx = 0x29; break;
3264 case 'CCFFCC': $colorIdx = 0x2A; break;
3265 case 'FFFF99': $colorIdx = 0x2B; break;
3266 case '99CCFF': $colorIdx = 0x2C; break;
3267 case 'FF99CC': $colorIdx = 0x2D; break;
3268 case 'CC99FF': $colorIdx = 0x2E; break;
3269 case 'FFCC99': $colorIdx = 0x2F; break;
3270 case '3366FF': $colorIdx = 0x30; break;
3271 case '33CCCC': $colorIdx = 0x31; break;
3272 case '99CC00': $colorIdx = 0x32; break;
3273 case 'FFCC00': $colorIdx = 0x33; break;
3274 case 'FF9900': $colorIdx = 0x34; break;
3275 case 'FF6600': $colorIdx = 0x35; break;
3276 case '666699': $colorIdx = 0x36; break;
3277 case '969696': $colorIdx = 0x37; break;
3278 case '003366': $colorIdx = 0x38; break;
3279 case '339966': $colorIdx = 0x39; break;
3280 case '003300': $colorIdx = 0x3A; break;
3281 case '333300': $colorIdx = 0x3B; break;
3282 case '993300': $colorIdx = 0x3C; break;
3283 case '993366': $colorIdx = 0x3D; break;
3284 case '333399': $colorIdx = 0x3E; break;
3285 case '333333': $colorIdx = 0x3F; break;
3286 default: $colorIdx = 0x00; break;
3287 }
3288 $dataBlockFont .= pack('V', $colorIdx);
3289 // Not used (4)
3290 $dataBlockFont .= pack('V', 0x00000000);
3291 // Options flags for modified font attributes
3292 $optionsFlags = 0;
3293 $optionsFlagsBold = ($conditional->getStyle()->getFont()->getBold() == null ? 1 : 0);
3294 $optionsFlags |= (1 == $optionsFlagsBold ? 0x00000002 : 0);
3295 $optionsFlags |= (1 == 1 ? 0x00000008 : 0);
3296 $optionsFlags |= (1 == 1 ? 0x00000010 : 0);
3297 $optionsFlags |= (1 == 0 ? 0x00000020 : 0);
3298 $optionsFlags |= (1 == 1 ? 0x00000080 : 0);
3299 $dataBlockFont .= pack('V', $optionsFlags);
3300 // Escapement type
3301 $dataBlockFont .= pack('V', $fontEscapement);
3302 // Underline type
3303 $dataBlockFont .= pack('V', $fontUnderline);
3304 // Always
3305 $dataBlockFont .= pack('V', 0x00000000);
3306 // Always
3307 $dataBlockFont .= pack('V', 0x00000000);
3308 // Not used (8)
3309 $dataBlockFont .= pack('VV', 0x00000000, 0x00000000);
3310 // Always
3311 $dataBlockFont .= pack('v', 0x0001);
3312 }
3313 if($bFormatAlign == 1){
3314 $blockAlign = 0;
3315 // Alignment and text break
3316 switch ($conditional->getStyle()->getAlignment()->getHorizontal()){
3317 case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL : $blockAlign = 0; break;
3318 case PHPExcel_Style_Alignment::HORIZONTAL_LEFT : $blockAlign = 1; break;
3319 case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT : $blockAlign = 3; break;
3320 case PHPExcel_Style_Alignment::HORIZONTAL_CENTER : $blockAlign = 2; break;
3321 case PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS : $blockAlign = 6; break;
3322 case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY : $blockAlign = 5; break;
3323 }
3324 if($conditional->getStyle()->getAlignment()->getWrapText() == true){
3325 $blockAlign |= 1 << 3;
3326 } else {
3327 $blockAlign |= 0 << 3;
3328 }
3329 switch ($conditional->getStyle()->getAlignment()->getVertical()){
3330 case PHPExcel_Style_Alignment::VERTICAL_BOTTOM : $blockAlign = 2 << 4; break;
3331 case PHPExcel_Style_Alignment::VERTICAL_TOP : $blockAlign = 0 << 4; break;
3332 case PHPExcel_Style_Alignment::VERTICAL_CENTER : $blockAlign = 1 << 4; break;
3333 case PHPExcel_Style_Alignment::VERTICAL_JUSTIFY : $blockAlign = 3 << 4; break;
3334 }
3335 $blockAlign |= 0 << 7;
3336
3337 // Text rotation angle
3338 $blockRotation = $conditional->getStyle()->getAlignment()->getTextRotation();
3339
3340 // Indentation
3341 $blockIndent = $conditional->getStyle()->getAlignment()->getIndent();
3342 if($conditional->getStyle()->getAlignment()->getShrinkToFit() == true){
3343 $blockIndent |= 1 << 4;
3344 } else {
3345 $blockIndent |= 0 << 4;
3346 }
3347 $blockIndent |= 0 << 6;
3348
3349 // Relative indentation
3350 $blockIndentRelative = 255;
3351
3352 $dataBlockAlign = pack('CCvvv', $blockAlign, $blockRotation, $blockIndent, $blockIndentRelative, 0x0000);
3353 }
3354 if($bFormatBorder == 1){
3355 $blockLineStyle = 0;
3356 switch ($conditional->getStyle()->getBorders()->getLeft()->getBorderStyle()){
3357 case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00; break;
3358 case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01; break;
3359 case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02; break;
3360 case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03; break;
3361 case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04; break;
3362 case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05; break;
3363 case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06; break;
3364 case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07; break;
3365 case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08; break;
3366 case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09; break;
3367 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A; break;
3368 case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B; break;
3369 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C; break;
3370 case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D; break;
3371 }
3372 switch ($conditional->getStyle()->getBorders()->getRight()->getBorderStyle()){
3373 case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 4; break;
3374 case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 4; break;
3375 case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 4; break;
3376 case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 4; break;
3377 case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 4; break;
3378 case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 4; break;
3379 case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 4; break;
3380 case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 4; break;
3381 case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 4; break;
3382 case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 4; break;
3383 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 4; break;
3384 case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 4; break;
3385 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 4; break;
3386 case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 4; break;
3387 }
3388 switch ($conditional->getStyle()->getBorders()->getTop()->getBorderStyle()){
3389 case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 8; break;
3390 case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 8; break;
3391 case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 8; break;
3392 case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 8; break;
3393 case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 8; break;
3394 case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 8; break;
3395 case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 8; break;
3396 case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 8; break;
3397 case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 8; break;
3398 case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 8; break;
3399 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 8; break;
3400 case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 8; break;
3401 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 8; break;
3402 case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 8; break;
3403 }
3404 switch ($conditional->getStyle()->getBorders()->getBottom()->getBorderStyle()){
3405 case PHPExcel_Style_Border::BORDER_NONE : $blockLineStyle |= 0x00 << 12; break;
3406 case PHPExcel_Style_Border::BORDER_THIN : $blockLineStyle |= 0x01 << 12; break;
3407 case PHPExcel_Style_Border::BORDER_MEDIUM : $blockLineStyle |= 0x02 << 12; break;
3408 case PHPExcel_Style_Border::BORDER_DASHED : $blockLineStyle |= 0x03 << 12; break;
3409 case PHPExcel_Style_Border::BORDER_DOTTED : $blockLineStyle |= 0x04 << 12; break;
3410 case PHPExcel_Style_Border::BORDER_THICK : $blockLineStyle |= 0x05 << 12; break;
3411 case PHPExcel_Style_Border::BORDER_DOUBLE : $blockLineStyle |= 0x06 << 12; break;
3412 case PHPExcel_Style_Border::BORDER_HAIR : $blockLineStyle |= 0x07 << 12; break;
3413 case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockLineStyle |= 0x08 << 12; break;
3414 case PHPExcel_Style_Border::BORDER_DASHDOT : $blockLineStyle |= 0x09 << 12; break;
3415 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockLineStyle |= 0x0A << 12; break;
3416 case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockLineStyle |= 0x0B << 12; break;
3417 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockLineStyle |= 0x0C << 12; break;
3418 case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockLineStyle |= 0x0D << 12; break;
3419 }
3420 //@todo _writeCFRule() => $blockLineStyle => Index Color for left line
3421 //@todo _writeCFRule() => $blockLineStyle => Index Color for right line
3422 //@todo _writeCFRule() => $blockLineStyle => Top-left to bottom-right on/off
3423 //@todo _writeCFRule() => $blockLineStyle => Bottom-left to top-right on/off
3424 $blockColor = 0;
3425 //@todo _writeCFRule() => $blockColor => Index Color for top line
3426 //@todo _writeCFRule() => $blockColor => Index Color for bottom line
3427 //@todo _writeCFRule() => $blockColor => Index Color for diagonal line
3428 switch ($conditional->getStyle()->getBorders()->getDiagonal()->getBorderStyle()){
3429 case PHPExcel_Style_Border::BORDER_NONE : $blockColor |= 0x00 << 21; break;
3430 case PHPExcel_Style_Border::BORDER_THIN : $blockColor |= 0x01 << 21; break;
3431 case PHPExcel_Style_Border::BORDER_MEDIUM : $blockColor |= 0x02 << 21; break;
3432 case PHPExcel_Style_Border::BORDER_DASHED : $blockColor |= 0x03 << 21; break;
3433 case PHPExcel_Style_Border::BORDER_DOTTED : $blockColor |= 0x04 << 21; break;
3434 case PHPExcel_Style_Border::BORDER_THICK : $blockColor |= 0x05 << 21; break;
3435 case PHPExcel_Style_Border::BORDER_DOUBLE : $blockColor |= 0x06 << 21; break;
3436 case PHPExcel_Style_Border::BORDER_HAIR : $blockColor |= 0x07 << 21; break;
3437 case PHPExcel_Style_Border::BORDER_MEDIUMDASHED : $blockColor |= 0x08 << 21; break;
3438 case PHPExcel_Style_Border::BORDER_DASHDOT : $blockColor |= 0x09 << 21; break;
3439 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT : $blockColor |= 0x0A << 21; break;
3440 case PHPExcel_Style_Border::BORDER_DASHDOTDOT : $blockColor |= 0x0B << 21; break;
3441 case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT : $blockColor |= 0x0C << 21; break;
3442 case PHPExcel_Style_Border::BORDER_SLANTDASHDOT : $blockColor |= 0x0D << 21; break;
3443 }
3444 $dataBlockBorder = pack('vv', $blockLineStyle, $blockColor);
3445 }
3446 if($bFormatFill == 1){
3447 // Fill Patern Style
3448 $blockFillPatternStyle = 0;
3449 switch ($conditional->getStyle()->getFill()->getFillType()){
3450 case PHPExcel_Style_Fill::FILL_NONE : $blockFillPatternStyle = 0x00; break;
3451 case PHPExcel_Style_Fill::FILL_SOLID : $blockFillPatternStyle = 0x01; break;
3452 case PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY : $blockFillPatternStyle = 0x02; break;
3453 case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY : $blockFillPatternStyle = 0x03; break;
3454 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY : $blockFillPatternStyle = 0x04; break;
3455 case PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL : $blockFillPatternStyle = 0x05; break;
3456 case PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL : $blockFillPatternStyle = 0x06; break;
3457 case PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN : $blockFillPatternStyle = 0x07; break;
3458 case PHPExcel_Style_Fill::FILL_PATTERN_DARKUP : $blockFillPatternStyle = 0x08; break;
3459 case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID : $blockFillPatternStyle = 0x09; break;
3460 case PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS : $blockFillPatternStyle = 0x0A; break;
3461 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL : $blockFillPatternStyle = 0x0B; break;
3462 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL : $blockFillPatternStyle = 0x0C; break;
3463 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN : $blockFillPatternStyle = 0x0D; break;
3464 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP : $blockFillPatternStyle = 0x0E; break;
3465 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID : $blockFillPatternStyle = 0x0F; break;
3466 case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS : $blockFillPatternStyle = 0x10; break;
3467 case PHPExcel_Style_Fill::FILL_PATTERN_GRAY125 : $blockFillPatternStyle = 0x11; break;
3468 case PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625 : $blockFillPatternStyle = 0x12; break;
3469 case PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR : $blockFillPatternStyle = 0x00; break; // does not exist in BIFF8
3470 case PHPExcel_Style_Fill::FILL_GRADIENT_PATH : $blockFillPatternStyle = 0x00; break; // does not exist in BIFF8
3471 default : $blockFillPatternStyle = 0x00; break;
3472 }
3473 // Color
3474 switch ($conditional->getStyle()->getFill()->getStartColor()->getRGB()) {
3475 case '000000': $colorIdxBg = 0x08; break;
3476 case 'FFFFFF': $colorIdxBg = 0x09; break;
3477 case 'FF0000': $colorIdxBg = 0x0A; break;
3478 case '00FF00': $colorIdxBg = 0x0B; break;
3479 case '0000FF': $colorIdxBg = 0x0C; break;
3480 case 'FFFF00': $colorIdxBg = 0x0D; break;
3481 case 'FF00FF': $colorIdxBg = 0x0E; break;
3482 case '00FFFF': $colorIdxBg = 0x0F; break;
3483 case '800000': $colorIdxBg = 0x10; break;
3484 case '008000': $colorIdxBg = 0x11; break;
3485 case '000080': $colorIdxBg = 0x12; break;
3486 case '808000': $colorIdxBg = 0x13; break;
3487 case '800080': $colorIdxBg = 0x14; break;
3488 case '008080': $colorIdxBg = 0x15; break;
3489 case 'C0C0C0': $colorIdxBg = 0x16; break;
3490 case '808080': $colorIdxBg = 0x17; break;
3491 case '9999FF': $colorIdxBg = 0x18; break;
3492 case '993366': $colorIdxBg = 0x19; break;
3493 case 'FFFFCC': $colorIdxBg = 0x1A; break;
3494 case 'CCFFFF': $colorIdxBg = 0x1B; break;
3495 case '660066': $colorIdxBg = 0x1C; break;
3496 case 'FF8080': $colorIdxBg = 0x1D; break;
3497 case '0066CC': $colorIdxBg = 0x1E; break;
3498 case 'CCCCFF': $colorIdxBg = 0x1F; break;
3499 case '000080': $colorIdxBg = 0x20; break;
3500 case 'FF00FF': $colorIdxBg = 0x21; break;
3501 case 'FFFF00': $colorIdxBg = 0x22; break;
3502 case '00FFFF': $colorIdxBg = 0x23; break;
3503 case '800080': $colorIdxBg = 0x24; break;
3504 case '800000': $colorIdxBg = 0x25; break;
3505 case '008080': $colorIdxBg = 0x26; break;
3506 case '0000FF': $colorIdxBg = 0x27; break;
3507 case '00CCFF': $colorIdxBg = 0x28; break;
3508 case 'CCFFFF': $colorIdxBg = 0x29; break;
3509 case 'CCFFCC': $colorIdxBg = 0x2A; break;
3510 case 'FFFF99': $colorIdxBg = 0x2B; break;
3511 case '99CCFF': $colorIdxBg = 0x2C; break;
3512 case 'FF99CC': $colorIdxBg = 0x2D; break;
3513 case 'CC99FF': $colorIdxBg = 0x2E; break;
3514 case 'FFCC99': $colorIdxBg = 0x2F; break;
3515 case '3366FF': $colorIdxBg = 0x30; break;
3516 case '33CCCC': $colorIdxBg = 0x31; break;
3517 case '99CC00': $colorIdxBg = 0x32; break;
3518 case 'FFCC00': $colorIdxBg = 0x33; break;
3519 case 'FF9900': $colorIdxBg = 0x34; break;
3520 case 'FF6600': $colorIdxBg = 0x35; break;
3521 case '666699': $colorIdxBg = 0x36; break;
3522 case '969696': $colorIdxBg = 0x37; break;
3523 case '003366': $colorIdxBg = 0x38; break;
3524 case '339966': $colorIdxBg = 0x39; break;
3525 case '003300': $colorIdxBg = 0x3A; break;
3526 case '333300': $colorIdxBg = 0x3B; break;
3527 case '993300': $colorIdxBg = 0x3C; break;
3528 case '993366': $colorIdxBg = 0x3D; break;
3529 case '333399': $colorIdxBg = 0x3E; break;
3530 case '333333': $colorIdxBg = 0x3F; break;
3531 default: $colorIdxBg = 0x41; break;
3532 }
3533 // Fg Color
3534 switch ($conditional->getStyle()->getFill()->getEndColor()->getRGB()) {
3535 case '000000': $colorIdxFg = 0x08; break;
3536 case 'FFFFFF': $colorIdxFg = 0x09; break;
3537 case 'FF0000': $colorIdxFg = 0x0A; break;
3538 case '00FF00': $colorIdxFg = 0x0B; break;
3539 case '0000FF': $colorIdxFg = 0x0C; break;
3540 case 'FFFF00': $colorIdxFg = 0x0D; break;
3541 case 'FF00FF': $colorIdxFg = 0x0E; break;
3542 case '00FFFF': $colorIdxFg = 0x0F; break;
3543 case '800000': $colorIdxFg = 0x10; break;
3544 case '008000': $colorIdxFg = 0x11; break;
3545 case '000080': $colorIdxFg = 0x12; break;
3546 case '808000': $colorIdxFg = 0x13; break;
3547 case '800080': $colorIdxFg = 0x14; break;
3548 case '008080': $colorIdxFg = 0x15; break;
3549 case 'C0C0C0': $colorIdxFg = 0x16; break;
3550 case '808080': $colorIdxFg = 0x17; break;
3551 case '9999FF': $colorIdxFg = 0x18; break;
3552 case '993366': $colorIdxFg = 0x19; break;
3553 case 'FFFFCC': $colorIdxFg = 0x1A; break;
3554 case 'CCFFFF': $colorIdxFg = 0x1B; break;
3555 case '660066': $colorIdxFg = 0x1C; break;
3556 case 'FF8080': $colorIdxFg = 0x1D; break;
3557 case '0066CC': $colorIdxFg = 0x1E; break;
3558 case 'CCCCFF': $colorIdxFg = 0x1F; break;
3559 case '000080': $colorIdxFg = 0x20; break;
3560 case 'FF00FF': $colorIdxFg = 0x21; break;
3561 case 'FFFF00': $colorIdxFg = 0x22; break;
3562 case '00FFFF': $colorIdxFg = 0x23; break;
3563 case '800080': $colorIdxFg = 0x24; break;
3564 case '800000': $colorIdxFg = 0x25; break;
3565 case '008080': $colorIdxFg = 0x26; break;
3566 case '0000FF': $colorIdxFg = 0x27; break;
3567 case '00CCFF': $colorIdxFg = 0x28; break;
3568 case 'CCFFFF': $colorIdxFg = 0x29; break;
3569 case 'CCFFCC': $colorIdxFg = 0x2A; break;
3570 case 'FFFF99': $colorIdxFg = 0x2B; break;
3571 case '99CCFF': $colorIdxFg = 0x2C; break;
3572 case 'FF99CC': $colorIdxFg = 0x2D; break;
3573 case 'CC99FF': $colorIdxFg = 0x2E; break;
3574 case 'FFCC99': $colorIdxFg = 0x2F; break;
3575 case '3366FF': $colorIdxFg = 0x30; break;
3576 case '33CCCC': $colorIdxFg = 0x31; break;
3577 case '99CC00': $colorIdxFg = 0x32; break;
3578 case 'FFCC00': $colorIdxFg = 0x33; break;
3579 case 'FF9900': $colorIdxFg = 0x34; break;
3580 case 'FF6600': $colorIdxFg = 0x35; break;
3581 case '666699': $colorIdxFg = 0x36; break;
3582 case '969696': $colorIdxFg = 0x37; break;
3583 case '003366': $colorIdxFg = 0x38; break;
3584 case '339966': $colorIdxFg = 0x39; break;
3585 case '003300': $colorIdxFg = 0x3A; break;
3586 case '333300': $colorIdxFg = 0x3B; break;
3587 case '993300': $colorIdxFg = 0x3C; break;
3588 case '993366': $colorIdxFg = 0x3D; break;
3589 case '333399': $colorIdxFg = 0x3E; break;
3590 case '333333': $colorIdxFg = 0x3F; break;
3591 default: $colorIdxFg = 0x40; break;
3592 }
3593 $dataBlockFill = pack('v', $blockFillPatternStyle);
3594 $dataBlockFill .= pack('v', $colorIdxFg | ($colorIdxBg << 7));
3595 }
3596 if($bFormatProt == 1){
3597 $dataBlockProtection = 0;
3598 if($conditional->getStyle()->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED){
3599 $dataBlockProtection = 1;
3600 }
3601 if($conditional->getStyle()->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED){
3602 $dataBlockProtection = 1 << 1;
3603 }
3604 }
3605
3606 $data = pack('CCvvVv', $type, $operatorType, $szValue1, $szValue2, $flags, 0x0000);
3607 if($bFormatFont == 1){ // Block Formatting : OK
3608 $data .= $dataBlockFont;
3609 }
3610 if($bFormatAlign == 1){
3611 $data .= $dataBlockAlign;
3612 }
3613 if($bFormatBorder == 1){
3614 $data .= $dataBlockBorder;
3615 }
3616 if($bFormatFill == 1){ // Block Formatting : OK
3617 $data .= $dataBlockFill;
3618 }
3619 if($bFormatProt == 1){
3620 $data .= $dataBlockProtection;
3621 }
3622 if(!is_null($operand1)){
3623 $data .= $operand1;
3624 }
3625 if(!is_null($operand2)){
3626 $data .= $operand2;
3627 }
3628 $header = pack('vv', $record, strlen($data));
3629 $this->_append($header . $data);
3630 }
3631
3635 private function _writeCFHeader(){
3636 $record = 0x01B0; // Record identifier
3637 $length = 0x0016; // Bytes to follow
3638
3639 $numColumnMin = null;
3640 $numColumnMax = null;
3641 $numRowMin = null;
3642 $numRowMax = null;
3643 $arrConditional = array();
3644 foreach ($this->_phpSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
3645 foreach ($conditionalStyles as $conditional) {
3646 if($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
3647 || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS){
3648 if(!in_array($conditional->getHashCode(), $arrConditional)){
3649 $arrConditional[] = $conditional->getHashCode();
3650 }
3651 // Cells
3652 $arrCoord = PHPExcel_Cell::coordinateFromString($cellCoordinate);
3653 if(!is_numeric($arrCoord[0])){
3654 $arrCoord[0] = PHPExcel_Cell::columnIndexFromString($arrCoord[0]);
3655 }
3656 if(is_null($numColumnMin) || ($numColumnMin > $arrCoord[0])){
3657 $numColumnMin = $arrCoord[0];
3658 }
3659 if(is_null($numColumnMax) || ($numColumnMax < $arrCoord[0])){
3660 $numColumnMax = $arrCoord[0];
3661 }
3662 if(is_null($numRowMin) || ($numRowMin > $arrCoord[1])){
3663 $numRowMin = $arrCoord[1];
3664 }
3665 if(is_null($numRowMax) || ($numRowMax < $arrCoord[1])){
3666 $numRowMax = $arrCoord[1];
3667 }
3668 }
3669 }
3670 }
3671 $needRedraw = 1;
3672 $cellRange = pack('vvvv', $numRowMin-1, $numRowMax-1, $numColumnMin-1, $numColumnMax-1);
3673
3674 $header = pack('vv', $record, $length);
3675 $data = pack('vv', count($arrConditional), $needRedraw);
3676 $data .= $cellRange;
3677 $data .= pack('v', 0x0001);
3678 $data .= $cellRange;
3679 $this->_append($header . $data);
3680 }
3681}
$column
Definition: 39dropdown.php:62
$parser
Definition: BPMN2Parser.php:23
$env
$size
Definition: RandomTest.php:84
if(!isset( $_REQUEST[ 'ReturnTo'])) if(!isset($_REQUEST['AuthId'])) $options
Definition: as_login.php:20
An exception for terminatinating execution or to throw for unit testing.
static getErrorCodes()
Get list of error codes.
Definition: DataType.php:68
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
static rangeBoundaries($pRange='A1:A1')
Calculate range boundaries.
Definition: Cell.php:707
static splitRange($pRange='A1:A1')
Split range into coordinate strings.
Definition: Cell.php:660
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
static sizeCol($sheet, $col='A')
Get the width of a column in pixels.
Definition: Excel5.php:46
static sizeRow($sheet, $row=1)
Convert the height of a cell from user's units to pixels.
Definition: Excel5.php:93
static getDefaultColumnWidthByFont(PHPExcel_Style_Font $font, $pPixels=false)
Get the effective column width for columns without a column dimension or column with width -1 For exa...
Definition: Font.php:571
static ConvertEncoding($value, $to, $from)
Convert string from one encoding to another.
Definition: String.php:493
static CountCharacters($value, $enc='UTF-8')
Get character count.
Definition: String.php:550
static UTF8toBIFF8UnicodeLong($value)
Converts a UTF-8 string into BIFF8 Unicode string data (16-bit string length) Writes the string using...
Definition: String.php:469
static UTF8toBIFF8UnicodeShort($value, $arrcRuns=array())
Converts a UTF-8 string into BIFF8 Unicode string data (8-bit string length) Writes the string using ...
Definition: String.php:434
const HORIZONTAL_CENTER_CONTINUOUS
Definition: Alignment.php:43
const BORDER_MEDIUMDASHDOTDOT
Definition: Border.php:48
const BORDER_MEDIUMDASHED
Definition: Border.php:49
const BORDER_SLANTDASHDOT
Definition: Border.php:50
const BORDER_MEDIUMDASHDOT
Definition: Border.php:47
const BORDER_DASHDOTDOT
Definition: Border.php:41
getConditionType()
Get Condition type.
getOperatorType()
Get Operator type.
getConditions()
Get Conditions.
const FILL_PATTERN_LIGHTUP
Definition: Fill.php:57
const FILL_SOLID
Definition: Fill.php:40
const FILL_PATTERN_DARKDOWN
Definition: Fill.php:43
const FILL_PATTERN_DARKUP
Definition: Fill.php:48
const FILL_PATTERN_LIGHTVERTICAL
Definition: Fill.php:58
const FILL_PATTERN_LIGHTGRAY
Definition: Fill.php:53
const FILL_PATTERN_LIGHTDOWN
Definition: Fill.php:52
const FILL_PATTERN_DARKGRAY
Definition: Fill.php:44
const FILL_PATTERN_GRAY125
Definition: Fill.php:51
const FILL_PATTERN_LIGHTHORIZONTAL
Definition: Fill.php:55
const FILL_PATTERN_DARKTRELLIS
Definition: Fill.php:47
const FILL_PATTERN_GRAY0625
Definition: Fill.php:50
const FILL_NONE
Definition: Fill.php:39
const FILL_PATTERN_MEDIUMGRAY
Definition: Fill.php:59
const FILL_GRADIENT_PATH
Definition: Fill.php:42
const FILL_GRADIENT_LINEAR
Definition: Fill.php:41
const FILL_PATTERN_DARKVERTICAL
Definition: Fill.php:49
const FILL_PATTERN_DARKHORIZONTAL
Definition: Fill.php:46
const FILL_PATTERN_LIGHTGRID
Definition: Fill.php:54
const FILL_PATTERN_LIGHTTRELLIS
Definition: Fill.php:56
const FILL_PATTERN_DARKGRID
Definition: Fill.php:45
const UNDERLINE_DOUBLE
Definition: Font.php:40
const UNDERLINE_SINGLEACCOUNTING
Definition: Font.php:43
const UNDERLINE_DOUBLEACCOUNTING
Definition: Font.php:41
const UNDERLINE_SINGLE
Definition: Font.php:42
const UNDERLINE_NONE
Definition: Font.php:39
_storeBof($type)
Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
Definition: BIFFwriter.php:172
_append($data)
General storage function.
Definition: BIFFwriter.php:139
_storeEof()
Writes Excel EOF record to indicate the end of a BIFF stream.
Definition: BIFFwriter.php:195
_writeUrlRange($row1, $col1, $row2, $col2, $url)
This is the more general form of _writeUrl().
Definition: Worksheet.php:1000
_writeNote($row, $col, $note)
Writes a note associated with the cell given by the row and column.
Definition: Worksheet.php:781
_writePanes()
Writes the Excel BIFF PANE record.
Definition: Worksheet.php:1732
_writeHeader()
Store the header caption BIFF record.
Definition: Worksheet.php:1880
_writeObjPicture($colL, $dxL, $rwT, $dyT, $colR, $dxR, $rwB, $dyB)
Store the OBJ record that precedes an IMDATA record.
Definition: Worksheet.php:2510
_writeUrlWeb($row1, $col1, $row2, $col2, $url)
Used to write http, ftp and mailto hyperlinks.
Definition: Worksheet.php:1026
_writePrintHeaders()
Write the PRINTHEADERS BIFF record.
Definition: Worksheet.php:2037
_writeAutoFilterInfo()
Write the AUTOFILTERINFO BIFF record.
Definition: Worksheet.php:2084
__construct(&$str_total, &$str_unique, &$str_table, &$colors, $parser, $preCalculateFormulas, $phpSheet)
Constructor.
Definition: Worksheet.php:214
_writeWsbool()
Write the WSBOOL BIFF record, mainly for fit-to-page.
Definition: Worksheet.php:2147
_writeScenProtect()
Write SCENPROTECT.
Definition: Worksheet.php:2288
close()
Add data to the beginning of the workbook (note the reverse order) and to the end of the workbook.
Definition: Worksheet.php:276
_writeCFHeader()
Write CFHeader record.
Definition: Worksheet.php:3635
_writeRangeProtection()
Write BIFF record RANGEPROTECTION.
Definition: Worksheet.php:1631
_writePassword()
Write the worksheet PASSWORD record.
Definition: Worksheet.php:2336
printRowColHeaders($print=1)
Set the option to print the row and column headers on the printed page.
Definition: Worksheet.php:609
_writeNumber($row, $col, $num, $xfIndex)
Write a double to the specified row and column (zero indexed).
Definition: Worksheet.php:650
if(preg_match('/^ $link_type[A-Z]:/', $url))
Definition: Worksheet.php:1149
setEscher(PHPExcel_Shared_Escher $pValue=null)
Set Escher object.
Definition: Worksheet.php:2725
static _mapErrorCode($errorCode)
Map Error code.
Definition: Worksheet.php:2976
_writeStringRecord($stringValue)
Write a STRING record.
Definition: Worksheet.php:948
_writeString($row, $col, $str, $xfIndex)
Write a LABELSST record or a LABEL record.
Definition: Worksheet.php:674
_writeGuts()
Write the GUTS BIFF record.
Definition: Worksheet.php:2103
_positionImage($col_start, $row_start, $x1, $y1, $width, $height)
Calculate the vertices that define the position of the image as required by the OBJ record.
Definition: Worksheet.php:2440
_writeUrlInternal($row1, $col1, $row2, $col2, $url)
Used to write internal reference hyperlinks such as "Sheet1!A1".
Definition: Worksheet.php:1071
setOutline($visible=true, $symbols_below=true, $symbols_right=true, $auto_style=false)
This method sets the properties for outlining and grouping.
Definition: Worksheet.php:623
_writeSheetProtection()
Write SHEETPROTECTION.
Definition: Worksheet.php:1584
_writeRichTextString($row, $col, $str, $xfIndex, $arrcRun)
Write a LABELSST record or a LABEL record.
Definition: Worksheet.php:688
_writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
Write a formula to the specified row and column (zero indexed).
Definition: Worksheet.php:866
_writeGridset()
Write the GRIDSET BIFF record.
Definition: Worksheet.php:2069
_writeMarginTop()
Store the TOPMARGIN BIFF record.
Definition: Worksheet.php:1999
_writeObjectProtect()
Write OBJECTPROTECT.
Definition: Worksheet.php:2312
_writeSetup()
Store the page setup SETUP BIFF record.
Definition: Worksheet.php:1812
_writePrintGridlines()
Write the PRINTGRIDLINES BIFF record.
Definition: Worksheet.php:2053
_writeMsoDrawing()
Write MSODRAWING record.
Definition: Worksheet.php:2733
_writeMarginLeft()
Store the LEFTMARGIN BIFF record.
Definition: Worksheet.php:1961
_processBitmap($bitmap)
Convert a 24 bit bitmap into the modified internal format used by Windows.
Definition: Worksheet.php:2613
_writeVcenter()
Store the vertical centering VCENTER BIFF record.
Definition: Worksheet.php:1946
_writeWindow2()
Write BIFF record Window2.
Definition: Worksheet.php:1289
_writeDefaultRowHeight()
Write BIFF record DEFAULTROWHEIGHT.
Definition: Worksheet.php:1343
getEscher()
Get Escher object.
Definition: Worksheet.php:2715
_writeZoom()
Store the window zoom factor.
Definition: Worksheet.php:2695
_writeBoolErr($row, $col, $value, $isError, $xfIndex)
Write a boolean or an error type to the specified row and column (zero indexed)
Definition: Worksheet.php:839
_writeDefcol()
Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
Definition: Worksheet.php:1365
_writeProtect()
Set the Biff PROTECT record to indicate that the worksheet is protected.
Definition: Worksheet.php:2267
_writeBreaks()
Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
Definition: Worksheet.php:2182
_writeRow($row, $height, $xfIndex, $hidden=false, $level=0)
This method is used to set the height and format for a row.
Definition: Worksheet.php:1219
_writeUrlExternal($row1, $col1, $row2, $col2, $url)
Write links to external directory names such as 'c:\foo.xls', c:\foo.xls::Sheet1!A1',...
Definition: Worksheet.php:1124
insertBitmap($row, $col, $bitmap, $x=0, $y=0, $scale_x=1, $scale_y=1)
Insert a 24bit bitmap image in a worksheet.
Definition: Worksheet.php:2366
_writeExterncount($count)
Write BIFF record EXTERNCOUNT to indicate the number of external sheet references in a worksheet.
Definition: Worksheet.php:1685
_writeMergedCells()
Store the MERGEDCELLS records for all ranges of merged cells.
Definition: Worksheet.php:1503
_writeDataValidity()
Store the DATAVALIDATIONS and DATAVALIDATION records.
Definition: Worksheet.php:2816
_writeCFRule(PHPExcel_Style_Conditional $conditional)
Write CFRule Record.
Definition: Worksheet.php:3024
_writeBlank($row, $col, $xfIndex)
Write a blank cell to the specified row and column (zero indexed).
Definition: Worksheet.php:819
_writeColinfo($col_array)
Write BIFF record COLINFO to define column widths.
Definition: Worksheet.php:1391
_writeHcenter()
Store the horizontal centering HCENTER BIFF record.
Definition: Worksheet.php:1930
_writeMarginRight()
Store the RIGHTMARGIN BIFF record.
Definition: Worksheet.php:1980
_writeDimensions()
Writes Excel DIMENSIONS to define the area in which there is data.
Definition: Worksheet.php:1269
_writeBIFF8CellRangeAddressFixed($range='A1')
Write a cell range address in BIFF8 always fixed range See section 2.5.14 in OpenOffice....
Definition: Worksheet.php:558
_writeLabelSst($row, $col, $str, $xfIndex)
Write a string to the specified row and column (zero indexed).
Definition: Worksheet.php:755
_writeLabel($row, $col, $str, $xfIndex)
Write a string to the specified row and column (zero indexed).
Definition: Worksheet.php:719
_writeSelection()
Write BIFF record SELECTION.
Definition: Worksheet.php:1439
_writePageLayoutView()
Write PLV Record.
Definition: Worksheet.php:2993
_processBitmapGd($image)
Convert a GD-image into the internal format.
Definition: Worksheet.php:2583
_writeExternsheet($sheetname)
Writes the Excel BIFF EXTERNSHEET record.
Definition: Worksheet.php:1703
_writeFooter()
Store the footer caption BIFF record.
Definition: Worksheet.php:1904
_writeMarginBottom()
Store the BOTTOMMARGIN BIFF record.
Definition: Worksheet.php:2018
_writeUrl($row, $col, $url)
Write a hyperlink.
Definition: Worksheet.php:979
getData()
Retrieves data from memory in one chunk, or from disk in $buffer sized chunks.
Definition: Worksheet.php:589
_writeSheetLayout()
Write SHEETLAYOUT record.
Definition: Worksheet.php:1557
$key
Definition: croninfo.php:18
$i
Definition: disco.tpl.php:19
$y
Definition: example_007.php:83
$x
Definition: example_009.php:98
if(!array_key_exists('StateId', $_REQUEST)) $id
$error
Definition: Error.php:17
$type
$password
Definition: pwgen.php:17
$errorCode