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
    */
1337
    function _append($data)
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
 
418 aurelien 1537
        $this->_append($header.$data.$xl_double);
1538
        return(0);
1539
    }
1604 raphael 1540
 
418 aurelien 1541
    /**
1542
    * Write a string to the specified row and column (zero indexed).
1543
    * NOTE: there is an Excel 5 defined limit of 255 characters.
1544
    * $format is optional.
1545
    * Returns  0 : normal termination
1546
    *         -2 : row or column out of range
1547
    *         -3 : long string truncated to 255 chars
1548
    *
1549
    * @access public
1550
    * @param integer $row    Zero indexed row
1551
    * @param integer $col    Zero indexed column
1552
    * @param string  $str    The string to write
1553
    * @param mixed   $format The XF format for the cell
1554
    * @return integer
1555
    */
1604 raphael 1556
    function writeString($row, $col, $str, $format = null)
418 aurelien 1557
    {
1604 raphael 1558
        if ($this->_BIFF_version == 0x0600) {
1559
            return $this->writeStringBIFF8($row, $col, $str, $format);
1560
        }
418 aurelien 1561
        $strlen    = strlen($str);
1562
        $record    = 0x0204;                   // Record identifier
1563
        $length    = 0x0008 + $strlen;         // Bytes to follow
1564
        $xf        = $this->_XF($format);      // The cell format
1604 raphael 1565
 
418 aurelien 1566
        $str_error = 0;
1604 raphael 1567
 
418 aurelien 1568
        // Check that row and col are valid and store max and min values
1604 raphael 1569
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1570
            return(-2);
1571
        }
1604 raphael 1572
        if ($col >= $this->_xls_colmax) {
418 aurelien 1573
            return(-2);
1574
        }
1604 raphael 1575
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1576
            $this->_dim_rowmin = $row;
1577
        }
1604 raphael 1578
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1579
            $this->_dim_rowmax = $row;
1580
        }
1604 raphael 1581
        if ($col <  $this->_dim_colmin) {
418 aurelien 1582
            $this->_dim_colmin = $col;
1583
        }
1604 raphael 1584
        if ($col >  $this->_dim_colmax) {
418 aurelien 1585
            $this->_dim_colmax = $col;
1586
        }
1604 raphael 1587
 
1588
        if ($strlen > $this->_xls_strmax) { // LABEL must be < 255 chars
418 aurelien 1589
            $str       = substr($str, 0, $this->_xls_strmax);
1590
            $length    = 0x0008 + $this->_xls_strmax;
1591
            $strlen    = $this->_xls_strmax;
1592
            $str_error = -3;
1593
        }
1604 raphael 1594
 
418 aurelien 1595
        $header    = pack("vv",   $record, $length);
1596
        $data      = pack("vvvv", $row, $col, $xf, $strlen);
1604 raphael 1597
        $this->_append($header . $data . $str);
418 aurelien 1598
        return($str_error);
1599
    }
1600
 
1601
    /**
1604 raphael 1602
    * Sets Input Encoding for writing strings
1603
    *
1604
    * @access public
1605
    * @param string $encoding The encoding. Ex: 'UTF-16LE', 'utf-8', 'ISO-859-7'
1606
    */
1607
    function setInputEncoding($encoding)
1608
    {
1609
         if ($encoding != 'UTF-16LE' && !function_exists('iconv')) {
1610
             $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv");
1611
         }
1612
         $this->_input_encoding = $encoding;
1613
    }
1614
 
1615
    /**
1616
    * Write a string to the specified row and column (zero indexed).
1617
    * This is the BIFF8 version (no 255 chars limit).
1618
    * $format is optional.
1619
    * Returns  0 : normal termination
1620
    *         -2 : row or column out of range
1621
    *         -3 : long string truncated to 255 chars
1622
    *
1623
    * @access public
1624
    * @param integer $row    Zero indexed row
1625
    * @param integer $col    Zero indexed column
1626
    * @param string  $str    The string to write
1627
    * @param mixed   $format The XF format for the cell
1628
    * @return integer
1629
    */
1630
    function writeStringBIFF8($row, $col, $str, $format = null)
1631
    {
1632
        if ($this->_input_encoding == 'UTF-16LE')
1633
        {
1634
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1635
            $encoding  = 0x1;
1636
        }
1637
        elseif ($this->_input_encoding != '')
1638
        {
1639
            $str = iconv($this->_input_encoding, 'UTF-16LE', $str);
1640
            $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);
1641
            $encoding  = 0x1;
1642
        }
1643
        else
1644
        {
1645
            $strlen    = strlen($str);
1646
            $encoding  = 0x0;
1647
        }
1648
        $record    = 0x00FD;                   // Record identifier
1649
        $length    = 0x000A;                   // Bytes to follow
1650
        $xf        = $this->_XF($format);      // The cell format
1651
 
1652
        $str_error = 0;
1653
 
1654
        // Check that row and col are valid and store max and min values
1655
        if ($this->_checkRowCol($row, $col) == false) {
1656
            return -2;
1657
        }
1658
 
1659
        $str = pack('vC', $strlen, $encoding).$str;
1660
 
1661
        /* check if string is already present */
1662
        if (!isset($this->_str_table[$str])) {
1663
            $this->_str_table[$str] = $this->_str_unique++;
1664
        }
1665
        $this->_str_total++;
1666
 
1667
        $header    = pack('vv',   $record, $length);
1668
        $data      = pack('vvvV', $row, $col, $xf, $this->_str_table[$str]);
1669
        $this->_append($header.$data);
1670
        return $str_error;
1671
    }
1672
 
1673
    /**
1674
    * Check row and col before writing to a cell, and update the sheet's
1675
    * dimensions accordingly
1676
    *
1677
    * @access private
1678
    * @param integer $row    Zero indexed row
1679
    * @param integer $col    Zero indexed column
1680
    * @return boolean true for success, false if row and/or col are grester
1681
    *                 then maximums allowed.
1682
    */
1683
    function _checkRowCol($row, $col)
1684
    {
1685
        if ($row >= $this->_xls_rowmax) {
1686
            return false;
1687
        }
1688
        if ($col >= $this->_xls_colmax) {
1689
            return false;
1690
        }
1691
        if ($row <  $this->_dim_rowmin) {
1692
            $this->_dim_rowmin = $row;
1693
        }
1694
        if ($row >  $this->_dim_rowmax) {
1695
            $this->_dim_rowmax = $row;
1696
        }
1697
        if ($col <  $this->_dim_colmin) {
1698
            $this->_dim_colmin = $col;
1699
        }
1700
        if ($col >  $this->_dim_colmax) {
1701
            $this->_dim_colmax = $col;
1702
        }
1703
        return true;
1704
    }
1705
 
1706
    /**
418 aurelien 1707
    * Writes a note associated with the cell given by the row and column.
1708
    * NOTE records don't have a length limit.
1709
    *
1710
    * @access public
1711
    * @param integer $row    Zero indexed row
1712
    * @param integer $col    Zero indexed column
1713
    * @param string  $note   The note to write
1714
    */
1715
    function writeNote($row, $col, $note)
1716
    {
1717
        $note_length    = strlen($note);
1718
        $record         = 0x001C;                // Record identifier
1719
        $max_length     = 2048;                  // Maximun length for a NOTE record
1720
        //$length      = 0x0006 + $note_length;    // Bytes to follow
1721
 
1722
        // Check that row and col are valid and store max and min values
1604 raphael 1723
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1724
            return(-2);
1725
        }
1604 raphael 1726
        if ($col >= $this->_xls_colmax) {
418 aurelien 1727
            return(-2);
1728
        }
1604 raphael 1729
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1730
            $this->_dim_rowmin = $row;
1731
        }
1604 raphael 1732
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1733
            $this->_dim_rowmax = $row;
1734
        }
1604 raphael 1735
        if ($col <  $this->_dim_colmin) {
418 aurelien 1736
            $this->_dim_colmin = $col;
1737
        }
1604 raphael 1738
        if ($col >  $this->_dim_colmax) {
418 aurelien 1739
            $this->_dim_colmax = $col;
1740
        }
1604 raphael 1741
 
418 aurelien 1742
        // Length for this record is no more than 2048 + 6
1743
        $length    = 0x0006 + min($note_length, 2048);
1744
        $header    = pack("vv",   $record, $length);
1745
        $data      = pack("vvv", $row, $col, $note_length);
1604 raphael 1746
        $this->_append($header . $data . substr($note, 0, 2048));
418 aurelien 1747
 
1604 raphael 1748
        for ($i = $max_length; $i < $note_length; $i += $max_length) {
418 aurelien 1749
            $chunk  = substr($note, $i, $max_length);
1750
            $length = 0x0006 + strlen($chunk);
1751
            $header = pack("vv",   $record, $length);
1752
            $data   = pack("vvv", -1, 0, strlen($chunk));
1753
            $this->_append($header.$data.$chunk);
1754
        }
1755
        return(0);
1756
    }
1757
 
1758
    /**
1759
    * Write a blank cell to the specified row and column (zero indexed).
1760
    * A blank cell is used to specify formatting without adding a string
1761
    * or a number.
1762
    *
1763
    * A blank cell without a format serves no purpose. Therefore, we don't write
1764
    * a BLANK record unless a format is specified.
1765
    *
1766
    * Returns  0 : normal termination (including no format)
1767
    *         -1 : insufficient number of arguments
1768
    *         -2 : row or column out of range
1769
    *
1770
    * @access public
1771
    * @param integer $row    Zero indexed row
1772
    * @param integer $col    Zero indexed column
1773
    * @param mixed   $format The XF format
1774
    */
1775
    function writeBlank($row, $col, $format)
1776
    {
1777
        // Don't write a blank cell unless it has a format
1604 raphael 1778
        if (!$format) {
418 aurelien 1779
            return(0);
1780
        }
1604 raphael 1781
 
418 aurelien 1782
        $record    = 0x0201;                 // Record identifier
1783
        $length    = 0x0006;                 // Number of bytes to follow
1784
        $xf        = $this->_XF($format);    // The cell format
1604 raphael 1785
 
418 aurelien 1786
        // Check that row and col are valid and store max and min values
1604 raphael 1787
        if ($row >= $this->_xls_rowmax) {
418 aurelien 1788
            return(-2);
1789
        }
1604 raphael 1790
        if ($col >= $this->_xls_colmax) {
418 aurelien 1791
            return(-2);
1792
        }
1604 raphael 1793
        if ($row <  $this->_dim_rowmin) {
418 aurelien 1794
            $this->_dim_rowmin = $row;
1795
        }
1604 raphael 1796
        if ($row >  $this->_dim_rowmax) {
418 aurelien 1797
            $this->_dim_rowmax = $row;
1798
        }
1604 raphael 1799
        if ($col <  $this->_dim_colmin) {
418 aurelien 1800
            $this->_dim_colmin = $col;
1801
        }
1604 raphael 1802
        if ($col >  $this->_dim_colmax) {
418 aurelien 1803
            $this->_dim_colmax = $col;
1804
        }
1604 raphael 1805
 
418 aurelien 1806
        $header    = pack("vv",  $record, $length);
1807
        $data      = pack("vvv", $row, $col, $xf);
1604 raphael 1808
        $this->_append($header . $data);
418 aurelien 1809
        return 0;
1810
    }
