| [ Index ] |
|
Code source de Dolibarr 2.0.1 |
1 <?php 2 3 /* 4 * Copyleft 2002 Johann Hanne 5 * 6 * This is free software; you can redistribute it and/or 7 * modify it under the terms of the GNU Lesser General Public 8 * License as published by the Free Software Foundation; either 9 * version 2.1 of the License, or (at your option) any later version. 10 * 11 * This software is distributed in the hope that it will be useful, 12 * but WITHOUT ANY WARRANTY; without even the implied warranty of 13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 14 * Lesser General Public License for more details. 15 * 16 * You should have received a copy of the GNU Lesser General Public 17 * License along with this software; if not, write to the 18 * Free Software Foundation, Inc., 59 Temple Place, 19 * Suite 330, Boston, MA 02111-1307 USA 20 */ 21 22 /* 23 * This is the Spreadsheet::WriteExcel Perl package ported to PHP 24 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org 25 */ 26 27 require_once "class.writeexcel_biffwriter.inc.php"; 28 29 class writeexcel_worksheet extends writeexcel_biffwriter { 30 31 var $_name; 32 var $_index; 33 var $_activesheet; 34 var $_firstsheet; 35 var $_url_format; 36 var $_parser; 37 var $_tempdir; 38 39 var $_ext_sheets; 40 var $_using_tmpfile; 41 var $_filehandle; 42 var $_fileclosed; 43 var $_offset; 44 var $_xls_rowmax; 45 var $_xls_colmax; 46 var $_xls_strmax; 47 var $_dim_rowmin; 48 var $_dim_rowmax; 49 var $_dim_colmin; 50 var $_dim_colmax; 51 var $_colinfo; 52 var $_selection; 53 var $_panes; 54 var $_active_pane; 55 var $_frozen; 56 var $_selected; 57 58 var $_paper_size; 59 var $_orientation; 60 var $_header; 61 var $_footer; 62 var $_hcenter; 63 var $_vcenter; 64 var $_margin_head; 65 var $_margin_foot; 66 var $_margin_left; 67 var $_margin_right; 68 var $_margin_top; 69 var $_margin_bottom; 70 71 var $_title_rowmin; 72 var $_title_rowmax; 73 var $_title_colmin; 74 var $_title_colmax; 75 var $_print_rowmin; 76 var $_print_rowmax; 77 var $_print_colmin; 78 var $_print_colmax; 79 80 var $_print_gridlines; 81 var $_screen_gridlines; 82 var $_print_headers; 83 84 var $_fit_page; 85 var $_fit_width; 86 var $_fit_height; 87 88 var $_hbreaks; 89 var $_vbreaks; 90 91 var $_protect; 92 var $_password; 93 94 var $_col_sizes; 95 var $_row_sizes; 96 97 var $_col_formats; 98 var $_row_formats; 99 100 var $_zoom; 101 var $_print_scale; 102 103 /* 104 * Constructor. Creates a new Worksheet object from a BIFFwriter object 105 */ 106 function writeexcel_worksheet($name, $index, &$activesheet, &$firstsheet, 107 &$url_format, &$parser, $tempdir) { 108 109 $this->writeexcel_biffwriter(); 110 111 $rowmax = 65536; // 16384 in Excel 5 112 $colmax = 256; 113 $strmax = 255; 114 115 $this->_name = $name; 116 $this->_index = $index; 117 $this->_activesheet = &$activesheet; 118 $this->_firstsheet = &$firstsheet; 119 $this->_url_format = &$url_format; 120 $this->_parser = &$parser; 121 $this->_tempdir = $tempdir; 122 123 $this->_ext_sheets = array(); 124 $this->_using_tmpfile = 1; 125 $this->_filehandle = false; 126 $this->_fileclosed = 0; 127 $this->_offset = 0; 128 $this->_xls_rowmax = $rowmax; 129 $this->_xls_colmax = $colmax; 130 $this->_xls_strmax = $strmax; 131 $this->_dim_rowmin = $rowmax +1; 132 $this->_dim_rowmax = 0; 133 $this->_dim_colmin = $colmax +1; 134 $this->_dim_colmax = 0; 135 $this->_colinfo = array(); 136 $this->_selection = array(0, 0); 137 $this->_panes = array(); 138 $this->_active_pane = 3; 139 $this->_frozen = 0; 140 $this->_selected = 0; 141 142 $this->_paper_size = 0x0; 143 $this->_orientation = 0x1; 144 $this->_header = ''; 145 $this->_footer = ''; 146 $this->_hcenter = 0; 147 $this->_vcenter = 0; 148 $this->_margin_head = 0.50; 149 $this->_margin_foot = 0.50; 150 $this->_margin_left = 0.75; 151 $this->_margin_right = 0.75; 152 $this->_margin_top = 1.00; 153 $this->_margin_bottom = 1.00; 154 155 $this->_title_rowmin = false; 156 $this->_title_rowmax = false; 157 $this->_title_colmin = false; 158 $this->_title_colmax = false; 159 $this->_print_rowmin = false; 160 $this->_print_rowmax = false; 161 $this->_print_colmin = false; 162 $this->_print_colmax = false; 163 164 $this->_print_gridlines = 1; 165 $this->_screen_gridlines = 1; 166 $this->_print_headers = 0; 167 168 $this->_fit_page = 0; 169 $this->_fit_width = 0; 170 $this->_fit_height = 0; 171 172 $this->_hbreaks = array(); 173 $this->_vbreaks = array(); 174 175 $this->_protect = 0; 176 $this->_password = false; 177 178 $this->_col_sizes = array(); 179 $this->_row_sizes = array(); 180 181 $this->_col_formats = array(); 182 $this->_row_formats = array(); 183 184 $this->_zoom = 100; 185 $this->_print_scale = 100; 186 187 $this->_initialize(); 188 } 189 190 ############################################################################### 191 # 192 # _initialize() 193 # 194 # Open a tmp file to store the majority of the Worksheet data. If this fails, 195 # for example due to write permissions, store the data in memory. This can be 196 # slow for large files. 197 # 198 function _initialize() { 199 200 # Open tmp file for storing Worksheet data. 201 $fh=fopen(tempnam($this->_tempdir, "php_writeexcel"), "w+b"); 202 203 if ($fh) { 204 # Store filehandle 205 $this->_filehandle = $fh; 206 } else { 207 # If tempfile() failed store data in memory 208 $this->_using_tmpfile = 0; 209 210 if ($this->_index == 0) { 211 $dir = $this->_tempdir; 212 213 //todo warn "Unable to create temp files in $dir. Refer to set_tempdir()". 214 // " in the Spreadsheet::WriteExcel documentation.\n" ; 215 } 216 } 217 } 218 219 /* 220 * Add data to the beginning of the workbook (note the reverse order) 221 * and to the end of the workbook. 222 */ 223 function _close($sheetnames) { 224 225 /////////////////////////////// 226 // Prepend in reverse order!! 227 // 228 229 $this->_store_dimensions(); // Prepend the sheet dimensions 230 $this->_store_password(); // Prepend the sheet password 231 $this->_store_protect(); // Prepend the sheet protection 232 $this->_store_setup(); // Prepend the page setup 233 $this->_store_margin_bottom(); // Prepend the bottom margin 234 $this->_store_margin_top(); // Prepend the top margin 235 $this->_store_margin_right(); // Prepend the right margin 236 $this->_store_margin_left(); // Prepend the left margin 237 $this->_store_vcenter(); // Prepend the page vertical 238 // centering 239 $this->_store_hcenter(); // Prepend the page horizontal 240 // centering 241 $this->_store_footer(); // Prepend the page footer 242 $this->_store_header(); // Prepend the page header 243 $this->_store_vbreak(); // Prepend the vertical page breaks 244 $this->_store_hbreak(); // Prepend the horizontal 245 // page breaks 246 $this->_store_wsbool(); // Prepend WSBOOL 247 $this->_store_gridset(); // Prepend GRIDSET 248 $this->_store_print_gridlines(); // Prepend PRINTGRIDLINES 249 $this->_store_print_headers(); // Prepend PRINTHEADERS 250 251 // Prepend EXTERNSHEET references 252 $num_sheets = sizeof($sheetnames); 253 for ($i = $num_sheets; $i > 0; $i--) { 254 $sheetname = $sheetnames[$i-1]; 255 $this->_store_externsheet($sheetname); 256 } 257 258 $this->_store_externcount($num_sheets); // Prepend the EXTERNCOUNT 259 // of external references. 260 261 // Prepend the COLINFO records if they exist 262 if (sizeof($this->_colinfo)>0){ 263 while (sizeof($this->_colinfo)>0) { 264 $arrayref = array_pop ($this->_colinfo); 265 $this->_store_colinfo($arrayref); 266 } 267 $this->_store_defcol(); 268 } 269 270 $this->_store_bof(0x0010); // Prepend the BOF record 271 272 // 273 // End of prepend. Read upwards from here. 274 //////////////////////////////////////////// 275 276 // Append 277 $this->_store_window2(); 278 $this->_store_zoom(); 279 280 if (sizeof($this->_panes)>0) { 281 $this->_store_panes($this->_panes); 282 } 283 284 $this->_store_selection($this->_selection); 285 $this->_store_eof(); 286 } 287 288 /* 289 * Retrieve the worksheet name. 290 */ 291 function get_name() { 292 return $this->_name; 293 } 294 295 ############################################################################### 296 # 297 # get_data(). 298 # 299 # Retrieves data from memory in one chunk, or from disk in $buffer 300 # sized chunks. 301 # 302 function get_data() { 303 304 $buffer = 4096; 305 306 # Return data stored in memory 307 if ($this->_data!==false) { 308 $tmp = $this->_data; 309 $this->_data=false; 310 $fh = $this->_filehandle; 311 if ($this->_using_tmpfile) { 312 fseek($fh, 0, SEEK_SET); 313 } 314 315 if ($this->_debug) { 316 print "*** worksheet::get_data() called (1):"; 317 for ($c=0;$c<strlen($tmp);$c++) { 318 if ($c%16==0) { 319 print "\n"; 320 } 321 printf("%02X ", ord($tmp[$c])); 322 } 323 print "\n"; 324 } 325 326 return $tmp; 327 } 328 329 # Return data stored on disk 330 if ($this->_using_tmpfile) { 331 if ($tmp=fread($this->_filehandle, $buffer)) { 332 333 if ($this->_debug) { 334 print "*** worksheet::get_data() called (2):"; 335 for ($c=0;$c<strlen($tmp);$c++) { 336 if ($c%16==0) { 337 print "\n"; 338 } 339 printf("%02X ", ord($tmp[$c])); 340 } 341 print "\n"; 342 } 343 344 return $tmp; 345 } 346 } 347 348 # No data to return 349 return false; 350 } 351 352 /* 353 * Set this worksheet as a selected worksheet, i.e. the worksheet has 354 * its tab highlighted. 355 */ 356 function select() { 357 $this->_selected = 1; 358 } 359 360 /* 361 * Set this worksheet as the active worksheet, i.e. the worksheet 362 * that is displayed when the workbook is opened. Also set it as 363 * selected. 364 */ 365 function activate() { 366 $this->_selected = 1; 367 $this->_activesheet = $this->_index; 368 } 369 370 /* 371 * Set this worksheet as the first visible sheet. This is necessary 372 * when there are a large number of worksheets and the activated 373 * worksheet is not visible on the screen. 374 */ 375 function set_first_sheet() { 376 $this->_firstsheet = $this->_index; 377 } 378 379 /* 380 * Set the worksheet protection flag to prevent accidental modification 381 * and to hide formulas if the locked and hidden format properties have 382 * been set. 383 */ 384 function protect($password) { 385 $this->_protect = 1; 386 $this->_password = $this->_encode_password($password); 387 } 388 389 ############################################################################### 390 # 391 # set_column($firstcol, $lastcol, $width, $format, $hidden) 392 # 393 # Set the width of a single column or a range of column. 394 # See also: _store_colinfo 395 # 396 function set_column() { 397 398 $_=func_get_args(); 399 400 $cell = $_[0]; 401 402 # Check for a cell reference in A1 notation and substitute row and column 403 if (preg_match('/^\D/', $cell)) { 404 $_ = $this->_substitute_cellref($_); 405 } 406 407 array_push($this->_colinfo, $_); 408 409 # Store the col sizes for use when calculating image vertices taking 410 # hidden columns into account. Also store the column formats. 411 # 412 if (sizeof($_)<3) { 413 # Ensure at least $firstcol, $lastcol and $width 414 return; 415 } 416 417 $width = $_[4] ? 0 : $_[2]; # Set width to zero if column is hidden 418 $format = $_[3]; 419 420 list($firstcol, $lastcol) = $_; 421 422 for ($col=$firstcol;$col<=$lastcol;$col++) { 423 $this->_col_sizes[$col] = $width; 424 if ($format) { 425 $this->_col_formats[$col] = $format; 426 } 427 } 428 } 429 430 ############################################################################### 431 # 432 # set_selection() 433 # 434 # Set which cell or cells are selected in a worksheet: see also the 435 # function _store_selection 436 # 437 function set_selection() { 438 439 $_=func_get_args(); 440 441 # Check for a cell reference in A1 notation and substitute row and column 442 if (preg_match('/^\D/', $_[0])) { 443 $_ = $this->_substitute_cellref($_); 444 } 445 446 $this->_selection = $_; 447 } 448 449 ############################################################################### 450 # 451 # freeze_panes() 452 # 453 # Set panes and mark them as frozen. See also _store_panes(). 454 # 455 function freeze_panes() { 456 457 $_=func_get_args(); 458 459 # Check for a cell reference in A1 notation and substitute row and column 460 if (preg_match('/^\D/', $_[0])) { 461 $_ = $this->_substitute_cellref($_); 462 } 463 464 $this->_frozen = 1; 465 $this->_panes = $_; 466 } 467 468 ############################################################################### 469 # 470 # thaw_panes() 471 # 472 # Set panes and mark them as unfrozen. See also _store_panes(). 473 # 474 function thaw_panes() { 475 476 $_=func_get_args(); 477 478 $this->_frozen = 0; 479 $this->_panes = $_; 480 } 481 482 /* 483 * Set the page orientation as portrait. 484 */ 485 function set_portrait() { 486 $this->_orientation = 1; 487 } 488 489 /* 490 * Set the page orientation as landscape. 491 */ 492 function set_landscape() { 493 $this->_orientation = 0; 494 } 495 496 /* 497 * Set the paper type. Ex. 1 = US Letter, 9 = A4 498 */ 499 function set_paper($type) { 500 $this->_paper_size = $type; 501 } 502 503 /* 504 * Set the page header caption and optional margin. 505 */ 506 function set_header($string, $margin) { 507 508 if (strlen($string) >= 255) { 509 trigger_error("Header string must be less than 255 characters", 510 E_USER_WARNING); 511 return; 512 } 513 514 $this->_header = $string; 515 $this->_margin_head = $margin; 516 } 517 518 /* 519 * Set the page footer caption and optional margin. 520 */ 521 function set_footer($string, $margin) { 522 if (strlen($string) >= 255) { 523 trigger_error("Footer string must be less than 255 characters", 524 E_USER_WARNING); 525 return; 526 } 527 528 $this->_footer = $string; 529 $this->_margin_foot = $margin; 530 } 531 532 /* 533 * Center the page horizontally. 534 */ 535 function center_horizontally($hcenter=1) { 536 $this->_hcenter = $hcenter; 537 } 538 539 /* 540 * Center the page horizontally. 541 */ 542 function center_vertically($vcenter=1) { 543 $this->_vcenter = $vcenter; 544 } 545 546 /* 547 * Set all the page margins to the same value in inches. 548 */ 549 function set_margins($margin) { 550 $this->set_margin_left($margin); 551 $this->set_margin_right($margin); 552 $this->set_margin_top($margin); 553 $this->set_margin_bottom($margin); 554 } 555 556 /* 557 * Set the left and right margins to the same value in inches. 558 */ 559 function set_margins_LR($margin) { 560 $this->set_margin_left($margin); 561 $this->set_margin_right($margin); 562 } 563 564 /* 565 * Set the top and bottom margins to the same value in inches. 566 */ 567 function set_margins_TB($margin) { 568 $this->set_margin_top($margin); 569 $this->set_margin_bottom($margin); 570 } 571 572 /* 573 * Set the left margin in inches. 574 */ 575 function set_margin_left($margin=0.75) { 576 $this->_margin_left = $margin; 577 } 578 579 /* 580 * Set the right margin in inches. 581 */ 582 function set_margin_right($margin=0.75) { 583 $this->_margin_right = $margin; 584 } 585 586 /* 587 * Set the top margin in inches. 588 */ 589 function set_margin_top($margin=1.00) { 590 $this->_margin_top = $margin; 591 } 592 593 /* 594 * Set the bottom margin in inches. 595 */ 596 function set_margin_bottom($margin=1.00) { 597 $this->_margin_bottom = $margin; 598 } 599 600 ############################################################################### 601 # 602 # repeat_rows($first_row, $last_row) 603 # 604 # Set the rows to repeat at the top of each printed page. See also the 605 # _store_name_xxxx() methods in Workbook.pm. 606 # 607 function repeat_rows() { 608 609 $_=func_get_args(); 610 611 $this->_title_rowmin = $_[0]; 612 $this->_title_rowmax = isset($_[1]) ? $_[1] : $_[0]; # Second row is optional 613 } 614 615 ############################################################################### 616 # 617 # repeat_columns($first_col, $last_col) 618 # 619 # Set the columns to repeat at the left hand side of each printed page. 620 # See also the _store_names() methods in Workbook.pm. 621 # 622 function repeat_columns() { 623 624 $_=func_get_args(); 625 626 # Check for a cell reference in A1 notation and substitute row and column 627 if (preg_match('/^\D/', $_[0])) { 628 $_ = $this->_substitute_cellref($_); 629 } 630 631 $this->_title_colmin = $_[0]; 632 $this->_title_colmax = isset($_[1]) ? $_[1] : $_[0]; # Second col is optional 633 } 634 635 ############################################################################### 636 # 637 # print_area($first_row, $first_col, $last_row, $last_col) 638 # 639 # Set the area of each worksheet that will be printed. See also the 640 # _store_names() methods in Workbook.pm. 641 # 642 function print_area() { 643 644 $_=func_get_args(); 645 646 # Check for a cell reference in A1 notation and substitute row and column 647 if (preg_match('/^\D/', $_[0])) { 648 $_ = $this->_substitute_cellref($_); 649 } 650 651 if (sizeof($_) != 4) { 652 # Require 4 parameters 653 return; 654 } 655 656 $this->_print_rowmin = $_[0]; 657 $this->_print_colmin = $_[1]; 658 $this->_print_rowmax = $_[2]; 659 $this->_print_colmax = $_[3]; 660 } 661 662 /* 663 * Set the option to hide gridlines on the screen and the printed page. 664 * There are two ways of doing this in the Excel BIFF format: The first 665 * is by setting the DspGrid field of the WINDOW2 record, this turns off 666 * the screen and subsequently the print gridline. The second method is 667 * to via the PRINTGRIDLINES and GRIDSET records, this turns off the 668 * printed gridlines only. The first method is probably sufficient for 669 * most cases. The second method is supported for backwards compatibility. 670 */ 671 function hide_gridlines($option=1) { 672 if ($option == 0) { 673 $this->_print_gridlines = 1; # 1 = display, 0 = hide 674 $this->_screen_gridlines = 1; 675 } elseif ($option == 1) { 676 $this->_print_gridlines = 0; 677 $this->_screen_gridlines = 1; 678 } else { 679 $this->_print_gridlines = 0; 680 $this->_screen_gridlines = 0; 681 } 682 } 683 684 /* 685 * Set the option to print the row and column headers on the printed page. 686 * See also the _store_print_headers() method below. 687 */ 688 function print_row_col_headers($headers=1) { 689 $this->_print_headers = $headers; 690 } 691 692 /* 693 * Store the vertical and horizontal number of pages that will define 694 * the maximum area printed. See also _store_setup() and _store_wsbool() 695 * below. 696 */ 697 function fit_to_pages($width, $height) { 698 $this->_fit_page = 1; 699 $this->_fit_width = $width; 700 $this->_fit_height = $height; 701 } 702 703 /* 704 * Store the horizontal page breaks on a worksheet. 705 */ 706 function set_h_pagebreaks($breaks) { 707 $this->_hbreaks=array_merge($this->_hbreaks, $breaks); 708 } 709 710 /* 711 * Store the vertical page breaks on a worksheet. 712 */ 713 function set_v_pagebreaks($breaks) { 714 $this->_vbreaks=array_merge($this->_vbreaks, $breaks); 715 } 716 717 /* 718 * Set the worksheet zoom factor. 719 */ 720 function set_zoom($scale=100) { 721 // Confine the scale to Excel's range 722 if ($scale < 10 || $scale > 400) { 723 trigger_error("Zoom factor $scale outside range: ". 724 "10 <= zoom <= 400", E_USER_WARNING); 725 $scale = 100; 726 } 727 728 $this->_zoom = $scale; 729 } 730 731 /* 732 * Set the scale factor for the printed page. 733 */ 734 function set_print_scale($scale=100) { 735 // Confine the scale to Excel's range 736 if ($scale < 10 || $scale > 400) { 737 trigger_error("Print scale $scale outside range: ". 738 "10 <= zoom <= 400", E_USER_WARNING); 739 $scale = 100; 740 } 741 742 // Turn off "fit to page" option 743 $this->_fit_page = 0; 744 745 $this->_print_scale = $scale; 746 } 747 748 ############################################################################### 749 # 750 # write($row, $col, $token, $format) 751 # 752 # Parse $token call appropriate write method. $row and $column are zero 753 # indexed. $format is optional. 754 # 755 # Returns: return value of called subroutine 756 # 757 function write() { 758 759 $_=func_get_args(); 760 761 # Check for a cell reference in A1 notation and substitute row and column 762 if (preg_match('/^\D/', $_[0])) { 763 $_ = $this->_substitute_cellref($_); 764 } 765 766 $token = $_[2]; 767 768 # Match an array ref. 769 if (is_array($token)) { 770 return call_user_method_array('write_row', $this, $_); 771 } 772 773 # Match number 774 if (preg_match('/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/', $token)) { 775 return call_user_method_array('write_number', $this, $_); 776 } 777 # Match http, https or ftp URL 778 elseif (preg_match('|^[fh]tt?ps?://|', $token)) { 779 return call_user_method_array('write_url', $this, $_); 780 } 781 # Match mailto: 782 elseif (preg_match('/^mailto:/', $token)) { 783 return call_user_method_array('write_url', $this, $_); 784 } 785 # Match internal or external sheet link 786 elseif (preg_match('[^(?:in|ex)ternal:]', $token)) { 787 return call_user_method_array('write_url', $this, $_); 788 } 789 # Match formula 790 elseif (preg_match('/^=/', $token)) { 791 return call_user_method_array('write_formula', $this, $_); 792 } 793 # Match blank 794 elseif ($token == '') { 795 array_splice($_, 2, 1); # remove the empty string from the parameter list 796 return call_user_method_array('write_blank', $this, $_); 797 } 798 # Default: match string 799 else { 800 return call_user_method_array('write_string', $this, $_); 801 } 802 } 803 804 ############################################################################### 805 # 806 # write_row($row, $col, $array_ref, $format) 807 # 808 # Write a row of data starting from ($row, $col). Call write_col() if any of 809 # the elements of the array ref are in turn array refs. This allows the writing 810 # of 1D or 2D arrays of data in one go. 811 # 812 # Returns: the first encountered error value or zero for no errors 813 # 814 function write_row() { 815 816 $_=func_get_args(); 817 818 # Check for a cell reference in A1 notation and substitute row and column 819 if (preg_match('/^\D/', $_[0])) { 820 $_ = $this->_substitute_cellref($_); 821 } 822 823 # Catch non array refs passed by user. 824 if (!is_array($_[2])) { 825 trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR); 826 } 827 828 list($row, $col, $tokens)=array_splice($_, 0, 3); 829 $options = $_[0]; 830 $error = 0; 831 832 foreach ($tokens as $token) { 833 834 # Check for nested arrays 835 if (is_array($token)) { 836 $ret = $this->write_col($row, $col, $token, $options); 837 } else { 838 $ret = $this->write ($row, $col, $token, $options); 839 } 840 841 # Return only the first error encountered, if any. 842 $error = $error || $ret; 843 $col++; 844 } 845 846 return $error; 847 } 848 849 ############################################################################### 850 # 851 # _XF() 852 # 853 # Returns an index to the XF record in the workbook. 854 # TODO 855 # 856 # Note: this is a function, not a method. 857 # 858 function _XF($row=false, $col=false, $format=false) { 859 860 if ($format) { 861 return $format->get_xf_index(); 862 } elseif (isset($this->_row_formats[$row])) { 863 return $this->_row_formats[$row]->get_xf_index(); 864 } elseif (isset($this->_col_formats[$col])) { 865 return $this->_col_formats[$col]->get_xf_index(); 866 } else { 867 return 0x0F; 868 } 869 } 870 871 ############################################################################### 872 # 873 # write_col($row, $col, $array_ref, $format) 874 # 875 # Write a column of data starting from ($row, $col). Call write_row() if any of 876 # the elements of the array ref are in turn array refs. This allows the writing 877 # of 1D or 2D arrays of data in one go. 878 # 879 # Returns: the first encountered error value or zero for no errors 880 # 881 function write_col() { 882 883 $_=func_get_args(); 884 885 # Check for a cell reference in A1 notation and substitute row and column 886 if (preg_match('/^\D/', $_[0])) { 887 $_ = $this->_substitute_cellref($_); 888 } 889 890 # Catch non array refs passed by user. 891 if (!is_array($_[2])) { 892 trigger_error("Not an array ref in call to write_row()!", E_USER_ERROR); 893 } 894 895 $row = array_shift($_); 896 $col = array_shift($_); 897 $tokens = array_shift($_); 898 $options = $_; 899 900 $error = 0; 901 902 foreach ($tokens as $token) { 903 904 # write() will deal with any nested arrays 905 $ret = $this->write($row, $col, $token, $options); 906 907 # Return only the first error encountered, if any. 908 $error = $error || $ret; 909 $row++; 910 } 911 912 return $error; 913 } 914 915 ############################################################################### 916 ############################################################################### 917 # 918 # Internal methods 919 # 920 921 ############################################################################### 922 # 923 # _append(), overloaded. 924 # 925 # Store Worksheet data in memory using the base class _append() or to a 926 # temporary file, the default. 927 # 928 function _append($data) { 929 930 if (func_num_args()>1) { 931 trigger_error("writeexcel_worksheet::_append() ". 932 "called with more than one argument", E_USER_ERROR); 933 } 934 935 if ($this->_using_tmpfile) { 936 937 if ($this->_debug) { 938 print "worksheet::_append() called:"; 939 for ($c=0;$c<strlen($data);$c++) { 940 if ($c%16==0) { 941 print "\n"; 942 } 943 printf("%02X ", ord($data[$c])); 944 } 945 print "\n"; 946 } 947 948 # Add CONTINUE records if necessary 949 if (strlen($data) > $this->_limit) { 950 $data = $this->_add_continue($data); 951 } 952 953 fputs($this->_filehandle, $data); 954 $this->_datasize += strlen($data); 955 } else { 956 parent::_append($data); 957 } 958 } 959 960 ############################################################################### 961 # 962 # _substitute_cellref() 963 # 964 # Substitute an Excel cell reference in A1 notation for zero based row and 965 # column values in an argument list. 966 # 967 # Ex: ("A4", "Hello") is converted to (3, 0, "Hello"). 968 # 969 // Exactly one array must be passed! 970 function _substitute_cellref($_) { 971 $cell = strtoupper(array_shift($_)); 972 973 # Convert a column range: 'A:A' or 'B:G' 974 if (preg_match('/([A-I]?[A-Z]):([A-I]?[A-Z])/', $cell, $reg)) { 975 list($dummy, $col1) = $this->_cell_to_rowcol($reg[1] .'1'); # Add a dummy row 976 list($dummy, $col2) = $this->_cell_to_rowcol($reg[2] .'1'); # Add a dummy row 977 return array_merge(array($col1, $col2), $_); 978 } 979 980 # Convert a cell range: 'A1:B7' 981 if (preg_match('/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) { 982 list($row1, $col1) = $this->_cell_to_rowcol($reg[1]); 983 list($row2, $col2) = $this->_cell_to_rowcol($reg[2]); 984 return array_merge(array($row1, $col1, $row2, $col2), $_); 985 } 986 987 # Convert a cell reference: 'A1' or 'AD2000' 988 if (preg_match('/\$?([A-I]?[A-Z]\$?\d+)/', $cell, $reg)) { 989 list($row1, $col1) = $this->_cell_to_rowcol($reg[1]); 990 return array_merge(array($row1, $col1), $_); 991 992 } 993 994 trigger_error("Unknown cell reference $cell", E_USER_ERROR); 995 } 996 997 ############################################################################### 998 # 999 # _cell_to_rowcol($cell_ref) 1000 # 1001 # Convert an Excel cell reference in A1 notation to a zero based row and column 1002 # reference; converts C1 to (0, 2). 1003 # 1004 # Returns: row, column 1005 # 1006 # TODO use functions in Utility.pm 1007 # 1008 function _cell_to_rowcol($cell) { 1009 1010 preg_match('/\$?([A-I]?[A-Z])\$?(\d+)/', $cell, $reg); 1011 1012 $col = $reg[1]; 1013 $row = $reg[2]; 1014 1015 # Convert base26 column string to number 1016 # All your Base are belong to us. 1017 $chars = preg_split('//', $col, -1, PREG_SPLIT_NO_EMPTY); 1018 $expn = 0; 1019 $col = 0; 1020 1021 while (sizeof($chars)) { 1022 $char = array_pop($chars); # LS char first 1023 $col += (ord($char) -ord('A') +1) * pow(26, $expn); 1024 $expn++; 1025 } 1026 1027 # Convert 1-index to zero-index 1028 $row--; 1029 $col--; 1030 1031 return array($row, $col); 1032 } 1033 1034 /* 1035 * This is an internal method that is used to filter elements of the 1036 * array of pagebreaks used in the _store_hbreak() and _store_vbreak() 1037 * methods. It: 1038 * 1. Removes duplicate entries from the list. 1039 * 2. Sorts the list. 1040 * 3. Removes 0 from the list if present. 1041 */ 1042 function _sort_pagebreaks($breaks) { 1043 // Hash slice to remove duplicates 1044 foreach ($breaks as $break) { 1045 $hash["$break"]=1; 1046 } 1047 1048 // Numerical sort 1049 $breaks=array_keys($hash); 1050 sort($breaks, SORT_NUMERIC); 1051 1052 // Remove zero 1053 if ($breaks[0] == 0) { 1054 array_shift($breaks); 1055 } 1056 1057 // 1000 vertical pagebreaks appears to be an internal Excel 5 limit. 1058 // It is slightly higher in Excel 97/200, approx. 1026 1059 if (sizeof($breaks) > 1000) { 1060 array_splice($breaks, 1000); 1061 } 1062 1063 return $breaks; 1064 } 1065 1066 /* 1067 * Based on the algorithm provided by Daniel Rentz of OpenOffice. 1068 */ 1069 function _encode_password($plaintext) { 1070 $chars=preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY); 1071 $count=sizeof($chars); 1072 1073 for ($c=0;$c<sizeof($chars);$c++) { 1074 $char=&$chars[$c]; 1075 $char = ord($char) << ++$i; 1076 $low_15 = $char & 0x7fff; 1077 $high_15 = $char & 0x7fff << 15; 1078 $high_15 = $high_15 >> 15; 1079 $char = $low_15 | $high_15; 1080 } 1081 1082 $password = 0x0000; 1083 1084 foreach ($chars as $char) { 1085 $password ^= $char; 1086 } 1087 1088 $password ^= $count; 1089 $password ^= 0xCE4B; 1090 1091 return $password; 1092 } 1093 1094 ############################################################################### 1095 ############################################################################### 1096 # 1097 # BIFF RECORDS 1098 # 1099 1100 ############################################################################### 1101 # 1102 # write_number($row, $col, $num, $format) 1103 # 1104 # Write a double to the specified row and column (zero indexed). 1105 # An integer can be written as a double. Excel will display an 1106 # integer. $format is optional. 1107 # 1108 # Returns 0 : normal termination 1109 # -1 : insufficient number of arguments 1110 # -2 : row or column out of range 1111 # 1112 function write_number() { 1113 1114 $_=func_get_args(); 1115 1116 # Check for a cell reference in A1 notation and substitute row and column 1117 if (preg_match('/^\D/', $_[0])) { 1118 $_ = $this->_substitute_cellref($_); 1119 } 1120 1121 # Check the number of args 1122 if (sizeof($_) < 3) { 1123 return -1; 1124 } 1125 1126 $record = 0x0203; # Record identifier 1127 $length = 0x000E; # Number of bytes to follow 1128 1129 $row = $_[0]; # Zero indexed row 1130 $col = $_[1]; # Zero indexed column 1131 $num = $_[2]; 1132 //!!! 1133 $xf = $this->_XF($row, $col, $_[3]); # The cell format 1134 1135 # Check that row and col are valid and store max and min values 1136 if ($row >= $this->_xls_rowmax) { return -2; } 1137 if ($col >= $this->_xls_colmax) { return -2; } 1138 if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; } 1139 if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; } 1140 if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; } 1141 if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; } 1142 1143 $header = pack("vv", $record, $length); 1144 $data = pack("vvv", $row, $col, $xf); 1145 $xl_double = pack("d", $num); 1146 1147 if ($this->_byte_order) { 1148 //TODO 1149 $xl_double = strrev($xl_double); 1150 } 1151 1152 $this->_append($header . $data . $xl_double); 1153 1154 return 0; 1155 } 1156 1157 ############################################################################### 1158 # 1159 # write_string ($row, $col, $string, $format) 1160 # 1161 # Write a string to the specified row and column (zero indexed). 1162 # NOTE: there is an Excel 5 defined limit of 255 characters. 1163 # $format is optional. 1164 # Returns 0 : normal termination 1165 # -1 : insufficient number of arguments 1166 # -2 : row or column out of range 1167 # -3 : long string truncated to 255 chars 1168 # 1169 function write_string() { 1170 1171 $_=func_get_args(); 1172 1173 # Check for a cell reference in A1 notation and substitute row and column 1174 if (preg_match('/^\D/', $_[0])) { 1175 $_ = $this->_substitute_cellref($_); 1176 } 1177 1178 # Check the number of args 1179 if (sizeof($_) < 3) { 1180 return -1; 1181 } 1182 1183 $record = 0x0204; # Record identifier 1184 $length = 0x0008 + strlen($_[2]); # Bytes to follow 1185 1186 $row = $_[0]; # Zero indexed row 1187 $col = $_[1]; # Zero indexed column 1188 $strlen = strlen($_[2]); 1189 $str = $_[2]; 1190 $xf = $this->_XF($row, $col, $_[3]); # The cell format 1191 1192 $str_error = 0; 1193 1194 # Check that row and col are valid and store max and min values 1195 if ($row >= $this->_xls_rowmax) { return -2; } 1196 if ($col >= $this->_xls_colmax) { return -2; } 1197 if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; } 1198 if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; } 1199 if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; } 1200 if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; } 1201 1202 if ($strlen > $this->_xls_strmax) { # LABEL must be < 255 chars 1203 $str = substr($str, 0, $this->_xls_strmax); 1204 $length = 0x0008 + $this->_xls_strmax; 1205 $strlen = $this->_xls_strmax; 1206 $str_error = -3; 1207 } 1208 1209 $header = pack("vv", $record, $length); 1210 $data = pack("vvvv", $row, $col, $xf, $strlen); 1211 1212 $this->_append($header . $data . $str); 1213 1214 return $str_error; 1215 } 1216 1217 ############################################################################### 1218 # 1219 # write_blank($row, $col, $format) 1220 # 1221 # Write a blank cell to the specified row and column (zero indexed). 1222 # A blank cell is used to specify formatting without adding a string 1223 # or a number. 1224 # 1225 # A blank cell without a format serves no purpose. Therefore, we don't write 1226 # a BLANK record unless a format is specified. This is mainly an optimisation 1227 # for the write_row() and write_col() methods. 1228 # 1229 # Returns 0 : normal termination (including no format) 1230 # -1 : insufficient number of arguments 1231 # -2 : row or column out of range 1232 # 1233 function write_blank() { 1234 1235 $_=func_get_args(); 1236 1237 # Check for a cell reference in A1 notation and substitute row and column 1238 if (preg_match('/^\D/', $_[0])) { 1239 $_ = $this->_substitute_cellref($_); 1240 } 1241 1242 # Check the number of args 1243 if (sizeof($_) < 2) { 1244 return -1; 1245 } 1246 1247 # Don't write a blank cell unless it has a format 1248 if (!isset($_[2])) { 1249 return 0; 1250 } 1251 1252 $record = 0x0201; # Record identifier 1253 $length = 0x0006; # Number of bytes to follow 1254 1255 $row = $_[0]; # Zero indexed row 1256 $col = $_[1]; # Zero indexed column 1257 $xf = $this->_XF($row, $col, $_[2]); # The cell format 1258 1259 # Check that row and col are valid and store max and min values 1260 if ($row >= $this->_xls_rowmax) { return -2; } 1261 if ($col >= $this->_xls_colmax) { return -2; } 1262 if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; } 1263 if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; } 1264 if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; } 1265 if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; } 1266 1267 $header = pack("vv", $record, $length); 1268 $data = pack("vvv", $row, $col, $xf); 1269 1270 $this->_append($header . $data); 1271 1272 return 0; 1273 } 1274 1275 ############################################################################### 1276 # 1277 # write_formula($row, $col, $formula, $format) 1278 # 1279 # Write a formula to the specified row and column (zero indexed). 1280 # The textual representation of the formula is passed to the parser in 1281 # Formula.pm which returns a packed binary string. 1282 # 1283 # $format is optional. 1284 # 1285 # Returns 0 : normal termination 1286 # -1 : insufficient number of arguments 1287 # -2 : row or column out of range 1288 # 1289 function write_formula() { 1290 1291 $_=func_get_args(); 1292 1293 # Check for a cell reference in A1 notation and substitute row and column 1294 if (preg_match('/^\D/', $_[0])) { 1295 $_ = $this->_substitute_cellref($_); 1296 } 1297 1298 # Check the number of args 1299 if (sizeof($_) < 3) { 1300 return -1; 1301 } 1302 1303 $record = 0x0006; # Record identifier 1304 $length=0; # Bytes to follow 1305 1306 $row = $_[0]; # Zero indexed row 1307 $col = $_[1]; # Zero indexed column 1308 $formula = $_[2]; # The formula text string 1309 1310 # Excel normally stores the last calculated value of the formula in $num. 1311 # Clearly we are not in a position to calculate this a priori. Instead 1312 # we set $num to zero and set the option flags in $grbit to ensure 1313 # automatic calculation of the formula when the file is opened. 1314 # 1315 $xf = $this->_XF($row, $col, $_[3]); # The cell format 1316 $num = 0x00; # Current value of formula 1317 $grbit = 0x03; # Option flags 1318 $chn = 0x0000; # Must be zero 1319 1320 # Check that row and col are valid and store max and min values 1321 if ($row >= $this->_xls_rowmax) { return -2; } 1322 if ($col >= $this->_xls_colmax) { return -2; } 1323 if ($row < $this->_dim_rowmin) { $this->_dim_rowmin = $row; } 1324 if ($row > $this->_dim_rowmax) { $this->_dim_rowmax = $row; } 1325 if ($col < $this->_dim_colmin) { $this->_dim_colmin = $col; } 1326 if ($col > $this->_dim_colmax) { $this->_dim_colmax = $col; } 1327 1328 # Strip the = sign at the beginning of the formula string 1329 $formula = preg_replace('/^=/', "", $formula); 1330 1331 # Parse the formula using the parser in Formula.pm 1332 $parser =& $this->_parser; 1333 $formula = $parser->parse_formula($formula); 1334 1335 $formlen = strlen($formula); # Length of the binary string 1336 $length = 0x16 + $formlen; # Length of the record data 1337 1338 $header = pack("vv", $record, $length); 1339 $data = pack("vvvdvVv", $row, $col, $xf, $num, 1340 $grbit, $chn, $formlen); 1341 1342 $this->_append($header . $data . $formula); 1343 1344 return 0; 1345 } 1346 1347 ############################################################################### 1348 # 1349 # write_url($row, $col, $url, $string, $format) 1350 # 1351 # Write a hyperlink. This is comprised of two elements: the visible label and 1352 # the invisible link. The visible label is the same as the link unless an 1353 # alternative string is specified. The label is written using the 1354 # write_string() method. Therefore the 255 characters string limit applies. 1355 # $string and $format are optional and their order is interchangeable. 1356 # 1357 # The hyperlink can be to a http, ftp, mail, internal sheet, or external 1358 # directory url. 1359 # 1360 # Returns 0 : normal termination 1361 # -1 : insufficient number of arguments 1362 # -2 : row or column out of range 1363 # -3 : long string truncated to 255 chars 1364 # 1365 function write_url() { 1366 1367 $_=func_get_args(); 1368 1369 # Check for a cell reference in A1 notation and substitute row and column 1370 if (preg_match('/^\D/', $_[0])) { 1371 $_ = $this->_substitute_cellref($_); 1372 } 1373 1374 # Check the number of args 1375 if (sizeof($_) < 3) { 1376 return -1; 1377 } 1378 1379 # Add start row and col to arg list 1380 return call_user_method_array('write_url_range', $this, 1381 array_merge(array($_[0], $_[1]), $_)); 1382 } 1383 1384 ############################################################################### 1385 # 1386 # write_url_range($row1, $col1, $row2, $col2, $url, $string, $format) 1387 # 1388 # This is the more general form of write_url(). It allows a hyperlink to be 1389 # written to a range of cells. This function also decides the type of hyperlink 1390 # to be written. These are either, Web (http, ftp, mailto), Internal 1391 # (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1'). 1392 # 1393 # See also write_url() above for a general description and return values. 1394 # 1395 function write_url_range() { 1396 1397 $_=func_get_args(); 1398 1399 # Check for a cell reference in A1 notation and substitute row and column 1400 if (preg_match('/^\D/', $_[0])) { 1401 $_ = $this->_substitute_cellref($_); 1402 } 1403 1404 # Check the number of args 1405 if (sizeof($_) < 5) { 1406 return -1; 1407 } 1408 1409 # Reverse the order of $string and $format if necessary. 1410 //TODO ($_[5], $_[6]) = ($_[6], $_[5]) if (ref $_[5]); 1411 1412 $url = $_[4]; 1413 1414 # Check for internal/external sheet links or default to web link 1415 if (preg_match('[^internal:]', $url)) { 1416 return call_user_method_array('_write_url_internal', $this, $_); 1417 } 1418 1419 if (preg_match('[^external:]', $url)) { 1420 return call_user_method_array('_write_url_external', $this, $_); 1421 } 1422 1423 return call_user_method_array('_write_url_web', $this, $_); 1424 } 1425 1426 ############################################################################### 1427 # 1428 # _write_url_web($row1, $col1, $row2, $col2, $url, $string, $format) 1429 # 1430 # Used to write http, ftp and mailto hyperlinks. 1431 # The link type ($options) is 0x03 is the same as absolute dir ref without 1432 # sheet. However it is differentiated by the $unknown2 data stream. 1433 # 1434 # See also write_url() above for a general description and return values. 1435 # 1436 function _write_url_web() { 1437 1438 $_=func_get_args(); 1439 1440 $record = 0x01B8; # Record identifier 1441 $length = 0x00000; # Bytes to follow 1442 1443 $row1 = $_[0]; # Start row 1444 $col1 = $_[1]; # Start column 1445 $row2 = $_[2]; # End row 1446 $col2 = $_[3]; # End column 1447 $url = $_[4]; # URL string 1448 if (isset($_[5])) { 1449 $str = $_[5]; # Alternative label 1450 } 1451 $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format 1452 1453 # Write the visible label using the write_string() method. 1454 if(!isset($str)) { 1455 $str = $url; 1456 } 1457 1458 $str_error = $this->write_string($row1, $col1, $str, $xf); 1459 1460 if ($str_error == -2) { 1461 return $str_error; 1462 } 1463 1464 # Pack the undocumented parts of the hyperlink stream 1465 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1466 $unknown2 = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B"); 1467 1468 # Pack the option flags 1469 $options = pack("V", 0x03); 1470 1471 # Convert URL to a null terminated wchar string 1472 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1473 $url = $url . "\0\0\0"; 1474 1475 # Pack the length of the URL 1476 $url_len = pack("V", strlen($url)); 1477 1478 # Calculate the data length 1479 $length = 0x34 + strlen($url); 1480 1481 # Pack the header data 1482 $header = pack("vv", $record, $length); 1483 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1484 1485 # Write the packed data 1486 $this->_append($header. 1487 $data. 1488 $unknown1. 1489 $options. 1490 $unknown2. 1491 $url_len. 1492 $url); 1493 1494 return $str_error; 1495 } 1496 1497 ############################################################################### 1498 # 1499 # _write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format) 1500 # 1501 # Used to write internal reference hyperlinks such as "Sheet1!A1". 1502 # 1503 # See also write_url() above for a general description and return values. 1504 # 1505 function _write_url_internal() { 1506 1507 $_=func_get_args(); 1508 1509 $record = 0x01B8; # Record identifier 1510 $length = 0x00000; # Bytes to follow 1511 1512 $row1 = $_[0]; # Start row 1513 $col1 = $_[1]; # Start column 1514 $row2 = $_[2]; # End row 1515 $col2 = $_[3]; # End column 1516 $url = $_[4]; # URL string 1517 if (isset($_[5])) { 1518 $str = $_[5]; # Alternative label 1519 } 1520 $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format 1521 1522 # Strip URL type 1523 $url = preg_replace('s[^internal:]', '', $url); 1524 1525 # Write the visible label 1526 if (!isset($str)) { 1527 $str = $url; 1528 } 1529 $str_error = $this->write_string($row1, $col1, $str, $xf); 1530 1531 if ($str_error == -2) { 1532 return $str_error; 1533 } 1534 1535 # Pack the undocumented parts of the hyperlink stream 1536 $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); 1537 1538 # Pack the option flags 1539 $options = pack("V", 0x08); 1540 1541 # Convert the URL type and to a null terminated wchar string 1542 $url = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY)); 1543 $url = $url . "\0\0\0"; 1544 1545 # Pack the length of the URL as chars (not wchars) 1546 $url_len = pack("V", int(strlen($url)/2)); 1547 1548 # Calculate the data length 1549 $length = 0x24 + strlen($url); 1550 1551 # Pack the header data 1552 $header = pack("vv", $record, $length); 1553 $data = pack("vvvv", $row1, $row2, $col1, $col2); 1554 1555 # Write the packed data 1556 $this->_append($header. 1557 $data. 1558 $unknown1. 1559 $options. 1560 $url_len. 1561 $url); 1562 1563 return $str_error; 1564 } 1565 1566 ############################################################################### 1567 # 1568 # _write_url_external($row1, $col1, $row2, $col2, $url, $string, $format) 1569 # 1570 # Write links to external directory names such as 'c:\foo.xls', 1571 # c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'. 1572 # 1573 # Note: Excel writes some relative links with the $dir_long string. We ignore 1574 # these cases for the sake of simpler code. 1575 # 1576 # See also write_url() above for a general description and return values. 1577 # 1578 function _write_url_external() { 1579 1580 $_=func_get_args(); 1581 1582 # Network drives are different. We will handle them separately 1583 # MS/Novell network drives and shares start with \\ 1584 if (preg_match('[^external:\\\\]', $_[4])) { 1585 return call_user_method_array('_write_url_external_net', $this, $_); 1586 } 1587 1588 $record = 0x01B8; # Record identifier 1589 $length = 0x00000; # Bytes to follow 1590 1591 $row1 = $_[0]; # Start row 1592 $col1 = $_[1]; # Start column 1593 $row2 = $_[2]; # End row 1594 $col2 = $_[3]; # End column 1595 $url = $_[4]; # URL string 1596 if (isset($_[5])) { 1597 $str = $_[5]; # Alternative label 1598 } 1599 $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format 1600 1601 # Strip URL type and change Unix dir separator to Dos style (if needed) 1602 # 1603 $url = preg_replace('[^external:]', '', $url); 1604 $url = preg_replace('[/]', "\\", $url); 1605 1606 # Write the visible label 1607 if (!isset($str)) { 1608 $str = preg_replace('[\#]', ' - ', $url); 1609 } 1610 $str_error = $this->write_string($row1, $col1, $str, $xf); 1611 if ($str_error == -2) { 1612 return $str_error; 1613 } 1614 1615 # Determine if the link is relative or absolute: 1616 # relative if link contains no dir separator, "somefile.xls" 1617 # relative if link starts with up-dir, "..\..\somefile.xls" 1618 # otherwise, absolute 1619 # 1620 $absolute = 0x02; # Bit mask 1621 1622 if (!preg_match('[\\]', $url)) { 1623 $absolute = 0x00; 1624 } 1625 1626 if (preg_match('[^\.\.\\]', $url)) { 1627 $absolute = 0x00; 1628 } 1629 1630 # Determine if the link contains a sheet reference and change some of the 1631 # parameters accordingly. 1632 # Split the dir name and sheet name (if it exists) 1633 # 1634 list($dir_long, $sheet) = preg_split('/\#/', $url); 1635 $link_type = 0x01 | $absolute; 1636 1637 //!!! 1638 if (isset($sheet)) { 1639 $link_type |= 0x08; 1640 $sheet_len = pack("V", length($sheet) + 0x01); 1641 $sheet = join("\0", split('', $sheet)); 1642 $sheet .= "\0\0\0"; 1643 } else { 1644 $sheet_len = ''; 1645 $sheet = ''; 1646 } 1647 1648 # Pack the link type 1649 $link_type = pack("V", $link_type); 1650 1651 1652 # Calculate the up-level dir count e.g.. (..\..\..\ == 3) 1653 /* TODO 1654 $up_count = 0; 1655 $up_count++ while $dir_long =~ s[^\.\.\\][]; 1656 $up_count = pack("v", $up_count); 1657 */ 1658 1659 # Store the short dos dir name (null terminated) 1660 $dir_short = $dir_long . "\0"; 1661 1662 # Store the long dir name as a wchar string (non-null terminated) 1663 $dir_long = join("\0", preg_split('', $dir_long, -1, PREG_SPLIT_NO_EMPTY)); 1664 $dir_long = $dir_long . "\0"; 1665 1666 # Pack the lengths of the dir strings 1667 $dir_short_len = pack("V", strlen($dir_short) ); 1668 $dir_long_len = pack("V", strlen($dir_long) ); 1669 $stream_len = pack("V", strlen($dir_long) + 0x06); 1670 1671 # Pack the undocumented parts of the hyperlink stream 1672 $unknown1 =pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000' ); 1673 $unknown2 =pack("H*",'0303000000000000C000000000000046' ); 1674 $unknown3 =pack("H*",'FFFFADDE000000000000000000000000000000000000000'); 1675 $unknown4 =pack("v", 0x03 ); 1676 1677 # Pack the main data stream 1678 $data = pack("vvvv", $row1, $row2, $col1, $col2) . 1679 $unknown1 . 1680 $link_type . 1681 $unknown2 . 1682 $up_count . 1683 $dir_short_len. 1684 $dir_short . 1685 $unknown3 . 1686 $stream_len . 1687 $dir_long_len . 1688 $unknown4 . 1689 $dir_long . 1690 $sheet_len . 1691 $sheet ; 1692 1693 # Pack the header data 1694 $length = strlen($data); 1695 $header = pack("vv", $record, $length); 1696 1697 # Write the packed data 1698 $this->_append($header . $data); 1699 1700 return $str_error; 1701 } 1702 1703 ############################################################################### 1704 # 1705 # write_url_xxx($row1, $col1, $row2, $col2, $url, $string, $format) 1706 # 1707 # Write links to external MS/Novell network drives and shares such as 1708 # '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'. 1709 # 1710 # See also write_url() above for a general description and return values. 1711 # 1712 function _write_url_external_net() { 1713 1714 $_=func_get_args(); 1715 1716 $record = 0x01B8; # Record identifier 1717 $length = 0x00000; # Bytes to follow 1718 1719 $row1 = $_[0]; # Start row 1720 $col1 = $_[1]; # Start column 1721 $row2 = $_[2]; # End row 1722 $col2 = $_[3]; # End column 1723 $url = $_[4]; # URL string 1724 if(isset($_[5])) { 1725 $str = $_[5]; # Alternative label 1726 } 1727 $xf = $_[6] ? $_[6] : $this->_url_format; # The cell format 1728 1729 # Strip URL type and change Unix dir separator to Dos style (if needed) 1730 # 1731 $url = preg_replace('[^external:]', "", $url); 1732 $url = preg_replace('[/]', "\\"); 1733 1734 # Write the visible label 1735 if (!isset($str)) { 1736 $str = preg_replace('[\#]', " - ", $url); 1737 } 1738 1739 $str_error = $this->write_string($row1, $col1, $str, $xf); 1740 if ($str_error == -2) { 1741 return $str_error; 1742 } 1743 1744 # Determine if the link contains a sheet reference and change some of the 1745 # parameters accordingly. 1746 # Split the dir name and sheet name (if it exists) 1747 # 1748 list($dir_long , $sheet) = preg_split('\#', $url); 1749 $link_type = 0x0103; # Always absolute 1750 1751 //!!! 1752 if (isset($sheet)) { 1753 $link_type |= 0x08; 1754 $sheet_len = pack("V", strlen($sheet) + 0x01); 1755 $sheet = join("\0", preg_split("''", $sheet, -1, PREG_SPLIT_NO_EMPTY)); 1756 $sheet .= "\0\0\0"; 1757 } else { 1758 $sheet_len = ''; 1759 $sheet = ''; 1760 } 1761 1762 # Pack the link type 1763 $link_type = pack("V", $link_type); 1764 1765 # Make the string null terminated 1766 $dir_long = $dir_long . "\0"; 1767 1768 # Pack the lengths of the dir string 1769 $dir_long_len = pack("V", strlen($dir_long)); 1770 1771 # Store the long dir name as a wchar string (non-null terminated) 1772 $dir_long = join("\0", preg_split("''", $dir_long, -1, PREG_SPLIT_NO_EMPTY)); 1773 $dir_long = $dir_long . "\0"; 1774 1775 # Pack the undocumented part of the hyperlink stream 1776 $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'); 1777 1778 # Pack the main data stream 1779 $data = pack("vvvv", $row1, $row2, $col1, $col2) . 1780 $unknown1 . 1781 $link_type . 1782 $dir_long_len . 1783 $dir_long . 1784 $sheet_len . 1785 $sheet ; 1786 1787 # Pack the header data 1788 $length = strlen($data); 1789 $header = pack("vv", $record, $length); 1790 1791 # Write the packed data 1792 $this->_append($header . $data); 1793 1794 return $str_error; 1795 } 1796 1797 ############################################################################### 1798 # 1799 # set_row($row, $height, $XF) 1800 # 1801 # This method is used to set the height and XF format for a row. 1802 # Writes the BIFF record ROW. 1803 # 1804 function set_row() { 1805 1806 $_=func_get_args(); 1807 1808 $record = 0x0208; # Record identifier 1809 $length = 0x0010; # Number of bytes to follow 1810 1811 $rw = $_[0]; # Row Number 1812 $colMic = 0x0000; # First defined column 1813 $colMac = 0x0000; # Last defined column 1814 //$miyRw; # Row height 1815 $irwMac = 0x0000; # Used by Excel to optimise loading 1816 $reserved = 0x0000; # Reserved 1817 $grbit = 0x01C0; # Option flags. (monkey) see $1 do 1818 //$ixfe; # XF index 1819 if (isset($_[2])) { 1820 $format = $_[2]; # Format object 1821 } 1822 1823 # Check for a format object 1824 if (isset($_[2])) { 1825 $ixfe = $format->get_xf_index(); 1826 } else { 1827 $ixfe = 0x0F; 1828 } 1829 1830 # Use set_row($row, undef, $XF) to set XF without setting height 1831 if (isset($_[1])) { 1832 $miyRw = $_[1] *20; 1833 } else { 1834 $miyRw = 0xff; 1835 } 1836 1837 $header = pack("vv", $record, $length); 1838 $data = pack("vvvvvvvv", $rw, $colMic, $colMac, $miyRw, 1839 $irwMac,$reserved, $grbit, $ixfe); 1840 1841 $this->_append($header . $data); 1842 1843 # Store the row sizes for use when calculating image vertices. 1844 # Also store the column formats. 1845 # 1846 # Ensure at least $row and $height 1847 if (sizeof($_) < 2) { 1848 return; 1849 } 1850 1851 $this->_row_sizes[$_[0]] = $_[1]; 1852 if (isset($_[2])) { 1853 $this->_row_formats[$_[0]] = $_[2]; 1854 } 1855 } 1856 1857 /* 1858 * Writes Excel DIMENSIONS to define the area in which there is data. 1859 */ 1860 function _store_dimensions() { 1861 $record = 0x0000; // Record identifier 1862 $length = 0x000A; // Number of bytes to follow 1863 $row_min = $this->_dim_rowmin; // First row 1864 $row_max = $this->_dim_rowmax; // Last row plus 1 1865 $col_min = $this->_dim_colmin; // First column 1866 $col_max = $this->_dim_colmax; // Last column plus 1 1867 $reserved = 0x0000; // Reserved by Excel 1868 1869 $header = pack("vv", $record, $length); 1870 $data = pack("vvvvv", $row_min, $row_max, 1871 $col_min, $col_max, $reserved); 1872 $this->_prepend($header . $data); 1873 } 1874 1875 /* 1876 * Write BIFF record Window2. 1877 */ 1878 function _store_window2() { 1879 $record = 0x023E; // Record identifier 1880 $length = 0x000A; // Number of bytes to follow 1881 1882 $grbit = 0x00B6; // Option flags 1883 $rwTop = 0x0000; // Top row visible in window 1884 $colLeft = 0x0000; // Leftmost column visible in window 1885 $rgbHdr = 0x00000000; // Row/column heading and gridline 1886 // color 1887 1888 // The options flags that comprise $grbit 1889 $fDspFmla = 0; // 0 - bit 1890 $fDspGrid = $this->_screen_gridlines; // 1 1891 $fDspRwCol = 1; // 2 1892 $fFrozen = $this->_frozen; // 3 1893 $fDspZeros = 1; // 4 1894 $fDefaultHdr = 1; // 5 1895 $fArabic = 0; // 6 1896 $fDspGuts = 1; // 7 1897 $fFrozenNoSplit = 0; // 0 - bit 1898 $fSelected = $this->_selected; // 1 1899 $fPaged = 1; // 2 1900 1901 $grbit = $fDspFmla; 1902 $grbit |= $fDspGrid << 1; 1903 $grbit |= $fDspRwCol << 2; 1904 $grbit |= $fFrozen << 3; 1905 $grbit |= $fDspZeros << 4; 1906 $grbit |= $fDefaultHdr << 5; 1907 $grbit |= $fArabic << 6; 1908 $grbit |= $fDspGuts << 7; 1909 $grbit |= $fFrozenNoSplit << 8; 1910 $grbit |= $fSelected << 9; 1911 $grbit |= $fPaged << 10; 1912 1913 $header = pack("vv", $record, $length); 1914 $data = pack("vvvV", $grbit, $rwTop, $colLeft, $rgbHdr); 1915 1916 $this->_append($header . $data); 1917 } 1918 1919 /* 1920 * Write BIFF record DEFCOLWIDTH if COLINFO records are in use. 1921 */ 1922 function _store_defcol() { 1923 $record = 0x0055; // Record identifier 1924 $length = 0x0002; // Number of bytes to follow 1925 1926 $colwidth = 0x0008; // Default column width 1927 1928 $header = pack("vv", $record, $length); 1929 $data = pack("v", $colwidth); 1930 1931 $this->_prepend($header . $data); 1932 } 1933 1934 ############################################################################### 1935 # 1936 # _store_colinfo($firstcol, $lastcol, $width, $format, $hidden) 1937 # 1938 # Write BIFF record COLINFO to define column widths 1939 # 1940 # Note: The SDK says the record length is 0x0B but Excel writes a 0x0C 1941 # length record. 1942 # 1943 function _store_colinfo($_) { 1944 1945 $record = 0x007D; # Record identifier 1946 $length = 0x000B; # Number of bytes to follow 1947 1948 $colFirst = $_[0] ? $_[0] : 0; # First formatted column 1949 $colLast = $_[1] ? $_[1] : 0; # Last formatted column 1950 $coldx = $_[2] ? $_[2] : 8.43; # Col width, 8.43 is Excel default 1951 1952 $coldx += 0.72; # Fudge. Excel subtracts 0.72 !? 1953 $coldx *= 256; # Convert to units of 1/256 of a char 1954 1955 //$ixfe; # XF index 1956 $grbit = $_[4] || 0; # Option flags 1957 $reserved = 0x00; # Reserved 1958 $format = $_[3]; # Format object 1959 1960 # Check for a format object 1961 if (isset($_[3])) { 1962 $ixfe = $format->get_xf_index(); 1963 } else { 1964 $ixfe = 0x0F; 1965 } 1966 1967 $header = pack("vv", $record, $length); 1968 $data = pack("vvvvvC", $colFirst, $colLast, $coldx, 1969 $ixfe, $grbit, $reserved); 1970 $this->_prepend($header . $data); 1971 } 1972 1973 ############################################################################### 1974 # 1975 # _store_selection($first_row, $first_col, $last_row, $last_col) 1976 # 1977 # Write BIFF record SELECTION. 1978 # 1979 function _store_selection($_) { 1980 1981 $record = 0x001D; # Record identifier 1982 $length = 0x000F; # Number of bytes to follow 1983 1984 $pnn = $this->_active_pane; # Pane position 1985 $rwAct = $_[0]; # Active row 1986 $colAct = $_[1]; # Active column 1987 $irefAct = 0; # Active cell ref 1988 $cref = 1; # Number of refs 1989 1990 $rwFirst = $_[0]; # First row in reference 1991 $colFirst = $_[1]; # First col in reference 1992 $rwLast = $_[2] ? $_[2] : $rwFirst; # Last row in reference 1993 $colLast = $_[3] ? $_[3] : $colFirst; # Last col in reference 1994 1995 # Swap last row/col for first row/col as necessary 1996 if ($rwFirst > $rwLast) { 1997 list($rwFirst, $rwLast) = array($rwLast, $rwFirst); 1998 } 1999 2000 if ($colFirst > $colLast) { 2001 list($colFirst, $colLast) = array($colLast, $colFirst); 2002 } 2003 2004 $header = pack("vv", $record, $length); 2005 $data = pack("CvvvvvvCC", $pnn, $rwAct, $colAct, 2006 $irefAct, $cref, 2007 $rwFirst, $rwLast, 2008 $colFirst, $colLast); 2009 2010 $this->_append($header . $data); 2011 } 2012 2013 /* 2014 * Write BIFF record EXTERNCOUNT to indicate the number of external 2015 * sheet references in a worksheet. 2016 * 2017 * Excel only stores references to external sheets that are used in 2018 * formulas. For simplicity we store references to all the sheets in 2019 * the workbook regardless of whether they are used or not. This reduces 2020 * the overall complexity and eliminates the need for a two way dialogue 2021 * between the formula parser the worksheet objects. 2022 */ 2023 function _store_externcount($cxals) { 2024 // $cxals Number of external references 2025 2026 $record = 0x0016; // Record identifier 2027 $length = 0x0002; // Number of bytes to follow 2028 2029 $header = pack("vv", $record, $length); 2030 $data = pack("v", $cxals); 2031 2032 $this->_prepend($header . $data); 2033 } 2034 2035 /* 2036 * Writes the Excel BIFF EXTERNSHEET record. These references are used 2037 * by formulas. A formula references a sheet name via an index. Since we 2038 * store a reference to all of the external worksheets the EXTERNSHEET 2039 * index is the same as the worksheet index. 2040 */ 2041 function _store_externsheet($sheetname) { 2042 $record = 0x0017; # Record identifier 2043 // $length Number of bytes to follow 2044 2045 // $cch Length of sheet name 2046 // $rgch Filename encoding 2047 2048 // References to the current sheet are encoded differently to 2049 // references to external sheets. 2050 if ($this->_name == $sheetname) { 2051 $sheetname = ''; 2052 $length = 0x02; // The following 2 bytes 2053 $cch = 1; // The following byte 2054 $rgch = 0x02; // Self reference 2055 } else { 2056 $length = 0x02 + strlen($sheetname); 2057 $cch = strlen($sheetname); 2058 $rgch = 0x03; // Reference to a sheet in the current 2059 // workbook 2060 } 2061 2062 $header = pack("vv", $record, $length); 2063 $data = pack("CC", $cch, $rgch); 2064 2065 $this->_prepend($header . $data . $sheetname); 2066 } 2067 2068 ############################################################################### 2069 # 2070 # _store_panes() 2071 # 2072 # 2073 # Writes the Excel BIFF PANE record. 2074 # The panes can either be frozen or thawed (unfrozen). 2075 # Frozen panes are specified in terms of a integer number of rows and columns. 2076 # Thawed panes are specified in terms of Excel's units for rows and columns. 2077 # 2078 function _store_panes() { 2079 2080 $_=func_get_args(); 2081 2082 $record = 0x0041; # Record identifier 2083 $length = 0x000A; # Number of bytes to follow 2084 2085 $y = $_[0] || 0; # Vertical split position 2086 $x = $_[1] || 0; # Horizontal split position 2087 if (isset($_[2])) { 2088 $rwTop = $_[2]; # Top row visible 2089 } 2090 if (isset($_[3])) { 2091 $colLeft = $_[3]; # Leftmost column visible 2092 } 2093 if (isset($_[4])) { 2094 $pnnAct = $_[4]; # Active pane 2095 } 2096 2097 # Code specific to frozen or thawed panes. 2098 if ($this->_frozen) { 2099 # Set default values for $rwTop and $colLeft 2100 if (!isset($rwTop)) { 2101 $rwTop = $y; 2102 } 2103 if (!isset($colLeft)) { 2104 $colLeft = $x; 2105 } 2106 } else { 2107 # Set default values for $rwTop and $colLeft 2108 if (!isset($rwTop)) { 2109 $rwTop = 0; 2110 } 2111 if (!isset($colLeft)) { 2112 $colLeft = 0; 2113 } 2114 2115 # Convert Excel's row and column units to the internal units. 2116 # The default row height is 12.75 2117 # The default column width is 8.43 2118 # The following slope and intersection values were interpolated. 2119 # 2120 $y = 20*$y + 255; 2121 $x = 113.879*$x + 390; 2122 } 2123 2124 # Determine which pane should be active. There is also the undocumented 2125 # option to override this should it be necessary: may be removed later. 2126 # 2127 if (!isset($pnnAct)) { 2128 # Bottom right 2129 if ($x != 0 && $y != 0) { 2130 $pnnAct = 0; 2131 } 2132 # Top right 2133 if ($x != 0 && $y == 0) { 2134 $pnnAct = 1; 2135 } 2136 # Bottom left 2137 if ($x == 0 && $y != 0) { 2138 $pnnAct = 2; 2139 } 2140 # Top left 2141 if ($x == 0 && $y == 0) { 2142 $pnnAct = 3; 2143 } 2144 } 2145 2146 $this->_active_pane = $pnnAct; # Used in _store_selection 2147 2148 $header = pack("vv", $record, $length); 2149 $data = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct); 2150 2151 $this->_append($header . $data); 2152 } 2153 2154 /* 2155 * Store the page setup SETUP BIFF record. 2156 */ 2157 function _store_setup() { 2158 $record = 0x00A1; // Record identifier 2159 $length = 0x0022; // Number of bytes to follow 2160 2161 $iPaperSize = $this->_paper_size; // Paper size 2162 $iScale = $this->_print_scale; // Print scaling factor 2163 $iPageStart = 0x01; // Starting page number 2164 $iFitWidth = $this->_fit_width; // Fit to number of pages wide 2165 $iFitHeight = $this->_fit_height; // Fit to number of pages high 2166 $grbit = 0x00; // Option flags 2167 $iRes = 0x0258; // Print resolution 2168 $iVRes = 0x0258; // Vertical print resolution 2169 $numHdr = $this->_margin_head; // Header Margin 2170 $numFtr = $this->_margin_foot; // Footer Margin 2171 $iCopies = 0x01; // Number of copies 2172 2173 $fLeftToRight = 0x0; // Print over then down 2174 $fLandscape = $this->_orientation; // Page orientation 2175 $fNoPls = 0x0; // Setup not read from printer 2176 $fNoColor = 0x0; // Print black and white 2177 $fDraft = 0x0; // Print draft quality 2178 $fNotes = 0x0; // Print notes 2179 $fNoOrient = 0x0; // Orientation not set 2180 $fUsePage = 0x0; // Use custom starting page 2181 2182 $grbit = $fLeftToRight; 2183 $grbit |= $fLandscape << 1; 2184 $grbit |= $fNoPls << 2; 2185 $grbit |= $fNoColor << 3; 2186 $grbit |= $fDraft << 4; 2187 $grbit |= $fNotes << 5; 2188 $grbit |= $fNoOrient << 6; 2189 $grbit |= $fUsePage << 7; 2190 2191 $numHdr = pack("d", $numHdr); 2192 $numFtr = pack("d", $numFtr); 2193 2194 if ($this->_byte_order) { 2195 $numHdr = strrev($numHdr); 2196 $numFtr = strrev($numFtr); 2197 } 2198 2199 $header = pack("vv", $record, $length); 2200 $data1 = pack("vvvvvvvv", $iPaperSize, 2201 $iScale, 2202 $iPageStart, 2203 $iFitWidth, 2204 $iFitHeight, 2205 $grbit, 2206 $iRes, 2207 $iVRes); 2208 $data2 = $numHdr . $numFtr; 2209 $data3 = pack("v", $iCopies); 2210 2211 $this->_prepend($header . $data1 . $data2 . $data3); 2212 } 2213 2214 /* 2215 * Store the header caption BIFF record. 2216 */ 2217 function _store_header() { 2218 $record = 0x0014; // Record identifier 2219 2220 $str = $this->_header; // header string 2221 $cch = strlen($str); // Length of header string 2222 $length = 1 + $cch; // Bytes to follow 2223 2224 $header = pack("vv", $record, $length); 2225 $data = pack("C", $cch); 2226 2227 $this->_append($header . $data . $str); 2228 } 2229 2230 /* 2231 * Store the footer caption BIFF record. 2232 */ 2233 function _store_footer() { 2234 $record = 0x0015; // Record identifier 2235 2236 $str = $this->_footer; // Footer string 2237 $cch = strlen($str); // Length of footer string 2238 $length = 1 + $cch; // Bytes to follow 2239 2240 $header = pack("vv", $record, $length); 2241 $data = pack("C", $cch); 2242 2243 $this->_append($header . $data . $str); 2244 } 2245 2246 /* 2247 * Store the horizontal centering HCENTER BIFF record. 2248 */ 2249 function _store_hcenter() { 2250 $record = 0x0083; // Record identifier 2251 $length = 0x0002; // Bytes to follow 2252 2253 $fHCenter = $this->_hcenter; // Horizontal centering 2254 2255 $header = pack("vv", $record, $length); 2256 $data = pack("v", $fHCenter); 2257 2258 $this->_append($header . $data); 2259 } 2260 2261 /* 2262 * Store the vertical centering VCENTER BIFF record. 2263 */ 2264 function _store_vcenter() { 2265 $record = 0x0084; // Record identifier 2266 $length = 0x0002; // Bytes to follow 2267 2268 $fVCenter = $this->_vcenter; // Horizontal centering 2269 2270 $header = pack("vv", $record, $length); 2271 $data = pack("v", $fVCenter); 2272 2273 $this->_append($header . $data); 2274 } 2275 2276 /* 2277 * Store the LEFTMARGIN BIFF record. 2278 */ 2279 function _store_margin_left() { 2280 $record = 0x0026; // Record identifier 2281 $length = 0x0008; // Bytes to follow 2282 2283 $margin = $this->_margin_left; // Margin in inches 2284 2285 $header = pack("vv", $record, $length); 2286 $data = pack("d", $margin); 2287 2288 if ($this->_byte_order) { 2289 $data = strrev($data); 2290 } 2291 2292 $this->_append($header . $data); 2293 } 2294 2295 /* 2296 * Store the RIGHTMARGIN BIFF record. 2297 */ 2298 function _store_margin_right() { 2299 $record = 0x0027; // Record identifier 2300 $length = 0x0008; // Bytes to follow 2301 2302 $margin = $this->_margin_right; // Margin in inches 2303 2304 $header = pack("vv", $record, $length); 2305 $data = pack("d", $margin); 2306 2307 if ($this->_byte_order) { 2308 $data = strrev($data); 2309 } 2310 2311 $this->_append($header . $data); 2312 } 2313 2314 /* 2315 * Store the TOPMARGIN BIFF record. 2316 */ 2317 function _store_margin_top() { 2318 $record = 0x0028; // Record identifier 2319 $length = 0x0008; // Bytes to follow 2320 2321 $margin = $this->_margin_top; // Margin in inches 2322 2323 $header = pack("vv", $record, $length); 2324 $data = pack("d", $margin); 2325 2326 if ($this->_byte_order) { 2327 $data = strrev($data); 2328 } 2329 2330 $this->_append($header . $data); 2331 } 2332 2333 /* 2334 * Store the BOTTOMMARGIN BIFF record. 2335 */ 2336 function _store_margin_bottom() { 2337 $record = 0x0029; // Record identifier 2338 $length = 0x0008; // Bytes to follow 2339 2340 $margin = $this->_margin_bottom; // Margin in inches 2341 2342 $header = pack("vv", $record, $length); 2343 $data = pack("d", $margin); 2344 2345 if ($this->_byte_order) { 2346 $data = strrev($data); 2347 } 2348 2349 $this->_append($header . $data); 2350 } 2351 2352 ############################################################################### 2353 # 2354 # merge_cells($first_row, $first_col, $last_row, $last_col) 2355 # 2356 # This is an Excel97/2000 method. It is required to perform more complicated 2357 # merging than the normal align merge in Format.pm 2358 # 2359 function merge_cells() { 2360 2361 $_=func_get_args(); 2362 2363 // Check for a cell reference in A1 notation and substitute row and column 2364 if (preg_match('/^\D/', $_[0])) { 2365 $_ = $this->_substitute_cellref($_); 2366 } 2367 2368 $record = 0x00E5; # Record identifier 2369 $length = 0x000A; # Bytes to follow 2370 2371 $cref = 1; # Number of refs 2372 $rwFirst = $_[0]; # First row in reference 2373 $colFirst = $_[1]; # First col in reference 2374 $rwLast = $_[2] || $rwFirst; # Last row in reference 2375 $colLast = $_[3] || $colFirst; # Last col in reference 2376 2377 // Swap last row/col for first row/col as necessary 2378 if ($rwFirst > $rwLast) { 2379 list($rwFirst, $rwLast) = array($rwLast, $rwFirst); 2380 } 2381 2382 if ($colFirst > $colLast) { 2383 list($colFirst, $colLast) = array($colLast, $colFirst); 2384 } 2385 2386 $header = pack("vv", $record, $length); 2387 $data = pack("vvvvv", $cref, 2388 $rwFirst, $rwLast, 2389 $colFirst, $colLast); 2390 2391 $this->_append($header . $data); 2392 } 2393 2394 /* 2395 * Write the PRINTHEADERS BIFF record. 2396 */ 2397 function _store_print_headers() { 2398 $record = 0x002a; // Record identifier 2399 $length = 0x0002; // Bytes to follow 2400 2401 $fPrintRwCol = $this->_print_headers; // Boolean flag 2402 2403 $header = pack("vv", $record, $length); 2404 $data = pack("v", $fPrintRwCol); 2405 2406 $this->_prepend($header . $data); 2407 } 2408 2409 /* 2410 * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction 2411 * with the GRIDSET record. 2412 */ 2413 function _store_print_gridlines() { 2414 $record = 0x002b; // Record identifier 2415 $length = 0x0002; // Bytes to follow 2416 2417 $fPrintGrid = $this->_print_gridlines; // Boolean flag 2418 2419 $header = pack("vv", $record, $length); 2420 $data = pack("v", $fPrintGrid); 2421 2422 $this->_prepend($header . $data); 2423 } 2424 2425 /* 2426 * Write the GRIDSET BIFF record. Must be used in conjunction with the 2427 * PRINTGRIDLINES record. 2428 */ 2429 function _store_gridset() { 2430 $record = 0x0082; // Record identifier 2431 $length = 0x0002; // Bytes to follow 2432 2433 $fGridSet = !$this->_print_gridlines; // Boolean flag 2434 2435 $header = pack("vv", $record, $length); 2436 $data = pack("v", $fGridSet); 2437 2438 $this->_prepend($header . $data); 2439 } 2440 2441 /* 2442 * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in 2443 * conjunction with the SETUP record. 2444 */ 2445 function _store_wsbool() { 2446 $record = 0x0081; # Record identifier 2447 $length = 0x0002; # Bytes to follow 2448 2449 // $grbit Option flags 2450 2451 // The only option that is of interest is the flag for fit to page. 2452 // So we set all the options in one go. 2453 if ($this->_fit_page) { 2454 $grbit = 0x05c1; 2455 } else { 2456 $grbit = 0x04c1; 2457 } 2458 2459 $header = pack("vv", $record, $length); 2460 $data = pack("v", $grbit); 2461 2462 $this->_prepend($header . $data); 2463 } 2464 2465 /* 2466 * Write the HORIZONTALPAGEBREAKS BIFF record. 2467 */ 2468 function _store_hbreak() { 2469 // Return if the user hasn't specified pagebreaks 2470 if(sizeof($this->_hbreaks)==0) { 2471 return; 2472 } 2473 2474 # Sort and filter array of page breaks 2475 $breaks = $this->_sort_pagebreaks($this->_hbreaks); 2476 2477 $record = 0x001b; // Record identifier 2478 $cbrk = sizeof($breaks); // Number of page breaks 2479 $length = ($cbrk + 1) * 2; // Bytes to follow 2480 2481 $header = pack("vv", $record, $length); 2482 $data = pack("v", $cbrk); 2483 2484 // Append each page break 2485 foreach ($breaks as $break) { 2486 $data .= pack("v", $break); 2487 } 2488 2489 $this->_prepend($header . $data); 2490 } 2491 2492 /* 2493 * Write the VERTICALPAGEBREAKS BIFF record. 2494 */ 2495 function _store_vbreak() { 2496 // Return if the user hasn't specified pagebreaks 2497 if(sizeof($this->_vbreaks)==0) { 2498 return; 2499 } 2500 2501 // Sort and filter array of page breaks 2502 $breaks = $this->_sort_pagebreaks($this->_vbreaks); 2503 2504 $record = 0x001a; // Record identifier 2505 $cbrk = sizeof($breaks); // Number of page breaks 2506 $length = ($cbrk + 1) * 2; // Bytes to follow 2507 2508 $header = pack("vv", $record, $length); 2509 $data = pack("v", $cbrk); 2510 2511 // Append each page break 2512 foreach ($breaks as $break) { 2513 $data .= pack("v", $break); 2514 } 2515 2516 $this->_prepend($header . $data); 2517 } 2518 2519 /* 2520 * Set the Biff PROTECT record to indicate that the worksheet is 2521 * protected. 2522 */ 2523 function _store_protect() { 2524 // Exit unless sheet protection has been specified 2525 if (!$this->_protect) { 2526 return; 2527 } 2528 2529 $record = 0x0012; // Record identifier 2530 $length = 0x0002; // Bytes to follow 2531 2532 $fLock = $this->_protect; // Worksheet is protected 2533 2534 $header = pack("vv", $record, $length); 2535 $data = pack("v", $fLock); 2536 2537 $this->_prepend($header . $data); 2538 } 2539 2540 /* 2541 * Write the worksheet PASSWORD record. 2542 */ 2543 function _store_password() { 2544 // Exit unless sheet protection and password have been specified 2545 if (!$this->_protect || !$this->_password) { 2546 return; 2547 } 2548 2549 $record = 0x0013; // Record identifier 2550 $length = 0x0002; // Bytes to follow 2551 2552 $wPassword = $this->_password; // Encoded password 2553 2554 $header = pack("vv", $record, $length); 2555 $data = pack("v", $wPassword); 2556 2557 $this->_prepend($header . $data); 2558 } 2559 2560 ############################################################################### 2561 # 2562 # insert_bitmap($row, $col, $filename, $x, $y, $scale_x, $scale_y) 2563 # 2564 # Insert a 24bit bitmap image in a worksheet. The main record required is 2565 # IMDATA but it must be proceeded by a OBJ record to define its position. 2566 # 2567 function insert_bitmap() { 2568 2569 $_=func_get_args(); 2570 2571 # Check for a cell reference in A1 notation and substitute row and column 2572 if (preg_match('/^\D/', $_[0])) { 2573 $_ = $this->_substitute_cellref($_); 2574 } 2575 2576 $row = $_[0]; 2577 $col = $_[1]; 2578 $bitmap = $_[2]; 2579 $x = $_[3] ? $_[3] : 0; 2580 $y = $_[4] ? $_[4] : 0; 2581 $scale_x = $_[5] ? $_[5] : 1; 2582 $scale_y = $_[6] ? $_[6] : 1; 2583 2584 list($width, $height, $size, $data) = $this->_process_bitmap($bitmap); 2585 2586 # Scale the frame of the image. 2587 $width *= $scale_x; 2588 $height *= $scale_y; 2589 2590 # Calculate the vertices of the image and write the OBJ record 2591 $this->_position_image($col, $row, $x, $y, $width, $height); 2592 2593 # Write the IMDATA record to store the bitmap data 2594 $record = 0x007f; 2595 $length = 8 + $size; 2596 $cf = 0x09; 2597 $env = 0x01; 2598 $lcb = $size; 2599 2600 $header = pack("vvvvV", $record, $length, $cf, $env, $lcb); 2601 2602 $this->_append($header . $data); 2603 } 2604 2605 /* 2606 * Calculate the vertices that define the position of the image as 2607 * required by the OBJ record. 2608 * 2609 * +------------+------------+ 2610 * | A | B | 2611 * +-----+------------+------------+ 2612 * | |(x1,y1) | | 2613 * | 1 |(A1)._______|______ | 2614 * | | | | | 2615 * | | | | | 2616 * +-----+----| BITMAP |-----+ 2617 * | | | | | 2618 * | 2 | |______________. | 2619 * | | | (B2)| 2620 * | | | (x2,y2)| 2621 * +---- +------------+------------+ 2622 * 2623 * Example of a bitmap that covers some of the area from cell A1 to 2624 * cell B2. 2625 * 2626 * Based on the width and height of the bitmap we need to calculate 8 2627 *vars: 2628 * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. 2629 * The width and height of the cells are also variable and have to be 2630 * taken into account. 2631 * The values of $col_start and $row_start are passed in from the calling 2632 * function. The values of $col_end and $row_end are calculated by 2633 * subtracting the width and height of the bitmap from the width and 2634 * height of the underlying cells. 2635 * The vertices are expressed as a percentage of the underlying cell 2636 * width as follows (rhs values are in pixels): 2637 * 2638 * x1 = X / W *1024 2639 * y1 = Y / H *256 2640 * x2 = (X-1) / W *1024 2641 * y2 = (Y-1) / H *256 2642 * 2643 * Where: X is distance from the left side of the underlying cell 2644 * Y is distance from the top of the underlying cell 2645 * W is the width of the cell 2646 * H is the height of the cell 2647 * 2648 * Note: the SDK incorrectly states that the height should be expressed 2649 * as a percentage of 1024. 2650 */ 2651 function _position_image($col_start, $row_start, $x1, $y1, 2652 $width, $height) { 2653 // $col_start Col containing upper left corner of object 2654 // $x1 Distance to left side of object 2655 2656 // $row_start Row containing top left corner of object 2657 // $y1 Distance to top of object 2658 2659 // $col_end Col containing lower right corner of object 2660 // $x2 Distance to right side of object 2661 2662 // $row_end Row containing bottom right corner of object 2663 // $y2 Distance to bottom of object 2664 2665 // $width Width of image frame 2666 // $height Height of image frame 2667 2668 // Initialise end cell to the same as the start cell 2669 $col_end = $col_start; 2670 $row_end = $row_start; 2671 2672 // Zero the specified offset if greater than the cell dimensions 2673 if ($x1 >= $this->_size_col($col_start)) { 2674 $x1 = 0; 2675 } 2676 if ($y1 >= $this->_size_row($row_start)) { 2677 $y1 = 0; 2678 } 2679 2680 $width = $width + $x1 -1; 2681 $height = $height + $y1 -1; 2682 2683 // Subtract the underlying cell widths to find the end cell of the 2684 // image 2685 while ($width >= $this->_size_col($col_end)) { 2686 $width -= $this->_size_col($col_end); 2687 $col_end++; 2688 } 2689 2690 // Subtract the underlying cell heights to find the end cell of the 2691 // image 2692 while ($height >= $this->_size_row($row_end)) { 2693 $height -= $this->_size_row($row_end); 2694 $row_end++; 2695 } 2696 2697 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a 2698 // cell with zero height or width. 2699 if ($this->_size_col($col_start) == 0) { return; } 2700 if ($this->_size_col($col_end) == 0) { return; } 2701 if ($this->_size_row($row_start) == 0) { return; } 2702 if ($this->_size_row($row_end) == 0) { return; } 2703 2704 // Convert the pixel values to the percentage value expected by Excel 2705 $x1 = $x1 / $this->_size_col($col_start) * 1024; 2706 $y1 = $y1 / $this->_size_row($row_start) * 256; 2707 $x2 = $width / $this->_size_col($col_end) * 1024; 2708 $y2 = $height / $this->_size_row($row_end) * 256; 2709 2710 $this->_store_obj_picture($col_start, $x1, $row_start, $y1, 2711 $col_end, $x2, $row_end, $y2); 2712 } 2713 2714 /* 2715 * Convert the width of a cell from user's units to pixels. By 2716 * interpolation the relationship is: y = 7x +5. If the width 2717 * hasn't been set by the user we use the default value. If the 2718 * col is hidden we use a value of zero. 2719 */ 2720 function _size_col($col) { 2721 // Look up the cell value to see if it has been changed 2722 if (isset($this->_col_sizes[$col])) { 2723 if ($this->_col_sizes[$col] == 0) { 2724 return 0; 2725 } else { 2726 return floor(7 * $this->_col_sizes[$col] + 5); 2727 } 2728 } else { 2729 return 64; 2730 } 2731 } 2732 2733 /* 2734 * Convert the height of a cell from user's units to pixels. By 2735 * interpolation # the relationship is: y = 4/3x. If the height 2736 * hasn't been set by the user we use the default value. If the 2737 * row is hidden we use a value of zero. (Not possible to hide row 2738 * yet). 2739 */ 2740 function _size_row($row) { 2741 // Look up the cell value to see if it has been changed 2742 if (isset($this->_row_sizes[$row])) { 2743 if ($this->_row_sizes[$row] == 0) { 2744 return 0; 2745 } else { 2746 return floor(4/3 * $this->_row_sizes[$row]); 2747 } 2748 } else { 2749 return 17; 2750 } 2751 } 2752 2753 /* 2754 * Store the OBJ record that precedes an IMDATA record. This could 2755 * be generalized to support other Excel objects. 2756 */ 2757 function _store_obj_picture($col_start, $x1, $row_start, $y1, 2758 $col_end, $x2, $row_end, $y2) { 2759 $record = 0x005d; // Record identifier 2760 $length = 0x003c; // Bytes to follow 2761 2762 $cObj = 0x0001; // Count of objects in file (set to 1) 2763 $OT = 0x0008; // Object type. 8 = Picture 2764 $id = 0x0001; // Object ID 2765 $grbit = 0x0614; // Option flags 2766 2767 $colL = $col_start; // Col containing upper left corner of 2768 // object 2769 $dxL = $x1; // Distance from left side of cell 2770 2771 $rwT = $row_start; // Row containing top left corner of 2772 // object 2773 $dyT = $y1; // Distance from top of cell 2774 2775 $colR = $col_end; // Col containing lower right corner of 2776 // object 2777 $dxR = $x2; // Distance from right of cell 2778 2779 $rwB = $row_end; // Row containing bottom right corner of 2780 // object 2781 $dyB = $y2; // Distance from bottom of cell 2782 2783 $cbMacro = 0x0000; // Length of FMLA structure 2784 $Reserved1 = 0x0000; // Reserved 2785 $Reserved2 = 0x0000; // Reserved 2786 2787 $icvBack = 0x09; // Background colour 2788 $icvFore = 0x09; // Foreground colour 2789 $fls = 0x00; // Fill pattern 2790 $fAuto = 0x00; // Automatic fill 2791 $icv = 0x08; // Line colour 2792 $lns = 0xff; // Line style 2793 $lnw = 0x01; // Line weight 2794 $fAutoB = 0x00; // Automatic border 2795 $frs = 0x0000; // Frame style 2796 $cf = 0x0009; // Image format, 9 = bitmap 2797 $Reserved3 = 0x0000; // Reserved 2798 $cbPictFmla = 0x0000; // Length of FMLA structure 2799 $Reserved4 = 0x0000; // Reserved 2800 $grbit2 = 0x0001; // Option flags 2801 $Reserved5 = 0x0000; // Reserved 2802 2803 $header = pack("vv", $record, $length); 2804 $data = pack("V", $cObj); 2805 $data .= pack("v", $OT); 2806 $data .= pack("v", $id); 2807 $data .= pack("v", $grbit); 2808 $data .= pack("v", $colL); 2809 $data .= pack("v", $dxL); 2810 $data .= pack("v", $rwT); 2811 $data .= pack("v", $dyT); 2812 $data .= pack("v", $colR); 2813 $data .= pack("v", $dxR); 2814 $data .= pack("v", $rwB); 2815 $data .= pack("v", $dyB); 2816 $data .= pack("v", $cbMacro); 2817 $data .= pack("V", $Reserved1); 2818 $data .= pack("v", $Reserved2); 2819 $data .= pack("C", $icvBack); 2820 $data .= pack("C", $icvFore); 2821 $data .= pack("C", $fls); 2822 $data .= pack("C", $fAuto); 2823 $data .= pack("C", $icv); 2824 $data .= pack("C", $lns); 2825 $data .= pack("C", $lnw); 2826 $data .= pack("C", $fAutoB); 2827 $data .= pack("v", $frs); 2828 $data .= pack("V", $cf); 2829 $data .= pack("v", $Reserved3); 2830 $data .= pack("v", $cbPictFmla); 2831 $data .= pack("v", $Reserved4); 2832 $data .= pack("v", $grbit2); 2833 $data .= pack("V", $Reserved5); 2834 2835 $this->_append($header . $data); 2836 } 2837 2838 /* 2839 * Convert a 24 bit bitmap into the modified internal format used by 2840 * Windows. This is described in BITMAPCOREHEADER and BITMAPCOREINFO 2841 * structures in the MSDN library. 2842 */ 2843 function _process_bitmap($bitmap) { 2844 // Open file and binmode the data in case the platform needs it. 2845 $bmp=fopen($bitmap, "rb"); 2846 if (!$bmp) { 2847 trigger_error("Could not open file '$bitmap'.", E_USER_ERROR); 2848 } 2849 2850 $data=fread($bmp, filesize($bitmap)); 2851 2852 // Check that the file is big enough to be a bitmap. 2853 if (strlen($data) <= 0x36) { 2854 trigger_error("$bitmap doesn't contain enough data.", 2855 E_USER_ERROR); 2856 } 2857 2858 // The first 2 bytes are used to identify the bitmap. 2859 if (substr($data, 0, 2) != "BM") { 2860 trigger_error("$bitmap doesn't appear to to be a ". 2861 "valid bitmap image.", E_USER_ERROR); 2862 } 2863 2864 // Remove bitmap data: ID. 2865 $data = substr($data, 2); 2866 2867 // Read and remove the bitmap size. This is more reliable than reading 2868 // the data size at offset 0x22. 2869 $array = unpack("Vsize", $data); 2870 $data = substr($data, 4); 2871 $size = $array["size"]; 2872 $size -= 0x36; # Subtract size of bitmap header. 2873 $size += 0x0C; # Add size of BIFF header. 2874 2875 // Remove bitmap data: reserved, offset, header length. 2876 $data = substr($data, 12); 2877 2878 // Read and remove the bitmap width and height. Verify the sizes. 2879 $array = unpack("Vwidth/Vheight", $data); 2880 $data = substr($data, 8); 2881 $width = $array["width"]; 2882 $height = $array["height"]; 2883 2884 if ($width > 0xFFFF) { 2885 trigger_error("$bitmap: largest image width supported is 64k.", 2886 E_USER_ERROR); 2887 } 2888 2889 if ($height > 0xFFFF) { 2890 trigger_error("$bitmap: largest image height supported is 64k.", 2891 E_USER_ERROR); 2892 } 2893 2894 // Read and remove the bitmap planes and bpp data. Verify them. 2895 $array = unpack("vplanes/vbitcount", $data); 2896 $data = substr($data, 4); 2897 $planes = $array["planes"]; 2898 $bitcount = $array["bitcount"]; 2899 2900 if ($bitcount != 24) { 2901 trigger_error("$bitmap isn't a 24bit true color bitmap.", 2902 E_USER_ERROR); 2903 } 2904 2905 if ($planes != 1) { 2906 trigger_error("$bitmap: only 1 plane supported in bitmap image.", 2907 E_USER_ERROR); 2908 } 2909 2910 // Read and remove the bitmap compression. Verify compression. 2911 $array = unpack("Vcompression", $data); 2912 $data = substr($data, 4); 2913 $compression = $array["compression"]; 2914 2915 if ($compression != 0) { 2916 trigger_error("$bitmap: compression not supported in bitmap image.", 2917 E_USER_ERROR); 2918 } 2919 2920 // Remove bitmap data: data size, hres, vres, colours, imp. colours. 2921 $data = substr($data, 20); 2922 2923 // Add the BITMAPCOREHEADER data 2924 $header = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18); 2925 $data = $header . $data; 2926 2927 return array($width, $height, $size, $data); 2928 } 2929 2930 /* 2931 * Store the window zoom factor. This should be a reduced fraction but for 2932 * simplicity we will store all fractions with a numerator of 100. 2933 */ 2934 function _store_zoom() { 2935 // If scale is 100% we don't need to write a record 2936 if ($this->_zoom == 100) { 2937 return; 2938 } 2939 2940 $record = 0x00A0; // Record identifier 2941 $length = 0x0004; // Bytes to follow 2942 2943 $header = pack("vv", $record, $length); 2944 $data = pack("vv", $this->_zoom, 100); 2945 2946 $this->_append($header . $data); 2947 } 2948 2949 } 2950 2951 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Mon Nov 26 12:29:37 2007 | par Balluche grâce à PHPXref 0.7 |
|
|