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