1811
 
1812
    /**
1813
    * Write a formula to the specified row and column (zero indexed).
1814
    * The textual representation of the formula is passed to the parser in
1815
    * Parser.php which returns a packed binary string.
1816
    *
1817
    * Returns  0 : normal termination
1818
    *         -1 : formula errors (bad formula)
1819
    *         -2 : row or column out of range
1820
    *
1821
    * @access public
1822
    * @param integer $row     Zero indexed row
1823
    * @param integer $col     Zero indexed column
1824
    * @param string  $formula The formula text string
1825
    * @param mixed   $format  The optional XF format
1826
    * @return integer
1827
    */
1604 raphael 1828
    function writeFormula($row, $col, $formula, $format = null)
418 aurelien 1829
    {
1830
        $record    = 0x0006;     // Record identifier
1604 raphael 1831
 
418 aurelien 1832
        // Excel normally stores the last calculated value of the formula in $num.
1833
        // Clearly we are not in a position to calculate this a priori. Instead
1834
        // we set $num to zero and set the option flags in $grbit to ensure
1835
        // automatic calculation of the formula when the file is opened.
1836
        //
1837
        $xf        = $this->_XF($format); // The cell format
1838
        $num       = 0x00;                // Current value of formula
1839
        $grbit     = 0x03;                // Option flags
1604 raphael 1840
        $unknown   = 0x0000;              // Must be zero
1841
 
1842
 
418 aurelien 1843
        // Check that row and col are valid and store max and min values
1604 raphael 1844
        if ($this->_checkRowCol($row, $col) == false) {
1845
            return -2;
418 aurelien 1846
        }
1604 raphael 1847
 
418 aurelien 1848
        // Strip the '=' or '@' sign at the beginning of the formula string
1604 raphael 1849
        if (preg_match("/^=/", $formula)) {
1850
            $formula = preg_replace("/(^=)/", "", $formula);
1851
        } elseif (preg_match("/^@/", $formula)) {
1852
            $formula = preg_replace("/(^@)/", "", $formula);
1853
        } else {
418 aurelien 1854
            // Error handling
1855
            $this->writeString($row, $col, 'Unrecognised character for formula');
1856
            return -1;
1857
        }
1604 raphael 1858
 
418 aurelien 1859
        // Parse the formula using the parser in Parser.php
1860
        $error = $this->_parser->parse($formula);
1604 raphael 1861
        if ($this->isError($error)) {
1862
            $this->writeString($row, $col, $error->getMessage());
418 aurelien 1863
            return -1;
1864
        }
1604 raphael 1865
 
418 aurelien 1866
        $formula = $this->_parser->toReversePolish();
1604 raphael 1867
        if ($this->isError($formula)) {
418 aurelien 1868
            $this->writeString($row, $col, $formula->getMessage());
1869
            return -1;
1870
        }
1604 raphael 1871
 
418 aurelien 1872
        $formlen    = strlen($formula);    // Length of the binary string
1873
        $length     = 0x16 + $formlen;     // Length of the record data
1604 raphael 1874
 
418 aurelien 1875
        $header    = pack("vv",      $record, $length);
1876
        $data      = pack("vvvdvVv", $row, $col, $xf, $num,
1604 raphael 1877
                                     $grbit, $unknown, $formlen);
1878
 
1879
        $this->_append($header . $data . $formula);
418 aurelien 1880
        return 0;
1881
    }
1604 raphael 1882
 
418 aurelien 1883
    /**
1884
    * Write a hyperlink.
1885
    * This is comprised of two elements: the visible label and
1886
    * the invisible link. The visible label is the same as the link unless an
1887
    * alternative string is specified. The label is written using the
1888
    * writeString() method. Therefore the 255 characters string limit applies.
1889
    * $string and $format are optional.
1890
    *
1891
    * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
1892
    * directory url.
1893
    *
1894
    * Returns  0 : normal termination
1895
    *         -2 : row or column out of range
1896
    *         -3 : long string truncated to 255 chars
1897
    *
1898
    * @access public
1899
    * @param integer $row    Row
1900
    * @param integer $col    Column
1901
    * @param string  $url    URL string
1902
    * @param string  $string Alternative label
1903
    * @param mixed   $format The cell format
1904
    * @return integer
1905
    */
1604 raphael 1906
    function writeUrl($row, $col, $url, $string = '', $format = null)
418 aurelien 1907
    {
1908
        // Add start row and col to arg list
1604 raphael 1909
        return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
418 aurelien 1910
    }
1604 raphael 1911
 
418 aurelien 1912
    /**
1913
    * This is the more general form of writeUrl(). It allows a hyperlink to be
1914
    * written to a range of cells. This function also decides the type of hyperlink
1915
    * to be written. These are either, Web (http, ftp, mailto), Internal
1916
    * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
1917
    *
1918
    * @access private
1919
    * @see writeUrl()
1920
    * @param integer $row1   Start row
1921
    * @param integer $col1   Start column
1922
    * @param integer $row2   End row
1923
    * @param integer $col2   End column
1924
    * @param string  $url    URL string
1925
    * @param string  $string Alternative label
1926
    * @param mixed   $format The cell format
1927
    * @return integer
1928
    */
1604 raphael 1929
 
1930
    function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
418 aurelien 1931
    {
1604 raphael 1932
 
418 aurelien 1933
        // Check for internal/external sheet links or default to web link
1934
        if (preg_match('[^internal:]', $url)) {
1935
            return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
1936
        }
1937
        if (preg_match('[^external:]', $url)) {
1938
            return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
1939
        }
1940
        return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
1941
    }
1604 raphael 1942
 
1943
 
418 aurelien 1944
    /**
1945
    * Used to write http, ftp and mailto hyperlinks.
1946
    * The link type ($options) is 0x03 is the same as absolute dir ref without
1947
    * sheet. However it is differentiated by the $unknown2 data stream.
1948
    *
1949
    * @access private
1950
    * @see writeUrl()
1951
    * @param integer $row1   Start row
1952
    * @param integer $col1   Start column
1953
    * @param integer $row2   End row
1954
    * @param integer $col2   End column
1955
    * @param string  $url    URL string
1956
    * @param string  $str    Alternative label
1957
    * @param mixed   $format The cell format
1958
    * @return integer
1959
    */
1604 raphael 1960
    function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 1961
    {
1962
        $record      = 0x01B8;                       // Record identifier
1963
        $length      = 0x00000;                      // Bytes to follow
1604 raphael 1964
 
1965
        if (!$format) {
418 aurelien 1966
            $format = $this->_url_format;
1967
        }
1604 raphael 1968
 
418 aurelien 1969
        // Write the visible label using the writeString() method.
1970
        if ($str == '') {
1971
            $str = $url;
1972
        }
1604 raphael 1973
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
1974
        if (($str_error == -2) || ($str_error == -3)) {
418 aurelien 1975
            return $str_error;
1976
        }
1604 raphael 1977
 
418 aurelien 1978
        // Pack the undocumented parts of the hyperlink stream
1979
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1980
        $unknown2    = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");
1604 raphael 1981
 
418 aurelien 1982
        // Pack the option flags
1983
        $options     = pack("V", 0x03);
1604 raphael 1984
 
418 aurelien 1985
        // Convert URL to a null terminated wchar string
1986
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
1987
        $url         = $url . "\0\0\0";
1604 raphael 1988
 
418 aurelien 1989
        // Pack the length of the URL
1990
        $url_len     = pack("V", strlen($url));
1604 raphael 1991
 
418 aurelien 1992
        // Calculate the data length
1993
        $length      = 0x34 + strlen($url);
1604 raphael 1994
 
418 aurelien 1995
        // Pack the header data
1996
        $header      = pack("vv",   $record, $length);
1997
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
1604 raphael 1998
 
418 aurelien 1999
        // Write the packed data
1604 raphael 2000
        $this->_append($header . $data .
2001
                       $unknown1 . $options .
2002
                       $unknown2 . $url_len . $url);
418 aurelien 2003
        return($str_error);
2004
    }
1604 raphael 2005
 
418 aurelien 2006
    /**
2007
    * Used to write internal reference hyperlinks such as "Sheet1!A1".
2008
    *
2009
    * @access private
2010
    * @see writeUrl()
2011
    * @param integer $row1   Start row
2012
    * @param integer $col1   Start column
2013
    * @param integer $row2   End row
2014
    * @param integer $col2   End column
2015
    * @param string  $url    URL string
2016
    * @param string  $str    Alternative label
2017
    * @param mixed   $format The cell format
2018
    * @return integer
2019
    */
1604 raphael 2020
    function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 2021
    {
2022
        $record      = 0x01B8;                       // Record identifier
2023
        $length      = 0x00000;                      // Bytes to follow
1604 raphael 2024
 
2025
        if (!$format) {
418 aurelien 2026
            $format = $this->_url_format;
2027
        }
1604 raphael 2028
 
418 aurelien 2029
        // Strip URL type
1604 raphael 2030
        $url = preg_replace('/^internal:/', '', $url);
2031
 
418 aurelien 2032
        // Write the visible label
2033
        if ($str == '') {
2034
            $str = $url;
2035
        }
1604 raphael 2036
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
2037
        if (($str_error == -2) || ($str_error == -3)) {
418 aurelien 2038
            return $str_error;
2039
        }
1604 raphael 2040
 
418 aurelien 2041
        // Pack the undocumented parts of the hyperlink stream
2042
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
1604 raphael 2043
 
418 aurelien 2044
        // Pack the option flags
2045
        $options     = pack("V", 0x08);
1604 raphael 2046
 
418 aurelien 2047
        // Convert the URL type and to a null terminated wchar string
2048
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
2049
        $url         = $url . "\0\0\0";
1604 raphael 2050
 
418 aurelien 2051
        // Pack the length of the URL as chars (not wchars)
2052
        $url_len     = pack("V", floor(strlen($url)/2));
1604 raphael 2053
 
418 aurelien 2054
        // Calculate the data length
2055
        $length      = 0x24 + strlen($url);
1604 raphael 2056
 
418 aurelien 2057
        // Pack the header data
2058
        $header      = pack("vv",   $record, $length);
2059
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);
1604 raphael 2060
 
418 aurelien 2061
        // Write the packed data
1604 raphael 2062
        $this->_append($header . $data .
2063
                       $unknown1 . $options .
2064
                       $url_len . $url);
418 aurelien 2065
        return($str_error);
2066
    }
1604 raphael 2067
 
418 aurelien 2068
    /**
2069
    * Write links to external directory names such as 'c:\foo.xls',
2070
    * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
2071
    *
2072
    * Note: Excel writes some relative links with the $dir_long string. We ignore
2073
    * these cases for the sake of simpler code.
2074
    *
2075
    * @access private
2076
    * @see writeUrl()
2077
    * @param integer $row1   Start row
2078
    * @param integer $col1   Start column
2079
    * @param integer $row2   End row
2080
    * @param integer $col2   End column
2081
    * @param string  $url    URL string
2082
    * @param string  $str    Alternative label
2083
    * @param mixed   $format The cell format
2084
    * @return integer
2085
    */
1604 raphael 2086
    function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
