Subversion Repositories eFlore/Applications.cel

Rev

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

Rev Author Line No. Line
418 aurelien 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
*
1604 raphael 8
*  The author of the Spreadsheet::WriteExcel module is John McNamara
418 aurelien 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
 
1604 raphael 35
require_once 'Spreadsheet/Excel/Writer/Parser.php';
36
require_once 'Spreadsheet/Excel/Writer/BIFFwriter.php';
418 aurelien 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
1604 raphael 105
    * @see _storeDimensions()
418 aurelien 106
    */
107
    var $_dim_rowmin;
108
 
109
    /**
110
    * Last row for the DIMENSIONS record
111
    * @var integer
1604 raphael 112
    * @see _storeDimensions()
418 aurelien 113
    */
114
    var $_dim_rowmax;
115
 
116
    /**
117
    * First column for the DIMENSIONS record
118
    * @var integer
1604 raphael 119
    * @see _storeDimensions()
418 aurelien 120
    */
121
    var $_dim_colmin;
122
 
123
    /**
124
    * Last column for the DIMENSIONS record
125
    * @var integer
1604 raphael 126
    * @see _storeDimensions()
418 aurelien 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
    /**
1604 raphael 281
    * Whether to display RightToLeft.
282
    * @var integer
283
    */
284
    var $_Arabic;
285
 
286
    /**
287
    * Whether to use outline.
288
    * @var integer
289
    */
290
    var $_outline_on;
291
 
292
    /**
293
    * Auto outline styles.
294
    * @var bool
295
    */
296
    var $_outline_style;
297
 
298
    /**
299
    * Whether to have outline summary below.
300
    * @var bool
301
    */
302
    var $_outline_below;
303
 
304
    /**
305
    * Whether to have outline summary at the right.
306
    * @var bool
307
    */
308
    var $_outline_right;
309
 
310
    /**
311
    * Outline row level.
312
    * @var integer
313
    */
314
    var $_outline_row_level;
315
 
316
    /**
317
    * Whether to fit to page when printing or not.
318
    * @var bool
319
    */
320
    var $_fit_page;
321
 
322
    /**
323
    * Number of pages to fit wide
324
    * @var integer
325
    */
326
    var $_fit_width;
327
 
328
    /**
329
    * Number of pages to fit high
330
    * @var integer
331
    */
332
    var $_fit_height;
333
 
334
    /**
335
    * Reference to the total number of strings in the workbook
336
    * @var integer
337
    */
338
    var $_str_total;
339
 
340
    /**
341
    * Reference to the number of unique strings in the workbook
342
    * @var integer
343
    */
344
    var $_str_unique;
345
 
346
    /**
347
    * Reference to the array containing all the unique strings in the workbook
348
    * @var array
349
    */
350
    var $_str_table;
351
 
352
    /**
353
     * Number of merged cell ranges in actual record
354
     * @var int $_merged_cells_counter
355
     */
356
    var $_merged_cells_counter = 0;
357
 
358
    /**
359
     * Number of actual mergedcells record
360
     * @var int $_merged_cells_record
361
     */
362
    var $_merged_cells_record = 0;
363
 
364
    /**
365
    * Merged cell ranges
366
    * @var array
367
    */
368
    var $_merged_ranges;
369
 
370
    /**
371
    * Charset encoding currently used when calling writeString()
372
    * @var string
373
    */
374
    var $_input_encoding;
375
 
376
    /**
418 aurelien 377
    * Constructor
378
    *
379
    * @param string  $name         The name of the new worksheet
380
    * @param integer $index        The index of the new worksheet
381
    * @param mixed   &$activesheet The current activesheet of the workbook we belong to
1604 raphael 382
    * @param mixed   &$firstsheet  The first worksheet in the workbook we belong to
418 aurelien 383
    * @param mixed   &$url_format  The default format for hyperlinks
384
    * @param mixed   &$parser      The formula parser created for the Workbook
1604 raphael 385
    * @param string  $tmp_dir      The path to the directory for temporary files
386
    * @access private
418 aurelien 387
    */
1604 raphael 388
    function Spreadsheet_Excel_Writer_Worksheet($BIFF_version, $name,
389
                                                $index, &$activesheet,
390
                                                &$firstsheet, &$str_total,
391
                                                &$str_unique, &$str_table,
392
                                                &$url_format, &$parser,
393
                                                $tmp_dir)
418 aurelien 394
    {
395
        // It needs to call its parent's constructor explicitly
396
        $this->Spreadsheet_Excel_Writer_BIFFwriter();
1604 raphael 397
        $this->_BIFF_version   = $BIFF_version;
418 aurelien 398
        $rowmax                = 65536; // 16384 in Excel 5
399
        $colmax                = 256;
1604 raphael 400
 
418 aurelien 401
        $this->name            = $name;
402
        $this->index           = $index;
403
        $this->activesheet     = &$activesheet;
404
        $this->firstsheet      = &$firstsheet;
1604 raphael 405
        $this->_str_total      = &$str_total;
406
        $this->_str_unique     = &$str_unique;
407
        $this->_str_table      = &$str_table;
418 aurelien 408
        $this->_url_format     = &$url_format;
409
        $this->_parser         = &$parser;
1604 raphael 410
 
418 aurelien 411
        //$this->ext_sheets      = array();
1604 raphael 412
        $this->_filehandle     = '';
418 aurelien 413
        $this->_using_tmpfile  = true;
414
        //$this->fileclosed      = 0;
415
        //$this->offset          = 0;
416
        $this->_xls_rowmax     = $rowmax;
417
        $this->_xls_colmax     = $colmax;
418
        $this->_xls_strmax     = 255;
419
        $this->_dim_rowmin     = $rowmax + 1;
420
        $this->_dim_rowmax     = 0;
421
        $this->_dim_colmin     = $colmax + 1;
422
        $this->_dim_colmax     = 0;
423
        $this->_colinfo        = array();
424
        $this->_selection      = array(0,0,0,0);
425
        $this->_panes          = array();
426
        $this->_active_pane    = 3;
427
        $this->_frozen         = 0;
428
        $this->selected        = 0;
1604 raphael 429
 
418 aurelien 430
        $this->_paper_size      = 0x0;
431
        $this->_orientation     = 0x1;
432
        $this->_header          = '';
433
        $this->_footer          = '';
434
        $this->_hcenter         = 0;
435
        $this->_vcenter         = 0;
436
        $this->_margin_head     = 0.50;
437
        $this->_margin_foot     = 0.50;
438
        $this->_margin_left     = 0.75;
439
        $this->_margin_right    = 0.75;
440
        $this->_margin_top      = 1.00;
441
        $this->_margin_bottom   = 1.00;
1604 raphael 442
 
443
        $this->title_rowmin     = null;
444
        $this->title_rowmax     = null;
445
        $this->title_colmin     = null;
446
        $this->title_colmax     = null;
447
        $this->print_rowmin     = null;
448
        $this->print_rowmax     = null;
449
        $this->print_colmin     = null;
450
        $this->print_colmax     = null;
451
 
452
        $this->_print_gridlines  = 1;
453
        $this->_screen_gridlines = 1;
454
        $this->_print_headers    = 0;
455
 
418 aurelien 456
        $this->_fit_page        = 0;
457
        $this->_fit_width       = 0;
458
        $this->_fit_height      = 0;
1604 raphael 459
 
418 aurelien 460
        $this->_hbreaks         = array();
461
        $this->_vbreaks         = array();
1604 raphael 462
 
418 aurelien 463
        $this->_protect         = 0;
1604 raphael 464
        $this->_password        = null;
465
 
418 aurelien 466
        $this->col_sizes        = array();
1604 raphael 467
        $this->_row_sizes        = array();
468
 
418 aurelien 469
        $this->_zoom            = 100;
470
        $this->_print_scale     = 100;
1604 raphael 471
 
472
        $this->_outline_row_level = 0;
473
        $this->_outline_style     = 0;
474
        $this->_outline_below     = 1;
475
        $this->_outline_right     = 1;
476
        $this->_outline_on        = 1;
477
        $this->_Arabic            = 0;
478
 
479
        $this->_merged_ranges     = array();
480
 
481
        $this->_input_encoding    = '';
482
 
483
        $this->_dv                = array();
484
 
485
        $this->_tmp_dir           = $tmp_dir;
486
        $this->_tmp_file          = '';
487
 
418 aurelien 488
        $this->_initialize();
489
    }
1604 raphael 490
 
418 aurelien 491
    /**
492
    * Open a tmp file to store the majority of the Worksheet data. If this fails,
493
    * for example due to write permissions, store the data in memory. This can be
494
    * slow for large files.
495
    *
496
    * @access private
497
    */
498
    function _initialize()
499
    {
1604 raphael 500
        if ($this->_using_tmpfile == false) {
501
            return;
502
        }
503
 
504
        if ($this->_tmp_dir === '' && ini_get('open_basedir') === true) {
505
            // open_basedir restriction in effect - store data in memory
506
            // ToDo: Let the error actually have an effect somewhere
507
            $this->_using_tmpfile = false;
508
            return new PEAR_Error('Temp file could not be opened since open_basedir restriction in effect - please use setTmpDir() - using memory storage instead');
509
        }
510
 
418 aurelien 511
        // Open tmp file for storing Worksheet data
1604 raphael 512
        if ($this->_tmp_dir === '') {
513
            $fh = tmpfile();
514
        } else {
515
            // For people with open base dir restriction
516
            $this->_tmp_file = tempnam($this->_tmp_dir, "Spreadsheet_Excel_Writer");
517
            $fh = @fopen($this->_tmp_file, "w+b");
418 aurelien 518
        }
1604 raphael 519
 
520
        if ($fh === false) {
418 aurelien 521
            // If tmpfile() fails store data in memory
522
            $this->_using_tmpfile = false;
1604 raphael 523
        } else {
524
            // Store filehandle
525
            $this->_filehandle = $fh;
418 aurelien 526
        }
527
    }
1604 raphael 528
 
418 aurelien 529
    /**
530
    * Add data to the beginning of the workbook (note the reverse order)
531
    * and to the end of the workbook.
532
    *
1604 raphael 533
    * @access public
418 aurelien 534
    * @see Spreadsheet_Excel_Writer_Workbook::storeWorkbook()
1604 raphael 535
    * @param array $sheetnames The array of sheetnames from the Workbook this
418 aurelien 536
    *                          worksheet belongs to
537
    */
538
    function close($sheetnames)
539
    {
540
        $num_sheets = count($sheetnames);
1604 raphael 541
 
418 aurelien 542
        /***********************************************
543
        * Prepend in reverse order!!
544
        */
1604 raphael 545
 
418 aurelien 546
        // Prepend the sheet dimensions
1604 raphael 547
        $this->_storeDimensions();
548
 
418 aurelien 549
        // Prepend the sheet password
550
        $this->_storePassword();
1604 raphael 551
 
418 aurelien 552
        // Prepend the sheet protection
553
        $this->_storeProtect();
1604 raphael 554
 
418 aurelien 555
        // Prepend the page setup
556
        $this->_storeSetup();
1604 raphael 557
 
558
        /* FIXME: margins are actually appended */
418 aurelien 559
        // Prepend the bottom margin
560
        $this->_storeMarginBottom();
1604 raphael 561
 
418 aurelien 562
        // Prepend the top margin
563
        $this->_storeMarginTop();
1604 raphael 564
 
418 aurelien 565
        // Prepend the right margin
566
        $this->_storeMarginRight();
1604 raphael 567
 
418 aurelien 568
        // Prepend the left margin
569
        $this->_storeMarginLeft();
1604 raphael 570
 
418 aurelien 571
        // Prepend the page vertical centering
572
        $this->_storeVcenter();
1604 raphael 573
 
418 aurelien 574
        // Prepend the page horizontal centering
575
        $this->_storeHcenter();
1604 raphael 576
 
418 aurelien 577
        // Prepend the page footer
578
        $this->_storeFooter();
1604 raphael 579
 
418 aurelien 580
        // Prepend the page header
581
        $this->_storeHeader();
1604 raphael 582
 
418 aurelien 583
        // Prepend the vertical page breaks
584
        $this->_storeVbreak();
1604 raphael 585
 
418 aurelien 586
        // Prepend the horizontal page breaks
587
        $this->_storeHbreak();
1604 raphael 588
 
418 aurelien 589
        // Prepend WSBOOL
590
        $this->_storeWsbool();
1604 raphael 591
 
418 aurelien 592
        // Prepend GRIDSET
593
        $this->_storeGridset();
1604 raphael 594
 
595
         //  Prepend GUTS
596
        if ($this->_BIFF_version == 0x0500) {
597
            $this->_storeGuts();
598
        }
599
 
418 aurelien 600
        // Prepend PRINTGRIDLINES
601
        $this->_storePrintGridlines();
1604 raphael 602
 
418 aurelien 603
        // Prepend PRINTHEADERS
604
        $this->_storePrintHeaders();
1604 raphael 605
 
418 aurelien 606
        // Prepend EXTERNSHEET references
1604 raphael 607
        if ($this->_BIFF_version == 0x0500) {
608
            for ($i = $num_sheets; $i > 0; $i--) {
609
                $sheetname = $sheetnames[$i-1];
610
                $this->_storeExternsheet($sheetname);
611
            }
418 aurelien 612
        }
1604 raphael 613
 
418 aurelien 614
        // Prepend the EXTERNCOUNT of external references.
1604 raphael 615
        if ($this->_BIFF_version == 0x0500) {
616
            $this->_storeExterncount($num_sheets);
617
        }
618
 
418 aurelien 619
        // Prepend the COLINFO records if they exist
1604 raphael 620
        if (!empty($this->_colinfo)) {
621
            $colcount = count($this->_colinfo);
622
            for ($i = 0; $i < $colcount; $i++) {
418 aurelien 623
                $this->_storeColinfo($this->_colinfo[$i]);
624
            }
625
            $this->_storeDefcol();
626
        }
1604 raphael 627
 
418 aurelien 628
        // Prepend the BOF record
629
        $this->_storeBof(0x0010);
1604 raphael 630
 
418 aurelien 631
        /*
632
        * End of prepend. Read upwards from here.
633
        ***********************************************/
1604 raphael 634
 
418 aurelien 635
        // Append
636
        $this->_storeWindow2();
637
        $this->_storeZoom();
638
        if (!empty($this->_panes)) {
639
            $this->_storePanes($this->_panes);
640
        }
641
        $this->_storeSelection($this->_selection);
1604 raphael 642
        $this->_storeMergedCells();
643
        /* TODO: add data validity */
644
        /*if ($this->_BIFF_version == 0x0600) {
645
            $this->_storeDataValidity();
646
        }*/
418 aurelien 647
        $this->_storeEof();
1604 raphael 648
 
649
        if ( $this->_tmp_file != '' ) {
650
          if ( $this->_filehandle ) {
651
            fclose($this->_filehandle);
652
            $this->_filehandle = '';
653
          }
654
          @unlink($this->_tmp_file);
655
          $this->_tmp_file      = '';
656
          $this->_using_tmpfile = true;
657
        }
418 aurelien 658
    }
1604 raphael 659
 
418 aurelien 660
    /**
661
    * Retrieve the worksheet name.
662
    * This is usefull when creating worksheets without a name.
663
    *
664
    * @access public
665
    * @return string The worksheet's name
666
    */
667
    function getName()
668
    {
669
        return $this->name;
670
    }
1604 raphael 671
 
418 aurelien 672
    /**
673
    * Retrieves data from memory in one chunk, or from disk in $buffer
674
    * sized chunks.
675
    *
676
    * @return string The data
677
    */
678
    function getData()
679
    {
680
        $buffer = 4096;
1604 raphael 681
 
418 aurelien 682
        // Return data stored in memory
1604 raphael 683
        if (isset($this->_data)) {
418 aurelien 684
            $tmp   = $this->_data;
685
            unset($this->_data);
686
            $fh    = $this->_filehandle;
687
            if ($this->_using_tmpfile) {
688
                fseek($fh, 0);
689
            }
1604 raphael 690
            return $tmp;
418 aurelien 691
        }
692
        // Return data stored on disk
1604 raphael 693
        if ($this->_using_tmpfile) {
418 aurelien 694
            if ($tmp = fread($this->_filehandle, $buffer)) {
1604 raphael 695
                return $tmp;
418 aurelien 696
            }
697
        }
1604 raphael 698
 
418 aurelien 699
        // No data to return
1604 raphael 700
        return '';
418 aurelien 701
    }
1604 raphael 702
 
418 aurelien 703
    /**
1604 raphael 704
    * Sets a merged cell range
705
    *
706
    * @access public
707
    * @param integer $first_row First row of the area to merge
708
    * @param integer $first_col First column of the area to merge
709
    * @param integer $last_row  Last row of the area to merge
710
    * @param integer $last_col  Last column of the area to merge
711
    */
712
    function setMerge($first_row, $first_col, $last_row, $last_col)
