ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
Worksheet.php
Go to the documentation of this file.
1<?php
2/*
3* Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
4*
5* The majority of this is _NOT_ my code. I simply ported it from the
6* PERL Spreadsheet::WriteExcel module.
7*
8* The author of the Spreadsheet::WriteExcel module is John McNamara
9* <jmcnamara@cpan.org>
10*
11* I _DO_ maintain this code, and John McNamara has nothing to do with the
12* porting of this code to PHP. Any questions directly related to this
13* class library should be directed to me.
14*
15* License Information:
16*
17* Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets
18* Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
19*
20* This library is free software; you can redistribute it and/or
21* modify it under the terms of the GNU Lesser General Public
22* License as published by the Free Software Foundation; either
23* version 2.1 of the License, or (at your option) any later version.
24*
25* This library is distributed in the hope that it will be useful,
26* but WITHOUT ANY WARRANTY; without even the implied warranty of
27* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28* Lesser General Public License for more details.
29*
30* You should have received a copy of the GNU Lesser General Public
31* License along with this library; if not, write to the Free Software
32* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
33*/
34
35require_once 'Spreadsheet/Excel/Writer/Parser.php';
36require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
37
47{
52 var $name;
53
58 var $index;
59
65
71
77
83
89
95
101
108
115
122
129
135
141
147
153
159
165
171
177
183
189
195
201
207
213
219
225
231
237
243
249
255
261
267
273
279
285
291
297
303
309
315
321
327
333
339
345
351
357
371 $index, &$activesheet,
372 &$firstsheet, &$str_total,
373 &$str_unique, &$str_table,
374 &$url_format, &$parser,
375 $tmp_dir)
376 {
377 // It needs to call its parent's constructor explicitly
379 $this->_BIFF_version = $BIFF_version;
380 $rowmax = 65536; // 16384 in Excel 5
381 $colmax = 256;
382
383 $this->name = $name;
384 $this->index = $index;
385 $this->activesheet = &$activesheet;
386 $this->firstsheet = &$firstsheet;
387 $this->_str_total = &$str_total;
388 $this->_str_unique = &$str_unique;
389 $this->_str_table = &$str_table;
390 $this->_url_format = &$url_format;
391 $this->_parser = &$parser;
392
393 //$this->ext_sheets = array();
394 $this->_filehandle = '';
395 $this->_using_tmpfile = true;
396 //$this->fileclosed = 0;
397 //$this->offset = 0;
398 $this->_xls_rowmax = $rowmax;
399 $this->_xls_colmax = $colmax;
400 $this->_xls_strmax = 255;
401 $this->_dim_rowmin = $rowmax + 1;
402 $this->_dim_rowmax = 0;
403 $this->_dim_colmin = $colmax + 1;
404 $this->_dim_colmax = 0;
405 $this->_colinfo = array();
406 $this->_selection = array(0,0,0,0);
407 $this->_panes = array();
408 $this->_active_pane = 3;
409 $this->_frozen = 0;
410 $this->selected = 0;
411
412 $this->_paper_size = 0x0;
413 $this->_orientation = 0x1;
414 $this->_header = '';
415 $this->_footer = '';
416 $this->_hcenter = 0;
417 $this->_vcenter = 0;
418 $this->_margin_head = 0.50;
419 $this->_margin_foot = 0.50;
420 $this->_margin_left = 0.75;
421 $this->_margin_right = 0.75;
422 $this->_margin_top = 1.00;
423 $this->_margin_bottom = 1.00;
424
425 $this->title_rowmin = null;
426 $this->title_rowmax = null;
427 $this->title_colmin = null;
428 $this->title_colmax = null;
429 $this->print_rowmin = null;
430 $this->print_rowmax = null;
431 $this->print_colmin = null;
432 $this->print_colmax = null;
433
434 $this->_print_gridlines = 1;
435 $this->_screen_gridlines = 1;
436 $this->_print_headers = 0;
437
438 $this->_fit_page = 0;
439 $this->_fit_width = 0;
440 $this->_fit_height = 0;
441
442 $this->_hbreaks = array();
443 $this->_vbreaks = array();
444
445 $this->_protect = 0;
446 $this->_password = null;
447
448 $this->col_sizes = array();
449 $this->_row_sizes = array();
450
451 $this->_zoom = 100;
452 $this->_print_scale = 100;
453
454 $this->_outline_row_level = 0;
455 $this->_outline_style = 0;
456 $this->_outline_below = 1;
457 $this->_outline_right = 1;
458 $this->_outline_on = 1;
459
460 $this->_merged_ranges = array();
461
462 $this->_input_encoding = '';
463
464 $this->_dv = array();
465
466 $this->_tmp_dir = $tmp_dir;
467
468 $this->_initialize();
469 }
470
478 function _initialize()
479 {
480 if ($this->_using_tmpfile == false) {
481 return;
482 }
483
484 if ($this->_tmp_dir === '' && ini_get('open_basedir') === false) {
485 // open_basedir restriction in effect - store data in memory
486 // ToDo: Let the error actually have an effect somewhere
487 $this->_using_tmpfile = false;
488 return new PEAR_Error('Temp file could not be opened since open_basedir restriction in effect - please use setTmpDir() - using memory storage instead');
489 }
490
491 // Open tmp file for storing Worksheet data
492 if ($this->_tmp_dir === '') {
493 $fh = tmpfile();
494 } else {
495 // For people with open base dir restriction
496 $tmpfilename = tempnam($this->_tmp_dir, "Spreadsheet_Excel_Writer");
497 $fh = @fopen($tmpfilename, "w+b");
498 }
499
500 if ($fh === false) {
501 // If tmpfile() fails store data in memory
502 $this->_using_tmpfile = false;
503 } else {
504 // Store filehandle
505 $this->_filehandle = $fh;
506 }
507 }
508
518 function close($sheetnames)
519 {
520 $num_sheets = count($sheetnames);
521
522 /***********************************************
523 * Prepend in reverse order!!
524 */
525
526 // Prepend the sheet dimensions
527 $this->_storeDimensions();
528
529 // Prepend the sheet password
530 $this->_storePassword();
531
532 // Prepend the sheet protection
533 $this->_storeProtect();
534
535 // Prepend the page setup
536 $this->_storeSetup();
537
538 /* FIXME: margins are actually appended */
539 // Prepend the bottom margin
540 $this->_storeMarginBottom();
541
542 // Prepend the top margin
543 $this->_storeMarginTop();
544
545 // Prepend the right margin
546 $this->_storeMarginRight();
547
548 // Prepend the left margin
549 $this->_storeMarginLeft();
550
551 // Prepend the page vertical centering
552 $this->_storeVcenter();
553
554 // Prepend the page horizontal centering
555 $this->_storeHcenter();
556
557 // Prepend the page footer
558 $this->_storeFooter();
559
560 // Prepend the page header
561 $this->_storeHeader();
562
563 // Prepend the vertical page breaks
564 $this->_storeVbreak();
565
566 // Prepend the horizontal page breaks
567 $this->_storeHbreak();
568
569 // Prepend WSBOOL
570 $this->_storeWsbool();
571
572 // Prepend GRIDSET
573 $this->_storeGridset();
574
575 // Prepend GUTS
576 if ($this->_BIFF_version == 0x0500) {
577 $this->_storeGuts();
578 }
579
580 // Prepend PRINTGRIDLINES
581 $this->_storePrintGridlines();
582
583 // Prepend PRINTHEADERS
584 $this->_storePrintHeaders();
585
586 // Prepend EXTERNSHEET references
587 if ($this->_BIFF_version == 0x0500) {
588 for ($i = $num_sheets; $i > 0; $i--) {
589 $sheetname = $sheetnames[$i-1];
590 $this->_storeExternsheet($sheetname);
591 }
592 }
593
594 // Prepend the EXTERNCOUNT of external references.
595 if ($this->_BIFF_version == 0x0500) {
596 $this->_storeExterncount($num_sheets);
597 }
598
599 // Prepend the COLINFO records if they exist
600 if (!empty($this->_colinfo)) {
601 $colcount = count($this->_colinfo);
602 for ($i = 0; $i < $colcount; $i++) {
603 $this->_storeColinfo($this->_colinfo[$i]);
604 }
605 $this->_storeDefcol();
606 }
607
608 // Prepend the BOF record
609 $this->_storeBof(0x0010);
610
611 /*
612 * End of prepend. Read upwards from here.
613 ***********************************************/
614
615 // Append
616 $this->_storeWindow2();
617 $this->_storeZoom();
618 if (!empty($this->_panes)) {
619 $this->_storePanes($this->_panes);
620 }
621 $this->_storeSelection($this->_selection);
622 $this->_storeMergedCells();
623 /* TODO: add data validity */
624 /*if ($this->_BIFF_version == 0x0600) {
625 $this->_storeDataValidity();
626 }*/
627 $this->_storeEof();
628 }
629
637 function getName()
638 {
639 return $this->name;
640 }
641
648 function getData()
649 {
650 $buffer = 4096;
651
652 // Return data stored in memory
653 if (isset($this->_data)) {
654 $tmp = $this->_data;
655 unset($this->_data);
656 $fh = $this->_filehandle;
657 if ($this->_using_tmpfile) {
658 fseek($fh, 0);
659 }
660 return $tmp;
661 }
662 // Return data stored on disk
663 if ($this->_using_tmpfile) {
664 if ($tmp = fread($this->_filehandle, $buffer)) {
665 return $tmp;
666 }
667 }
668
669 // No data to return
670 return '';
671 }
672
682 function setMerge($first_row, $first_col, $last_row, $last_col)
683 {
684 if (($last_row < $first_row) || ($last_col < $first_col)) {
685 return;
686 }
687 // don't check rowmin, rowmax, etc... because we don't know when this
688 // is going to be called
689 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col);
690 }
691
698 function select()
699 {
700 $this->selected = 1;
701 }
702
710 function activate()
711 {
712 $this->selected = 1;
713 $this->activesheet = $this->index;
714 }
715
723 function setFirstSheet()
724 {
725 $this->firstsheet = $this->index;
726 }
727
736 function protect($password)
737 {
738 $this->_protect = 1;
739 $this->_password = $this->_encodePassword($password);
740 }
741
753 function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
754 {
755 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
756
757 // Set width to zero if column is hidden
758 $width = ($hidden) ? 0 : $width;
759
760 for ($col = $firstcol; $col <= $lastcol; $col++) {
761 $this->col_sizes[$col] = $width;
762 }
763 }
764
774 function setSelection($first_row,$first_column,$last_row,$last_column)
775 {
776 $this->_selection = array($first_row,$first_column,$last_row,$last_column);
777 }
778
790 function freezePanes($panes)
791 {
792 $this->_frozen = 1;
793 $this->_panes = $panes;
794 }
795
807 function thawPanes($panes)
808 {
809 $this->_frozen = 0;
810 $this->_panes = $panes;
811 }
812
818 function setPortrait()
819 {
820 $this->_orientation = 1;
821 }
822
828 function setLandscape()
829 {
830 $this->_orientation = 0;
831 }
832
839 function setPaper($size = 0)
840 {
841 $this->_paper_size = $size;
842 }
843
844
852 function setHeader($string,$margin = 0.50)
853 {
854 if (strlen($string) >= 255) {
855 //carp 'Header string must be less than 255 characters';
856 return;
857 }
858 $this->_header = $string;
859 $this->_margin_head = $margin;
860 }
861
869 function setFooter($string,$margin = 0.50)
870 {
871 if (strlen($string) >= 255) {
872 //carp 'Footer string must be less than 255 characters';
873 return;
874 }
875 $this->_footer = $string;
876 $this->_margin_foot = $margin;
877 }
878
885 function centerHorizontally($center = 1)
886 {
887 $this->_hcenter = $center;
888 }
889
896 function centerVertically($center = 1)
897 {
898 $this->_vcenter = $center;
899 }
900
907 function setMargins($margin)
908 {
909 $this->setMarginLeft($margin);
910 $this->setMarginRight($margin);
911 $this->setMarginTop($margin);
912 $this->setMarginBottom($margin);
913 }
914
921 function setMargins_LR($margin)
922 {
923 $this->setMarginLeft($margin);
924 $this->setMarginRight($margin);
925 }
926
933 function setMargins_TB($margin)
934 {
935 $this->setMarginTop($margin);
936 $this->setMarginBottom($margin);
937 }
938
945 function setMarginLeft($margin = 0.75)
946 {
947 $this->_margin_left = $margin;
948 }
949
956 function setMarginRight($margin = 0.75)
957 {
958 $this->_margin_right = $margin;
959 }
960
967 function setMarginTop($margin = 1.00)
968 {
969 $this->_margin_top = $margin;
970 }
971
978 function setMarginBottom($margin = 1.00)
979 {
980 $this->_margin_bottom = $margin;
981 }
982
990 function repeatRows($first_row, $last_row = null)
991 {
992 $this->title_rowmin = $first_row;
993 if (isset($last_row)) { //Second row is optional
994 $this->title_rowmax = $last_row;
995 } else {
996 $this->title_rowmax = $first_row;
997 }
998 }
999
1007 function repeatColumns($first_col, $last_col = null)
1008 {
1009 $this->title_colmin = $first_col;
1010 if (isset($last_col)) { // Second col is optional
1011 $this->title_colmax = $last_col;
1012 } else {
1013 $this->title_colmax = $first_col;
1014 }
1015 }
1016
1026 function printArea($first_row, $first_col, $last_row, $last_col)
1027 {
1028 $this->print_rowmin = $first_row;
1029 $this->print_colmin = $first_col;
1030 $this->print_rowmax = $last_row;
1031 $this->print_colmax = $last_col;
1032 }
1033
1034
1040 function hideGridlines()
1041 {
1042 $this->_print_gridlines = 0;
1043 }
1044
1051 {
1052 $this->_screen_gridlines = 0;
1053 }
1054
1061 function printRowColHeaders($print = 1)
1062 {
1063 $this->_print_headers = $print;
1064 }
1065
1075 function fitToPages($width, $height)
1076 {
1077 $this->_fit_page = 1;
1078 $this->_fit_width = $width;
1079 $this->_fit_height = $height;
1080 }
1081
1089 function setHPagebreaks($breaks)
1090 {
1091 foreach ($breaks as $break) {
1092 array_push($this->_hbreaks, $break);
1093 }
1094 }
1095
1103 function setVPagebreaks($breaks)
1104 {
1105 foreach ($breaks as $break) {
1106 array_push($this->_vbreaks, $break);
1107 }
1108 }
1109
1110
1117 function setZoom($scale = 100)
1118 {
1119 // Confine the scale to Excel's range
1120 if ($scale < 10 || $scale > 400) {
1121 $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");
1122 $scale = 100;
1123 }
1124
1125 $this->_zoom = floor($scale);
1126 }
1127
1135 function setPrintScale($scale = 100)
1136 {
1137 // Confine the scale to Excel's range
1138 if ($scale < 10 || $scale > 400) {
1139 $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");
1140 $scale = 100;
1141 }
1142
1143 // Turn off "fit to page" option
1144 $this->_fit_page = 0;
1145
1146 $this->_print_scale = floor($scale);
1147 }
1148
1158 function write($row, $col, $token, $format = null)
1159 {
1160 // Check for a cell reference in A1 notation and substitute row and column
1161 /*if ($_[0] =~ /^\D/) {
1162 @_ = $this->_substituteCellref(@_);
1163 }*/
1164
1165 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
1166 // Match number
1167 return $this->writeNumber($row, $col, $token, $format);
1168 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
1169 // Match http or ftp URL
1170 return $this->writeUrl($row, $col, $token, '', $format);
1171 } elseif (preg_match("/^mailto:/", $token)) {
1172 // Match mailto:
1173 return $this->writeUrl($row, $col, $token, '', $format);
1174 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
1175 // Match internal or external sheet link
1176 return $this->writeUrl($row, $col, $token, '', $format);
1177 } elseif (preg_match("/^=/", $token)) {
1178 // Match formula
1179 return $this->writeFormula($row, $col, $token, $format);
1180 } elseif ($token == '') {
1181 // Match blank
1182 return $this->writeBlank($row, $col, $format);
1183 } else {
1184 // Default: match string
1185 return $this->writeString($row, $col, $token, $format);
1186 }
1187 }
1188
1200 function writeRow($row, $col, $val, $format = null)
1201 {
1202 $retval = '';
1203 if (is_array($val)) {
1204 foreach ($val as $v) {
1205 if (is_array($v)) {
1206 $this->writeCol($row, $col, $v, $format);
1207 } else {
1208 $this->write($row, $col, $v, $format);
1209 }
1210 $col++;
1211 }
1212 } else {
1213 $retval = new PEAR_Error('$val needs to be an array');
1214 }
1215 return($retval);
1216 }
1217
1229 function writeCol($row, $col, $val, $format = null)
1230 {
1231 $retval = '';
1232 if (is_array($val)) {
1233 foreach ($val as $v) {
1234 $this->write($row, $col, $v, $format);
1235 $row++;
1236 }
1237 } else {
1238 $retval = new PEAR_Error('$val needs to be an array');
1239 }
1240 return($retval);
1241 }
1242
1250 function _XF(&$format)
1251 {
1252 if ($format) {
1253 return($format->getXfIndex());
1254 } else {
1255 return(0x0F);
1256 }
1257 }
1258
1259
1260 /******************************************************************************
1261 *******************************************************************************
1262 *
1263 * Internal methods
1264 */
1265
1266
1274 function _append($data)
1275 {
1276 if ($this->_using_tmpfile) {
1277 // Add CONTINUE records if necessary
1278 if (strlen($data) > $this->_limit) {
1279 $data = $this->_addContinue($data);
1280 }
1281 fwrite($this->_filehandle, $data);
1282 $this->_datasize += strlen($data);
1283 } else {
1284 parent::_append($data);
1285 }
1286 }
1287
1298 function _substituteCellref($cell)
1299 {
1300 $cell = strtoupper($cell);
1301
1302 // Convert a column range: 'A:A' or 'B:G'
1303 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
1304 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
1305 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
1306 return(array($col1, $col2));
1307 }
1308
1309 // Convert a cell range: 'A1:B7'
1310 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
1311 list($row1, $col1) = $this->_cellToRowcol($match[1]);
1312 list($row2, $col2) = $this->_cellToRowcol($match[2]);
1313 return(array($row1, $col1, $row2, $col2));
1314 }
1315
1316 // Convert a cell reference: 'A1' or 'AD2000'
1317 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
1318 list($row1, $col1) = $this->_cellToRowcol($match[1]);
1319 return(array($row1, $col1));
1320 }
1321
1322 // TODO use real error codes
1323 $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE);
1324 }
1325
1334 function _cellToRowcol($cell)
1335 {
1336 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
1337 $col = $match[1];
1338 $row = $match[2];
1339
1340 // Convert base26 column string to number
1341 $chars = split('', $col);
1342 $expn = 0;
1343 $col = 0;
1344
1345 while ($chars) {
1346 $char = array_pop($chars); // LS char first
1347 $col += (ord($char) -ord('A') +1) * pow(26,$expn);
1348 $expn++;
1349 }
1350
1351 // Convert 1-index to zero-index
1352 $row--;
1353 $col--;
1354
1355 return(array($row, $col));
1356 }
1357
1365 function _encodePassword($plaintext)
1366 {
1367 $password = 0x0000;
1368 $i = 1; // char position
1369
1370 // split the plain text password in its component characters
1371 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
1372 foreach ($chars as $char) {
1373 $value = ord($char) << $i; // shifted ASCII value
1374 $rotated_bits = $value >> 15; // rotated bits beyond bit 15
1375 $value &= 0x7fff; // first 15 bits
1376 $password ^= ($value | $rotated_bits);
1377 $i++;
1378 }
1379
1380 $password ^= strlen($plaintext);
1381 $password ^= 0xCE4B;
1382
1383 return($password);
1384 }
1385
1395 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
1396 {
1397 $this->_outline_on = $visible;
1398 $this->_outline_below = $symbols_below;
1399 $this->_outline_right = $symbols_right;
1400 $this->_outline_style = $auto_style;
1401
1402 // Ensure this is a boolean vale for Window2
1403 if ($this->_outline_on) {
1404 $this->_outline_on = 1;
1405 }
1406 }
1407
1408 /******************************************************************************
1409 *******************************************************************************
1410 *
1411 * BIFF RECORDS
1412 */
1413
1414
1430 function writeNumber($row, $col, $num, $format = null)
1431 {
1432 $record = 0x0203; // Record identifier
1433 $length = 0x000E; // Number of bytes to follow
1434
1435 $xf = $this->_XF($format); // The cell format
1436
1437 // Check that row and col are valid and store max and min values
1438 if ($row >= $this->_xls_rowmax) {
1439 return(-2);
1440 }
1441 if ($col >= $this->_xls_colmax) {
1442 return(-2);
1443 }
1444 if ($row < $this->_dim_rowmin) {
1445 $this->_dim_rowmin = $row;
1446 }
1447 if ($row > $this->_dim_rowmax) {
1448 $this->_dim_rowmax = $row;
1449 }
1450 if ($col < $this->_dim_colmin) {
1451 $this->_dim_colmin = $col;
1452 }
1453 if ($col > $this->_dim_colmax) {
1454 $this->_dim_colmax = $col;
1455 }
1456
1457 $header = pack("vv", $record, $length);
1458 $data = pack("vvv", $row, $col, $xf);
1459 $xl_double = pack("d", $num);
1460 if ($this->_byte_order) { // if it's Big Endian
1461 $xl_double = strrev($xl_double);
1462 }
1463
1464 $this->_append($header.$data.$xl_double);
1465 return(0);
1466 }
1467
1483 function writeString($row, $col, $str, $format = null)
1484 {
1485 if ($this->_BIFF_version == 0x0600) {
1486 return $this->writeStringBIFF8($row, $col, $str, $format);
1487 }
1488 $strlen = strlen($str);
1489 $record = 0x0204; // Record identifier
1490 $length = 0x0008 + $strlen; // Bytes to follow
1491 $xf = $this->_XF($format); // The cell format
1492
1493 $str_error = 0;
1494
1495 // Check that row and col are valid and store max and min values
1496 if ($row >= $this->_xls_rowmax) {
1497 return(-2);
1498 }
1499 if ($col >= $this->_xls_colmax) {
1500 return(-2);
1501 }
1502 if ($row < $this->_dim_rowmin) {
1503 $this->_dim_rowmin = $row;
1504 }
1505 if ($row > $this->_dim_rowmax) {
1506 $this->_dim_rowmax = $row;
1507 }
1508 if ($col < $this->_dim_colmin) {
1509 $this->_dim_colmin = $col;
1510 }
1511 if ($col > $this->_dim_colmax) {
1512 $this->_dim_colmax = $col;
1513 }
1514
1515 if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
1516 $str = substr($str, 0, $this->_xls_strmax);
1517 $length = 0x0008 + $this->_xls_strmax;
1518 $strlen = $this->_xls_strmax;
1519 $str_error = -3;
1520 }
1521
1522 $header = pack("vv", $record, $length);
1523 $data = pack("vvvv", $row, $col, $xf, $strlen);
1524 $this->_append($header . $data . $str);
1525 return($str_error);
1526 }
1527
1534 function setInputEncoding($encoding)
1535 {
1536 if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
1537 $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
1538 }
1539 $this->_input_encoding = $encoding;
1540 }
1541
1557 function writeStringBIFF8($row, $col, $str, $format = null)
1558 {
1559 if ($this->_input_encoding == 'UTF-16LE')
1560 {
1561 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1562 $encoding = 0x1;
1563 }
1564 elseif ($this->_input_encoding != '')
1565 {
1566 $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
1567 $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1568 $encoding = 0x1;
1569 }
1570 else
1571 {
1572 $strlen = strlen($str);
1573 $encoding = 0x0;
1574 }
1575 $record = 0x00FD; // Record identifier
1576 $length = 0x000A; // Bytes to follow
1577 $xf = $this->_XF($format); // The cell format
1578
1579 $str_error = 0;
1580
1581 // Check that row and col are valid and store max and min values
1582 if ($this->_checkRowCol($row, $col) == false) {
1583 return -2;
1584 }
1585
1586 $str = pack('vC', $strlen, $encoding).$str;
1587
1588 /* check if string is already present */
1589 if (!isset($this->_str_table[$str])) {
1590 $this->_str_table[$str] = $this->_str_unique++;
1591 }
1592 $this->_str_total++;
1593
1594 $header = pack('vv', $record, $length);
1595 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
1596 $this->_append($header.$data);
1597 return $str_error;
1598 }
1599
1610 function _checkRowCol($row, $col)
1611 {
1612 if ($row >= $this->_xls_rowmax) {
1613 return false;
1614 }
1615 if ($col >= $this->_xls_colmax) {
1616 return false;
1617 }
1618 if ($row < $this->_dim_rowmin) {
1619 $this->_dim_rowmin = $row;
1620 }
1621 if ($row > $this->_dim_rowmax) {
1622 $this->_dim_rowmax = $row;
1623 }
1624 if ($col < $this->_dim_colmin) {
1625 $this->_dim_colmin = $col;
1626 }
1627 if ($col > $this->_dim_colmax) {
1628 $this->_dim_colmax = $col;
1629 }
1630 return true;
1631 }
1632
1642 function writeNote($row, $col, $note)
1643 {
1644 $note_length = strlen($note);
1645 $record = 0x001C; // Record identifier
1646 $max_length = 2048; // Maximun length for a NOTE record
1647 //$length = 0x0006 + $note_length; // Bytes to follow
1648
1649 // Check that row and col are valid and store max and min values
1650 if ($row >= $this->_xls_rowmax) {
1651 return(-2);
1652 }
1653 if ($col >= $this->_xls_colmax) {
1654 return(-2);
1655 }
1656 if ($row < $this->_dim_rowmin) {
1657 $this->_dim_rowmin = $row;
1658 }
1659 if ($row > $this->_dim_rowmax) {
1660 $this->_dim_rowmax = $row;
1661 }
1662 if ($col < $this->_dim_colmin) {
1663 $this->_dim_colmin = $col;
1664 }
1665 if ($col > $this->_dim_colmax) {
1666 $this->_dim_colmax = $col;
1667 }
1668
1669 // Length for this record is no more than 2048 + 6
1670 $length = 0x0006 + min($note_length, 2048);
1671 $header = pack("vv", $record, $length);
1672 $data = pack("vvv", $row, $col, $note_length);
1673 $this->_append($header . $data . substr($note, 0, 2048));
1674
1675 for ($i = $max_length; $i < $note_length; $i += $max_length) {
1676 $chunk = substr($note, $i, $max_length);
1677 $length = 0x0006 + strlen($chunk);
1678 $header = pack("vv", $record, $length);
1679 $data = pack("vvv", -1, 0, strlen($chunk));
1680 $this->_append($header.$data.$chunk);
1681 }
1682 return(0);
1683 }
1684
1702 function writeBlank($row, $col, $format)
1703 {
1704 // Don't write a blank cell unless it has a format
1705 if (!$format) {
1706 return(0);
1707 }
1708
1709 $record = 0x0201; // Record identifier
1710 $length = 0x0006; // Number of bytes to follow
1711 $xf = $this->_XF($format); // The cell format
1712
1713 // Check that row and col are valid and store max and min values
1714 if ($row >= $this->_xls_rowmax) {
1715 return(-2);
1716 }
1717 if ($col >= $this->_xls_colmax) {
1718 return(-2);
1719 }
1720 if ($row < $this->_dim_rowmin) {
1721 $this->_dim_rowmin = $row;
1722 }
1723 if ($row > $this->_dim_rowmax) {
1724 $this->_dim_rowmax = $row;
1725 }
1726 if ($col < $this->_dim_colmin) {
1727 $this->_dim_colmin = $col;
1728 }
1729 if ($col > $this->_dim_colmax) {
1730 $this->_dim_colmax = $col;
1731 }
1732
1733 $header = pack("vv", $record, $length);
1734 $data = pack("vvv", $row, $col, $xf);
1735 $this->_append($header . $data);
1736 return 0;
1737 }
1738
1755 function writeFormula($row, $col, $formula, $format = null)
1756 {
1757 $record = 0x0006; // Record identifier
1758
1759 // Excel normally stores the last calculated value of the formula in $num.
1760 // Clearly we are not in a position to calculate this a priori. Instead
1761 // we set $num to zero and set the option flags in $grbit to ensure
1762 // automatic calculation of the formula when the file is opened.
1763 //
1764 $xf = $this->_XF($format); // The cell format
1765 $num = 0x00; // Current value of formula
1766 $grbit = 0x03; // Option flags
1767 $unknown = 0x0000; // Must be zero
1768
1769
1770 // Check that row and col are valid and store max and min values
1771 if ($this->_checkRowCol($row, $col) == false) {
1772 return -2;
1773 }
1774
1775 // Strip the '=' or '@' sign at the beginning of the formula string
1776 if (preg_match("/^=/", $formula)) {
1777 $formula = preg_replace("/(^=)/", "", $formula);
1778 } elseif (preg_match("/^@/", $formula)) {
1779 $formula = preg_replace("/(^@)/", "", $formula);
1780 } else {
1781 // Error handling
1782 $this->writeString($row, $col, 'Unrecognised character for formula');
1783 return -1;
1784 }
1785
1786 // Parse the formula using the parser in Parser.php
1787 $error = $this->_parser->parse($formula);
1788 if ($this->isError($error)) {
1789 $this->writeString($row, $col, $error->getMessage());
1790 return -1;
1791 }
1792
1793 $formula = $this->_parser->toReversePolish();
1794 if ($this->isError($formula)) {
1795 $this->writeString($row, $col, $formula->getMessage());
1796 return -1;
1797 }
1798
1799 $formlen = strlen($formula); // Length of the binary string
1800 $length = 0x16 + $formlen; // Length of the record data
1801
1802 $header = pack("vv", $record, $length);
1803 $data = pack("vvvdvVv", $row, $col, $xf, $num,
1804 $grbit, $unknown, $formlen);
1805
1806 $this->_append($header . $data . $formula);
1807 return 0;
1808 }
1809
1833 function writeUrl($row, $col, $url, $string = '', $format = null)
1834 {
1835 // Add start row and col to arg list
1836 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
1837 }
1838
1857 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
1858 {
1859
1860 // Check for internal/external sheet links or default to web link
1861 if (preg_match('[^internal:]', $url)) {
1862 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
1863 }
1864 if (preg_match('[^external:]', $url)) {
1865 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
1866 }
1867 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
1868 }
1869
1870
1887 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
1888 {
1889 $record = 0x01B8; // Record identifier
1890 $length = 0x00000; // Bytes to follow
1891
1892 if (!$format) {
1893 $format = $this->_url_format;
1894 }
1895
1896 // Write the visible label using the writeString() method.
1897 if ($str == '') {
1898 $str = $url;
1899 }
1900 $str_error = $this->writeString($row1, $col1, $str, $format);
1901 if (($str_error == -2) || ($str_error == -3)) {
1902 return $str_error;
1903 }
1904
1905 // Pack the undocumented parts of the hyperlink stream
1906 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1907 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
1908
1909 // Pack the option flags
1910 $options = pack("V", 0x03);
1911
1912 // Convert URL to a null terminated wchar string
1913 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1914 $url = $url . "\0\0\0";
1915
1916 // Pack the length of the URL
1917 $url_len = pack("V", strlen($url));
1918
1919 // Calculate the data length
1920 $length = 0x34 + strlen($url);
1921
1922 // Pack the header data
1923 $header = pack("vv", $record, $length);
1924 $data = pack("vvvv", $row1, $row2, $col1, $col2);
1925
1926 // Write the packed data
1927 $this->_append($header . $data .
1929 $unknown2 . $url_len . $url);
1930 return($str_error);
1931 }
1932
1947 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
1948 {
1949 $record = 0x01B8; // Record identifier
1950 $length = 0x00000; // Bytes to follow
1951
1952 if (!$format) {
1953 $format = $this->_url_format;
1954 }
1955
1956 // Strip URL type
1957 $url = preg_replace('/^internal:/', '', $url);
1958
1959 // Write the visible label
1960 if ($str == '') {
1961 $str = $url;
1962 }
1963 $str_error = $this->writeString($row1, $col1, $str, $format);
1964 if (($str_error == -2) || ($str_error == -3)) {
1965 return $str_error;
1966 }
1967
1968 // Pack the undocumented parts of the hyperlink stream
1969 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1970
1971 // Pack the option flags
1972 $options = pack("V", 0x08);
1973
1974 // Convert the URL type and to a null terminated wchar string
1975 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1976 $url = $url . "\0\0\0";
1977
1978 // Pack the length of the URL as chars (not wchars)
1979 $url_len = pack("V", floor(strlen($url)/2));
1980
1981 // Calculate the data length
1982 $length = 0x24 + strlen($url);
1983
1984 // Pack the header data
1985 $header = pack("vv", $record, $length);
1986 $data = pack("vvvv", $row1, $row2, $col1, $col2);
1987
1988 // Write the packed data
1989 $this->_append($header . $data .
1991 $url_len . $url);
1992 return($str_error);
1993 }
1994
2013 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
2014 {
2015 // Network drives are different. We will handle them separately
2016 // MS/Novell network drives and shares start with \\
2017 if (preg_match('[^external:\\\\]', $url)) {
2018 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
2019 }
2020
2021 $record = 0x01B8; // Record identifier
2022 $length = 0x00000; // Bytes to follow
2023
2024 if (!$format) {
2025 $format = $this->_url_format;
2026 }
2027
2028 // Strip URL type and change Unix dir separator to Dos style (if needed)
2029 //
2030 $url = preg_replace('/^external:/', '', $url);
2031 $url = preg_replace('/\//', "\\", $url);
2032
2033 // Write the visible label
2034 if ($str == '') {
2035 $str = preg_replace('/\#/', ' - ', $url);
2036 }
2037 $str_error = $this->writeString($row1, $col1, $str, $format);
2038 if (($str_error == -2) or ($str_error == -3)) {
2039 return $str_error;
2040 }
2041
2042 // Determine if the link is relative or absolute:
2043 // relative if link contains no dir separator, "somefile.xls"
2044 // relative if link starts with up-dir, "..\..\somefile.xls"
2045 // otherwise, absolute
2046
2047 $absolute = 0x02; // Bit mask
2048 if (!preg_match("/\\\/", $url)) {
2049 $absolute = 0x00;
2050 }
2051 if (preg_match("/^\.\.\\\/", $url)) {
2052 $absolute = 0x00;
2053 }
2055
2056 // Determine if the link contains a sheet reference and change some of the
2057 // parameters accordingly.
2058 // Split the dir name and sheet name (if it exists)
2059 /*if (preg_match("/\#/", $url)) {
2060 list($dir_long, $sheet) = split("\#", $url);
2061 } else {
2062 $dir_long = $url;
2063 }
2064
2065 if (isset($sheet)) {
2066 $link_type |= 0x08;
2067 $sheet_len = pack("V", strlen($sheet) + 0x01);
2068 $sheet = join("\0", split('', $sheet));
2069 $sheet .= "\0\0\0";
2070 } else {
2071 $sheet_len = '';
2072 $sheet = '';
2073 }*/
2075 if (preg_match("/\#/", $url)) {
2076 $link_type |= 0x08;
2077 }
2078
2079
2080
2081 // Pack the link type
2083
2084 // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
2085 $up_count = preg_match_all("/\.\.\\\/", $dir_long, $useless);
2086 $up_count = pack("v", $up_count);
2087
2088 // Store the short dos dir name (null terminated)
2089 $dir_short = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
2090
2091 // Store the long dir name as a wchar string (non-null terminated)
2092 //$dir_long = join("\0", split('', $dir_long));
2093 $dir_long = $dir_long . "\0";
2094
2095 // Pack the lengths of the dir strings
2096 $dir_short_len = pack("V", strlen($dir_short) );
2097 $dir_long_len = pack("V", strlen($dir_long) );
2098 $stream_len = pack("V", 0);//strlen($dir_long) + 0x06);
2099
2100 // Pack the undocumented parts of the hyperlink stream
2101 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' );
2102 $unknown2 = pack("H*",'0303000000000000C000000000000046' );
2103 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
2104 $unknown4 = pack("v", 0x03 );
2105
2106 // Pack the main data stream
2107 $data = pack("vvvv", $row1, $row2, $col1, $col2) .
2108 $unknown1 .
2109 $link_type .
2110 $unknown2 .
2111 $up_count .
2113 $dir_short .
2114 $unknown3 .
2115 $stream_len ;/*.
2116 $dir_long_len .
2117 $unknown4 .
2118 $dir_long .
2119 $sheet_len .
2120 $sheet ;*/
2121
2122 // Pack the header data
2123 $length = strlen($data);
2124 $header = pack("vv", $record, $length);
2125
2126 // Write the packed data
2127 $this->_append($header. $data);
2128 return($str_error);
2129 }
2130
2131
2143 function setRow($row, $height, $format = null, $hidden = false, $level = 0)
2144 {
2145 $record = 0x0208; // Record identifier
2146 $length = 0x0010; // Number of bytes to follow
2147
2148 $colMic = 0x0000; // First defined column
2149 $colMac = 0x0000; // Last defined column
2150 $irwMac = 0x0000; // Used by Excel to optimise loading
2151 $reserved = 0x0000; // Reserved
2152 $grbit = 0x0000; // Option flags
2153 $ixfe = $this->_XF($format); // XF index
2154
2155 // set _row_sizes so _sizeRow() can use it
2156 $this->_row_sizes[$row] = $height;
2157
2158 // Use setRow($row, null, $XF) to set XF format without setting height
2159 if ($height != null) {
2160 $miyRw = $height * 20; // row height
2161 } else {
2162 $miyRw = 0xff; // default row height is 256
2163 }
2164
2165 $level = max(0, min($level, 7)); // level should be between 0 and 7
2166 $this->_outline_row_level = max($level, $this->_outline_row_level);
2167
2168
2169 // Set the options flags. fUnsynced is used to show that the font and row
2170 // heights are not compatible. This is usually the case for WriteExcel.
2171 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
2172 // is collapsed. Instead it is used to indicate that the previous row is
2173 // collapsed. The zero height flag, 0x20, is used to collapse a row.
2174
2175 $grbit |= $level;
2176 if ($hidden) {
2177 $grbit |= 0x0020;
2178 }
2179 $grbit |= 0x0040; // fUnsynced
2180 if ($format) {
2181 $grbit |= 0x0080;
2182 }
2183 $grbit |= 0x0100;
2184
2185 $header = pack("vv", $record, $length);
2186 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
2187 $irwMac,$reserved, $grbit, $ixfe);
2188 $this->_append($header.$data);
2189 }
2190
2197 {
2198 $record = 0x0200; // Record identifier
2199 $row_min = $this->_dim_rowmin; // First row
2200 $row_max = $this->_dim_rowmax + 1; // Last row plus 1
2201 $col_min = $this->_dim_colmin; // First column
2202 $col_max = $this->_dim_colmax + 1; // Last column plus 1
2203 $reserved = 0x0000; // Reserved by Excel
2204
2205 if ($this->_BIFF_version == 0x0500) {
2206 $length = 0x000A; // Number of bytes to follow
2207 $data = pack("vvvvv", $row_min, $row_max,
2208 $col_min, $col_max, $reserved);
2209 } elseif ($this->_BIFF_version == 0x0600) {
2210 $length = 0x000E;
2211 $data = pack("VVvvv", $row_min, $row_max,
2212 $col_min, $col_max, $reserved);
2213 }
2214 $header = pack("vv", $record, $length);
2215 $this->_prepend($header.$data);
2216 }
2217
2223 function _storeWindow2()
2224 {
2225 $record = 0x023E; // Record identifier
2226 if ($this->_BIFF_version == 0x0500) {
2227 $length = 0x000A; // Number of bytes to follow
2228 } elseif ($this->_BIFF_version == 0x0600) {
2229 $length = 0x0012;
2230 }
2231
2232 $grbit = 0x00B6; // Option flags
2233 $rwTop = 0x0000; // Top row visible in window
2234 $colLeft = 0x0000; // Leftmost column visible in window
2235
2236
2237 // The options flags that comprise $grbit
2238 $fDspFmla = 0; // 0 - bit
2239 $fDspGrid = $this->_screen_gridlines; // 1
2240 $fDspRwCol = 1; // 2
2241 $fFrozen = $this->_frozen; // 3
2242 $fDspZeros = 1; // 4
2243 $fDefaultHdr = 1; // 5
2244 $fArabic = 0; // 6
2245 $fDspGuts = $this->_outline_on; // 7
2246 $fFrozenNoSplit = 0; // 0 - bit
2247 $fSelected = $this->selected; // 1
2248 $fPaged = 1; // 2
2249
2250 $grbit = $fDspFmla;
2251 $grbit |= $fDspGrid << 1;
2252 $grbit |= $fDspRwCol << 2;
2253 $grbit |= $fFrozen << 3;
2254 $grbit |= $fDspZeros << 4;
2255 $grbit |= $fDefaultHdr << 5;
2256 $grbit |= $fArabic << 6;
2257 $grbit |= $fDspGuts << 7;
2258 $grbit |= $fFrozenNoSplit << 8;
2259 $grbit |= $fSelected << 9;
2260 $grbit |= $fPaged << 10;
2261
2262 $header = pack("vv", $record, $length);
2263 $data = pack("vvv", $grbit, $rwTop, $colLeft);
2264 // FIXME !!!
2265 if ($this->_BIFF_version == 0x0500) {
2266 $rgbHdr = 0x00000000; // Row/column heading and gridline color
2267 $data .= pack("V", $rgbHdr);
2268 } elseif ($this->_BIFF_version == 0x0600) {
2269 $rgbHdr = 0x0040; // Row/column heading and gridline color index
2270 $zoom_factor_page_break = 0x0000;
2271 $zoom_factor_normal = 0x0000;
2272 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
2273 }
2274 $this->_append($header.$data);
2275 }
2276
2282 function _storeDefcol()
2283 {
2284 $record = 0x0055; // Record identifier
2285 $length = 0x0002; // Number of bytes to follow
2286 $colwidth = 0x0008; // Default column width
2287
2288 $header = pack("vv", $record, $length);
2289 $data = pack("v", $colwidth);
2290 $this->_prepend($header . $data);
2291 }
2292
2308 function _storeColinfo($col_array)
2309 {
2310 if (isset($col_array[0])) {
2311 $colFirst = $col_array[0];
2312 }
2313 if (isset($col_array[1])) {
2314 $colLast = $col_array[1];
2315 }
2316 if (isset($col_array[2])) {
2317 $coldx = $col_array[2];
2318 } else {
2319 $coldx = 8.43;
2320 }
2321 if (isset($col_array[3])) {
2322 $format = $col_array[3];
2323 } else {
2324 $format = 0;
2325 }
2326 if (isset($col_array[4])) {
2327 $grbit = $col_array[4];
2328 } else {
2329 $grbit = 0;
2330 }
2331 if (isset($col_array[5])) {
2332 $level = $col_array[5];
2333 } else {
2334 $level = 0;
2335 }
2336 $record = 0x007D; // Record identifier
2337 $length = 0x000B; // Number of bytes to follow
2338
2339 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !?
2340 $coldx *= 256; // Convert to units of 1/256 of a char
2341
2342 $ixfe = $this->_XF($format);
2343 $reserved = 0x00; // Reserved
2344
2345 $level = max(0, min($level, 7));
2346 $grbit |= $level << 8;
2347
2348 $header = pack("vv", $record, $length);
2349 $data = pack("vvvvvC", $colFirst, $colLast, $coldx,
2350 $ixfe, $grbit, $reserved);
2351 $this->_prepend($header.$data);
2352 }
2353
2361 function _storeSelection($array)
2362 {
2363 list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
2364 $record = 0x001D; // Record identifier
2365 $length = 0x000F; // Number of bytes to follow
2366
2367 $pnn = $this->_active_pane; // Pane position
2368 $rwAct = $rwFirst; // Active row
2369 $colAct = $colFirst; // Active column
2370 $irefAct = 0; // Active cell ref
2371 $cref = 1; // Number of refs
2372
2373 if (!isset($rwLast)) {
2374 $rwLast = $rwFirst; // Last row in reference
2375 }
2376 if (!isset($colLast)) {
2377 $colLast = $colFirst; // Last col in reference
2378 }
2379
2380 // Swap last row/col for first row/col as necessary
2381 if ($rwFirst > $rwLast) {
2382 list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
2383 }
2384
2385 if ($colFirst > $colLast) {
2386 list($colFirst, $colLast) = array($colLast, $colFirst);
2387 }
2388
2389 $header = pack("vv", $record, $length);
2390 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct,
2391 $irefAct, $cref,
2392 $rwFirst, $rwLast,
2393 $colFirst, $colLast);
2394 $this->_append($header . $data);
2395 }
2396
2403 {
2404 // if there are no merged cell ranges set, return
2405 if (count($this->_merged_ranges) == 0) {
2406 return;
2407 }
2408 $record = 0x00E5;
2409 $length = 2 + count($this->_merged_ranges) * 8;
2410
2411 $header = pack('vv', $record, $length);
2412 $data = pack('v', count($this->_merged_ranges));
2413 foreach ($this->_merged_ranges as $range) {
2414 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
2415 }
2416 $this->_append($header . $data);
2417 }
2418
2432 function _storeExterncount($count)
2433 {
2434 $record = 0x0016; // Record identifier
2435 $length = 0x0002; // Number of bytes to follow
2436
2437 $header = pack("vv", $record, $length);
2438 $data = pack("v", $count);
2439 $this->_prepend($header . $data);
2440 }
2441
2451 function _storeExternsheet($sheetname)
2452 {
2453 $record = 0x0017; // Record identifier
2454
2455 // References to the current sheet are encoded differently to references to
2456 // external sheets.
2457 //
2458 if ($this->name == $sheetname) {
2459 $sheetname = '';
2460 $length = 0x02; // The following 2 bytes
2461 $cch = 1; // The following byte
2462 $rgch = 0x02; // Self reference
2463 } else {
2464 $length = 0x02 + strlen($sheetname);
2465 $cch = strlen($sheetname);
2466 $rgch = 0x03; // Reference to a sheet in the current workbook
2467 }
2468
2469 $header = pack("vv", $record, $length);
2470 $data = pack("CC", $cch, $rgch);
2471 $this->_prepend($header . $data . $sheetname);
2472 }
2473
2488 function _storePanes($panes)
2489 {
2490 $y = $panes[0];
2491 $x = $panes[1];
2492 $rwTop = $panes[2];
2493 $colLeft = $panes[3];
2494 if (count($panes) > 4) { // if Active pane was received
2495 $pnnAct = $panes[4];
2496 } else {
2497 $pnnAct = null;
2498 }
2499 $record = 0x0041; // Record identifier
2500 $length = 0x000A; // Number of bytes to follow
2501
2502 // Code specific to frozen or thawed panes.
2503 if ($this->_frozen) {
2504 // Set default values for $rwTop and $colLeft
2505 if (!isset($rwTop)) {
2506 $rwTop = $y;
2507 }
2508 if (!isset($colLeft)) {
2509 $colLeft = $x;
2510 }
2511 } else {
2512 // Set default values for $rwTop and $colLeft
2513 if (!isset($rwTop)) {
2514 $rwTop = 0;
2515 }
2516 if (!isset($colLeft)) {
2517 $colLeft = 0;
2518 }
2519
2520 // Convert Excel's row and column units to the internal units.
2521 // The default row height is 12.75
2522 // The default column width is 8.43
2523 // The following slope and intersection values were interpolated.
2524 //
2525 $y = 20*$y + 255;
2526 $x = 113.879*$x + 390;
2527 }
2528
2529
2530 // Determine which pane should be active. There is also the undocumented
2531 // option to override this should it be necessary: may be removed later.
2532 //
2533 if (!isset($pnnAct)) {
2534 if ($x != 0 && $y != 0) {
2535 $pnnAct = 0; // Bottom right
2536 }
2537 if ($x != 0 && $y == 0) {
2538 $pnnAct = 1; // Top right
2539 }
2540 if ($x == 0 && $y != 0) {
2541 $pnnAct = 2; // Bottom left
2542 }
2543 if ($x == 0 && $y == 0) {
2544 $pnnAct = 3; // Top left
2545 }
2546 }
2547
2548 $this->_active_pane = $pnnAct; // Used in _storeSelection
2549
2550 $header = pack("vv", $record, $length);
2551 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
2552 $this->_append($header . $data);
2553 }
2554
2560 function _storeSetup()
2561 {
2562 $record = 0x00A1; // Record identifier
2563 $length = 0x0022; // Number of bytes to follow
2564
2565 $iPaperSize = $this->_paper_size; // Paper size
2566 $iScale = $this->_print_scale; // Print scaling factor
2567 $iPageStart = 0x01; // Starting page number
2568 $iFitWidth = $this->_fit_width; // Fit to number of pages wide
2569 $iFitHeight = $this->_fit_height; // Fit to number of pages high
2570 $grbit = 0x00; // Option flags
2571 $iRes = 0x0258; // Print resolution
2572 $iVRes = 0x0258; // Vertical print resolution
2573 $numHdr = $this->_margin_head; // Header Margin
2574 $numFtr = $this->_margin_foot; // Footer Margin
2575 $iCopies = 0x01; // Number of copies
2576
2577 $fLeftToRight = 0x0; // Print over then down
2578 $fLandscape = $this->_orientation; // Page orientation
2579 $fNoPls = 0x0; // Setup not read from printer
2580 $fNoColor = 0x0; // Print black and white
2581 $fDraft = 0x0; // Print draft quality
2582 $fNotes = 0x0; // Print notes
2583 $fNoOrient = 0x0; // Orientation not set
2584 $fUsePage = 0x0; // Use custom starting page
2585
2586 $grbit = $fLeftToRight;
2587 $grbit |= $fLandscape << 1;
2588 $grbit |= $fNoPls << 2;
2589 $grbit |= $fNoColor << 3;
2590 $grbit |= $fDraft << 4;
2591 $grbit |= $fNotes << 5;
2592 $grbit |= $fNoOrient << 6;
2593 $grbit |= $fUsePage << 7;
2594
2595 $numHdr = pack("d", $numHdr);
2596 $numFtr = pack("d", $numFtr);
2597 if ($this->_byte_order) { // if it's Big Endian
2598 $numHdr = strrev($numHdr);
2599 $numFtr = strrev($numFtr);
2600 }
2601
2602 $header = pack("vv", $record, $length);
2603 $data1 = pack("vvvvvvvv", $iPaperSize,
2604 $iScale,
2605 $iPageStart,
2606 $iFitWidth,
2607 $iFitHeight,
2608 $grbit,
2609 $iRes,
2610 $iVRes);
2611 $data2 = $numHdr.$numFtr;
2612 $data3 = pack("v", $iCopies);
2613 $this->_prepend($header . $data1 . $data2 . $data3);
2614 }
2615
2621 function _storeHeader()
2622 {
2623 $record = 0x0014; // Record identifier
2624
2625 $str = $this->_header; // header string
2626 $cch = strlen($str); // Length of header string
2627 if ($this->_BIFF_version == 0x0600) {
2628 $encoding = 0x0; // TODO: Unicode support
2629 $length = 3 + $cch; // Bytes to follow
2630 } else {
2631 $length = 1 + $cch; // Bytes to follow
2632 }
2633
2634 $header = pack("vv", $record, $length);
2635 if ($this->_BIFF_version == 0x0600) {
2636 $data = pack("vC", $cch, $encoding);
2637 } else {
2638 $data = pack("C", $cch);
2639 }
2640
2641 $this->_prepend($header.$data.$str);
2642 }
2643
2649 function _storeFooter()
2650 {
2651 $record = 0x0015; // Record identifier
2652
2653 $str = $this->_footer; // Footer string
2654 $cch = strlen($str); // Length of footer string
2655 if ($this->_BIFF_version == 0x0600) {
2656 $encoding = 0x0; // TODO: Unicode support
2657 $length = 3 + $cch; // Bytes to follow
2658 } else {
2659 $length = 1 + $cch;
2660 }
2661
2662 $header = pack("vv", $record, $length);
2663 if ($this->_BIFF_version == 0x0600) {
2664 $data = pack("vC", $cch, $encoding);
2665 } else {
2666 $data = pack("C", $cch);
2667 }
2668
2669 $this->_prepend($header . $data . $str);
2670 }
2671
2677 function _storeHcenter()
2678 {
2679 $record = 0x0083; // Record identifier
2680 $length = 0x0002; // Bytes to follow
2681
2682 $fHCenter = $this->_hcenter; // Horizontal centering
2683
2684 $header = pack("vv", $record, $length);
2685 $data = pack("v", $fHCenter);
2686
2687 $this->_prepend($header.$data);
2688 }
2689
2695 function _storeVcenter()
2696 {
2697 $record = 0x0084; // Record identifier
2698 $length = 0x0002; // Bytes to follow
2699
2700 $fVCenter = $this->_vcenter; // Horizontal centering
2701
2702 $header = pack("vv", $record, $length);
2703 $data = pack("v", $fVCenter);
2704 $this->_prepend($header . $data);
2705 }
2706
2713 {
2714 $record = 0x0026; // Record identifier
2715 $length = 0x0008; // Bytes to follow
2716
2717 $margin = $this->_margin_left; // Margin in inches
2718
2719 $header = pack("vv", $record, $length);
2720 $data = pack("d", $margin);
2721 if ($this->_byte_order) { // if it's Big Endian
2722 $data = strrev($data);
2723 }
2724
2725 $this->_prepend($header . $data);
2726 }
2727
2734 {
2735 $record = 0x0027; // Record identifier
2736 $length = 0x0008; // Bytes to follow
2737
2738 $margin = $this->_margin_right; // Margin in inches
2739
2740 $header = pack("vv", $record, $length);
2741 $data = pack("d", $margin);
2742 if ($this->_byte_order) { // if it's Big Endian
2743 $data = strrev($data);
2744 }
2745
2746 $this->_prepend($header . $data);
2747 }
2748
2755 {
2756 $record = 0x0028; // Record identifier
2757 $length = 0x0008; // Bytes to follow
2758
2759 $margin = $this->_margin_top; // Margin in inches
2760
2761 $header = pack("vv", $record, $length);
2762 $data = pack("d", $margin);
2763 if ($this->_byte_order) { // if it's Big Endian
2764 $data = strrev($data);
2765 }
2766
2767 $this->_prepend($header . $data);
2768 }
2769
2776 {
2777 $record = 0x0029; // Record identifier
2778 $length = 0x0008; // Bytes to follow
2779
2780 $margin = $this->_margin_bottom; // Margin in inches
2781
2782 $header = pack("vv", $record, $length);
2783 $data = pack("d", $margin);
2784 if ($this->_byte_order) { // if it's Big Endian
2785 $data = strrev($data);
2786 }
2787
2788 $this->_prepend($header . $data);
2789 }
2790
2802 function mergeCells($first_row, $first_col, $last_row, $last_col)
2803 {
2804 $record = 0x00E5; // Record identifier
2805 $length = 0x000A; // Bytes to follow
2806 $cref = 1; // Number of refs
2807
2808 // Swap last row/col for first row/col as necessary
2809 if ($first_row > $last_row) {
2810 list($first_row, $last_row) = array($last_row, $first_row);
2811 }
2812
2813 if ($first_col > $last_col) {
2814 list($first_col, $last_col) = array($last_col, $first_col);
2815 }
2816
2817 $header = pack("vv", $record, $length);
2818 $data = pack("vvvvv", $cref, $first_row, $last_row,
2819 $first_col, $last_col);
2820
2821 $this->_append($header.$data);
2822 }
2823
2830 {
2831 $record = 0x002a; // Record identifier
2832 $length = 0x0002; // Bytes to follow
2833
2834 $fPrintRwCol = $this->_print_headers; // Boolean flag
2835
2836 $header = pack("vv", $record, $length);
2837 $data = pack("v", $fPrintRwCol);
2838 $this->_prepend($header . $data);
2839 }
2840
2848 {
2849 $record = 0x002b; // Record identifier
2850 $length = 0x0002; // Bytes to follow
2851
2852 $fPrintGrid = $this->_print_gridlines; // Boolean flag
2853
2854 $header = pack("vv", $record, $length);
2855 $data = pack("v", $fPrintGrid);
2856 $this->_prepend($header . $data);
2857 }
2858
2865 function _storeGridset()
2866 {
2867 $record = 0x0082; // Record identifier
2868 $length = 0x0002; // Bytes to follow
2869
2870 $fGridSet = !($this->_print_gridlines); // Boolean flag
2871
2872 $header = pack("vv", $record, $length);
2873 $data = pack("v", $fGridSet);
2874 $this->_prepend($header . $data);
2875 }
2876
2885 function _storeGuts()
2886 {
2887 $record = 0x0080; // Record identifier
2888 $length = 0x0008; // Bytes to follow
2889
2890 $dxRwGut = 0x0000; // Size of row gutter
2891 $dxColGut = 0x0000; // Size of col gutter
2892
2893 $row_level = $this->_outline_row_level;
2894 $col_level = 0;
2895
2896 // Calculate the maximum column outline level. The equivalent calculation
2897 // for the row outline level is carried out in setRow().
2898 $colcount = count($this->_colinfo);
2899 for ($i = 0; $i < $colcount; $i++) {
2900 // Skip cols without outline level info.
2901 if (count($this->_colinfo[$i]) >= 6) {
2902 $col_level = max($this->_colinfo[$i][5], $col_level);
2903 }
2904 }
2905
2906 // Set the limits for the outline levels (0 <= x <= 7).
2907 $col_level = max(0, min($col_level, 7));
2908
2909 // The displayed level is one greater than the max outline levels
2910 if ($row_level) {
2911 $row_level++;
2912 }
2913 if ($col_level) {
2914 $col_level++;
2915 }
2916
2917 $header = pack("vv", $record, $length);
2918 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
2919
2920 $this->_prepend($header.$data);
2921 }
2922
2923
2930 function _storeWsbool()
2931 {
2932 $record = 0x0081; // Record identifier
2933 $length = 0x0002; // Bytes to follow
2934 $grbit = 0x0000;
2935
2936 // The only option that is of interest is the flag for fit to page. So we
2937 // set all the options in one go.
2938 //
2939 /*if ($this->_fit_page) {
2940 $grbit = 0x05c1;
2941 } else {
2942 $grbit = 0x04c1;
2943 }*/
2944 // Set the option flags
2945 $grbit |= 0x0001; // Auto page breaks visible
2946 if ($this->_outline_style) {
2947 $grbit |= 0x0020; // Auto outline styles
2948 }
2949 if ($this->_outline_below) {
2950 $grbit |= 0x0040; // Outline summary below
2951 }
2952 if ($this->_outline_right) {
2953 $grbit |= 0x0080; // Outline summary right
2954 }
2955 if ($this->_fit_page) {
2956 $grbit |= 0x0100; // Page setup fit to page
2957 }
2958 if ($this->_outline_on) {
2959 $grbit |= 0x0400; // Outline symbols displayed
2960 }
2961
2962 $header = pack("vv", $record, $length);
2963 $data = pack("v", $grbit);
2964 $this->_prepend($header . $data);
2965 }
2966
2972 function _storeHbreak()
2973 {
2974 // Return if the user hasn't specified pagebreaks
2975 if (empty($this->_hbreaks)) {
2976 return;
2977 }
2978
2979 // Sort and filter array of page breaks
2980 $breaks = $this->_hbreaks;
2981 sort($breaks, SORT_NUMERIC);
2982 if ($breaks[0] == 0) { // don't use first break if it's 0
2983 array_shift($breaks);
2984 }
2985
2986 $record = 0x001b; // Record identifier
2987 $cbrk = count($breaks); // Number of page breaks
2988 if ($this->_BIFF_version == 0x0600) {
2989 $length = 2 + 6*$cbrk; // Bytes to follow
2990 } else {
2991 $length = 2 + 2*$cbrk; // Bytes to follow
2992 }
2993
2994 $header = pack("vv", $record, $length);
2995 $data = pack("v", $cbrk);
2996
2997 // Append each page break
2998 foreach ($breaks as $break) {
2999 if ($this->_BIFF_version == 0x0600) {
3000 $data .= pack("vvv", $break, 0x0000, 0x00ff);
3001 } else {
3002 $data .= pack("v", $break);
3003 }
3004 }
3005
3006 $this->_prepend($header.$data);
3007 }
3008
3009
3015 function _storeVbreak()
3016 {
3017 // Return if the user hasn't specified pagebreaks
3018 if (empty($this->_vbreaks)) {
3019 return;
3020 }
3021
3022 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
3023 // It is slightly higher in Excel 97/200, approx. 1026
3024 $breaks = array_slice($this->_vbreaks,0,1000);
3025
3026 // Sort and filter array of page breaks
3027 sort($breaks, SORT_NUMERIC);
3028 if ($breaks[0] == 0) { // don't use first break if it's 0
3029 array_shift($breaks);
3030 }
3031
3032 $record = 0x001a; // Record identifier
3033 $cbrk = count($breaks); // Number of page breaks
3034 if ($this->_BIFF_version == 0x0600) {
3035 $length = 2 + 6*$cbrk; // Bytes to follow
3036 } else {
3037 $length = 2 + 2*$cbrk; // Bytes to follow
3038 }
3039
3040 $header = pack("vv", $record, $length);
3041 $data = pack("v", $cbrk);
3042
3043 // Append each page break
3044 foreach ($breaks as $break) {
3045 if ($this->_BIFF_version == 0x0600) {
3046 $data .= pack("vvv", $break, 0x0000, 0xffff);
3047 } else {
3048 $data .= pack("v", $break);
3049 }
3050 }
3051
3052 $this->_prepend($header . $data);
3053 }
3054
3060 function _storeProtect()
3061 {
3062 // Exit unless sheet protection has been specified
3063 if ($this->_protect == 0) {
3064 return;
3065 }
3066
3067 $record = 0x0012; // Record identifier
3068 $length = 0x0002; // Bytes to follow
3069
3070 $fLock = $this->_protect; // Worksheet is protected
3071
3072 $header = pack("vv", $record, $length);
3073 $data = pack("v", $fLock);
3074
3075 $this->_prepend($header.$data);
3076 }
3077
3084 {
3085 // Exit unless sheet protection and password have been specified
3086 if (($this->_protect == 0) || (!isset($this->_password))) {
3087 return;
3088 }
3089
3090 $record = 0x0013; // Record identifier
3091 $length = 0x0002; // Bytes to follow
3092
3093 $wPassword = $this->_password; // Encoded password
3094
3095 $header = pack("vv", $record, $length);
3096 $data = pack("v", $wPassword);
3097
3098 $this->_prepend($header . $data);
3099 }
3100
3101
3114 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
3115 {
3116 $bitmap_array = $this->_processBitmap($bitmap);
3117 if ($this->isError($bitmap_array)) {
3118 $this->writeString($row, $col, $bitmap_array->getMessage());
3119 return;
3120 }
3121 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
3122
3123 // Scale the frame of the image.
3124 $width *= $scale_x;
3125 $height *= $scale_y;
3126
3127 // Calculate the vertices of the image and write the OBJ record
3128 $this->_positionImage($col, $row, $x, $y, $width, $height);
3129
3130 // Write the IMDATA record to store the bitmap data
3131 $record = 0x007f;
3132 $length = 8 + $size;
3133 $cf = 0x09;
3134 $env = 0x01;
3135 $lcb = $size;
3136
3137 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb);
3138 $this->_append($header.$data);
3139 }
3140
3192 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
3193 {
3194 // Initialise end cell to the same as the start cell
3195 $col_end = $col_start; // Col containing lower right corner of object
3196 $row_end = $row_start; // Row containing bottom right corner of object
3197
3198 // Zero the specified offset if greater than the cell dimensions
3199 if ($x1 >= $this->_sizeCol($col_start)) {
3200 $x1 = 0;
3201 }
3202 if ($y1 >= $this->_sizeRow($row_start)) {
3203 $y1 = 0;
3204 }
3205
3206 $width = $width + $x1 -1;
3207 $height = $height + $y1 -1;
3208
3209 // Subtract the underlying cell widths to find the end cell of the image
3210 while ($width >= $this->_sizeCol($col_end)) {
3211 $width -= $this->_sizeCol($col_end);
3212 $col_end++;
3213 }
3214
3215 // Subtract the underlying cell heights to find the end cell of the image
3216 while ($height >= $this->_sizeRow($row_end)) {
3217 $height -= $this->_sizeRow($row_end);
3218 $row_end++;
3219 }
3220
3221 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
3222 // with zero eight or width.
3223 //
3224 if ($this->_sizeCol($col_start) == 0) {
3225 return;
3226 }
3227 if ($this->_sizeCol($col_end) == 0) {
3228 return;
3229 }
3230 if ($this->_sizeRow($row_start) == 0) {
3231 return;
3232 }
3233 if ($this->_sizeRow($row_end) == 0) {
3234 return;
3235 }
3236
3237 // Convert the pixel values to the percentage value expected by Excel
3238 $x1 = $x1 / $this->_sizeCol($col_start) * 1024;
3239 $y1 = $y1 / $this->_sizeRow($row_start) * 256;
3240 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object
3241 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object
3242
3243 $this->_storeObjPicture($col_start, $x1,
3244 $row_start, $y1,
3245 $col_end, $x2,
3246 $row_end, $y2);
3247 }
3248
3258 function _sizeCol($col)
3259 {
3260 // Look up the cell value to see if it has been changed
3261 if (isset($this->col_sizes[$col])) {
3262 if ($this->col_sizes[$col] == 0) {
3263 return(0);
3264 } else {
3265 return(floor(7 * $this->col_sizes[$col] + 5));
3266 }
3267 } else {
3268 return(64);
3269 }
3270 }
3271
3282 function _sizeRow($row)
3283 {
3284 // Look up the cell value to see if it has been changed
3285 if (isset($this->_row_sizes[$row])) {
3286 if ($this->_row_sizes[$row] == 0) {
3287 return(0);
3288 } else {
3289 return(floor(4/3 * $this->_row_sizes[$row]));
3290 }
3291 } else {
3292 return(17);
3293 }
3294 }
3295
3310 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
3311 {
3312 $record = 0x005d; // Record identifier
3313 $length = 0x003c; // Bytes to follow
3314
3315 $cObj = 0x0001; // Count of objects in file (set to 1)
3316 $OT = 0x0008; // Object type. 8 = Picture
3317 $id = 0x0001; // Object ID
3318 $grbit = 0x0614; // Option flags
3319
3320 $cbMacro = 0x0000; // Length of FMLA structure
3321 $Reserved1 = 0x0000; // Reserved
3322 $Reserved2 = 0x0000; // Reserved
3323
3324 $icvBack = 0x09; // Background colour
3325 $icvFore = 0x09; // Foreground colour
3326 $fls = 0x00; // Fill pattern
3327 $fAuto = 0x00; // Automatic fill
3328 $icv = 0x08; // Line colour
3329 $lns = 0xff; // Line style
3330 $lnw = 0x01; // Line weight
3331 $fAutoB = 0x00; // Automatic border
3332 $frs = 0x0000; // Frame style
3333 $cf = 0x0009; // Image format, 9 = bitmap
3334 $Reserved3 = 0x0000; // Reserved
3335 $cbPictFmla = 0x0000; // Length of FMLA structure
3336 $Reserved4 = 0x0000; // Reserved
3337 $grbit2 = 0x0001; // Option flags
3338 $Reserved5 = 0x0000; // Reserved
3339
3340
3341 $header = pack("vv", $record, $length);
3342 $data = pack("V", $cObj);
3343 $data .= pack("v", $OT);
3344 $data .= pack("v", $id);
3345 $data .= pack("v", $grbit);
3346 $data .= pack("v", $colL);
3347 $data .= pack("v", $dxL);
3348 $data .= pack("v", $rwT);
3349 $data .= pack("v", $dyT);
3350 $data .= pack("v", $colR);
3351 $data .= pack("v", $dxR);
3352 $data .= pack("v", $rwB);
3353 $data .= pack("v", $dyB);
3354 $data .= pack("v", $cbMacro);
3355 $data .= pack("V", $Reserved1);
3356 $data .= pack("v", $Reserved2);
3357 $data .= pack("C", $icvBack);
3358 $data .= pack("C", $icvFore);
3359 $data .= pack("C", $fls);
3360 $data .= pack("C", $fAuto);
3361 $data .= pack("C", $icv);
3362 $data .= pack("C", $lns);
3363 $data .= pack("C", $lnw);
3364 $data .= pack("C", $fAutoB);
3365 $data .= pack("v", $frs);
3366 $data .= pack("V", $cf);
3367 $data .= pack("v", $Reserved3);
3368 $data .= pack("v", $cbPictFmla);
3369 $data .= pack("v", $Reserved4);
3370 $data .= pack("v", $grbit2);
3371 $data .= pack("V", $Reserved5);
3372
3373 $this->_append($header . $data);
3374 }
3375
3385 function _processBitmap($bitmap)
3386 {
3387 // Open file.
3388 $bmp_fd = @fopen($bitmap,"rb");
3389 if (!$bmp_fd) {
3390 $this->raiseError("Couldn't import $bitmap");
3391 }
3392
3393 // Slurp the file into a string.
3394 $data = fread($bmp_fd, filesize($bitmap));
3395
3396 // Check that the file is big enough to be a bitmap.
3397 if (strlen($data) <= 0x36) {
3398 $this->raiseError("$bitmap doesn't contain enough data.\n");
3399 }
3400
3401 // The first 2 bytes are used to identify the bitmap.
3402 $identity = unpack("A2ident", $data);
3403 if ($identity['ident'] != "BM") {
3404 $this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n");
3405 }
3406
3407 // Remove bitmap data: ID.
3408 $data = substr($data, 2);
3409
3410 // Read and remove the bitmap size. This is more reliable than reading
3411 // the data size at offset 0x22.
3412 //
3413 $size_array = unpack("Vsa", substr($data, 0, 4));
3414 $size = $size_array['sa'];
3415 $data = substr($data, 4);
3416 $size -= 0x36; // Subtract size of bitmap header.
3417 $size += 0x0C; // Add size of BIFF header.
3418
3419 // Remove bitmap data: reserved, offset, header length.
3420 $data = substr($data, 12);
3421
3422 // Read and remove the bitmap width and height. Verify the sizes.
3423 $width_and_height = unpack("V2", substr($data, 0, 8));
3424 $width = $width_and_height[1];
3425 $height = $width_and_height[2];
3426 $data = substr($data, 8);
3427 if ($width > 0xFFFF) {
3428 $this->raiseError("$bitmap: largest image width supported is 65k.\n");
3429 }
3430 if ($height > 0xFFFF) {
3431 $this->raiseError("$bitmap: largest image height supported is 65k.\n");
3432 }
3433
3434 // Read and remove the bitmap planes and bpp data. Verify them.
3435 $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
3436 $data = substr($data, 4);
3437 if ($planes_and_bitcount[2] != 24) { // Bitcount
3438 $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n");
3439 }
3440 if ($planes_and_bitcount[1] != 1) {
3441 $this->raiseError("$bitmap: only 1 plane supported in bitmap image.\n");
3442 }
3443
3444 // Read and remove the bitmap compression. Verify compression.
3445 $compression = unpack("Vcomp", substr($data, 0, 4));
3446 $data = substr($data, 4);
3447
3448 //$compression = 0;
3449 if ($compression['comp'] != 0) {
3450 $this->raiseError("$bitmap: compression not supported in bitmap image.\n");
3451 }
3452
3453 // Remove bitmap data: data size, hres, vres, colours, imp. colours.
3454 $data = substr($data, 20);
3455
3456 // Add the BITMAPCOREHEADER data
3457 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
3458 $data = $header . $data;
3459
3460 return (array($width, $height, $size, $data));
3461 }
3462
3469 function _storeZoom()
3470 {
3471 // If scale is 100 we don't need to write a record
3472 if ($this->_zoom == 100) {
3473 return;
3474 }
3475
3476 $record = 0x00A0; // Record identifier
3477 $length = 0x0004; // Bytes to follow
3478
3479 $header = pack("vv", $record, $length);
3480 $data = pack("vv", $this->_zoom, 100);
3481 $this->_append($header . $data);
3482 }
3483
3487 function setValidation($row1, $col1, $row2, $col2, &$validator)
3488 {
3489 $this->_dv[] = $validator->_getData() .
3490 pack("vvvvv", 1, $row1, $row2, $col1, $col2);
3491 }
3492
3499 {
3500 $record = 0x01b2; // Record identifier
3501 $length = 0x0012; // Bytes to follow
3502
3503 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records
3504 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position
3505 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position
3506 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible
3507
3508 $header = pack('vv', $record, $length);
3509 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
3510 count($this->_dv));
3511 $this->_append($header.$data);
3512
3513 $record = 0x01be; // Record identifier
3514 foreach ($this->_dv as $dv) {
3515 $length = strlen($dv); // Bytes to follow
3516 $header = pack("vv", $record, $length);
3517 $this->_append($header . $dv);
3518 }
3519 }
3520}
3521?>
const PEAR_ERROR_DIE
Definition: PEAR.php:34
$size
Definition: RandomTest.php:79
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279
& raiseError($message=null, $code=null, $mode=null, $options=null, $userinfo=null, $error_class=null, $skipmsg=false)
This method is a wrapper that returns an instance of the configured error class with this object's de...
Definition: PEAR.php:524
_storeBof($type)
Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
Definition: BIFFwriter.php:170
_addContinue($data)
Excel limits the size of BIFF records.
Definition: BIFFwriter.php:219
Spreadsheet_Excel_Writer_BIFFwriter()
Constructor.
Definition: BIFFwriter.php:98
_storeEof()
Writes Excel EOF record to indicate the end of a BIFF stream.
Definition: BIFFwriter.php:199
setMargins_LR($margin)
Set the left and right margins to the same value in inches.
Definition: Worksheet.php:921
_storeHeader()
Store the header caption BIFF record.
Definition: Worksheet.php:2621
_storeColinfo($col_array)
Write BIFF record COLINFO to define column widths.
Definition: Worksheet.php:2308
writeRow($row, $col, $val, $format=null)
Write an array of values as a row.
Definition: Worksheet.php:1200
protect($password)
Set the worksheet protection flag to prevent accidental modification and to hide formulas if the lock...
Definition: Worksheet.php:736
_storeProtect()
Set the Biff PROTECT record to indicate that the worksheet is protected.
Definition: Worksheet.php:3060
setMerge($first_row, $first_col, $last_row, $last_col)
Sets a merged cell range.
Definition: Worksheet.php:682
_storePassword()
Write the worksheet PASSWORD record.
Definition: Worksheet.php:3083
setSelection($first_row, $first_column, $last_row, $last_column)
Set which cell or cells are selected in a worksheet.
Definition: Worksheet.php:774
_storeMarginBottom()
Store the BOTTOMMARGIN BIFF record.
Definition: Worksheet.php:2775
setLandscape()
Set the page orientation as landscape.
Definition: Worksheet.php:828
_storeDimensions()
Writes Excel DIMENSIONS to define the area in which there is data.
Definition: Worksheet.php:2196
_storePanes($panes)
Writes the Excel BIFF PANE record.
Definition: Worksheet.php:2488
setPortrait()
Set the page orientation as portrait.
Definition: Worksheet.php:818
_substituteCellref($cell)
Substitute an Excel cell reference in A1 notation for zero based row and column values in an argument...
Definition: Worksheet.php:1298
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:3114
writeString($row, $col, $str, $format=null)
Write a string to the specified row and column (zero indexed).
Definition: Worksheet.php:1483
printArea($first_row, $first_col, $last_row, $last_col)
Set the area of each worksheet that will be printed.
Definition: Worksheet.php:1026
freezePanes($panes)
Set panes and mark them as frozen.
Definition: Worksheet.php:790
_storeMarginLeft()
Store the LEFTMARGIN BIFF record.
Definition: Worksheet.php:2712
_storeWindow2()
Write BIFF record Window2.
Definition: Worksheet.php:2223
writeUrl($row, $col, $url, $string='', $format=null)
Write a hyperlink.
Definition: Worksheet.php:1833
if(!preg_match("/\\\/", $url)) if(preg_match("/^\.\.\\\/", $url) $link_type)
Definition: Worksheet.php:2054
setColumn($firstcol, $lastcol, $width, $format=null, $hidden=0, $level=0)
Set the width of a single column or a range of columns.
Definition: Worksheet.php:753
Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name, $index, &$activesheet, &$firstsheet, &$str_total, &$str_unique, &$str_table, &$url_format, &$parser, $tmp_dir)
Constructor.
Definition: Worksheet.php:370
_sizeRow($row)
Convert the height of a cell from user's units to pixels.
Definition: Worksheet.php:3282
repeatColumns($first_col, $last_col=null)
Set the columns to repeat at the left hand side of each printed page.
Definition: Worksheet.php:1007
setValidation($row1, $col1, $row2, $col2, &$validator)
FIXME: add comments.
Definition: Worksheet.php:3487
_initialize()
Open a tmp file to store the majority of the Worksheet data.
Definition: Worksheet.php:478
hideScreenGridlines()
Set the option to hide gridlines on the worksheet (as seen on the screen).
Definition: Worksheet.php:1050
_storeHcenter()
Store the horizontal centering HCENTER BIFF record.
Definition: Worksheet.php:2677
setFooter($string, $margin=0.50)
Set the page footer caption and optional margin.
Definition: Worksheet.php:869
_storePrintGridlines()
Write the PRINTGRIDLINES BIFF record.
Definition: Worksheet.php:2847
setPrintScale($scale=100)
Set the scale factor for the printed page.
Definition: Worksheet.php:1135
getData()
Retrieves data from memory in one chunk, or from disk in $buffer sized chunks.
Definition: Worksheet.php:648
_storeWsbool()
Write the WSBOOL BIFF record, mainly for fit-to-page.
Definition: Worksheet.php:2930
setMarginRight($margin=0.75)
Set the right margin in inches.
Definition: Worksheet.php:956
_append($data)
Store Worksheet data in memory using the parent's class append() or to a temporary file,...
Definition: Worksheet.php:1274
setMarginBottom($margin=1.00)
Set the bottom margin in inches.
Definition: Worksheet.php:978
_storePrintHeaders()
Write the PRINTHEADERS BIFF record.
Definition: Worksheet.php:2829
_storeGridset()
Write the GRIDSET BIFF record.
Definition: Worksheet.php:2865
_storeSetup()
Store the page setup SETUP BIFF record.
Definition: Worksheet.php:2560
writeNumber($row, $col, $num, $format=null)
Write a double to the specified row and column (zero indexed).
Definition: Worksheet.php:1430
_XF(&$format)
Returns an index to the XF record in the workbook.
Definition: Worksheet.php:1250
setVPagebreaks($breaks)
Store the vertical page breaks on a worksheet (for printing).
Definition: Worksheet.php:1103
writeNote($row, $col, $note)
Writes a note associated with the cell given by the row and column.
Definition: Worksheet.php:1642
fitToPages($width, $height)
Set the vertical and horizontal number of pages that will define the maximum area printed.
Definition: Worksheet.php:1075
_sizeCol($col)
Convert the width of a cell from user's units to pixels.
Definition: Worksheet.php:3258
_storeHbreak()
Write the HORIZONTALPAGEBREAKS BIFF record.
Definition: Worksheet.php:2972
getName()
Retrieve the worksheet name.
Definition: Worksheet.php:637
_cellToRowcol($cell)
Convert an Excel cell reference in A1 notation to a zero based row and column reference; converts C1 ...
Definition: Worksheet.php:1334
_storeGuts()
Write the GUTS BIFF record.
Definition: Worksheet.php:2885
_storeVcenter()
Store the vertical centering VCENTER BIFF record.
Definition: Worksheet.php:2695
setFirstSheet()
Set this worksheet as the first visible sheet.
Definition: Worksheet.php:723
centerVertically($center=1)
Center the page vertically.
Definition: Worksheet.php:896
write($row, $col, $token, $format=null)
Map to the appropriate write method acording to the token recieved.
Definition: Worksheet.php:1158
printRowColHeaders($print=1)
Set the option to print the row and column headers on the printed page.
Definition: Worksheet.php:1061
select()
Set this worksheet as a selected worksheet, i.e.
Definition: Worksheet.php:698
_storeExterncount($count)
Write BIFF record EXTERNCOUNT to indicate the number of external sheet references in a worksheet.
Definition: Worksheet.php:2432
setHPagebreaks($breaks)
Store the horizontal page breaks on a worksheet (for printing).
Definition: Worksheet.php:1089
_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:3192
setMargins_TB($margin)
Set the top and bottom margins to the same value in inches.
Definition: Worksheet.php:933
if(($str_error==-2) or($str_error==-3)) $absolute
Definition: Worksheet.php:2047
setRow($row, $height, $format=null, $hidden=false, $level=0)
This method is used to set the height and format for a row.
Definition: Worksheet.php:2143
_storeExternsheet($sheetname)
Writes the Excel BIFF EXTERNSHEET record.
Definition: Worksheet.php:2451
_storeMarginTop()
Store the TOPMARGIN BIFF record.
Definition: Worksheet.php:2754
_writeUrlRange($row1, $col1, $row2, $col2, $url, $string='', $format=null)
This is the more general form of writeUrl().
Definition: Worksheet.php:1857
mergeCells($first_row, $first_col, $last_row, $last_col)
Merges the area given by its arguments.
Definition: Worksheet.php:2802
close($sheetnames)
Add data to the beginning of the workbook (note the reverse order) and to the end of the workbook.
Definition: Worksheet.php:518
_processBitmap($bitmap)
Convert a 24 bit bitmap into the modified internal format used by Windows.
Definition: Worksheet.php:3385
hideGridlines()
Set the option to hide gridlines on the printed page.
Definition: Worksheet.php:1040
thawPanes($panes)
Set panes and mark them as unfrozen.
Definition: Worksheet.php:807
writeBlank($row, $col, $format)
Write a blank cell to the specified row and column (zero indexed).
Definition: Worksheet.php:1702
_storeFooter()
Store the footer caption BIFF record.
Definition: Worksheet.php:2649
_storeZoom()
Store the window zoom factor.
Definition: Worksheet.php:3469
setInputEncoding($encoding)
Sets Input Encoding for writing strings.
Definition: Worksheet.php:1534
setMarginTop($margin=1.00)
Set the top margin in inches.
Definition: Worksheet.php:967
_storeMergedCells()
Store the MERGEDCELLS record for all ranges of merged cells.
Definition: Worksheet.php:2402
setZoom($scale=100)
Set the worksheet zoom factor.
Definition: Worksheet.php:1117
_encodePassword($plaintext)
Based on the algorithm provided by Daniel Rentz of OpenOffice.
Definition: Worksheet.php:1365
_writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format=null)
Write links to external directory names such as 'c:\foo.xls', c:\foo.xls::Sheet1!A1',...
Definition: Worksheet.php:2013
_storeMarginRight()
Store the RIGHTMARGIN BIFF record.
Definition: Worksheet.php:2733
writeStringBIFF8($row, $col, $str, $format=null)
Write a string to the specified row and column (zero indexed).
Definition: Worksheet.php:1557
_storeVbreak()
Write the VERTICALPAGEBREAKS BIFF record.
Definition: Worksheet.php:3015
writeFormula($row, $col, $formula, $format=null)
Write a formula to the specified row and column (zero indexed).
Definition: Worksheet.php:1755
writeCol($row, $col, $val, $format=null)
Write an array of values as a column.
Definition: Worksheet.php:1229
repeatRows($first_row, $last_row=null)
Set the rows to repeat at the top of each printed page.
Definition: Worksheet.php:990
_storeSelection($array)
Write BIFF record SELECTION.
Definition: Worksheet.php:2361
_writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format=null)
Used to write http, ftp and mailto hyperlinks.
Definition: Worksheet.php:1887
setMarginLeft($margin=0.75)
Set the left margin in inches.
Definition: Worksheet.php:945
setPaper($size=0)
Set the paper type.
Definition: Worksheet.php:839
centerHorizontally($center=1)
Center the page horinzontally.
Definition: Worksheet.php:885
_writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format=null)
Used to write internal reference hyperlinks such as "Sheet1!A1".
Definition: Worksheet.php:1947
setHeader($string, $margin=0.50)
Set the page header caption and optional margin.
Definition: Worksheet.php:852
_storeDataValidity()
Store the DVAL and DV records.
Definition: Worksheet.php:3498
_storeDefcol()
Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
Definition: Worksheet.php:2282
activate()
Set this worksheet as the active worksheet, i.e.
Definition: Worksheet.php:710
_storeObjPicture($colL, $dxL, $rwT, $dyT, $colR, $dxR, $rwB, $dyB)
Store the OBJ record that precedes an IMDATA record.
Definition: Worksheet.php:3310
setMargins($margin)
Set all the page margins to the same value in inches.
Definition: Worksheet.php:907
setOutline($visible=true, $symbols_below=true, $symbols_right=true, $auto_style=false)
This method sets the properties for outlining and grouping.
Definition: Worksheet.php:1395
_checkRowCol($row, $col)
Check row and col before writing to a cell, and update the sheet's dimensions accordingly.
Definition: Worksheet.php:1610
$y
Definition: example_007.php:83
$x
Definition: example_009.php:98
$header
$data
if(!is_array($argv)) $options