418 aurelien 2087
    {
2088
        // Network drives are different. We will handle them separately
2089
        // MS/Novell network drives and shares start with \\
2090
        if (preg_match('[^external:\\\\]', $url)) {
2091
            return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
2092
        }
2093
 
2094
        $record      = 0x01B8;                       // Record identifier
2095
        $length      = 0x00000;                      // Bytes to follow
2096
 
1604 raphael 2097
        if (!$format) {
418 aurelien 2098
            $format = $this->_url_format;
2099
        }
2100
 
2101
        // Strip URL type and change Unix dir separator to Dos style (if needed)
2102
        //
1604 raphael 2103
        $url = preg_replace('/^external:/', '', $url);
2104
        $url = preg_replace('/\//', "\\", $url);
418 aurelien 2105
 
2106
        // Write the visible label
2107
        if ($str == '') {
1604 raphael 2108
            $str = preg_replace('/\#/', ' - ', $url);
418 aurelien 2109
        }
1604 raphael 2110
        $str_error = is_numeric($str) ? $this->writeNumber($row1, $col1, $str, $format) : $this->writeString($row1, $col1, $str, $format);
418 aurelien 2111
        if (($str_error == -2) or ($str_error == -3)) {
2112
            return $str_error;
2113
        }
2114
 
2115
        // Determine if the link is relative or absolute:
2116
        //   relative if link contains no dir separator, "somefile.xls"
2117
        //   relative if link starts with up-dir, "..\..\somefile.xls"
2118
        //   otherwise, absolute
2119
 
2120
        $absolute    = 0x02; // Bit mask
1604 raphael 2121
        if (!preg_match("/\\\/", $url)) {
418 aurelien 2122
            $absolute    = 0x00;
2123
        }
1604 raphael 2124
        if (preg_match("/^\.\.\\\/", $url)) {
418 aurelien 2125
            $absolute    = 0x00;
2126
        }
1604 raphael 2127
        $link_type               = 0x01 | $absolute;
418 aurelien 2128
 
2129
        // Determine if the link contains a sheet reference and change some of the
2130
        // parameters accordingly.
2131
        // Split the dir name and sheet name (if it exists)
1604 raphael 2132
        /*if (preg_match("/\#/", $url)) {
2133
            list($dir_long, $sheet) = split("\#", $url);
2134
        } else {
2135
            $dir_long = $url;
2136
        }
418 aurelien 2137
 
2138
        if (isset($sheet)) {
2139
            $link_type |= 0x08;
2140
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
1604 raphael 2141
            $sheet      = join("\0", split('', $sheet));
418 aurelien 2142
            $sheet     .= "\0\0\0";
1604 raphael 2143
        } else {
418 aurelien 2144
            $sheet_len   = '';
2145
            $sheet       = '';
1604 raphael 2146
        }*/
2147
        $dir_long = $url;
2148
        if (preg_match("/\#/", $url)) {
2149
            $link_type |= 0x08;
418 aurelien 2150
        }
1604 raphael 2151
 
2152
 
418 aurelien 2153
 
2154
        // Pack the link type
2155
        $link_type   = pack("V", $link_type);
2156
 
2157
        // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
1604 raphael 2158
        $up_count    = preg_match_all("/\.\.\\\/", $dir_long, $useless);
418 aurelien 2159
        $up_count    = pack("v", $up_count);
2160
 
2161
        // Store the short dos dir name (null terminated)
1604 raphael 2162
        $dir_short   = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
418 aurelien 2163
 
2164
        // Store the long dir name as a wchar string (non-null terminated)
1604 raphael 2165
        //$dir_long       = join("\0", split('', $dir_long));
418 aurelien 2166
        $dir_long       = $dir_long . "\0";
2167
 
2168
        // Pack the lengths of the dir strings
2169
        $dir_short_len = pack("V", strlen($dir_short)      );
2170
        $dir_long_len  = pack("V", strlen($dir_long)       );
1604 raphael 2171
        $stream_len    = pack("V", 0);//strlen($dir_long) + 0x06);
418 aurelien 2172
 
2173
        // Pack the undocumented parts of the hyperlink stream
2174
        $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'       );
2175
        $unknown2 = pack("H*",'0303000000000000C000000000000046'               );
2176
        $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
2177
        $unknown4 = pack("v",  0x03                                            );
2178
 
2179
        // Pack the main data stream
2180
        $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
2181
                          $unknown1     .
2182
                          $link_type    .
2183
                          $unknown2     .
2184
                          $up_count     .
2185
                          $dir_short_len.
2186
                          $dir_short    .
2187
                          $unknown3     .
1604 raphael 2188
                          $stream_len   ;/*.
418 aurelien 2189
                          $dir_long_len .
2190
                          $unknown4     .
2191
                          $dir_long     .
2192
                          $sheet_len    .
1604 raphael 2193
                          $sheet        ;*/
418 aurelien 2194
 
2195
        // Pack the header data
2196
        $length   = strlen($data);
2197
        $header   = pack("vv", $record, $length);
2198
 
2199
        // Write the packed data
2200
        $this->_append($header. $data);
2201
        return($str_error);
2202
    }
1604 raphael 2203
 
2204
 
418 aurelien 2205
    /**
2206
    * This method is used to set the height and format for a row.
2207
    *
2208
    * @access public
2209
    * @param integer $row    The row to set
1604 raphael 2210
    * @param integer $height Height we are giving to the row.
2211
    *                        Use null to set XF without setting height
418 aurelien 2212
    * @param mixed   $format XF format we are giving to the row
1604 raphael 2213
    * @param bool    $hidden The optional hidden attribute
2214
    * @param integer $level  The optional outline level for row, in range [0,7]
418 aurelien 2215
    */
1604 raphael 2216
    function setRow($row, $height, $format = null, $hidden = false, $level = 0)
418 aurelien 2217
    {
2218
        $record      = 0x0208;               // Record identifier
2219
        $length      = 0x0010;               // Number of bytes to follow
1604 raphael 2220
 
418 aurelien 2221
        $colMic      = 0x0000;               // First defined column
2222
        $colMac      = 0x0000;               // Last defined column
2223
        $irwMac      = 0x0000;               // Used by Excel to optimise loading
2224
        $reserved    = 0x0000;               // Reserved
1604 raphael 2225
        $grbit       = 0x0000;               // Option flags
418 aurelien 2226
        $ixfe        = $this->_XF($format);  // XF index
1604 raphael 2227
 
2228
        // set _row_sizes so _sizeRow() can use it
2229
        $this->_row_sizes[$row] = $height;
2230
 
2231
        // Use setRow($row, null, $XF) to set XF format without setting height
2232
        if ($height != null) {
418 aurelien 2233
            $miyRw = $height * 20;  // row height
1604 raphael 2234
        } else {
418 aurelien 2235
            $miyRw = 0xff;          // default row height is 256
2236
        }
1604 raphael 2237
 
2238
        $level = max(0, min($level, 7));  // level should be between 0 and 7
2239
        $this->_outline_row_level = max($level, $this->_outline_row_level);
2240
 
2241
 
2242
        // Set the options flags. fUnsynced is used to show that the font and row
2243
        // heights are not compatible. This is usually the case for WriteExcel.
2244
        // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
2245
        // is collapsed. Instead it is used to indicate that the previous row is
2246
        // collapsed. The zero height flag, 0x20, is used to collapse a row.
2247
 
2248
        $grbit |= $level;
2249
        if ($hidden) {
2250
            $grbit |= 0x0020;
2251
        }
2252
        $grbit |= 0x0040; // fUnsynced
2253
        if ($format) {
2254
            $grbit |= 0x0080;
2255
        }
2256
        $grbit |= 0x0100;
2257
 
418 aurelien 2258
        $header   = pack("vv",       $record, $length);
2259
        $data     = pack("vvvvvvvv", $row, $colMic, $colMac, $miyRw,
2260
                                     $irwMac,$reserved, $grbit, $ixfe);
2261
        $this->_append($header.$data);
2262
    }
1604 raphael 2263
 
418 aurelien 2264
    /**
2265
    * Writes Excel DIMENSIONS to define the area in which there is data.
2266
    *
2267
    * @access private
2268
    */
1604 raphael 2269
    function _storeDimensions()
418 aurelien 2270
    {
1604 raphael 2271
        $record    = 0x0200;                 // Record identifier
2272
        $row_min   = $this->_dim_rowmin;     // First row
2273
        $row_max   = $this->_dim_rowmax + 1; // Last row plus 1
2274
        $col_min   = $this->_dim_colmin;     // First column
2275
        $col_max   = $this->_dim_colmax + 1; // Last column plus 1
2276
        $reserved  = 0x0000;                 // Reserved by Excel
2277
 
2278
        if ($this->_BIFF_version == 0x0500) {
2279
            $length    = 0x000A;               // Number of bytes to follow
2280
            $data      = pack("vvvvv", $row_min, $row_max,
2281
                                       $col_min, $col_max, $reserved);
2282
        } elseif ($this->_BIFF_version == 0x0600) {
2283
            $length    = 0x000E;
2284
            $data      = pack("VVvvv", $row_min, $row_max,
2285
                                       $col_min, $col_max, $reserved);
2286
        }
2287
        $header = pack("vv", $record, $length);
418 aurelien 2288
        $this->_prepend($header.$data);
2289
    }
1604 raphael 2290
 
418 aurelien 2291
    /**
2292
    * Write BIFF record Window2.
2293
    *
2294
    * @access private
2295
    */
2296
    function _storeWindow2()
2297
    {
2298
        $record         = 0x023E;     // Record identifier
1604 raphael 2299
        if ($this->_BIFF_version == 0x0500) {
2300
            $length         = 0x000A;     // Number of bytes to follow
2301
        } elseif ($this->_BIFF_version == 0x0600) {
2302
            $length         = 0x0012;
2303
        }
2304
 
418 aurelien 2305
        $grbit          = 0x00B6;     // Option flags
2306
        $rwTop          = 0x0000;     // Top row visible in window
2307
        $colLeft        = 0x0000;     // Leftmost column visible in window
1604 raphael 2308
 
2309
 
418 aurelien 2310
        // The options flags that comprise $grbit
2311
        $fDspFmla       = 0;                     // 0 - bit
1604 raphael 2312
        $fDspGrid       = $this->_screen_gridlines; // 1
418 aurelien 2313
        $fDspRwCol      = 1;                     // 2
2314
        $fFrozen        = $this->_frozen;        // 3
2315
        $fDspZeros      = 1;                     // 4
2316
        $fDefaultHdr    = 1;                     // 5
1604 raphael 2317
        $fArabic        = $this->_Arabic;        // 6
2318
        $fDspGuts       = $this->_outline_on;    // 7
418 aurelien 2319
        $fFrozenNoSplit = 0;                     // 0 - bit
2320
        $fSelected      = $this->selected;       // 1
2321
        $fPaged         = 1;                     // 2
1604 raphael 2322
 
418 aurelien 2323
        $grbit             = $fDspFmla;
2324
        $grbit            |= $fDspGrid       << 1;
2325
        $grbit            |= $fDspRwCol      << 2;
2326
        $grbit            |= $fFrozen        << 3;
2327
        $grbit            |= $fDspZeros      << 4;
2328
        $grbit            |= $fDefaultHdr    << 5;
2329
        $grbit            |= $fArabic        << 6;
2330
        $grbit            |= $fDspGuts       << 7;
2331
        $grbit            |= $fFrozenNoSplit << 8;
2332
        $grbit            |= $fSelected      << 9;
2333
        $grbit            |= $fPaged         << 10;
1604 raphael 2334
 
418 aurelien 2335
        $header  = pack("vv",   $record, $length);
1604 raphael 2336
        $data    = pack("vvv", $grbit, $rwTop, $colLeft);
2337
        // FIXME !!!
2338
        if ($this->_BIFF_version == 0x0500) {
2339
            $rgbHdr         = 0x00000000; // Row/column heading and gridline color
2340
            $data .= pack("V", $rgbHdr);
2341
        } elseif ($this->_BIFF_version == 0x0600) {
2342
            $rgbHdr       = 0x0040; // Row/column heading and gridline color index
2343
            $zoom_factor_page_break = 0x0000;
2344
            $zoom_factor_normal     = 0x0000;
2345
            $data .= pack("vvvvV", $rgbHdr, 0x0000, $zoom_factor_page_break, $zoom_factor_normal, 0x00000000);
2346
        }
418 aurelien 2347
        $this->_append($header.$data);
2348
    }