713
    {
714
        if (($last_row < $first_row) || ($last_col < $first_col)) {
715
            return;
716
        }
717
 
718
        $max_record_ranges = floor(($this->_limit - 6) / 8);
719
        if($this->_merged_cells_counter >= $max_record_ranges)
720
          {
721
            $this->_merged_cells_record++;
722
            $this->_merged_cells_counter = 0;
723
          }
724
 
725
        // don't check rowmin, rowmax, etc... because we don't know when this
726
        // is going to be called
727
        $this->_merged_ranges[$this->_merged_cells_record][] = array($first_row, $first_col, $last_row, $last_col);
728
        $this->_merged_cells_counter++;
729
    }
730
 
731
    /**
418 aurelien 732
    * Set this worksheet as a selected worksheet,
733
    * i.e. the worksheet has its tab highlighted.
734
    *
735
    * @access public
736
    */
737
    function select()
738
    {
739
        $this->selected = 1;
740
    }
1604 raphael 741
 
418 aurelien 742
    /**
743
    * Set this worksheet as the active worksheet,
744
    * i.e. the worksheet that is displayed when the workbook is opened.
745
    * Also set it as selected.
746
    *
747
    * @access public
748
    */
749
    function activate()
750
    {
751
        $this->selected = 1;
752
        $this->activesheet = $this->index;
753
    }
1604 raphael 754
 
418 aurelien 755
    /**
756
    * Set this worksheet as the first visible sheet.
757
    * This is necessary when there are a large number of worksheets and the
758
    * activated worksheet is not visible on the screen.
759
    *
760
    * @access public
761
    */
762
    function setFirstSheet()
763
    {
764
        $this->firstsheet = $this->index;
765
    }
1604 raphael 766
 
418 aurelien 767
    /**
768
    * Set the worksheet protection flag
769
    * to prevent accidental modification and to
770
    * hide formulas if the locked and hidden format properties have been set.
771
    *
772
    * @access public
773
    * @param string $password The password to use for protecting the sheet.
774
    */
775
    function protect($password)
776
    {
777
        $this->_protect   = 1;
778
        $this->_password  = $this->_encodePassword($password);
779
    }
1604 raphael 780
 
418 aurelien 781
    /**
782
    * Set the width of a single column or a range of columns.
783
    *
784
    * @access public
785
    * @param integer $firstcol first column on the range
786
    * @param integer $lastcol  last column on the range
787
    * @param integer $width    width to set
788
    * @param mixed   $format   The optional XF format to apply to the columns
789
    * @param integer $hidden   The optional hidden atribute
1604 raphael 790
    * @param integer $level    The optional outline level
418 aurelien 791
    */
1604 raphael 792
    function setColumn($firstcol, $lastcol, $width, $format = null, $hidden = 0, $level = 0)
793
    { // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06
794
        // look for any ranges this might overlap and remove, size or split where necessary
795
        foreach ($this->_colinfo as $key => $colinfo)
796
        {
797
            $existing_start = $colinfo[0]; $existing_end = $colinfo[1];
798
            // if the new range starts within another range
799
            if ($firstcol > $existing_start && $firstcol < $existing_end)
800
            { // trim the existing range to the beginning of the new range
801
                $this->_colinfo[$key][1] = $firstcol - 1;
802
                // if the new range lies WITHIN the existing range
803
                if ($lastcol < $existing_end)
804
                { // split the existing range by adding a range after our new range
805
                    $this->_colinfo[] = array($lastcol+1, $existing_end, $colinfo[2], &$colinfo[3], $colinfo[4], $colinfo[5]);
806
                }
807
            } // if the new range ends inside an existing range
808
            elseif ($lastcol > $existing_start && $lastcol < $existing_end)
809
            { // trim the existing range to the end of the new range
810
                $this->_colinfo[$key][0] = $lastcol + 1;
811
            } // if the new range completely overlaps the existing range
812
            elseif ($firstcol <= $existing_start && $lastcol >= $existing_end)
813
            {
814
                unset($this->_colinfo[$key]);
815
            }
816
        } // added by Dan Lynn <dan@spiderweblabs.com) on 2006-12-06
817
        // regenerate keys
818
        $this->_colinfo = array_values($this->_colinfo);
819
        $this->_colinfo[] = array($firstcol, $lastcol, $width, &$format, $hidden, $level);
820
        // Set width to zero if column is hidden
821
        $width = ($hidden) ? 0 : $width;
822
        for ($col = $firstcol; $col <= $lastcol; $col++)
823
        {
824
            $this->col_sizes[$col] = $width;
825
        }
418 aurelien 826
    }
1604 raphael 827
 
418 aurelien 828
    /**
829
    * Set which cell or cells are selected in a worksheet
830
    *
831
    * @access public
832
    * @param integer $first_row    first row in the selected quadrant
833
    * @param integer $first_column first column in the selected quadrant
834
    * @param integer $last_row     last row in the selected quadrant
835
    * @param integer $last_column  last column in the selected quadrant
836
    */
837
    function setSelection($first_row,$first_column,$last_row,$last_column)
838
    {
839
        $this->_selection = array($first_row,$first_column,$last_row,$last_column);
840
    }
1604 raphael 841
 
418 aurelien 842
    /**
843
    * Set panes and mark them as frozen.
844
    *
845
    * @access public
846
    * @param array $panes This is the only parameter received and is composed of the following:
847
    *                     0 => Vertical split position,
848
    *                     1 => Horizontal split position
849
    *                     2 => Top row visible
850
    *                     3 => Leftmost column visible
851
    *                     4 => Active pane
852
    */
853
    function freezePanes($panes)
854
    {
855
        $this->_frozen = 1;
856
        $this->_panes  = $panes;
857
    }
1604 raphael 858
 
418 aurelien 859
    /**
860
    * Set panes and mark them as unfrozen.
861
    *
862
    * @access public
863
    * @param array $panes This is the only parameter received and is composed of the following:
864
    *                     0 => Vertical split position,
865
    *                     1 => Horizontal split position
866
    *                     2 => Top row visible
867
    *                     3 => Leftmost column visible
868
    *                     4 => Active pane
869
    */
870
    function thawPanes($panes)
871
    {
872
        $this->_frozen = 0;
873
        $this->_panes  = $panes;
874
    }
1604 raphael 875
 
418 aurelien 876
    /**
877
    * Set the page orientation as portrait.
878
    *
879
    * @access public
880
    */
881
    function setPortrait()
882
    {
883
        $this->_orientation = 1;
884
    }
1604 raphael 885
 
418 aurelien 886
    /**
887
    * Set the page orientation as landscape.
888
    *
889
    * @access public
890
    */
891
    function setLandscape()
892
    {
893
        $this->_orientation = 0;
894
    }
1604 raphael 895
 
418 aurelien 896
    /**
897
    * Set the paper type. Ex. 1 = US Letter, 9 = A4
898
    *
899
    * @access public
900
    * @param integer $size The type of paper size to use
901
    */
902
    function setPaper($size = 0)
903
    {
904
        $this->_paper_size = $size;
905
    }
1604 raphael 906
 
907
 
418 aurelien 908
    /**
909
    * Set the page header caption and optional margin.
910
    *
911
    * @access public
912
    * @param string $string The header text
913
    * @param float  $margin optional head margin in inches.
914
    */
915
    function setHeader($string,$margin = 0.50)
916
    {
917
        if (strlen($string) >= 255) {
918
            //carp 'Header string must be less than 255 characters';
919
            return;
920
        }
921
        $this->_header      = $string;
922
        $this->_margin_head = $margin;
923
    }
1604 raphael 924
 
418 aurelien 925
    /**
926
    * Set the page footer caption and optional margin.
927
    *
928
    * @access public
929
    * @param string $string The footer text
930
    * @param float  $margin optional foot margin in inches.
931
    */
932
    function setFooter($string,$margin = 0.50)
933
    {
934
        if (strlen($string) >= 255) {
935
            //carp 'Footer string must be less than 255 characters';
936
            return;
937
        }
938
        $this->_footer      = $string;
939
        $this->_margin_foot = $margin;
940
    }
1604 raphael 941
 
418 aurelien 942
    /**
943
    * Center the page horinzontally.
944
    *
945
    * @access public
946
    * @param integer $center the optional value for centering. Defaults to 1 (center).
947
    */
948
    function centerHorizontally($center = 1)
949
    {
950
        $this->_hcenter = $center;
951
    }
1604 raphael 952
 
418 aurelien 953
    /**
954
    * Center the page vertically.
955
    *
956
    * @access public
957
    * @param integer $center the optional value for centering. Defaults to 1 (center).
958
    */
959
    function centerVertically($center = 1)
960
    {
961
        $this->_vcenter = $center;
962
    }
1604 raphael 963
 
418 aurelien 964
    /**
965
    * Set all the page margins to the same value in inches.
966
    *
967
    * @access public
968
    * @param float $margin The margin to set in inches
969
    */
970
    function setMargins($margin)
971
    {
972
        $this->setMarginLeft($margin);
973
        $this->setMarginRight($margin);
974
        $this->setMarginTop($margin);
975
        $this->setMarginBottom($margin);
976
    }
1604 raphael 977
 
418 aurelien 978
    /**
979
    * Set the left and right margins to the same value in inches.
980
    *
981
    * @access public
982
    * @param float $margin The margin to set in inches
983
    */
984
    function setMargins_LR($margin)
985
    {
986
        $this->setMarginLeft($margin);
987
        $this->setMarginRight($margin);
988
    }
1604 raphael 989
 
418 aurelien 990
    /**
991
    * Set the top and bottom margins to the same value in inches.
992
    *
993
    * @access public
994
    * @param float $margin The margin to set in inches
995
    */
996
    function setMargins_TB($margin)
997
    {
998
        $this->setMarginTop($margin);
999
        $this->setMarginBottom($margin);
1000
    }
1604 raphael 1001
 
418 aurelien 1002
    /**
1003
    * Set the left margin in inches.
1004
    *
1005
    * @access public
1006
    * @param float $margin The margin to set in inches
1007
    */
1008
    function setMarginLeft($margin = 0.75)
1009
    {
1010
        $this->_margin_left = $margin;
1011
    }
1604 raphael 1012
 
418 aurelien 1013
    /**
1014
    * Set the right margin in inches.
1015
    *
1016
    * @access public
1017
    * @param float $margin The margin to set in inches
1018
    */
1019
    function setMarginRight($margin = 0.75)
1020
    {
1021
        $this->_margin_right = $margin;
1022
    }
1604 raphael 1023
 
418 aurelien 1024
    /**
1025
    * Set the top margin in inches.
1026
    *
1027
    * @access public
1028
    * @param float $margin The margin to set in inches
1029
    */
1030
    function setMarginTop($margin = 1.00)
1031
    {
1032
        $this->_margin_top = $margin;
1033
    }
1604 raphael 1034
 
418 aurelien 1035
    /**
1036
    * Set the bottom margin in inches.
1037
    *
1038
    * @access public
1039
    * @param float $margin The margin to set in inches
1040
    */
1041
    function setMarginBottom($margin = 1.00)
1042
    {
1043
        $this->_margin_bottom = $margin;
1044
    }
1604 raphael 1045
 
418 aurelien 1046
    /**
1047
    * Set the rows to repeat at the top of each printed page.
1048
    *
1049
    * @access public
1050
    * @param integer $first_row First row to repeat
1051
    * @param integer $last_row  Last row to repeat. Optional.
1052
    */
1604 raphael 1053
    function repeatRows($first_row, $last_row = null)
418 aurelien 1054
    {
1055
        $this->title_rowmin  = $first_row;
1056
        if (isset($last_row)) { //Second row is optional
1057
            $this->title_rowmax  = $last_row;
1604 raphael 1058
        } else {
418 aurelien 1059
            $this->title_rowmax  = $first_row;
1060
        }
1061
    }
1604 raphael 1062
 
418 aurelien 1063
    /**
1064
    * Set the columns to repeat at the left hand side of each printed page.
1065
    *
1066
    * @access public
1067
    * @param integer $first_col First column to repeat
1068
    * @param integer $last_col  Last column to repeat. Optional.
1069
    */
1604 raphael 1070
    function repeatColumns($first_col, $last_col = null)
418 aurelien 1071
    {
1072
        $this->title_colmin  = $first_col;
1073
        if (isset($last_col)) { // Second col is optional
1074
            $this->title_colmax  = $last_col;
1604 raphael 1075
        } else {
418 aurelien 1076
            $this->title_colmax  = $first_col;
1077
        }
1078
    }
1604 raphael 1079
 
418 aurelien 1080
    /**
1081
    * Set the area of each worksheet that will be printed.
1082
    *
1083
    * @access public
1084
    * @param integer $first_row First row of the area to print
1085
    * @param integer $first_col First column of the area to print
1086
    * @param integer $last_row  Last row of the area to print
1087
    * @param integer $last_col  Last column of the area to print
1088
    */
1089
    function printArea($first_row, $first_col, $last_row, $last_col)
1090
    {
1091
        $this->print_rowmin  = $first_row;
1092
        $this->print_colmin  = $first_col;
1093
        $this->print_rowmax  = $last_row;
1094
        $this->print_colmax  = $last_col;
1095
    }
1604 raphael 1096
 
1097
 
418 aurelien 1098
    /**
1604 raphael 1099
    * Set the option to hide gridlines on the printed page.
418 aurelien 1100
    *
1101
    * @access public
1102
    */
1103
    function hideGridlines()
1104
    {
1105
        $this->_print_gridlines = 0;
1106
    }
1604 raphael 1107
 
418 aurelien 1108
    /**
1604 raphael 1109
    * Set the option to hide gridlines on the worksheet (as seen on the screen).
1110
    *
1111
    * @access public
1112
    */
1113
    function hideScreenGridlines()
1114
    {
1115
        $this->_screen_gridlines = 0;
1116
    }
1117
 
1118
    /**
418 aurelien 1119
    * Set the option to print the row and column headers on the printed page.
1120
    *
1121
    * @access public
1122
    * @param integer $print Whether to print the headers or not. Defaults to 1 (print).
1123
    */
1124
    function printRowColHeaders($print = 1)
1125
    {
1126
        $this->_print_headers = $print;
1127
    }
1604 raphael 1128
 
418 aurelien 1129
    /**
1130
    * Set the vertical and horizontal number of pages that will define the maximum area printed.
1131
    * It doesn't seem to work with OpenOffice.
1132
    *
1133
    * @access public
1134
    * @param  integer $width  Maximun width of printed area in pages
1135
    * @param  integer $height Maximun heigth of printed area in pages
1136
    * @see setPrintScale()
1137
    */
1138
    function fitToPages($width, $height)
1139
    {
1140
        $this->_fit_page      = 1;
1141
        $this->_fit_width     = $width;
1142
        $this->_fit_height    = $height;
1143
    }
1604 raphael 1144
 
418 aurelien 1145
    /**
1146
    * Store the horizontal page breaks on a worksheet (for printing).
1147
    * The breaks represent the row after which the break is inserted.
1148
    *
1149
    * @access public
1150
    * @param array $breaks Array containing the horizontal page breaks
1151
    */
1152
    function setHPagebreaks($breaks)
1153
    {
1604 raphael 1154
        foreach ($breaks as $break) {
1155
            array_push($this->_hbreaks, $break);
418 aurelien 1156
        }
1157
    }
1604 raphael 1158
 
418 aurelien 1159
    /**
1160
    * Store the vertical page breaks on a worksheet (for printing).
1161
    * The breaks represent the column after which the break is inserted.
1162
    *
1163
    * @access public
1164
    * @param array $breaks Array containing the vertical page breaks
1165
    */
1166
    function setVPagebreaks($breaks)
1167
    {
1604 raphael 1168
        foreach ($breaks as $break) {
1169
            array_push($this->_vbreaks, $break);
418 aurelien 1170
        }
1171
    }
1604 raphael 1172
 
1173
 
418 aurelien 1174
    /**
1175
    * Set the worksheet zoom factor.
1176
    *
1177
    * @access public
1178
    * @param integer $scale The zoom factor
1179
    */
1180
    function setZoom($scale = 100)
1181
    {
1182
        // Confine the scale to Excel's range
1604 raphael 1183
        if ($scale < 10 || $scale > 400) {
418 aurelien 1184
            $this->raiseError("Zoom factor $scale outside range: 10 <= zoom <= 400");
1185
            $scale = 100;
1186
        }
1604 raphael 1187
 
418 aurelien 1188
        $this->_zoom = floor($scale);
1189
    }
1604 raphael 1190
 
418 aurelien 1191
    /**
1604 raphael 1192
    * Set the scale factor for the printed page.
418 aurelien 1193
    * It turns off the "fit to page" option
1194
    *
1195
    * @access public
1196
    * @param integer $scale The optional scale factor. Defaults to 100
1197
    */
1198
    function setPrintScale($scale = 100)
