1 <?php
3 /*
4 * Copyleft 2002 Johann Hanne
5 *
6 * This is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This software is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this software; if not, write to the
18 * Free Software Foundation, Inc., 59 Temple Place,
19 * Suite 330, Boston, MA 02111-1307 USA
20 */
22 /*
23 * This is the Spreadsheet::WriteExcel Perl package ported to PHP
24 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
25 */
27 require_once "class.writeexcel_biffwriter.inc.php";
28 require_once "class.writeexcel_format.inc.php";
29 require_once "class.writeexcel_formula.inc.php";
30 require_once "class.writeexcel_olewriter.inc.php";
32 class writeexcel_workbook extends writeexcel_biffwriter {
34 var $_filename;
35 var $_tmpfilename;
36 var $_parser;
37 var $_tempdir;
38 var $_1904;
39 var $_activesheet;
40 var $_firstsheet;
41 var $_selected;
42 var $_xf_index;
43 var $_fileclosed;
44 var $_biffsize;
45 var $_sheetname;
46 var $_tmp_format;
47 var $_url_format;
48 var $_worksheets;
49 var $_sheetnames;
50 var $_formats;
51 var $_palette;
53 ###############################################################################
54 #
55 # new()
56 #
57 # Constructor. Creates a new Workbook object from a BIFFwriter object.
58 #
59 function writeexcel_workbook($filename) {
61 $this->writeexcel_biffwriter();
63 $tmp_format = new writeexcel_format();
64 $byte_order = $this->_byte_order;
65 $parser = new writeexcel_formula($byte_order);
67 $this->_filename = $filename;
68 $this->_parser = $parser;
69 //? $this->_tempdir = undef;
70 $this->_1904 = 0;
71 $this->_activesheet = 0;
72 $this->_firstsheet = 0;
73 $this->_selected = 0;
74 $this->_xf_index = 16; # 15 style XF's and 1 cell XF.
75 $this->_fileclosed = 0;
76 $this->_biffsize = 0;
77 $this->_sheetname = "Sheet";
78 $this->_tmp_format = $tmp_format;
79 $this->_url_format = false;
80 $this->_worksheets = array();
81 $this->_sheetnames = array();
82 $this->_formats = array();
83 $this->_palette = array();
85 # Add the default format for hyperlinks
86 $this->_url_format = $this->addformat(array('color' => 'blue', 'underline' => 1));
88 # Check for a filename
89 if ($this->_filename == '') {
90 //todo: print error
91 return;
92 }
94 # Try to open the named file and see if it throws any errors.
95 # If the filename is a reference it is assumed that it is a valid
96 # filehandle and ignore
97 #
98 //todo
100 # Set colour palette.
101 $this->set_palette_xl97();
102 }
104 ###############################################################################
105 #
106 # close()
107 #
108 # Calls finalization methods and explicitly close the OLEwriter file
109 # handle.
110 #
111 function close() {
112 # Prevent close() from being called twice.
113 if ($this->_fileclosed) {
114 return;
115 }
117 $this->_store_workbook();
118 $this->_fileclosed = 1;
119 }
121 //PHPport: method DESTROY deleted
123 ###############################################################################
124 #
125 # sheets()
126 #
127 # An accessor for the _worksheets[] array
128 #
129 # Returns: a list of the worksheet objects in a workbook
130 #
131 function sheets() {
132 return $this->_worksheets;
133 }
135 //PHPport: method worksheets deleted:
136 # This method is now deprecated. Use the sheets() method instead.
138 ###############################################################################
139 #
140 # addworksheet($name)
141 #
142 # Add a new worksheet to the Excel workbook.
143 # TODO: Add accessor for $self->{_sheetname} for international Excel versions.
144 #
145 # Returns: reference to a worksheet object
146 #
147 function addworksheet($name="") {
149 # Check that sheetname is <= 31 chars (Excel limit).
150 if (strlen($name) > 31) {
151 trigger_error("Sheetname $name must be <= 31 chars", E_USER_ERROR);
152 }
154 $index = sizeof($this->_worksheets);
155 $sheetname = $this->_sheetname;
157 if ($name == "") {
158 $name = $sheetname . ($index+1);
159 }
161 # Check that the worksheet name doesn't already exist: a fatal Excel error.
162 foreach ($this->_worksheets as $tmp) {
163 if ($name == $tmp->get_name()) {
164 trigger_error("Worksheet '$name' already exists", E_USER_ERROR);
165 }
166 }
168 $worksheet = new writeexcel_worksheet($name, $index, $this->_activesheet,
169 $this->_firstsheet,
170 $this->_url_format, $this->_parser,
171 $this->_tempdir);
173 $this->_worksheets[$index] = $worksheet; # Store ref for iterator
174 $this->_sheetnames[$index] = $name; # Store EXTERNSHEET names
175 $this->_parser->set_ext_sheet($name, $index); # Store names in Formula.pm
176 return $worksheet;
177 }
179 ###############################################################################
180 #
181 # addformat(%properties)
182 #
183 # Add a new format to the Excel workbook. This adds an XF record and
184 # a FONT record. Also, pass any properties to the Format::new().
185 #
186 function addformat($para=false) {
187 if($para===false) {
188 $format = new writeexcel_format($this->_xf_index);
189 } else {
190 $format = new writeexcel_format($this->_xf_index, $para);
191 }
193 $this->_xf_index += 1;
194 # Store format reference
195 $this->_formats[]=$format;
197 return $format;
198 }
200 ###############################################################################
201 #
202 # set_1904()
203 #
204 # Set the date system: 0 = 1900 (the default), 1 = 1904
205 #
206 function set_1904($_1904) {
207 $this->_1904 = $_1904;
208 }
210 ###############################################################################
211 #
212 # get_1904()
213 #
214 # Return the date system: 0 = 1900, 1 = 1904
215 #
216 function get_1904() {
217 return $this->_1904;
218 }
220 ###############################################################################
221 #
222 # set_custom_color()
223 #
224 # Change the RGB components of the elements in the colour palette.
225 #
226 function set_custom_color($index, $red, $green, $blue) {
227 // todo
228 /*
229 # Match a HTML #xxyyzz style parameter
230 if (defined $_[1] and $_[1] =~ /^#(\w\w)(\w\w)(\w\w)/ ) {
231 @_ = ($_[0], hex $1, hex $2, hex $3);
232 }
233 */
235 $aref = $this->_palette;
237 # Check that the colour index is the right range
238 if ($index < 8 or $index > 64) {
239 //todo carp "Color index $index outside range: 8 <= index <= 64";
240 return;
241 }
243 # Check that the colour components are in the right range
244 if ( ($red < 0 || $red > 255) ||
245 ($green < 0 || $green > 255) ||
246 ($blue < 0 || $blue > 255) )
247 {
248 //todo carp "Color component outside range: 0 <= color <= 255";
249 return;
250 }
252 $index -=8; # Adjust colour index (wingless dragonfly)
254 # Set the RGB value
255 $aref[$index] = array($red, $green, $blue, 0);
257 return $index +8;
258 }
260 ###############################################################################
261 #
262 # set_palette_xl97()
263 #
264 # Sets the colour palette to the Excel 97+ default.
265 #
266 function set_palette_xl97() {
267 $this->_palette = array(
268 array(0x00, 0x00, 0x00, 0x00), # 8
269 array(0xff, 0xff, 0xff, 0x00), # 9
270 array(0xff, 0x00, 0x00, 0x00), # 10
271 array(0x00, 0xff, 0x00, 0x00), # 11
272 array(0x00, 0x00, 0xff, 0x00), # 12
273 array(0xff, 0xff, 0x00, 0x00), # 13
274 array(0xff, 0x00, 0xff, 0x00), # 14
275 array(0x00, 0xff, 0xff, 0x00), # 15
276 array(0x80, 0x00, 0x00, 0x00), # 16
277 array(0x00, 0x80, 0x00, 0x00), # 17
278 array(0x00, 0x00, 0x80, 0x00), # 18
279 array(0x80, 0x80, 0x00, 0x00), # 19
280 array(0x80, 0x00, 0x80, 0x00), # 20
281 array(0x00, 0x80, 0x80, 0x00), # 21
282 array(0xc0, 0xc0, 0xc0, 0x00), # 22
283 array(0x80, 0x80, 0x80, 0x00), # 23
284 array(0x99, 0x99, 0xff, 0x00), # 24
285 array(0x99, 0x33, 0x66, 0x00), # 25
286 array(0xff, 0xff, 0xcc, 0x00), # 26
287 array(0xcc, 0xff, 0xff, 0x00), # 27
288 array(0x66, 0x00, 0x66, 0x00), # 28
289 array(0xff, 0x80, 0x80, 0x00), # 29
290 array(0x00, 0x66, 0xcc, 0x00), # 30
291 array(0xcc, 0xcc, 0xff, 0x00), # 31
292 array(0x00, 0x00, 0x80, 0x00), # 32
293 array(0xff, 0x00, 0xff, 0x00), # 33
294 array(0xff, 0xff, 0x00, 0x00), # 34
295 array(0x00, 0xff, 0xff, 0x00), # 35
296 array(0x80, 0x00, 0x80, 0x00), # 36
297 array(0x80, 0x00, 0x00, 0x00), # 37
298 array(0x00, 0x80, 0x80, 0x00), # 38
299 array(0x00, 0x00, 0xff, 0x00), # 39
300 array(0x00, 0xcc, 0xff, 0x00), # 40
301 array(0xcc, 0xff, 0xff, 0x00), # 41
302 array(0xcc, 0xff, 0xcc, 0x00), # 42
303 array(0xff, 0xff, 0x99, 0x00), # 43
304 array(0x99, 0xcc, 0xff, 0x00), # 44
305 array(0xff, 0x99, 0xcc, 0x00), # 45
306 array(0xcc, 0x99, 0xff, 0x00), # 46
307 array(0xff, 0xcc, 0x99, 0x00), # 47
308 array(0x33, 0x66, 0xff, 0x00), # 48
309 array(0x33, 0xcc, 0xcc, 0x00), # 49
310 array(0x99, 0xcc, 0x00, 0x00), # 50
311 array(0xff, 0xcc, 0x00, 0x00), # 51
312 array(0xff, 0x99, 0x00, 0x00), # 52
313 array(0xff, 0x66, 0x00, 0x00), # 53
314 array(0x66, 0x66, 0x99, 0x00), # 54
315 array(0x96, 0x96, 0x96, 0x00), # 55
316 array(0x00, 0x33, 0x66, 0x00), # 56
317 array(0x33, 0x99, 0x66, 0x00), # 57
318 array(0x00, 0x33, 0x00, 0x00), # 58
319 array(0x33, 0x33, 0x00, 0x00), # 59
320 array(0x99, 0x33, 0x00, 0x00), # 60
321 array(0x99, 0x33, 0x66, 0x00), # 61
322 array(0x33, 0x33, 0x99, 0x00), # 62
323 array(0x33, 0x33, 0x33, 0x00), # 63
324 );
326 return 0;
327 }
329 ###############################################################################
330 #
331 # set_palette_xl5()
332 #
333 # Sets the colour palette to the Excel 5 default.
334 #
335 function set_palette_xl5() {
336 $this->_palette = array(
337 array(0x00, 0x00, 0x00, 0x00), # 8
338 array(0xff, 0xff, 0xff, 0x00), # 9
339 array(0xff, 0x00, 0x00, 0x00), # 10
340 array(0x00, 0xff, 0x00, 0x00), # 11
341 array(0x00, 0x00, 0xff, 0x00), # 12
342 array(0xff, 0xff, 0x00, 0x00), # 13
343 array(0xff, 0x00, 0xff, 0x00), # 14
344 array(0x00, 0xff, 0xff, 0x00), # 15
345 array(0x80, 0x00, 0x00, 0x00), # 16
346 array(0x00, 0x80, 0x00, 0x00), # 17
347 array(0x00, 0x00, 0x80, 0x00), # 18
348 array(0x80, 0x80, 0x00, 0x00), # 19
349 array(0x80, 0x00, 0x80, 0x00), # 20
350 array(0x00, 0x80, 0x80, 0x00), # 21
351 array(0xc0, 0xc0, 0xc0, 0x00), # 22
352 array(0x80, 0x80, 0x80, 0x00), # 23
353 array(0x80, 0x80, 0xff, 0x00), # 24
354 array(0x80, 0x20, 0x60, 0x00), # 25
355 array(0xff, 0xff, 0xc0, 0x00), # 26
356 array(0xa0, 0xe0, 0xe0, 0x00), # 27
357 array(0x60, 0x00, 0x80, 0x00), # 28
358 array(0xff, 0x80, 0x80, 0x00), # 29
359 array(0x00, 0x80, 0xc0, 0x00), # 30
360 array(0xc0, 0xc0, 0xff, 0x00), # 31
361 array(0x00, 0x00, 0x80, 0x00), # 32
362 array(0xff, 0x00, 0xff, 0x00), # 33
363 array(0xff, 0xff, 0x00, 0x00), # 34
364 array(0x00, 0xff, 0xff, 0x00), # 35
365 array(0x80, 0x00, 0x80, 0x00), # 36
366 array(0x80, 0x00, 0x00, 0x00), # 37
367 array(0x00, 0x80, 0x80, 0x00), # 38
368 array(0x00, 0x00, 0xff, 0x00), # 39
369 array(0x00, 0xcf, 0xff, 0x00), # 40
370 array(0x69, 0xff, 0xff, 0x00), # 41
371 array(0xe0, 0xff, 0xe0, 0x00), # 42
372 array(0xff, 0xff, 0x80, 0x00), # 43
373 array(0xa6, 0xca, 0xf0, 0x00), # 44
374 array(0xdd, 0x9c, 0xb3, 0x00), # 45
375 array(0xb3, 0x8f, 0xee, 0x00), # 46
376 array(0xe3, 0xe3, 0xe3, 0x00), # 47
377 array(0x2a, 0x6f, 0xf9, 0x00), # 48
378 array(0x3f, 0xb8, 0xcd, 0x00), # 49
379 array(0x48, 0x84, 0x36, 0x00), # 50
380 array(0x95, 0x8c, 0x41, 0x00), # 51
381 array(0x8e, 0x5e, 0x42, 0x00), # 52
382 array(0xa0, 0x62, 0x7a, 0x00), # 53
383 array(0x62, 0x4f, 0xac, 0x00), # 54
384 array(0x96, 0x96, 0x96, 0x00), # 55
385 array(0x1d, 0x2f, 0xbe, 0x00), # 56
386 array(0x28, 0x66, 0x76, 0x00), # 57
387 array(0x00, 0x45, 0x00, 0x00), # 58
388 array(0x45, 0x3e, 0x01, 0x00), # 59
389 array(0x6a, 0x28, 0x13, 0x00), # 60
390 array(0x85, 0x39, 0x6a, 0x00), # 61
391 array(0x4a, 0x32, 0x85, 0x00), # 62
392 array(0x42, 0x42, 0x42, 0x00), # 63
393 );
395 return 0;
396 }
398 ###############################################################################
399 #
400 # set_tempdir()
401 #
402 # Change the default temp directory used by _initialize() in Worksheet.pm.
403 #
404 function set_tempdir($tempdir) {
405 //todo
406 /*
407 croak "$_[0] is not a valid directory" unless -d $_[0];
408 croak "set_tempdir must be called before addworksheet" if $self->sheets();
409 */
411 $this->_tempdir = $tempdir;
412 }
414 ###############################################################################
415 #
416 # _store_workbook()
417 #
418 # Assemble worksheets into a workbook and send the BIFF data to an OLE
419 # storage.
420 #
421 function _store_workbook() {
423 # Ensure that at least one worksheet has been selected.
424 if ($this->_activesheet == 0) {
425 $this->_worksheets[0]->_selected = 1;
426 }
428 # Calculate the number of selected worksheet tabs and call the finalization
429 # methods for each worksheet
430 for ($c=0;$c<sizeof($this->_worksheets);$c++) {
431 $sheet=$this->_worksheets[$c];
432 if ($sheet->_selected) {
433 $this->_selected++;
435 }
436 $sheet->_close($this->_sheetnames);
437 }
439 # Add Workbook globals
440 $this->_store_bof(0x0005);
442 $this->_store_externs(); # For print area and repeat rows
444 $this->_store_names(); # For print area and repeat rows
446 $this->_store_window1();
448 $this->_store_1904();
450 $this->_store_all_fonts();
452 $this->_store_all_num_formats();
454 $this->_store_all_xfs();
456 $this->_store_all_styles();
458 $this->_store_palette();
460 $this->_calc_sheet_offsets();
462 # Add BOUNDSHEET records
463 for ($c=0;$c<sizeof($this->_worksheets);$c++) {
464 $sheet=$this->_worksheets[$c];
465 $this->_store_boundsheet($sheet->_name, $sheet->_offset);
466 }
468 # End Workbook globals
469 $this->_store_eof();
471 # Store the workbook in an OLE container
472 $this->_store_OLE_file();
473 }
475 ###############################################################################
476 #
477 # _store_OLE_file()
478 #
479 # Store the workbook in an OLE container if the total size of the workbook data
480 # is less than ~ 7MB.
481 #
482 function _store_OLE_file() {
483 ## ABR
484 if ($this->_tmpfilename != '') {
485 $OLE = new writeexcel_olewriter(CACHE_DIR.'/tmp/'.$this->_tmpfilename);
486 $OLE->_OLEtmpfilename = CACHE_DIR.$this->_tmpfilename;
487 } else {
488 $OLE = new writeexcel_olewriter($this->_filename);
489 $OLE->_OLEtmpfilename = '';
490 };
491 ## END ABR
493 # Write Worksheet data if data <~ 7MB
494 if ($OLE->set_size($this->_biffsize)) {
495 $OLE->write_header();
496 $OLE->write($this->_data);
498 for ($c=0;$c<sizeof($this->_worksheets);$c++) {
499 $sheet=$this->_worksheets[$c];
500 while ($tmp = $sheet->get_data()) {
501 $OLE->write($tmp);
502 }
503 }
504 }
506 $OLE->close();
507 }
509 ###############################################################################
510 #
511 # _calc_sheet_offsets()
512 #
513 # Calculate offsets for Worksheet BOF records.
514 #
515 function _calc_sheet_offsets() {
517 $BOF = 11;
518 $EOF = 4;
519 $offset = $this->_datasize;
521 foreach ($this->_worksheets as $sheet) {
522 $offset += $BOF + strlen($sheet->_name);
523 }
525 $offset += $EOF;
527 for ($c=0;$c<sizeof($this->_worksheets);$c++) {
528 $sheet=$this->_worksheets[$c];
529 $sheet->_offset = $offset;
530 $offset += $sheet->_datasize;
531 }
533 $this->_biffsize = $offset;
534 }
536 ###############################################################################
537 #
538 # _store_all_fonts()
539 #
540 # Store the Excel FONT records.
541 #
542 function _store_all_fonts() {
543 # _tmp_format is added by new(). We use this to write the default XF's
544 $format = $this->_tmp_format;
545 $font = $format->get_font();
547 # Note: Fonts are 0-indexed. According to the SDK there is no index 4,
548 # so the following fonts are 0, 1, 2, 3, 5
549 #
550 for ($c=0;$c<5;$c++) {
551 $this->_append($font);
552 }
554 # Iterate through the XF objects and write a FONT record if it isn't the
555 # same as the default FONT and if it hasn't already been used.
556 #
557 $index = 6; # The first user defined FONT
559 $key = $format->get_font_key(); # The default font from _tmp_format
560 $fonts[$key] = 0; # Index of the default font
562 for ($c=0;$c<sizeof($this->_formats);$c++) {
563 $format=$this->_formats[$c];
565 $key = $format->get_font_key();
567 if (isset($fonts[$key])) {
568 # FONT has already been used
569 $format->_font_index = $fonts[$key];
570 } else {
571 # Add a new FONT record
572 $fonts[$key] = $index;
573 $format->_font_index = $index;
574 $index++;
575 $font = $format->get_font();
576 $this->_append($font);
577 }
578 }
579 }
581 ###############################################################################
582 #
583 # _store_all_num_formats()
584 #
585 # Store user defined numerical formats i.e. FORMAT records
586 #
587 function _store_all_num_formats() {
589 # Leaning num_format syndrome
590 $num_formats_list=array();
591 $index = 164;
593 # Iterate through the XF objects and write a FORMAT record if it isn't a
594 # built-in format type and if the FORMAT string hasn't already been used.
595 #
597 for ($c=0;$c<sizeof($this->_formats);$c++) {
598 $format=$this->_formats[$c];
600 $num_format = $format->_num_format;
602 # Check if $num_format is an index to a built-in format.
603 # Also check for a string of zeros, which is a valid format string
604 # but would evaluate to zero.
605 #
606 if (!preg_match('/^0+\d/', $num_format)) {
607 if (preg_match('/^\d+$/', $num_format)) {
608 # built-in
609 continue;
610 }
611 }
613 if (isset($num_formats[$num_format])) {
614 # FORMAT has already been used
615 $format->_num_format = $num_formats[$num_format];
616 } else {
617 # Add a new FORMAT
618 $num_formats[$num_format] = $index;
619 $format->_num_format = $index;
620 array_push($num_formats_list, $num_format);
621 $index++;
622 }
623 }
625 # Write the new FORMAT records starting from 0xA4
626 $index = 164;
627 foreach ($num_formats_list as $num_format) {
628 $this->_store_num_format($num_format, $index);
629 $index++;
630 }
631 }
633 ###############################################################################
634 #
635 # _store_all_xfs()
636 #
637 # Write all XF records.
638 #
639 function _store_all_xfs() {
640 # _tmp_format is added by new(). We use this to write the default XF's
641 # The default font index is 0
642 #
643 $format = $this->_tmp_format;
644 $xf;
646 for ($c=0;$c<15;$c++) {
647 $xf = $format->get_xf('style'); # Style XF
648 $this->_append($xf);
649 }
651 $xf = $format->get_xf('cell'); # Cell XF
652 $this->_append($xf);
654 # User defined XFs
655 foreach ($this->_formats as $format) {
656 $xf = $format->get_xf('cell');
657 $this->_append($xf);
658 }
659 }
661 ###############################################################################
662 #
663 # _store_all_styles()
664 #
665 # Write all STYLE records.
666 #
667 function _store_all_styles() {
668 $this->_store_style();
669 }
671 ###############################################################################
672 #
673 # _store_externs()
674 #
675 # Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
676 # the NAME records.
677 #
678 function _store_externs() {
680 # Create EXTERNCOUNT with number of worksheets
681 $this->_store_externcount(sizeof($this->_worksheets));
683 # Create EXTERNSHEET for each worksheet
684 foreach ($this->_sheetnames as $sheetname) {
685 $this->_store_externsheet($sheetname);
686 }
687 }
689 ###############################################################################
690 #
691 # _store_names()
692 #
693 # Write the NAME record to define the print area and the repeat rows and cols.
694 #
695 function _store_names() {
697 # Create the print area NAME records
698 foreach ($this->_worksheets as $worksheet) {
699 # Write a Name record if the print area has been defined
700 if ($worksheet->_print_rowmin!==false) {
701 $this->_store_name_short(
702 $worksheet->_index,
703 0x06, # NAME type
704 $worksheet->_print_rowmin,
705 $worksheet->_print_rowmax,
706 $worksheet->_print_colmin,
707 $worksheet->_print_colmax
708 );
709 }
710 }
712 # Create the print title NAME records
713 foreach ($this->_worksheets as $worksheet) {
715 $rowmin = $worksheet->_title_rowmin;
716 $rowmax = $worksheet->_title_rowmax;
717 $colmin = $worksheet->_title_colmin;
718 $colmax = $worksheet->_title_colmax;
720 # Determine if row + col, row, col or nothing has been defined
721 # and write the appropriate record
722 #
723 if ($rowmin!==false && $colmin!==false) {
724 # Row and column titles have been defined.
725 # Row title has been defined.
726 $this->_store_name_long(
727 $worksheet->_index,
728 0x07, # NAME type
729 $rowmin,
730 $rowmax,
731 $colmin,
732 $colmax
733 );
734 } elseif ($rowmin!==false) {
735 # Row title has been defined.
736 $this->_store_name_short(
737 $worksheet->_index,
738 0x07, # NAME type
739 $rowmin,
740 $rowmax,
741 0x00,
742 0xff
743 );
744 } elseif ($colmin!==false) {
745 # Column title has been defined.
746 $this->_store_name_short(
747 $worksheet->_index,
748 0x07, # NAME type
749 0x0000,
750 0x3fff,
751 $colmin,
752 $colmax
753 );
754 } else {
755 # Print title hasn't been defined.
756 }
757 }
758 }
760 ###############################################################################
761 ###############################################################################
762 #
763 # BIFF RECORDS
764 #
766 ###############################################################################
767 #
768 # _store_window1()
769 #
770 # Write Excel BIFF WINDOW1 record.
771 #
772 function _store_window1() {
774 $record = 0x003D; # Record identifier
775 $length = 0x0012; # Number of bytes to follow
777 $xWn = 0x0000; # Horizontal position of window
778 $yWn = 0x0000; # Vertical position of window
779 $dxWn = 0x25BC; # Width of window
780 $dyWn = 0x1572; # Height of window
782 $grbit = 0x0038; # Option flags
783 $ctabsel = $this->_selected; # Number of workbook tabs selected
784 $wTabRatio = 0x0258; # Tab to scrollbar ratio
786 $itabFirst = $this->_firstsheet; # 1st displayed worksheet
787 $itabCur = $this->_activesheet; # Active worksheet
789 $header = pack("vv", $record, $length);
790 $data = pack("vvvvvvvvv", $xWn, $yWn, $dxWn, $dyWn,
791 $grbit,
792 $itabCur, $itabFirst,
793 $ctabsel, $wTabRatio);
795 $this->_append($header . $data);
796 }
798 ###############################################################################
799 #
800 # _store_boundsheet()
801 #
802 # Writes Excel BIFF BOUNDSHEET record.
803 #
804 function _store_boundsheet($sheetname, $offset) {
805 $record = 0x0085; # Record identifier
806 $length = 0x07 + strlen($sheetname); # Number of bytes to follow
808 //$sheetname = $_[0]; # Worksheet name
809 //$offset = $_[1]; # Location of worksheet BOF
810 $grbit = 0x0000; # Sheet identifier
811 $cch = strlen($sheetname); # Length of sheet name
813 $header = pack("vv", $record, $length);
814 $data = pack("VvC", $offset, $grbit, $cch);
816 $this->_append($header . $data . $sheetname);
817 }
819 ###############################################################################
820 #
821 # _store_style()
822 #
823 # Write Excel BIFF STYLE records.
824 #
825 function _store_style() {
826 $record = 0x0293; # Record identifier
827 $length = 0x0004; # Bytes to follow
829 $ixfe = 0x8000; # Index to style XF
830 $BuiltIn = 0x00; # Built-in style
831 $iLevel = 0xff; # Outline style level
833 $header = pack("vv", $record, $length);
834 $data = pack("vCC", $ixfe, $BuiltIn, $iLevel);
836 $this->_append($header . $data);
837 }
839 ###############################################################################
840 #
841 # _store_num_format()
842 #
843 # Writes Excel FORMAT record for non "built-in" numerical formats.
844 #
845 function _store_num_format($num_format, $index) {
846 $record = 0x041E; # Record identifier
847 $length = 0x03 + strlen($num_format); # Number of bytes to follow
849 $format = $num_format; # Custom format string
850 $ifmt = $index; # Format index code
851 $cch = strlen($format); # Length of format string
853 $header = pack("vv", $record, $length);
854 $data = pack("vC", $ifmt, $cch);
856 $this->_append($header . $data . $format);
857 }
859 ###############################################################################
860 #
861 # _store_1904()
862 #
863 # Write Excel 1904 record to indicate the date system in use.
864 #
865 function _store_1904() {
866 $record = 0x0022; # Record identifier
867 $length = 0x0002; # Bytes to follow
869 $f1904 = $this->_1904; # Flag for 1904 date system
871 $header = pack("vv", $record, $length);
872 $data = pack("v", $f1904);
874 $this->_append($header . $data);
875 }
877 ###############################################################################
878 #
879 # _store_externcount($count)
880 #
881 # Write BIFF record EXTERNCOUNT to indicate the number of external sheet
882 # references in the workbook.
883 #
884 # Excel only stores references to external sheets that are used in NAME.
885 # The workbook NAME record is required to define the print area and the repeat
886 # rows and columns.
887 #
888 # A similar method is used in Worksheet.pm for a slightly different purpose.
889 #
890 function _store_externcount($par0) {
891 $record = 0x0016; # Record identifier
892 $length = 0x0002; # Number of bytes to follow
894 $cxals = $par0; # Number of external references
896 $header = pack("vv", $record, $length);
897 $data = pack("v", $cxals);
899 $this->_append($header . $data);
900 }
902 ###############################################################################
903 #
904 # _store_externsheet($sheetname)
905 #
906 #
907 # Writes the Excel BIFF EXTERNSHEET record. These references are used by
908 # formulas. NAME record is required to define the print area and the repeat
909 # rows and columns.
910 #
911 # A similar method is used in Worksheet.pm for a slightly different purpose.
912 #
913 function _store_externsheet($par0) {
914 $record = 0x0017; # Record identifier
915 $length = 0x02 + strlen($par0); # Number of bytes to follow
917 $sheetname = $par0; # Worksheet name
918 $cch = strlen($sheetname); # Length of sheet name
919 $rgch = 0x03; # Filename encoding
921 $header = pack("vv", $record, $length);
922 $data = pack("CC", $cch, $rgch);
924 $this->_append($header . $data . $sheetname);
925 }
927 ###############################################################################
928 #
929 # _store_name_short()
930 #
931 #
932 # Store the NAME record in the short format that is used for storing the print
933 # area, repeat rows only and repeat columns only.
934 #
935 function _store_name_short($par0, $par1, $par2, $par3, $par4, $par5) {
936 $record = 0x0018; # Record identifier
937 $length = 0x0024; # Number of bytes to follow
939 $index = $par0; # Sheet index
940 $type = $par1;
942 $grbit = 0x0020; # Option flags
943 $chKey = 0x00; # Keyboard shortcut
944 $cch = 0x01; # Length of text name
945 $cce = 0x0015; # Length of text definition
946 $ixals = $index +1; # Sheet index
947 $itab = $ixals; # Equal to ixals
948 $cchCustMenu = 0x00; # Length of cust menu text
949 $cchDescription = 0x00; # Length of description text
950 $cchHelptopic = 0x00; # Length of help topic text
951 $cchStatustext = 0x00; # Length of status bar text
952 $rgch = $type; # Built-in name type
954 $unknown03 = 0x3b;
955 $unknown04 = 0xffff-$index;
956 $unknown05 = 0x0000;
957 $unknown06 = 0x0000;
958 $unknown07 = 0x1087;
959 $unknown08 = 0x8005;
961 $rowmin = $par2; # Start row
962 $rowmax = $par3; # End row
963 $colmin = $par4; # Start column
964 $colmax = $par5; # end column
966 $header = pack("vv", $record, $length);
967 $data = pack("v", $grbit);
968 $data .= pack("C", $chKey);
969 $data .= pack("C", $cch);
970 $data .= pack("v", $cce);
971 $data .= pack("v", $ixals);
972 $data .= pack("v", $itab);
973 $data .= pack("C", $cchCustMenu);
974 $data .= pack("C", $cchDescription);
975 $data .= pack("C", $cchHelptopic);
976 $data .= pack("C", $cchStatustext);
977 $data .= pack("C", $rgch);
978 $data .= pack("C", $unknown03);
979 $data .= pack("v", $unknown04);
980 $data .= pack("v", $unknown05);
981 $data .= pack("v", $unknown06);
982 $data .= pack("v", $unknown07);
983 $data .= pack("v", $unknown08);
984 $data .= pack("v", $index);
985 $data .= pack("v", $index);
986 $data .= pack("v", $rowmin);
987 $data .= pack("v", $rowmax);
988 $data .= pack("C", $colmin);
989 $data .= pack("C", $colmax);
991 $this->_append($header . $data);
992 }
994 ###############################################################################
995 #
996 # _store_name_long()
997 #
998 #
999 # Store the NAME record in the long format that is used for storing the repeat
1000 # rows and columns when both are specified. This share a lot of code with
1001 # _store_name_short() but we use a separate method to keep the code clean.
1002 # Code abstraction for reuse can be carried too far, and I should know. ;-)
1003 #
1004 function _store_name_long($par0, $par1, $par2, $par3, $par4, $par5) {
1005 $record = 0x0018; # Record identifier
1006 $length = 0x003d; # Number of bytes to follow
1008 $index = $par0; # Sheet index
1009 $type = $par1;
1011 $grbit = 0x0020; # Option flags
1012 $chKey = 0x00; # Keyboard shortcut
1013 $cch = 0x01; # Length of text name
1014 $cce = 0x002e; # Length of text definition
1015 $ixals = $index +1; # Sheet index
1016 $itab = $ixals; # Equal to ixals
1017 $cchCustMenu = 0x00; # Length of cust menu text
1018 $cchDescription = 0x00; # Length of description text
1019 $cchHelptopic = 0x00; # Length of help topic text
1020 $cchStatustext = 0x00; # Length of status bar text
1021 $rgch = $type; # Built-in name type
1023 $unknown01 = 0x29;
1024 $unknown02 = 0x002b;
1025 $unknown03 = 0x3b;
1026 $unknown04 = 0xffff-$index;
1027 $unknown05 = 0x0000;
1028 $unknown06 = 0x0000;
1029 $unknown07 = 0x1087;
1030 $unknown08 = 0x8008;
1032 $rowmin = $par2; # Start row
1033 $rowmax = $par3; # End row
1034 $colmin = $par4; # Start column
1035 $colmax = $par5; # end column
1037 $header = pack("vv", $record, $length);
1038 $data = pack("v", $grbit);
1039 $data .= pack("C", $chKey);
1040 $data .= pack("C", $cch);
1041 $data .= pack("v", $cce);
1042 $data .= pack("v", $ixals);
1043 $data .= pack("v", $itab);
1044 $data .= pack("C", $cchCustMenu);
1045 $data .= pack("C", $cchDescription);
1046 $data .= pack("C", $cchHelptopic);
1047 $data .= pack("C", $cchStatustext);
1048 $data .= pack("C", $rgch);
1049 $data .= pack("C", $unknown01);
1050 $data .= pack("v", $unknown02);
1051 # Column definition
1052 $data .= pack("C", $unknown03);
1053 $data .= pack("v", $unknown04);
1054 $data .= pack("v", $unknown05);
1055 $data .= pack("v", $unknown06);
1056 $data .= pack("v", $unknown07);
1057 $data .= pack("v", $unknown08);
1058 $data .= pack("v", $index);
1059 $data .= pack("v", $index);
1060 $data .= pack("v", 0x0000);
1061 $data .= pack("v", 0x3fff);
1062 $data .= pack("C", $colmin);
1063 $data .= pack("C", $colmax);
1064 # Row definition
1065 $data .= pack("C", $unknown03);
1066 $data .= pack("v", $unknown04);
1067 $data .= pack("v", $unknown05);
1068 $data .= pack("v", $unknown06);
1069 $data .= pack("v", $unknown07);
1070 $data .= pack("v", $unknown08);
1071 $data .= pack("v", $index);
1072 $data .= pack("v", $index);
1073 $data .= pack("v", $rowmin);
1074 $data .= pack("v", $rowmax);
1075 $data .= pack("C", 0x00);
1076 $data .= pack("C", 0xff);
1077 # End of data
1078 $data .= pack("C", 0x10);
1080 $this->_append($header . $data);
1081 }
1083 ###############################################################################
1084 #
1085 # _store_palette()
1086 #
1087 # Stores the PALETTE biff record.
1088 #
1089 function _store_palette() {
1090 $aref = $this->_palette;
1092 $record = 0x0092; # Record identifier
1093 $length = 2 + 4 * sizeof($aref); # Number of bytes to follow
1094 $ccv = sizeof($aref); # Number of RGB values to follow
1095 //$data; # The RGB data
1097 # Pack the RGB data
1098 foreach($aref as $dat) {
1099 $data .= call_user_func_array('pack', array_merge(array("CCCC"), $dat));
1100 }
1102 $header = pack("vvv", $record, $length, $ccv);
1104 $this->_append($header . $data);
1105 }
1107 }
1109 ?>