1604 raphael 2349
 
418 aurelien 2350
    /**
2351
    * Write BIFF record DEFCOLWIDTH if COLINFO records are in use.
2352
    *
2353
    * @access private
2354
    */
2355
    function _storeDefcol()
2356
    {
2357
        $record   = 0x0055;      // Record identifier
2358
        $length   = 0x0002;      // Number of bytes to follow
2359
        $colwidth = 0x0008;      // Default column width
1604 raphael 2360
 
418 aurelien 2361
        $header   = pack("vv", $record, $length);
2362
        $data     = pack("v",  $colwidth);
1604 raphael 2363
        $this->_prepend($header . $data);
418 aurelien 2364
    }
1604 raphael 2365
 
418 aurelien 2366
    /**
2367
    * Write BIFF record COLINFO to define column widths
2368
    *
2369
    * Note: The SDK says the record length is 0x0B but Excel writes a 0x0C
2370
    * length record.
2371
    *
2372
    * @access private
2373
    * @param array $col_array This is the only parameter received and is composed of the following:
2374
    *                0 => First formatted column,
2375
    *                1 => Last formatted column,
2376
    *                2 => Col width (8.43 is Excel default),
2377
    *                3 => The optional XF format of the column,
2378
    *                4 => Option flags.
1604 raphael 2379
    *                5 => Optional outline level
418 aurelien 2380
    */
2381
    function _storeColinfo($col_array)
2382
    {
2383
        if (isset($col_array[0])) {
2384
            $colFirst = $col_array[0];
2385
        }
2386
        if (isset($col_array[1])) {
2387
            $colLast = $col_array[1];
2388
        }
2389
        if (isset($col_array[2])) {
2390
            $coldx = $col_array[2];
1604 raphael 2391
        } else {
418 aurelien 2392
            $coldx = 8.43;
2393
        }
2394
        if (isset($col_array[3])) {
2395
            $format = $col_array[3];
1604 raphael 2396
        } else {
418 aurelien 2397
            $format = 0;
2398
        }
2399
        if (isset($col_array[4])) {
2400
            $grbit = $col_array[4];
1604 raphael 2401
        } else {
418 aurelien 2402
            $grbit = 0;
2403
        }
1604 raphael 2404
        if (isset($col_array[5])) {
2405
            $level = $col_array[5];
2406
        } else {
2407
            $level = 0;
2408
        }
418 aurelien 2409
        $record   = 0x007D;          // Record identifier
2410
        $length   = 0x000B;          // Number of bytes to follow
1604 raphael 2411
 
418 aurelien 2412
        $coldx   += 0.72;            // Fudge. Excel subtracts 0.72 !?
2413
        $coldx   *= 256;             // Convert to units of 1/256 of a char
1604 raphael 2414
 
418 aurelien 2415
        $ixfe     = $this->_XF($format);
2416
        $reserved = 0x00;            // Reserved
1604 raphael 2417
 
2418
        $level = max(0, min($level, 7));
2419
        $grbit |= $level << 8;
2420
 
418 aurelien 2421
        $header   = pack("vv",     $record, $length);
2422
        $data     = pack("vvvvvC", $colFirst, $colLast, $coldx,
2423
                                   $ixfe, $grbit, $reserved);
2424
        $this->_prepend($header.$data);
2425
    }
1604 raphael 2426
 
418 aurelien 2427
    /**
2428
    * Write BIFF record SELECTION.
2429
    *
2430
    * @access private
2431
    * @param array $array array containing ($rwFirst,$colFirst,$rwLast,$colLast)
2432
    * @see setSelection()
2433
    */
2434
    function _storeSelection($array)
2435
    {
2436
        list($rwFirst,$colFirst,$rwLast,$colLast) = $array;
2437
        $record   = 0x001D;                  // Record identifier
2438
        $length   = 0x000F;                  // Number of bytes to follow
1604 raphael 2439
 
418 aurelien 2440
        $pnn      = $this->_active_pane;     // Pane position
2441
        $rwAct    = $rwFirst;                // Active row
2442
        $colAct   = $colFirst;               // Active column
2443
        $irefAct  = 0;                       // Active cell ref
2444
        $cref     = 1;                       // Number of refs
1604 raphael 2445
 
418 aurelien 2446
        if (!isset($rwLast)) {
2447
            $rwLast   = $rwFirst;       // Last  row in reference
2448
        }
2449
        if (!isset($colLast)) {
2450
            $colLast  = $colFirst;      // Last  col in reference
2451
        }
1604 raphael 2452
 
418 aurelien 2453
        // Swap last row/col for first row/col as necessary
1604 raphael 2454
        if ($rwFirst > $rwLast) {
418 aurelien 2455
            list($rwFirst, $rwLast) = array($rwLast, $rwFirst);
2456
        }
1604 raphael 2457
 
2458
        if ($colFirst > $colLast) {
418 aurelien 2459
            list($colFirst, $colLast) = array($colLast, $colFirst);
2460
        }
1604 raphael 2461
 
418 aurelien 2462
        $header   = pack("vv",         $record, $length);
2463
        $data     = pack("CvvvvvvCC",  $pnn, $rwAct, $colAct,
2464
                                       $irefAct, $cref,
2465
                                       $rwFirst, $rwLast,
2466
                                       $colFirst, $colLast);
1604 raphael 2467
        $this->_append($header . $data);
418 aurelien 2468
    }
1604 raphael 2469
 
418 aurelien 2470
    /**
1604 raphael 2471
    * Store the MERGEDCELLS record for all ranges of merged cells
2472
    *
2473
    * @access private
2474
    */
2475
    function _storeMergedCells()
2476
    {
2477
        // if there are no merged cell ranges set, return
2478
        if (count($this->_merged_ranges) == 0) {
2479
            return;
2480
        }
2481
        $record   = 0x00E5;
2482
        foreach($this->_merged_ranges as $ranges)
2483
          {
2484
            $length   = 2 + count($ranges) * 8;
2485
            $header   = pack('vv', $record, $length);
2486
            $data     = pack('v',  count($ranges));
2487
            foreach($ranges as $range)
2488
              $data .= pack('vvvv', $range[0], $range[2], $range[1], $range[3]);
2489
            $string=$header.$data;
2490
            $this->_append(&$string, true);
2491
          }
2492
    }
2493
 
2494
    /**
418 aurelien 2495
    * Write BIFF record EXTERNCOUNT to indicate the number of external sheet
2496
    * references in a worksheet.
2497
    *
2498
    * Excel only stores references to external sheets that are used in formulas.
2499
    * For simplicity we store references to all the sheets in the workbook
2500
    * regardless of whether they are used or not. This reduces the overall
2501
    * complexity and eliminates the need for a two way dialogue between the formula
2502
    * parser the worksheet objects.
2503
    *
2504
    * @access private
2505
    * @param integer $count The number of external sheet references in this worksheet
2506
    */
2507
    function _storeExterncount($count)
2508
    {
1604 raphael 2509
        $record = 0x0016;          // Record identifier
2510
        $length = 0x0002;          // Number of bytes to follow
2511
 
2512
        $header = pack("vv", $record, $length);
2513
        $data   = pack("v",  $count);
2514
        $this->_prepend($header . $data);
418 aurelien 2515
    }
1604 raphael 2516
 
418 aurelien 2517
    /**
2518
    * Writes the Excel BIFF EXTERNSHEET record. These references are used by
2519
    * formulas. A formula references a sheet name via an index. Since we store a
2520
    * reference to all of the external worksheets the EXTERNSHEET index is the same
2521
    * as the worksheet index.
2522
    *
2523
    * @access private
2524
    * @param string $sheetname The name of a external worksheet
2525
    */
2526
    function _storeExternsheet($sheetname)
2527
    {
2528
        $record    = 0x0017;         // Record identifier
1604 raphael 2529
 
418 aurelien 2530
        // References to the current sheet are encoded differently to references to
2531
        // external sheets.
2532
        //
2533
        if ($this->name == $sheetname) {
2534
            $sheetname = '';
2535
            $length    = 0x02;  // The following 2 bytes
2536
            $cch       = 1;     // The following byte
2537
            $rgch      = 0x02;  // Self reference
1604 raphael 2538
        } else {
418 aurelien 2539
            $length    = 0x02 + strlen($sheetname);
2540
            $cch       = strlen($sheetname);
2541
            $rgch      = 0x03;  // Reference to a sheet in the current workbook
2542
        }
1604 raphael 2543
 
2544
        $header = pack("vv",  $record, $length);
2545
        $data   = pack("CC", $cch, $rgch);
2546
        $this->_prepend($header . $data . $sheetname);
418 aurelien 2547
    }
1604 raphael 2548
 
418 aurelien 2549
    /**
2550
    * Writes the Excel BIFF PANE record.
2551
    * The panes can either be frozen or thawed (unfrozen).
2552
    * Frozen panes are specified in terms of an integer number of rows and columns.
2553
    * Thawed panes are specified in terms of Excel's units for rows and columns.
2554
    *
2555
    * @access private
2556
    * @param array $panes This is the only parameter received and is composed of the following:
2557
    *                     0 => Vertical split position,
2558
    *                     1 => Horizontal split position
2559
    *                     2 => Top row visible
2560
    *                     3 => Leftmost column visible
2561
    *                     4 => Active pane
2562
    */
2563
    function _storePanes($panes)
