| [ Index ] |
PHP Cross Reference of Moodle 1.9.3 [Build 15-Oct-2008] |
[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])) {