1199
    {
1200
        // Confine the scale to Excel's range
1604 raphael 1201
        if ($scale < 10 || $scale > 400) {
418 aurelien 1202
            $this->raiseError("Print scale $scale outside range: 10 <= zoom <= 400");
1203
            $scale = 100;
1204
        }
1604 raphael 1205
 
418 aurelien 1206
        // Turn off "fit to page" option
1604 raphael 1207
        $this->_fit_page = 0;
1208
 
418 aurelien 1209
        $this->_print_scale = floor($scale);
1210
    }
1604 raphael 1211
 
418 aurelien 1212
    /**
1213
    * Map to the appropriate write method acording to the token recieved.
1214
    *
1215
    * @access public
1216
    * @param integer $row    The row of the cell we are writing to
1217
    * @param integer $col    The column of the cell we are writing to
1218
    * @param mixed   $token  What we are writing
1219
    * @param mixed   $format The optional format to apply to the cell
1220
    */
1604 raphael 1221
    function write($row, $col, $token, $format = null)
418 aurelien 1222
    {
1223
        // Check for a cell reference in A1 notation and substitute row and column
1224
        /*if ($_[0] =~ /^\D/) {
1225
            @_ = $this->_substituteCellref(@_);
1226
    }*/
1604 raphael 1227
 
1228
        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
1229
            // Match number
1230
            return $this->writeNumber($row, $col, $token, $format);
1231
        } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
1232
            // Match http or ftp URL
1233
            return $this->writeUrl($row, $col, $token, '', $format);
1234
        } elseif (preg_match("/^mailto:/", $token)) {
1235
            // Match mailto:
1236
            return $this->writeUrl($row, $col, $token, '', $format);
1237
        } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
1238
            // Match internal or external sheet link
1239
            return $this->writeUrl($row, $col, $token, '', $format);
1240
        } elseif (preg_match("/^=/", $token)) {
1241
            // Match formula
418 aurelien 1242
            return $this->writeFormula($row, $col, $token, $format);
1604 raphael 1243
        } elseif ($token == '') {
1244
            // Match blank
1245
            return $this->writeBlank($row, $col, $format);
1246
        } else {
1247
            // Default: match string
1248
            return $this->writeString($row, $col, $token, $format);
418 aurelien 1249
        }
1250
    }
1604 raphael 1251
 
418 aurelien 1252
    /**
1253
    * Write an array of values as a row
1254
    *
1255
    * @access public
1604 raphael 1256
    * @param integer $row    The row we are writing to
1257
    * @param integer $col    The first col (leftmost col) we are writing to
1258
    * @param array   $val    The array of values to write
1259
    * @param mixed   $format The optional format to apply to the cell
1260
    * @return mixed PEAR_Error on failure
418 aurelien 1261
    */
1262
 
1604 raphael 1263
    function writeRow($row, $col, $val, $format = null)
1264
    {
1265
        $retval = '';
418 aurelien 1266
        if (is_array($val)) {
1604 raphael 1267
            foreach ($val as $v) {
418 aurelien 1268
                if (is_array($v)) {
1269
                    $this->writeCol($row, $col, $v, $format);
1270
                } else {
1271
                    $this->write($row, $col, $v, $format);
1272
                }
1273
                $col++;
1274
            }
1275
        } else {
1276
            $retval = new PEAR_Error('$val needs to be an array');
1277
        }
1278
        return($retval);
1279
    }
1604 raphael 1280
 
418 aurelien 1281
    /**
1282
    * Write an array of values as a column
1283
    *
1284
    * @access public
1604 raphael 1285
    * @param integer $row    The first row (uppermost row) we are writing to
1286
    * @param integer $col    The col we are writing to
1287
    * @param array   $val    The array of values to write
1288
    * @param mixed   $format The optional format to apply to the cell
1289
    * @return mixed PEAR_Error on failure
418 aurelien 1290
    */
1604 raphael 1291
 
1292
    function writeCol($row, $col, $val, $format = null)
418 aurelien 1293
    {
1604 raphael 1294
        $retval = '';
418 aurelien 1295
        if (is_array($val)) {
1604 raphael 1296
            foreach ($val as $v) {
418 aurelien 1297
                $this->write($row, $col, $v, $format);
1298
                $row++;
1299
            }
1300
        } else {
1301
            $retval = new PEAR_Error('$val needs to be an array');
1302
        }
1303
        return($retval);
1304
    }
1604 raphael 1305
 
418 aurelien 1306
    /**
1307
    * Returns an index to the XF record in the workbook
1308
    *
1309
    * @access private
1310
    * @param mixed &$format The optional XF format
1311
    * @return integer The XF record index
1312
    */
1313
    function _XF(&$format)
1314
    {
1604 raphael 1315
        if ($format) {
418 aurelien 1316
            return($format->getXfIndex());
1604 raphael 1317
        } else {
418 aurelien 1318
            return(0x0F);
1319
        }
1320
    }
1604 raphael 1321
 
1322
 
418 aurelien 1323
    /******************************************************************************
1324
    *******************************************************************************
1325
    *
1326
    * Internal methods
1327
    */
1604 raphael 1328
 
1329
 
418 aurelien 1330
    /**
1331
    * Store Worksheet data in memory using the parent's class append() or to a
1332
    * temporary file, the default.
1333
    *
1334
    * @access private
1335
    * @param string $data The binary data to append
1336
    */
1605 raphael 1337
    function _append(&$data)
418 aurelien 1338
    {
1604 raphael 1339
        if ($this->_using_tmpfile) {
418 aurelien 1340
            // Add CONTINUE records if necessary
1341
            if (strlen($data) > $this->_limit) {
1342
                $data = $this->_addContinue($data);
1343
            }
1604 raphael 1344
            fwrite($this->_filehandle, $data);
418 aurelien 1345
            $this->_datasize += strlen($data);
1604 raphael 1346
        } else {
418 aurelien 1347
            parent::_append($data);
1348
        }
1349
    }
1604 raphael 1350
 
418 aurelien 1351
    /**
1352
    * Substitute an Excel cell reference in A1 notation for  zero based row and
1353
    * column values in an argument list.
1354
    *
1355
    * Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
1356
    *
1357
    * @access private
1358
    * @param string $cell The cell reference. Or range of cells.
1359
    * @return array
1360
    */
1361
    function _substituteCellref($cell)
1362
    {
1363
        $cell = strtoupper($cell);
1604 raphael 1364
 
418 aurelien 1365
        // Convert a column range: 'A:A' or 'B:G'
1604 raphael 1366
        if (preg_match("/([A-I]?[A-Z]):([A-I]?[A-Z])/", $cell, $match)) {
418 aurelien 1367
            list($no_use, $col1) =  $this->_cellToRowcol($match[1] .'1'); // Add a dummy row
1368
            list($no_use, $col2) =  $this->_cellToRowcol($match[2] .'1'); // Add a dummy row
1369
            return(array($col1, $col2));
1370
        }
1604 raphael 1371
 
418 aurelien 1372
        // Convert a cell range: 'A1:B7'
1604 raphael 1373
        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/", $cell, $match)) {
418 aurelien 1374
            list($row1, $col1) =  $this->_cellToRowcol($match[1]);
1375
            list($row2, $col2) =  $this->_cellToRowcol($match[2]);
1376
            return(array($row1, $col1, $row2, $col2));
1377
        }
1604 raphael 1378
 
418 aurelien 1379
        // Convert a cell reference: 'A1' or 'AD2000'
1604 raphael 1380
        if (preg_match("/\$?([A-I]?[A-Z]\$?\d+)/", $cell)) {
418 aurelien 1381
            list($row1, $col1) =  $this->_cellToRowcol($match[1]);
1382
            return(array($row1, $col1));
1383
        }
1604 raphael 1384
 
418 aurelien 1385
        // TODO use real error codes
1386
        $this->raiseError("Unknown cell reference $cell", 0, PEAR_ERROR_DIE);
1387
    }
1604 raphael 1388
 
418 aurelien 1389
    /**
1390
    * Convert an Excel cell reference in A1 notation to a zero based row and column
1391
    * reference; converts C1 to (0, 2).
1392
    *
1393
    * @access private
1394
    * @param string $cell The cell reference.
1395
    * @return array containing (row, column)
1396
    */
1397
    function _cellToRowcol($cell)
1398
    {
1399
        preg_match("/\$?([A-I]?[A-Z])\$?(\d+)/",$cell,$match);
1400
        $col     = $match[1];
1401
        $row     = $match[2];
1604 raphael 1402
 
418 aurelien 1403
        // Convert base26 column string to number
996 aurelien 1404
        $chars = explode('', $col);
418 aurelien 1405
        $expn  = 0;
1406
        $col   = 0;
1604 raphael 1407
 
418 aurelien 1408
        while ($chars) {
1409
            $char = array_pop($chars);        // LS char first
1410
            $col += (ord($char) -ord('A') +1) * pow(26,$expn);
1411
            $expn++;
1412
        }
1604 raphael 1413
 
418 aurelien 1414
        // Convert 1-index to zero-index
1415
        $row--;
1416
        $col--;
1604 raphael 1417
 
418 aurelien 1418
        return(array($row, $col));
1419
    }
1604 raphael 1420
 
418 aurelien 1421
    /**
1422
    * Based on the algorithm provided by Daniel Rentz of OpenOffice.
1423
    *
1424
    * @access private
1425
    * @param string $plaintext The password to be encoded in plaintext.
1426
    * @return string The encoded password
1427
    */
1428
    function _encodePassword($plaintext)
1429
    {
1430
        $password = 0x0000;
1431
        $i        = 1;       // char position
1604 raphael 1432
 
418 aurelien 1433
        // split the plain text password in its component characters
1434
        $chars = preg_split('//', $plaintext, -1, PREG_SPLIT_NO_EMPTY);
1604 raphael 1435
        foreach ($chars as $char) {
1436
            $value        = ord($char) << $i;   // shifted ASCII value
418 aurelien 1437
            $rotated_bits = $value >> 15;       // rotated bits beyond bit 15
1438
            $value       &= 0x7fff;             // first 15 bits
1439
            $password    ^= ($value | $rotated_bits);
1440
            $i++;
1441
        }
1604 raphael 1442
 
418 aurelien 1443
        $password ^= strlen($plaintext);
1444
        $password ^= 0xCE4B;
1445
 
1446
        return($password);
1447
    }
1448
 
1604 raphael 1449
    /**
1450
    * This method sets the properties for outlining and grouping. The defaults
1451
    * correspond to Excel's defaults.
1452
    *
1453
    * @param bool $visible
1454
    * @param bool $symbols_below
1455
    * @param bool $symbols_right
1456
    * @param bool $auto_style
1457
    */
1458
    function setOutline($visible = true, $symbols_below = true, $symbols_right = true, $auto_style = false)
1459
    {
1460
        $this->_outline_on    = $visible;
1461
        $this->_outline_below = $symbols_below;
1462
        $this->_outline_right = $symbols_right;
1463
        $this->_outline_style = $auto_style;
1464
 
1465
        // Ensure this is a boolean vale for Window2
1466
        if ($this->_outline_on) {
1467
            $this->_outline_on = 1;
1468
        }
1469
     }
1470
 
1471
    /**
1472
    * This method sets the worksheet direction to right-to-left (RTL)
1473
    *
1474
    * @param bool $rtl
1475
    */
1476
    function setRTL($rtl = true)
1477
    {
1478
        $this->_Arabic = ($rtl ? 1 : 0);
1479
     }
1480
 
418 aurelien 1481
    /******************************************************************************
1482
    *******************************************************************************
1483
    *
1484
    * BIFF RECORDS
1485
    */
1604 raphael 1486
 
1487
 
418 aurelien 1488
    /**
1489
    * Write a double to the specified row and column (zero indexed).
1490
    * An integer can be written as a double. Excel will display an
1491
    * integer. $format is optional.
1492
    *
1493
    * Returns  0 : normal termination
1494
    *         -2 : row or column out of range
1495
    *
1496
    * @access public
1497
    * @param integer $row    Zero indexed row
1498
    * @param integer $col    Zero indexed column
1499
    * @param float   $num    The number to write
1500
    * @param mixed   $format The optional XF format
1501
    * @return integer
1502
    */
1604 raphael 1503
    function writeNumber($row, $col, $num, $format = null)
418 aurelien 1504
    {
1505
        $record    = 0x0203;                 // Record identifier
1506
        $length    = 0x000E;                 // Number of bytes to follow
1604 raphael 1507
 
418 aurelien 1508
        $xf        = $this->_XF($format);    // The cell format
1604 raphael 1509
 
418 aurelien 1510
        // Check that row and col are valid and store max and min values
1604 raphael 1511
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1512
            return(-2);
1513
        }
1604 raphael 1514
        if ($col >= $this->_xls_colmax) {
418 aurelien 1515
            return(-2);
1516
        }
1604 raphael 1517
        if ($row <  $this->_dim_rowmin)  {
418 aurelien 1518
            $this->_dim_rowmin = $row;
1519
        }
1604 raphael 1520
        if ($row >  $this->_dim_rowmax)  {
418 aurelien 1521
            $this->_dim_rowmax = $row;
1522
        }
1604 raphael 1523
        if ($col <  $this->_dim_colmin)  {
418 aurelien 1524
            $this->_dim_colmin = $col;
1525
        }
1604 raphael 1526
        if ($col >  $this->_dim_colmax)  {
418 aurelien 1527
            $this->_dim_colmax = $col;
1528
        }
1604 raphael 1529
 
418 aurelien 1530
        $header    = pack("vv",  $record, $length);
1531
        $data      = pack("vvv", $row, $col, $xf);
1532
        $xl_double = pack("d",   $num);
1604 raphael 1533
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 1534
            $xl_double = strrev($xl_double);
1535
        }
1604 raphael 1536
 
1605 raphael 1537
		$data = $header . $data . $xl_double;
1538
        $this->_append($data);
418 aurelien 1539
        return(0);
1540
    }
1604 raphael 1541
 
418 aurelien 1542
    /**
1543
    * Write a string to the specified row and column (zero indexed).
1544
    * NOTE: there is an Excel 5 defined limit of 255 characters.
1545
    * $format is optional.
1546
    * Returns  0 : normal termination
1547
    *         -2 : row or column out of range
1548
    *         -3 : long string truncated to 255 chars
1549
    *
1550
    * @access public
1551
    * @param integer $row    Zero indexed row
1552
    * @param integer $col    Zero indexed column
1553
    * @param string  $str    The string to write
1554
    * @param mixed   $format The XF format for the cell
1555
    * @return integer
1556
    */
1604 raphael 1557
    function writeString($row, $col, $str, $format = null)
418 aurelien 1558
    {
1604 raphael 1559
        if ($this->_BIFF_version == 0x0600) {
1560
            return $this->writeStringBIFF8($row, $col, $str, $format);
1561
        }
418 aurelien 1562
        $strlen    = strlen($str);
1563
        $record    = 0x0204;                   // Record identifier
1564
        $length    = 0x0008 + $strlen;         // Bytes to follow
1565
        $xf        = $this->_XF($format);      // The cell format
1604 raphael 1566
 
418 aurelien 1567
        $str_error = 0;
1604 raphael 1568
 
418 aurelien 1569
        // Check that row and col are valid and store max and min values
1604 raphael 1570
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1571
            return(-2);
1572
        }
1604 raphael 1573
        if ($col >= $this->_xls_colmax) {
418 aurelien 1574
            return(-2);
1575
        }
1604 raphael 1576
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1577
            $this->_dim_rowmin = $row;
1578
        }
1604 raphael 1579
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1580
            $this->_dim_rowmax = $row;
1581
        }
1604 raphael 1582
        if ($col <  $this->_dim_colmin) {
418 aurelien 1583
            $this->_dim_colmin = $col;
1584
        }
1604 raphael 1585
        if ($col >  $this->_dim_colmax) {
418 aurelien 1586
            $this->_dim_colmax = $col;
1587
        }
1604 raphael 1588
 
1589
        if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
418 aurelien 1590
            $str       = substr($str, 0, $this->_xls_strmax);
1591
            $length    = 0x0008 + $this->_xls_strmax;
1592
            $strlen    = $this->_xls_strmax;
1593
            $str_error = -3;
1594
        }
1604 raphael 1595
 
418 aurelien 1596
        $header    = pack("vv",   $record, $length);
1597
        $data      = pack("vvvv", $row, $col, $xf, $strlen);
1605 raphael 1598
 
1599
		$data = $header . $data . $str;
1600
        $this->_append($data);
418 aurelien 1601
        return($str_error);
1602
    }
1603
 
1604
    /**
1604 raphael 1605
    * Sets Input Encoding for writing strings
1606
    *
1607
    * @access public
1608
    * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
1609
    */
1610
    function setInputEncoding($encoding)
1611
    {
1612
         if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
1613
             $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
1614
         }
1615
         $this->_input_encoding = $encoding;
1616
    }
1617
 