2564
    {
2565
        $y       = $panes[0];
2566
        $x       = $panes[1];
2567
        $rwTop   = $panes[2];
2568
        $colLeft = $panes[3];
2569
        if (count($panes) > 4) { // if Active pane was received
2570
            $pnnAct = $panes[4];
1604 raphael 2571
        } else {
2572
            $pnnAct = null;
418 aurelien 2573
        }
2574
        $record  = 0x0041;       // Record identifier
2575
        $length  = 0x000A;       // Number of bytes to follow
1604 raphael 2576
 
418 aurelien 2577
        // Code specific to frozen or thawed panes.
1604 raphael 2578
        if ($this->_frozen) {
418 aurelien 2579
            // Set default values for $rwTop and $colLeft
2580
            if (!isset($rwTop)) {
2581
                $rwTop   = $y;
2582
            }
2583
            if (!isset($colLeft)) {
2584
                $colLeft = $x;
2585
            }
1604 raphael 2586
        } else {
418 aurelien 2587
            // Set default values for $rwTop and $colLeft
2588
            if (!isset($rwTop)) {
2589
                $rwTop   = 0;
2590
            }
2591
            if (!isset($colLeft)) {
2592
                $colLeft = 0;
2593
            }
1604 raphael 2594
 
418 aurelien 2595
            // Convert Excel's row and column units to the internal units.
2596
            // The default row height is 12.75
2597
            // The default column width is 8.43
2598
            // The following slope and intersection values were interpolated.
2599
            //
2600
            $y = 20*$y      + 255;
2601
            $x = 113.879*$x + 390;
2602
        }
1604 raphael 2603
 
2604
 
418 aurelien 2605
        // Determine which pane should be active. There is also the undocumented
2606
        // option to override this should it be necessary: may be removed later.
2607
        //
1604 raphael 2608
        if (!isset($pnnAct)) {
2609
            if ($x != 0 && $y != 0) {
418 aurelien 2610
                $pnnAct = 0; // Bottom right
1604 raphael 2611
            }
2612
            if ($x != 0 && $y == 0) {
418 aurelien 2613
                $pnnAct = 1; // Top right
1604 raphael 2614
            }
2615
            if ($x == 0 && $y != 0) {
418 aurelien 2616
                $pnnAct = 2; // Bottom left
1604 raphael 2617
            }
2618
            if ($x == 0 && $y == 0) {
418 aurelien 2619
                $pnnAct = 3; // Top left
1604 raphael 2620
            }
418 aurelien 2621
        }
1604 raphael 2622
 
418 aurelien 2623
        $this->_active_pane = $pnnAct; // Used in _storeSelection
1604 raphael 2624
 
418 aurelien 2625
        $header     = pack("vv",    $record, $length);
2626
        $data       = pack("vvvvv", $x, $y, $rwTop, $colLeft, $pnnAct);
1604 raphael 2627
        $this->_append($header . $data);
418 aurelien 2628
    }
1604 raphael 2629
 
418 aurelien 2630
    /**
2631
    * Store the page setup SETUP BIFF record.
2632
    *
2633
    * @access private
2634
    */
2635
    function _storeSetup()
2636
    {
2637
        $record       = 0x00A1;                  // Record identifier
2638
        $length       = 0x0022;                  // Number of bytes to follow
1604 raphael 2639
 
418 aurelien 2640
        $iPaperSize   = $this->_paper_size;    // Paper size
2641
        $iScale       = $this->_print_scale;   // Print scaling factor
2642
        $iPageStart   = 0x01;                 // Starting page number
2643
        $iFitWidth    = $this->_fit_width;    // Fit to number of pages wide
2644
        $iFitHeight   = $this->_fit_height;   // Fit to number of pages high
2645
        $grbit        = 0x00;                 // Option flags
2646
        $iRes         = 0x0258;               // Print resolution
2647
        $iVRes        = 0x0258;               // Vertical print resolution
2648
        $numHdr       = $this->_margin_head;  // Header Margin
2649
        $numFtr       = $this->_margin_foot;   // Footer Margin
2650
        $iCopies      = 0x01;                 // Number of copies
1604 raphael 2651
 
418 aurelien 2652
        $fLeftToRight = 0x0;                     // Print over then down
2653
        $fLandscape   = $this->_orientation;     // Page orientation
2654
        $fNoPls       = 0x0;                     // Setup not read from printer
2655
        $fNoColor     = 0x0;                     // Print black and white
2656
        $fDraft       = 0x0;                     // Print draft quality
2657
        $fNotes       = 0x0;                     // Print notes
2658
        $fNoOrient    = 0x0;                     // Orientation not set
2659
        $fUsePage     = 0x0;                     // Use custom starting page
1604 raphael 2660
 
418 aurelien 2661
        $grbit           = $fLeftToRight;
2662
        $grbit          |= $fLandscape    << 1;
2663
        $grbit          |= $fNoPls        << 2;
2664
        $grbit          |= $fNoColor      << 3;
2665
        $grbit          |= $fDraft        << 4;
2666
        $grbit          |= $fNotes        << 5;
2667
        $grbit          |= $fNoOrient     << 6;
2668
        $grbit          |= $fUsePage      << 7;
1604 raphael 2669
 
418 aurelien 2670
        $numHdr = pack("d", $numHdr);
2671
        $numFtr = pack("d", $numFtr);
1604 raphael 2672
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2673
            $numHdr = strrev($numHdr);
2674
            $numFtr = strrev($numFtr);
2675
        }
1604 raphael 2676
 
418 aurelien 2677
        $header = pack("vv", $record, $length);
2678
        $data1  = pack("vvvvvvvv", $iPaperSize,
2679
                                   $iScale,
2680
                                   $iPageStart,
2681
                                   $iFitWidth,
2682
                                   $iFitHeight,
2683
                                   $grbit,
2684
                                   $iRes,
2685
                                   $iVRes);
2686
        $data2  = $numHdr.$numFtr;
2687
        $data3  = pack("v", $iCopies);
1604 raphael 2688
        $this->_prepend($header . $data1 . $data2 . $data3);
418 aurelien 2689
    }
1604 raphael 2690
 
418 aurelien 2691
    /**
2692
    * Store the header caption BIFF record.
2693
    *
2694
    * @access private
2695
    */
2696
    function _storeHeader()
2697
    {
2698
        $record  = 0x0014;               // Record identifier
1604 raphael 2699
 
2700
        $str      = $this->_header;       // header string
2701
        $cch      = strlen($str);         // Length of header string
2702
        if ($this->_BIFF_version == 0x0600) {
2703
            $encoding = 0x0;                  // TODO: Unicode support
2704
            $length   = 3 + $cch;             // Bytes to follow
2705
        } else {
2706
            $length  = 1 + $cch;             // Bytes to follow
2707
        }
2708
 
2709
        $header   = pack("vv", $record, $length);
2710
        if ($this->_BIFF_version == 0x0600) {
2711
            $data     = pack("vC",  $cch, $encoding);
2712
        } else {
2713
            $data      = pack("C",  $cch);
2714
        }
2715
 
2716
        $this->_prepend($header.$data.$str);
418 aurelien 2717
    }
1604 raphael 2718
 
418 aurelien 2719
    /**
2720
    * Store the footer caption BIFF record.
2721
    *
2722
    * @access private
2723
    */
2724
    function _storeFooter()
2725
    {
2726
        $record  = 0x0015;               // Record identifier
1604 raphael 2727
 
2728
        $str      = $this->_footer;       // Footer string
2729
        $cch      = strlen($str);         // Length of footer string
2730
        if ($this->_BIFF_version == 0x0600) {
2731
            $encoding = 0x0;                  // TODO: Unicode support
2732
            $length   = 3 + $cch;             // Bytes to follow
2733
        } else {
2734
            $length  = 1 + $cch;
2735
        }
2736
 
418 aurelien 2737
        $header    = pack("vv", $record, $length);
1604 raphael 2738
        if ($this->_BIFF_version == 0x0600) {
2739
            $data      = pack("vC",  $cch, $encoding);
2740
        } else {
2741
            $data      = pack("C",  $cch);
2742
        }
2743
 
2744
        $this->_prepend($header . $data . $str);
418 aurelien 2745
    }
1604 raphael 2746
 
418 aurelien 2747
    /**
2748
    * Store the horizontal centering HCENTER BIFF record.
2749
    *
2750
    * @access private
2751
    */
2752
    function _storeHcenter()
2753
    {
2754
        $record   = 0x0083;              // Record identifier
2755
        $length   = 0x0002;              // Bytes to follow
1604 raphael 2756
 
418 aurelien 2757
        $fHCenter = $this->_hcenter;     // Horizontal centering
1604 raphael 2758
 
418 aurelien 2759
        $header    = pack("vv", $record, $length);
2760
        $data      = pack("v",  $fHCenter);
1604 raphael 2761
 
2762
        $this->_prepend($header.$data);
418 aurelien 2763
    }
1604 raphael 2764
 
418 aurelien 2765
    /**
2766
    * Store the vertical centering VCENTER BIFF record.
2767
    *
2768
    * @access private
2769
    */
2770
    function _storeVcenter()
2771
    {
2772
        $record   = 0x0084;              // Record identifier
2773
        $length   = 0x0002;              // Bytes to follow
1604 raphael 2774
 
418 aurelien 2775
        $fVCenter = $this->_vcenter;     // Horizontal centering
1604 raphael 2776
 
418 aurelien 2777
        $header    = pack("vv", $record, $length);
2778
        $data      = pack("v",  $fVCenter);
1604 raphael 2779
        $this->_prepend($header . $data);
418 aurelien 2780
    }
1604 raphael 2781
 
418 aurelien 2782
    /**
2783
    * Store the LEFTMARGIN BIFF record.
2784
    *
2785
    * @access private
2786
    */
2787
    function _storeMarginLeft()
2788
    {
2789
        $record  = 0x0026;                   // Record identifier
2790
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2791
 
418 aurelien 2792
        $margin  = $this->_margin_left;       // Margin in inches
1604 raphael 2793
 
418 aurelien 2794
        $header    = pack("vv",  $record, $length);
2795
        $data      = pack("d",   $margin);
1604 raphael 2796
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2797
            $data = strrev($data);
2798
        }
1604 raphael 2799
 
2800
        $this->_prepend($header . $data);
418 aurelien 2801
    }
1604 raphael 2802
 
418 aurelien 2803
    /**
2804
    * Store the RIGHTMARGIN BIFF record.
2805
    *
2806
    * @access private
2807
    */
2808
    function _storeMarginRight()
2809
    {
2810
        $record  = 0x0027;                   // Record identifier
2811
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2812
 
418 aurelien 2813
        $margin  = $this->_margin_right;      // Margin in inches
1604 raphael 2814
 
418 aurelien 2815
        $header    = pack("vv",  $record, $length);
2816
        $data      = pack("d",   $margin);
1604 raphael 2817
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2818
            $data = strrev($data);
2819
        }
1604 raphael 2820
 
2821
        $this->_prepend($header . $data);
418 aurelien 2822
    }
1604 raphael 2823
 
418 aurelien 2824
    /**
2825
    * Store the TOPMARGIN BIFF record.
2826
    *
2827
    * @access private
2828
    */
2829
    function _storeMarginTop()
2830
    {
2831
        $record  = 0x0028;                   // Record identifier
2832
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2833
 
418 aurelien 2834
        $margin  = $this->_margin_top;        // Margin in inches
1604 raphael 2835
 
418 aurelien 2836
        $header    = pack("vv",  $record, $length);
2837
        $data      = pack("d",   $margin);
1604 raphael 2838
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2839
            $data = strrev($data);
2840
        }
1604 raphael 2841
 
2842
        $this->_prepend($header . $data);
418 aurelien 2843
    }
1604 raphael 2844
 
