| [ 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::WriteExcel: A library for generating Excel Spreadsheets 18 * Copyright (C) 2002 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 ('Parser.php'); 36 require_once ('BIFFwriter.php'); 37 38 /** 39 * Class for generating Excel Spreadsheets 40 * 41 * @author Xavier Noguer <xnoguer@rezebra.com> 42 * @package Spreadsheet_WriteExcel 43 */ 44 45 class Worksheet extends BIFFwriter 46 { 47 48 /** 49 * Constructor 50 * 51 * @param string $name The name of the new worksheet 52 * @param integer $index The index of the new worksheet 53 * @param mixed &$activesheet The current activesheet of the workbook we belong to 54 * @param mixed &$firstsheet The first worksheet in the workbook we belong to 55 * @param mixed &$url_format The default format for hyperlinks 56 * @param mixed &$parser The formula parser created for the Workbook 57 */ 58 function Worksheet($name,$index,&$activesheet,&$firstsheet,&$url_format,&$parser) 59 { 60 $this->BIFFwriter(); // It needs to call its parent's constructor explicitly 61 $rowmax = 65536; // 16384 in Excel 5 62 $colmax = 256; 63 $strmax = 255; 64 65 $this->name = $name; 66 $this->index = $index; 67 $this->activesheet = &$activesheet; 68 $this->firstsheet = &$firstsheet; 69 $this->_url_format = $url_format; 70 $this->_parser = &$parser; 71 72 $this->ext_sheets = array(); 73 $this->_using_tmpfile = 1; 74 $this->_filehandle = ""; 75 $this->fileclosed = 0; 76 $this->offset = 0; 77 $this->xls_rowmax = $rowmax; 78 $this->xls_colmax = $colmax; 79 $this->xls_strmax = $strmax; 80 $this->dim_rowmin = $rowmax +1; 81 $this->dim_rowmax = 0; 82 $this->dim_colmin = $colmax +1; 83 $this->dim_colmax = 0; 84 $this->colinfo = array(); 85 $this->_selection = array(0,0,0,0); 86 $this->_panes = array(); 87 $this->_active_pane = 3; 88 $this->_frozen = 0; 89 $this->selected = 0; 90 91 $this->_paper_size = 0x0; 92 $this->_orientation = 0x1; 93 $this->_header = ''; 94 $this->_footer = ''; 95 $this->_hcenter = 0; 96 $this->_vcenter = 0; 97 $this->_margin_head = 0.50; 98 $this->_margin_foot = 0.50; 99 $this->_margin_left = 0.75; 100 $this->_margin_right = 0.75; 101 $this->_margin_top = 1.00; 102 $this->_margin_bottom = 1.00; 103 104 $this->_title_rowmin = NULL; 105 $this->_title_rowmax = NULL; 106 $this->_title_colmin = NULL; 107 $this->_title_colmax = NULL; 108 $this->_print_rowmin = NULL; 109 $this->_print_rowmax = NULL; 110 $this->_print_colmin = NULL; 111 $this->_print_colmax = NULL; 112 113 $this->_print_gridlines = 1; 114 $this->_print_headers = 0; 115 116 $this->_fit_page = 0; 117 $this->_fit_width = 0; 118 $this->_fit_height = 0; 119 120 $this->_hbreaks = array(); 121 $this->_vbreaks = array(); 122 123 $this->_protect = 0; 124 $this->_password = NULL; 125 126 $this->col_sizes = array(); 127 $this->row_sizes = array(); 128 129 $this->_zoom = 100; 130 $this->_print_scale = 100; 131 132 $this->_initialize(); 133 } 134 135 /** 136 * Open a tmp file to store the majority of the Worksheet data. If this fails, 137 * for example due to write permissions, store the data in memory. This can be 138 * slow for large files. 139 */ 140 function _initialize() 141 { 142 // Open tmp file for storing Worksheet data 143 $fh = tmpfile(); 144 if ( $fh) { 145 // Store filehandle 146 $this->_filehandle = $fh; 147 } 148 else { 149 // If tmpfile() fails store data in memory 150 $this->_using_tmpfile = 0; 151 } 152 } 153 154 /** 155 * Add data to the beginning of the workbook (note the reverse order) 156 * and to the end of the workbook. 157 * 158 * @access public 159 * @see Workbook::store_workbook() 160 * @param array $sheetnames The array of sheetnames from the Workbook this 161 * worksheet belongs to 162 */ 163 function close($sheetnames) 164 { 165 $num_sheets = count($sheetnames); 166 167 /*********************************************** 168 * Prepend in reverse order!! 169 */ 170 171 // Prepend the sheet dimensions 172 $this->_store_dimensions(); 173 174 // Prepend the sheet password 175 $this->_store_password(); 176 177 // Prepend the sheet protection 178 $this->_store_protect(); 179 180 // Prepend the page setup 181 $this->_store_setup(); 182 183 // Prepend the bottom margin 184 $this->_store_margin_bottom(); 185 186 // Prepend the top margin 187 $this->_store_margin_top(); 188 189 // Prepend the right margin 190 $this->_store_margin_right(); 191 192 // Prepend the left margin 193 $this->_store_margin_left(); 194 195 // Prepend the page vertical centering 196 $this->store_vcenter(); 197 198 // Prepend the page horizontal centering 199 $this->store_hcenter(); 200 201 // Prepend the page footer 202 $this->store_footer(); 203 204 // Prepend the page header 205 $this->store_header(); 206 207 // Prepend the vertical page breaks 208 $this->_store_vbreak(); 209 210 // Prepend the horizontal page breaks 211 $this->_store_hbreak(); 212 213 // Prepend WSBOOL 214 $this->_store_wsbool(); 215 216 // Prepend GRIDSET 217 $this->_store_gridset(); 218 219 // Prepend PRINTGRIDLINES 220 $this->_store_print_gridlines(); 221 222 // Prepend PRINTHEADERS 223 $this->_store_print_headers(); 224 225 // Prepend EXTERNSHEET references 226 for ($i = $num_sheets; $i > 0; $i--) { 227 $sheetname = $sheetnames[$i-1]; 228 $this->_store_externsheet($sheetname); 229 } 230 231 // Prepend the EXTERNCOUNT of external references. 232 $this->_store_externcount($num_sheets); 233 234 // Prepend the COLINFO records if they exist 235 if (!empty($this->colinfo)){ 236 for($i=0; $i < count($this->colinfo); $i++) 237 { 238 $this->_store_colinfo($this->colinfo[$i]); 239 } 240 $this->_store_defcol(); 241 } 242 243 // Prepend the BOF record 244 $this->_store_bof(0x0010); 245 246 /* 247 * End of prepend. Read upwards from here. 248 ***********************************************/ 249 250 // Append 251 $this->_store_window2(); 252 $this->_store_zoom(); 253 if(!empty($this->_panes)) 254 $this->_store_panes($this->_panes); 255 $this->_store_selection($this->_selection); 256 $this->_store_eof(); 257 } 258 259 /** 260 * Retrieve the worksheet name. This is usefull when creating worksheets 261 * without a name. 262 * 263 * @access public 264 * @return string The worksheet's name 265 */ 266 function get_name() 267 { 268 return($this->name); 269 } 270 271 /** 272 * Retrieves data from memory in one chunk, or from disk in $buffer 273 * sized chunks. 274 * 275 * @return string The data 276 */ 277 function get_data() 278 { 279 $buffer = 4096; 280 281 // Return data stored in memory 282 if (isset($this->_data)) { 283 $tmp = $this->_data; 284 unset($this->_data); 285 $fh = $this->_filehandle; 286 if ($this->_using_tmpfile) { 287 fseek($fh, 0); 288 } 289 return($tmp); 290 } 291 // Return data stored on disk 292 if ($this->_using_tmpfile) { 293 if ($tmp = fread($this->_filehandle, $buffer)) { 294 return($tmp); 295 } 296 } 297 298 // No data to return 299 return(''); 300 } 301 302 /** 303 * Set this worksheet as a selected worksheet, i.e. the worksheet has its tab 304 * highlighted. 305 * 306 * @access public 307 */ 308 function select() 309 { 310 $this->selected = 1; 311 } 312 313 /** 314 * Set this worksheet as the active worksheet, i.e. the worksheet that is 315 * displayed when the workbook is opened. Also set it as selected. 316 * 317 * @access public 318 */ 319 function activate() 320 { 321 $this->selected = 1; 322 $this->activesheet =& $this->index; 323 } 324 325 /** 326 * Set this worksheet as the first visible sheet. This is necessary 327 * when there are a large number of worksheets and the activated 328 * worksheet is not visible on the screen. 329 * 330 * @access public 331 */ 332 function set_first_sheet() 333 { 334 $this->firstsheet = $this->index; 335 } 336 337 /** 338 * Set the worksheet protection flag to prevent accidental modification and to 339 * hide formulas if the locked and hidden format properties have been set. 340 * 341 * @access public 342 * @param string $password The password to use for protecting the sheet. 343 */ 344 function protect($password) 345 { 346 $this->_protect = 1; 347 $this->_password = $this->_encode_password($password); 348 } 349 350 /** 351 * Set the width of a single column or a range of columns. 352 * 353 * @access public 354 * @see _store_colinfo() 355 * @param integer $firstcol first column on the range 356 * @param integer $lastcol last column on the range 357 * @param integer $width width to set 358 * @param mixed $format The optional XF format to apply to the columns 359 * @param integer $hidden The optional hidden atribute 360 */ 361 function set_column($firstcol, $lastcol, $width, $format = 0, $hidden = 0) 362 { 363 $this->colinfo[] = array($firstcol, $lastcol, $width, $format, $hidden); 364 365 // Set width to zero if column is hidden 366 $width = ($hidden) ? 0 : $width; 367 368 for($col = $firstcol; $col <= $lastcol; $col++) { 369 $this->col_sizes[$col] = $width; 370 } 371 } 372 373 /** 374 * Set which cell or cells are selected in a worksheet 375 * 376 * @access public 377 * @param integer $first_row first row in the selected quadrant 378 * @param integer $first_column first column in the selected quadrant 379 * @param integer $last_row last row in the selected quadrant 380 * @param integer $last_column last column in the selected quadrant 381 * @see _store_selection() 382 */ 383 function set_selection($first_row,$first_column,$last_row,$last_column) 384 { 385 $this->_selection = array($first_row,$first_column,$last_row,$last_column); 386 } 387 388 /** 389 * Set panes and mark them as frozen. 390 * 391 * @access public 392 * @param array $panes This is the only parameter received and is composed of the following: 393 * 0 => Vertical split position, 394 * 1 => Horizontal split position 395 * 2 => Top row visible 396 * 3 => Leftmost column visible 397 * 4 => Active pane 398 */ 399 function freeze_panes($panes) 400 { 401 $this->_frozen = 1; 402 $this->_panes = $panes; 403 } 404 405 /** 406 * Set panes and mark them as unfrozen. 407 * 408 * @access public 409 * @param array $panes This is the only parameter received and is composed of the following: 410 * 0 => Vertical split position, 411 * 1 => Horizontal split position 412 * 2 => Top row visible 413 * 3 => Leftmost column visible 414 * 4 => Active pane 415 */ 416 function thaw_panes($panes) 417 { 418 $this->_frozen = 0; 419 $this->_panes = $panes; 420 } 421 422 /** 423 * Set the page orientation as portrait. 424 * 425 * @access public 426 */ 427 function set_portrait() 428 { 429 $this->_orientation = 1; 430 } 431 432 /** 433 * Set the page orientation as landscape. 434 * 435 * @access public 436 */ 437 function set_landscape() 438 { 439 $this->_orientation = 0; 440 } 441 442 /** 443 * Set the paper type. Ex. 1 = US Letter, 9 = A4 444 * 445 * @access public 446 * @param integer $size The type of paper size to use 447 */ 448 function set_paper($size = 0) 449 { 450 $this->_paper_size = $size; 451 } 452 453 454 /** 455 * Set the page header caption and optional margin. 456 * 457 * @access public 458 * @param string $string The header text 459 * @param float $margin optional head margin in inches. 460 */ 461 function set_header($string,$margin = 0.50) 462 { 463 if (strlen($string) >= 255) { 464 //carp 'Header string must be less than 255 characters'; 465 return; 466 } 467 $this->_header = $string; 468 $this->_margin_head = $margin; 469 } 470 471 /** 472 * Set the page footer caption and optional margin. 473 * 474 * @access public 475 * @param string $string The footer text 476 * @param float $margin optional foot margin in inches. 477 */ 478 function set_footer($string,$margin = 0.50) 479 { 480 if (strlen($string) >= 255) { 481 //carp 'Footer string must be less than 255 characters'; 482 return; 483 } 484 $this->_footer = $string; 485 $this->_margin_foot = $margin; 486 } 487 488 /** 489 * Center the page horinzontally. 490 * 491 * @access public 492 * @param integer $center the optional value for centering. Defaults to 1 (center). 493 */ 494 function center_horizontally($center = 1) 495 { 496 $this->_hcenter = $center; 497 } 498 499 /** 500 * Center the page horinzontally. 501 * 502 * @access public 503 * @param integer $center the optional value for centering. Defaults to 1 (center). 504 */ 505 function center_vertically($center = 1) 506 { 507 $this->_vcenter = $center; 508 } 509 510 /** 511 * Set all the page margins to the same value in inches. 512 * 513 * @access public 514 * @param float $margin The margin to set in inches 515 */ 516 function set_margins($margin) 517 { 518 $this->set_margin_left($margin); 519 $this->set_margin_right($margin); 520 $this->set_margin_top($margin); 521 $this->set_margin_bottom($margin); 522 } 523 524 /** 525 * Set the left and right margins to the same value in inches. 526 * 527 * @access public 528 * @param float $margin The margin to set in inches 529 */ 530 function set_margins_LR($margin) 531 { 532 $this->set_margin_left($margin); 533 $this->set_margin_right($margin); 534 } 535 536 /** 537 * Set the top and bottom margins to the same value in inches. 538 * 539 * @access public 540 * @param float $margin The margin to set in inches 541 */ 542 function set_margins_TB($margin) 543 { 544 $this->set_margin_top($margin); 545 $this->set_margin_bottom($margin); 546 } 547 548 /** 549 * Set the left margin in inches. 550 * 551 * @access public 552 * @param float $margin The margin to set in inches 553 */ 554 function set_margin_left($margin = 0.75) 555 { 556 $this->_margin_left = $margin; 557 } 558 559 /** 560 * Set the right margin in inches. 561 * 562 * @access public 563 * @param float $margin The margin to set in inches 564 */ 565 function set_margin_right($margin = 0.75) 566 { 567 $this->_margin_right = $margin; 568 } 569 570 /** 571 * Set the top margin in inches. 572 * 573 * @access public 574 * @param float $margin The margin to set in inches 575 */ 576 function set_margin_top($margin = 1.00) 577 { 578 $this->_margin_top = $margin; 579 } 580 581 /** 582 * Set the bottom margin in inches. 583 * 584 * @access public 585 * @param float $margin The margin to set in inches 586 */ 587 function set_margin_bottom($margin = 1.00) 588 { 589 $this->_margin_bottom = $margin; 590 } 591 592 /** 593 * Set the rows to repeat at the top of each printed page. See also the 594 * _store_name_xxxx() methods in Workbook.php 595 * 596 * @access public 597 * @param integer $first_row First row to repeat 598 * @param integer $last_row Last row to repeat. Optional. 599 */ 600 function repeat_rows($first_row, $last_row = NULL) 601 { 602 $this->_title_rowmin = $first_row; 603 if(isset($last_row)) { //Second row is optional 604 $this->_title_rowmax = $last_row; 605 } 606 else { 607 $this->_title_rowmax = $first_row; 608 } 609 } 610 611 /** 612 * Set the columns to repeat at the left hand side of each printed page. 613 * See also the _store_names() methods in Workbook.php 614 * 615 * @access public 616 * @param integer $first_col First column to repeat 617 * @param integer $last_col Last column to repeat. Optional. 618 */ 619 function repeat_columns($first_col, $last_col = NULL) 620 { 621 $this->_title_colmin = $first_col; 622 if(isset($last_col)) { // Second col is optional 623 $this->_title_colmax = $last_col; 624 } 625 else { 626 $this->_title_colmax = $first_col; 627 } 628 } 629 630 /** 631 * Set the area of each worksheet that will be printed. 632 * 633 * @access public 634 * @see Workbook::_store_names() 635 * @param integer $first_row First row of the area to print 636 * @param integer $first_col First column of the area to print 637 * @param integer $last_row Last row of the area to print 638 * @param integer $last_col Last column of the area to print 639 */ 640 function print_area($first_row, $first_col, $last_row, $last_col) 641 { 642 $this->_print_rowmin = $first_row; 643 $this->_print_colmin = $first_col; 644 $this->_print_rowmax = $last_row; 645 $this->_print_colmax = $last_col; 646 } 647 648 649 /** 650 * Set the option to hide gridlines on the printed page. 651 * 652 * @access public 653 * @see _store_print_gridlines(), _store_gridset() 654 */ 655 function hide_gridlines() 656 { 657 $this->_print_gridlines = 0; 658 } 659 660 /** 661 * Set the option to print the row and column headers on the printed page. 662 * See also the _store_print_headers() method below. 663 * 664 * @access public 665 * @see _store_print_headers() 666 * @param integer $print Whether to print the headers or not. Defaults to 1 (print). 667 */ 668 function print_row_col_headers($print = 1) 669 { 670 $this->_print_headers = $print; 671 } 672 673 /** 674 * Store the vertical and horizontal number of pages that will define the 675 * maximum area printed. It doesn't seem to work with OpenOffice. 676 * 677 * @access public 678 * @param integer $width Maximun width of printed area in pages 679 * @param integer $heigth Maximun heigth of printed area in pages 680 * @see set_print_scale() 681 */ 682 function fit_to_pages($width, $height) 683 { 684 $this->_fit_page = 1; 685 $this->_fit_width = $width; 686 $this->_fit_height = $height; 687 } 688 689 /** 690 * Store the horizontal page breaks on a worksheet (for printing). 691 * The breaks represent the row after which the break is inserted. 692 * 693 * @access public 694 * @param array $breaks Array containing the horizontal page breaks 695 */ 696 function set_h_pagebreaks($breaks) 697 { 698 foreach($breaks as $break) { 699 array_push($this->_hbreaks,$break); 700 } 701 } 702 703 /** 704 * Store the vertical page breaks on a worksheet (for printing). 705 * The breaks represent the column after which the break is inserted. 706 * 707 * @access public 708 * @param array $breaks Array containing the vertical page breaks 709 */ 710 function set_v_pagebreaks($breaks) 711 { 712 foreach($breaks as $break) { 713 array_push($this->_vbreaks,$break); 714 } 715 } 716 717 718 /** 719 * Set the worksheet zoom factor. 720 * 721 * @access public 722 * @param integer $scale The zoom factor 723 */ 724 function set_zoom($scale = 100) 725 { 726 // Confine the scale to Excel's range 727 if ($scale < 10 or $scale > 400) { 728 //carp "Zoom factor $scale outside range: 10 <= zoom <= 400"; 729 $scale = 100; 730 } 731 732 $this->_zoom = floor($scale); 733 } 734 735 /** 736 * Set the scale factor for the printed page. 737 * It turns off the "fit to page" option 738 * 739 * @access public 740 * @param integer $scale The optional scale factor. Defaults to 100 741 */ 742 function set_print_scale($scale = 100) 743 { 744 // Confine the scale to Excel's range 745 if ($scale < 10 or $scale > 400) 746 { 747 // REPLACE THIS FOR A WARNING 748 die("Print scale $scale outside range: 10 <= zoom <= 400"); 749 $scale = 100; 750 } 751 752 // Turn off "fit to page" option 753 $this->_fit_page = 0; 754 755 $this->_print_scale = floor($scale); 756 } 757 758 /** 759 * Map to the appropriate write method acording to the token recieved. 760 * 761 * @access public 762 * @param integer $row The row of the cell we are writing to 763 * @param integer $col The column of the cell we are writing to 764 * @param mixed $token What we are writing 765 * @param mixed $format The optional format to apply to the cell 766 */ 767 function write($row, $col, $token, $format = 0) 768 { 769 // Check for a cell reference in A1 notation and substitute row and column 770 /*if ($_[0] =~ /^\D/) { 771 @_ = $this->_substitute_cellref(@_); 772 }*/ 773 774 /* 775 # Match an array ref. 776 if (ref $token eq "ARRAY") { 777 return $this->write_row(@_); 778 }*/ 779 780 // Match number 781 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/",$token)) { 782 return $this->write_number($row,$col,$token,$format); 783 } 784 // Match http or ftp URL 785 elseif (preg_match("/^[fh]tt?p:\/\//",$token)) { 786 return $this->write_url($row, $col, $token, $format); 787 } 788 // Match mailto: 789 elseif (preg_match("/^mailto:/",$token)) { 790 return $this->write_url($row, $col, $token, $format); 791 } 792 // Match internal or external sheet link 793 elseif (preg_match("/^(?:in|ex)ternal:/",$token)) { 794 return $this->write_url($row, $col, $token, $format); 795 } 796 // Match formula 797 elseif (preg_match("/^=/",$token)) { 798 return $this->write_formula($row, $col, $token, $format); 799 } 800 // Match formula 801 elseif (preg_match("/^@/",$token)) { 802 return $this->write_formula($row, $col, $token, $format); 803 } 804 // Match blank 805 elseif ($token == '') { 806 return $this->write_blank($row,$col,$format); 807 } 808 // Default: match string 809 else { 810 return $this->write_string($row,$col,$token,$format); 811 } 812 } 813 814 /** 815 * Returns an index to the XF record in the workbook 816 * 817 * @param mixed $format The optional XF format 818 * @return integer The XF record index 819 */ 820 function _XF(&$format) 821 { 822 if($format != 0) 823 { 824 return($format->get_xf_index()); 825 } 826 else 827 { 828 return(0x0F); 829 } 830 } 831 832 833 /****************************************************************************** 834 ******************************************************************************* 835 * 836 * Internal methods 837 */ 838 839 840 /** 841 * Store Worksheet data in memory using the parent's class append() or to a 842 * temporary file, the default. 843 * 844 * @param string $data The binary data to append 845 */ 846 function _append($data) 847 { 848 if ($this->_using_tmpfile) 849 { 850 // Add CONTINUE records if necessary 851 if (strlen($data) > $this->_limit) { 852 $data = $this->_add_continue($data); 853 } 854 fwrite($this->_filehandle,$data); 855 $this->_datasize += strlen($data); 856 } 857 else { 858 parent::_append($data); 859 } 860 } 861 862 /** 863 * Substitute an Excel cell reference in A1 notation for zero based row and 864 * column values in an argument list. 865 * 866 * Ex: ("A4", "Hello") is converted to (3, 0, "Hello"). 867 * 868 * @param string $cell The cell reference. Or range of cells. 869 * @return array 870 */ 871 function _substitute_cellref($cell) 872 { 873 $cell = strtoupper($cell); 874 875 // Convert a column range: 'A:A' or 'B:G' 876 if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/",$cell,$match)) { 877 list($no_use, $col1) = $this->_cell_to_rowcol($match[1] .'1'); // Add a dummy row 878 list($no_use, $col2) = $this->_cell_to_rowcol($match[2] .'1'); // Add a dummy row 879 return(array($col1, $col2)); 880 } 881 882 // Convert a cell range: 'A1:B7' 883 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/",$cell,$match)) { 884 list($row1, $col1) = $this->_cell_to_rowcol($match[1]); 885 list($row2, $col2) = $this->_cell_to_rowcol($match[2]); 886 return(array($row1, $col1, $row2, $col2)); 887 } 888 889 // Convert a cell reference: 'A1' or 'AD2000' 890 if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/",$cell)) { 891 list($row1, $col1) = $this->_cell_to_rowcol($match[1]); 892 return(array($row1, $col1)); 893 } 894 895 die("Unknown cell reference $cell "); 896 } 897 898 /** 899 * Convert an Excel cell reference in A1 notation to a zero based row and column 900 * reference; converts C1 to (0, 2). 901 * 902 * @param string $cell The cell reference. 903 * @return array containing (row, column) 904 */ 905 function _cell_to_rowcol($cell) 906 { 907 preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match); 908 $col = $match[1]; 909 $row = $match[2]; 910 911 // Convert base26 column string to number 912 $chars = split('', $col); 913 $expn = 0; 914 $col = 0; 915 916 while ($chars) { 917 $char = array_pop($chars); // LS char first 918 $col += (ord($char) -ord('A') +1) * pow(26,$expn); 919 $expn++; 920 } 921 922 // Convert 1-index to zero-index 923 $row--; 924 $col--; 925 926 return(array($row, $col)); 927 } 928 929 /** 930 * Based on the algorithm provided by Daniel Rentz of OpenOffice. 931 * 932 * @param string $plaintext The password to be encoded in plaintext. 933 * @return string The encoded password 934 */ 935 function _encode_password($plaintext) 936 { 937 $password = 0x0000; 938 $i = 1; // char position 939 940 // split the plain text password in its component characters 941 $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY); 942 foreach($chars as $char) 943 { 944 $value = ord($char) << $i; // shifted ASCII value 945 $bit_16 = $value & 0x8000; // the bit 16 946 $bit_16 >>= 15; // 0x0000 or 0x0001 947 //$bit_17 = $value & 0x00010000; 948 //$bit_17 >>= 15; 949 $value &= 0x7fff; // first 15 bits 950 $password ^= ($value | $bit_16); 951 //$password ^= ($value | $bit_16 | $bit_17); 952 $i++; 953 } 954 955 $password ^= strlen($plaintext); 956 $password ^= 0xCE4B; 957 958 return($password); 959 } 960 961 /****************************************************************************** 962 ******************************************************************************* 963 * 964 * BIFF RECORDS 965 */ 966 967 968 /** 969 * Write a double to the specified row and column (zero indexed). 970 * An integer can be written as a double. Excel will display an 971 * integer. $format is optional. 972 * 973 * Returns 0 : normal termination 974 * -2 : row or column out of range 975 * 976 * @access public 977 * @param integer $row Zero indexed row 978 * @param integer $col Zero indexed column 979 * @param float $num The number to write 980 * @param mixed $format The optional XF format 981 */ 982 function write_number($row, $col, $num, $format = 0) 983 { 984 $record = 0x0203; // Record identifier 985 $length = 0x000E; // Number of bytes to follow 986 $xf = $this->_XF($format); // The cell format 987 988 // Check that row and col are valid and store max and min values 989 if ($row >= $this->xls_rowmax) 990 { 991 return(-2); 992 } 993 if ($col >= $this->xls_colmax) 994 { 995 return(-2); 996 } 997 if ($row < $this->dim_rowmin) 998 { 999 $this->dim_rowmin = $row; 1000 } 1001 if ($row > $this->dim_rowmax) 1002 { 1003 $this->dim_rowmax = $row; 1004 } 1005 if ($col < $this->dim_colmin) 1006 { 1007 $this->dim_colmin = $col; 1008 } 1009 if ($col > $this->dim_colmax) 1010 { 1011 $this->dim_colmax = $col; 1012 } 1013 1014 $header = pack("vv", $record, $length); 1015 $data = pack("vvv", $row, $col, $xf); 1016 $xl_double = pack("d", $num); 1017 if ($this->_byte_order) // if it's Big Endian 1018 { 1019 $xl_double = strrev($xl_double); 1020 } 1021 1022 $this->_append($header.$data.$xl_double); 1023 return(0); 1024 } 1025 1026 /** 1027 * Write a string to the specified row and column (zero indexed). 1028 * NOTE: there is an Excel 5 defined limit of 255 characters. 1029 * $format is optional. 1030 * Returns 0 : normal termination 1031 * -1 : insufficient number of arguments 1032 * -2 : row or column out of range 1033 * -3 : long string truncated to 255 chars 1034 * 1035 * @access public 1036 * @param integer $row Zero indexed row 1037 * @param integer $col Zero indexed column 1038 * @param string $str The string to write 1039 * @param mixed $format The XF format for the cell 1040 */ 1041 function write_string($row, $col, $str, $format = 0) 1042 { 1043 $strlen = strlen($str); 1044 $record = 0x0204; // Record identifier 1045 $length = 0x0008 + $strlen; // Bytes to follow 1046 $xf = $this->_XF($format); // The cell format 1047 1048 $str_error = 0; 1049 1050 // Check that row and col are valid and store max and min values 1051 if ($row >= $this->xls_rowmax) 1052 { 1053 return(-2); 1054 } 1055 if ($col >= $this->xls_colmax) 1056 { 1057 return(-2); 1058 } 1059 if ($row < $this->dim_rowmin) 1060 { 1061 $this->dim_rowmin = $row; 1062 } 1063 if ($row > $this->dim_rowmax) 1064 { 1065 $this->dim_rowmax = $row; 1066 } 1067 if ($col < $this->dim_colmin) 1068 { 1069 $this->dim_colmin = $col; 1070 } 1071 if ($col > $this->dim_colmax) 1072 { 1073 $this->dim_colmax = $col; 1074 } 1075 1076 if ($strlen > $this->xls_strmax) // LABEL must be < 255 chars 1077 { 1078 $str = substr($str, 0, $this->xls_strmax); 1079 $length = 0x0008 + $this->xls_strmax; 1080 $strlen = $this->xls_strmax; 1081 $str_error = -3; 1082 } 1083 1084 $header = pack("vv", $record, $length); 1085 $data = pack("vvvv", $row, $col, $xf, $strlen); 1086 $this->_append($header.$data.$str); 1087 return($str_error); 1088 } 1089 1090 /** 1091 * Writes a note associated with the cell given by the row and column. 1092 * NOTE records don't have a length limit. 1093 * 1094 * @access public 1095 * @param integer $row Zero indexed row 1096 * @param integer $col Zero indexed column 1097 * @param string $note The note to write 1098 */ 1099 function write_note($row, $col, $note) 1100 { 1101 $note_length = strlen($note); 1102 $record = 0x001C; // Record identifier 1103 $max_length = 2048; // Maximun length for a NOTE record 1104 //$length = 0x0006 + $note_length; // Bytes to follow 1105 1106 // Check that row and col are valid and store max and min values 1107 if ($row >= $this->xls_rowmax) 1108 { 1109 return(-2); 1110 } 1111 if ($col >= $this->xls_colmax) 1112 { 1113 return(-2); 1114 } 1115 if ($row < $this->dim_rowmin) 1116 { 1117 $this->dim_rowmin = $row; 1118 } 1119 if ($row > $this->dim_rowmax) 1120 { 1121 $this->dim_rowmax = $row; 1122 } 1123 if ($col < $this->dim_colmin) 1124 { 1125 $this->dim_colmin = $col; 1126 } 1127 if ($col > $this->dim_colmax) 1128 { 1129 $this->dim_colmax = $col; 1130 } 1131 1132 // Length for this record is no more than 2048 + 6 1133 $length = 0x0006 + min($note_length, 2048); 1134 $header = pack("vv", $record, $length); 1135 $data = pack("vvv", $row, $col, $note_length); 1136 $this->_append($header.$data.substr($note, 0, 2048)); 1137 1138 for($i = $max_length; $i < $note_length; $i += $max_length) 1139 { 1140 $chunk = substr($note, $i, $max_length); 1141 $length = 0x0006 + strlen($chunk); 1142 $header = pack("vv", $record, $length); 1143 $data = pack("vvv", -1, 0, strlen($chunk)); 1144 $this->_append($header.$data.$chunk); 1145 } 1146 return(0); 1147 } 1148 1149 /** 1150 * Write a blank cell to the specified row and column (zero indexed). 1151 * A blank cell is used to specify formatting without adding a string 1152 * or a number. 1153 * 1154 * A blank cell without a format serves no purpose. Therefore, we don't write 1155 * a BLANK record unless a format is specified. This is mainly an optimisation 1156 * for the write_row() and write_col() methods. 1157 * 1158 * Returns 0 : normal termination (including no format) 1159 * -1 : insufficient number of arguments 1160 * -2 : row or column out of range 1161 * 1162 * @access public 1163 * @param integer $row Zero indexed row 1164 * @param integer $col Zero indexed column 1165 * @param mixed $format The XF format 1166 */ 1167 function write_blank($row, $col, $format = 0) 1168 { 1169 // Don't write a blank cell unless it has a format 1170 if ($format == 0) 1171 { 1172 return(0); 1173 } 1174 1175 $record = 0x0201; // Record identifier 1176 $length = 0x0006; // Number of bytes to follow 1177 $xf = $this->_XF($format); // The cell format 1178 1179 // Check that row and col are valid and store max and min values 1180 if ($row >= $this->xls_rowmax) 1181 { 1182 return(-2); 1183 } 1184 if ($col >= $this->xls_colmax) 1185 { 1186 return(-2); 1187 } 1188 if ($row < $this->dim_rowmin) 1189 { 1190 $this->dim_rowmin = $row; 1191 } 1192 if ($row > $this->dim_rowmax) 1193 { 1194 $this->dim_rowmax = $row; 1195 } 1196 if ($col < $this->dim_colmin) 1197 { 1198 $this->dim_colmin = $col; 1199 } 1200 if ($col > $this->dim_colmax) 1201 { 1202 $this->dim_colmax = $col; 1203 } 1204 1205 $header = pack("vv", $record, $length); 1206 $data = pack("vvv", $row, $col, $xf); 1207 $this->_append($header.$data); 1208 return 0; 1209 } 1210 1211 /** 1212 * Write a formula to the specified row and column (zero indexed). 1213 * The textual representation of the formula is passed to the parser in 1214 * Parser.php which returns a packed binary string. 1215 * 1216 * Returns 0 : normal termination 1217 * -2 : row or column out of range 1218 * 1219 * @access public 1220 * @param integer $row Zero indexed row 1221 * @param integer $col Zero indexed column 1222 * @param string $formula The formula text string 1223 * @param mixed $format The optional XF format 1224 */ 1225 function write_formula($row, $col, $formula, $format = 0) 1226 { 1227 $record = 0x0006; // Record identifier 1228 1229 // Excel normally stores the last calculated value of the formula in $num. 1230 // Clearly we are not in a position to calculate this a priori. Instead 1231 // we set $num to zero and set the option flags in $grbit to ensure 1232 // automatic calculation of the formula when the file is opened. 1233 // 1234 $xf = $this->_XF($format); // The cell format 1235 $num = 0x00; // Current value of formula 1236 $grbit = 0x03; // Option flags 1237 $chn = 0x0000; // Must be zero 1238 1239 1240 // Check that row and col are valid and store max and min values 1241 if ($row >= $this->xls_rowmax) 1242 { 1243 return(-2); 1244 } 1245 if ($col >= $this->xls_colmax) 1246 { 1247 return(-2); 1248 } 1249 if ($row < $this->dim_rowmin) 1250 { 1251 $this->dim_rowmin = $row; 1252 } 1253 if ($row > $this->dim_rowmax) 1254 { 1255 $this->dim_rowmax = $row; 1256 } 1257 if ($col < $this->dim_colmin) 1258 { 1259 $this->dim_colmin = $col; 1260 } 1261 if ($col > $this->dim_colmax) 1262 { 1263 $this->dim_colmax = $col; 1264 } 1265 1266 // Strip the '=' or '@' sign at the beginning of the formula string 1267 if (ereg("^=",$formula)) { 1268 $formula = preg_replace("/(^=)/","",$formula); 1269 } 1270 elseif(ereg("^@",$formula)) { 1271 $formula = preg_replace("/(^@)/","",$formula); 1272 } 1273 else { 1274 die("Unrecognised character for formula"); 1275 } 1276 1277 // Parse the formula using the parser in Parser.php 1278 //$tree = new Parser($this->_byte_order); 1279 $this->_parser->parse($formula); 1280 //$tree->parse($formula); 1281 $formula = $this->_parser->to_reverse_polish(); 1282 1283 $formlen = strlen($formula); // Length of the binary string 1284 $length = 0x16 + $formlen; // Length of the record data 1285 1286 $header = pack("vv", $record, $length); 1287 $data = pack("vvvdvVv", $row, $col, $xf, $num, 1288 $grbit, $chn, $formlen); 1289 1290 $this->_append($header.$data.$formula); 1291 return 0; 1292 } 1293 1294 /** 1295 * Write a hyperlink. This is comprised of two elements: the visible label and 1296 * the invisible link. The visible label is the same as the link unless an 1297 * alternative string is specified. The label is written using the 1298 * write_string() method. Therefore the 255 characters string limit applies. 1299 * $string and $format are optional and their order is interchangeable. 1300 * 1301 * The hyperlink can be to a http, ftp, mail, internal sheet, or external 1302 * directory url. 1303 * 1304 * Returns 0 : normal termination 1305 * -1 : insufficient number of arguments 1306 * -2 : row or column out of range 1307 * -3 : long string truncated to 255 chars 1308 * 1309 * @access public 1310 * @param integer $row Row 1311 * @param integer $col Column 1312 * @param string $url URL string 1313 * @param string $string Alternative label 1314 * @param mixed $format The cell format 1315 */ 1316 function write_url($row, $col, $url, $string = '', $format = 0) 1317 { 1318 // Add start row and col to arg list 1319 return($this->_write_url_range($row, $col, $row, $col, $url, $string, $format)); 1320 } 1321 1322 /** 1323 * This is the more general form of write_url(). It allows a hyperlink to be 1324 * written to a range of cells. This function also decides the type of hyperlink 1325 * to be written. These are either, Web (http, ftp, mailto), Internal 1326 * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1'). 1327 * 1328 * See also write_url() above for a general description and return values. 1329 * 1330 * @param integer $row1 Start row 1331 * @param integer $col1 Start column 1332 * @param integer $row2 End row 1333 * @param integer $col2 End column 1334 * @param string $url URL string 1335 * @param string $string Alternative label 1336 * @param mixed $format The cell format 1337 */ 1338 1339 function _write_url_range($row1, $col1, $row2, $col2, $url, $string = '', $format = 0) 1340 { 1341 // Check for internal/external sheet links or default to web link 1342 if (preg_match('[^internal:]', $url)) { 1343 return($this->_write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format)); 1344 } 1345 if (preg_match('[^external:]', $url)) { 1346 return($this->_write_url_external($row1, $col1, $row2, $col2, $url, $string, $format)); 1347 } 1348 return($this->_write_url_web($row1, $col1, $row2, $col2, $url, $string, $format)); 1349 } 1350 1351 1352 /** 1353 * Used to write http, ftp and mailto hyperlinks. 1354 * The link type ($options) is 0x03 is the same as absolute dir ref without 1355 * sheet. However it is differentiated by the $unknown2 data stream. 1356 * 1357 * @see write_url() 1358 * @param integer $row1 Start row 1359 * @param integer $col1 Start column 1360 * @param integer $row2 End row 1361 * @param integer $col2 End column 1362 * @param string $url URL string 1363 * @param string $str Alternative label 1364 * @param mixed $format The cell format 1365 */ 1366 function _write_url_web($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1367 { 1368 $record = 0x01B8; // Record identifier 1369 $length = 0x00000; // Bytes to follow 1370 1371 if($format == 0) { 1372 $format = $this->_url_format; 1373 } 1374 1375 // Write the visible label using the write_string() method. 1376 if($str == '') { 1377 $str = $url; 1378 } 1379 $str_error = $this->write_string($row1, $col1, $str, $format); 1380 if ($str_error == -2) { 1381 return($str_error); 1382 } 1383 1384 // Pack the undocumented parts of the hyperlink stream 1385 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1386 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B"); 1387 1388 // Pack the option flags 1389 $options = pack("V", 0x03); 1390 1391 // Convert URL to a null terminated wchar string 1392 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1393 $url = $url . "\0\0\0"; 1394 1395 // Pack the length of the URL 1396 $url_len = pack("V", strlen($url)); 1397 1398 // Calculate the data length 1399 $length = 0x34 + strlen($url); 1400 1401 // Pack the header data 1402 $header = pack("vv", $record, $length); 1403 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1404 1405 // Write the packed data 1406 $this->_append( $header. $data. 1407 $unknown1. $options. 1408 $unknown2. $url_len. $url); 1409 return($str_error); 1410 } 1411 1412 /** 1413 * Used to write internal reference hyperlinks such as "Sheet1!A1". 1414 * 1415 * @see write_url() 1416 * @param integer $row1 Start row 1417 * @param integer $col1 Start column 1418 * @param integer $row2 End row 1419 * @param integer $col2 End column 1420 * @param string $url URL string 1421 * @param string $str Alternative label 1422 * @param mixed $format The cell format 1423 */ 1424 function _write_url_internal($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1425 { 1426 $record = 0x01B8; // Record identifier 1427 $length = 0x00000; // Bytes to follow 1428 1429 if ($format == 0) { 1430 $format = $this->_url_format; 1431 } 1432 1433 // Strip URL type 1434 $url = preg_replace('s[^internal:]', '', $url); 1435 1436 // Write the visible label 1437 if($str == '') { 1438 $str = $url; 1439 } 1440 $str_error = $this->write_string($row1, $col1, $str, $format); 1441 if ($str_error == -2) { 1442 return($str_error); 1443 } 1444 1445 // Pack the undocumented parts of the hyperlink stream 1446 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1447 1448 // Pack the option flags 1449 $options = pack("V", 0x08); 1450 1451 // Convert the URL type and to a null terminated wchar string 1452 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1453 $url = $url . "\0\0\0"; 1454 1455 // Pack the length of the URL as chars (not wchars) 1456 $url_len = pack("V", floor(strlen($url)/2)); 1457 1458 // Calculate the data length 1459 $length = 0x24 + strlen($url); 1460 1461 // Pack the header data 1462 $header = pack("vv", $record, $length); 1463 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1464 1465 // Write the packed data 1466 $this->_append($header. $data. 1467 $unknown1. $options. 1468 $url_len. $url); 1469 return($str_error); 1470 } 1471 1472 /** 1473 * Write links to external directory names such as 'c:\foo.xls', 1474 * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'. 1475 * 1476 * Note: Excel writes some relative links with the $dir_long string. We ignore 1477 * these cases for the sake of simpler code. 1478 * 1479 * @see write_url() 1480 * @param integer $row1 Start row 1481 * @param integer $col1 Start column 1482 * @param integer $row2 End row 1483 * @param integer $col2 End column 1484 * @param string $url URL string 1485 * @param string $str Alternative label 1486 * @param mixed $format The cell format 1487 */ 1488 function _write_url_external($row1, $col1, $row2, $col2, $url, $str, $format = 0) 1489 { 1490 // Network drives are different. We will handle them separately 1491 // MS/Novell network drives and shares start with \\ 1492 if (preg_match('[^external:\\\\]', $url)) { 1493 return($this->_write_url_external_net($row1, $col1, $row2, $col2, $url, $str, $format)); 1494 } 1495 1496 $record = 0x01B8; // Record identifier 1497 $length = 0x00000; // Bytes to follow 1498 1499 if ($format == 0) { 1500 $format = $this->_url_format; 1501 } 1502 1503 // Strip URL type and change Unix dir separator to Dos style (if needed) 1504 // 1505 $url = preg_replace('[^external:]', '', $url); 1506 $url = preg_replace('[/]', "\\", $url); 1507 1508 // Write the visible label 1509 if ($str == '') { 1510 $str = preg_replace('[\#]', ' - ', $url); 1511 } 1512 $str_error = $this->write_string($row1, $col1, $str, $format); 1513 if ($str_error == -2) { 1514 return($str_error); 1515 } 1516 1517 // Determine if the link is relative or absolute: 1518 // relative if link contains no dir separator, "somefile.xls" 1519 // relative if link starts with up-dir, "..\..\somefile.xls" 1520 // otherwise, absolute 1521 1522 $absolute = 0x02; // Bit mask 1523 if (!preg_match('[\\]', $url)) { 1524 $absolute = 0x00; 1525 } 1526 if (preg_match('[^\.\.\\]', $url)) { 1527 $absolute = 0x00; 1528 } 1529 1530 // Determine if the link contains a sheet reference and change some of the 1531 // parameters accordingly. 1532 // Split the dir name and sheet name (if it exists) 1533 list($dir_long , $sheet) = split('/\#/', $url); 1534 $link_type = 0x01 | $absolute; 1535 1536 if (isset($sheet)) { 1537 $link_type |= 0x08; 1538 $sheet_len = pack("V", strlen($sheet) + 0x01); 1539 $sheet = join("\0", split('', $sheet)); 1540 $sheet .= "\0\0\0"; 1541 } 1542 else { 1543 $sheet_len = ''; 1544 $sheet = ''; 1545 } 1546 1547 // Pack the link type 1548 $link_type = pack("V", $link_type); 1549 1550 // Calculate the up-level dir count e.g.. (..\..\..\ == 3) 1551 $up_count = preg_match_all("/\.\.\\/", $dir_long, $useless); 1552 $up_count = pack("v", $up_count); 1553 1554 // Store the short dos dir name (null terminated) 1555 $dir_short = preg_replace('/\.\.\\/', '', $dir_long) . "\0"; 1556 1557 // Store the long dir name as a wchar string (non-null terminated) 1558 $dir_long = join("\0", split('', $dir_long)); 1559 $dir_long = $dir_long . "\0"; 1560 1561 // Pack the lengths of the dir strings 1562 $dir_short_len = pack("V", strlen($dir_short) ); 1563 $dir_long_len = pack("V", strlen($dir_long) ); 1564 $stream_len = pack("V", strlen($dir_long) + 0x06); 1565 1566 // Pack the undocumented parts of the hyperlink stream 1567 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' ); 1568 $unknown2 = pack("H*",'0303000000000000C000000000000046' ); 1569 $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000'); 1570 $unknown4 = pack("v", 0x03 ); 1571 1572 // Pack the main data stream 1573 $data = pack("vvvv", $row1, $row2, $col1, $col2) . 1574 $unknown1 . 1575 $link_type . 1576 $unknown2 . 1577 $up_count . 1578 $dir_short_len. 1579 $dir_short . 1580 $unknown3 . 1581 $stream_len . 1582 $dir_long_len . 1583 $unknown4 . 1584 $dir_long . 1585 $sheet_len . 1586 $sheet ; 1587 1588 // Pack the header data 1589 $length = strlen($data); 1590 $header = pack("vv", $record, $length); 1591 1592 // Write the packed data 1593 $this->_append($header. $data); 1594 return($str_error); 1595 } 1596 1597 1598 /* 1599 ############################################################################### 1600 # 1601 # write_url_xxx($row1, $col1, $row2, $col2, $url, $string, $format) 1602 # 1603 # Write links to external MS/Novell network drives and shares such as 1604 # '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'. 1605 # 1606 # See also write_url() above for a general description and return values. 1607 # 1608 sub _write_url_external_net { 1609 1610 my $this = shift; 1611 1612 my $record = 0x01B8; # Record identifier 1613 my $length = 0x00000; # Bytes to follow 1614 1615 my $row1 = $_[0]; # Start row 1616 my $col1 = $_[1]; # Start column 1617 my $row2 = $_[2]; # End row 1618 my $col2 = $_[3]; # End column 1619 my $url = $_[4]; # URL string 1620 my $str = $_[5]; # Alternative label 1621 my $xf = $_[6] || $this->{_url_format};# The cell format 1622 1623 1624 # Strip URL type and change Unix dir separator to Dos style (if needed) 1625 # 1626 $url =~ s[^external:][]; 1627 $url =~ s[/][\\]g; 1628 1629 1630 # Write the visible label 1631 ($str = $url) =~ s[\#][ - ] unless defined $str; 1632 my $str_error = $this->write_string($row1, $col1, $str, $xf); 1633 return $str_error if $str_error == -2; 1634 1635 1636 # Determine if the link contains a sheet reference and change some of the 1637 # parameters accordingly. 1638 # Split the dir name and sheet name (if it exists) 1639 # 1640 my ($dir_long , $sheet) = split /\#/, $url; 1641 my $link_type = 0x0103; # Always absolute 1642 my $sheet_len; 1643 1644 if (defined $sheet) { 1645 $link_type |= 0x08; 1646 $sheet_len = pack("V", length($sheet) + 0x01); 1647 $sheet = join("\0", split('', $sheet)); 1648 $sheet .= "\0\0\0"; 1649 } 1650 else { 1651 $sheet_len = ''; 1652 $sheet = ''; 1653 } 1654 1655 # Pack the link type 1656 $link_type = pack("V", $link_type); 1657 1658 1659 # Make the string null terminated 1660 $dir_long = $dir_long . "\0"; 1661 1662 1663 # Pack the lengths of the dir string 1664 my $dir_long_len = pack("V", length $dir_long); 1665 1666 1667 # Store the long dir name as a wchar string (non-null terminated) 1668 $dir_long = join("\0", split('', $dir_long)); 1669 $dir_long = $dir_long . "\0"; 1670 1671 1672 # Pack the undocumented part of the hyperlink stream 1673 my $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'); 1674 1675 1676 # Pack the main data stream 1677 my $data = pack("vvvv", $row1, $row2, $col1, $col2) . 1678 $unknown1 . 1679 $link_type . 1680 $dir_long_len . 1681 $dir_long . 1682 $sheet_len . 1683 $sheet ; 1684 1685 1686 # Pack the header data 1687 $length = length $data; 1688 my $header = pack("vv", $record, $length); 1689 1690 1691 # Write the packed data 1692 $this->_append( $header, $data); 1693 1694 return $str_error; 1695 }*/ 1696 1697 /** 1698 * This method is used to set the height and XF format for a row. 1699 * Writes the BIFF record ROW. 1700 * 1701 * @access public 1702 * @param integer $row The row to set 1703 * @param integer $height Height we are giving to the row. 1704 * Use NULL to set XF without setting height 1705 * @param mixed $format XF format we are giving to the row 1706 */ 1707 function set_row($row, $height, $format = 0) 1708 { 1709 $record = 0x0208; // Record identifier 1710 $length = 0x0010; // Number of bytes to follow 1711 1712 $colMic = 0x0000; // First defined column 1713 $colMac = 0x0000; // Last defined column 1714 $irwMac = 0x0000; // Used by Excel to optimise loading 1715 $reserved = 0x0000; // Reserved 1716 $grbit = 0x01C0; // Option flags. (monkey) see $1 do 1717 $ixfe = $this->_XF($format); // XF index 1718 1719 // Use set_row($row, NULL, $XF) to set XF without setting height 1720 if ($height != NULL) { 1721 $miyRw = $height * 20; // row height 1722 } 1723 else { 1724 $miyRw = 0xff; // default row height is 256 1725 } 1726 1727 $header = pack("vv", $record, $length); 1728 $data = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw, 1729 $irwMac,$reserved, $grbit, $ixfe); 1730 $this->_append($header.$data); 1731 } 1732 1733 /** 1734 * Writes Excel DIMENSIONS to define the area in which there is data. 1735 */ 1736 function _store_dimensions() 1737 { 1738 $record = 0x0000; // Record identifier 1739 $length = 0x000A; // Number of bytes to follow 1740 $row_min = $this->dim_rowmin; // First row 1741 $row_max = $this->dim_rowmax; // Last row plus 1 1742 $col_min = $this->dim_colmin; // First column 1743 $col_max = $this->dim_colmax; // Last column plus 1 1744 $reserved = 0x0000; // Reserved by Excel 1745 1746 $header = pack("vv", $record, $length); 1747 $data = pack("vvvvv", $row_min, $row_max, 1748 $col_min, $col_max, $reserved); 1749 $this->_prepend($header.$data); 1750 } 1751 1752 /** 1753 * Write BIFF record Window2. 1754 */ 1755 function _store_window2() 1756 { 1757 $record = 0x023E; // Record identifier 1758 $length = 0x000A; // Number of bytes to follow 1759 1760 $grbit = 0x00B6; // Option flags 1761 $rwTop = 0x0000; // Top row visible in window 1762 $colLeft = 0x0000; // Leftmost column visible in window 1763 $rgbHdr = 0x00000000; // Row/column heading and gridline color 1764 1765 // The options flags that comprise $grbit 1766 $fDspFmla = 0; // 0 - bit 1767 $fDspGrid = 1; // 1 1768 $fDspRwCol = 1; // 2 1769 $fFrozen = $this->_frozen; // 3 1770 $fDspZeros = 1; // 4 1771 $fDefaultHdr = 1; // 5 1772 $fArabic = 0; // 6 1773 $fDspGuts = 1; // 7 1774 $fFrozenNoSplit = 0; // 0 - bit 1775 $fSelected = $this->selected; // 1 1776 $fPaged = 1; // 2 1777 1778 $grbit = $fDspFmla; 1779 $grbit |= $fDspGrid << 1; 1780 $grbit |= $fDspRwCol << 2; 1781 $grbit |= $fFrozen << 3; 1782 $grbit |= $fDspZeros << 4; 1783 $grbit |= $fDefaultHdr << 5; 1784 $grbit |= $fArabic << 6; 1785 $grbit |= $fDspGuts << 7; 1786 $grbit |= $fFrozenNoSplit << 8; 1787 $grbit |= $fSelected << 9; 1788 $grbit |= $fPaged << 10; 1789 1790 $header = pack("vv", $record, $length); 1791 $data = pack("vvvV", $grbit, $rwTop, $colLeft, $rgbHdr); 1792 $this->_append($header.$data); 1793 } 1794 1795 /** 1796 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use. 1797 */ 1798 function _store_defcol() 1799 { 1800 $record = 0x0055; // Record identifier 1801 $length = 0x0002; // Number of bytes to follow 1802 $colwidth = 0x0008; // Default column width 1803 1804 $header = pack("vv", $record, $length); 1805 $data = pack("v", $colwidth); 1806 $this->_prepend($header.$data); 1807 } 1808 1809 /** 1810 * Write BIFF record COLINFO to define column widths 1811 * 1812 * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C 1813 * length record. 1814 * 1815 * @param array $col_array This is the only parameter received and is composed of the following: 1816 * 0 => First formatted column, 1817 * 1 => Last formatted column, 1818 * 2 => Col width (8.43 is Excel default), 1819 * 3 => The optional XF format of the column, 1820 * 4 => Option flags. 1821 */ 1822 function _store_colinfo($col_array) 1823 { 1824 if(isset($col_array[0])) { 1825 $colFirst = $col_array[0]; 1826 } 1827 if(isset($col_array[1])) { 1828 $colLast = $col_array[1]; 1829 } 1830 if(isset($col_array[2])) { 1831 $coldx = $col_array[2]; 1832 } 1833 else { 1834 $coldx = 8.43; 1835 } 1836 if(isset($col_array[3])) { 1837 $format = $col_array[3]; 1838 } 1839 else { 1840 $format = 0; 1841 } 1842 if(isset($col_array[4])) { 1843 $grbit = $col_array[4]; 1844 } 1845 else { 1846 $grbit = 0; 1847 } 1848 $record = 0x007D; // Record identifier 1849 $length = 0x000B; // Number of bytes to follow 1850 1851 $coldx += 0.72; // Fudge. Excel subtracts 0.72 !? 1852 $coldx *= 256; // Convert to units of 1/256 of a char 1853 1854 $ixfe = $this->_XF($format); 1855 $reserved = 0x00; // Reserved 1856 1857 $header = pack("vv", $record, $length); 1858 $data = pack("vvvvvC", $colFirst, $colLast, $coldx, 1859 $ixfe, $grbit, $reserved); 1860 $this->_prepend($header.$data); 1861 } 1862 1863 /** 1864 * Write BIFF record SELECTION. 1865 * 1866 * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast) 1867 * @see set_selection() 1868 */ 1869 function _store_selection($array) 1870 { 1871 list($rwFirst,$colFirst,$rwLast,$colLast) = $array; 1872 $record = 0x001D; // Record identifier 1873 $length = 0x000F; // Number of bytes to follow 1874 1875 $pnn = $this->_active_pane; // Pane position 1876 $rwAct = $rwFirst; // Active row 1877 $colAct = $colFirst; // Active column 1878 $irefAct = 0; // Active cell ref 1879 $cref = 1; // Number of refs 1880 1881 if (!isset($rwLast)) { 1882 $rwLast = $rwFirst; // Last row in reference 1883 } 1884 if (!isset($colLast)) { 1885 $colLast = $colFirst; // Last col in reference 1886 } 1887 1888 // Swap last row/col for first row/col as necessary 1889 if ($rwFirst > $rwLast) 1890 { 1891 list($rwFirst, $rwLast) = array($rwLast, $rwFirst); 1892 } 1893 1894 if ($colFirst > $colLast) 1895 { 1896 list($colFirst, $colLast) = array($colLast, $colFirst); 1897 } 1898 1899 $header = pack("vv", $record, $length); 1900 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct, 1901 $irefAct, $cref, 1902 $rwFirst, $rwLast, 1903 $colFirst, $colLast); 1904 $this->_append($header.$data); 1905 } 1906 1907 1908 /** 1909 * Write BIFF record EXTERNCOUNT to indicate the number of external sheet 1910 * references in a worksheet. 1911 * 1912 * Excel only stores references to external sheets that are used in formulas. 1913 * For simplicity we store references to all the sheets in the workbook 1914 * regardless of whether they are used or not. This reduces the overall 1915 * complexity and eliminates the need for a two way dialogue between the formula 1916 * parser the worksheet objects. 1917 * 1918 * @param integer $count The number of external sheet references in this worksheet 1919 */ 1920 function _store_externcount($count) 1921 { 1922 $record = 0x0016; // Record identifier 1923 $length = 0x0002; // Number of bytes to follow 1924 1925 $header = pack("vv", $record, $length); 1926 $data = pack("v", $count); 1927 $this->_prepend($header.$data); 1928 } 1929 1930 /** 1931 * Writes the Excel BIFF EXTERNSHEET record. These references are used by 1932 * formulas. A formula references a sheet name via an index. Since we store a 1933 * reference to all of the external worksheets the EXTERNSHEET index is the same 1934 * as the worksheet index. 1935 * 1936 * @param string $sheetname The name of a external worksheet 1937 */ 1938 function _store_externsheet($sheetname) 1939 { 1940 $record = 0x0017; // Record identifier 1941 1942 // References to the current sheet are encoded differently to references to 1943 // external sheets. 1944 // 1945 if ($this->name == $sheetname) { 1946 $sheetname = ''; 1947 $length = 0x02; // The following 2 bytes 1948 $cch = 1; // The following byte 1949 $rgch = 0x02; // Self reference 1950 } 1951 else { 1952 $length = 0x02 + strlen($sheetname); 1953 $cch = strlen($sheetname); 1954 $rgch = 0x03; // Reference to a sheet in the current workbook 1955 } 1956 1957 $header = pack("vv", $record, $length); 1958 $data = pack("CC", $cch, $rgch); 1959 $this->_prepend($header.$data.$sheetname); 1960 } 1961 1962 /** 1963 * Writes the Excel BIFF PANE record. 1964 * The panes can either be frozen or thawed (unfrozen). 1965 * Frozen panes are specified in terms of an integer number of rows and columns. 1966 * Thawed panes are specified in terms of Excel's units for rows and columns. 1967 * 1968 * @param array $panes This is the only parameter received and is composed of the following: 1969 * 0 => Vertical split position, 1970 * 1 => Horizontal split position 1971 * 2 => Top row visible 1972 * 3 => Leftmost column visible 1973 * 4 => Active pane 1974 */ 1975 function _store_panes($panes) 1976 { 1977 $y = $panes[0]; 1978 $x = $panes[1]; 1979 $rwTop = $panes[2]; 1980 $colLeft = $panes[3]; 1981 if(count($panes) > 4) { // if Active pane was received 1982 $pnnAct = $panes[4]; 1983 } 1984 else { 1985 $pnnAct = NULL; 1986 } 1987 $record = 0x0041; // Record identifier 1988 $length = 0x000A; // Number of bytes to follow 1989 1990 // Code specific to frozen or thawed panes. 1991 if ($this->_frozen) { 1992 // Set default values for $rwTop and $colLeft 1993 if(!isset($rwTop)) { 1994 $rwTop = $y; 1995 } 1996 if(!isset($colLeft)) { 1997 $colLeft = $x; 1998 } 1999 } 2000 else { 2001 // Set default values for $rwTop and $colLeft 2002 if(!isset($rwTop)) { 2003 $rwTop = 0; 2004 } 2005 if(!isset($colLeft)) { 2006 $colLeft = 0; 2007 } 2008 2009 // Convert Excel's row and column units to the internal units. 2010 // The default row height is 12.75 2011 // The default column width is 8.43 2012 // The following slope and intersection values were interpolated. 2013 // 2014 $y = 20*$y + 255; 2015 $x = 113.879*$x + 390; 2016 } 2017 2018 2019 // Determine which pane should be active. There is also the undocumented 2020 // option to override this should it be necessary: may be removed later. 2021 // 2022 if (!isset($pnnAct)) 2023 { 2024 if ($x != 0 and $y != 0) 2025 $pnnAct = 0; // Bottom right 2026 if ($x != 0 and $y == 0) 2027 $pnnAct = 1; // Top right 2028 if ($x == 0 and $y != 0) 2029 $pnnAct = 2; // Bottom left 2030 if ($x == 0 and $y == 0) 2031 $pnnAct = 3; // Top left 2032 } 2033 2034 $this->_active_pane = $pnnAct; // Used in _store_selection 2035 2036 $header = pack("vv", $record, $length); 2037 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct); 2038 $this->_append($header.$data); 2039 } 2040 2041 /** 2042 * Store the page setup SETUP BIFF record. 2043 */ 2044 function _store_setup() 2045 { 2046 $record = 0x00A1; // Record identifier 2047 $length = 0x0022; // Number of bytes to follow 2048 2049 $iPaperSize = $this->_paper_size; // Paper size 2050 $iScale = $this->_print_scale; // Print scaling factor 2051 $iPageStart = 0x01; // Starting page number 2052 $iFitWidth = $this->_fit_width; // Fit to number of pages wide 2053 $iFitHeight = $this->_fit_height; // Fit to number of pages high 2054 $grbit = 0x00; // Option flags 2055 $iRes = 0x0258; // Print resolution 2056 $iVRes = 0x0258; // Vertical print resolution 2057 $numHdr = $this->_margin_head; // Header Margin 2058 $numFtr = $this->_margin_foot; // Footer Margin 2059 $iCopies = 0x01; // Number of copies 2060 2061 $fLeftToRight = 0x0; // Print over then down 2062 $fLandscape = $this->_orientation; // Page orientation 2063 $fNoPls = 0x0; // Setup not read from printer 2064 $fNoColor = 0x0; // Print black and white 2065 $fDraft = 0x0; // Print draft quality 2066 $fNotes = 0x0; // Print notes 2067 $fNoOrient = 0x0; // Orientation not set 2068 $fUsePage = 0x0; // Use custom starting page 2069 2070 $grbit = $fLeftToRight; 2071 $grbit |= $fLandscape << 1; 2072 $grbit |= $fNoPls << 2; 2073 $grbit |= $fNoColor << 3; 2074 $grbit |= $fDraft << 4; 2075 $grbit |= $fNotes << 5; 2076 $grbit |= $fNoOrient << 6; 2077 $grbit |= $fUsePage << 7; 2078 2079 $numHdr = pack("d", $numHdr); 2080 $numFtr = pack("d", $numFtr); 2081 if ($this->_byte_order) // if it's Big Endian 2082 { 2083 $numHdr = strrev($numHdr); 2084 $numFtr = strrev($numFtr); 2085 } 2086 2087 $header = pack("vv", $record, $length); 2088 $data1 = pack("vvvvvvvv", $iPaperSize, 2089 $iScale, 2090 $iPageStart, 2091 $iFitWidth, 2092 $iFitHeight, 2093 $grbit, 2094 $iRes, 2095 $iVRes); 2096 $data2 = $numHdr .$numFtr; 2097 $data3 = pack("v", $iCopies); 2098 $this->_prepend($header.$data1.$data2.$data3); 2099 } 2100 2101 /** 2102 * Store the header caption BIFF record. 2103 */ 2104 function store_header() 2105 { 2106 $record = 0x0014; // Record identifier 2107 2108 $str = $this->_header; // header string 2109 $cch = strlen($str); // Length of header string 2110 $length = 1 + $cch; // Bytes to follow 2111 2112 $header = pack("vv", $record, $length); 2113 $data = pack("C", $cch); 2114 2115 $this->_append($header.$data.$str); 2116 } 2117 2118 /** 2119 * Store the footer caption BIFF record. 2120 */ 2121 function store_footer() 2122 { 2123 $record = 0x0015; // Record identifier 2124 2125 $str = $this->_footer; // Footer string 2126 $cch = strlen($str); // Length of footer string 2127 $length = 1 + $cch; // Bytes to follow 2128 2129 $header = pack("vv", $record, $length); 2130 $data = pack("C", $cch); 2131 2132 $this->_append($header.$data.$str); 2133 } 2134 2135 /** 2136 * Store the horizontal centering HCENTER BIFF record. 2137 */ 2138 function store_hcenter() 2139 { 2140 $record = 0x0083; // Record identifier 2141 $length = 0x0002; // Bytes to follow 2142 2143 $fHCenter = $this->_hcenter; // Horizontal centering 2144 2145 $header = pack("vv", $record, $length); 2146 $data = pack("v", $fHCenter); 2147 2148 $this->_append($header.$data); 2149 } 2150 2151 /** 2152 * Store the vertical centering VCENTER BIFF record. 2153 */ 2154 function store_vcenter() 2155 { 2156 $record = 0x0084; // Record identifier 2157 $length = 0x0002; // Bytes to follow 2158 2159 $fVCenter = $this->_vcenter; // Horizontal centering 2160 2161 $header = pack("vv", $record, $length); 2162 $data = pack("v", $fVCenter); 2163 $this->_append($header.$data); 2164 } 2165 2166 /** 2167 * Store the LEFTMARGIN BIFF record. 2168 */ 2169 function _store_margin_left() 2170 { 2171 $record = 0x0026; // Record identifier 2172 $length = 0x0008; // Bytes to follow 2173 2174 $margin = $this->_margin_left; // Margin in inches 2175 2176 $header = pack("vv", $record, $length); 2177 $data = pack("d", $margin); 2178 if ($this->_byte_order) // if it's Big Endian 2179 { 2180 $data = strrev($data); 2181 } 2182 2183 $this->_append($header.$data); 2184 } 2185 2186 /** 2187 * Store the RIGHTMARGIN BIFF record. 2188 */ 2189 function _store_margin_right() 2190 { 2191 $record = 0x0027; // Record identifier 2192 $length = 0x0008; // Bytes to follow 2193 2194 $margin = $this->_margin_right; // Margin in inches 2195 2196 $header = pack("vv", $record, $length); 2197 $data = pack("d", $margin); 2198 if ($this->_byte_order) // if it's Big Endian 2199 { 2200 $data = strrev($data); 2201 } 2202 2203 $this->_append($header.$data); 2204 } 2205 2206 /** 2207 * Store the TOPMARGIN BIFF record. 2208 */ 2209 function _store_margin_top() 2210 { 2211 $record = 0x0028; // Record identifier 2212 $length = 0x0008; // Bytes to follow 2213 2214 $margin = $this->_margin_top; // Margin in inches 2215 2216 $header = pack("vv", $record, $length); 2217 $data = pack("d", $margin); 2218 if ($this->_byte_order) // if it's Big Endian 2219 { 2220 $data = strrev($data); 2221 } 2222 2223 $this->_append($header.$data); 2224 } 2225 2226 /** 2227 * Store the BOTTOMMARGIN BIFF record. 2228 */ 2229 function _store_margin_bottom() 2230 { 2231 $record = 0x0029; // Record identifier 2232 $length = 0x0008; // Bytes to follow 2233 2234 $margin = $this->_margin_bottom; // Margin in inches 2235 2236 $header = pack("vv", $record, $length); 2237 $data = pack("d", $margin); 2238 if ($this->_byte_order) // if it's Big Endian 2239 { 2240 $data = strrev($data); 2241 } 2242 2243 $this->_append($header.$data); 2244 } 2245 2246 /** 2247 * This is an Excel97/2000 method. It is required to perform more complicated 2248 * merging than the normal set_align('merge'). It merges the area given by 2249 * its arguments. 2250 * 2251 * @access public 2252 * @param integer $first_row First row of the area to merge 2253 * @param integer $first_col First column of the area to merge 2254 * @param integer $last_row Last row of the area to merge 2255 * @param integer $last_col Last column of the area to merge 2256 */ 2257 function merge_cells($first_row, $first_col, $last_row, $last_col) 2258 { 2259 $record = 0x00E5; // Record identifier 2260 $length = 0x000A; // Bytes to follow