1618
    /**
1619
    * Write a string to the specified row and column (zero indexed).
1620
    * This is the BIFF8 version (no 255 chars limit).
1621
    * $format is optional.
1622
    * Returns  0 : normal termination
1623
    *         -2 : row or column out of range
1624
    *         -3 : long string truncated to 255 chars
1625
    *
1626
    * @access public
1627
    * @param integer $row    Zero indexed row
1628
    * @param integer $col    Zero indexed column
1629
    * @param string  $str    The string to write
1630
    * @param mixed   $format The XF format for the cell
1631
    * @return integer
1632
    */
1633
    function writeStringBIFF8($row, $col, $str, $format = null)
1634
    {
1635
        if ($this->_input_encoding == 'UTF-16LE')
1636
        {
1637
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1638
            $encoding  = 0x1;
1639
        }
1640
        elseif ($this->_input_encoding != '')
1641
        {
1642
            $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
1643
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1644
            $encoding  = 0x1;
1645
        }
1646
        else
1647
        {
1648
            $strlen    = strlen($str);
1649
            $encoding  = 0x0;
1650
        }
1651
        $record    = 0x00FD;                   // Record identifier
1652
        $length    = 0x000A;                   // Bytes to follow
1653
        $xf        = $this->_XF($format);      // The cell format
1654
 
1655
        $str_error = 0;
1656
 
1657
        // Check that row and col are valid and store max and min values
1658
        if ($this->_checkRowCol($row, $col) == false) {
1659
            return -2;
1660
        }
1661
 
1662
        $str = pack('vC', $strlen, $encoding).$str;
1663
 
1664
        /* check if string is already present */
1665
        if (!isset($this->_str_table[$str])) {
1666
            $this->_str_table[$str] = $this->_str_unique++;
1667
        }
1668
        $this->_str_total++;
1669
 
1670
        $header    = pack('vv',   $record, $length);
1671
        $data      = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
1605 raphael 1672
		$data = $header . $data;
1673
 
1674
		$this->_append($data);
1604 raphael 1675
        return $str_error;
1676
    }
1677
 
1678
    /**
1679
    * Check row and col before writing to a cell, and update the sheet's
1680
    * dimensions accordingly
1681
    *
1682
    * @access private
1683
    * @param integer $row    Zero indexed row
1684
    * @param integer $col    Zero indexed column
1685
    * @return boolean true for success, false if row and/or col are grester
1686
    *                 then maximums allowed.
1687
    */
1688
    function _checkRowCol($row, $col)
1689
    {
1690
        if ($row >= $this->_xls_rowmax) {
1691
            return false;
1692
        }
1693
        if ($col >= $this->_xls_colmax) {
1694
            return false;
1695
        }
1696
        if ($row <  $this->_dim_rowmin) {
1697
            $this->_dim_rowmin = $row;
1698
        }
1699
        if ($row >  $this->_dim_rowmax) {
1700
            $this->_dim_rowmax = $row;
1701
        }
1702
        if ($col <  $this->_dim_colmin) {
1703
            $this->_dim_colmin = $col;
1704
        }
1705
        if ($col >  $this->_dim_colmax) {
1706
            $this->_dim_colmax = $col;
1707
        }
1708
        return true;
1709
    }
1710
 
1711
    /**
418 aurelien 1712
    * Writes a note associated with the cell given by the row and column.
1713
    * NOTE records don't have a length limit.
1714
    *
1715
    * @access public
1716
    * @param integer $row    Zero indexed row
1717
    * @param integer $col    Zero indexed column
1718
    * @param string  $note   The note to write
1719
    */
1720
    function writeNote($row, $col, $note)
1721
    {
1722
        $note_length    = strlen($note);
1723
        $record         = 0x001C;                // Record identifier
1724
        $max_length     = 2048;                  // Maximun length for a NOTE record
1725
        //$length      = 0x0006 + $note_length;    // Bytes to follow
1726
 
1727
        // Check that row and col are valid and store max and min values
1604 raphael 1728
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1729
            return(-2);
1730
        }
1604 raphael 1731
        if ($col >= $this->_xls_colmax) {
418 aurelien 1732
            return(-2);
1733
        }
1604 raphael 1734
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1735
            $this->_dim_rowmin = $row;
1736
        }
1604 raphael 1737
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1738
            $this->_dim_rowmax = $row;
1739
        }
1604 raphael 1740
        if ($col <  $this->_dim_colmin) {
418 aurelien 1741
            $this->_dim_colmin = $col;
1742
        }
1604 raphael 1743
        if ($col >  $this->_dim_colmax) {
418 aurelien 1744
            $this->_dim_colmax = $col;
1745
        }
1604 raphael 1746
 
418 aurelien 1747
        // Length for this record is no more than 2048 + 6
1748
        $length    = 0x0006 + min($note_length, 2048);
1749
        $header    = pack("vv",   $record, $length);
1605 raphael 1750
        $data      = pack("vvv", $row, $col, $note_length) . substr($note, 0, 2048);
1751
		$data = $header . $data;
418 aurelien 1752
 
1605 raphael 1753
        $this->_append($data);
1754
 
1604 raphael 1755
        for ($i = $max_length; $i < $note_length; $i += $max_length) {
418 aurelien 1756
            $chunk  = substr($note, $i, $max_length);
1757
            $length = 0x0006 + strlen($chunk);
1758
            $header = pack("vv",   $record, $length);
1605 raphael 1759
            $data   = pack("vvv", -1, 0, strlen($chunk)) . $chunk;
1760
			$data = $header . $data;
1761
 
1762
            $this->_append($data);
418 aurelien 1763
        }
1764
        return(0);
1765
    }
1766
 
1767
    /**
1768
    * Write a blank cell to the specified row and column (zero indexed).
1769
    * A blank cell is used to specify formatting without adding a string
1770
    * or a number.
1771
    *
1772
    * A blank cell without a format serves no purpose. Therefore, we don't write
1773
    * a BLANK record unless a format is specified.
1774
    *
1775
    * Returns  0 : normal termination (including no format)
1776
    *         -1 : insufficient number of arguments
1777
    *         -2 : row or column out of range
1778
    *
1779
    * @access public
1780
    * @param integer $row    Zero indexed row
1781
    * @param integer $col    Zero indexed column
1782
    * @param mixed   $format The XF format
1783
    */
1784
    function writeBlank($row, $col, $format)
1785
    {
1786
        // Don't write a blank cell unless it has a format
1604 raphael 1787
        if (!$format) {
418 aurelien 1788
            return(0);
1789
        }
1604 raphael 1790
 
418 aurelien 1791
        $record    = 0x0201;                 // Record identifier
1792
        $length    = 0x0006;                 // Number of bytes to follow
1793
        $xf        = $this->_XF($format);    // The cell format
1604 raphael 1794
 
418 aurelien 1795
        // Check that row and col are valid and store max and min values
1604 raphael 1796
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1797
            return(-2);
1798
        }
1604 raphael 1799
        if ($col >= $this->_xls_colmax) {
418 aurelien 1800
            return(-2);
1801
        }
1604 raphael 1802
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1803
            $this->_dim_rowmin = $row;
1804
        }
1604 raphael 1805
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1806
            $this->_dim_rowmax = $row;
1807
        }
1604 raphael 1808
        if ($col <  $this->_dim_colmin) {
418 aurelien 1809
            $this->_dim_colmin = $col;
1810
        }
1604 raphael 1811
        if ($col >  $this->_dim_colmax) {
418 aurelien 1812
            $this->_dim_colmax = $col;
1813
        }
1604 raphael 1814
 
418 aurelien 1815
        $header    = pack("vv",  $record, $length);
1816
        $data      = pack("vvv", $row, $col, $xf);
1605 raphael 1817
		$data = $header . $data;
1818
 
1819
        $this->_append($data);
418 aurelien 1820
        return 0;
1821
    }
1822
 
1823
    /**
1824
    * Write a formula to the specified row and column (zero indexed).
1825
    * The textual representation of the formula is passed to the parser in
1826
    * Parser.php which returns a packed binary string.
1827
    *
1828
    * Returns  0 : normal termination
1829
    *         -1 : formula errors (bad formula)
1830
    *         -2 : row or column out of range
1831
    *
1832
    * @access public
1833
    * @param integer $row     Zero indexed row
1834
    * @param integer $col     Zero indexed column
1835
    * @param string  $formula The formula text string
1836
    * @param mixed   $format  The optional XF format
1837
    * @return integer
1838
    */
1604 raphael 1839
    function writeFormula($row, $col, $formula, $format = null)
418 aurelien 1840
    {
1841
        $record    = 0x0006;     // Record identifier
1604 raphael 1842
 
418 aurelien 1843
        // Excel normally stores the last calculated value of the formula in $num.
1844
        // Clearly we are not in a position to calculate this a priori. Instead
1845
        // we set $num to zero and set the option flags in $grbit to ensure
1846
        // automatic calculation of the formula when the file is opened.
1847
        //
1848
        $xf        = $this->_XF($format); // The cell format
1849
        $num       = 0x00;                // Current value of formula
1850
        $grbit     = 0x03;                // Option flags
1604 raphael 1851
        $unknown   = 0x0000;              // Must be zero
1852
 
1853
 
418 aurelien 1854
        // Check that row and col are valid and store max and min values
1604 raphael 1855
        if ($this->_checkRowCol($row, $col) == false) {
1856
            return -2;
418 aurelien 1857
        }
1604 raphael 1858
 
418 aurelien 1859
        // Strip the '=' or '@' sign at the beginning of the formula string
1604 raphael 1860
        if (preg_match("/^=/", $formula)) {
1861
            $formula = preg_replace("/(^=)/", "", $formula);
1862
        } elseif (preg_match("/^@/", $formula)) {
1863
            $formula = preg_replace("/(^@)/", "", $formula);
1864
        } else {
418 aurelien 1865
            // Error handling
1866
            $this->writeString($row, $col, 'Unrecognised character for formula');
1867
            return -1;
1868
        }
1604 raphael 1869
 
418 aurelien 1870
        // Parse the formula using the parser in Parser.php
1871
        $error = $this->_parser->parse($formula);
1604 raphael 1872
        if ($this->isError($error)) {
1873
            $this->writeString($row, $col, $error->getMessage());
418 aurelien 1874
            return -1;
1875
        }
1604 raphael 1876
 
418 aurelien 1877
        $formula = $this->_parser->toReversePolish();
1604 raphael 1878
        if ($this->isError($formula)) {
418 aurelien 1879
            $this->writeString($row, $col, $formula->getMessage());
1880
            return -1;
1881
        }
1604 raphael 1882
 
418 aurelien 1883
        $formlen    = strlen($formula);    // Length of the binary string
1884
        $length     = 0x16 + $formlen;     // Length of the record data
1604 raphael 1885
 
418 aurelien 1886
        $header    = pack("vv",      $record, $length);
1887
        $data      = pack("vvvdvVv", $row, $col, $xf, $num,
1604 raphael 1888
                                     $grbit, $unknown, $formlen);
1605 raphael 1889
		$data = $header . $data . $formula;
1604 raphael 1890
 
1605 raphael 1891
        $this->_append($data);
418 aurelien 1892
        return 0;
1893
    }
1604 raphael 1894
 
418 aurelien 1895
    /**
1896
    * Write a hyperlink.
1897
    * This is comprised of two elements: the visible label and
1898
    * the invisible link. The visible label is the same as the link unless an
1899
    * alternative string is specified. The label is written using the
1900
    * writeString() method. Therefore the 255 characters string limit applies.
1901
    * $string and $format are optional.
1902
    *
1903
    * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
1904
    * directory url.
1905
    *
1906
    * Returns  0 : normal termination
1907
    *         -2 : row or column out of range
1908
    *         -3 : long string truncated to 255 chars
1909
    *
1910
    * @access public
1911
    * @param integer $row    Row
1912
    * @param integer $col    Column
1913
    * @param string  $url    URL string
1914
    * @param string  $string Alternative label
1915
    * @param mixed   $format The cell format
1916
    * @return integer
1917
    */
1604 raphael 1918
    function writeUrl($row, $col, $url, $string = '', $format = null)
418 aurelien 1919
    {
1920
        // Add start row and col to arg list
1604 raphael 1921
        return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
418 aurelien 1922
    }
1604 raphael 1923
 
418 aurelien 1924
    /**
1925
    * This is the more general form of writeUrl(). It allows a hyperlink to be
1926
    * written to a range of cells. This function also decides the type of hyperlink
1927
    * to be written. These are either, Web (http, ftp, mailto), Internal
1928
    * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
1929
    *
1930
    * @access private
1931
    * @see writeUrl()
1932
    * @param integer $row1   Start row
1933
    * @param integer $col1   Start column
1934
    * @param integer $row2   End row
1935
    * @param integer $col2   End column
1936
    * @param string  $url    URL string
1937
    * @param string  $string Alternative label
1938
    * @param mixed   $format The cell format
1939
    * @return integer
1940
    */
1604 raphael 1941
 
1942
    function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
418 aurelien 1943
    {
1604 raphael 1944
 
418 aurelien 1945
        // Check for internal/external sheet links or default to web link
1946
        if (preg_match('[^internal:]', $url)) {
1947
            return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
1948
        }
1949
        if (preg_match('[^external:]', $url)) {
1950
            return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
1951
        }
1952
        return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
1953
    }
1604 raphael 1954
 
1955
 
418 aurelien 1956
    /**
1957
    * Used to write http, ftp and mailto hyperlinks.
1958
    * The link type ($options) is 0x03 is the same as absolute dir ref without
1959
    * sheet. However it is differentiated by the $unknown2 data stream.
1960
    *
1961
    * @access private
1962
    * @see writeUrl()
1963
    * @param integer $row1   Start row
1964
    * @param integer $col1   Start column
1965
    * @param integer $row2   End row
1966
    * @param integer $col2   End column
1967
    * @param string  $url    URL string
1968
    * @param string  $str    Alternative label
1969
    * @param mixed   $format The cell format
1970
    * @return integer
1971
    */
1604 raphael 1972
    function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 1973
    {
1974
        $record      = 0x01B8;                       // Record identifier
1975
        $length      = 0x00000;                      // Bytes to follow
1604 raphael 1976
 
1977
        if (!$format) {
418 aurelien 1978
            $format = $this->_url_format;
1979
        }
1604 raphael 1980
 
418 aurelien 1981
        // Write the visible label using the writeString() method.
1982
        if ($str == '') {
1983
            $str = $url;
1984
        }
1604 raphael 1985
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
1986
        if (($str_error == -2) || ($str_error == -3)) {
418 aurelien 1987
            return $str_error;
1988
        }
1604 raphael 1989
 
418 aurelien 1990
        // Pack the undocumented parts of the hyperlink stream
1991
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1992
        $unknown2    = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
1604 raphael 1993
 
418 aurelien 1994
        // Pack the option flags
1995
        $options     = pack("V", 0x03);
1604 raphael 1996
 
418 aurelien 1997
        // Convert URL to a null terminated wchar string
1998
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1999
        $url         = $url . "\0\0\0";
1604 raphael 2000
 
418 aurelien 2001
        // Pack the length of the URL
2002
        $url_len     = pack("V", strlen($url));
1604 raphael 2003
 
418 aurelien 2004
        // Calculate the data length
2005
        $length      = 0x34 + strlen($url);
1604 raphael 2006
 
418 aurelien 2007
        // Pack the header data
2008
        $header      = pack("vv",   $record, $length);
2009
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
1604 raphael 2010
 
1605 raphael 2011
		$data = $header . $data . $unknown1 . $options . $unknown2 . $url_len . $url;
2012
 
418 aurelien 2013
        // Write the packed data
1605 raphael 2014
        $this->_append($data);
418 aurelien 2015
        return($str_error);
2016
    }
1604 raphael 2017
 
418 aurelien 2018
    /**
2019
    * Used to write internal reference hyperlinks such as "Sheet1!A1".
2020
    *
2021
    * @access private
2022
    * @see writeUrl()
2023
    * @param integer $row1   Start row
2024
    * @param integer $col1   Start column
2025
    * @param integer $row2   End row
2026
    * @param integer $col2   End column
2027
    * @param string  $url    URL string
2028
    * @param string  $str    Alternative label
2029
    * @param mixed   $format The cell format
2030
    * @return integer
2031
    */
