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