418 aurelien 2845
    /**
2846
    * Store the BOTTOMMARGIN BIFF record.
2847
    *
2848
    * @access private
2849
    */
2850
    function _storeMarginBottom()
2851
    {
2852
        $record  = 0x0029;                   // Record identifier
2853
        $length  = 0x0008;                   // Bytes to follow
1604 raphael 2854
 
418 aurelien 2855
        $margin  = $this->_margin_bottom;     // Margin in inches
1604 raphael 2856
 
418 aurelien 2857
        $header    = pack("vv",  $record, $length);
2858
        $data      = pack("d",   $margin);
1604 raphael 2859
        if ($this->_byte_order) { // if it's Big Endian
418 aurelien 2860
            $data = strrev($data);
2861
        }
1604 raphael 2862
 
2863
        $this->_prepend($header . $data);
418 aurelien 2864
    }
2865
 
2866
    /**
2867
    * Merges the area given by its arguments.
2868
    * This is an Excel97/2000 method. It is required to perform more complicated
2869
    * merging than the normal setAlign('merge').
2870
    *
2871
    * @access public
2872
    * @param integer $first_row First row of the area to merge
2873
    * @param integer $first_col First column of the area to merge
2874
    * @param integer $last_row  Last row of the area to merge
2875
    * @param integer $last_col  Last column of the area to merge
2876
    */
2877
    function mergeCells($first_row, $first_col, $last_row, $last_col)
2878
    {
2879
        $record  = 0x00E5;                   // Record identifier
2880
        $length  = 0x000A;                   // Bytes to follow
2881
        $cref     = 1;                       // Number of refs
2882
 
2883
        // Swap last row/col for first row/col as necessary
2884
        if ($first_row > $last_row) {
2885
            list($first_row, $last_row) = array($last_row, $first_row);
2886
        }
1604 raphael 2887
 
418 aurelien 2888
        if ($first_col > $last_col) {
2889
            list($first_col, $last_col) = array($last_col, $first_col);
2890
        }
1604 raphael 2891
 
418 aurelien 2892
        $header   = pack("vv",    $record, $length);
2893
        $data     = pack("vvvvv", $cref, $first_row, $last_row,
2894
                                  $first_col, $last_col);
1604 raphael 2895
 
418 aurelien 2896
        $this->_append($header.$data);
2897
    }
2898
 
2899
    /**
2900
    * Write the PRINTHEADERS BIFF record.
2901
    *
2902
    * @access private
2903
    */
2904
    function _storePrintHeaders()
2905
    {
2906
        $record      = 0x002a;                   // Record identifier
2907
        $length      = 0x0002;                   // Bytes to follow
1604 raphael 2908
 
418 aurelien 2909
        $fPrintRwCol = $this->_print_headers;     // Boolean flag
1604 raphael 2910
 
418 aurelien 2911
        $header      = pack("vv", $record, $length);
2912
        $data        = pack("v", $fPrintRwCol);
1604 raphael 2913
        $this->_prepend($header . $data);
418 aurelien 2914
    }
1604 raphael 2915
 
418 aurelien 2916
    /**
2917
    * Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the
2918
    * GRIDSET record.
2919
    *
2920
    * @access private
2921
    */
2922
    function _storePrintGridlines()
2923
    {
2924
        $record      = 0x002b;                    // Record identifier
2925
        $length      = 0x0002;                    // Bytes to follow
1604 raphael 2926
 
418 aurelien 2927
        $fPrintGrid  = $this->_print_gridlines;    // Boolean flag
1604 raphael 2928
 
418 aurelien 2929
        $header      = pack("vv", $record, $length);
2930
        $data        = pack("v", $fPrintGrid);
1604 raphael 2931
        $this->_prepend($header . $data);
418 aurelien 2932
    }
1604 raphael 2933
 
418 aurelien 2934
    /**
2935
    * Write the GRIDSET BIFF record. Must be used in conjunction with the
2936
    * PRINTGRIDLINES record.
2937
    *
2938
    * @access private
2939
    */
2940
    function _storeGridset()
2941
    {
2942
        $record      = 0x0082;                        // Record identifier
2943
        $length      = 0x0002;                        // Bytes to follow
1604 raphael 2944
 
418 aurelien 2945
        $fGridSet    = !($this->_print_gridlines);     // Boolean flag
1604 raphael 2946
 
418 aurelien 2947
        $header      = pack("vv",  $record, $length);
2948
        $data        = pack("v",   $fGridSet);
1604 raphael 2949
        $this->_prepend($header . $data);
2950
    }
2951
 
2952
    /**
2953
    * Write the GUTS BIFF record. This is used to configure the gutter margins
2954
    * where Excel outline symbols are displayed. The visibility of the gutters is
2955
    * controlled by a flag in WSBOOL.
2956
    *
2957
    * @see _storeWsbool()
2958
    * @access private
2959
    */
2960
    function _storeGuts()
2961
    {
2962
        $record      = 0x0080;   // Record identifier
2963
        $length      = 0x0008;   // Bytes to follow
2964
 
2965
        $dxRwGut     = 0x0000;   // Size of row gutter
2966
        $dxColGut    = 0x0000;   // Size of col gutter
2967
 
2968
        $row_level   = $this->_outline_row_level;
2969
        $col_level   = 0;
2970
 
2971
        // Calculate the maximum column outline level. The equivalent calculation
2972
        // for the row outline level is carried out in setRow().
2973
        $colcount = count($this->_colinfo);
2974
        for ($i = 0; $i < $colcount; $i++) {
2975
           // Skip cols without outline level info.
2976
           if (count($this->_colinfo[$i]) >= 6) {
2977
              $col_level = max($this->_colinfo[$i][5], $col_level);
2978
           }
2979
        }
2980
 
2981
        // Set the limits for the outline levels (0 <= x <= 7).
2982
        $col_level = max(0, min($col_level, 7));
2983
 
2984
        // The displayed level is one greater than the max outline levels
2985
        if ($row_level) {
2986
            $row_level++;
2987
        }
2988
        if ($col_level) {
2989
            $col_level++;
2990
        }
2991
 
2992
        $header = pack("vv",   $record, $length);
2993
        $data   = pack("vvvv", $dxRwGut, $dxColGut, $row_level, $col_level);
2994
 
418 aurelien 2995
        $this->_prepend($header.$data);
2996
    }
1604 raphael 2997
 
2998
 
418 aurelien 2999
    /**
3000
    * Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction
3001
    * with the SETUP record.
3002
    *
3003
    * @access private
3004
    */
3005
    function _storeWsbool()
3006
    {
3007
        $record      = 0x0081;   // Record identifier
3008
        $length      = 0x0002;   // Bytes to follow
1604 raphael 3009
        $grbit       = 0x0000;
3010
 
418 aurelien 3011
        // The only option that is of interest is the flag for fit to page. So we
3012
        // set all the options in one go.
3013
        //
1604 raphael 3014
        /*if ($this->_fit_page) {
418 aurelien 3015
            $grbit = 0x05c1;
1604 raphael 3016
        } else {
418 aurelien 3017
            $grbit = 0x04c1;
1604 raphael 3018
        }*/
3019
        // Set the option flags
3020
        $grbit |= 0x0001;                           // Auto page breaks visible
3021
        if ($this->_outline_style) {
3022
            $grbit |= 0x0020; // Auto outline styles
418 aurelien 3023
        }
1604 raphael 3024
        if ($this->_outline_below) {
3025
            $grbit |= 0x0040; // Outline summary below
3026
        }
3027
        if ($this->_outline_right) {
3028
            $grbit |= 0x0080; // Outline summary right
3029
        }
3030
        if ($this->_fit_page) {
3031
            $grbit |= 0x0100; // Page setup fit to page
3032
        }
3033
        if ($this->_outline_on) {
3034
            $grbit |= 0x0400; // Outline symbols displayed
3035
        }
3036
 
3037
        $header = pack("vv", $record, $length);
3038
        $data   = pack("v",  $grbit);
3039
        $this->_prepend($header . $data);
418 aurelien 3040
    }
1604 raphael 3041
 
418 aurelien 3042
    /**
3043
    * Write the HORIZONTALPAGEBREAKS BIFF record.
3044
    *
3045
    * @access private
3046
    */
3047
    function _storeHbreak()
3048
    {
3049
        // Return if the user hasn't specified pagebreaks
3050
        if (empty($this->_hbreaks)) {
3051
            return;
3052
        }
1604 raphael 3053
 
418 aurelien 3054
        // Sort and filter array of page breaks
3055
        $breaks = $this->_hbreaks;
1604 raphael 3056
        sort($breaks, SORT_NUMERIC);
418 aurelien 3057
        if ($breaks[0] == 0) { // don't use first break if it's 0
3058
            array_shift($breaks);
3059
        }
1604 raphael 3060
 
418 aurelien 3061
        $record  = 0x001b;               // Record identifier
3062
        $cbrk    = count($breaks);       // Number of page breaks
1604 raphael 3063
        if ($this->_BIFF_version == 0x0600) {
3064
            $length  = 2 + 6*$cbrk;      // Bytes to follow
3065
        } else {
3066
            $length  = 2 + 2*$cbrk;      // Bytes to follow
3067
        }
3068
 
418 aurelien 3069
        $header  = pack("vv", $record, $length);
3070
        $data    = pack("v",  $cbrk);
1604 raphael 3071
 
418 aurelien 3072
        // Append each page break
1604 raphael 3073
        foreach ($breaks as $break) {
3074
            if ($this->_BIFF_version == 0x0600) {
3075
                $data .= pack("vvv", $break, 0x0000, 0x00ff);
3076
            } else {
3077
                $data .= pack("v", $break);
3078
            }
418 aurelien 3079
        }
1604 raphael 3080
 
418 aurelien 3081
        $this->_prepend($header.$data);
3082
    }
1604 raphael 3083
 
3084
 
418 aurelien 3085
    /**
3086
    * Write the VERTICALPAGEBREAKS BIFF record.
3087
    *
3088
    * @access private
3089
    */
3090
    function _storeVbreak()
3091
    {
3092
        // Return if the user hasn't specified pagebreaks
3093
        if (empty($this->_vbreaks)) {
3094
            return;
3095
        }
1604 raphael 3096
 
418 aurelien 3097
        // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
3098
        // It is slightly higher in Excel 97/200, approx. 1026
3099
        $breaks = array_slice($this->_vbreaks,0,1000);
1604 raphael 3100
 
418 aurelien 3101
        // Sort and filter array of page breaks
1604 raphael 3102
        sort($breaks, SORT_NUMERIC);
418 aurelien 3103
        if ($breaks[0] == 0) { // don't use first break if it's 0
3104
            array_shift($breaks);
3105
        }
1604 raphael 3106
 
418 aurelien 3107
        $record  = 0x001a;               // Record identifier
3108
        $cbrk    = count($breaks);       // Number of page breaks
1604 raphael 3109
        if ($this->_BIFF_version == 0x0600) {
3110
            $length  = 2 + 6*$cbrk;      // Bytes to follow
3111
        } else {
3112
            $length  = 2 + 2*$cbrk;      // Bytes to follow
3113
        }
3114
 
418 aurelien 3115
        $header  = pack("vv",  $record, $length);
3116
        $data    = pack("v",   $cbrk);
1604 raphael 3117
 
418 aurelien 3118
        // Append each page break
3119
        foreach ($breaks as $break) {
1604 raphael 3120
            if ($this->_BIFF_version == 0x0600) {
3121
                $data .= pack("vvv", $break, 0x0000, 0xffff);
3122
            } else {
3123
                $data .= pack("v", $break);
3124
            }
418 aurelien 3125
        }
1604 raphael 3126
 
3127
        $this->_prepend($header . $data);
418 aurelien 3128
    }