1604 raphael 2032
    function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 2033
    {
2034
        $record      = 0x01B8;                       // Record identifier
2035
        $length      = 0x00000;                      // Bytes to follow
1604 raphael 2036
 
2037
        if (!$format) {
418 aurelien 2038
            $format = $this->_url_format;
2039
        }
1604 raphael 2040
 
418 aurelien 2041
        // Strip URL type
1604 raphael 2042
        $url = preg_replace('/^internal:/', '', $url);
2043
 
418 aurelien 2044
        // Write the visible label
2045
        if ($str == '') {
2046
            $str = $url;
2047
        }
1604 raphael 2048
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
2049
        if (($str_error == -2) || ($str_error == -3)) {
418 aurelien 2050
            return $str_error;
2051
        }
1604 raphael 2052
 
418 aurelien 2053
        // Pack the undocumented parts of the hyperlink stream
2054
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1604 raphael 2055
 
418 aurelien 2056
        // Pack the option flags
2057
        $options     = pack("V", 0x08);
1604 raphael 2058
 
418 aurelien 2059
        // Convert the URL type and to a null terminated wchar string
2060
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
2061
        $url         = $url . "\0\0\0";
1604 raphael 2062
 
418 aurelien 2063
        // Pack the length of the URL as chars (not wchars)
2064
        $url_len     = pack("V", floor(strlen($url)/2));
1604 raphael 2065
 
418 aurelien 2066
        // Calculate the data length
2067
        $length      = 0x24 + strlen($url);
1604 raphael 2068
 
418 aurelien 2069
        // Pack the header data
2070
        $header      = pack("vv",   $record, $length);
2071
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
1604 raphael 2072
 
1605 raphael 2073
		$data = $header . $data . $unknown1 . $options . $url_len . $url;
2074
 
418 aurelien 2075
        // Write the packed data
1605 raphael 2076
        $this->_append($data);
418 aurelien 2077
        return($str_error);
2078
    }
1604 raphael 2079
 
418 aurelien 2080
    /**
2081
    * Write links to external directory names such as 'c:\foo.xls',
2082
    * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
2083
    *
2084
    * Note: Excel writes some relative links with the $dir_long string. We ignore
2085
    * these cases for the sake of simpler code.
2086
    *
2087
    * @access private
2088
    * @see writeUrl()
2089
    * @param integer $row1   Start row
2090
    * @param integer $col1   Start column
2091
    * @param integer $row2   End row
2092
    * @param integer $col2   End column
2093
    * @param string  $url    URL string
2094
    * @param string  $str    Alternative label
2095
    * @param mixed   $format The cell format
2096
    * @return integer
2097
    */
1604 raphael 2098
    function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 2099
    {
2100
        // Network drives are different. We will handle them separately
2101
        // MS/Novell network drives and shares start with \\
2102
        if (preg_match('[^external:\\\\]', $url)) {
2103
            return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
2104
        }
2105
 
2106
        $record      = 0x01B8;                       // Record identifier
2107
        $length      = 0x00000;                      // Bytes to follow
2108
 
1604 raphael 2109
        if (!$format) {
418 aurelien 2110
            $format = $this->_url_format;
2111
        }
2112
 
2113
        // Strip URL type and change Unix dir separator to Dos style (if needed)
2114
        //
1604 raphael 2115
        $url = preg_replace('/^external:/', '', $url);
2116
        $url = preg_replace('/\//', "\\", $url);
418 aurelien 2117
 
2118
        // Write the visible label
2119
        if ($str == '') {
1604 raphael 2120
            $str = preg_replace('/\#/', ' - ', $url);
418 aurelien 2121
        }
1604 raphael 2122
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
418 aurelien 2123
        if (($str_error == -2) or ($str_error == -3)) {
2124
            return $str_error;
2125
        }
2126
 
2127
        // Determine if the link is relative or absolute:
2128
        //   relative if link contains no dir separator, "somefile.xls"
2129
        //   relative if link starts with up-dir, "..\..\somefile.xls"
2130
        //   otherwise, absolute
2131
 
2132
        $absolute    = 0x02; // Bit mask
1604 raphael 2133
        if (!preg_match("/\\\/", $url)) {
418 aurelien 2134
            $absolute    = 0x00;
2135
        }
1604 raphael 2136
        if (preg_match("/^\.\.\\\/", $url)) {
418 aurelien 2137
            $absolute    = 0x00;
2138
        }
1604 raphael 2139
        $link_type               = 0x01 | $absolute;
418 aurelien 2140
 
2141
        // Determine if the link contains a sheet reference and change some of the
2142
        // parameters accordingly.
2143
        // Split the dir name and sheet name (if it exists)
1604 raphael 2144
        /*if (preg_match("/\#/", $url)) {
2145
            list($dir_long, $sheet) = split("\#", $url);
2146
        } else {
2147
            $dir_long = $url;
2148
        }
418 aurelien 2149
 
2150
        if (isset($sheet)) {
2151
            $link_type |= 0x08;
2152
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
1604 raphael 2153
            $sheet      = join("\0", split('', $sheet));
418 aurelien 2154
            $sheet     .= "\0\0\0";
1604 raphael 2155
        } else {
418 aurelien 2156
            $sheet_len   = '';
2157
            $sheet       = '';
1604 raphael 2158
        }*/
2159
        $dir_long = $url;
2160
        if (preg_match("/\#/", $url)) {
2161
            $link_type |= 0x08;
418 aurelien 2162
        }
1604 raphael 2163
 
2164
 
418 aurelien 2165
 
2166
        // Pack the link type
2167
        $link_type   = pack("V", $link_type);
2168
 
2169
        // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1604 raphael 2170
        $up_count    = preg_match_all("/\.\.\\\/", $dir_long, $useless);
418 aurelien 2171
        $up_count    = pack("v", $up_count);
2172
 
2173
        // Store the short dos dir name (null terminated)
1604 raphael 2174
        $dir_short   = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
418 aurelien 2175
 
2176
        // Store the long dir name as a wchar string (non-null terminated)
1604 raphael 2177
        //$dir_long       = join("\0", split('', $dir_long));
418 aurelien 2178
        $dir_long       = $dir_long . "\0";
2179
 
2180
        // Pack the lengths of the dir strings
2181
        $dir_short_len = pack("V", strlen($dir_short)      );
2182
        $dir_long_len  = pack("V", strlen($dir_long)       );
1604 raphael 2183
        $stream_len    = pack("V", 0);//strlen($dir_long) + 0x06);
418 aurelien 2184
 
2185
        // Pack the undocumented parts of the hyperlink stream
2186
        $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'       );
2187
        $unknown2 = pack("H*",'0303000000000000C000000000000046'               );
2188
        $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
2189
        $unknown4 = pack("v",  0x03                                            );
2190
 
2191
        // Pack the main data stream
2192
        $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
2193
                          $unknown1     .
2194
                          $link_type    .
2195
                          $unknown2     .
2196
                          $up_count     .
2197
                          $dir_short_len.
2198
                          $dir_short    .
2199
                          $unknown3     .
1604 raphael 2200
                          $stream_len   ;/*.
418 aurelien 2201
                          $dir_long_len .
2202
                          $unknown4     .
2203
                          $dir_long     .
2204
                          $sheet_len    .
1604 raphael 2205
                          $sheet        ;*/
418 aurelien 2206
 
2207
        // Pack the header data
2208
        $length   = strlen($data);
2209
        $header   = pack("vv", $record, $length);
1605 raphael 2210
		$data = $header. $data;
2211
 
418 aurelien 2212
        // Write the packed data
1605 raphael 2213
        $this->_append($data);
418 aurelien 2214
        return($str_error);
2215
    }
1604 raphael 2216
 
2217
 
418 aurelien 2218
    /**
2219
    * This method is used to set the height and format for a row.
2220
    *
2221
    * @access public
2222
    * @param integer $row    The row to set
1604 raphael 2223
    * @param integer $height Height we are giving to the row.
2224
    *                        Use null to set XF without setting height
418 aurelien 2225
    * @param mixed   $format XF format we are giving to the row
1604 raphael 2226
    * @param bool    $hidden The optional hidden attribute
2227
    * @param integer $level  The optional outline level for row, in range [0,7]
418 aurelien 2228
    */
1604 raphael 2229
    function setRow($row, $height, $format = null, $hidden = false, $level = 0)
418 aurelien 2230
    {
2231
        $record      = 0x0208;               // Record identifier
2232
        $length      = 0x0010;               // Number of bytes to follow
1604 raphael 2233
 
418 aurelien 2234
        $colMic      = 0x0000;               // First defined column
2235
        $colMac      = 0x0000;               // Last defined column
2236
        $irwMac      = 0x0000;               // Used by Excel to optimise loading
2237
        $reserved    = 0x0000;               // Reserved
1604 raphael 2238
        $grbit       = 0x0000;               // Option flags
418 aurelien 2239
        $ixfe        = $this->_XF($format);  // XF index
1604 raphael 2240
 
2241
        // set _row_sizes so _sizeRow() can use it
2242
        $this->_row_sizes[$row] = $height;
2243
 
2244
        // Use setRow($row, null, $XF) to set XF format without setting height
2245
        if ($height != null) {
418 aurelien 2246
            $miyRw = $height * 20;  // row height
1604 raphael 2247
        } else {
418 aurelien 2248
            $miyRw = 0xff;          // default row height is 256
2249
        }
1604 raphael 2250
 
2251
        $level = max(0, min($level, 7));  // level should be between 0 and 7
2252
        $this->_outline_row_level = max($level, $this->_outline_row_level);
2253
 
2254
 
2255
        // Set the options flags. fUnsynced is used to show that the font and row
2256
        // heights are not compatible. This is usually the case for WriteExcel.
2257
        // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
2258
        // is collapsed. Instead it is used to indicate that the previous row is
2259
        // collapsed. The zero height flag, 0x20, is used to collapse a row.
2260
 
2261
        $grbit |= $level;
2262
        if ($hidden) {
2263
            $grbit |= 0x0020;
2264
        }
2265
        $grbit |= 0x0040; // fUnsynced
2266
        if ($format) {
2267
            $grbit |= 0x0080;
2268
        }
2269
        $grbit |= 0x0100;
2270
 
418 aurelien 2271
        $header   = pack("vv",       $record, $length);
2272
        $data     = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
2273
                                     $irwMac,$reserved, $grbit, $ixfe);
1605 raphael 2274
		$data = $header . $data;
2275
        $this->_append($data);
418 aurelien 2276
    }
1604 raphael 2277
 
418 aurelien 2278
    /**
2279
    * Writes Excel DIMENSIONS to define the area in which there is data.
2280
    *
2281
    * @access private
2282
    */
1604 raphael 2283
    function _storeDimensions()
418 aurelien 2284
    {
1604 raphael 2285
        $record    = 0x0200;                 // Record identifier
2286
        $row_min   = $this->_dim_rowmin;     // First row
2287
        $row_max   = $this->_dim_rowmax + 1; // Last row plus 1
2288
        $col_min   = $this->_dim_colmin;     // First column
2289
        $col_max   = $this->_dim_colmax + 1; // Last column plus 1
2290
        $reserved  = 0x0000;                 // Reserved by Excel
2291
 
2292
        if ($this->_BIFF_version == 0x0500) {
2293
            $length    = 0x000A;               // Number of bytes to follow
2294
            $data      = pack("vvvvv", $row_min, $row_max,
2295
                                       $col_min, $col_max, $reserved);
2296
        } elseif ($this->_BIFF_version == 0x0600) {
2297
            $length    = 0x000E;
2298
            $data      = pack("VVvvv", $row_min, $row_max,
2299
                                       $col_min, $col_max, $reserved);
2300
        }
2301
        $header = pack("vv", $record, $length);
418 aurelien 2302
        $this->_prepend($header.$data);
2303
    }
1604 raphael 2304
 
418 aurelien 2305
    /**
2306
    * Write BIFF record Window2.
2307
    *
2308
    * @access private
2309
    */
2310
    function _storeWindow2()
2311
    {
2312
        $record         = 0x023E;     // Record identifier
1604 raphael 2313
        if ($this->_BIFF_version == 0x0500) {
2314
            $length         = 0x000A;     // Number of bytes to follow
2315
        } elseif ($this->_BIFF_version == 0x0600) {
2316
            $length         = 0x0012;
2317
        }
2318
 
418 aurelien 2319
        $grbit          = 0x00B6;     // Option flags
2320
        $rwTop          = 0x0000;     // Top row visible in window
2321
        $colLeft        = 0x0000;     // Leftmost column visible in window
1604 raphael 2322
 
2323
 
418 aurelien 2324
        // The options flags that comprise $grbit
2325
        $fDspFmla       = 0;                     // 0 - bit
1604 raphael 2326
        $fDspGrid       = $this->_screen_gridlines; // 1
418 aurelien 2327
        $fDspRwCol      = 1;                     // 2
2328
        $fFrozen        = $this->_frozen;        // 3
2329
        $fDspZeros      = 1;                     // 4
2330
        $fDefaultHdr    = 1;                     // 5
1604 raphael 2331
        $fArabic        = $this->_Arabic;        // 6
2332
        $fDspGuts       = $this->_outline_on;    // 7
418 aurelien 2333
        $fFrozenNoSplit = 0;                     // 0 - bit
2334
        $fSelected      = $this->selected;       // 1
2335
        $fPaged         = 1;                     // 2
1604 raphael 2336
 
418 aurelien 2337
        $grbit             = $fDspFmla;
2338
        $grbit            |= $fDspGrid       << 1;
2339
        $grbit            |= $fDspRwCol      << 2;
2340
        $grbit            |= $fFrozen        << 3;
2341
        $grbit            |= $fDspZeros      << 4;
2342
        $grbit            |= $fDefaultHdr    << 5;
2343
        $grbit            |= $fArabic        << 6;
2344
        $grbit            |= $fDspGuts       << 7;
2345
        $grbit            |= $fFrozenNoSplit << 8;
2346
        $grbit            |= $fSelected      << 9;
2347
        $grbit            |= $fPaged         << 10;
1604 raphael 2348
 
418 aurelien 2349
        $header  = pack("vv",   $record, $length);
1604 raphael 2350
        $data    = pack("vvv", $grbit, $rwTop, $colLeft);
2351
        // FIXME !!!
2352
        if ($this->_BIFF_version == 0x0500) {
2353
            $rgbHdr         = 0x00000000; // Row/column heading and gridline color
2354
            $data .= pack("V", $rgbHdr);
2355
        } elseif ($this->_BIFF_version == 0x0600) {
2356
            $rgbHdr       = 0x0040; // Row/column heading and gridline color index
2357
            $zoom_factor_page_break = 0x0000;
2358
            $zoom_factor_normal     = 0x0000;
2359
            $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
2360
        }
1605 raphael 2361
		$data = $header.$data;
2362
 
2363
        $this->_append($data);
418 aurelien 2364
    }
1604 raphael 2365
 
418 aurelien 2366
    /**
2367
    * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
2368
    *
2369
    * @access private
2370
    */
2371
    function _storeDefcol()
2372
    {
2373
        $record   = 0x0055;      // Record identifier
2374
        $length   = 0x0002;      // Number of bytes to follow
2375
        $colwidth = 0x0008;      // Default column width
1604 raphael 2376
 
418 aurelien 2377
        $header   = pack("vv", $record, $length);
2378
        $data     = pack("v",  $colwidth);
1604 raphael 2379
        $this->_prepend($header . $data);
418 aurelien 2380
    }
1604 raphael 2381
 
418 aurelien 2382
    /**
2383
    * Write BIFF record COLINFO to define column widths
2384
    *
2385
    * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
2386
    * length record.
2387
    *
2388
    * @access private
2389
    * @param array $col_array This is the only parameter received and is composed of the following:
2390
    *                0 => First formatted column,
2391
    *                1 => Last formatted column,
2392
    *                2 => Col width (8.43 is Excel default),
2393
    *                3 => The optional XF format of the column,
2394
    *                4 => Option flags.
1604 raphael 2395
    *                5 => Optional outline level
418 aurelien 2396
    */
2397
    function _storeColinfo($col_array)
2398
    {
2399
        if (isset($col_array[0])) {
2400
            $colFirst = $col_array[0];
2401
        }
2402
        if (isset($col_array[1])) {
2403
            $colLast = $col_array[1];
2404
        }
2405
        if (isset($col_array[2])) {
2406
            $coldx = $col_array[2];
1604 raphael 2407
        } else {
418 aurelien 2408
            $coldx = 8.43;
2409
        }
2410
        if (isset($col_array[3])) {
2411
            $format = $col_array[3];
1604 raphael 2412
        } else {
418 aurelien 2413
            $format = 0;
2414
        }
2415
        if (isset($col_array[4])) {
2416
            $grbit = $col_array[4];
1604 raphael 2417
        } else {
418 aurelien 2418
            $grbit = 0;
2419
        }
1604 raphael 2420
        if (isset($col_array[5])) {
2421
            $level = $col_array[5];
2422
        } else {
2423
            $level = 0;
2424
        }
418 aurelien 2425
        $record   = 0x007D;          // Record identifier
2426
        $length   = 0x000B;          // Number of bytes to follow
1604 raphael 2427
 
418 aurelien 2428
        $coldx   += 0.72;            // Fudge. Excel subtracts 0.72 !?
2429
        $coldx   *= 256;             // Convert to units of 1/256 of a char
1604 raphael 2430
 
418 aurelien 2431
        $ixfe     = $this->_XF($format);
2432
        $reserved = 0x00;            // Reserved
1604 raphael 2433
 
2434
        $level = max(0, min($level, 7));
2435
        $grbit |= $level << 8;
2436
 
418 aurelien 2437
        $header   = pack("vv",     $record, $length);
2438
        $data     = pack("vvvvvC", $colFirst, $colLast, $coldx,
2439
                                   $ixfe, $grbit, $reserved);
2440
        $this->_prepend($header.$data);
2441
    }
