Subversion Repositories Applications.bazar

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

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