1604 raphael 3129
 
418 aurelien 3130
    /**
3131
    * Set the Biff PROTECT record to indicate that the worksheet is protected.
3132
    *
3133
    * @access private
3134
    */
3135
    function _storeProtect()
3136
    {
3137
        // Exit unless sheet protection has been specified
3138
        if ($this->_protect == 0) {
3139
            return;
3140
        }
1604 raphael 3141
 
418 aurelien 3142
        $record      = 0x0012;             // Record identifier
3143
        $length      = 0x0002;             // Bytes to follow
1604 raphael 3144
 
418 aurelien 3145
        $fLock       = $this->_protect;    // Worksheet is protected
1604 raphael 3146
 
418 aurelien 3147
        $header      = pack("vv", $record, $length);
3148
        $data        = pack("v",  $fLock);
1604 raphael 3149
 
418 aurelien 3150
        $this->_prepend($header.$data);
3151
    }
1604 raphael 3152
 
418 aurelien 3153
    /**
3154
    * Write the worksheet PASSWORD record.
3155
    *
3156
    * @access private
3157
    */
3158
    function _storePassword()
3159
    {
3160
        // Exit unless sheet protection and password have been specified
1604 raphael 3161
        if (($this->_protect == 0) || (!isset($this->_password))) {
418 aurelien 3162
            return;
3163
        }
1604 raphael 3164
 
418 aurelien 3165
        $record      = 0x0013;               // Record identifier
3166
        $length      = 0x0002;               // Bytes to follow
1604 raphael 3167
 
418 aurelien 3168
        $wPassword   = $this->_password;     // Encoded password
1604 raphael 3169
 
418 aurelien 3170
        $header      = pack("vv", $record, $length);
3171
        $data        = pack("v",  $wPassword);
1604 raphael 3172
 
3173
        $this->_prepend($header . $data);
418 aurelien 3174
    }
3175
 
1604 raphael 3176
 
418 aurelien 3177
    /**
3178
    * Insert a 24bit bitmap image in a worksheet.
3179
    *
3180
    * @access public
3181
    * @param integer $row     The row we are going to insert the bitmap into
3182
    * @param integer $col     The column we are going to insert the bitmap into
3183
    * @param string  $bitmap  The bitmap filename
3184
    * @param integer $x       The horizontal position (offset) of the image inside the cell.
3185
    * @param integer $y       The vertical position (offset) of the image inside the cell.
3186
    * @param integer $scale_x The horizontal scale
3187
    * @param integer $scale_y The vertical scale
3188
    */
3189
    function insertBitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
3190
    {
3191
        $bitmap_array = $this->_processBitmap($bitmap);
1604 raphael 3192
        if ($this->isError($bitmap_array)) {
418 aurelien 3193
            $this->writeString($row, $col, $bitmap_array->getMessage());
3194
            return;
3195
        }
3196
        list($width, $height, $size, $data) = $bitmap_array; //$this->_processBitmap($bitmap);
1604 raphael 3197
 
418 aurelien 3198
        // Scale the frame of the image.
3199
        $width  *= $scale_x;
3200
        $height *= $scale_y;
1604 raphael 3201
 
418 aurelien 3202
        // Calculate the vertices of the image and write the OBJ record
3203
        $this->_positionImage($col, $row, $x, $y, $width, $height);
1604 raphael 3204
 
418 aurelien 3205
        // Write the IMDATA record to store the bitmap data
3206
        $record      = 0x007f;
3207
        $length      = 8 + $size;
3208
        $cf          = 0x09;
3209
        $env         = 0x01;
3210
        $lcb         = $size;
1604 raphael 3211
 
418 aurelien 3212
        $header      = pack("vvvvV", $record, $length, $cf, $env, $lcb);
3213
        $this->_append($header.$data);
3214
    }
1604 raphael 3215
 
418 aurelien 3216
    /**
3217
    * Calculate the vertices that define the position of the image as required by
3218
    * the OBJ record.
3219
    *
3220
    *         +------------+------------+
3221
    *         |     A      |      B     |
3222
    *   +-----+------------+------------+
3223
    *   |     |(x1,y1)     |            |
3224
    *   |  1  |(A1)._______|______      |
3225
    *   |     |    |              |     |
3226
    *   |     |    |              |     |
3227
    *   +-----+----|    BITMAP    |-----+
3228
    *   |     |    |              |     |
3229
    *   |  2  |    |______________.     |
3230
    *   |     |            |        (B2)|
3231
    *   |     |            |     (x2,y2)|
3232
    *   +---- +------------+------------+
3233
    *
3234
    * Example of a bitmap that covers some of the area from cell A1 to cell B2.
3235
    *
3236
    * Based on the width and height of the bitmap we need to calculate 8 vars:
3237
    *     $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
3238
    * The width and height of the cells are also variable and have to be taken into
3239
    * account.
3240
    * The values of $col_start and $row_start are passed in from the calling
3241
    * function. The values of $col_end and $row_end are calculated by subtracting
3242
    * the width and height of the bitmap from the width and height of the
3243
    * underlying cells.
3244
    * The vertices are expressed as a percentage of the underlying cell width as
3245
    * follows (rhs values are in pixels):
3246
    *
3247
    *       x1 = X / W *1024
3248
    *       y1 = Y / H *256
3249
    *       x2 = (X-1) / W *1024
3250
    *       y2 = (Y-1) / H *256
3251
    *
3252
    *       Where:  X is distance from the left side of the underlying cell
3253
    *               Y is distance from the top of the underlying cell
3254
    *               W is the width of the cell
3255
    *               H is the height of the cell
3256
    *
3257
    * @access private
3258
    * @note  the SDK incorrectly states that the height should be expressed as a
3259
    *        percentage of 1024.
3260
    * @param integer $col_start Col containing upper left corner of object
3261
    * @param integer $row_start Row containing top left corner of object
3262
    * @param integer $x1        Distance to left side of object
3263
    * @param integer $y1        Distance to top of object
3264
    * @param integer $width     Width of image frame
3265
    * @param integer $height    Height of image frame
3266
    */
3267
    function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
3268
    {
3269
        // Initialise end cell to the same as the start cell
3270
        $col_end    = $col_start;  // Col containing lower right corner of object
3271
        $row_end    = $row_start;  // Row containing bottom right corner of object
1604 raphael 3272
 
418 aurelien 3273
        // Zero the specified offset if greater than the cell dimensions
1604 raphael 3274
        if ($x1 >= $this->_sizeCol($col_start)) {
418 aurelien 3275
            $x1 = 0;
3276
        }
1604 raphael 3277
        if ($y1 >= $this->_sizeRow($row_start)) {
418 aurelien 3278
            $y1 = 0;
3279
        }
1604 raphael 3280
 
418 aurelien 3281
        $width      = $width  + $x1 -1;
3282
        $height     = $height + $y1 -1;
1604 raphael 3283
 
418 aurelien 3284
        // Subtract the underlying cell widths to find the end cell of the image
3285
        while ($width >= $this->_sizeCol($col_end)) {
3286
            $width -= $this->_sizeCol($col_end);
3287
            $col_end++;
3288
        }
1604 raphael 3289
 
418 aurelien 3290
        // Subtract the underlying cell heights to find the end cell of the image
3291
        while ($height >= $this->_sizeRow($row_end)) {
3292
            $height -= $this->_sizeRow($row_end);
3293
            $row_end++;
3294
        }
1604 raphael 3295
 
418 aurelien 3296
        // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
3297
        // with zero eight or width.
3298
        //
1604 raphael 3299
        if ($this->_sizeCol($col_start) == 0) {
418 aurelien 3300
            return;
1604 raphael 3301
        }
3302
        if ($this->_sizeCol($col_end)   == 0) {
418 aurelien 3303
            return;
1604 raphael 3304
        }
3305
        if ($this->_sizeRow($row_start) == 0) {
418 aurelien 3306
            return;
1604 raphael 3307
        }
3308
        if ($this->_sizeRow($row_end)   == 0) {
418 aurelien 3309
            return;
1604 raphael 3310
        }
3311
 
418 aurelien 3312
        // Convert the pixel values to the percentage value expected by Excel
3313
        $x1 = $x1     / $this->_sizeCol($col_start)   * 1024;
3314
        $y1 = $y1     / $this->_sizeRow($row_start)   *  256;
3315
        $x2 = $width  / $this->_sizeCol($col_end)     * 1024; // Distance to right side of object
3316
        $y2 = $height / $this->_sizeRow($row_end)     *  256; // Distance to bottom of object
1604 raphael 3317
 
3318
        $this->_storeObjPicture($col_start, $x1,
3319
                                 $row_start, $y1,
3320
                                 $col_end, $x2,
3321
                                 $row_end, $y2);
418 aurelien 3322
    }
1604 raphael 3323
 
418 aurelien 3324
    /**
3325
    * Convert the width of a cell from user's units to pixels. By interpolation
3326
    * the relationship is: y = 7x +5. If the width hasn't been set by the user we
3327
    * use the default value. If the col is hidden we use a value of zero.
3328
    *
3329
    * @access private
1604 raphael 3330
    * @param integer $col The column
418 aurelien 3331
    * @return integer The width in pixels
3332
    */
3333
    function _sizeCol($col)
3334
    {
3335
        // Look up the cell value to see if it has been changed
3336
        if (isset($this->col_sizes[$col])) {
3337
            if ($this->col_sizes[$col] == 0) {
3338
                return(0);
1604 raphael 3339
            } else {
418 aurelien 3340
                return(floor(7 * $this->col_sizes[$col] + 5));
3341
            }
1604 raphael 3342
        } else {
418 aurelien 3343
            return(64);
3344
        }
3345
    }
1604 raphael 3346
 
418 aurelien 3347
    /**
3348
    * Convert the height of a cell from user's units to pixels. By interpolation
3349
    * the relationship is: y = 4/3x. If the height hasn't been set by the user we
3350
    * use the default value. If the row is hidden we use a value of zero. (Not
3351
    * possible to hide row yet).
3352
    *
3353
    * @access private
3354
    * @param integer $row The row
3355
    * @return integer The width in pixels
3356
    */
3357
    function _sizeRow($row)
3358
    {
3359
        // Look up the cell value to see if it has been changed
1604 raphael 3360
        if (isset($this->_row_sizes[$row])) {
3361
            if ($this->_row_sizes[$row] == 0) {
418 aurelien 3362
                return(0);
1604 raphael 3363
            } else {
3364
                return(floor(4/3 * $this->_row_sizes[$row]));
418 aurelien 3365
            }
1604 raphael 3366
        } else {
418 aurelien 3367
            return(17);
3368
        }
3369
    }
1604 raphael 3370
 