1604 raphael 2442
 
418 aurelien 2443
    /**
2444
    * Write BIFF record SELECTION.
2445
    *
2446
    * @access private
2447
    * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
2448
    * @see setSelection()
2449
    */
2450
    function _storeSelection($array)
2451
    {
2452
        list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
2453
        $record   = 0x001D;                  // Record identifier
2454
        $length   = 0x000F;                  // Number of bytes to follow
1604 raphael 2455
 
418 aurelien 2456
        $pnn      = $this->_active_pane;     // Pane position
2457
        $rwAct    = $rwFirst;                // Active row
2458
        $colAct   = $colFirst;               // Active column
2459
        $irefAct  = 0;                       // Active cell ref
2460
        $cref     = 1;                       // Number of refs
1604 raphael 2461
 
418 aurelien 2462
        if (!isset($rwLast)) {
2463
            $rwLast   = $rwFirst;       // Last  row in reference
2464
        }
2465
        if (!isset($colLast)) {
2466
            $colLast  = $colFirst;      // Last  col in reference
2467
        }
1604 raphael 2468
 
418 aurelien 2469
        // Swap last row/col for first row/col as necessary
1604 raphael 2470
        if ($rwFirst > $rwLast) {
418 aurelien 2471
            list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
2472
        }
1604 raphael 2473
 
2474
        if ($colFirst > $colLast) {
418 aurelien 2475
            list($colFirst, $colLast) = array($colLast, $colFirst);
2476
        }
1604 raphael 2477
 
418 aurelien 2478
        $header   = pack("vv",         $record, $length);
2479
        $data     = pack("CvvvvvvCC",  $pnn, $rwAct, $colAct,
2480
                                       $irefAct, $cref,
2481
                                       $rwFirst, $rwLast,
2482
                                       $colFirst, $colLast);
1605 raphael 2483
		$data = $header . $data;
2484
 
2485
        $this->_append($data);
418 aurelien 2486
    }
1604 raphael 2487
 
418 aurelien 2488
    /**
1604 raphael 2489
    * Store the MERGEDCELLS record for all ranges of merged cells
2490
    *
2491
    * @access private
2492
    */
2493
    function _storeMergedCells()
2494
    {
2495
        // if there are no merged cell ranges set, return
2496
        if (count($this->_merged_ranges) == 0) {
2497
            return;
2498
        }
2499
        $record   = 0x00E5;
2500
        foreach($this->_merged_ranges as $ranges)
2501
          {
2502
            $length   = 2 + count($ranges) * 8;
2503
            $header   = pack('vv', $record, $length);
2504
            $data     = pack('v',  count($ranges));
2505
            foreach($ranges as $range)
2506
              $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
1605 raphael 2507
            $string = $header . $data;
2508
 
2509
            $this->_append($string);
1604 raphael 2510
          }
2511
    }
2512
 
2513
    /**
418 aurelien 2514
    * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
2515
    * references in a worksheet.
2516
    *
2517
    * Excel only stores references to external sheets that are used in formulas.
2518
    * For simplicity we store references to all the sheets in the workbook
2519
    * regardless of whether they are used or not. This reduces the overall
2520
    * complexity and eliminates the need for a two way dialogue between the formula
2521
    * parser the worksheet objects.
2522
    *
2523
    * @access private
2524
    * @param integer $count The number of external sheet references in this worksheet
2525
    */
2526
    function _storeExterncount($count)
2527
    {
1604 raphael 2528
        $record = 0x0016;          // Record identifier
2529
        $length = 0x0002;          // Number of bytes to follow
2530
 
2531
        $header = pack("vv", $record, $length);
2532
        $data   = pack("v",  $count);
2533
        $this->_prepend($header . $data);
418 aurelien 2534
    }
1604 raphael 2535
 
418 aurelien 2536
    /**
2537
    * Writes the Excel BIFF EXTERNSHEET record. These references are used by
2538
    * formulas. A formula references a sheet name via an index. Since we store a
2539
    * reference to all of the external worksheets the EXTERNSHEET index is the same
2540
    * as the worksheet index.
2541
    *
2542
    * @access private
2543
    * @param string $sheetname The name of a external worksheet
2544
    */
2545
    function _storeExternsheet($sheetname)
2546
    {
2547
        $record    = 0x0017;         // Record identifier
1604 raphael 2548
 
418 aurelien 2549
        // References to the current sheet are encoded differently to references to
2550
        // external sheets.
2551
        //
2552
        if ($this->name == $sheetname) {
2553
            $sheetname = '';
2554
            $length    = 0x02;  // The following 2 bytes
2555
            $cch       = 1;     // The following byte
2556
            $rgch      = 0x02;  // Self reference
1604 raphael 2557
        } else {
418 aurelien 2558
            $length    = 0x02 + strlen($sheetname);
2559
            $cch       = strlen($sheetname);
2560
            $rgch      = 0x03;  // Reference to a sheet in the current workbook
2561
        }
1604 raphael 2562
 
2563
        $header = pack("vv",  $record, $length);
2564
        $data   = pack("CC", $cch, $rgch);
2565
        $this->_prepend($header . $data . $sheetname);
418 aurelien 2566
    }
1604 raphael 2567
 
418 aurelien 2568
    /**
2569
    * Writes the Excel BIFF PANE record.
2570
    * The panes can either be frozen or thawed (unfrozen).
2571
    * Frozen panes are specified in terms of an integer number of rows and columns.
2572
    * Thawed panes are specified in terms of Excel's units for rows and columns.
2573
    *
2574
    * @access private
2575
    * @param array $panes This is the only parameter received and is composed of the following:
2576
    *                     0 => Vertical split position,
2577
    *                     1 => Horizontal split position
2578
    *                     2 => Top row visible
2579
    *                     3 => Leftmost column visible
2580
    *                     4 => Active pane
2581
    */
2582
    function _storePanes($panes)
2583
    {
2584
        $y       = $panes[0];
2585
        $x       = $panes[1];
2586
        $rwTop   = $panes[2];
2587
        $colLeft = $panes[3];
2588
        if (count($panes) > 4) { // if Active pane was received
2589
            $pnnAct = $panes[4];
1604 raphael 2590
        } else {
2591
            $pnnAct = null;
418 aurelien 2592
        }
2593
        $record  = 0x0041;       // Record identifier
2594
        $length  = 0x000A;       // Number of bytes to follow
1604 raphael 2595
 
418 aurelien 2596
        // Code specific to frozen or thawed panes.
1604 raphael 2597
        if ($this->_frozen) {
418 aurelien 2598
            // Set default values for $rwTop and $colLeft
2599
            if (!isset($rwTop)) {
2600
                $rwTop   = $y;
2601
            }
2602
            if (!isset($colLeft)) {
2603
                $colLeft = $x;
2604
            }
1604 raphael 2605
        } else {
418 aurelien 2606
            // Set default values for $rwTop and $colLeft
2607
            if (!isset($rwTop)) {
2608
                $rwTop   = 0;
2609
            }
2610
            if (!isset($colLeft)) {
2611
                $colLeft = 0;
2612
            }
1604 raphael 2613
 
418 aurelien 2614
            // Convert Excel's row and column units to the internal units.
2615
            // The default row height is 12.75
2616
            // The default column width is 8.43
2617
            // The following slope and intersection values were interpolated.
2618
            //
2619
            $y = 20*$y      + 255;
2620
            $x = 113.879*$x + 390;
2621
        }
1604 raphael 2622
 
2623
 
418 aurelien 2624
        // Determine which pane should be active. There is also the undocumented
2625
        // option to override this should it be necessary: may be removed later.
2626
        //
1604 raphael 2627
        if (!isset($pnnAct)) {
2628
            if ($x != 0 && $y != 0) {
418 aurelien 2629
                $pnnAct = 0; // Bottom right
1604 raphael 2630
            }
2631
            if ($x != 0 && $y == 0) {
418 aurelien 2632
                $pnnAct = 1; // Top right
1604 raphael 2633
            }
2634
            if ($x == 0 && $y != 0) {
418 aurelien 2635
                $pnnAct = 2; // Bottom left
1604 raphael 2636
            }
2637
            if ($x == 0 && $y == 0) {
418 aurelien 2638
                $pnnAct = 3; // Top left
1604 raphael 2639
            }
418 aurelien 2640
        }
1604 raphael 2641
 
418 aurelien 2642
        $this->_active_pane = $pnnAct; // Used in _storeSelection
1604 raphael 2643
 
418 aurelien 2644
        $header     = pack("vv",    $record, $length);
2645
        $data       = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
1605 raphael 2646
		$data = $header . $data;
2647
        $this->_append($data);
418 aurelien 2648
    }
1604 raphael 2649
 
418 aurelien 2650
    /**
2651
    * Store the page setup SETUP BIFF record.
2652
    *
2653
    * @access private
2654
    */
2655
    function _storeSetup()
2656
    {
2657
        $record       = 0x00A1;                  // Record identifier
2658
        $length       = 0x0022;                  // Number of bytes to follow
1604 raphael 2659
 
418 aurelien 2660
        $iPaperSize   = $this->_paper_size;    // Paper size
2661
        $iScale       = $this->_print_scale;   // Print scaling factor
2662
        $iPageStart   = 0x01;                 // Starting page number
2663
        $iFitWidth    = $this->_fit_width;    // Fit to number of pages wide
2664
        $iFitHeight   = $this->_fit_height;   // Fit to number of pages high
2665
        $grbit        = 0x00;                 // Option flags
2666
        $iRes         = 0x0258;               // Print resolution
2667
        $iVRes        = 0x0258;               // Vertical print resolution
2668
        $numHdr       = $this->_margin_head;  // Header Margin
2669
        $numFtr       = $this->_margin_foot;   // Footer Margin
2670
        $iCopies      = 0x01;                 // Number of copies
1604 raphael 2671
 
418 aurelien 2672
        $fLeftToRight = 0x0;                     // Print over then down
2673
        $fLandscape   = $this->_orientation;     // Page orientation
2674
        $fNoPls       = 0x0;                     // Setup not read from printer
2675
        $fNoColor     = 0x0;                     // Print black and white
2676
        $fDraft       = 0x0;                     // Print draft quality
2677
        $fNotes       = 0x0;                     // Print notes
2678
        $fNoOrient    = 0x0;                     // Orientation not set
2679
        $fUsePage     = 0x0;                     // Use custom starting page
1604 raphael 2680
 
418 aurelien 2681
        $grbit           = $fLeftToRight;
2682
        $grbit          |= $fLandscape    << 1;
2683
        $grbit          |= $fNoPls        << 2;
2684
        $grbit          |= $fNoColor      << 3;
2685
        $grbit          |= $fDraft        << 4;
2686
        $grbit          |= $fNotes        << 5;
2687
        $grbit          |= $fNoOrient     << 6;
2688
        $grbit          |= $fUsePage      << 7;
1604 raphael 2689
 
418 aurelien 2690
        $numHdr = pack("d", $numHdr);
2691
        $numFtr = pack("d", $numFtr);
1604 raphael 2692
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2693
            $numHdr = strrev($numHdr);
2694
            $numFtr = strrev($numFtr);
2695
        }
1604 raphael 2696
 
418 aurelien 2697
        $header = pack("vv", $record, $length);
2698
        $data1  = pack("vvvvvvvv", $iPaperSize,
2699
                                   $iScale,
2700
                                   $iPageStart,
2701
                                   $iFitWidth,
2702
                                   $iFitHeight,
2703
                                   $grbit,
2704
                                   $iRes,
2705
                                   $iVRes);
2706
        $data2  = $numHdr.$numFtr;
2707
        $data3  = pack("v", $iCopies);
1604 raphael 2708
        $this->_prepend($header . $data1 . $data2 . $data3);
418 aurelien 2709
    }
1604 raphael 2710
 
418 aurelien 2711
    /**
2712
    * Store the header caption BIFF record.
2713
    *
2714
    * @access private
2715
    */
2716
    function _storeHeader()
2717
    {
2718
        $record  = 0x0014;               // Record identifier
1604 raphael 2719
 
2720
        $str      = $this->_header;       // header string
2721
        $cch      = strlen($str);         // Length of header string
2722
        if ($this->_BIFF_version == 0x0600) {
2723
            $encoding = 0x0;                  // TODO: Unicode support
2724
            $length   = 3 + $cch;             // Bytes to follow
2725
        } else {
2726
            $length  = 1 + $cch;             // Bytes to follow
2727
        }
2728
 
2729
        $header   = pack("vv", $record, $length);
2730
        if ($this->_BIFF_version == 0x0600) {
2731
            $data     = pack("vC",  $cch, $encoding);
2732
        } else {
2733
            $data      = pack("C",  $cch);
2734
        }
2735
 
2736
        $this->_prepend($header.$data.$str);
418 aurelien 2737
    }
1604 raphael 2738
 
418 aurelien 2739
    /**
2740
    * Store the footer caption BIFF record.
2741
    *
2742
    * @access private
2743
    */
2744
    function _storeFooter()
2745
    {
2746
        $record  = 0x0015;               // Record identifier
1604 raphael 2747
 
2748
        $str      = $this->_footer;       // Footer string
2749
        $cch      = strlen($str);         // Length of footer string
2750
        if ($this->_BIFF_version == 0x0600) {
2751
            $encoding = 0x0;                  // TODO: Unicode support
2752
            $length   = 3 + $cch;             // Bytes to follow
2753
        } else {
2754
            $length  = 1 + $cch;
2755
        }
2756
 
418 aurelien 2757
        $header    = pack("vv", $record, $length);
1604 raphael 2758
        if ($this->_BIFF_version == 0x0600) {
2759
            $data      = pack("vC",  $cch, $encoding);
2760
        } else {
2761
            $data      = pack("C",  $cch);
2762
        }
2763
 
2764
        $this->_prepend($header . $data . $str);
418 aurelien 2765
    }
1604 raphael 2766
 
418 aurelien 2767
    /**
2768
    * Store the horizontal centering HCENTER BIFF record.
2769
    *
2770
    * @access private
2771
    */
2772
    function _storeHcenter()
2773
    {
2774
        $record   = 0x0083;              // Record identifier
2775
        $length   = 0x0002;              // Bytes to follow
1604 raphael 2776
 
418 aurelien 2777
        $fHCenter = $this->_hcenter;     // Horizontal centering
1604 raphael 2778
 
418 aurelien 2779
        $header    = pack("vv", $record, $length);
2780
        $data      = pack("v",  $fHCenter);
1604 raphael 2781
 
2782
        $this->_prepend($header.$data);
418 aurelien 2783
    }
1604 raphael 2784
 
418 aurelien 2785
    /**
2786
    * Store the vertical centering VCENTER BIFF record.
2787
    *
2788
    * @access private
2789
    */
2790
    function _storeVcenter()
2791
    {
2792
        $record   = 0x0084;              // Record identifier
2793
        $length   = 0x0002;              // Bytes to follow
1604 raphael 2794
 
418 aurelien 2795
        $fVCenter = $this->_vcenter;     // Horizontal centering
1604 raphael 2796
 
418 aurelien 2797
        $header    = pack("vv", $record, $length);
2798
        $data      = pack("v",  $fVCenter);
1604 raphael 2799
        $this->_prepend($header . $data);
418 aurelien 2800
    }
1604 raphael 2801
 
418 aurelien 2802
    /**
2803
    * Store the LEFTMARGIN BIFF record.
2804
    *
2805
    * @access private
2806
    */
2807
    function _storeMarginLeft()
2808
    {
2809
        $record  = 0x0026;                   // Record identifier
2810
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2811
 
418 aurelien 2812
        $margin  = $this->_margin_left;       // Margin in inches
1604 raphael 2813
 
418 aurelien 2814
        $header    = pack("vv",  $record, $length);
2815
        $data      = pack("d",   $margin);
1604 raphael 2816
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2817
            $data = strrev($data);
2818
        }
1604 raphael 2819
 
2820
        $this->_prepend($header . $data);
418 aurelien 2821
    }
1604 raphael 2822
 
418 aurelien 2823
    /**
2824
    * Store the RIGHTMARGIN BIFF record.
2825
    *
2826
    * @access private
2827
    */
2828
    function _storeMarginRight()
2829
    {
2830
        $record  = 0x0027;                   // Record identifier
2831
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2832
 
418 aurelien 2833
        $margin  = $this->_margin_right;      // Margin in inches
1604 raphael 2834
 
418 aurelien 2835
        $header    = pack("vv",  $record, $length);
2836
        $data      = pack("d",   $margin);
1604 raphael 2837
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2838
            $data = strrev($data);
2839
        }
