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 35/** 36* Class for generating Excel Spreadsheets 37* 38* @author Xavier Noguer <xnoguer@rezebra.com> 39* @category FileFormats 40* @package Spreadsheet_Excel_Writer 41*/ 42 43class Spreadsheet_Excel_Writer_Worksheet extends Spreadsheet_Excel_Writer_BIFFwriter 44{ 45 /** 46 * Name of the Worksheet 47 * @var string 48 */ 49 var $name; 50 51 /** 52 * Index for the Worksheet 53 * @var integer 54 */ 55 var $index; 56 57 /** 58 * Reference to the (default) Format object for URLs 59 * @var object Format 60 */ 61 var $_url_format; 62 63 /** 64 * Reference to the parser used for parsing formulas 65 * @var object Format 66 */ 67 var $_parser; 68 69 /** 70 * Filehandle to the temporary file for storing data 71 * @var resource 72 */ 73 var $_filehandle; 74 75 /** 76 * Boolean indicating if we are using a temporary file for storing data 77 * @var bool 78 */ 79 var $_using_tmpfile; 80 81 /** 82 * Maximum number of rows for an Excel spreadsheet (BIFF5) 83 * @var integer 84 */ 85 var $_xls_rowmax; 86 87 /** 88 * Maximum number of columns for an Excel spreadsheet (BIFF5) 89 * @var integer 90 */ 91 var $_xls_colmax; 92 93 /** 94 * Maximum number of characters for a string (LABEL record in BIFF5) 95 * @var integer 96 */ 97 var $_xls_strmax; 98 99 /** 100 * First row for the DIMENSIONS record 101 * @var integer 102 * @see _storeDimensions() 103 */ 104 var $_dim_rowmin; 105 106 /** 107 * Last row for the DIMENSIONS record 108 * @var integer 109 * @see _storeDimensions() 110 */ 111 var $_dim_rowmax; 112 113 /** 114 * First column for the DIMENSIONS record 115 * @var integer 116 * @see _storeDimensions() 117 */ 118 var $_dim_colmin; 119 120 /** 121 * Last column for the DIMENSIONS record 122 * @var integer 123 * @see _storeDimensions() 124 */ 125 var $_dim_colmax; 126 127 /** 128 * Array containing format information for columns 129 * @var array 130 */ 131 var $_colinfo; 132 133 /** 134 * Array containing the selected area for the worksheet 135 * @var array 136 */ 137 var $_selection; 138 139 /** 140 * Array containing the panes for the worksheet 141 * @var array 142 */ 143 var $_panes; 144 145 /** 146 * The active pane for the worksheet 147 * @var integer 148 */ 149 var $_active_pane; 150 151 /** 152 * Bit specifying if panes are frozen 153 * @var integer 154 */ 155 var $_frozen; 156 157 /** 158 * Bit specifying if the worksheet is selected 159 * @var integer 160 */ 161 var $selected; 162 163 /** 164 * The paper size (for printing) (DOCUMENT!!!) 165 * @var integer 166 */ 167 var $_paper_size; 168 169 /** 170 * Bit specifying paper orientation (for printing). 0 => landscape, 1 => portrait 171 * @var integer 172 */ 173 var $_orientation; 174 175 /** 176 * The page header caption 177 * @var string 178 */ 179 var $_header; 180 181 /** 182 * The page footer caption 183 * @var string 184 */ 185 var $_footer; 186 187 /** 188 * The horizontal centering value for the page 189 * @var integer 190 */ 191 var $_hcenter; 192 193 /** 194 * The vertical centering value for the page 195 * @var integer 196 */ 197 var $_vcenter; 198 199 /** 200 * The margin for the header 201 * @var float 202 */ 203 var $_margin_head; 204 205 /** 206 * The margin for the footer 207 * @var float 208 */ 209 var $_margin_foot; 210 211 /** 212 * The left margin for the worksheet in inches 213 * @var float 214 */ 215 var $_margin_left; 216 217 /** 218 * The right margin for the worksheet in inches 219 * @var float 220 */ 221 var $_margin_right; 222 223 /** 224 * The top margin for the worksheet in inches 225 * @var float 226 */ 227 var $_margin_top; 228 229 /** 230 * The bottom margin for the worksheet in inches 231 * @var float 232 */ 233 var $_margin_bottom; 234 235 /** 236 * First row to reapeat on each printed page 237 * @var integer 238 */ 239 var $title_rowmin; 240 241 /** 242 * Last row to reapeat on each printed page 243 * @var integer 244 */ 245 var $title_rowmax; 246 247 /** 248 * First column to reapeat on each printed page 249 * @var integer 250 */ 251 var $title_colmin; 252 253 /** 254 * First row of the area to print 255 * @var integer 256 */ 257 var $print_rowmin; 258 259 /** 260 * Last row to of the area to print 261 * @var integer 262 */ 263 var $print_rowmax; 264 265 /** 266 * First column of the area to print 267 * @var integer 268 */ 269 var $print_colmin; 270 271 /** 272 * Last column of the area to print 273 * @var integer 274 */ 275 var $print_colmax; 276 277 /** 278 * Whether to use outline. 279 * @var integer 280 */ 281 var $_outline_on; 282 283 /** 284 * Auto outline styles. 285 * @var bool 286 */ 287 var $_outline_style; 288 289 /** 290 * Whether to have outline summary below. 291 * @var bool 292 */ 293 var $_outline_below; 294 295 /** 296 * Whether to have outline summary at the right. 297 * @var bool 298 */ 299 var $_outline_right; 300 301 /** 302 * Outline row level. 303 * @var integer 304 */ 305 var $_outline_row_level; 306 307 /** 308 * Whether to fit to page when printing or not. 309 * @var bool 310 */ 311 var $_fit_page; 312 313 /** 314 * Number of pages to fit wide 315 * @var integer 316 */ 317 var $_fit_width; 318 319 /** 320 * Number of pages to fit high 321 * @var integer 322 */ 323 var $_fit_height; 324 325 /** 326 * Reference to the total number of strings in the workbook 327 * @var integer 328 */ 329 var $_str_total; 330 331 /** 332 * Reference to the number of unique strings in the workbook 333 * @var integer 334 */ 335 var $_str_unique; 336 337 /** 338 * Reference to the array containing all the unique strings in the workbook 339 * @var array 340 */ 341 var $_str_table; 342 343 /** 344 * Merged cell ranges 345 * @var array 346 */ 347 var $_merged_ranges; 348 349 /** 350 * Constructor 351 * 352 * @param string $name The name of the new worksheet 353 * @param integer $index The index of the new worksheet 354 * @param mixed &$activesheet The current activesheet of the workbook we belong to 355 * @param mixed &$firstsheet The first worksheet in the workbook we belong to 356 * @param mixed &$url_format The default format for hyperlinks 357 * @param mixed &$parser The formula parser created for the Workbook 358 * @access private 359 */ 360 function __construct($BIFF_version, $name, 361 $index, &$activesheet, 362 &$firstsheet, &$str_total, 363 &$str_unique, &$str_table, 364 &$url_format, &$parser) 365 { 366 // It needs to call its parent's constructor explicitly 367 $this->Spreadsheet_Excel_Writer_BIFFwriter(); 368 $this->_BIFF_version = $BIFF_version; 369 $rowmax = 65536; // 16384 in Excel 5 370 $colmax = 256; 371 372 $this->name = $name; 373 $this->index = $index; 374 $this->activesheet = &$activesheet; 375 $this->firstsheet = &$firstsheet; 376 $this->_str_total = &$str_total; 377 $this->_str_unique = &$str_unique; 378 $this->_str_table = &$str_table; 379 $this->_url_format = &$url_format; 380 $this->_parser = &$parser; 381 382 //$this->ext_sheets = array(); 383 $this->_filehandle = ""; 384 $this->_using_tmpfile = true; 385 //$this->fileclosed = 0; 386 //$this->offset = 0; 387 $this->_xls_rowmax = $rowmax; 388 $this->_xls_colmax = $colmax; 389 $this->_xls_strmax = 255; 390 $this->_dim_rowmin = $rowmax + 1; 391 $this->_dim_rowmax = 0; 392 $this->_dim_colmin = $colmax + 1; 393 $this->_dim_colmax = 0; 394 $this->_colinfo = array(); 395 $this->_selection = array(0,0,0,0); 396 $this->_panes = array(); 397 $this->_active_pane = 3; 398 $this->_frozen = 0; 399 $this->selected = 0; 400 401 $this->_paper_size = 0x0; 402 $this->_orientation = 0x1; 403 $this->_header = ''; 404 $this->_footer = ''; 405 $this->_hcenter = 0; 406 $this->_vcenter = 0; 407 $this->_margin_head = 0.50; 408 $this->_margin_foot = 0.50; 409 $this->_margin_left = 0.75; 410 $this->_margin_right = 0.75; 411 $this->_margin_top = 1.00; 412 $this->_margin_bottom = 1.00; 413 414 $this->title_rowmin = NULL; 415 $this->title_rowmax = NULL; 416 $this->title_colmin = NULL; 417 $this->title_colmax = NULL; 418 $this->print_rowmin = NULL; 419 $this->print_rowmax = NULL; 420 $this->print_colmin = NULL; 421 $this->print_colmax = NULL; 422 423 $this->_print_gridlines = 1; 424 $this->_print_headers = 0; 425 426 $this->_fit_page = 0; 427 $this->_fit_width = 0; 428 $this->_fit_height = 0; 429 430 $this->_hbreaks = array(); 431 $this->_vbreaks = array(); 432 433 $this->_protect = 0; 434 $this->_password = NULL; 435 436 $this->col_sizes = array(); 437 $this->row_sizes = array(); 438 439 $this->_zoom = 100; 440 $this->_print_scale = 100; 441 442 $this->_outline_row_level = 0; 443 $this->_outline_style = 0; 444 $this->_outline_below = 1; 445 $this->_outline_right = 1; 446 $this->_outline_on = 1; 447 448 $this->_merged_ranges = array(); 449 450 $this->_dv = array(); 451 452 $this->_initialize(); 453 } 454 455 /** 456 * Open a tmp file to store the majority of the Worksheet data. If this fails, 457 * for example due to write permissions, store the data in memory. This can be 458 * slow for large files. 459 * 460 * @access private 461 */ 462 function _initialize() 463 { 464 // Open tmp file for storing Worksheet data 465 $fh = tmpfile(); 466 if ( $fh) { 467 // Store filehandle 468 $this->_filehandle = $fh; 469 } 470 else { 471 // If tmpfile() fails store data in memory 472 $this->_using_tmpfile = false; 473 } 474 } 475 476 /** 477 * Add data to the beginning of the workbook (note the reverse order) 478 * and to the end of the workbook. 479 * 480 * @access public 481 * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook() 482 * @param array $sheetnames The array of sheetnames from the Workbook this 483 * worksheet belongs to 484 */ 485 function close($sheetnames) 486 { 487 $num_sheets = count($sheetnames); 488 489 /*********************************************** 490 * Prepend in reverse order!! 491 */ 492 493 // Prepend the sheet dimensions 494 $this->_storeDimensions(); 495 496 // Prepend the sheet password 497 $this->_storePassword(); 498 499 // Prepend the sheet protection 500 $this->_storeProtect(); 501 502 // Prepend the page setup 503 $this->_storeSetup(); 504 505 /* FIXME: margins are actually appended */ 506 // Prepend the bottom margin 507 $this->_storeMarginBottom(); 508 509 // Prepend the top margin 510 $this->_storeMarginTop(); 511 512 // Prepend the right margin 513 $this->_storeMarginRight(); 514 515 // Prepend the left margin 516 $this->_storeMarginLeft(); 517 518 // Prepend the page vertical centering 519 $this->_storeVcenter(); 520 521 // Prepend the page horizontal centering 522 $this->_storeHcenter(); 523 524 // Prepend the page footer 525 $this->_storeFooter(); 526 527 // Prepend the page header 528 $this->_storeHeader(); 529 530 // Prepend the vertical page breaks 531 $this->_storeVbreak(); 532 533 // Prepend the horizontal page breaks 534 $this->_storeHbreak(); 535 536 // Prepend WSBOOL 537 $this->_storeWsbool(); 538 539 // Prepend GRIDSET 540 $this->_storeGridset(); 541 542 // Prepend GUTS 543 if ($this->_BIFF_version == 0x0500) { 544 $this->_storeGuts(); 545 } 546 547 // Prepend PRINTGRIDLINES 548 $this->_storePrintGridlines(); 549 550 // Prepend PRINTHEADERS 551 $this->_storePrintHeaders(); 552 553 // Prepend EXTERNSHEET references 554 if ($this->_BIFF_version == 0x0500) { 555 for ($i = $num_sheets; $i > 0; $i--) { 556 $sheetname = $sheetnames[$i-1]; 557 $this->_storeExternsheet($sheetname); 558 } 559 } 560 561 // Prepend the EXTERNCOUNT of external references. 562 if ($this->_BIFF_version == 0x0500) { 563 $this->_storeExterncount($num_sheets); 564 } 565 566 // Prepend the COLINFO records if they exist 567 if (!empty($this->_colinfo)) 568 { 569 foreach($this->_colinfo as $colinfo) { 570 $this->_storeColinfo($colinfo); 571 } 572 $this->_storeDefcol(); 573 } 574 575 // Prepend the BOF record 576 $this->_storeBof(0x0010); 577 578 /* 579 * End of prepend. Read upwards from here. 580 ***********************************************/ 581 582 // Append 583 $this->_storeWindow2(); 584 $this->_storeZoom(); 585 if (!empty($this->_panes)) { 586 $this->_storePanes($this->_panes); 587 } 588 $this->_storeSelection($this->_selection); 589 $this->_storeMergedCells(); 590 /* TODO: add data validity */ 591 /*if ($this->_BIFF_version == 0x0600) { 592 $this->_storeDataValidity(); 593 }*/ 594 $this->_storeEof(); 595 } 596 597 /** 598 * Retrieve the worksheet name. 599 * This is usefull when creating worksheets without a name. 600 * 601 * @access public 602 * @return string The worksheet's name 603 */ 604 function getName() 605 { 606 return $this->name; 607 } 608 609 /** 610 * Retrieves data from memory in one chunk, or from disk in $buffer 611 * sized chunks. 612 * 613 * @return string The data 614 */ 615 function getData() 616 { 617 $buffer = 4096; 618 619 // Return data stored in memory 620 if (isset($this->_data)) 621 { 622 $tmp = $this->_data; 623 unset($this->_data); 624 $fh = $this->_filehandle; 625 if ($this->_using_tmpfile) { 626 fseek($fh, 0); 627 } 628 return $tmp; 629 } 630 // Return data stored on disk 631 if ($this->_using_tmpfile) 632 { 633 if ($tmp = fread($this->_filehandle, $buffer)) { 634 return $tmp; 635 } 636 } 637 638 // No data to return 639 return ''; 640 } 641 642 /** 643 * Sets a merged cell range 644 * 645 * @access public 646 * @param integer $first_row First row of the area to merge 647 * @param integer $first_col First column of the area to merge 648 * @param integer $last_row Last row of the area to merge 649 * @param integer $last_col Last column of the area to merge 650 */ 651 function setMerge($first_row, $first_col, $last_row, $last_col) 652 { 653 if (($last_row < $first_row) or ($last_col < $first_col)) { 654 return; 655 } 656 // don't check rowmin, rowmax, etc... because we don't know when this 657 // is going to be called 658 $this->_merged_ranges[] = array($first_row, $first_col, $last_row, $last_col); 659 } 660 661 /** 662 * Set this worksheet as a selected worksheet, 663 * i.e. the worksheet has its tab highlighted. 664 * 665 * @access public 666 */ 667 function select() 668 { 669 $this->selected = 1; 670 } 671 672 /** 673 * Set this worksheet as the active worksheet, 674 * i.e. the worksheet that is displayed when the workbook is opened. 675 * Also set it as selected. 676 * 677 * @access public 678 */ 679 function activate() 680 { 681 $this->selected = 1; 682 $this->activesheet = $this->index; 683 } 684 685 /** 686 * Set this worksheet as the first visible sheet. 687 * This is necessary when there are a large number of worksheets and the 688 * activated worksheet is not visible on the screen. 689 * 690 * @access public 691 */ 692 function setFirstSheet() 693 { 694 $this->firstsheet = $this->index; 695 } 696 697 /** 698 * Set the worksheet protection flag 699 * to prevent accidental modification and to 700 * hide formulas if the locked and hidden format properties have been set. 701 * 702 * @access public 703 * @param string $password The password to use for protecting the sheet. 704 */ 705 function protect($password) 706 { 707 $this->_protect = 1; 708 $this->_password = $this->_encodePassword($password); 709 } 710 711 /** 712 * Set the width of a single column or a range of columns. 713 * 714 * @access public 715 * @param integer $firstcol first column on the range 716 * @param integer $lastcol last column on the range 717 * @param integer $width width to set 718 * @param mixed $format The optional XF format to apply to the columns 719 * @param integer $hidden The optional hidden atribute 720 * @param integer $level The optional outline level 721 */ 722 function setColumn($firstcol, $lastcol, $width, $format = 0, $hidden = 0, $level = 0) 723 { 724 $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level); 725 726 // Set width to zero if column is hidden 727 $width = ($hidden) ? 0 : $width; 728 729 for ($col = $firstcol; $col <= $lastcol; $col++) { 730 $this->col_sizes[$col] = $width; 731 } 732 } 733 734 /** 735 * Set which cell or cells are selected in a worksheet 736 * 737 * @access public 738 * @param integer $first_row first row in the selected quadrant 739 * @param integer $first_column first column in the selected quadrant 740 * @param integer $last_row last row in the selected quadrant 741 * @param integer $last_column last column in the selected quadrant 742 */ 743 function setSelection($first_row,$first_column,$last_row,$last_column) 744 { 745 $this->_selection = array($first_row,$first_column,$last_row,$last_column); 746 } 747 748 /** 749 * Set panes and mark them as frozen. 750 * 751 * @access public 752 * @param array $panes This is the only parameter received and is composed of the following: 753 * 0 => Vertical split position, 754 * 1 => Horizontal split position 755 * 2 => Top row visible 756 * 3 => Leftmost column visible 757 * 4 => Active pane 758 */ 759 function freezePanes($panes) 760 { 761 $this->_frozen = 1; 762 $this->_panes = $panes; 763 } 764 765 /** 766 * Set panes and mark them as unfrozen. 767 * 768 * @access public 769 * @param array $panes This is the only parameter received and is composed of the following: 770 * 0 => Vertical split position, 771 * 1 => Horizontal split position 772 * 2 => Top row visible 773 * 3 => Leftmost column visible 774 * 4 => Active pane 775 */ 776 function thawPanes($panes) 777 { 778 $this->_frozen = 0; 779 $this->_panes = $panes; 780 } 781 782 /** 783 * Set the page orientation as portrait. 784 * 785 * @access public 786 */ 787 function setPortrait() 788 { 789 $this->_orientation = 1; 790 } 791 792 /** 793 * Set the page orientation as landscape. 794 * 795 * @access public 796 */ 797 function setLandscape() 798 { 799 $this->_orientation = 0; 800 } 801 802 /** 803 * Set the paper type. Ex. 1 = US Letter, 9 = A4 804 * 805 * @access public 806 * @param integer $size The type of paper size to use 807 */ 808 function setPaper($size = 0) 809 { 810 $this->_paper_size = $size; 811 } 812 813 814 /** 815 * Set the page header caption and optional margin. 816 * 817 * @access public 818 * @param string $string The header text 819 * @param float $margin optional head margin in inches. 820 */ 821 function setHeader($string,$margin = 0.50) 822 { 823 if (strlen($string) >= 255) { 824 //carp 'Header string must be less than 255 characters'; 825 return; 826 } 827 $this->_header = $string; 828 $this->_margin_head = $margin; 829 } 830 831 /** 832 * Set the page footer caption and optional margin. 833 * 834 * @access public 835 * @param string $string The footer text 836 * @param float $margin optional foot margin in inches. 837 */ 838 function setFooter($string,$margin = 0.50) 839 { 840 if (strlen($string) >= 255) { 841 //carp 'Footer string must be less than 255 characters'; 842 return; 843 } 844 $this->_footer = $string; 845 $this->_margin_foot = $margin; 846 } 847 848 /** 849 * Center the page horinzontally. 850 * 851 * @access public 852 * @param integer $center the optional value for centering. Defaults to 1 (center). 853 */ 854 function centerHorizontally($center = 1) 855 { 856 $this->_hcenter = $center; 857 } 858 859 /** 860 * Center the page vertically. 861 * 862 * @access public 863 * @param integer $center the optional value for centering. Defaults to 1 (center). 864 */ 865 function centerVertically($center = 1) 866 { 867 $this->_vcenter = $center; 868 } 869 870 /** 871 * Set all the page margins to the same value in inches. 872 * 873 * @access public 874 * @param float $margin The margin to set in inches 875 */ 876 function setMargins($margin) 877 { 878 $this->setMarginLeft($margin); 879 $this->setMarginRight($margin); 880 $this->setMarginTop($margin); 881 $this->setMarginBottom($margin); 882 } 883 884 /** 885 * Set the left and right margins to the same value in inches. 886 * 887 * @access public 888 * @param float $margin The margin to set in inches 889 */ 890 function setMargins_LR($margin) 891 { 892 $this->setMarginLeft($margin); 893 $this->setMarginRight($margin); 894 } 895 896 /** 897 * Set the top and bottom margins to the same value in inches. 898 * 899 * @access public 900 * @param float $margin The margin to set in inches 901 */ 902 function setMargins_TB($margin) 903 { 904 $this->setMarginTop($margin); 905 $this->setMarginBottom($margin); 906 } 907 908 /** 909 * Set the left margin in inches. 910 * 911 * @access public 912 * @param float $margin The margin to set in inches 913 */ 914 function setMarginLeft($margin = 0.75) 915 { 916 $this->_margin_left = $margin; 917 } 918 919 /** 920 * Set the right margin in inches. 921 * 922 * @access public 923 * @param float $margin The margin to set in inches 924 */ 925 function setMarginRight($margin = 0.75) 926 { 927 $this->_margin_right = $margin; 928 } 929 930 /** 931 * Set the top margin in inches. 932 * 933 * @access public 934 * @param float $margin The margin to set in inches 935 */ 936 function setMarginTop($margin = 1.00) 937 { 938 $this->_margin_top = $margin; 939 } 940 941 /** 942 * Set the bottom margin in inches. 943 * 944 * @access public 945 * @param float $margin The margin to set in inches 946 */ 947 function setMarginBottom($margin = 1.00) 948 { 949 $this->_margin_bottom = $margin; 950 } 951 952 /** 953 * Set the rows to repeat at the top of each printed page. 954 * 955 * @access public 956 * @param integer $first_row First row to repeat 957 * @param integer $last_row Last row to repeat. Optional. 958 */ 959 function repeatRows($first_row, $last_row = NULL) 960 { 961 $this->title_rowmin = $first_row; 962 if (isset($last_row)) { //Second row is optional 963 $this->title_rowmax = $last_row; 964 } 965 else { 966 $this->title_rowmax = $first_row; 967 } 968 } 969 970 /** 971 * Set the columns to repeat at the left hand side of each printed page. 972 * 973 * @access public 974 * @param integer $first_col First column to repeat 975 * @param integer $last_col Last column to repeat. Optional. 976 */ 977 function repeatColumns($first_col, $last_col = NULL) 978 { 979 $this->title_colmin = $first_col; 980 if (isset($last_col)) { // Second col is optional 981 $this->title_colmax = $last_col; 982 } 983 else { 984 $this->title_colmax = $first_col; 985 } 986 } 987 988 /** 989 * Set the area of each worksheet that will be printed. 990 * 991 * @access public 992 * @param integer $first_row First row of the area to print 993 * @param integer $first_col First column of the area to print 994 * @param integer $last_row Last row of the area to print 995 * @param integer $last_col Last column of the area to print 996 */ 997 function printArea($first_row, $first_col, $last_row, $last_col) 998 { 999 $this->print_rowmin = $first_row; 1000 $this->print_colmin = $first_col; 1001 $this->print_rowmax = $last_row; 1002 $this->print_colmax = $last_col; 1003 } 1004 1005 1006 /** 1007 * Set the option to hide gridlines on the printed page. 1008 * 1009 * @access public 1010 */ 1011 function hideGridlines() 1012 { 1013 $this->_print_gridlines = 0; 1014 } 1015 1016 /** 1017 * Set the option to print the row and column headers on the printed page. 1018 * 1019 * @access public 1020 * @param integer $print Whether to print the headers or not. Defaults to 1 (print). 1021 */ 1022 function printRowColHeaders($print = 1) 1023 { 1024 $this->_print_headers = $print; 1025 } 1026 1027 /** 1028 * Set the vertical and horizontal number of pages that will define the maximum area printed. 1029 * It doesn't seem to work with OpenOffice. 1030 * 1031 * @access public 1032 * @param integer $width Maximun width of printed area in pages 1033 * @param integer $height Maximun height of printed area in pages 1034 * @see setPrintScale() 1035 */ 1036 function fitToPages($width, $height) 1037 { 1038 $this->_fit_page = 1; 1039 $this->_fit_width = $width; 1040 $this->_fit_height = $height; 1041 } 1042 1043 /** 1044 * Store the horizontal page breaks on a worksheet (for printing). 1045 * The breaks represent the row after which the break is inserted. 1046 * 1047 * @access public 1048 * @param array $breaks Array containing the horizontal page breaks 1049 */ 1050 function setHPagebreaks($breaks) 1051 { 1052 foreach($breaks as $break) { 1053 array_push($this->_hbreaks,$break); 1054 } 1055 } 1056 1057 /** 1058 * Store the vertical page breaks on a worksheet (for printing). 1059 * The breaks represent the column after which the break is inserted. 1060 * 1061 * @access public 1062 * @param array $breaks Array containing the vertical page breaks 1063 */ 1064 function setVPagebreaks($breaks) 1065 { 1066 foreach($breaks as $break) { 1067 array_push($this->_vbreaks,$break); 1068 } 1069 } 1070 1071 1072 /** 1073 * Set the worksheet zoom factor. 1074 * 1075 * @access public 1076 * @param integer $scale The zoom factor 1077 */ 1078 function setZoom($scale = 100) 1079 { 1080 // Confine the scale to Excel's range 1081 if ($scale < 10 or $scale > 400) 1082 { 1083 $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400"); 1084 $scale = 100; 1085 } 1086 1087 $this->_zoom = floor($scale); 1088 } 1089 1090 /** 1091 * Set the scale factor for the printed page. 1092 * It turns off the "fit to page" option 1093 * 1094 * @access public 1095 * @param integer $scale The optional scale factor. Defaults to 100 1096 */ 1097 function setPrintScale($scale = 100) 1098 { 1099 // Confine the scale to Excel's range 1100 if ($scale < 10 or $scale > 400) 1101 { 1102 $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400"); 1103 $scale = 100; 1104 } 1105 1106 // Turn off "fit to page" option 1107 $this->_fit_page = 0; 1108 1109 $this->_print_scale = floor($scale); 1110 } 1111 1112 /** 1113 * Map to the appropriate write method acording to the token recieved. 1114 * 1115 * @access public 1116 * @param integer $row The row of the cell we are writing to 1117 * @param integer $col The column of the cell we are writing to 1118 * @param mixed $token What we are writing 1119 * @param mixed $format The optional format to apply to the cell 1120 */ 1121 function write($row, $col, $token, $format = 0) 1122 { 1123 // Check for a cell reference in A1 notation and substitute row and column 1124 /*if ($_[0] =~ /^\D/) { 1125 @_ = $this->_substituteCellref(@_); 1126 }*/ 1127 1128 1129 // Match number 1130 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/",$token)) { 1131 return $this->writeNumber($row,$col,$token,$format); 1132 } 1133 // Match http or ftp URL 1134 elseif (preg_match("/^[fh]tt?p:\/\//",$token)) { 1135 return $this->writeUrl($row, $col, $token, '', $format); 1136 } 1137 // Match mailto: 1138 elseif (preg_match("/^mailto:/",$token)) { 1139 return $this->writeUrl($row, $col, $token, '', $format); 1140 } 1141 // Match internal or external sheet link 1142 elseif (preg_match("/^(?:in|ex)ternal:/",$token)) { 1143 return $this->writeUrl($row, $col, $token, '', $format); 1144 } 1145 // Match formula 1146 elseif (preg_match("/^=/",$token)) { 1147 return $this->writeFormula($row, $col, $token, $format); 1148 } 1149 // Match formula 1150 elseif (preg_match("/^@/",$token)) { 1151 return $this->writeFormula($row, $col, $token, $format); 1152 } 1153 // Match blank 1154 elseif ($token == '') { 1155 return $this->writeBlank($row,$col,$format); 1156 } 1157 // Default: match string 1158 else { 1159 return $this->writeString($row,$col,$token,$format); 1160 } 1161 } 1162 1163 /** 1164 * Write an array of values as a row 1165 * 1166 * @access public 1167 * @param integer $row The row we are writing to 1168 * @param integer $col The first col (leftmost col) we are writing to 1169 * @param array $val The array of values to write 1170 * @param mixed $format The optional format to apply to the cell 1171 * @return mixed PEAR_Error on failure 1172 */ 1173 1174 function writeRow($row, $col, $val, $format=0) 1175 { 1176 $retval = ''; 1177 if (is_array($val)) { 1178 foreach($val as $v) { 1179 if (is_array($v)) { 1180 $this->writeCol($row, $col, $v, $format); 1181 } else { 1182 $this->write($row, $col, $v, $format); 1183 } 1184 $col++; 1185 } 1186 } else { 1187 $retval = new PEAR_Error('$val needs to be an array'); 1188 } 1189 return($retval); 1190 } 1191 1192 /** 1193 * Write an array of values as a column 1194 * 1195 * @access public 1196 * @param integer $row The first row (uppermost row) we are writing to 1197 * @param integer $col The col we are writing to 1198 * @param array $val The array of values to write 1199 * @param mixed $format The optional format to apply to the cell 1200 * @return mixed PEAR_Error on failure 1201 */ 1202 1203 function writeCol($row, $col, $val, $format=0) 1204 { 1205 $retval = ''; 1206 if (is_array($val)) { 1207 foreach($val as $v) { 1208 $this->write($row, $col, $v, $format); 1209 $row++; 1210 } 1211 } else { 1212 $retval = new PEAR_Error('$val needs to be an array'); 1213 } 1214 return($retval); 1215 } 1216 1217 /** 1218 * Returns an index to the XF record in the workbook 1219 * 1220 * @access private 1221 * @param mixed &$format The optional XF format 1222 * @return integer The XF record index 1223 */ 1224 function _XF(&$format) 1225 { 1226 if ($format != 0) { 1227 return($format->getXfIndex()); 1228 } 1229 else { 1230 return(0x0F); 1231 } 1232 } 1233 1234 1235 /****************************************************************************** 1236 ******************************************************************************* 1237 * 1238 * Internal methods 1239 */ 1240 1241 1242 /** 1243 * Store Worksheet data in memory using the parent's class append() or to a 1244 * temporary file, the default. 1245 * 1246 * @access private 1247 * @param string $data The binary data to append 1248 */ 1249 function _append($data) 1250 { 1251 if ($this->_using_tmpfile) 1252 { 1253 // Add CONTINUE records if necessary 1254 if (strlen($data) > $this->_limit) { 1255 $data = $this->_addContinue($data); 1256 } 1257 fwrite($this->_filehandle,$data); 1258 $this->_datasize += strlen($data); 1259 } 1260 else { 1261 parent::_append($data); 1262 } 1263 } 1264 1265 /** 1266 * Substitute an Excel cell reference in A1 notation for zero based row and 1267 * column values in an argument list. 1268 * 1269 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello"). 1270 * 1271 * @access private 1272 * @param string $cell The cell reference. Or range of cells. 1273 * @return array 1274 */ 1275 function _substituteCellref($cell) 1276 { 1277 $cell = strtoupper($cell); 1278 1279 // Convert a column range: 'A:A' or 'B:G' 1280 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/",$cell,$match)) { 1281 list($no_use, $col1) = $this->_cellToRowcol($match[1] .'1'); // Add a dummy row 1282 list($no_use, $col2) = $this->_cellToRowcol($match[2] .'1'); // Add a dummy row 1283 return(array($col1, $col2)); 1284 } 1285 1286 // Convert a cell range: 'A1:B7' 1287 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/",$cell,$match)) { 1288 list($row1, $col1) = $this->_cellToRowcol($match[1]); 1289 list($row2, $col2) = $this->_cellToRowcol($match[2]); 1290 return(array($row1, $col1, $row2, $col2)); 1291 } 1292 1293 // Convert a cell reference: 'A1' or 'AD2000' 1294 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/",$cell)) { 1295 list($row1, $col1) = $this->_cellToRowcol($match[1]); 1296 return(array($row1, $col1)); 1297 } 1298 1299 // TODO use real error codes 1300 $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE); 1301 } 1302 1303 /** 1304 * Convert an Excel cell reference in A1 notation to a zero based row and column 1305 * reference; converts C1 to (0, 2). 1306 * 1307 * @access private 1308 * @param string $cell The cell reference. 1309 * @return array containing (row, column) 1310 */ 1311 function _cellToRowcol($cell) 1312 { 1313 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match); 1314 $col = $match[1]; 1315 $row = $match[2]; 1316 1317 // Convert base26 column string to number 1318 $chars = explode('', $col); 1319 $expn = 0; 1320 $col = 0; 1321 1322 while ($chars) { 1323 $char = array_pop($chars); // LS char first 1324 $col += (ord($char) -ord('A') +1) * pow(26,$expn); 1325 $expn++; 1326 } 1327 1328 // Convert 1-index to zero-index 1329 $row--; 1330 $col--; 1331 1332 return(array($row, $col)); 1333 } 1334 1335 /** 1336 * Based on the algorithm provided by Daniel Rentz of OpenOffice. 1337 * 1338 * @access private 1339 * @param string $plaintext The password to be encoded in plaintext. 1340 * @return string The encoded password 1341 */ 1342 function _encodePassword($plaintext) 1343 { 1344 $password = 0x0000; 1345 $i = 1; // char position 1346 1347 // split the plain text password in its component characters 1348 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY); 1349 foreach($chars as $char) 1350 { 1351 $value = ord($char) << $i; // shifted ASCII value 1352 $rotated_bits = $value >> 15; // rotated bits beyond bit 15 1353 $value &= 0x7fff; // first 15 bits 1354 $password ^= ($value | $rotated_bits); 1355 $i++; 1356 } 1357 1358 $password ^= strlen($plaintext); 1359 $password ^= 0xCE4B; 1360 1361 return($password); 1362 } 1363 1364 /** 1365 * This method sets the properties for outlining and grouping. The defaults 1366 * correspond to Excel's defaults. 1367 * 1368 * @param bool $visible 1369 * @param bool $symbols_below 1370 * @param bool $symbols_right 1371 * @param bool $auto_style 1372 */ 1373 function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false) 1374 { 1375 $this->_outline_on = $visible; 1376 $this->_outline_below = $symbols_below; 1377 $this->_outline_right = $symbols_right; 1378 $this->_outline_style = $auto_style; 1379 1380 // Ensure this is a boolean vale for Window2 1381 if ($this->_outline_on) { 1382 $this->_outline_on = 1; 1383 } 1384 } 1385 1386 /****************************************************************************** 1387 ******************************************************************************* 1388 * 1389 * BIFF RECORDS 1390 */ 1391 1392 1393 /** 1394 * Write a double to the specified row and column (zero indexed). 1395 * An integer can be written as a double. Excel will display an 1396 * integer. $format is optional. 1397 * 1398 * Returns 0 : normal termination 1399 * -2 : row or column out of range 1400 * 1401 * @access public 1402 * @param integer $row Zero indexed row 1403 * @param integer $col Zero indexed column 1404 * @param float $num The number to write 1405 * @param mixed $format The optional XF format 1406 * @return integer 1407 */ 1408 function writeNumber($row, $col, $num, $format = 0) 1409 { 1410 $record = 0x0203; // Record identifier 1411 $length = 0x000E; // Number of bytes to follow 1412 1413 $xf = $this->_XF($format); // The cell format 1414 1415 // Check that row and col are valid and store max and min values 1416 if ($row >= $this->_xls_rowmax) 1417 { 1418 return(-2); 1419 } 1420 if ($col >= $this->_xls_colmax) 1421 { 1422 return(-2); 1423 } 1424 if ($row < $this->_dim_rowmin) 1425 { 1426 $this->_dim_rowmin = $row; 1427 } 1428 if ($row > $this->_dim_rowmax) 1429 { 1430 $this->_dim_rowmax = $row; 1431 } 1432 if ($col < $this->_dim_colmin) 1433 { 1434 $this->_dim_colmin = $col; 1435 } 1436 if ($col > $this->_dim_colmax) 1437 { 1438 $this->_dim_colmax = $col; 1439 } 1440 1441 $header = pack("vv", $record, $length); 1442 $data = pack("vvv", $row, $col, $xf); 1443 $xl_double = pack("d", $num); 1444 if ($this->_byte_order) // if it's Big Endian 1445 { 1446 $xl_double = strrev($xl_double); 1447 } 1448 1449 $this->_append($header.$data.$xl_double); 1450 return(0); 1451 } 1452 1453 /** 1454 * Write a string to the specified row and column (zero indexed). 1455 * NOTE: there is an Excel 5 defined limit of 255 characters. 1456 * $format is optional. 1457 * Returns 0 : normal termination 1458 * -2 : row or column out of range 1459 * -3 : long string truncated to 255 chars 1460 * 1461 * @access public 1462 * @param integer $row Zero indexed row 1463 * @param integer $col Zero indexed column 1464 * @param string $str The string to write 1465 * @param mixed $format The XF format for the cell 1466 * @return integer 1467 */ 1468 function writeString($row, $col, $str, $format = 0) 1469 { 1470 if ($this->_BIFF_version == 0x0600) { 1471 return $this->writeStringBIFF8($row, $col, $str, $format); 1472 } 1473 $strlen = strlen($str); 1474 $record = 0x0204; // Record identifier 1475 $length = 0x0008 + $strlen; // Bytes to follow 1476 $xf = $this->_XF($format); // The cell format 1477 1478 $str_error = 0; 1479 1480 // Check that row and col are valid and store max and min values 1481 if ($row >= $this->_xls_rowmax) 1482 { 1483 return(-2); 1484 } 1485 if ($col >= $this->_xls_colmax) 1486 { 1487 return(-2); 1488 } 1489 if ($row < $this->_dim_rowmin) 1490 { 1491 $this->_dim_rowmin = $row; 1492 } 1493 if ($row > $this->_dim_rowmax) 1494 { 1495 $this->_dim_rowmax = $row; 1496 } 1497 if ($col < $this->_dim_colmin) 1498 { 1499 $this->_dim_colmin = $col; 1500 } 1501 if ($col > $this->_dim_colmax) 1502 { 1503 $this->_dim_colmax = $col; 1504 } 1505 1506 if ($strlen > $this->_xls_strmax) // LABEL must be < 255 chars 1507 { 1508 $str = substr($str, 0, $this->_xls_strmax); 1509 $length = 0x0008 + $this->_xls_strmax; 1510 $strlen = $this->_xls_strmax; 1511 $str_error = -3; 1512 } 1513 1514 $header = pack("vv", $record, $length); 1515 $data = pack("vvvv", $row, $col, $xf, $strlen); 1516 $this->_append($header.$data.$str); 1517 return($str_error); 1518 } 1519 1520 function writeStringBIFF8($row, $col, $str, $format = 0) 1521 { 1522 $strlen = strlen($str); 1523 $record = 0x00FD; // Record identifier 1524 $length = 0x000A; // Bytes to follow 1525 $xf = $this->_XF($format); // The cell format 1526 $encoding = 0x0; 1527 1528 $str_error = 0; 1529 1530 // Check that row and col are valid and store max and min values 1531 if ($this->_checkRowCol($row, $col) == false) { 1532 return -2; 1533 } 1534 1535 $str = pack('vC', $strlen, $encoding).$str; 1536 1537 /* check if string is already present */ 1538 if (!isset($this->_str_table[$str])) { 1539 $this->_str_table[$str] = $this->_str_unique++; 1540 } 1541 $this->_str_total++; 1542 1543 $header = pack('vv', $record, $length); 1544 $data = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]); 1545 $this->_append($header.$data); 1546 return $str_error; 1547 } 1548 1549 /** 1550 * Check row and col before writing to a cell, and update the sheet's 1551 * dimensions accordingly 1552 * 1553 * @access private 1554 * @param integer $row Zero indexed row 1555 * @param integer $col Zero indexed column 1556 * @return boolean true for success, false if row and/or col are grester 1557 * then maximums allowed. 1558 */ 1559 function _checkRowCol($row, $col) 1560 { 1561 if ($row >= $this->_xls_rowmax) { 1562 return false; 1563 } 1564 if ($col >= $this->_xls_colmax) { 1565 return false; 1566 } 1567 if ($row < $this->_dim_rowmin) { 1568 $this->_dim_rowmin = $row; 1569 } 1570 if ($row > $this->_dim_rowmax) { 1571 $this->_dim_rowmax = $row; 1572 } 1573 if ($col < $this->_dim_colmin) { 1574 $this->_dim_colmin = $col; 1575 } 1576 if ($col > $this->_dim_colmax) { 1577 $this->_dim_colmax = $col; 1578 } 1579 return true; 1580 } 1581 1582 /** 1583 * Writes a note associated with the cell given by the row and column. 1584 * NOTE records don't have a length limit. 1585 * 1586 * @access public 1587 * @param integer $row Zero indexed row 1588 * @param integer $col Zero indexed column 1589 * @param string $note The note to write 1590 */ 1591 function writeNote($row, $col, $note) 1592 { 1593 $note_length = strlen($note); 1594 $record = 0x001C; // Record identifier 1595 $max_length = 2048; // Maximun length for a NOTE record 1596 //$length = 0x0006 + $note_length; // Bytes to follow 1597 1598 // Check that row and col are valid and store max and min values 1599 if ($row >= $this->_xls_rowmax) 1600 { 1601 return(-2); 1602 } 1603 if ($col >= $this->_xls_colmax) 1604 { 1605 return(-2); 1606 } 1607 if ($row < $this->_dim_rowmin) 1608 { 1609 $this->_dim_rowmin = $row; 1610 } 1611 if ($row > $this->_dim_rowmax) 1612 { 1613 $this->_dim_rowmax = $row; 1614 } 1615 if ($col < $this->_dim_colmin) 1616 { 1617 $this->_dim_colmin = $col; 1618 } 1619 if ($col > $this->_dim_colmax) 1620 { 1621 $this->_dim_colmax = $col; 1622 } 1623 1624 // Length for this record is no more than 2048 + 6 1625 $length = 0x0006 + min($note_length, 2048); 1626 $header = pack("vv", $record, $length); 1627 $data = pack("vvv", $row, $col, $note_length); 1628 $this->_append($header.$data.substr($note, 0, 2048)); 1629 1630 for($i = $max_length; $i < $note_length; $i += $max_length) 1631 { 1632 $chunk = substr($note, $i, $max_length); 1633 $length = 0x0006 + strlen($chunk); 1634 $header = pack("vv", $record, $length); 1635 $data = pack("vvv", -1, 0, strlen($chunk)); 1636 $this->_append($header.$data.$chunk); 1637 } 1638 return(0); 1639 } 1640 1641 /** 1642 * Write a blank cell to the specified row and column (zero indexed). 1643 * A blank cell is used to specify formatting without adding a string 1644 * or a number. 1645 * 1646 * A blank cell without a format serves no purpose. Therefore, we don't write 1647 * a BLANK record unless a format is specified. 1648 * 1649 * Returns 0 : normal termination (including no format) 1650 * -1 : insufficient number of arguments 1651 * -2 : row or column out of range 1652 * 1653 * @access public 1654 * @param integer $row Zero indexed row 1655 * @param integer $col Zero indexed column 1656 * @param mixed $format The XF format 1657 */ 1658 function writeBlank($row, $col, $format) 1659 { 1660 // Don't write a blank cell unless it has a format 1661 if ($format == 0) 1662 { 1663 return(0); 1664 } 1665 1666 $record = 0x0201; // Record identifier 1667 $length = 0x0006; // Number of bytes to follow 1668 $xf = $this->_XF($format); // The cell format 1669 1670 // Check that row and col are valid and store max and min values 1671 if ($row >= $this->_xls_rowmax) 1672 { 1673 return(-2); 1674 } 1675 if ($col >= $this->_xls_colmax) 1676 { 1677 return(-2); 1678 } 1679 if ($row < $this->_dim_rowmin) 1680 { 1681 $this->_dim_rowmin = $row; 1682 } 1683 if ($row > $this->_dim_rowmax) 1684 { 1685 $this->_dim_rowmax = $row; 1686 } 1687 if ($col < $this->_dim_colmin) 1688 { 1689 $this->_dim_colmin = $col; 1690 } 1691 if ($col > $this->_dim_colmax) 1692 { 1693 $this->_dim_colmax = $col; 1694 } 1695 1696 $header = pack("vv", $record, $length); 1697 $data = pack("vvv", $row, $col, $xf); 1698 $this->_append($header.$data); 1699 return 0; 1700 } 1701 1702 /** 1703 * Write a formula to the specified row and column (zero indexed). 1704 * The textual representation of the formula is passed to the parser in 1705 * Parser.php which returns a packed binary string. 1706 * 1707 * Returns 0 : normal termination 1708 * -1 : formula errors (bad formula) 1709 * -2 : row or column out of range 1710 * 1711 * @access public 1712 * @param integer $row Zero indexed row 1713 * @param integer $col Zero indexed column 1714 * @param string $formula The formula text string 1715 * @param mixed $format The optional XF format 1716 * @return integer 1717 */ 1718 function writeFormula($row, $col, $formula, $format = 0) 1719 { 1720 $record = 0x0006; // Record identifier 1721 1722 // Excel normally stores the last calculated value of the formula in $num. 1723 // Clearly we are not in a position to calculate this a priori. Instead 1724 // we set $num to zero and set the option flags in $grbit to ensure 1725 // automatic calculation of the formula when the file is opened. 1726 // 1727 $xf = $this->_XF($format); // The cell format 1728 $num = 0x00; // Current value of formula 1729 $grbit = 0x03; // Option flags 1730 $unknown = 0x0000; // Must be zero 1731 1732 1733 // Check that row and col are valid and store max and min values 1734 if ($this->_checkRowCol($row, $col) == false) { 1735 return -2; 1736 } 1737 1738 // Strip the '=' or '@' sign at the beginning of the formula string 1739 if (preg_match("/^=/",$formula)) { 1740 $formula = preg_replace("/(^=)/","",$formula); 1741 } 1742 elseif (preg_match("/^@/",$formula)) { 1743 $formula = preg_replace("/(^@)/","",$formula); 1744 } 1745 else 1746 { 1747 // Error handling 1748 $this->writeString($row, $col, 'Unrecognised character for formula'); 1749 return -1; 1750 } 1751 1752 // Parse the formula using the parser in Parser.php 1753 $error = $this->_parser->parse($formula); 1754 if ($this->isError($error)) 1755 { 1756 $this->writeString($row, $col, $error->getMessage()); 1757 return -1; 1758 } 1759 1760 $formula = $this->_parser->toReversePolish(); 1761 if ($this->isError($formula)) 1762 { 1763 $this->writeString($row, $col, $formula->getMessage()); 1764 return -1; 1765 } 1766 1767 $formlen = strlen($formula); // Length of the binary string 1768 $length = 0x16 + $formlen; // Length of the record data 1769 1770 $header = pack("vv", $record, $length); 1771 $data = pack("vvvdvVv", $row, $col, $xf, $num, 1772 $grbit, $unknown, $formlen); 1773 1774 $this->_append($header.$data.$formula); 1775 return 0; 1776 } 1777 1778 /** 1779 * Write a hyperlink. 1780 * This is comprised of two elements: the visible label and 1781 * the invisible link. The visible label is the same as the link unless an 1782 * alternative string is specified. The label is written using the 1783 * writeString() method. Therefore the 255 characters string limit applies. 1784 * $string and $format are optional. 1785 * 1786 * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external 1787 * directory url. 1788 * 1789 * Returns 0 : normal termination 1790 * -2 : row or column out of range 1791 * -3 : long string truncated to 255 chars 1792 * 1793 * @access public 1794 * @param integer $row Row 1795 * @param integer $col Column 1796 * @param string $url URL string 1797 * @param string $string Alternative label 1798 * @param mixed $format The cell format 1799 * @return integer 1800 */ 1801 function writeUrl($row, $col, $url, $string = '', $format = 0) 1802 { 1803 // Add start row and col to arg list 1804 return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format)); 1805 } 1806 1807 /** 1808 * This is the more general form of writeUrl(). It allows a hyperlink to be 1809 * written to a range of cells. This function also decides the type of hyperlink 1810 * to be written. These are either, Web (http, ftp, mailto), Internal 1811 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1'). 1812 * 1813 * @access private 1814 * @see writeUrl() 1815 * @param integer $row1 Start row 1816 * @param integer $col1 Start column 1817 * @param integer $row2 End row 1818 * @param integer $col2 End column 1819 * @param string $url URL string 1820 * @param string $string Alternative label 1821 * @param mixed $format The cell format 1822 * @return integer 1823 */ 1824 1825 function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = 0) 1826 { 1827 1828 // Check for internal/external sheet links or default to web link 1829 if (preg_match('[^internal:]', $url)) { 1830 return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format)); 1831 } 1832 if (preg_match('[^external:]', $url)) { 1833 return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format)); 1834 } 1835 return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format)); 1836 } 1837 1838 1839 /** 1840 * Used to write http, ftp and mailto hyperlinks. 1841 * The link type ($options) is 0x03 is the same as absolute dir ref without 1842 * sheet. However it is differentiated by the $unknown2 data stream. 1843 * 1844 * @access private 1845 * @see writeUrl() 1846 * @param integer $row1 Start row 1847 * @param integer $col1 Start column 1848 * @param integer $row2 End row 1849 * @param integer $col2 End column 1850 * @param string $url URL string 1851 * @param string $str Alternative label 1852 * @param mixed $format The cell format 1853 * @return integer 1854 */ 1855 function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1856 { 1857 $record = 0x01B8; // Record identifier 1858 $length = 0x00000; // Bytes to follow 1859 1860 if ($format == 0) { 1861 $format = $this->_url_format; 1862 } 1863 1864 // Write the visible label using the writeString() method. 1865 if ($str == '') { 1866 $str = $url; 1867 } 1868 $str_error = $this->writeString($row1, $col1, $str, $format); 1869 if (($str_error == -2) or ($str_error == -3)) { 1870 return $str_error; 1871 } 1872 1873 // Pack the undocumented parts of the hyperlink stream 1874 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1875 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B"); 1876 1877 // Pack the option flags 1878 $options = pack("V", 0x03); 1879 1880 // Convert URL to a null terminated wchar string 1881 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1882 $url = $url . "\0\0\0"; 1883 1884 // Pack the length of the URL 1885 $url_len = pack("V", strlen($url)); 1886 1887 // Calculate the data length 1888 $length = 0x34 + strlen($url); 1889 1890 // Pack the header data 1891 $header = pack("vv", $record, $length); 1892 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1893 1894 // Write the packed data 1895 $this->_append( $header. $data. 1896 $unknown1. $options. 1897 $unknown2. $url_len. $url); 1898 return($str_error); 1899 } 1900 1901 /** 1902 * Used to write internal reference hyperlinks such as "Sheet1!A1". 1903 * 1904 * @access private 1905 * @see writeUrl() 1906 * @param integer $row1 Start row 1907 * @param integer $col1 Start column 1908 * @param integer $row2 End row 1909 * @param integer $col2 End column 1910 * @param string $url URL string 1911 * @param string $str Alternative label 1912 * @param mixed $format The cell format 1913 * @return integer 1914 */ 1915 function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1916 { 1917 $record = 0x01B8; // Record identifier 1918 $length = 0x00000; // Bytes to follow 1919 1920 if ($format == 0) { 1921 $format = $this->_url_format; 1922 } 1923 1924 // Strip URL type 1925 $url = preg_replace('s[^internal:]', '', $url); 1926 1927 // Write the visible label 1928 if ($str == '') { 1929 $str = $url; 1930 } 1931 $str_error = $this->writeString($row1, $col1, $str, $format); 1932 if (($str_error == -2) or ($str_error == -3)) { 1933 return $str_error; 1934 } 1935 1936 // Pack the undocumented parts of the hyperlink stream 1937 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1938 1939 // Pack the option flags 1940 $options = pack("V", 0x08); 1941 1942 // Convert the URL type and to a null terminated wchar string 1943 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1944 $url = $url . "\0\0\0"; 1945 1946 // Pack the length of the URL as chars (not wchars) 1947 $url_len = pack("V", floor(strlen($url)/2)); 1948 1949 // Calculate the data length 1950 $length = 0x24 + strlen($url); 1951 1952 // Pack the header data 1953 $header = pack("vv", $record, $length); 1954 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1955 1956 // Write the packed data 1957 $this->_append($header. $data. 1958 $unknown1. $options. 1959 $url_len. $url); 1960 return($str_error); 1961 } 1962 1963 /** 1964 * Write links to external directory names such as 'c:\foo.xls', 1965 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'. 1966 * 1967 * Note: Excel writes some relative links with the $dir_long string. We ignore 1968 * these cases for the sake of simpler code. 1969 * 1970 * @access private 1971 * @see writeUrl() 1972 * @param integer $row1 Start row 1973 * @param integer $col1 Start column 1974 * @param integer $row2 End row 1975 * @param integer $col2 End column 1976 * @param string $url URL string 1977 * @param string $str Alternative label 1978 * @param mixed $format The cell format 1979 * @return integer 1980 */ 1981 function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1982 { 1983 // Network drives are different. We will handle them separately 1984 // MS/Novell network drives and shares start with \\ 1985 if (preg_match('[^external:\\\\]', $url)) { 1986 return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format)); 1987 } 1988 1989 $record = 0x01B8; // Record identifier 1990 $length = 0x00000; // Bytes to follow 1991 1992 if ($format == 0) { 1993 $format = $this->_url_format; 1994 } 1995 1996 // Strip URL type and change Unix dir separator to Dos style (if needed) 1997 // 1998 $url = preg_replace('[^external:]', '', $url); 1999 $url = preg_replace('[/]', "\\", $url); 2000 2001 // Write the visible label 2002 if ($str == '') { 2003 $str = preg_replace('[\#]', ' - ', $url); 2004 } 2005 $str_error = $this->writeString($row1, $col1, $str, $format); 2006 if (($str_error == -2) or ($str_error == -3)) { 2007 return $str_error; 2008 } 2009 2010 // Determine if the link is relative or absolute: 2011 // relative if link contains no dir separator, "somefile.xls" 2012 // relative if link starts with up-dir, "..\..\somefile.xls" 2013 // otherwise, absolute 2014 2015 $absolute = 0x02; // Bit mask 2016 if (!preg_match('[\\]', $url)) { 2017 $absolute = 0x00; 2018 } 2019 if (preg_match('[^\.\.\\]', $url)) { 2020 $absolute = 0x00; 2021 } 2022 2023 // Determine if the link contains a sheet reference and change some of the 2024 // parameters accordingly. 2025 // Split the dir name and sheet name (if it exists) 2026 list($dir_long , $sheet) = explode('#', $url); 2027 $link_type = 0x01 | $absolute; 2028 2029 if (isset($sheet)) { 2030 $link_type |= 0x08; 2031 $sheet_len = pack("V", strlen($sheet) + 0x01); 2032 $sheet = join("\0", explode('', $sheet)); 2033 $sheet .= "\0\0\0"; 2034 } 2035 else { 2036 $sheet_len = ''; 2037 $sheet = ''; 2038 } 2039 2040 // Pack the link type 2041 $link_type = pack("V", $link_type); 2042 2043 // Calculate the up-level dir count e.g.. (..\..\..\ == 3) 2044 $up_count = preg_match_all("/\.\.\\/", $dir_long, $useless); 2045 $up_count = pack("v", $up_count); 2046 2047 // Store the short dos dir name (null terminated) 2048 $dir_short = preg_replace('/\.\.\\/', '', $dir_long) . "\0"; 2049 2050 // Store the long dir name as a wchar string (non-null terminated) 2051 $dir_long = join("\0", explode('', $dir_long)); 2052 $dir_long = $dir_long . "\0"; 2053 2054 // Pack the lengths of the dir strings 2055 $dir_short_len = pack("V", strlen($dir_short) ); 2056 $dir_long_len = pack("V", strlen($dir_long) ); 2057 $stream_len = pack("V", strlen($dir_long) + 0x06); 2058 2059 // Pack the undocumented parts of the hyperlink stream 2060 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' ); 2061 $unknown2 = pack("H*",'0303000000000000C000000000000046' ); 2062 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000'); 2063 $unknown4 = pack("v", 0x03 ); 2064 2065 // Pack the main data stream 2066 $data = pack("vvvv", $row1, $row2, $col1, $col2) . 2067 $unknown1 . 2068 $link_type . 2069 $unknown2 . 2070 $up_count . 2071 $dir_short_len. 2072 $dir_short . 2073 $unknown3 . 2074 $stream_len . 2075 $dir_long_len . 2076 $unknown4 . 2077 $dir_long . 2078 $sheet_len . 2079 $sheet ; 2080 2081 // Pack the header data 2082 $length = strlen($data); 2083 $header = pack("vv", $record, $length); 2084 2085 // Write the packed data 2086 $this->_append($header. $data); 2087 return($str_error); 2088 } 2089 2090 2091 /** 2092 * This method is used to set the height and format for a row. 2093 * 2094 * @access public 2095 * @param integer $row The row to set 2096 * @param integer $height Height we are giving to the row. 2097 * Use NULL to set XF without setting height 2098 * @param mixed $format XF format we are giving to the row 2099 * @param bool $hidden The optional hidden attribute 2100 * @param integer $level The optional outline level for row, in range [0,7] 2101 */ 2102 function setRow($row, $height, $format = 0, $hidden = false, $level = 0) 2103 { 2104 $record = 0x0208; // Record identifier 2105 $length = 0x0010; // Number of bytes to follow 2106 2107 $colMic = 0x0000; // First defined column 2108 $colMac = 0x0000; // Last defined column 2109 $irwMac = 0x0000; // Used by Excel to optimise loading 2110 $reserved = 0x0000; // Reserved 2111 $grbit = 0x0000; // Option flags 2112 $ixfe = $this->_XF($format); // XF index 2113 2114 // Use setRow($row, NULL, $XF) to set XF format without setting height 2115 if ($height != NULL) { 2116 $miyRw = $height * 20; // row height 2117 } 2118 else { 2119 $miyRw = 0xff; // default row height is 256 2120 } 2121 2122 $level = max(0, min($level, 7)); // level should be between 0 and 7 2123 $this->_outline_row_level = max($level, $this->_outline_row_level); 2124 2125 2126 // Set the options flags. fUnsynced is used to show that the font and row 2127 // heights are not compatible. This is usually the case for WriteExcel. 2128 // The collapsed flag 0x10 doesn't seem to be used to indicate that a row 2129 // is collapsed. Instead it is used to indicate that the previous row is 2130 // collapsed. The zero height flag, 0x20, is used to collapse a row. 2131 2132 $grbit |= $level; 2133 if ($hidden) { 2134 $grbit |= 0x0020; 2135 } 2136 $grbit |= 0x0040; // fUnsynced 2137 if ($format) { 2138 $grbit |= 0x0080; 2139 } 2140 $grbit |= 0x0100; 2141 2142 $header = pack("vv", $record, $length); 2143 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw, 2144 $irwMac,$reserved, $grbit, $ixfe); 2145 $this->_append($header.$data); 2146 } 2147 2148 /** 2149 * Writes Excel DIMENSIONS to define the area in which there is data. 2150 * 2151 * @access private 2152 */ 2153 function _storeDimensions() 2154 { 2155 $record = 0x0200; // Record identifier 2156 $row_min = $this->_dim_rowmin; // First row 2157 $row_max = $this->_dim_rowmax + 1; // Last row plus 1 2158 $col_min = $this->_dim_colmin; // First column 2159 $col_max = $this->_dim_colmax + 1; // Last column plus 1 2160 $reserved = 0x0000; // Reserved by Excel 2161 2162 if ($this->_BIFF_version == 0x0500) { 2163 $length = 0x000A; // Number of bytes to follow 2164 $data = pack("vvvvv", $row_min, $row_max, 2165 $col_min, $col_max, $reserved); 2166 } 2167 elseif ($this->_BIFF_version == 0x0600) { 2168 $length = 0x000E; 2169 $data = pack("VVvvv", $row_min, $row_max, 2170 $col_min, $col_max, $reserved); 2171 } 2172 $header = pack("vv", $record, $length); 2173 $this->_prepend($header.$data); 2174 } 2175 2176 /** 2177 * Write BIFF record Window2. 2178 * 2179 * @access private 2180 */ 2181 function _storeWindow2() 2182 { 2183 $record = 0x023E; // Record identifier 2184 if ($this->_BIFF_version == 0x0500) { 2185 $length = 0x000A; // Number of bytes to follow 2186 } 2187 elseif ($this->_BIFF_version == 0x0600) { 2188 $length = 0x0012; 2189 } 2190 2191 $grbit = 0x00B6; // Option flags 2192 $rwTop = 0x0000; // Top row visible in window 2193 $colLeft = 0x0000; // Leftmost column visible in window 2194 2195 2196 // The options flags that comprise $grbit 2197 $fDspFmla = 0; // 0 - bit 2198 $fDspGrid = 1; // 1 2199 $fDspRwCol = 1; // 2 2200 $fFrozen = $this->_frozen; // 3 2201 $fDspZeros = 1; // 4 2202 $fDefaultHdr = 1; // 5 2203 $fArabic = 0; // 6 2204 $fDspGuts = $this->_outline_on; // 7 2205 $fFrozenNoSplit = 0; // 0 - bit 2206 $fSelected = $this->selected; // 1 2207 $fPaged = 1; // 2 2208 2209 $grbit = $fDspFmla; 2210 $grbit |= $fDspGrid << 1; 2211 $grbit |= $fDspRwCol << 2; 2212 $grbit |= $fFrozen << 3; 2213 $grbit |= $fDspZeros << 4; 2214 $grbit |= $fDefaultHdr << 5; 2215 $grbit |= $fArabic << 6; 2216 $grbit |= $fDspGuts << 7; 2217 $grbit |= $fFrozenNoSplit << 8; 2218 $grbit |= $fSelected << 9; 2219 $grbit |= $fPaged << 10; 2220 2221 $header = pack("vv", $record, $length); 2222 $data = pack("vvv", $grbit, $rwTop, $colLeft); 2223 // FIXME !!! 2224 if ($this->_BIFF_version == 0x0500) { 2225 $rgbHdr = 0x00000000; // Row/column heading and gridline color 2226 $data .= pack("V", $rgbHdr); 2227 } 2228 elseif ($this->_BIFF_version == 0x0600) { 2229 $rgbHdr = 0x0040; // Row/column heading and gridline color index 2230 $zoom_factor_page_break = 0x0000; 2231 $zoom_factor_normal = 0x0000; 2232 $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000); 2233 } 2234 $this->_append($header.$data); 2235 } 2236 2237 /** 2238 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use. 2239 * 2240 * @access private 2241 */ 2242 function _storeDefcol() 2243 { 2244 $record = 0x0055; // Record identifier 2245 $length = 0x0002; // Number of bytes to follow 2246 $colwidth = 0x0008; // Default column width 2247 2248 $header = pack("vv", $record, $length); 2249 $data = pack("v", $colwidth); 2250 $this->_prepend($header.$data); 2251 } 2252 2253 /** 2254 * Write BIFF record COLINFO to define column widths 2255 * 2256 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C 2257 * length record. 2258 * 2259 * @access private 2260 * @param array $col_array This is the only parameter received and is composed of the following: 2261 * 0 => First formatted column, 2262 * 1 => Last formatted column, 2263 * 2 => Col width (8.43 is Excel default), 2264 * 3 => The optional XF format of the column, 2265 * 4 => Option flags. 2266 * 5 => Optional outline level 2267 */ 2268 function _storeColinfo($col_array) 2269 { 2270 if (isset($col_array[0])) { 2271 $colFirst = $col_array[0]; 2272 } 2273 if (isset($col_array[1])) { 2274 $colLast = $col_array[1]; 2275 } 2276 if (isset($col_array[2])) { 2277 $coldx = $col_array[2]; 2278 } 2279 else { 2280 $coldx = 8.43; 2281 } 2282 if (isset($col_array[3])) { 2283 $format = $col_array[3]; 2284 } 2285 else { 2286 $format = 0; 2287 } 2288 if (isset($col_array[4])) { 2289 $grbit = $col_array[4]; 2290 } 2291 else { 2292 $grbit = 0; 2293 } 2294 if (isset($col_array[5])) { 2295 $level = $col_array[5]; 2296 } 2297 else { 2298 $level = 0; 2299 } 2300 $record = 0x007D; // Record identifier 2301 $length = 0x000B; // Number of bytes to follow 2302 2303 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !? 2304 $coldx *= 256; // Convert to units of 1/256 of a char 2305 2306 $ixfe = $this->_XF($format); 2307 $reserved = 0x00; // Reserved 2308 2309 $level = max(0, min($level, 7)); 2310 $grbit |= $level << 8; 2311 2312 $header = pack("vv", $record, $length); 2313 $data = pack("vvvvvC", $colFirst, $colLast, $coldx, 2314 $ixfe, $grbit, $reserved); 2315 $this->_prepend($header.$data); 2316 } 2317 2318 /** 2319 * Write BIFF record SELECTION. 2320 * 2321 * @access private 2322 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast) 2323 * @see setSelection() 2324 */ 2325 function _storeSelection($array) 2326 { 2327 list($rwFirst,$colFirst,$rwLast,$colLast) = $array; 2328 $record = 0x001D; // Record identifier 2329 $length = 0x000F; // Number of bytes to follow 2330 2331 $pnn = $this->_active_pane; // Pane position 2332 $rwAct = $rwFirst; // Active row 2333 $colAct = $colFirst; // Active column 2334 $irefAct = 0; // Active cell ref 2335 $cref = 1; // Number of refs 2336 2337 if (!isset($rwLast)) { 2338 $rwLast = $rwFirst; // Last row in reference 2339 } 2340 if (!isset($colLast)) { 2341 $colLast = $colFirst; // Last col in reference 2342 } 2343 2344 // Swap last row/col for first row/col as necessary 2345 if ($rwFirst > $rwLast) 2346 { 2347 list($rwFirst, $rwLast) = array($rwLast, $rwFirst); 2348 } 2349 2350 if ($colFirst > $colLast) 2351 { 2352 list($colFirst, $colLast) = array($colLast, $colFirst); 2353 } 2354 2355 $header = pack("vv", $record, $length); 2356 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct, 2357 $irefAct, $cref, 2358 $rwFirst, $rwLast, 2359 $colFirst, $colLast); 2360 $this->_append($header.$data); 2361 } 2362 2363 /** 2364 * Store the MERGEDCELLS record for all ranges of merged cells 2365 * 2366 * @access private 2367 */ 2368 function _storeMergedCells() 2369 { 2370 // if there are no merged cell ranges set, return 2371 if (count($this->_merged_ranges) == 0) { 2372 return; 2373 } 2374 $record = 0x00E5; 2375 $length = 2 + count($this->_merged_ranges) * 8; 2376 2377 $header = pack('vv', $record, $length); 2378 $data = pack('v', count($this->_merged_ranges)); 2379 foreach ($this->_merged_ranges as $range) { 2380 $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]); 2381 } 2382 $this->_append($header.$data); 2383 } 2384 2385 /** 2386 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet 2387 * references in a worksheet. 2388 * 2389 * Excel only stores references to external sheets that are used in formulas. 2390 * For simplicity we store references to all the sheets in the workbook 2391 * regardless of whether they are used or not. This reduces the overall 2392 * complexity and eliminates the need for a two way dialogue between the formula 2393 * parser the worksheet objects. 2394 * 2395 * @access private 2396 * @param integer $count The number of external sheet references in this worksheet 2397 */ 2398 function _storeExterncount($count) 2399 { 2400 $record = 0x0016; // Record identifier 2401 $length = 0x0002; // Number of bytes to follow 2402 2403 $header = pack("vv", $record, $length); 2404 $data = pack("v", $count); 2405 $this->_prepend($header.$data); 2406 } 2407 2408 /** 2409 * Writes the Excel BIFF EXTERNSHEET record. These references are used by 2410 * formulas. A formula references a sheet name via an index. Since we store a 2411 * reference to all of the external worksheets the EXTERNSHEET index is the same 2412 * as the worksheet index. 2413 * 2414 * @access private 2415 * @param string $sheetname The name of a external worksheet 2416 */ 2417 function _storeExternsheet($sheetname) 2418 { 2419 $record = 0x0017; // Record identifier 2420 2421 // References to the current sheet are encoded differently to references to 2422 // external sheets. 2423 // 2424 if ($this->name == $sheetname) { 2425 $sheetname = ''; 2426 $length = 0x02; // The following 2 bytes 2427 $cch = 1; // The following byte 2428 $rgch = 0x02; // Self reference 2429 } 2430 else { 2431 $length = 0x02 + strlen($sheetname); 2432 $cch = strlen($sheetname); 2433 $rgch = 0x03; // Reference to a sheet in the current workbook 2434 } 2435 2436 $header = pack("vv", $record, $length); 2437 $data = pack("CC", $cch, $rgch); 2438 $this->_prepend($header.$data.$sheetname); 2439 } 2440 2441 /** 2442 * Writes the Excel BIFF PANE record. 2443 * The panes can either be frozen or thawed (unfrozen). 2444 * Frozen panes are specified in terms of an integer number of rows and columns. 2445 * Thawed panes are specified in terms of Excel's units for rows and columns. 2446 * 2447 * @access private 2448 * @param array $panes This is the only parameter received and is composed of the following: 2449 * 0 => Vertical split position, 2450 * 1 => Horizontal split position 2451 * 2 => Top row visible 2452 * 3 => Leftmost column visible 2453 * 4 => Active pane 2454 */ 2455 function _storePanes($panes) 2456 { 2457 $y = $panes[0]; 2458 $x = $panes[1]; 2459 $rwTop = $panes[2]; 2460 $colLeft = $panes[3]; 2461 if (count($panes) > 4) { // if Active pane was received 2462 $pnnAct = $panes[4]; 2463 } 2464 else { 2465 $pnnAct = NULL; 2466 } 2467 $record = 0x0041; // Record identifier 2468 $length = 0x000A; // Number of bytes to follow 2469 2470 // Code specific to frozen or thawed panes. 2471 if ($this->_frozen) 2472 { 2473 // Set default values for $rwTop and $colLeft 2474 if (!isset($rwTop)) { 2475 $rwTop = $y; 2476 } 2477 if (!isset($colLeft)) { 2478 $colLeft = $x; 2479 } 2480 } 2481 else 2482 { 2483 // Set default values for $rwTop and $colLeft 2484 if (!isset($rwTop)) { 2485 $rwTop = 0; 2486 } 2487 if (!isset($colLeft)) { 2488 $colLeft = 0; 2489 } 2490 2491 // Convert Excel's row and column units to the internal units. 2492 // The default row height is 12.75 2493 // The default column width is 8.43 2494 // The following slope and intersection values were interpolated. 2495 // 2496 $y = 20*$y + 255; 2497 $x = 113.879*$x + 390; 2498 } 2499 2500 2501 // Determine which pane should be active. There is also the undocumented 2502 // option to override this should it be necessary: may be removed later. 2503 // 2504 if (!isset($pnnAct)) 2505 { 2506 if ($x != 0 and $y != 0) 2507 $pnnAct = 0; // Bottom right 2508 if ($x != 0 and $y == 0) 2509 $pnnAct = 1; // Top right 2510 if ($x == 0 and $y != 0) 2511 $pnnAct = 2; // Bottom left 2512 if ($x == 0 and $y == 0) 2513 $pnnAct = 3; // Top left 2514 } 2515 2516 $this->_active_pane = $pnnAct; // Used in _storeSelection 2517 2518 $header = pack("vv", $record, $length); 2519 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct); 2520 $this->_append($header.$data); 2521 } 2522 2523 /** 2524 * Store the page setup SETUP BIFF record. 2525 * 2526 * @access private 2527 */ 2528 function _storeSetup() 2529 { 2530 $record = 0x00A1; // Record identifier 2531 $length = 0x0022; // Number of bytes to follow 2532 2533 $iPaperSize = $this->_paper_size; // Paper size 2534 $iScale = $this->_print_scale; // Print scaling factor 2535 $iPageStart = 0x01; // Starting page number 2536 $iFitWidth = $this->_fit_width; // Fit to number of pages wide 2537 $iFitHeight = $this->_fit_height; // Fit to number of pages high 2538 $grbit = 0x00; // Option flags 2539 $iRes = 0x0258; // Print resolution 2540 $iVRes = 0x0258; // Vertical print resolution 2541 $numHdr = $this->_margin_head; // Header Margin 2542 $numFtr = $this->_margin_foot; // Footer Margin 2543 $iCopies = 0x01; // Number of copies 2544 2545 $fLeftToRight = 0x0; // Print over then down 2546 $fLandscape = $this->_orientation; // Page orientation 2547 $fNoPls = 0x0; // Setup not read from printer 2548 $fNoColor = 0x0; // Print black and white 2549 $fDraft = 0x0; // Print draft quality 2550 $fNotes = 0x0; // Print notes 2551 $fNoOrient = 0x0; // Orientation not set 2552 $fUsePage = 0x0; // Use custom starting page 2553 2554 $grbit = $fLeftToRight; 2555 $grbit |= $fLandscape << 1; 2556 $grbit |= $fNoPls << 2; 2557 $grbit |= $fNoColor << 3; 2558 $grbit |= $fDraft << 4; 2559 $grbit |= $fNotes << 5; 2560 $grbit |= $fNoOrient << 6; 2561 $grbit |= $fUsePage << 7; 2562 2563 $numHdr = pack("d", $numHdr); 2564 $numFtr = pack("d", $numFtr); 2565 if ($this->_byte_order) // if it's Big Endian 2566 { 2567 $numHdr = strrev($numHdr); 2568 $numFtr = strrev($numFtr); 2569 } 2570 2571 $header = pack("vv", $record, $length); 2572 $data1 = pack("vvvvvvvv", $iPaperSize, 2573 $iScale, 2574 $iPageStart, 2575 $iFitWidth, 2576 $iFitHeight, 2577 $grbit, 2578 $iRes, 2579 $iVRes); 2580 $data2 = $numHdr.$numFtr; 2581 $data3 = pack("v", $iCopies); 2582 $this->_prepend($header.$data1.$data2.$data3); 2583 } 2584 2585 /** 2586 * Store the header caption BIFF record. 2587 * 2588 * @access private 2589 */ 2590 function _storeHeader() 2591 { 2592 $record = 0x0014; // Record identifier 2593 2594 $str = $this->_header; // header string 2595 $cch = strlen($str); // Length of header string 2596 if ($this->_BIFF_version == 0x0600) { 2597 $encoding = 0x0; // TODO: Unicode support 2598 $length = 3 + $cch; // Bytes to follow 2599 } 2600 else { 2601 $length = 1 + $cch; // Bytes to follow 2602 } 2603 $header = pack("vv", $record, $length); 2604 if ($this->_BIFF_version == 0x0600) { 2605 $data = pack("vC", $cch, $encoding); 2606 } 2607 else { 2608 $data = pack("C", $cch); 2609 } 2610 2611 $this->_append($header.$data.$str); 2612 } 2613 2614 /** 2615 * Store the footer caption BIFF record. 2616 * 2617 * @access private 2618 */ 2619 function _storeFooter() 2620 { 2621 $record = 0x0015; // Record identifier 2622 2623 $str = $this->_footer; // Footer string 2624 $cch = strlen($str); // Length of footer string 2625 if ($this->_BIFF_version == 0x0600) { 2626 $encoding = 0x0; // TODO: Unicode support 2627 $length = 3 + $cch; // Bytes to follow 2628 } 2629 else { 2630 $length = 1 + $cch; 2631 } 2632 $header = pack("vv", $record, $length); 2633 if ($this->_BIFF_version == 0x0600) { 2634 $data = pack("vC", $cch, $encoding); 2635 } 2636 else { 2637 $data = pack("C", $cch); 2638 } 2639 2640 $this->_append($header.$data.$str); 2641 } 2642 2643 /** 2644 * Store the horizontal centering HCENTER BIFF record. 2645 * 2646 * @access private 2647 */ 2648 function _storeHcenter() 2649 { 2650 $record = 0x0083; // Record identifier 2651 $length = 0x0002; // Bytes to follow 2652 2653 $fHCenter = $this->_hcenter; // Horizontal centering 2654 2655 $header = pack("vv", $record, $length); 2656 $data = pack("v", $fHCenter); 2657 2658 $this->_append($header.$data); 2659 } 2660 2661 /** 2662 * Store the vertical centering VCENTER BIFF record. 2663 * 2664 * @access private 2665 */ 2666 function _storeVcenter() 2667 { 2668 $record = 0x0084; // Record identifier 2669 $length = 0x0002; // Bytes to follow 2670 2671 $fVCenter = $this->_vcenter; // Horizontal centering 2672 2673 $header = pack("vv", $record, $length); 2674 $data = pack("v", $fVCenter); 2675 $this->_append($header.$data); 2676 } 2677 2678 /** 2679 * Store the LEFTMARGIN BIFF record. 2680 * 2681 * @access private 2682 */ 2683 function _storeMarginLeft() 2684 { 2685 $record = 0x0026; // Record identifier 2686 $length = 0x0008; // Bytes to follow 2687 2688 $margin = $this->_margin_left; // Margin in inches 2689 2690 $header = pack("vv", $record, $length); 2691 $data = pack("d", $margin); 2692 if ($this->_byte_order) // if it's Big Endian 2693 { 2694 $data = strrev($data); 2695 } 2696 2697 $this->_append($header.$data); 2698 } 2699 2700 /** 2701 * Store the RIGHTMARGIN BIFF record. 2702 * 2703 * @access private 2704 */ 2705 function _storeMarginRight() 2706 { 2707 $record = 0x0027; // Record identifier 2708 $length = 0x0008; // Bytes to follow 2709 2710 $margin = $this->_margin_right; // Margin in inches 2711 2712 $header = pack("vv", $record, $length); 2713 $data = pack("d", $margin); 2714 if ($this->_byte_order) // if it's Big Endian 2715 { 2716 $data = strrev($data); 2717 } 2718 2719 $this->_append($header.$data); 2720 } 2721 2722 /** 2723 * Store the TOPMARGIN BIFF record. 2724 * 2725 * @access private 2726 */ 2727 function _storeMarginTop() 2728 { 2729 $record = 0x0028; // Record identifier 2730 $length = 0x0008; // Bytes to follow 2731 2732 $margin = $this->_margin_top; // Margin in inches 2733 2734 $header = pack("vv", $record, $length); 2735 $data = pack("d", $margin); 2736 if ($this->_byte_order) // if it's Big Endian 2737 { 2738 $data = strrev($data); 2739 } 2740 2741 $this->_append($header.$data); 2742 } 2743 2744 /** 2745 * Store the BOTTOMMARGIN BIFF record. 2746 * 2747 * @access private 2748 */ 2749 function _storeMarginBottom() 2750 { 2751 $record = 0x0029; // Record identifier 2752 $length = 0x0008; // Bytes to follow 2753 2754 $margin = $this->_margin_bottom; // Margin in inches 2755 2756 $header = pack("vv", $record, $length); 2757 $data = pack("d", $margin); 2758 if ($this->_byte_order) // if it's Big Endian 2759 { 2760 $data = strrev($data); 2761 } 2762 2763 $this->_append($header.$data); 2764 } 2765 2766 /** 2767 * Merges the area given by its arguments. 2768 * This is an Excel97/2000 method. It is required to perform more complicated 2769 * merging than the normal setAlign('merge'). 2770 * 2771 * @access public 2772 * @param integer $first_row First row of the area to merge 2773 * @param integer $first_col First column of the area to merge 2774 * @param integer $last_row Last row of the area to merge 2775 * @param integer $last_col Last column of the area to merge 2776 */ 2777 function mergeCells($first_row, $first_col, $last_row, $last_col) 2778 { 2779 $record = 0x00E5; // Record identifier 2780 $length = 0x000A; // Bytes to follow 2781 $cref = 1; // Number of refs 2782 2783 // Swap last row/col for first row/col as necessary 2784 if ($first_row > $last_row) { 2785 list($first_row, $last_row) = array($last_row, $first_row); 2786 } 2787 2788 if ($first_col > $last_col) { 2789 list($first_col, $last_col) = array($last_col, $first_col); 2790 } 2791 2792 $header = pack("vv", $record, $length); 2793 $data = pack("vvvvv", $cref, $first_row, $last_row, 2794 $first_col, $last_col); 2795 2796 $this->_append($header.$data); 2797 } 2798 2799 /** 2800 * Write the PRINTHEADERS BIFF record. 2801 * 2802 * @access private 2803 */ 2804 function _storePrintHeaders() 2805 { 2806 $record = 0x002a; // Record identifier 2807 $length = 0x0002; // Bytes to follow 2808 2809 $fPrintRwCol = $this->_print_headers; // Boolean flag 2810 2811 $header = pack("vv", $record, $length); 2812 $data = pack("v", $fPrintRwCol); 2813 $this->_prepend($header.$data); 2814 } 2815 2816 /** 2817 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the 2818 * GRIDSET record. 2819 * 2820 * @access private 2821 */ 2822 function _storePrintGridlines() 2823 { 2824 $record = 0x002b; // Record identifier 2825 $length = 0x0002; // Bytes to follow 2826 2827 $fPrintGrid = $this->_print_gridlines; // Boolean flag 2828 2829 $header = pack("vv", $record, $length); 2830 $data = pack("v", $fPrintGrid); 2831 $this->_prepend($header.$data); 2832 } 2833 2834 /** 2835 * Write the GRIDSET BIFF record. Must be used in conjunction with the 2836 * PRINTGRIDLINES record. 2837 * 2838 * @access private 2839 */ 2840 function _storeGridset() 2841 { 2842 $record = 0x0082; // Record identifier 2843 $length = 0x0002; // Bytes to follow 2844 2845 $fGridSet = !($this->_print_gridlines); // Boolean flag 2846 2847 $header = pack("vv", $record, $length); 2848 $data = pack("v", $fGridSet); 2849 $this->_prepend($header.$data); 2850 } 2851 2852 /** 2853 * Write the GUTS BIFF record. This is used to configure the gutter margins 2854 * where Excel outline symbols are displayed. The visibility of the gutters is 2855 * controlled by a flag in WSBOOL. 2856 * 2857 * @see _storeWsbool() 2858 * @access private 2859 */ 2860 function _storeGuts() 2861 { 2862 $record = 0x0080; // Record identifier 2863 $length = 0x0008; // Bytes to follow 2864 2865 $dxRwGut = 0x0000; // Size of row gutter 2866 $dxColGut = 0x0000; // Size of col gutter 2867 2868 $row_level = $this->_outline_row_level; 2869 $col_level = 0; 2870 2871 // Calculate the maximum column outline level. The equivalent calculation 2872 // for the row outline level is carried out in setRow(). 2873 foreach ($this->_colinfo as $colinfo) 2874 { 2875 // Skip cols without outline level info. 2876 if (count($col_level) >= 6) { 2877 $col_level = max($colinfo[5], $col_level); 2878 } 2879 } 2880 2881 // Set the limits for the outline levels (0 <= x <= 7). 2882 $col_level = max(0, min($col_level, 7)); 2883 2884 // The displayed level is one greater than the max outline levels 2885 if ($row_level) { 2886 $row_level++; 2887 } 2888 if ($col_level) { 2889 $col_level++; 2890 } 2891 2892 $header = pack("vv", $record, $length); 2893 $data = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level); 2894 2895 $this->_prepend($header.$data); 2896 } 2897 2898 2899 /** 2900 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction 2901 * with the SETUP record. 2902 * 2903 * @access private 2904 */ 2905 function _storeWsbool() 2906 { 2907 $record = 0x0081; // Record identifier 2908 $length = 0x0002; // Bytes to follow 2909 $grbit = 0x0000; 2910 2911 // The only option that is of interest is the flag for fit to page. So we 2912 // set all the options in one go. 2913 // 2914 /*if ($this->_fit_page) { 2915 $grbit = 0x05c1; 2916 } 2917 else { 2918 $grbit = 0x04c1; 2919 }*/ 2920 // Set the option flags 2921 $grbit |= 0x0001; // Auto page breaks visible 2922 if ($this->_outline_style) { 2923 $grbit |= 0x0020; // Auto outline styles 2924 } 2925 if ($this->_outline_below) { 2926 $grbit |= 0x0040; // Outline summary below 2927 } 2928 if ($this->_outline_right) { 2929 $grbit |= 0x0080; // Outline summary right 2930 } 2931 if ($this->_fit_page) { 2932 $grbit |= 0x0100; // Page setup fit to page 2933 } 2934 if ($this->_outline_on) { 2935 $grbit |= 0x0400; // Outline symbols displayed 2936 } 2937 2938 $header = pack("vv", $record, $length); 2939 $data = pack("v", $grbit); 2940 $this->_prepend($header.$data); 2941 } 2942 2943 /** 2944 * Write the HORIZONTALPAGEBREAKS BIFF record. 2945 * 2946 * @access private 2947 */ 2948 function _storeHbreak() 2949 { 2950 // Return if the user hasn't specified pagebreaks 2951 if (empty($this->_hbreaks)) { 2952 return; 2953 } 2954 2955 // Sort and filter array of page breaks 2956 $breaks = $this->_hbreaks; 2957 sort($breaks, SORT_NUMERIC); 2958 if ($breaks[0] == 0) { // don't use first break if it's 0 2959 array_shift($breaks); 2960 } 2961 2962 $record = 0x001b; // Record identifier 2963 $cbrk = count($breaks); // Number of page breaks 2964 $length = 2 + 6*$cbrk; // Bytes to follow 2965 2966 $header = pack("vv", $record, $length); 2967 $data = pack("v", $cbrk); 2968 2969 // Append each page break 2970 foreach($breaks as $break) { 2971 $data .= pack("vvv", $break, 0x0000, 0x00ff); 2972 } 2973 2974 $this->_prepend($header.$data); 2975 } 2976 2977 2978 /** 2979 * Write the VERTICALPAGEBREAKS BIFF record. 2980 * 2981 * @access private 2982 */ 2983 function _storeVbreak() 2984 { 2985 // Return if the user hasn't specified pagebreaks 2986 if (empty($this->_vbreaks)) { 2987 return; 2988 } 2989 2990 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit. 2991 // It is slightly higher in Excel 97/200, approx. 1026 2992 $breaks = array_slice($this->_vbreaks,0,1000); 2993 2994 // Sort and filter array of page breaks 2995 sort($breaks, SORT_NUMERIC); 2996 if ($breaks[0] == 0) { // don't use first break if it's 0 2997 array_shift($breaks); 2998 } 2999 3000 $record = 0x001a; // Record identifier 3001 $cbrk = count($breaks); // Number of page breaks 3002 $length = 2 + 6*$cbrk; // Bytes to follow 3003 3004 $header = pack("vv", $record, $length); 3005 $data = pack("v", $cbrk); 3006 3007 // Append each page break 3008 foreach ($breaks as $break) { 3009 $data .= pack("vvv", $break, 0x0000, 0xffff); 3010 } 3011 3012 $this->_prepend($header.$data); 3013 } 3014 3015 /** 3016 * Set the Biff PROTECT record to indicate that the worksheet is protected. 3017 * 3018 * @access private 3019 */ 3020 function _storeProtect() 3021 { 3022 // Exit unless sheet protection has been specified 3023 if ($this->_protect == 0) { 3024 return; 3025 } 3026 3027 $record = 0x0012; // Record identifier 3028 $length = 0x0002; // Bytes to follow 3029 3030 $fLock = $this->_protect; // Worksheet is protected 3031 3032 $header = pack("vv", $record, $length); 3033 $data = pack("v", $fLock); 3034 3035 $this->_prepend($header.$data); 3036 } 3037 3038 /** 3039 * Write the worksheet PASSWORD record. 3040 * 3041 * @access private 3042 */ 3043 function _storePassword() 3044 { 3045 // Exit unless sheet protection and password have been specified 3046 if (($this->_protect == 0) or (!isset($this->_password))) { 3047 return; 3048 } 3049 3050 $record = 0x0013; // Record identifier 3051 $length = 0x0002; // Bytes to follow 3052 3053 $wPassword = $this->_password; // Encoded password 3054 3055 $header = pack("vv", $record, $length); 3056 $data = pack("v", $wPassword); 3057 3058 $this->_prepend($header.$data); 3059 } 3060 3061 3062 /** 3063 * Insert a 24bit bitmap image in a worksheet. 3064 * 3065 * @access public 3066 * @param integer $row The row we are going to insert the bitmap into 3067 * @param integer $col The column we are going to insert the bitmap into 3068 * @param string $bitmap The bitmap filename 3069 * @param integer $x The horizontal position (offset) of the image inside the cell. 3070 * @param integer $y The vertical position (offset) of the image inside the cell. 3071 * @param integer $scale_x The horizontal scale 3072 * @param integer $scale_y The vertical scale 3073 */ 3074 function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) 3075 { 3076 $bitmap_array = $this->_processBitmap($bitmap); 3077 if ($this->isError($bitmap_array)) 3078 { 3079 $this->writeString($row, $col, $bitmap_array->getMessage()); 3080 return; 3081 } 3082 list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap); 3083 3084 // Scale the frame of the image. 3085 $width *= $scale_x; 3086 $height *= $scale_y; 3087 3088 // Calculate the vertices of the image and write the OBJ record 3089 $this->_positionImage($col, $row, $x, $y, $width, $height); 3090 3091 // Write the IMDATA record to store the bitmap data 3092 $record = 0x007f; 3093 $length = 8 + $size; 3094 $cf = 0x09; 3095 $env = 0x01; 3096 $lcb = $size; 3097 3098 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb); 3099 $this->_append($header.$data); 3100 } 3101 3102 /** 3103 * Calculate the vertices that define the position of the image as required by 3104 * the OBJ record. 3105 * 3106 * +------------+------------+ 3107 * | A | B | 3108 * +-----+------------+------------+ 3109 * | |(x1,y1) | | 3110 * | 1 |(A1)._______|______ | 3111 * | | | | | 3112 * | | | | | 3113 * +-----+----| BITMAP |-----+ 3114 * | | | | | 3115 * | 2 | |______________. | 3116 * | | | (B2)| 3117 * | | | (x2,y2)| 3118 * +---- +------------+------------+ 3119 * 3120 * Example of a bitmap that covers some of the area from cell A1 to cell B2. 3121 * 3122 * Based on the width and height of the bitmap we need to calculate 8 vars: 3123 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. 3124 * The width and height of the cells are also variable and have to be taken into 3125 * account. 3126 * The values of $col_start and $row_start are passed in from the calling 3127 * function. The values of $col_end and $row_end are calculated by subtracting 3128 * the width and height of the bitmap from the width and height of the 3129 * underlying cells. 3130 * The vertices are expressed as a percentage of the underlying cell width as 3131 * follows (rhs values are in pixels): 3132 * 3133 * x1 = X / W *1024 3134 * y1 = Y / H *256 3135 * x2 = (X-1) / W *1024 3136 * y2 = (Y-1) / H *256 3137 * 3138 * Where: X is distance from the left side of the underlying cell 3139 * Y is distance from the top of the underlying cell 3140 * W is the width of the cell 3141 * H is the height of the cell 3142 * 3143 * @access private 3144 * @note the SDK incorrectly states that the height should be expressed as a 3145 * percentage of 1024. 3146 * @param integer $col_start Col containing upper left corner of object 3147 * @param integer $row_start Row containing top left corner of object 3148 * @param integer $x1 Distance to left side of object 3149 * @param integer $y1 Distance to top of object 3150 * @param integer $width Width of image frame 3151 * @param integer $height Height of image frame 3152 */ 3153 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height) 3154 { 3155 // Initialise end cell to the same as the start cell 3156 $col_end = $col_start; // Col containing lower right corner of object 3157 $row_end = $row_start; // Row containing bottom right corner of object 3158 3159 // Zero the specified offset if greater than the cell dimensions 3160 if ($x1 >= $this->_sizeCol($col_start)) 3161 { 3162 $x1 = 0; 3163 } 3164 if ($y1 >= $this->_sizeRow($row_start)) 3165 { 3166 $y1 = 0; 3167 } 3168 3169 $width = $width + $x1 -1; 3170 $height = $height + $y1 -1; 3171 3172 // Subtract the underlying cell widths to find the end cell of the image 3173 while ($width >= $this->_sizeCol($col_end)) { 3174 $width -= $this->_sizeCol($col_end); 3175 $col_end++; 3176 } 3177 3178 // Subtract the underlying cell heights to find the end cell of the image 3179 while ($height >= $this->_sizeRow($row_end)) { 3180 $height -= $this->_sizeRow($row_end); 3181 $row_end++; 3182 } 3183 3184 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell 3185 // with zero eight or width. 3186 // 3187 if ($this->_sizeCol($col_start) == 0) 3188 return; 3189 if ($this->_sizeCol($col_end) == 0) 3190 return; 3191 if ($this->_sizeRow($row_start) == 0) 3192 return; 3193 if ($this->_sizeRow($row_end) == 0) 3194 return; 3195 3196 // Convert the pixel values to the percentage value expected by Excel 3197 $x1 = $x1 / $this->_sizeCol($col_start) * 1024; 3198 $y1 = $y1 / $this->_sizeRow($row_start) * 256; 3199 $x2 = $width / $this->_sizeCol($col_end) * 1024; // Distance to right side of object 3200 $y2 = $height / $this->_sizeRow($row_end) * 256; // Distance to bottom of object 3201 3202 $this->_storeObjPicture( $col_start, $x1, 3203 $row_start, $y1, 3204 $col_end, $x2, 3205 $row_end, $y2 3206 ); 3207 } 3208 3209 /** 3210 * Convert the width of a cell from user's units to pixels. By interpolation 3211 * the relationship is: y = 7x +5. If the width hasn't been set by the user we 3212 * use the default value. If the col is hidden we use a value of zero. 3213 * 3214 * @access private 3215 * @param integer $col The column 3216 * @return integer The width in pixels 3217 */ 3218 function _sizeCol($col) 3219 { 3220 // Look up the cell value to see if it has been changed 3221 if (isset($this->col_sizes[$col])) { 3222 if ($this->col_sizes[$col] == 0) { 3223 return(0); 3224 } 3225 else { 3226 return(floor(7 * $this->col_sizes[$col] + 5)); 3227 } 3228 } 3229 else { 3230 return(64); 3231 } 3232 } 3233 3234 /** 3235 * Convert the height of a cell from user's units to pixels. By interpolation 3236 * the relationship is: y = 4/3x. If the height hasn't been set by the user we 3237 * use the default value. If the row is hidden we use a value of zero. (Not 3238 * possible to hide row yet). 3239 * 3240 * @access private 3241 * @param integer $row The row 3242 * @return integer The width in pixels 3243 */ 3244 function _sizeRow($row) 3245 { 3246 // Look up the cell value to see if it has been changed 3247 if (isset($this->row_sizes[$row])) { 3248 if ($this->row_sizes[$row] == 0) { 3249 return(0); 3250 } 3251 else { 3252 return(floor(4/3 * $this->row_sizes[$row])); 3253 } 3254 } 3255 else { 3256 return(17); 3257 } 3258 } 3259 3260 /** 3261 * Store the OBJ record that precedes an IMDATA record. This could be generalise 3262 * to support other Excel objects. 3263 * 3264 * @access private 3265 * @param integer $colL Column containing upper left corner of object 3266 * @param integer $dxL Distance from left side of cell 3267 * @param integer $rwT Row containing top left corner of object 3268 * @param integer $dyT Distance from top of cell 3269 * @param integer $colR Column containing lower right corner of object 3270 * @param integer $dxR Distance from right of cell 3271 * @param integer $rwB Row containing bottom right corner of object 3272 * @param integer $dyB Distance from bottom of cell 3273 */ 3274 function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB) 3275 { 3276 $record = 0x005d; // Record identifier 3277 $length = 0x003c; // Bytes to follow 3278 3279 $cObj = 0x0001; // Count of objects in file (set to 1) 3280 $OT = 0x0008; // Object type. 8 = Picture 3281 $id = 0x0001; // Object ID 3282 $grbit = 0x0614; // Option flags 3283 3284 $cbMacro = 0x0000; // Length of FMLA structure 3285 $Reserved1 = 0x0000; // Reserved 3286 $Reserved2 = 0x0000; // Reserved 3287 3288 $icvBack = 0x09; // Background colour 3289 $icvFore = 0x09; // Foreground colour 3290 $fls = 0x00; // Fill pattern 3291 $fAuto = 0x00; // Automatic fill 3292 $icv = 0x08; // Line colour 3293 $lns = 0xff; // Line style 3294 $lnw = 0x01; // Line weight 3295 $fAutoB = 0x00; // Automatic border 3296 $frs = 0x0000; // Frame style 3297 $cf = 0x0009; // Image format, 9 = bitmap 3298 $Reserved3 = 0x0000; // Reserved 3299 $cbPictFmla = 0x0000; // Length of FMLA structure 3300 $Reserved4 = 0x0000; // Reserved 3301 $grbit2 = 0x0001; // Option flags 3302 $Reserved5 = 0x0000; // Reserved 3303 3304 3305 $header = pack("vv", $record, $length); 3306 $data = pack("V", $cObj); 3307 $data .= pack("v", $OT); 3308 $data .= pack("v", $id); 3309 $data .= pack("v", $grbit); 3310 $data .= pack("v", $colL); 3311 $data .= pack("v", $dxL); 3312 $data .= pack("v", $rwT); 3313 $data .= pack("v", $dyT); 3314 $data .= pack("v", $colR); 3315 $data .= pack("v", $dxR); 3316 $data .= pack("v", $rwB); 3317 $data .= pack("v", $dyB); 3318 $data .= pack("v", $cbMacro); 3319 $data .= pack("V", $Reserved1); 3320 $data .= pack("v", $Reserved2); 3321 $data .= pack("C", $icvBack); 3322 $data .= pack("C", $icvFore); 3323 $data .= pack("C", $fls); 3324 $data .= pack("C", $fAuto); 3325 $data .= pack("C", $icv); 3326 $data .= pack("C", $lns); 3327 $data .= pack("C", $lnw); 3328 $data .= pack("C", $fAutoB); 3329 $data .= pack("v", $frs); 3330 $data .= pack("V", $cf); 3331 $data .= pack("v", $Reserved3); 3332 $data .= pack("v", $cbPictFmla); 3333 $data .= pack("v", $Reserved4); 3334 $data .= pack("v", $grbit2); 3335 $data .= pack("V", $Reserved5); 3336 3337 $this->_append($header.$data); 3338 } 3339 3340 /** 3341 * Convert a 24 bit bitmap into the modified internal format used by Windows. 3342 * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the 3343 * MSDN library. 3344 * 3345 * @access private 3346 * @param string $bitmap The bitmap to process 3347 * @return array Array with data and properties of the bitmap 3348 */ 3349 function _processBitmap($bitmap) 3350 { 3351 // Open file. 3352 $bmp_fd = @fopen($bitmap,"rb"); 3353 if (!$bmp_fd) { 3354 $this->raiseError("Couldn't import $bitmap"); 3355 } 3356 3357 // Slurp the file into a string. 3358 $data = fread($bmp_fd, filesize($bitmap)); 3359 3360 // Check that the file is big enough to be a bitmap. 3361 if (strlen($data) <= 0x36) { 3362 $this->raiseError("$bitmap doesn't contain enough data.\n"); 3363 } 3364 3365 // The first 2 bytes are used to identify the bitmap. 3366 $identity = unpack("A2", $data); 3367 if ($identity[''] != "BM") { 3368 $this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n"); 3369 } 3370 3371 // Remove bitmap data: ID. 3372 $data = substr($data, 2); 3373 3374 // Read and remove the bitmap size. This is more reliable than reading 3375 // the data size at offset 0x22. 3376 // 3377 $size_array = unpack("V", substr($data, 0, 4)); 3378 $size = $size_array['']; 3379 $data = substr($data, 4); 3380 $size -= 0x36; // Subtract size of bitmap header. 3381 $size += 0x0C; // Add size of BIFF header. 3382 3383 // Remove bitmap data: reserved, offset, header length. 3384 $data = substr($data, 12); 3385 3386 // Read and remove the bitmap width and height. Verify the sizes. 3387 $width_and_height = unpack("V2", substr($data, 0, 8)); 3388 $width = $width_and_height[1]; 3389 $height = $width_and_height[2]; 3390 $data = substr($data, 8); 3391 if ($width > 0xFFFF) { 3392 $this->raiseError("$bitmap: largest image width supported is 65k.\n"); 3393 } 3394 if ($height > 0xFFFF) { 3395 $this->raiseError("$bitmap: largest image height supported is 65k.\n"); 3396 } 3397 3398 // Read and remove the bitmap planes and bpp data. Verify them. 3399 $planes_and_bitcount = unpack("v2", substr($data, 0, 4)); 3400 $data = substr($data, 4); 3401 if ($planes_and_bitcount[2] != 24) { // Bitcount 3402 $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n"); 3403 } 3404 if ($planes_and_bitcount[1] != 1) { 3405 $this->raiseError("$bitmap: only 1 plane nupported in bitmap image.\n"); 3406 } 3407 3408 // Read and remove the bitmap compression. Verify compression. 3409 $compression = unpack("V", substr($data, 0, 4)); 3410 $data = substr($data, 4); 3411 3412 //$compression = 0; 3413 if ($compression[""] != 0) { 3414 $this->raiseError("$bitmap: compression not supported in bitmap image.\n"); 3415 } 3416 3417 // Remove bitmap data: data size, hres, vres, colours, imp. colours. 3418 $data = substr($data, 20); 3419 3420 // Add the BITMAPCOREHEADER data 3421 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18); 3422 $data = $header . $data; 3423 3424 return (array($width, $height, $size, $data)); 3425 } 3426 3427 /** 3428 * Store the window zoom factor. This should be a reduced fraction but for 3429 * simplicity we will store all fractions with a numerator of 100. 3430 * 3431 * @access private 3432 */ 3433 function _storeZoom() 3434 { 3435 // If scale is 100 we don't need to write a record 3436 if ($this->_zoom == 100) { 3437 return; 3438 } 3439 3440 $record = 0x00A0; // Record identifier 3441 $length = 0x0004; // Bytes to follow 3442 3443 $header = pack("vv", $record, $length); 3444 $data = pack("vv", $this->_zoom, 100); 3445 $this->_append($header.$data); 3446 } 3447 3448 /** 3449 * FIXME: add comments 3450 */ 3451 function setValidation($row1, $col1, $row2, $col2, &$validator) 3452 { 3453 $this->_dv[] = $validator->_getData() . 3454 pack("vvvvv", 1, $row1, $row2, $col1, $col2); 3455 } 3456 3457 /** 3458 * Store the DVAL and DV records. 3459 * 3460 * @access private 3461 */ 3462 function _storeDataValidity() 3463 { 3464 $record = 0x01b2; // Record identifier 3465 $length = 0x0012; // Bytes to follow 3466 3467 $grbit = 0x0002; // Prompt box at cell, no cached validity data at DV records 3468 $horPos = 0x00000000; // Horizontal position of prompt box, if fixed position 3469 $verPos = 0x00000000; // Vertical position of prompt box, if fixed position 3470 $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible 3471 3472 $header = pack('vv', $record, $length); 3473 $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, 3474 count($this->_dv)); 3475 $this->_append($header.$data); 3476 3477 $record = 0x01be; // Record identifier 3478 foreach($this->_dv as $dv) 3479 { 3480 $length = strlen($dv); // Bytes to follow 3481 $header = pack("vv", $record, $length); 3482 $this->_append($header.$dv); 3483 } 3484 } 3485} 3486