418 aurelien 3371
    /**
3372
    * Store the OBJ record that precedes an IMDATA record. This could be generalise
3373
    * to support other Excel objects.
3374
    *
3375
    * @access private
3376
    * @param integer $colL Column containing upper left corner of object
3377
    * @param integer $dxL  Distance from left side of cell
3378
    * @param integer $rwT  Row containing top left corner of object
3379
    * @param integer $dyT  Distance from top of cell
3380
    * @param integer $colR Column containing lower right corner of object
3381
    * @param integer $dxR  Distance from right of cell
3382
    * @param integer $rwB  Row containing bottom right corner of object
3383
    * @param integer $dyB  Distance from bottom of cell
3384
    */
3385
    function _storeObjPicture($colL,$dxL,$rwT,$dyT,$colR,$dxR,$rwB,$dyB)
3386
    {
3387
        $record      = 0x005d;   // Record identifier
3388
        $length      = 0x003c;   // Bytes to follow
1604 raphael 3389
 
418 aurelien 3390
        $cObj        = 0x0001;   // Count of objects in file (set to 1)
3391
        $OT          = 0x0008;   // Object type. 8 = Picture
3392
        $id          = 0x0001;   // Object ID
3393
        $grbit       = 0x0614;   // Option flags
1604 raphael 3394
 
418 aurelien 3395
        $cbMacro     = 0x0000;   // Length of FMLA structure
3396
        $Reserved1   = 0x0000;   // Reserved
3397
        $Reserved2   = 0x0000;   // Reserved
1604 raphael 3398
 
418 aurelien 3399
        $icvBack     = 0x09;     // Background colour
3400
        $icvFore     = 0x09;     // Foreground colour
3401
        $fls         = 0x00;     // Fill pattern
3402
        $fAuto       = 0x00;     // Automatic fill
3403
        $icv         = 0x08;     // Line colour
3404
        $lns         = 0xff;     // Line style
3405
        $lnw         = 0x01;     // Line weight
3406
        $fAutoB      = 0x00;     // Automatic border
3407
        $frs         = 0x0000;   // Frame style
3408
        $cf          = 0x0009;   // Image format, 9 = bitmap
3409
        $Reserved3   = 0x0000;   // Reserved
3410
        $cbPictFmla  = 0x0000;   // Length of FMLA structure
3411
        $Reserved4   = 0x0000;   // Reserved
3412
        $grbit2      = 0x0001;   // Option flags
3413
        $Reserved5   = 0x0000;   // Reserved
1604 raphael 3414
 
3415
 
418 aurelien 3416
        $header      = pack("vv", $record, $length);
3417
        $data        = pack("V", $cObj);
3418
        $data       .= pack("v", $OT);
3419
        $data       .= pack("v", $id);
3420
        $data       .= pack("v", $grbit);
3421
        $data       .= pack("v", $colL);
3422
        $data       .= pack("v", $dxL);
3423
        $data       .= pack("v", $rwT);
3424
        $data       .= pack("v", $dyT);
3425
        $data       .= pack("v", $colR);
3426
        $data       .= pack("v", $dxR);
3427
        $data       .= pack("v", $rwB);
3428
        $data       .= pack("v", $dyB);
3429
        $data       .= pack("v", $cbMacro);
3430
        $data       .= pack("V", $Reserved1);
3431
        $data       .= pack("v", $Reserved2);
3432
        $data       .= pack("C", $icvBack);
3433
        $data       .= pack("C", $icvFore);
3434
        $data       .= pack("C", $fls);
3435
        $data       .= pack("C", $fAuto);
3436
        $data       .= pack("C", $icv);
3437
        $data       .= pack("C", $lns);
3438
        $data       .= pack("C", $lnw);
3439
        $data       .= pack("C", $fAutoB);
3440
        $data       .= pack("v", $frs);
3441
        $data       .= pack("V", $cf);
3442
        $data       .= pack("v", $Reserved3);
3443
        $data       .= pack("v", $cbPictFmla);
3444
        $data       .= pack("v", $Reserved4);
3445
        $data       .= pack("v", $grbit2);
3446
        $data       .= pack("V", $Reserved5);
1604 raphael 3447
 
3448
        $this->_append($header . $data);
418 aurelien 3449
    }
1604 raphael 3450
 
418 aurelien 3451
    /**
3452
    * Convert a 24 bit bitmap into the modified internal format used by Windows.
3453
    * This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the
3454
    * MSDN library.
3455
    *
3456
    * @access private
3457
    * @param string $bitmap The bitmap to process
3458
    * @return array Array with data and properties of the bitmap
3459
    */
3460
    function _processBitmap($bitmap)
3461
    {
3462
        // Open file.
3463
        $bmp_fd = @fopen($bitmap,"rb");
3464
        if (!$bmp_fd) {
1604 raphael 3465
            $this->raiseError("Couldn't import $bitmap");
418 aurelien 3466
        }
1604 raphael 3467
 
418 aurelien 3468
        // Slurp the file into a string.
3469
        $data = fread($bmp_fd, filesize($bitmap));
1604 raphael 3470
 
418 aurelien 3471
        // Check that the file is big enough to be a bitmap.
3472
        if (strlen($data) <= 0x36) {
1604 raphael 3473
            $this->raiseError("$bitmap doesn't contain enough data.\n");
418 aurelien 3474
        }
1604 raphael 3475
 
418 aurelien 3476
        // The first 2 bytes are used to identify the bitmap.
1604 raphael 3477
        $identity = unpack("A2ident", $data);
3478
        if ($identity['ident'] != "BM") {
3479
            $this->raiseError("$bitmap doesn't appear to be a valid bitmap image.\n");
418 aurelien 3480
        }
1604 raphael 3481
 
418 aurelien 3482
        // Remove bitmap data: ID.
3483
        $data = substr($data, 2);
1604 raphael 3484
 
418 aurelien 3485
        // Read and remove the bitmap size. This is more reliable than reading
3486
        // the data size at offset 0x22.
3487
        //
1604 raphael 3488
        $size_array   = unpack("Vsa", substr($data, 0, 4));
3489
        $size   = $size_array['sa'];
418 aurelien 3490
        $data   = substr($data, 4);
3491
        $size  -= 0x36; // Subtract size of bitmap header.
3492
        $size  += 0x0C; // Add size of BIFF header.
1604 raphael 3493
 
418 aurelien 3494
        // Remove bitmap data: reserved, offset, header length.
3495
        $data = substr($data, 12);
1604 raphael 3496
 
418 aurelien 3497
        // Read and remove the bitmap width and height. Verify the sizes.
3498
        $width_and_height = unpack("V2", substr($data, 0, 8));
3499
        $width  = $width_and_height[1];
3500
        $height = $width_and_height[2];
3501
        $data   = substr($data, 8);
1604 raphael 3502
        if ($width > 0xFFFF) {
3503
            $this->raiseError("$bitmap: largest image width supported is 65k.\n");
418 aurelien 3504
        }
1604 raphael 3505
        if ($height > 0xFFFF) {
3506
            $this->raiseError("$bitmap: largest image height supported is 65k.\n");
418 aurelien 3507
        }
1604 raphael 3508
 
418 aurelien 3509
        // Read and remove the bitmap planes and bpp data. Verify them.
3510
        $planes_and_bitcount = unpack("v2", substr($data, 0, 4));
3511
        $data = substr($data, 4);
3512
        if ($planes_and_bitcount[2] != 24) { // Bitcount
1604 raphael 3513
            $this->raiseError("$bitmap isn't a 24bit true color bitmap.\n");
418 aurelien 3514
        }
3515
        if ($planes_and_bitcount[1] != 1) {
1604 raphael 3516
            $this->raiseError("$bitmap: only 1 plane supported in bitmap image.\n");
418 aurelien 3517
        }
1604 raphael 3518
 
418 aurelien 3519
        // Read and remove the bitmap compression. Verify compression.
1604 raphael 3520
        $compression = unpack("Vcomp", substr($data, 0, 4));
418 aurelien 3521
        $data = substr($data, 4);
1604 raphael 3522
 
418 aurelien 3523
        //$compression = 0;
1604 raphael 3524
        if ($compression['comp'] != 0) {
3525
            $this->raiseError("$bitmap: compression not supported in bitmap image.\n");
418 aurelien 3526
        }
1604 raphael 3527
 
418 aurelien 3528
        // Remove bitmap data: data size, hres, vres, colours, imp. colours.
3529
        $data = substr($data, 20);
1604 raphael 3530
 
418 aurelien 3531
        // Add the BITMAPCOREHEADER data
3532
        $header  = pack("Vvvvv", 0x000c, $width, $height, 0x01, 0x18);
3533
        $data    = $header . $data;
1604 raphael 3534
 
418 aurelien 3535
        return (array($width, $height, $size, $data));
3536
    }
1604 raphael 3537
 
418 aurelien 3538
    /**
3539
    * Store the window zoom factor. This should be a reduced fraction but for
3540
    * simplicity we will store all fractions with a numerator of 100.
3541
    *
3542
    * @access private
3543
    */
3544
    function _storeZoom()
3545
    {
3546
        // If scale is 100 we don't need to write a record
3547
        if ($this->_zoom == 100) {
3548
            return;
3549
        }
1604 raphael 3550
 
418 aurelien 3551
        $record      = 0x00A0;               // Record identifier
3552
        $length      = 0x0004;               // Bytes to follow
1604 raphael 3553
 
418 aurelien 3554
        $header      = pack("vv", $record, $length);
3555
        $data        = pack("vv", $this->_zoom, 100);
1604 raphael 3556
        $this->_append($header . $data);
3557
    }
3558
 
3559
    /**
3560
    * FIXME: add comments
3561
    */
3562
    function setValidation($row1, $col1, $row2, $col2, &$validator)
3563
    {
3564
        $this->_dv[] = $validator->_getData() .
3565
                       pack("vvvvv", 1, $row1, $row2, $col1, $col2);
3566
    }
3567
 
3568
    /**
3569
    * Store the DVAL and DV records.
3570
    *
3571
    * @access private
3572
    */
3573
    function _storeDataValidity()
3574
    {
3575
        $record      = 0x01b2;      // Record identifier
3576
        $length      = 0x0012;      // Bytes to follow
3577
 
3578
        $grbit       = 0x0002;      // Prompt box at cell, no cached validity data at DV records
3579
        $horPos      = 0x00000000;  // Horizontal position of prompt box, if fixed position
3580
        $verPos      = 0x00000000;  // Vertical position of prompt box, if fixed position
3581
        $objId       = 0xffffffff;  // Object identifier of drop down arrow object, or -1 if not visible
3582
 
3583
        $header      = pack('vv', $record, $length);
3584
        $data        = pack('vVVVV', $grbit, $horPos, $verPos, $objId,
3585
                                     count($this->_dv));
418 aurelien 3586
        $this->_append($header.$data);
1604 raphael 3587
 
3588
        $record = 0x01be;              // Record identifier
3589
        foreach ($this->_dv as $dv) {
3590
            $length = strlen($dv);      // Bytes to follow
3591
            $header = pack("vv", $record, $length);
3592
            $this->_append($header . $dv);
3593
        }
418 aurelien 3594
    }
3595
}
3596
?>