1604 raphael 2840
 
2841
        $this->_prepend($header . $data);
418 aurelien 2842
    }
1604 raphael 2843
 
418 aurelien 2844
    /**
2845
    * Store the TOPMARGIN BIFF record.
2846
    *
2847
    * @access private
2848
    */
2849
    function _storeMarginTop()
2850
    {
2851
        $record  = 0x0028;                   // Record identifier
2852
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2853
 
418 aurelien 2854
        $margin  = $this->_margin_top;        // Margin in inches
1604 raphael 2855
 
418 aurelien 2856
        $header    = pack("vv",  $record, $length);
2857
        $data      = pack("d",   $margin);
1604 raphael 2858
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2859
            $data = strrev($data);
2860
        }
1604 raphael 2861
 
2862
        $this->_prepend($header . $data);
418 aurelien 2863
    }
1604 raphael 2864
 
418 aurelien 2865
    /**
2866
    * Store the BOTTOMMARGIN BIFF record.
2867
    *
2868
    * @access private
2869
    */
2870
    function _storeMarginBottom()
2871
    {
2872
        $record  = 0x0029;                   // Record identifier
2873
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2874
 
418 aurelien 2875
        $margin  = $this->_margin_bottom;     // Margin in inches
1604 raphael 2876
 
418 aurelien 2877
        $header    = pack("vv",  $record, $length);
2878
        $data      = pack("d",   $margin);
1604 raphael 2879
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2880
            $data = strrev($data);
2881
        }
1604 raphael 2882
 
2883
        $this->_prepend($header . $data);
418 aurelien 2884
    }
2885
 
2886
    /**
2887
    * Merges the area given by its arguments.
2888
    * This is an Excel97/2000 method. It is required to perform more complicated
2889
    * merging than the normal setAlign('merge').
2890
    *
2891
    * @access public
2892
    * @param integer $first_row First row of the area to merge
2893
    * @param integer $first_col First column of the area to merge
2894
    * @param integer $last_row  Last row of the area to merge
2895
    * @param integer $last_col  Last column of the area to merge
2896
    */
2897
    function mergeCells($first_row, $first_col, $last_row, $last_col)
2898
    {
2899
        $record  = 0x00E5;                   // Record identifier
2900
        $length  = 0x000A;                   // Bytes to follow
2901
        $cref     = 1;                       // Number of refs
2902
 
2903
        // Swap last row/col for first row/col as necessary
2904
        if ($first_row > $last_row) {
2905
            list($first_row, $last_row) = array($last_row, $first_row);
2906
        }
1604 raphael 2907
 
418 aurelien 2908
        if ($first_col > $last_col) {
2909
            list($first_col, $last_col) = array($last_col, $first_col);
2910
        }
1604 raphael 2911
 
418 aurelien 2912
        $header   = pack("vv",    $record, $length);
2913
        $data     = pack("vvvvv", $cref, $first_row, $last_row,
2914
                                  $first_col, $last_col);
1605 raphael 2915
		$data = $header . $data;
2916
		$this->_append($data);
418 aurelien 2917
    }
2918
 
2919
    /**
2920
    * Write the PRINTHEADERS BIFF record.
2921
    *
2922
    * @access private
2923
    */
2924
    function _storePrintHeaders()
2925
    {
2926
        $record      = 0x002a;                   // Record identifier
2927
        $length      = 0x0002;                   // Bytes to follow
1604 raphael 2928
 
418 aurelien 2929
        $fPrintRwCol = $this->_print_headers;     // Boolean flag
1604 raphael 2930
 
418 aurelien 2931
        $header      = pack("vv", $record, $length);
2932
        $data        = pack("v", $fPrintRwCol);
1604 raphael 2933
        $this->_prepend($header . $data);
418 aurelien 2934
    }
1604 raphael 2935
 
418 aurelien 2936
    /**
2937
    * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
2938
    * GRIDSET record.
2939
    *
2940
    * @access private
2941
    */
2942
    function _storePrintGridlines()
2943
    {
2944
        $record      = 0x002b;                    // Record identifier
2945
        $length      = 0x0002;                    // Bytes to follow
1604 raphael 2946
 
418 aurelien 2947
        $fPrintGrid  = $this->_print_gridlines;    // Boolean flag
1604 raphael 2948
 
418 aurelien 2949
        $header      = pack("vv", $record, $length);
2950
        $data        = pack("v", $fPrintGrid);
1604 raphael 2951
        $this->_prepend($header . $data);
418 aurelien 2952
    }
1604 raphael 2953
 
418 aurelien 2954
    /**
2955
    * Write the GRIDSET BIFF record. Must be used in conjunction with the
2956
    * PRINTGRIDLINES record.
2957
    *
2958
    * @access private
2959
    */
2960
    function _storeGridset()
2961
    {
2962
        $record      = 0x0082;                        // Record identifier
2963
        $length      = 0x0002;                        // Bytes to follow
1604 raphael 2964
 
418 aurelien 2965
        $fGridSet    = !($this->_print_gridlines);     // Boolean flag
1604 raphael 2966
 
418 aurelien 2967
        $header      = pack("vv",  $record, $length);
2968
        $data        = pack("v",   $fGridSet);
1604 raphael 2969
        $this->_prepend($header . $data);
2970
    }
2971
 
2972
    /**
2973
    * Write the GUTS BIFF record. This is used to configure the gutter margins
2974
    * where Excel outline symbols are displayed. The visibility of the gutters is
2975
    * controlled by a flag in WSBOOL.
2976
    *
2977
    * @see _storeWsbool()
2978
    * @access private
2979
    */
2980
    function _storeGuts()
2981
    {
2982
        $record      = 0x0080;   // Record identifier
2983
        $length      = 0x0008;   // Bytes to follow
2984
 
2985
        $dxRwGut     = 0x0000;   // Size of row gutter
2986
        $dxColGut    = 0x0000;   // Size of col gutter
2987
 
2988
        $row_level   = $this->_outline_row_level;
2989
        $col_level   = 0;
2990
 
2991
        // Calculate the maximum column outline level. The equivalent calculation
2992
        // for the row outline level is carried out in setRow().
2993
        $colcount = count($this->_colinfo);
2994
        for ($i = 0; $i < $colcount; $i++) {
2995
           // Skip cols without outline level info.
2996
           if (count($this->_colinfo[$i]) >= 6) {
2997
              $col_level = max($this->_colinfo[$i][5], $col_level);
2998
           }
2999
        }
3000
 
3001
        // Set the limits for the outline levels (0 <= x <= 7).
3002
        $col_level = max(0, min($col_level, 7));
3003
 
3004
        // The displayed level is one greater than the max outline levels
3005
        if ($row_level) {
3006
            $row_level++;
3007
        }
3008
        if ($col_level) {
3009
            $col_level++;
3010
        }
3011
 
3012
        $header = pack("vv",   $record, $length);
3013
        $data   = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
3014
 
418 aurelien 3015
        $this->_prepend($header.$data);
3016
    }
1604 raphael 3017
 
3018
 
418 aurelien 3019
    /**
3020
    * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
3021
    * with the SETUP record.
3022
    *
3023
    * @access private
3024
    */
3025
    function _storeWsbool()
3026
    {
3027
        $record      = 0x0081;   // Record identifier
3028
        $length      = 0x0002;   // Bytes to follow
1604 raphael 3029
        $grbit       = 0x0000;
3030
 
418 aurelien 3031
        // The only option that is of interest is the flag for fit to page. So we
3032
        // set all the options in one go.
3033
        //
1604 raphael 3034
        /*if ($this->_fit_page) {
418 aurelien 3035
            $grbit = 0x05c1;
1604 raphael 3036
        } else {
418 aurelien 3037
            $grbit = 0x04c1;
1604 raphael 3038
        }*/
3039
        // Set the option flags
3040
        $grbit |= 0x0001;                           // Auto page breaks visible
3041
        if ($this->_outline_style) {
3042
            $grbit |= 0x0020; // Auto outline styles
418 aurelien 3043
        }
1604 raphael 3044
        if ($this->_outline_below) {
3045
            $grbit |= 0x0040; // Outline summary below
3046
        }
3047
        if ($this->_outline_right) {
3048
            $grbit |= 0x0080; // Outline summary right
3049
        }
3050
        if ($this->_fit_page) {
3051
            $grbit |= 0x0100; // Page setup fit to page
3052
        }
3053
        if ($this->_outline_on) {
3054
            $grbit |= 0x0400; // Outline symbols displayed
3055
        }
3056
 
3057
        $header = pack("vv", $record, $length);
3058
        $data   = pack("v",  $grbit);
3059
        $this->_prepend($header . $data);
418 aurelien 3060
    }
1604 raphael 3061
 
418 aurelien 3062
    /**
3063
    * Write the HORIZONTALPAGEBREAKS BIFF record.
3064
    *
3065
    * @access private
3066
    */
3067
    function _storeHbreak()
3068
    {
3069
        // Return if the user hasn't specified pagebreaks
3070
        if (empty($this->_hbreaks)) {
3071
            return;
3072
        }
1604 raphael 3073
 
418 aurelien 3074
        // Sort and filter array of page breaks
3075
        $breaks = $this->_hbreaks;
1604 raphael 3076
        sort($breaks, SORT_NUMERIC);
418 aurelien 3077
        if ($breaks[0] == 0) { // don't use first break if it's 0
3078
            array_shift($breaks);
3079
        }
1604 raphael 3080
 
418 aurelien 3081
        $record  = 0x001b;               // Record identifier
3082
        $cbrk    = count($breaks);       // Number of page breaks
1604 raphael 3083
        if ($this->_BIFF_version == 0x0600) {
3084
            $length  = 2 + 6*$cbrk;      // Bytes to follow
3085
        } else {
3086
            $length  = 2 + 2*$cbrk;      // Bytes to follow
3087
        }
3088
 
418 aurelien 3089
        $header  = pack("vv", $record, $length);
3090
        $data    = pack("v",  $cbrk);
1604 raphael 3091
 
418 aurelien 3092
        // Append each page break
1604 raphael 3093
        foreach ($breaks as $break) {
3094
            if ($this->_BIFF_version == 0x0600) {
3095
                $data .= pack("vvv", $break, 0x0000, 0x00ff);
3096
            } else {
3097
                $data .= pack("v", $break);
3098
            }
418 aurelien 3099
        }
1604 raphael 3100
 
418 aurelien 3101
        $this->_prepend($header.$data);
3102
    }
1604 raphael 3103
 
3104
 
418 aurelien 3105
    /**
3106
    * Write the VERTICALPAGEBREAKS BIFF record.
3107
    *
3108
    * @access private
3109
    */
3110
    function _storeVbreak()
3111
    {
3112
        // Return if the user hasn't specified pagebreaks
3113
        if (empty($this->_vbreaks)) {
3114
            return;
3115
        }
1604 raphael 3116
 
418 aurelien 3117
        // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
3118
        // It is slightly higher in Excel 97/200, approx. 1026
3119
        $breaks = array_slice($this->_vbreaks,0,1000);
1604 raphael 3120
 
418 aurelien 3121
        // Sort and filter array of page breaks
1604 raphael 3122
        sort($breaks, SORT_NUMERIC);
418 aurelien 3123
        if ($breaks[0] == 0) { // don't use first break if it's 0
3124
            array_shift($breaks);
3125
        }
1604 raphael 3126
 
418 aurelien 3127
        $record  = 0x001a;               // Record identifier
3128
        $cbrk    = count($breaks);       // Number of page breaks
1604 raphael 3129
        if ($this->_BIFF_version == 0x0600) {
3130
            $length  = 2 + 6*$cbrk;      // Bytes to follow
3131
        } else {
3132
            $length  = 2 + 2*$cbrk;      // Bytes to follow
3133
        }
3134
 
418 aurelien 3135
        $header  = pack("vv",  $record, $length);
3136
        $data    = pack("v",   $cbrk);
1604 raphael 3137
 
418 aurelien 3138
        // Append each page break
3139
        foreach ($breaks as $break) {
1604 raphael 3140
            if ($this->_BIFF_version == 0x0600) {
3141
                $data .= pack("vvv", $break, 0x0000, 0xffff);
3142
            } else {
3143
                $data .= pack("v", $break);
3144
            }
418 aurelien 3145
        }
1604 raphael 3146
 
3147
        $this->_prepend($header . $data);
418 aurelien 3148
    }
1604 raphael 3149
 
418 aurelien 3150
    /**
3151
    * Set the Biff PROTECT record to indicate that the worksheet is protected.
3152
    *
3153
    * @access private
3154
    */
3155
    function _storeProtect()
3156
    {
3157
        // Exit unless sheet protection has been specified
3158
        if ($this->_protect == 0) {
3159
            return;
3160
        }
1604 raphael 3161
 
418 aurelien 3162
        $record      = 0x0012;             // Record identifier
3163
        $length      = 0x0002;             // Bytes to follow
1604 raphael 3164
 
418 aurelien 3165
        $fLock       = $this->_protect;    // Worksheet is protected
1604 raphael 3166
 
418 aurelien 3167
        $header      = pack("vv", $record, $length);
3168
        $data        = pack("v",  $fLock);
1604 raphael 3169
 
418 aurelien 3170
        $this->_prepend($header.$data);
3171
    }
1604 raphael 3172
 
418 aurelien 3173
    /**
3174
    * Write the worksheet PASSWORD record.
3175
    *
3176
    * @access private
3177
    */
3178
    function _storePassword()
3179
    {
3180
        // Exit unless sheet protection and password have been specified
1604 raphael 3181
        if (($this->_protect == 0) || (!isset($this->_password))) {
418 aurelien 3182
            return;
3183
        }
1604 raphael 3184
 
418 aurelien 3185
        $record      = 0x0013;               // Record identifier
3186
        $length      = 0x0002;               // Bytes to follow
1604 raphael 3187
 
418 aurelien 3188
        $wPassword   = $this->_password;     // Encoded password
1604 raphael 3189
 
418 aurelien 3190
        $header      = pack("vv", $record, $length);
3191
        $data        = pack("v",  $wPassword);
1604 raphael 3192
 
3193
        $this->_prepend($header . $data);
418 aurelien 3194
    }
3195
 
1604 raphael 3196
 
418 aurelien 3197
    /**
3198
    * Insert a 24bit bitmap image in a worksheet.
3199
    *
3200
    * @access public
3201
    * @param integer $row     The row we are going to insert the bitmap into
3202
    * @param integer $col     The column we are going to insert the bitmap into
3203
    * @param string  $bitmap  The bitmap filename
3204
    * @param integer $x       The horizontal position (offset) of the image inside the cell.
3205
    * @param integer $y       The vertical position (offset) of the image inside the cell.
3206
    * @param integer $scale_x The horizontal scale
3207
    * @param integer $scale_y The vertical scale
3208
    */
3209
    function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
3210
    {
3211
        $bitmap_array = $this->_processBitmap($bitmap);
1604 raphael 3212
        if ($this->isError($bitmap_array)) {
418 aurelien 3213
            $this->writeString($row, $col, $bitmap_array->getMessage());
3214
            return;
3215
        }
3216
        list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
1604 raphael 3217
 
418 aurelien 3218
        // Scale the frame of the image.
3219
        $width  *= $scale_x;
3220
        $height *= $scale_y;
1604 raphael 3221
 
418 aurelien 3222
        // Calculate the vertices of the image and write the OBJ record
3223
        $this->_positionImage($col, $row, $x, $y, $width, $height);
1604 raphael 3224
 
418 aurelien 3225
        // Write the IMDATA record to store the bitmap data
3226
        $record      = 0x007f;
3227
        $length      = 8 + $size;
3228
        $cf          = 0x09;
3229
        $env         = 0x01;
3230
        $lcb         = $size;
1604 raphael 3231
 
418 aurelien 3232
        $header      = pack("vvvvV", $record, $length, $cf, $env, $lcb);
1605 raphael 3233
		$data = $header . $data;
3234
		$this->_append($data);
418 aurelien 3235
    }
1604 raphael 3236
 
418 aurelien 3237
    /**
3238
    * Calculate the vertices that define the position of the image as required by
3239
    * the OBJ record.
3240
    *
3241
    *         +------------+------------+
3242
    *         |     A      |      B     |
3243
    *   +-----+------------+------------+
3244
    *   |     |(x1,y1)     |            |
3245
    *   |  1  |(A1)._______|______      |
3246
    *   |     |    |              |     |
3247
    *   |     |    |              |     |
3248
    *   +-----+----|    BITMAP    |-----+
3249
    *   |     |    |              |     |
3250
    *   |  2  |    |______________.     |
3251
    *   |     |            |        (B2)|
3252
    *   |     |            |     (x2,y2)|
3253
    *   +---- +------------+------------+
3254
    *
3255
    * Example of a bitmap that covers some of the area from cell A1 to cell B2.
3256
    *
3257
    * Based on the width and height of the bitmap we need to calculate 8 vars:
3258
    *     $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
3259
    * The width and height of the cells are also variable and have to be taken into
3260
    * account.
3261
    * The values of $col_start and $row_start are passed in from the calling
3262
    * function. The values of $col_end and $row_end are calculated by subtracting
3263
    * the width and height of the bitmap from the width and height of the
3264
    * underlying cells.
3265
    * The vertices are expressed as a percentage of the underlying cell width as
3266
    * follows (rhs values are in pixels):
3267
    *
3268
    *       x1 = X / W *1024
3269
    *       y1 = Y / H *256
3270
    *       x2 = (X-1) / W *1024
3271
    *       y2 = (Y-1) / H *256
3272
    *
3273
    *       Where:  X is distance from the left side of the underlying cell
3274
    *               Y is distance from the top of the underlying cell
3275
    *               W is the width of the cell
3276
    *               H is the height of the cell
3277
    *
3278
    * @access private
3279
    * @note  the SDK incorrectly states that the height should be expressed as a
3280
    *        percentage of 1024.
3281
    * @param integer $col_start Col containing upper left corner of object
3282
    * @param integer $row_start Row containing top left corner of object
3283
    * @param integer $x1        Distance to left side of object
3284
    * @param integer $y1        Distance to top of object
3285
    * @param integer $width     Width of image frame
3286
    * @param integer $height    Height of image frame
3287
    */
3288
    function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
3289
    {
3290
        // Initialise end cell to the same as the start cell
3291
        $col_end    = $col_start;  // Col containing lower right corner of object
3292
        $row_end    = $row_start;  // Row containing bottom right corner of object
1604 raphael 3293
 
418 aurelien 3294
        // Zero the specified offset if greater than the cell dimensions
1604 raphael 3295
        if ($x1 >= $this->_sizeCol($col_start)) {
418 aurelien 3296
            $x1 = 0;
3297
        }
1604 raphael 3298
        if ($y1 >= $this->_sizeRow($row_start)) {
418 aurelien 3299
            $y1 = 0;
3300
        }
1604 raphael 3301
 
418 aurelien 3302
        $width      = $width  + $x1 -1;
3303
        $height     = $height + $y1 -1;
1604 raphael 3304
 
418 aurelien 3305
        // Subtract the underlying cell widths to find the end cell of the image
3306
        while ($width >= $this->_sizeCol($col_end)) {
3307
            $width -= $this->_sizeCol($col_end);
3308
            $col_end++;
3309
        }
1604 raphael 3310
 
418 aurelien 3311
        // Subtract the underlying cell heights to find the end cell of the image
3312
        while ($height >= $this->_sizeRow($row_end)) {
3313
            $height -= $this->_sizeRow($row_end);
3314
            $row_end++;
3315
        }
1604 raphael 3316
 
418 aurelien 3317
        // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
3318
        // with zero eight or width.
3319
        //
1604 raphael 3320
        if ($this->_sizeCol($col_start) == 0) {
418 aurelien 3321
            return;
1604 raphael 3322
        }
3323
        if ($this->_sizeCol($col_end)   == 0) {
418 aurelien 3324
            return;
1604 raphael 3325
        }
3326
        if ($this->_sizeRow($row_start) == 0) {
418 aurelien 3327
            return;
1604 raphael 3328
        }
3329
        if ($this->_sizeRow($row_end)   == 0) {
418 aurelien 3330
            return;
1604 raphael 3331
        }
3332
 
418 aurelien 3333
        // Convert the pixel values to the percentage value expected by Excel
3334
        $x1 = $x1     / $this->_sizeCol($col_start)   * 1024;
3335
        $y1 = $y1     / $this->_sizeRow($row_start)   *  256;
3336
        $x2 = $width  / $this->_sizeCol($col_end)     * 1024; // Distance to right side of object
3337
        $y2 = $height / $this->_sizeRow($row_end)     *  256; // Distance to bottom of object
1604 raphael 3338
 
3339
        $this->_storeObjPicture($col_start, $x1,
3340
                                 $row_start, $y1,
3341
                                 $col_end, $x2,
3342
                                 $row_end, $y2);
418 aurelien 3343
    }
1604 raphael 3344
 
418 aurelien 3345
    /**
3346
    * Convert the width of a cell from user's units to pixels. By interpolation
3347
    * the relationship is: y = 7x +5. If the width hasn't been set by the user we
3348
    * use the default value. If the col is hidden we use a value of zero.
3349
    *
3350
    * @access private
1604 raphael 3351
    * @param integer $col The column
418 aurelien 3352
    * @return integer The width in pixels
3353
    */
3354
    function _sizeCol($col)
3355
    {
3356
        // Look up the cell value to see if it has been changed
3357
        if (isset($this->col_sizes[$col])) {
3358
            if ($this->col_sizes[$col] == 0) {
3359
                return(0);
1604 raphael 3360
            } else {
418 aurelien 3361
                return(floor(7 * $this->col_sizes[$col] + 5));
3362
            }
1604 raphael 3363
        } else {
418 aurelien 3364
            return(64);
3365
        }
3366
    }
1604 raphael 3367
 
418 aurelien 3368
    /**
3369
    * Convert the height of a cell from user's units to pixels. By interpolation
3370
    * the relationship is: y = 4/3x. If the height hasn't been set by the user we
3371
    * use the default value. If the row is hidden we use a value of zero. (Not
3372
    * possible to hide row yet).
3373
    *
3374
    * @access private
3375
    * @param integer $row The row
3376
    * @return integer The width in pixels
3377
    */
3378
    function _sizeRow($row)
3379
    {
3380
        // Look up the cell value to see if it has been changed
1604 raphael 3381
        if (isset($this->_row_sizes[$row])) {
3382
            if ($this->_row_sizes[$row] == 0) {
418 aurelien 3383
                return(0);
1604 raphael 3384
            } else {
3385
                return(floor(4/3 * $this->_row_sizes[$row]));
418 aurelien 3386
            }
1604 raphael 3387
        } else {
418 aurelien 3388
            return(17);
3389
        }
3390
    }
1604 raphael 3391
 
418 aurelien 3392
    /**
3393
    * Store the OBJ record that precedes an IMDATA record. This could be generalise
3394
    * to support other Excel objects.
3395
    *
3396
    * @access private
3397
    * @param integer $colL Column containing upper left corner of object
3398
    * @param integer $dxL  Distance from left side of cell
3399
    * @param integer $rwT  Row containing top left corner of object
3400
    * @param integer $dyT  Distance from top of cell
3401
    * @param integer $colR Column containing lower right corner of object
3402
    * @param integer $dxR  Distance from right of cell
3403
    * @param integer $rwB  Row containing bottom right corner of object
3404
    * @param integer $dyB  Distance from bottom of cell
3405
    */
3406
    function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
3407
    {
3408
        $record      = 0x005d;   // Record identifier
3409
        $length      = 0x003c;   // Bytes to follow
1604 raphael 3410
 
418 aurelien 3411
        $cObj        = 0x0001;   // Count of objects in file (set to 1)
3412
        $OT          = 0x0008;   // Object type. 8 = Picture
3413
        $id          = 0x0001;   // Object ID
3414
        $grbit       = 0x0614;   // Option flags
1604 raphael 3415
 
418 aurelien 3416
        $cbMacro     = 0x0000;   // Length of FMLA structure
3417
        $Reserved1   = 0x0000;   // Reserved
3418
        $Reserved2   = 0x0000;   // Reserved
1604 raphael 3419
 
418 aurelien 3420
        $icvBack     = 0x09;     // Background colour
3421
        $icvFore     = 0x09;     // Foreground colour
3422
        $fls         = 0x00;     // Fill pattern
3423
        $fAuto       = 0x00;     // Automatic fill
3424
        $icv         = 0x08;     // Line colour
3425
        $lns         = 0xff;     // Line style
3426
        $lnw         = 0x01;     // Line weight
3427
        $fAutoB      = 0x00;     // Automatic border
3428
        $frs         = 0x0000;   // Frame style
3429
        $cf          = 0x0009;   // Image format, 9 = bitmap
3430
        $Reserved3   = 0x0000;   // Reserved
3431
        $cbPictFmla  = 0x0000;   // Length of FMLA structure
3432
        $Reserved4   = 0x0000;   // Reserved
3433
        $grbit2      = 0x0001;   // Option flags
3434
        $Reserved5   = 0x0000;   // Reserved
1604 raphael 3435
 
3436
 
418 aurelien 3437
        $header      = pack("vv", $record, $length);
3438
        $data        = pack("V", $cObj);
3439
        $data       .= pack("v", $OT);
3440
        $data       .= pack("v", $id);
3441
        $data       .= pack("v", $grbit);
3442
        $data       .= pack("v", $colL);
3443
        $data       .= pack("v", $dxL);
3444
        $data       .= pack("v", $rwT);
3445
        $data       .= pack("v", $dyT);
3446
        $data       .= pack("v", $colR);
3447
        $data       .= pack("v", $dxR);
3448
        $data       .= pack("v", $rwB);
3449
        $data       .= pack("v", $dyB);
3450
        $data       .= pack("v", $cbMacro);
3451
        $data       .= pack("V", $Reserved1);
3452
        $data       .= pack("v", $Reserved2);
3453
        $data       .= pack("C", $icvBack);
3454
        $data       .= pack("C", $icvFore);
3455
        $data       .= pack("C", $fls);
3456
        $data       .= pack("C", $fAuto);
3457
        $data       .= pack("C", $icv);
3458
        $data       .= pack("C", $lns);
3459
        $data       .= pack("C", $lnw);
3460
        $data       .= pack("C", $fAutoB);
3461
        $data       .= pack("v", $frs);
3462
        $data       .= pack("V", $cf);
3463
        $data       .= pack("v", $Reserved3);
3464
        $data       .= pack("v", $cbPictFmla);
3465
        $data       .= pack("v", $Reserved4);
3466
        $data       .= pack("v", $grbit2);
3467
        $data       .= pack("V", $Reserved5);
1604 raphael 3468
 
1605 raphael 3469
		$data = $header . $data;
3470
		$this->_append($data);
418 aurelien 3471
    }
1604 raphael 3472
 
418 aurelien 3473
    /**
3474
    * Convert a 24 bit bitmap into the modified internal format used by Windows.
3475
    * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
3476
    * MSDN library.
3477
    *
3478
    * @access private
3479
    * @param string $bitmap The bitmap to process
3480
    * @return array Array with data and properties of the bitmap
3481
    */
3482
    function _processBitmap($bitmap)
3483
    {
3484
        // Open file.
3485
        $bmp_fd = @fopen($bitmap,"rb");
3486
        if (!$bmp_fd) {
1604 raphael 3487
            $this->raiseError("Couldn't import $bitmap");
418 aurelien 3488
        }
1604 raphael 3489
 
418 aurelien 3490
        // Slurp the file into a string.
3491
        $data = fread($bmp_fd, filesize($bitmap));
1604 raphael 3492
 
418 aurelien 3493
        // Check that the file is big enough to be a bitmap.
3494
        if (strlen($data) <= 0x36) {
1604 raphael 3495
            $this->raiseError("$bitmap doesn't contain enough data.\n");
418 aurelien 3496
        }
1604 raphael 3497
 
418 aurelien 3498
        // The first 2 bytes are used to identify the bitmap.
1604 raphael 3499
        $identity = unpack("A2ident", $data);
3500
        if ($identity['ident'] != "BM") {
3501
            $this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n");
418 aurelien 3502
        }
1604 raphael 3503
 
418 aurelien 3504
        // Remove bitmap data: ID.
3505
        $data = substr($data, 2);
1604 raphael 3506
 
418 aurelien 3507
        // Read and remove the bitmap size. This is more reliable than reading
3508
        // the data size at offset 0x22.
3509
        //
1604 raphael 3510
        $size_array   = unpack("Vsa", substr($data, 0, 4));
3511
        $size   = $size_array['sa'];
418 aurelien 3512
        $data   = substr($data, 4);
3513
        $size  -= 0x36; // Subtract size of bitmap header.
3514
        $size  += 0x0C; // Add size of BIFF header.
1604 raphael 3515
 
418 aurelien 3516
        // Remove bitmap data: reserved, offset, header length.
3517
        $data = substr($data, 12);
1604 raphael 3518
 
418 aurelien 3519
        // Read and remove the bitmap width and height. Verify the sizes.
3520
        $width_and_height = unpack("V2", substr($data, 0, 8));
3521
        $width  = $width_and_height[1];
3522
        $height = $width_and_height[2];
3523
        $data   = substr($data, 8);
1604 raphael 3524
        if ($width > 0xFFFF) {
3525
            $this->raiseError("$bitmap: largest image width supported is 65k.\n");
418 aurelien 3526
        }
1604 raphael 3527
        if ($height > 0xFFFF) {
3528
            $this->raiseError("$bitmap: largest image height supported is 65k.\n");
418 aurelien 3529
        }
1604 raphael 3530
 
418 aurelien 3531
        // Read and remove the bitmap planes and bpp data. Verify them.
3532
        $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
3533
        $data = substr($data, 4);
3534
        if ($planes_and_bitcount[2] != 24) { // Bitcount
1604 raphael 3535
            $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n");
418 aurelien 3536
        }
3537
        if ($planes_and_bitcount[1] != 1) {
1604 raphael 3538
            $this->raiseError("$bitmap: only 1 plane supported in bitmap image.\n");
418 aurelien 3539
        }
1604 raphael 3540
 
418 aurelien 3541
        // Read and remove the bitmap compression. Verify compression.
1604 raphael 3542
        $compression = unpack("Vcomp", substr($data, 0, 4));
418 aurelien 3543
        $data = substr($data, 4);
1604 raphael 3544
 
418 aurelien 3545
        //$compression = 0;
1604 raphael 3546
        if ($compression['comp'] != 0) {
3547
            $this->raiseError("$bitmap: compression not supported in bitmap image.\n");
418 aurelien 3548
        }
1604 raphael 3549
 
418 aurelien 3550
        // Remove bitmap data: data size, hres, vres, colours, imp. colours.
3551
        $data = substr($data, 20);
1604 raphael 3552
 
418 aurelien 3553
        // Add the BITMAPCOREHEADER data
3554
        $header  = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
3555
        $data    = $header . $data;
1604 raphael 3556
 
418 aurelien 3557
        return (array($width, $height, $size, $data));
3558
    }
1604 raphael 3559
 
418 aurelien 3560
    /**
3561
    * Store the window zoom factor. This should be a reduced fraction but for
3562
    * simplicity we will store all fractions with a numerator of 100.
3563
    *
3564
    * @access private
3565
    */
3566
    function _storeZoom()
3567
    {
3568
        // If scale is 100 we don't need to write a record
3569
        if ($this->_zoom == 100) {
3570
            return;
3571
        }
1604 raphael 3572
 
418 aurelien 3573
        $record      = 0x00A0;               // Record identifier
3574
        $length      = 0x0004;               // Bytes to follow
1604 raphael 3575
 
418 aurelien 3576
        $header      = pack("vv", $record, $length);
3577
        $data        = pack("vv", $this->_zoom, 100);
1605 raphael 3578
		$data = $header . $data;
3579
		$this->_append($data);
1604 raphael 3580
    }
3581
 
3582
    /**
3583
    * FIXME: add comments
3584
    */
3585
    function setValidation($row1, $col1, $row2, $col2, &$validator)
3586
    {
3587
        $this->_dv[] = $validator->_getData() .
3588
                       pack("vvvvv", 1, $row1, $row2, $col1, $col2);
3589
    }
3590
 
3591
    /**
3592
    * Store the DVAL and DV records.
3593
    *
3594
    * @access private
3595
    */
3596
    function _storeDataValidity()
3597
    {
3598
        $record      = 0x01b2;      // Record identifier
3599
        $length      = 0x0012;      // Bytes to follow
3600
 
3601
        $grbit       = 0x0002;      // Prompt box at cell, no cached validity data at DV records
3602
        $horPos      = 0x00000000;  // Horizontal position of prompt box, if fixed position
3603
        $verPos      = 0x00000000;  // Vertical position of prompt box, if fixed position
3604
        $objId       = 0xffffffff;  // Object identifier of drop down arrow object, or -1 if not visible
3605
 
3606
        $header      = pack('vv', $record, $length);
3607
        $data        = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
3608
                                     count($this->_dv));
1605 raphael 3609
		$data = $header . $data;
3610
		$this->_append($data);
1604 raphael 3611
 
3612
        $record = 0x01be;              // Record identifier
3613
        foreach ($this->_dv as $dv) {
3614
            $length = strlen($dv);      // Bytes to follow
3615
            $header = pack("vv", $record, $length);
1605 raphael 3616
			$header .= $dv;
3617
            $this->_append($header);
1604 raphael 3618
        }
418 aurelien 3619
    }
3620
}
3621
?>