Gitiles
Code Review Sign In
192.168.1.100 / T800_MD_V1.6 / c33b3079c38f91561c2f84f1ebed20ad8c60532a / . / mcu / tools / perl / Spreadsheet / ParseExcel.pm
blob: 57554aaf339956920c4541a94237545e922cbbfe [file] [log] [blame]
yu.dongc33b3072024-08-21 23:14:49 -0700[diff] [blame^]1package Spreadsheet::ParseExcel;
2
3##############################################################################
4#
5# Spreadsheet::ParseExcel - Extract information from an Excel file.
6#
7# Copyright 2000-2008, Takanori Kawai
8#
9# perltidy with standard settings.
10#
11# Documentation after __END__
12#
13
14use strict;
15use warnings;
16use 5.008;
17
18use OLE::Storage_Lite;
19use IO::File;
20use Config;
21
22use Crypt::RC4;
23use Digest::Perl::MD5;
24
25our $VERSION = '0.59';
26
27use Spreadsheet::ParseExcel::Workbook;
28use Spreadsheet::ParseExcel::Worksheet;
29use Spreadsheet::ParseExcel::Font;
30use Spreadsheet::ParseExcel::Format;
31use Spreadsheet::ParseExcel::Cell;
32use Spreadsheet::ParseExcel::FmtDefault;
33
34my @aColor = (
35 '000000', # 0x00
36 'FFFFFF', 'FFFFFF', 'FFFFFF', 'FFFFFF',
37 'FFFFFF', 'FFFFFF', 'FFFFFF', 'FFFFFF', # 0x08
38 'FFFFFF', 'FF0000', '00FF00', '0000FF',
39 'FFFF00', 'FF00FF', '00FFFF', '800000', # 0x10
40 '008000', '000080', '808000', '800080',
41 '008080', 'C0C0C0', '808080', '9999FF', # 0x18
42 '993366', 'FFFFCC', 'CCFFFF', '660066',
43 'FF8080', '0066CC', 'CCCCFF', '000080', # 0x20
44 'FF00FF', 'FFFF00', '00FFFF', '800080',
45 '800000', '008080', '0000FF', '00CCFF', # 0x28
46 'CCFFFF', 'CCFFCC', 'FFFF99', '99CCFF',
47 'FF99CC', 'CC99FF', 'FFCC99', '3366FF', # 0x30
48 '33CCCC', '99CC00', 'FFCC00', 'FF9900',
49 'FF6600', '666699', '969696', '003366', # 0x38
50 '339966', '003300', '333300', '993300',
51 '993366', '333399', '333333', 'FFFFFF' # 0x40
52);
53use constant verExcel95 => 0x500;
54use constant verExcel97 => 0x600;
55use constant verBIFF2 => 0x00;
56use constant verBIFF3 => 0x02;
57use constant verBIFF4 => 0x04;
58use constant verBIFF5 => 0x08;
59use constant verBIFF8 => 0x18;
60
61use constant MS_BIFF_CRYPTO_NONE => 0;
62use constant MS_BIFF_CRYPTO_XOR => 1;
63use constant MS_BIFF_CRYPTO_RC4 => 2;
64
65use constant sizeof_BIFF_8_FILEPASS => ( 6 + 3 * 16 );
66
67use constant REKEY_BLOCK => 0x400;
68
69# Error code for some of the common parsing errors.
70use constant ErrorNone => 0;
71use constant ErrorNoFile => 1;
72use constant ErrorNoExcelData => 2;
73use constant ErrorFileEncrypted => 3;
74
75our %error_strings = (
76 ErrorNone, '', # 0
77 ErrorNoFile, 'File not found', # 1
78 ErrorNoExcelData, 'No Excel data found in file', # 2
79 ErrorFileEncrypted, 'File is encrypted', # 3
80
81);
82
83
84our %ProcTbl = (
85
86 #Develpers' Kit P291
87 0x14 => \&_subHeader, # Header
88 0x15 => \&_subFooter, # Footer
89 0x18 => \&_subName, # NAME(?)
90 0x1A => \&_subVPageBreak, # Vertical Page Break
91 0x1B => \&_subHPageBreak, # Horizontal Page Break
92 0x22 => \&_subFlg1904, # 1904 Flag
93 0x26 => \&_subMargin, # Left Margin
94 0x27 => \&_subMargin, # Right Margin
95 0x28 => \&_subMargin, # Top Margin
96 0x29 => \&_subMargin, # Bottom Margin
97 0x2A => \&_subPrintHeaders, # Print Headers
98 0x2B => \&_subPrintGridlines, # Print Gridlines
99 0x3C => \&_subContinue, # Continue
100 0x43 => \&_subXF, # XF for Excel < 4.
101 0x0443 => \&_subXF, # XF for Excel = 4.
102
103 #Develpers' Kit P292
104 0x55 => \&_subDefColWidth, # Consider
105 0x5C => \&_subWriteAccess, # WRITEACCESS
106 0x7D => \&_subColInfo, # Colinfo
107 0x7E => \&_subRK, # RK
108 0x81 => \&_subWSBOOL, # WSBOOL
109 0x83 => \&_subHcenter, # HCENTER
110 0x84 => \&_subVcenter, # VCENTER
111 0x85 => \&_subBoundSheet, # BoundSheet
112
113 0x92 => \&_subPalette, # Palette, fgp
114
115 0x99 => \&_subStandardWidth, # Standard Col
116
117 #Develpers' Kit P293
118 0xA1 => \&_subSETUP, # SETUP
119 0xBD => \&_subMulRK, # MULRK
120 0xBE => \&_subMulBlank, # MULBLANK
121 0xD6 => \&_subRString, # RString
122
123 #Develpers' Kit P294
124 0xE0 => \&_subXF, # ExTended Format
125 0xE5 => \&_subMergeArea, # MergeArea (Not Documented)
126 0xFC => \&_subSST, # Shared String Table
127 0xFD => \&_subLabelSST, # Label SST
128
129 #Develpers' Kit P295
130 0x201 => \&_subBlank, # Blank
131
132 0x202 => \&_subInteger, # Integer(Not Documented)
133 0x203 => \&_subNumber, # Number
134 0x204 => \&_subLabel, # Label
135 0x205 => \&_subBoolErr, # BoolErr
136 0x207 => \&_subString, # STRING
137 0x208 => \&_subRow, # RowData
138 0x221 => \&_subArray, # Array (Consider)
139 0x225 => \&_subDefaultRowHeight, # Consider
140
141 0x31 => \&_subFont, # Font
142 0x231 => \&_subFont, # Font
143
144 0x27E => \&_subRK, # RK
145 0x41E => \&_subFormat, # Format
146
147 0x06 => \&_subFormula, # Formula
148 0x406 => \&_subFormula, # Formula
149
150 0x009 => \&_subBOF, # BOF(BIFF2)
151 0x209 => \&_subBOF, # BOF(BIFF3)
152 0x409 => \&_subBOF, # BOF(BIFF4)
153 0x809 => \&_subBOF, # BOF(BIFF5-8)
154);
155
156our $BIGENDIAN;
157our $PREFUNC;
158our $_CellHandler;
159our $_NotSetCell;
160our $_Object;
161our $_use_perlio;
162
163#------------------------------------------------------------------------------
164# Spreadsheet::ParseExcel->new
165#------------------------------------------------------------------------------
166sub new {
167 my ( $class, %hParam ) = @_;
168
169 if ( not defined $_use_perlio ) {
170 if ( exists $Config{useperlio}
171 && defined $Config{useperlio}
172 && $Config{useperlio} eq "define" )
173 {
174 $_use_perlio = 1;
175 }
176 else {
177 $_use_perlio = 0;
178 require IO::Scalar;
179 import IO::Scalar;
180 }
181 }
182
183 # Check ENDIAN(Little: Intel etc. BIG: Sparc etc)
184 $BIGENDIAN =
185 ( defined $hParam{Endian} ) ? $hParam{Endian}
186 : ( unpack( "H08", pack( "L", 2 ) ) eq '02000000' ) ? 0
187 : 1;
188 my $self = {};
189 bless $self, $class;
190
191 $self->{GetContent} = \&_subGetContent;
192
193 if ( $hParam{EventHandlers} ) {
194 $self->SetEventHandlers( $hParam{EventHandlers} );
195 }
196 else {
197 $self->SetEventHandlers( \%ProcTbl );
198 }
199 if ( $hParam{AddHandlers} ) {
200 foreach my $sKey ( keys( %{ $hParam{AddHandlers} } ) ) {
201 $self->SetEventHandler( $sKey, $hParam{AddHandlers}->{$sKey} );
202 }
203 }
204 $_CellHandler = $hParam{CellHandler} if ( $hParam{CellHandler} );
205 $_NotSetCell = $hParam{NotSetCell};
206 $_Object = $hParam{Object};
207
208
209 if ( defined $hParam{Password} ) {
210 $self->{Password} = $hParam{Password};
211 }
212 else {
213 $self->{Password} = 'VelvetSweatshop';
214 }
215
216 $self->{_error_status} = ErrorNone;
217 return $self;
218}
219
220#------------------------------------------------------------------------------
221# Spreadsheet::ParseExcel->SetEventHandler
222#------------------------------------------------------------------------------
223sub SetEventHandler {
224 my ( $self, $key, $sub_ref ) = @_;
225 $self->{FuncTbl}->{$key} = $sub_ref;
226}
227
228#------------------------------------------------------------------------------
229# Spreadsheet::ParseExcel->SetEventHandlers
230#------------------------------------------------------------------------------
231sub SetEventHandlers {
232 my ( $self, $rhTbl ) = @_;
233 $self->{FuncTbl} = undef;
234 foreach my $sKey ( keys %$rhTbl ) {
235 $self->{FuncTbl}->{$sKey} = $rhTbl->{$sKey};
236 }
237}
238
239#------------------------------------------------------------------------------
240# Decryption routines
241# based on sources of gnumeric (ms-biff.c ms-excel-read.c)
242#------------------------------------------------------------------------------
243sub md5state {
244 my ( $md5 ) = @_;
245 my $s = '';
246 for ( my $i = 0 ; $i < 4 ; $i++ ) {
247 my $v = $md5->{_state}[$i];
248 $s .= chr( $v & 0xff );
249 $s .= chr( ( $v >> 8 ) & 0xff );
250 $s .= chr( ( $v >> 16 ) & 0xff );
251 $s .= chr( ( $v >> 24 ) & 0xff );
252 }
253
254 return $s;
255}
256
257sub MakeKey {
258 my ( $block, $key, $valContext ) = @_;
259
260 my $pwarray = "\0" x 64;
261
262 substr( $pwarray, 0, 5 ) = substr( $valContext, 0, 5 );
263
264 substr( $pwarray, 5, 1 ) = chr( $block & 0xff );
265 substr( $pwarray, 6, 1 ) = chr( ( $block >> 8 ) & 0xff );
266 substr( $pwarray, 7, 1 ) = chr( ( $block >> 16 ) & 0xff );
267 substr( $pwarray, 8, 1 ) = chr( ( $block >> 24 ) & 0xff );
268
269 substr( $pwarray, 9, 1 ) = "\x80";
270 substr( $pwarray, 56, 1 ) = "\x48";
271
272 my $md5 = Digest::Perl::MD5->new();
273 $md5->add( $pwarray );
274
275 my $s = md5state( $md5 );
276
277 ${$key} = Crypt::RC4->new( $s );
278}
279
280sub VerifyPassword {
281 my ( $password, $docid, $salt_data, $hashedsalt_data, $valContext ) = @_;
282
283 my $pwarray = "\0" x 64;
284 my $i;
285 my $md5 = Digest::Perl::MD5->new();
286
287 for ( $i = 0 ; $i < length( $password ) ; $i++ ) {
288 my $o = ord( substr( $password, $i, 1 ) );
289 substr( $pwarray, 2 * $i, 1 ) = chr( $o & 0xff );
290 substr( $pwarray, 2 * $i + 1, 1 ) = chr( ( $o >> 8 ) & 0xff );
291 }
292 substr( $pwarray, 2 * $i, 1 ) = chr( 0x80 );
293 substr( $pwarray, 56, 1 ) = chr( ( $i << 4 ) & 0xff );
294
295 $md5->add( $pwarray );
296
297 my $mdContext1 = md5state( $md5 );
298
299 my $offset = 0;
300 my $keyoffset = 0;
301 my $tocopy = 5;
302
303 $md5->reset;
304
305 while ( $offset != 16 ) {
306 if ( ( 64 - $offset ) < 5 ) {
307 $tocopy = 64 - $offset;
308 }
309
310 substr( $pwarray, $offset, $tocopy ) =
311 substr( $mdContext1, $keyoffset, $tocopy );
312
313 $offset += $tocopy;
314
315 if ( $offset == 64 ) {
316 $md5->add( $pwarray );
317 $keyoffset = $tocopy;
318 $tocopy = 5 - $tocopy;
319 $offset = 0;
320 next;
321 }
322
323 $keyoffset = 0;
324 $tocopy = 5;
325 substr( $pwarray, $offset, 16 ) = $docid;
326 $offset += 16;
327 }
328
329 substr( $pwarray, 16, 1 ) = "\x80";
330 substr( $pwarray, 17, 47 ) = "\0" x 47;
331 substr( $pwarray, 56, 1 ) = "\x80";
332 substr( $pwarray, 57, 1 ) = "\x0a";
333
334 $md5->add( $pwarray );
335 ${$valContext} = md5state( $md5 );
336
337 my $key;
338
339 MakeKey( 0, \$key, ${$valContext} );
340
341 my $salt = $key->RC4( $salt_data );
342 my $hashedsalt = $key->RC4( $hashedsalt_data );
343
344 $salt .= "\x80" . "\0" x 47;
345
346 substr( $salt, 56, 1 ) = "\x80";
347
348 $md5->reset;
349 $md5->add( $salt );
350 my $mdContext2 = md5state( $md5 );
351
352 return ( $mdContext2 eq $hashedsalt );
353}
354
355sub SkipBytes {
356 my ( $q, $start, $count ) = @_;
357
358 my $scratch = "\0" x REKEY_BLOCK;
359 my $block;
360
361 $block = int( ( $start + $count ) / REKEY_BLOCK );
362
363 if ( $block != $q->{block} ) {
364 MakeKey( $q->{block} = $block, \$q->{rc4_key}, $q->{md5_ctxt} );
365 $count = ( $start + $count ) % REKEY_BLOCK;
366 }
367
368 $q->{rc4_key}->RC4( substr( $scratch, 0, $count ) );
369
370 return 1;
371}
372
373sub SetDecrypt {
374 my ( $q, $version, $password ) = @_;
375
376 if ( $q->{opcode} != 0x2f ) {
377 return 0;
378 }
379
380 if ( $password eq '' ) {
381 return 0;
382 }
383
384 # TODO old versions decryption
385 #if (version < MS_BIFF_V8 || q->data[0] == 0)
386 # return ms_biff_pre_biff8_query_set_decrypt (q, password);
387
388 if ( $q->{length} != sizeof_BIFF_8_FILEPASS ) {
389 return 0;
390 }
391
392 unless (
393 VerifyPassword(
394 $password,
395 substr( $q->{data}, 6, 16 ),
396 substr( $q->{data}, 22, 16 ),
397 substr( $q->{data}, 38, 16 ),
398 \$q->{md5_ctxt}
399 )
400 )
401 {
402 return 0;
403 }
404
405 $q->{encryption} = MS_BIFF_CRYPTO_RC4;
406 $q->{block} = -1;
407
408 # The first record after FILEPASS seems to be unencrypted
409 $q->{dont_decrypt_next_record} = 1;
410
411 # Pretend to decrypt the entire stream up till this point, it was
412 # encrypted, but do it anyway to keep the rc4 state in sync
413
414 SkipBytes( $q, 0, $q->{streamPos} );
415
416 return 1;
417}
418
419sub InitStream {
420 my ( $stream_data ) = @_;
421 my %q;
422
423 $q{opcode} = 0;
424 $q{length} = 0;
425 $q{data} = '';
426
427 $q{stream} = $stream_data; # data stream
428 $q{streamLen} = length( $stream_data ); # stream length
429 $q{streamPos} = 0; # stream position
430
431 $q{encryption} = 0;
432 $q{xor_key} = '';
433 $q{rc4_key} = '';
434 $q{md5_ctxt} = '';
435 $q{block} = 0;
436 $q{dont_decrypt_next_record} = 0;
437
438 return \%q;
439}
440
441sub QueryNext {
442 my ( $q ) = @_;
443
444 if ( $q->{streamPos} + 4 >= $q->{streamLen} ) {
445 return 0;
446 }
447
448 my $data = substr( $q->{stream}, $q->{streamPos}, 4 );
449
450 ( $q->{opcode}, $q->{length} ) = unpack( 'v2', $data );
451
452 # No biff record should be larger than around 20,000.
453 if ( $q->{length} >= 20000 ) {
454 return 0;
455 }
456
457 if ( $q->{length} > 0 ) {
458 $q->{data} = substr( $q->{stream}, $q->{streamPos} + 4, $q->{length} );
459 }
460 else {
461 $q->{data} = undef;
462 $q->{dont_decrypt_next_record} = 1;
463 }
464
465 if ( $q->{encryption} == MS_BIFF_CRYPTO_RC4 ) {
466 if ( $q->{dont_decrypt_next_record} ) {
467 SkipBytes( $q, $q->{streamPos}, 4 + $q->{length} );
468 $q->{dont_decrypt_next_record} = 0;
469 }
470 else {
471 my $pos = $q->{streamPos};
472 my $data = $q->{data};
473 my $len = $q->{length};
474 my $res = '';
475
476 # Pretend to decrypt header.
477 SkipBytes( $q, $pos, 4 );
478 $pos += 4;
479
480 while ( $q->{block} != int( ( $pos + $len ) / REKEY_BLOCK ) ) {
481 my $step = REKEY_BLOCK - ( $pos % REKEY_BLOCK );
482 $res .= $q->{rc4_key}->RC4( substr( $data, 0, $step ) );
483 $data = substr( $data, $step );
484 $pos += $step;
485 $len -= $step;
486 MakeKey( ++$q->{block}, \$q->{rc4_key}, $q->{md5_ctxt} );
487 }
488
489 $res .= $q->{rc4_key}->RC4( substr( $data, 0, $len ) );
490 $q->{data} = $res;
491 }
492 }
493 elsif ( $q->{encryption} == MS_BIFF_CRYPTO_XOR ) {
494
495 # not implemented
496 return 0;
497 }
498 elsif ( $q->{encryption} == MS_BIFF_CRYPTO_NONE ) {
499
500 }
501
502 $q->{streamPos} += 4 + $q->{length};
503
504 return 1;
505}
506
507###############################################################################
508#
509# Parse()
510#
511# Parse the Excel file and convert it into a tree of objects..
512#
513sub parse {
514
515 my ( $self, $source, $formatter ) = @_;
516
517 my $workbook = Spreadsheet::ParseExcel::Workbook->new();
518 $workbook->{SheetCount} = 0;
519
520 my ( $biff_data, $data_length ) = $self->_get_content( $source, $workbook );
521 return undef if not $biff_data;
522
523 if ( $formatter ) {
524 $workbook->{FmtClass} = $formatter;
525 }
526 else {
527 $workbook->{FmtClass} = Spreadsheet::ParseExcel::FmtDefault->new();
528 }
529
530 # Parse the BIFF data.
531 my $stream = InitStream( $biff_data );
532
533 while ( QueryNext( $stream ) ) {
534
535 my $record = $stream->{opcode};
536 my $record_length = $stream->{length};
537
538 my $record_header = $stream->{data};
539
540 # If the file contains a FILEPASS record we assume that it is encrypted
541 # and cannot be parsed.
542 if ( $record == 0x002F ) {
543 unless ( SetDecrypt( $stream, '', $self->{Password} ) ) {
544 $self->{_error_status} = ErrorFileEncrypted;
545 return undef;
546 }
547 }
548
549 # Special case of a formula String with no string.
550 if ( $workbook->{_PrevPos}
551 && ( defined $self->{FuncTbl}->{$record} )
552 && ( $record != 0x207 ) )
553 {
554 my $iPos = $workbook->{_PrevPos};
555 $workbook->{_PrevPos} = undef;
556
557 my ( $row, $col, $format_index ) = @$iPos;
558 _NewCell(
559 $workbook, $row, $col,
560 Kind => 'Formula String',
561 Val => '',
562 FormatNo => $format_index,
563 Format => $workbook->{Format}[$format_index],
564 Numeric => 0,
565 Code => undef,
566 Book => $workbook,
567 );
568 }
569
570 # If the BIFF record matches 0x0*09 then it is a BOF record.
571 # We reset the _skip_chart flag to ensure we check the sheet type.
572 if ( ( $record & 0xF0FF ) == 0x09 ) {
573 $workbook->{_skip_chart} = 0;
574 }
575
576 if ( defined $self->{FuncTbl}->{$record} && !$workbook->{_skip_chart} )
577 {
578 $self->{FuncTbl}->{$record}
579 ->( $workbook, $record, $record_length, $record_header );
580 }
581
582 $PREFUNC = $record if ( $record != 0x3C ); #Not Continue
583
584 return $workbook if defined $workbook->{_ParseAbort};
585 }
586
587 return $workbook;
588}
589
590###############################################################################
591#
592# _get_content()
593#
594# Get the Excel BIFF content from the file or filehandle.
595#
596sub _get_content {
597
598 my ( $self, $source, $workbook ) = @_;
599 my ( $biff_data, $data_length );
600
601 # Reset the error status in case method is called more than once.
602 $self->{_error_status} = ErrorNone;
603
604 if ( ref( $source ) eq "SCALAR" ) {
605
606 # Specified by a scalar buffer.
607 ( $biff_data, $data_length ) = $self->{GetContent}->( $source );
608
609 }
610 elsif ( ( ref( $source ) =~ /GLOB/ ) || ( ref( $source ) eq 'Fh' ) ) {
611
612 # For CGI.pm (Light FileHandle)
613 binmode( $source );
614 my $sWk;
615 my $sBuff = '';
616
617 while ( read( $source, $sWk, 4096 ) ) {
618 $sBuff .= $sWk;
619 }
620
621 ( $biff_data, $data_length ) = $self->{GetContent}->( \$sBuff );
622
623 }
624 elsif ( ref( $source ) eq 'ARRAY' ) {
625
626 # Specified by file content
627 $workbook->{File} = undef;
628 my $sData = join( '', @$source );
629 ( $biff_data, $data_length ) = $self->{GetContent}->( \$sData );
630 }
631 else {
632
633 # Specified by filename .
634 $workbook->{File} = $source;
635
636 if ( !-e $source ) {
637 $self->{_error_status} = ErrorNoFile;
638 return undef;
639 }
640
641 ( $biff_data, $data_length ) = $self->{GetContent}->( $source );
642 }
643
644 # If the read was successful return the data.
645 if ( $data_length ) {
646 return ( $biff_data, $data_length );
647 }
648 else {
649 $self->{_error_status} = ErrorNoExcelData;
650 return undef;
651 }
652
653}
654
655#------------------------------------------------------------------------------
656# _subGetContent (for Spreadsheet::ParseExcel)
657#------------------------------------------------------------------------------
658sub _subGetContent {
659 my ( $sFile ) = @_;
660
661 my $oOl = OLE::Storage_Lite->new( $sFile );
662 return ( undef, undef ) unless ( $oOl );
663 my @aRes = $oOl->getPpsSearch(
664 [
665 OLE::Storage_Lite::Asc2Ucs( 'Book' ),
666 OLE::Storage_Lite::Asc2Ucs( 'Workbook' )
667 ],
668 1, 1
669 );
670 return ( undef, undef ) if ( $#aRes < 0 );
671
672 #Hack from Herbert
673 if ( $aRes[0]->{Data} ) {
674 return ( $aRes[0]->{Data}, length( $aRes[0]->{Data} ) );
675 }
676
677 #Same as OLE::Storage_Lite
678 my $oIo;
679
680 #1. $sFile is Ref of scalar
681 if ( ref( $sFile ) eq 'SCALAR' ) {
682 if ( $_use_perlio ) {
683 open $oIo, "<", \$sFile;
684 }
685 else {
686 $oIo = IO::Scalar->new;
687 $oIo->open( $sFile );
688 }
689 }
690
691 #2. $sFile is a IO::Handle object
692 elsif ( UNIVERSAL::isa( $sFile, 'IO::Handle' ) ) {
693 $oIo = $sFile;
694 binmode( $oIo );
695 }
696
697 #3. $sFile is a simple filename string
698 elsif ( !ref( $sFile ) ) {
699 $oIo = IO::File->new;
700 $oIo->open( "<$sFile" ) || return undef;
701 binmode( $oIo );
702 }
703 my $sWk;
704 my $sBuff = '';
705
706 while ( $oIo->read( $sWk, 4096 ) ) { #4_096 has no special meanings
707 $sBuff .= $sWk;
708 }
709 $oIo->close();
710
711 #Not Excel file (simple method)
712 return ( undef, undef ) if ( substr( $sBuff, 0, 1 ) ne "\x09" );
713 return ( $sBuff, length( $sBuff ) );
714}
715
716#------------------------------------------------------------------------------
717# _subBOF (for Spreadsheet::ParseExcel) Developers' Kit : P303
718#------------------------------------------------------------------------------
719sub _subBOF {
720 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
721 my ( $iVer, $iDt ) = unpack( "v2", $sWk );
722
723 #Workbook Global
724 if ( $iDt == 0x0005 ) {
725 $oBook->{Version} = unpack( "v", $sWk );
726 $oBook->{BIFFVersion} =
727 ( $oBook->{Version} == verExcel95 ) ? verBIFF5 : verBIFF8;
728 $oBook->{_CurSheet} = undef;
729 $oBook->{_CurSheet_} = -1;
730 }
731
732 #Worksheet or Dialogsheet
733 elsif ( $iDt != 0x0020 ) { #if($iDt == 0x0010)
734 if ( defined $oBook->{_CurSheet_} ) {
735 $oBook->{_CurSheet} = $oBook->{_CurSheet_} + 1;
736 $oBook->{_CurSheet_}++;
737
738 (
739 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{SheetVersion},
740 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{SheetType},
741 )
742 = unpack( "v2", $sWk )
743 if ( length( $sWk ) > 4 );
744 }
745 else {
746 $oBook->{BIFFVersion} = int( $bOp / 0x100 );
747 if ( ( $oBook->{BIFFVersion} == verBIFF2 )
748 || ( $oBook->{BIFFVersion} == verBIFF3 )
749 || ( $oBook->{BIFFVersion} == verBIFF4 ) )
750 {
751 $oBook->{Version} = $oBook->{BIFFVersion};
752 $oBook->{_CurSheet} = 0;
753 $oBook->{Worksheet}[ $oBook->{SheetCount} ] =
754 Spreadsheet::ParseExcel::Worksheet->new(
755 _Name => '',
756 Name => '',
757 _Book => $oBook,
758 _SheetNo => $oBook->{SheetCount},
759 );
760 $oBook->{SheetCount}++;
761 }
762 }
763 }
764 else {
765
766 # Set flag to ignore all chart records until we reach another BOF.
767 $oBook->{_skip_chart} = 1;
768 }
769}
770
771#------------------------------------------------------------------------------
772# _subBlank (for Spreadsheet::ParseExcel) DK:P303
773#------------------------------------------------------------------------------
774sub _subBlank {
775 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
776 my ( $iR, $iC, $iF ) = unpack( "v3", $sWk );
777 _NewCell(
778 $oBook, $iR, $iC,
779 Kind => 'BLANK',
780 Val => '',
781 FormatNo => $iF,
782 Format => $oBook->{Format}[$iF],
783 Numeric => 0,
784 Code => undef,
785 Book => $oBook,
786 );
787
788 #2.MaxRow, MaxCol, MinRow, MinCol
789 _SetDimension( $oBook, $iR, $iC, $iC );
790}
791
792#------------------------------------------------------------------------------
793# _subInteger (for Spreadsheet::ParseExcel) Not in DK
794#------------------------------------------------------------------------------
795sub _subInteger {
796 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
797 my ( $iR, $iC, $iF, $sTxt, $sDum );
798
799 ( $iR, $iC, $iF, $sDum, $sTxt ) = unpack( "v3cv", $sWk );
800 _NewCell(
801 $oBook, $iR, $iC,
802 Kind => 'INTEGER',
803 Val => $sTxt,
804 FormatNo => $iF,
805 Format => $oBook->{Format}[$iF],
806 Numeric => 0,
807 Code => undef,
808 Book => $oBook,
809 );
810
811 #2.MaxRow, MaxCol, MinRow, MinCol
812 _SetDimension( $oBook, $iR, $iC, $iC );
813}
814
815#------------------------------------------------------------------------------
816# _subNumber (for Spreadsheet::ParseExcel) : DK: P354
817#------------------------------------------------------------------------------
818sub _subNumber {
819 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
820
821 my ( $iR, $iC, $iF ) = unpack( "v3", $sWk );
822 my $dVal = _convDval( substr( $sWk, 6, 8 ) );
823 _NewCell(
824 $oBook, $iR, $iC,
825 Kind => 'Number',
826 Val => $dVal,
827 FormatNo => $iF,
828 Format => $oBook->{Format}[$iF],
829 Numeric => 1,
830 Code => undef,
831 Book => $oBook,
832 );
833
834 #2.MaxRow, MaxCol, MinRow, MinCol
835 _SetDimension( $oBook, $iR, $iC, $iC );
836}
837
838#------------------------------------------------------------------------------
839# _convDval (for Spreadsheet::ParseExcel)
840#------------------------------------------------------------------------------
841sub _convDval {
842 my ( $sWk ) = @_;
843 return
844 unpack( "d",
845 ( $BIGENDIAN ) ? pack( "c8", reverse( unpack( "c8", $sWk ) ) ) : $sWk );
846}
847
848#------------------------------------------------------------------------------
849# _subRString (for Spreadsheet::ParseExcel) DK:P405
850#------------------------------------------------------------------------------
851sub _subRString {
852 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
853 my ( $iR, $iC, $iF, $iL, $sTxt );
854 ( $iR, $iC, $iF, $iL ) = unpack( "v4", $sWk );
855 $sTxt = substr( $sWk, 8, $iL );
856
857 #Has STRUN
858 if ( length( $sWk ) > ( 8 + $iL ) ) {
859 _NewCell(
860 $oBook, $iR, $iC,
861 Kind => 'RString',
862 Val => $sTxt,
863 FormatNo => $iF,
864 Format => $oBook->{Format}[$iF],
865 Numeric => 0,
866 Code => '_native_', #undef,
867 Book => $oBook,
868 Rich => substr( $sWk, ( 8 + $iL ) + 1 ),
869 );
870 }
871 else {
872 _NewCell(
873 $oBook, $iR, $iC,
874 Kind => 'RString',
875 Val => $sTxt,
876 FormatNo => $iF,
877 Format => $oBook->{Format}[$iF],
878 Numeric => 0,
879 Code => '_native_',
880 Book => $oBook,
881 );
882 }
883
884 #2.MaxRow, MaxCol, MinRow, MinCol
885 _SetDimension( $oBook, $iR, $iC, $iC );
886}
887
888#------------------------------------------------------------------------------
889# _subBoolErr (for Spreadsheet::ParseExcel) DK:P306
890#------------------------------------------------------------------------------
891sub _subBoolErr {
892 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
893 my ( $iR, $iC, $iF ) = unpack( "v3", $sWk );
894 my ( $iVal, $iFlg ) = unpack( "cc", substr( $sWk, 6, 2 ) );
895 my $sTxt = DecodeBoolErr( $iVal, $iFlg );
896
897 _NewCell(
898 $oBook, $iR, $iC,
899 Kind => 'BoolError',
900 Val => $sTxt,
901 FormatNo => $iF,
902 Format => $oBook->{Format}[$iF],
903 Numeric => 0,
904 Code => undef,
905 Book => $oBook,
906 );
907
908 #2.MaxRow, MaxCol, MinRow, MinCol
909 _SetDimension( $oBook, $iR, $iC, $iC );
910}
911
912###############################################################################
913#
914# _subRK()
915#
916# Decode the RK BIFF record.
917#
918sub _subRK {
919
920 my ( $workbook, $biff_number, $length, $data ) = @_;
921
922 my ( $row, $col, $format_index, $rk_number ) = unpack( 'vvvV', $data );
923
924 my $number = _decode_rk_number( $rk_number );
925
926 _NewCell(
927 $workbook, $row, $col,
928 Kind => 'RK',
929 Val => $number,
930 FormatNo => $format_index,
931 Format => $workbook->{Format}->[$format_index],
932 Numeric => 1,
933 Code => undef,
934 Book => $workbook,
935 );
936
937 # Store the max and min row/col values.
938 _SetDimension( $workbook, $row, $col, $col );
939}
940
941#------------------------------------------------------------------------------
942# _subArray (for Spreadsheet::ParseExcel) DK:P297
943#------------------------------------------------------------------------------
944sub _subArray {
945 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
946 my ( $iBR, $iER, $iBC, $iEC ) = unpack( "v2c2", $sWk );
947
948}
949
950#------------------------------------------------------------------------------
951# _subFormula (for Spreadsheet::ParseExcel) DK:P336
952#------------------------------------------------------------------------------
953sub _subFormula {
954 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
955 my ( $iR, $iC, $iF ) = unpack( "v3", $sWk );
956
957 my ( $iFlg ) = unpack( "v", substr( $sWk, 12, 2 ) );
958 if ( $iFlg == 0xFFFF ) {
959 my ( $iKind ) = unpack( "c", substr( $sWk, 6, 1 ) );
960 my ( $iVal ) = unpack( "c", substr( $sWk, 8, 1 ) );
961
962 if ( ( $iKind == 1 ) or ( $iKind == 2 ) ) {
963 my $sTxt =
964 ( $iKind == 1 )
965 ? DecodeBoolErr( $iVal, 0 )
966 : DecodeBoolErr( $iVal, 1 );
967 _NewCell(
968 $oBook, $iR, $iC,
969 Kind => 'Formula Bool',
970 Val => $sTxt,
971 FormatNo => $iF,
972 Format => $oBook->{Format}[$iF],
973 Numeric => 0,
974 Code => undef,
975 Book => $oBook,
976 );
977 }
978 else { # Result (Reserve Only)
979 $oBook->{_PrevPos} = [ $iR, $iC, $iF ];
980 }
981 }
982 else {
983 my $dVal = _convDval( substr( $sWk, 6, 8 ) );
984 _NewCell(
985 $oBook, $iR, $iC,
986 Kind => 'Formula Number',
987 Val => $dVal,
988 FormatNo => $iF,
989 Format => $oBook->{Format}[$iF],
990 Numeric => 1,
991 Code => undef,
992 Book => $oBook,
993 );
994 }
995
996 #2.MaxRow, MaxCol, MinRow, MinCol
997 _SetDimension( $oBook, $iR, $iC, $iC );
998}
999
1000#------------------------------------------------------------------------------
1001# _subString (for Spreadsheet::ParseExcel) DK:P414
1002#------------------------------------------------------------------------------
1003sub _subString {
1004 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1005
1006 #Position (not enough for ARRAY)
1007
1008 my $iPos = $oBook->{_PrevPos};
1009 return undef unless ( $iPos );
1010 $oBook->{_PrevPos} = undef;
1011 my ( $iR, $iC, $iF ) = @$iPos;
1012
1013 my ( $iLen, $sTxt, $sCode );
1014 if ( $oBook->{BIFFVersion} == verBIFF8 ) {
1015 my ( $raBuff, $iLen ) = _convBIFF8String( $oBook, $sWk, 1 );
1016 $sTxt = $raBuff->[0];
1017 $sCode = ( $raBuff->[1] ) ? 'ucs2' : undef;
1018 }
1019 elsif ( $oBook->{BIFFVersion} == verBIFF5 ) {
1020 $sCode = '_native_';
1021 $iLen = unpack( "v", $sWk );
1022 $sTxt = substr( $sWk, 2, $iLen );
1023 }
1024 else {
1025 $sCode = '_native_';
1026 $iLen = unpack( "c", $sWk );
1027 $sTxt = substr( $sWk, 1, $iLen );
1028 }
1029 _NewCell(
1030 $oBook, $iR, $iC,
1031 Kind => 'String',
1032 Val => $sTxt,
1033 FormatNo => $iF,
1034 Format => $oBook->{Format}[$iF],
1035 Numeric => 0,
1036 Code => $sCode,
1037 Book => $oBook,
1038 );
1039
1040 #2.MaxRow, MaxCol, MinRow, MinCol
1041 _SetDimension( $oBook, $iR, $iC, $iC );
1042}
1043
1044#------------------------------------------------------------------------------
1045# _subLabel (for Spreadsheet::ParseExcel) DK:P344
1046#------------------------------------------------------------------------------
1047sub _subLabel {
1048 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1049 my ( $iR, $iC, $iF ) = unpack( "v3", $sWk );
1050 my ( $sLbl, $sCode );
1051
1052 #BIFF8
1053 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1054 my ( $raBuff, $iLen, $iStPos, $iLenS ) =
1055 _convBIFF8String( $oBook, substr( $sWk, 6 ), 1 );
1056 $sLbl = $raBuff->[0];
1057 $sCode = ( $raBuff->[1] ) ? 'ucs2' : undef;
1058 }
1059
1060 #Before BIFF8
1061 else {
1062 $sLbl = substr( $sWk, 8 );
1063 $sCode = '_native_';
1064 }
1065 _NewCell(
1066 $oBook, $iR, $iC,
1067 Kind => 'Label',
1068 Val => $sLbl,
1069 FormatNo => $iF,
1070 Format => $oBook->{Format}[$iF],
1071 Numeric => 0,
1072 Code => $sCode,
1073 Book => $oBook,
1074 );
1075
1076 #2.MaxRow, MaxCol, MinRow, MinCol
1077 _SetDimension( $oBook, $iR, $iC, $iC );
1078}
1079
1080###############################################################################
1081#
1082# _subMulRK()
1083#
1084# Decode the Multiple RK BIFF record.
1085#
1086sub _subMulRK {
1087
1088 my ( $workbook, $biff_number, $length, $data ) = @_;
1089
1090 # JMN: I don't know why this is here.
1091 return if $workbook->{SheetCount} <= 0;
1092
1093 my ( $row, $first_col ) = unpack( "v2", $data );
1094 my $last_col = unpack( "v", substr( $data, length( $data ) - 2, 2 ) );
1095
1096 # Iterate over the RK array and decode the data.
1097 my $pos = 4;
1098 for my $col ( $first_col .. $last_col ) {
1099
1100 my $data = substr( $data, $pos, 6 );
1101 my ( $format_index, $rk_number ) = unpack 'vV', $data;
1102 my $number = _decode_rk_number( $rk_number );
1103
1104 _NewCell(
1105 $workbook, $row, $col,
1106 Kind => 'MulRK',
1107 Val => $number,
1108 FormatNo => $format_index,
1109 Format => $workbook->{Format}->[$format_index],
1110 Numeric => 1,
1111 Code => undef,
1112 Book => $workbook,
1113 );
1114 $pos += 6;
1115 }
1116
1117 # Store the max and min row/col values.
1118 _SetDimension( $workbook, $row, $first_col, $last_col );
1119}
1120
1121#------------------------------------------------------------------------------
1122# _subMulBlank (for Spreadsheet::ParseExcel) DK:P349
1123#------------------------------------------------------------------------------
1124sub _subMulBlank {
1125 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1126 my ( $iR, $iSc ) = unpack( "v2", $sWk );
1127 my $iEc = unpack( "v", substr( $sWk, length( $sWk ) - 2, 2 ) );
1128 my $iPos = 4;
1129 for ( my $iC = $iSc ; $iC <= $iEc ; $iC++ ) {
1130 my $iF = unpack( 'v', substr( $sWk, $iPos, 2 ) );
1131 _NewCell(
1132 $oBook, $iR, $iC,
1133 Kind => 'MulBlank',
1134 Val => '',
1135 FormatNo => $iF,
1136 Format => $oBook->{Format}[$iF],
1137 Numeric => 0,
1138 Code => undef,
1139 Book => $oBook,
1140 );
1141 $iPos += 2;
1142 }
1143
1144 #2.MaxRow, MaxCol, MinRow, MinCol
1145 _SetDimension( $oBook, $iR, $iSc, $iEc );
1146}
1147
1148#------------------------------------------------------------------------------
1149# _subLabelSST (for Spreadsheet::ParseExcel) DK: P345
1150#------------------------------------------------------------------------------
1151sub _subLabelSST {
1152 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1153 my ( $iR, $iC, $iF, $iIdx ) = unpack( 'v3V', $sWk );
1154
1155 _NewCell(
1156 $oBook, $iR, $iC,
1157 Kind => 'PackedIdx',
1158 Val => $oBook->{PkgStr}[$iIdx]->{Text},
1159 FormatNo => $iF,
1160 Format => $oBook->{Format}[$iF],
1161 Numeric => 0,
1162 Code => ( $oBook->{PkgStr}[$iIdx]->{Unicode} ) ? 'ucs2' : undef,
1163 Book => $oBook,
1164 Rich => $oBook->{PkgStr}[$iIdx]->{Rich},
1165 );
1166
1167 #2.MaxRow, MaxCol, MinRow, MinCol
1168 _SetDimension( $oBook, $iR, $iC, $iC );
1169}
1170
1171#------------------------------------------------------------------------------
1172# _subFlg1904 (for Spreadsheet::ParseExcel) DK:P296
1173#------------------------------------------------------------------------------
1174sub _subFlg1904 {
1175 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1176 $oBook->{Flg1904} = unpack( "v", $sWk );
1177}
1178
1179#------------------------------------------------------------------------------
1180# _subRow (for Spreadsheet::ParseExcel) DK:P403
1181#------------------------------------------------------------------------------
1182sub _subRow {
1183 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1184 return undef unless ( defined $oBook->{_CurSheet} );
1185
1186 #0. Get Worksheet info (MaxRow, MaxCol, MinRow, MinCol)
1187 my ( $iR, $iSc, $iEc, $iHght, $undef1, $undef2, $iGr, $iXf ) =
1188 unpack( "v8", $sWk );
1189 $iEc--;
1190
1191 # TODO. we need to handle hidden rows:
1192 # $iGr & 0x20
1193 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RowHeight}[$iR] = $iHght / 20;
1194
1195 #2.MaxRow, MaxCol, MinRow, MinCol
1196 _SetDimension( $oBook, $iR, $iSc, $iEc );
1197}
1198
1199#------------------------------------------------------------------------------
1200# _SetDimension (for Spreadsheet::ParseExcel)
1201#------------------------------------------------------------------------------
1202sub _SetDimension {
1203 my ( $oBook, $iR, $iSc, $iEc ) = @_;
1204 return undef unless ( defined $oBook->{_CurSheet} );
1205
1206 #2.MaxRow, MaxCol, MinRow, MinCol
1207 #2.1 MinRow
1208 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinRow} = $iR
1209 unless ( defined $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinRow} )
1210 and ( $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinRow} <= $iR );
1211
1212 #2.2 MaxRow
1213 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxRow} = $iR
1214 unless ( defined $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxRow} )
1215 and ( $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxRow} > $iR );
1216
1217 #2.3 MinCol
1218 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinCol} = $iSc
1219 unless ( defined $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinCol} )
1220 and ( $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MinCol} <= $iSc );
1221
1222 #2.4 MaxCol
1223 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxCol} = $iEc
1224 unless ( defined $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxCol} )
1225 and ( $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{MaxCol} > $iEc );
1226
1227}
1228
1229#------------------------------------------------------------------------------
1230# _subDefaultRowHeight (for Spreadsheet::ParseExcel) DK: P318
1231#------------------------------------------------------------------------------
1232sub _subDefaultRowHeight {
1233 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1234 return undef unless ( defined $oBook->{_CurSheet} );
1235
1236 #1. RowHeight
1237 my ( $iDum, $iHght ) = unpack( "v2", $sWk );
1238 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{DefRowHeight} = $iHght / 20;
1239
1240}
1241
1242#------------------------------------------------------------------------------
1243# _subStandardWidth(for Spreadsheet::ParseExcel) DK:P413
1244#------------------------------------------------------------------------------
1245sub _subStandardWidth {
1246 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1247 my $iW = unpack( "v", $sWk );
1248 $oBook->{StandardWidth} = _convert_col_width( $oBook, $iW );
1249}
1250
1251###############################################################################
1252#
1253# _subDefColWidth()
1254#
1255# Read the DEFCOLWIDTH Biff record. This gives the width in terms of chars
1256# and is different from the width in the COLINFO record.
1257#
1258sub _subDefColWidth {
1259
1260 my ( $self, $record, $length, $data ) = @_;
1261
1262 my $width = unpack 'v', $data;
1263
1264 # Adjustment for default Arial 10 width.
1265 $width = 8.43 if $width == 8;
1266
1267 $self->{Worksheet}->[ $self->{_CurSheet} ]->{DefColWidth} = $width;
1268}
1269
1270###############################################################################
1271#
1272# _convert_col_width()
1273#
1274# Converts from the internal Excel column width units to user units seen in the
1275# interface. It is first necessary to convert the internal width to pixels and
1276# then to user units. The conversion is specific to a default font of Arial 10.
1277# TODO, the conversion should be extended to other fonts and sizes.
1278#
1279sub _convert_col_width {
1280
1281 my $self = shift;
1282 my $excel_width = shift;
1283
1284 # Convert from Excel units to pixels (rounded up).
1285 my $pixels = int( 0.5 + $excel_width * 7 / 256 );
1286
1287 # Convert from pixels to user units.
1288 # The conversion is different for columns <= 1 user unit (12 pixels).
1289 my $user_width;
1290 if ( $pixels <= 12 ) {
1291 $user_width = $pixels / 12;
1292 }
1293 else {
1294 $user_width = ( $pixels - 5 ) / 7;
1295 }
1296
1297 # Round up to 2 decimal places.
1298 $user_width = int( $user_width * 100 + 0.5 ) / 100;
1299
1300 return $user_width;
1301}
1302
1303#------------------------------------------------------------------------------
1304# _subColInfo (for Spreadsheet::ParseExcel) DK:P309
1305#------------------------------------------------------------------------------
1306sub _subColInfo {
1307
1308 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1309
1310 return undef unless defined $oBook->{_CurSheet};
1311
1312 my ( $iSc, $iEc, $iW, $iXF, $iGr ) = unpack( "v5", $sWk );
1313
1314 for ( my $i = $iSc ; $i <= $iEc ; $i++ ) {
1315
1316 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{ColWidth}[$i] =
1317 _convert_col_width( $oBook, $iW );
1318
1319 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{ColFmtNo}[$i] = $iXF;
1320
1321 # TODO. we need to handle hidden cols: $iGr & 0x01.
1322 }
1323}
1324
1325#------------------------------------------------------------------------------
1326# _subSST (for Spreadsheet::ParseExcel) DK:P413
1327#------------------------------------------------------------------------------
1328sub _subSST {
1329 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1330 _subStrWk( $oBook, substr( $sWk, 8 ) );
1331}
1332
1333#------------------------------------------------------------------------------
1334# _subContinue (for Spreadsheet::ParseExcel) DK:P311
1335#------------------------------------------------------------------------------
1336sub _subContinue {
1337 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1338
1339 #if(defined $self->{FuncTbl}->{$bOp}) {
1340 # $self->{FuncTbl}->{$PREFUNC}->($oBook, $bOp, $bLen, $sWk);
1341 #}
1342
1343 _subStrWk( $oBook, $sWk, 1 ) if ( $PREFUNC == 0xFC );
1344}
1345
1346#------------------------------------------------------------------------------
1347# _subWriteAccess (for Spreadsheet::ParseExcel) DK:P451
1348#------------------------------------------------------------------------------
1349sub _subWriteAccess {
1350 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1351 return if ( defined $oBook->{_Author} );
1352
1353 #BIFF8
1354 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1355 $oBook->{Author} = _convBIFF8String( $oBook, $sWk );
1356 }
1357
1358 #Before BIFF8
1359 else {
1360 my ( $iLen ) = unpack( "c", $sWk );
1361 $oBook->{Author} =
1362 $oBook->{FmtClass}->TextFmt( substr( $sWk, 1, $iLen ), '_native_' );
1363 }
1364}
1365
1366#------------------------------------------------------------------------------
1367# _convBIFF8String (for Spreadsheet::ParseExcel)
1368#------------------------------------------------------------------------------
1369sub _convBIFF8String {
1370 my ( $oBook, $sWk, $iCnvFlg ) = @_;
1371 my ( $iLen, $iFlg ) = unpack( "vc", $sWk );
1372 my ( $iHigh, $iExt, $iRich ) = ( $iFlg & 0x01, $iFlg & 0x04, $iFlg & 0x08 );
1373 my ( $iStPos, $iExtCnt, $iRichCnt, $sStr );
1374
1375 #2. Rich and Ext
1376 if ( $iRich && $iExt ) {
1377 $iStPos = 9;
1378 ( $iRichCnt, $iExtCnt ) = unpack( 'vV', substr( $sWk, 3, 6 ) );
1379 }
1380 elsif ( $iRich ) { #Only Rich
1381 $iStPos = 5;
1382 $iRichCnt = unpack( 'v', substr( $sWk, 3, 2 ) );
1383 $iExtCnt = 0;
1384 }
1385 elsif ( $iExt ) { #Only Ext
1386 $iStPos = 7;
1387 $iRichCnt = 0;
1388 $iExtCnt = unpack( 'V', substr( $sWk, 3, 4 ) );
1389 }
1390 else { #Nothing Special
1391 $iStPos = 3;
1392 $iExtCnt = 0;
1393 $iRichCnt = 0;
1394 }
1395
1396 #3.Get String
1397 if ( $iHigh ) { #Compressed
1398 $iLen *= 2;
1399 $sStr = substr( $sWk, $iStPos, $iLen );
1400 _SwapForUnicode( \$sStr );
1401 $sStr = $oBook->{FmtClass}->TextFmt( $sStr, 'ucs2' )
1402 unless ( $iCnvFlg );
1403 }
1404 else { #Not Compressed
1405 $sStr = substr( $sWk, $iStPos, $iLen );
1406 $sStr = $oBook->{FmtClass}->TextFmt( $sStr, undef ) unless ( $iCnvFlg );
1407 }
1408
1409 #4. return
1410 if ( wantarray ) {
1411
1412 #4.1 Get Rich and Ext
1413 if ( length( $sWk ) < $iStPos + $iLen + $iRichCnt * 4 + $iExtCnt ) {
1414 return (
1415 [ undef, $iHigh, undef, undef ],
1416 $iStPos + $iLen + $iRichCnt * 4 + $iExtCnt,
1417 $iStPos, $iLen
1418 );
1419 }
1420 else {
1421 return (
1422 [
1423 $sStr,
1424 $iHigh,
1425 substr( $sWk, $iStPos + $iLen, $iRichCnt * 4 ),
1426 substr( $sWk, $iStPos + $iLen + $iRichCnt * 4, $iExtCnt )
1427 ],
1428 $iStPos + $iLen + $iRichCnt * 4 + $iExtCnt,
1429 $iStPos, $iLen
1430 );
1431 }
1432 }
1433 else {
1434 return $sStr;
1435 }
1436}
1437
1438#------------------------------------------------------------------------------
1439# _subXF (for Spreadsheet::ParseExcel) DK:P453
1440#------------------------------------------------------------------------------
1441sub _subXF {
1442 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1443
1444 my ( $iFnt, $iIdx );
1445 my (
1446 $iLock, $iHidden, $iStyle, $i123, $iAlH, $iWrap,
1447 $iAlV, $iJustL, $iRotate, $iInd, $iShrink, $iMerge,
1448 $iReadDir, $iBdrD, $iBdrSL, $iBdrSR, $iBdrST, $iBdrSB,
1449 $iBdrSD, $iBdrCL, $iBdrCR, $iBdrCT, $iBdrCB, $iBdrCD,
1450 $iFillP, $iFillCF, $iFillCB
1451 );
1452
1453
1454 if ( $oBook->{BIFFVersion} == verBIFF4 ) {
1455
1456 # Minimal support for Excel 4. We just get the font and format indices
1457 # so that the cell data value can be formatted.
1458 ( $iFnt, $iIdx, ) = unpack( "CC", $sWk );
1459 }
1460 elsif ( $oBook->{BIFFVersion} == verBIFF8 ) {
1461 my ( $iGen, $iAlign, $iGen2, $iBdr1, $iBdr2, $iBdr3, $iPtn );
1462
1463 ( $iFnt, $iIdx, $iGen, $iAlign, $iGen2, $iBdr1, $iBdr2, $iBdr3, $iPtn )
1464 = unpack( "v7Vv", $sWk );
1465 $iLock = ( $iGen & 0x01 ) ? 1 : 0;
1466 $iHidden = ( $iGen & 0x02 ) ? 1 : 0;
1467 $iStyle = ( $iGen & 0x04 ) ? 1 : 0;
1468 $i123 = ( $iGen & 0x08 ) ? 1 : 0;
1469 $iAlH = ( $iAlign & 0x07 );
1470 $iWrap = ( $iAlign & 0x08 ) ? 1 : 0;
1471 $iAlV = ( $iAlign & 0x70 ) / 0x10;
1472 $iJustL = ( $iAlign & 0x80 ) ? 1 : 0;
1473
1474 $iRotate = ( ( $iAlign & 0xFF00 ) / 0x100 ) & 0x00FF;
1475 $iRotate = 90 if ( $iRotate == 255 );
1476 $iRotate = 90 - $iRotate if ( $iRotate > 90 );
1477
1478 $iInd = ( $iGen2 & 0x0F );
1479 $iShrink = ( $iGen2 & 0x10 ) ? 1 : 0;
1480 $iMerge = ( $iGen2 & 0x20 ) ? 1 : 0;
1481 $iReadDir = ( ( $iGen2 & 0xC0 ) / 0x40 ) & 0x03;
1482 $iBdrSL = $iBdr1 & 0x0F;
1483 $iBdrSR = ( ( $iBdr1 & 0xF0 ) / 0x10 ) & 0x0F;
1484 $iBdrST = ( ( $iBdr1 & 0xF00 ) / 0x100 ) & 0x0F;
1485 $iBdrSB = ( ( $iBdr1 & 0xF000 ) / 0x1000 ) & 0x0F;
1486
1487 $iBdrCL = ( ( $iBdr2 & 0x7F ) ) & 0x7F;
1488 $iBdrCR = ( ( $iBdr2 & 0x3F80 ) / 0x80 ) & 0x7F;
1489 $iBdrD = ( ( $iBdr2 & 0xC000 ) / 0x4000 ) & 0x3;
1490
1491 $iBdrCT = ( ( $iBdr3 & 0x7F ) ) & 0x7F;
1492 $iBdrCB = ( ( $iBdr3 & 0x3F80 ) / 0x80 ) & 0x7F;
1493 $iBdrCD = ( ( $iBdr3 & 0x1FC000 ) / 0x4000 ) & 0x7F;
1494 $iBdrSD = ( ( $iBdr3 & 0x1E00000 ) / 0x200000 ) & 0xF;
1495 $iFillP = ( ( $iBdr3 & 0xFC000000 ) / 0x4000000 ) & 0x3F;
1496
1497 $iFillCF = ( $iPtn & 0x7F );
1498 $iFillCB = ( ( $iPtn & 0x3F80 ) / 0x80 ) & 0x7F;
1499 }
1500 else {
1501 my ( $iGen, $iAlign, $iPtn, $iPtn2, $iBdr1, $iBdr2 );
1502
1503 ( $iFnt, $iIdx, $iGen, $iAlign, $iPtn, $iPtn2, $iBdr1, $iBdr2 ) =
1504 unpack( "v8", $sWk );
1505 $iLock = ( $iGen & 0x01 ) ? 1 : 0;
1506 $iHidden = ( $iGen & 0x02 ) ? 1 : 0;
1507 $iStyle = ( $iGen & 0x04 ) ? 1 : 0;
1508 $i123 = ( $iGen & 0x08 ) ? 1 : 0;
1509
1510 $iAlH = ( $iAlign & 0x07 );
1511 $iWrap = ( $iAlign & 0x08 ) ? 1 : 0;
1512 $iAlV = ( $iAlign & 0x70 ) / 0x10;
1513 $iJustL = ( $iAlign & 0x80 ) ? 1 : 0;
1514
1515 $iRotate = ( ( $iAlign & 0x300 ) / 0x100 ) & 0x3;
1516
1517 $iFillCF = ( $iPtn & 0x7F );
1518 $iFillCB = ( ( $iPtn & 0x1F80 ) / 0x80 ) & 0x7F;
1519
1520 $iFillP = ( $iPtn2 & 0x3F );
1521 $iBdrSB = ( ( $iPtn2 & 0x1C0 ) / 0x40 ) & 0x7;
1522 $iBdrCB = ( ( $iPtn2 & 0xFE00 ) / 0x200 ) & 0x7F;
1523
1524 $iBdrST = ( $iBdr1 & 0x07 );
1525 $iBdrSL = ( ( $iBdr1 & 0x38 ) / 0x8 ) & 0x07;
1526 $iBdrSR = ( ( $iBdr1 & 0x1C0 ) / 0x40 ) & 0x07;
1527 $iBdrCT = ( ( $iBdr1 & 0xFE00 ) / 0x200 ) & 0x7F;
1528
1529 $iBdrCL = ( $iBdr2 & 0x7F ) & 0x7F;
1530 $iBdrCR = ( ( $iBdr2 & 0x3F80 ) / 0x80 ) & 0x7F;
1531 }
1532
1533 push @{ $oBook->{Format} }, Spreadsheet::ParseExcel::Format->new(
1534 FontNo => $iFnt,
1535 Font => $oBook->{Font}[$iFnt],
1536 FmtIdx => $iIdx,
1537
1538 Lock => $iLock,
1539 Hidden => $iHidden,
1540 Style => $iStyle,
1541 Key123 => $i123,
1542 AlignH => $iAlH,
1543 Wrap => $iWrap,
1544 AlignV => $iAlV,
1545 JustLast => $iJustL,
1546 Rotate => $iRotate,
1547
1548 Indent => $iInd,
1549 Shrink => $iShrink,
1550 Merge => $iMerge,
1551 ReadDir => $iReadDir,
1552
1553 BdrStyle => [ $iBdrSL, $iBdrSR, $iBdrST, $iBdrSB ],
1554 BdrColor => [ $iBdrCL, $iBdrCR, $iBdrCT, $iBdrCB ],
1555 BdrDiag => [ $iBdrD, $iBdrSD, $iBdrCD ],
1556 Fill => [ $iFillP, $iFillCF, $iFillCB ],
1557 );
1558}
1559
1560#------------------------------------------------------------------------------
1561# _subFormat (for Spreadsheet::ParseExcel) DK: P336
1562#------------------------------------------------------------------------------
1563sub _subFormat {
1564
1565 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1566 my $sFmt;
1567
1568 if ( $oBook->{BIFFVersion} <= verBIFF5 ) {
1569 $sFmt = substr( $sWk, 3, unpack( 'c', substr( $sWk, 2, 1 ) ) );
1570 $sFmt = $oBook->{FmtClass}->TextFmt( $sFmt, '_native_' );
1571 }
1572 else {
1573 $sFmt = _convBIFF8String( $oBook, substr( $sWk, 2 ) );
1574 }
1575
1576 my $format_index = unpack( 'v', substr( $sWk, 0, 2 ) );
1577
1578 # Excel 4 and earlier used an index of 0 to indicate that a built-in format
1579 # that was stored implicitly.
1580 if ( $oBook->{BIFFVersion} <= verBIFF4 && $format_index == 0 ) {
1581 $format_index = keys %{ $oBook->{FormatStr} };
1582 }
1583
1584 $oBook->{FormatStr}->{$format_index} = $sFmt;
1585}
1586
1587#------------------------------------------------------------------------------
1588# _subPalette (for Spreadsheet::ParseExcel) DK: P393
1589#------------------------------------------------------------------------------
1590sub _subPalette {
1591 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1592 for ( my $i = 0 ; $i < unpack( 'v', $sWk ) ; $i++ ) {
1593
1594 # push @aColor, unpack('H6', substr($sWk, $i*4+2));
1595 $aColor[ $i + 8 ] = unpack( 'H6', substr( $sWk, $i * 4 + 2 ) );
1596 }
1597}
1598
1599#------------------------------------------------------------------------------
1600# _subFont (for Spreadsheet::ParseExcel) DK:P333
1601#------------------------------------------------------------------------------
1602sub _subFont {
1603 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1604 my ( $iHeight, $iAttr, $iCIdx, $iBold, $iSuper, $iUnderline, $sFntName );
1605 my ( $bBold, $bItalic, $bUnderline, $bStrikeout );
1606
1607 if ( $oBook->{BIFFVersion} == verBIFF8 ) {
1608 ( $iHeight, $iAttr, $iCIdx, $iBold, $iSuper, $iUnderline ) =
1609 unpack( "v5c", $sWk );
1610 my ( $iSize, $iHigh ) = unpack( 'cc', substr( $sWk, 14, 2 ) );
1611 if ( $iHigh ) {
1612 $sFntName = substr( $sWk, 16, $iSize * 2 );
1613 _SwapForUnicode( \$sFntName );
1614 $sFntName = $oBook->{FmtClass}->TextFmt( $sFntName, 'ucs2' );
1615 }
1616 else {
1617 $sFntName = substr( $sWk, 16, $iSize );
1618 $sFntName = $oBook->{FmtClass}->TextFmt( $sFntName, '_native_' );
1619 }
1620 $bBold = ( $iBold >= 0x2BC ) ? 1 : 0;
1621 $bItalic = ( $iAttr & 0x02 ) ? 1 : 0;
1622 $bStrikeout = ( $iAttr & 0x08 ) ? 1 : 0;
1623 $bUnderline = ( $iUnderline ) ? 1 : 0;
1624 }
1625 elsif ( $oBook->{BIFFVersion} == verBIFF5 ) {
1626 ( $iHeight, $iAttr, $iCIdx, $iBold, $iSuper, $iUnderline ) =
1627 unpack( "v5c", $sWk );
1628 $sFntName =
1629 $oBook->{FmtClass}
1630 ->TextFmt( substr( $sWk, 15, unpack( "c", substr( $sWk, 14, 1 ) ) ),
1631 '_native_' );
1632 $bBold = ( $iBold >= 0x2BC ) ? 1 : 0;
1633 $bItalic = ( $iAttr & 0x02 ) ? 1 : 0;
1634 $bStrikeout = ( $iAttr & 0x08 ) ? 1 : 0;
1635 $bUnderline = ( $iUnderline ) ? 1 : 0;
1636 }
1637 else {
1638 ( $iHeight, $iAttr ) = unpack( "v2", $sWk );
1639 $iCIdx = undef;
1640 $iSuper = 0;
1641
1642 $bBold = ( $iAttr & 0x01 ) ? 1 : 0;
1643 $bItalic = ( $iAttr & 0x02 ) ? 1 : 0;
1644 $bUnderline = ( $iAttr & 0x04 ) ? 1 : 0;
1645 $bStrikeout = ( $iAttr & 0x08 ) ? 1 : 0;
1646
1647 $sFntName = substr( $sWk, 5, unpack( "c", substr( $sWk, 4, 1 ) ) );
1648 }
1649 push @{ $oBook->{Font} }, Spreadsheet::ParseExcel::Font->new(
1650 Height => $iHeight / 20.0,
1651 Attr => $iAttr,
1652 Color => $iCIdx,
1653 Super => $iSuper,
1654 UnderlineStyle => $iUnderline,
1655 Name => $sFntName,
1656
1657 Bold => $bBold,
1658 Italic => $bItalic,
1659 Underline => $bUnderline,
1660 Strikeout => $bStrikeout,
1661 );
1662
1663 #Skip Font[4]
1664 push @{ $oBook->{Font} }, {} if ( scalar( @{ $oBook->{Font} } ) == 4 );
1665
1666}
1667
1668#------------------------------------------------------------------------------
1669# _subBoundSheet (for Spreadsheet::ParseExcel): DK: P307
1670#------------------------------------------------------------------------------
1671sub _subBoundSheet {
1672 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1673 my ( $iPos, $iGr, $iKind ) = unpack( "Lc2", $sWk );
1674 $iKind &= 0x0F;
1675 return if ( ( $iKind != 0x00 ) && ( $iKind != 0x01 ) );
1676
1677 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1678 my ( $iSize, $iUni ) = unpack( "cc", substr( $sWk, 6, 2 ) );
1679 my $sWsName = substr( $sWk, 8 );
1680 if ( $iUni & 0x01 ) {
1681 _SwapForUnicode( \$sWsName );
1682 $sWsName = $oBook->{FmtClass}->TextFmt( $sWsName, 'ucs2' );
1683 }
1684 $oBook->{Worksheet}[ $oBook->{SheetCount} ] =
1685 Spreadsheet::ParseExcel::Worksheet->new(
1686 Name => $sWsName,
1687 Kind => $iKind,
1688 _Pos => $iPos,
1689 _Book => $oBook,
1690 _SheetNo => $oBook->{SheetCount},
1691 );
1692 }
1693 else {
1694 $oBook->{Worksheet}[ $oBook->{SheetCount} ] =
1695 Spreadsheet::ParseExcel::Worksheet->new(
1696 Name =>
1697 $oBook->{FmtClass}->TextFmt( substr( $sWk, 7 ), '_native_' ),
1698 Kind => $iKind,
1699 _Pos => $iPos,
1700 _Book => $oBook,
1701 _SheetNo => $oBook->{SheetCount},
1702 );
1703 }
1704 $oBook->{SheetCount}++;
1705}
1706
1707#------------------------------------------------------------------------------
1708# _subHeader (for Spreadsheet::ParseExcel) DK: P340
1709#------------------------------------------------------------------------------
1710sub _subHeader {
1711 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1712 return undef unless ( defined $oBook->{_CurSheet} );
1713 my $sW;
1714
1715 if ( !defined $sWk ) {
1716 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Header} = undef;
1717 return;
1718 }
1719
1720 #BIFF8
1721 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1722 $sW = _convBIFF8String( $oBook, $sWk );
1723 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Header} =
1724 ( $sW eq "\x00" ) ? undef : $sW;
1725 }
1726
1727 #Before BIFF8
1728 else {
1729 my ( $iLen ) = unpack( "c", $sWk );
1730 $sW =
1731 $oBook->{FmtClass}->TextFmt( substr( $sWk, 1, $iLen ), '_native_' );
1732 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Header} =
1733 ( $sW eq "\x00\x00\x00" ) ? undef : $sW;
1734 }
1735}
1736
1737#------------------------------------------------------------------------------
1738# _subFooter (for Spreadsheet::ParseExcel) DK: P335
1739#------------------------------------------------------------------------------
1740sub _subFooter {
1741 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1742 return undef unless ( defined $oBook->{_CurSheet} );
1743 my $sW;
1744
1745 if ( !defined $sWk ) {
1746 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Footer} = undef;
1747 return;
1748 }
1749
1750 #BIFF8
1751 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1752 $sW = _convBIFF8String( $oBook, $sWk );
1753 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Footer} =
1754 ( $sW eq "\x00" ) ? undef : $sW;
1755 }
1756
1757 #Before BIFF8
1758 else {
1759 my ( $iLen ) = unpack( "c", $sWk );
1760 $sW =
1761 $oBook->{FmtClass}->TextFmt( substr( $sWk, 1, $iLen ), '_native_' );
1762 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Footer} =
1763 ( $sW eq "\x00\x00\x00" ) ? undef : $sW;
1764 }
1765}
1766
1767#------------------------------------------------------------------------------
1768# _subHPageBreak (for Spreadsheet::ParseExcel) DK: P341
1769#------------------------------------------------------------------------------
1770sub _subHPageBreak {
1771 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1772 my @aBreak;
1773 my $iCnt = unpack( "v", $sWk );
1774
1775 return undef unless ( defined $oBook->{_CurSheet} );
1776
1777 #BIFF8
1778 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1779 for ( my $i = 0 ; $i < $iCnt ; $i++ ) {
1780 my ( $iRow, $iColB, $iColE ) =
1781 unpack( 'v3', substr( $sWk, 2 + $i * 6, 6 ) );
1782
1783 # push @aBreak, [$iRow, $iColB, $iColE];
1784 push @aBreak, $iRow;
1785 }
1786 }
1787
1788 #Before BIFF8
1789 else {
1790 for ( my $i = 0 ; $i < $iCnt ; $i++ ) {
1791 my ( $iRow ) = unpack( 'v', substr( $sWk, 2 + $i * 2, 2 ) );
1792 push @aBreak, $iRow;
1793
1794 # push @aBreak, [$iRow, 0, 255];
1795 }
1796 }
1797 @aBreak = sort { $a <=> $b } @aBreak;
1798 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{HPageBreak} = \@aBreak;
1799}
1800
1801#------------------------------------------------------------------------------
1802# _subVPageBreak (for Spreadsheet::ParseExcel) DK: P447
1803#------------------------------------------------------------------------------
1804sub _subVPageBreak {
1805 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1806 return undef unless ( defined $oBook->{_CurSheet} );
1807
1808 my @aBreak;
1809 my $iCnt = unpack( "v", $sWk );
1810
1811 #BIFF8
1812 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1813 for ( my $i = 0 ; $i < $iCnt ; $i++ ) {
1814 my ( $iCol, $iRowB, $iRowE ) =
1815 unpack( 'v3', substr( $sWk, 2 + $i * 6, 6 ) );
1816 push @aBreak, $iCol;
1817
1818 # push @aBreak, [$iCol, $iRowB, $iRowE];
1819 }
1820 }
1821
1822 #Before BIFF8
1823 else {
1824 for ( my $i = 0 ; $i < $iCnt ; $i++ ) {
1825 my ( $iCol ) = unpack( 'v', substr( $sWk, 2 + $i * 2, 2 ) );
1826 push @aBreak, $iCol;
1827
1828 # push @aBreak, [$iCol, 0, 65535];
1829 }
1830 }
1831 @aBreak = sort { $a <=> $b } @aBreak;
1832 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{VPageBreak} = \@aBreak;
1833}
1834
1835#------------------------------------------------------------------------------
1836# _subMargin (for Spreadsheet::ParseExcel) DK: P306, 345, 400, 440
1837#------------------------------------------------------------------------------
1838sub _subMargin {
1839 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1840 return undef unless ( defined $oBook->{_CurSheet} );
1841
1842 # The "Mergin" options are a workaround for a backward compatible typo.
1843
1844 my $dWk = _convDval( substr( $sWk, 0, 8 ) );
1845 if ( $bOp == 0x26 ) {
1846 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{LeftMergin} = $dWk;
1847 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{LeftMargin} = $dWk;
1848 }
1849 elsif ( $bOp == 0x27 ) {
1850 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RightMergin} = $dWk;
1851 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RightMargin} = $dWk;
1852 }
1853 elsif ( $bOp == 0x28 ) {
1854 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{TopMergin} = $dWk;
1855 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{TopMargin} = $dWk;
1856 }
1857 elsif ( $bOp == 0x29 ) {
1858 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{BottomMergin} = $dWk;
1859 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{BottomMargin} = $dWk;
1860 }
1861}
1862
1863#------------------------------------------------------------------------------
1864# _subHcenter (for Spreadsheet::ParseExcel) DK: P340
1865#------------------------------------------------------------------------------
1866sub _subHcenter {
1867 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1868 return undef unless ( defined $oBook->{_CurSheet} );
1869
1870 my $iWk = unpack( "v", $sWk );
1871 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{HCenter} = $iWk;
1872
1873}
1874
1875#------------------------------------------------------------------------------
1876# _subVcenter (for Spreadsheet::ParseExcel) DK: P447
1877#------------------------------------------------------------------------------
1878sub _subVcenter {
1879 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1880 return undef unless ( defined $oBook->{_CurSheet} );
1881
1882 my $iWk = unpack( "v", $sWk );
1883 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{VCenter} = $iWk;
1884}
1885
1886#------------------------------------------------------------------------------
1887# _subPrintGridlines (for Spreadsheet::ParseExcel) DK: P397
1888#------------------------------------------------------------------------------
1889sub _subPrintGridlines {
1890 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1891 return undef unless ( defined $oBook->{_CurSheet} );
1892
1893 my $iWk = unpack( "v", $sWk );
1894 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{PrintGrid} = $iWk;
1895
1896}
1897
1898#------------------------------------------------------------------------------
1899# _subPrintHeaders (for Spreadsheet::ParseExcel) DK: P397
1900#------------------------------------------------------------------------------
1901sub _subPrintHeaders {
1902 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1903 return undef unless ( defined $oBook->{_CurSheet} );
1904
1905 my $iWk = unpack( "v", $sWk );
1906 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{PrintHeaders} = $iWk;
1907}
1908
1909#------------------------------------------------------------------------------
1910# _subSETUP (for Spreadsheet::ParseExcel) DK: P409
1911#------------------------------------------------------------------------------
1912sub _subSETUP {
1913 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1914 return undef unless ( defined $oBook->{_CurSheet} );
1915
1916 # Workaround for some apps and older Excels that don't write a
1917 # complete SETUP record.
1918 return undef if $bLen != 34;
1919
1920 my $oWkS = $oBook->{Worksheet}[ $oBook->{_CurSheet} ];
1921 my $iGrBit;
1922
1923 (
1924 $oWkS->{PaperSize}, $oWkS->{Scale}, $oWkS->{PageStart},
1925 $oWkS->{FitWidth}, $oWkS->{FitHeight}, $iGrBit,
1926 $oWkS->{Res}, $oWkS->{VRes},
1927 ) = unpack( 'v8', $sWk );
1928
1929 $oWkS->{HeaderMargin} = _convDval( substr( $sWk, 16, 8 ) );
1930 $oWkS->{FooterMargin} = _convDval( substr( $sWk, 24, 8 ) );
1931 $oWkS->{Copis} = unpack( 'v2', substr( $sWk, 32, 2 ) );
1932 $oWkS->{LeftToRight} = ( ( $iGrBit & 0x01 ) ? 1 : 0 );
1933 $oWkS->{Landscape} = ( ( $iGrBit & 0x02 ) ? 1 : 0 );
1934 $oWkS->{NoPls} = ( ( $iGrBit & 0x04 ) ? 1 : 0 );
1935 $oWkS->{NoColor} = ( ( $iGrBit & 0x08 ) ? 1 : 0 );
1936 $oWkS->{Draft} = ( ( $iGrBit & 0x10 ) ? 1 : 0 );
1937 $oWkS->{Notes} = ( ( $iGrBit & 0x20 ) ? 1 : 0 );
1938 $oWkS->{NoOrient} = ( ( $iGrBit & 0x40 ) ? 1 : 0 );
1939 $oWkS->{UsePage} = ( ( $iGrBit & 0x80 ) ? 1 : 0 );
1940
1941 # The NoPls flag indicates that the values have not been taken from an
1942 # actual printer and thus may not be accurate.
1943
1944 # Set default scale if NoPls otherwise it may be an invalid value of 0XFF.
1945 $oWkS->{Scale} = 100 if $oWkS->{NoPls};
1946
1947 # Workaround for a backward compatible typo.
1948 $oWkS->{HeaderMergin} = $oWkS->{HeaderMargin};
1949 $oWkS->{FooterMergin} = $oWkS->{FooterMargin};
1950
1951}
1952
1953#------------------------------------------------------------------------------
1954# _subName (for Spreadsheet::ParseExcel) DK: P350
1955#------------------------------------------------------------------------------
1956sub _subName {
1957 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
1958 my (
1959 $iGrBit, $cKey, $cCh, $iCce, $ixAls,
1960 $iTab, $cchCust, $cchDsc, $cchHep, $cchStatus
1961 ) = unpack( 'vc2v3c4', $sWk );
1962
1963 #Builtin Name + Length == 1
1964 if ( ( $iGrBit & 0x20 ) && ( $cCh == 1 ) ) {
1965
1966 #BIFF8
1967 if ( $oBook->{BIFFVersion} >= verBIFF8 ) {
1968 my $iName = unpack( 'n', substr( $sWk, 14 ) );
1969 my $iSheet = unpack( 'v', substr( $sWk, 8 ) ) - 1;
1970
1971 # Workaround for mal-formed Excel workbooks where Print_Title is
1972 # set as Global (i.e. itab = 0). Note, this will have to be
1973 # treated differently when we get around to handling global names.
1974 return undef if $iSheet == -1;
1975
1976 if ( $iName == 6 ) { #PrintArea
1977 my ( $iSheetW, $raArea ) = _ParseNameArea( substr( $sWk, 16 ) );
1978 $oBook->{PrintArea}[$iSheet] = $raArea;
1979 }
1980 elsif ( $iName == 7 ) { #Title
1981 my ( $iSheetW, $raArea ) = _ParseNameArea( substr( $sWk, 16 ) );
1982 my @aTtlR = ();
1983 my @aTtlC = ();
1984 foreach my $raI ( @$raArea ) {
1985 if ( $raI->[3] == 0xFF ) { #Row Title
1986 push @aTtlR, [ $raI->[0], $raI->[2] ];
1987 }
1988 else { #Col Title
1989 push @aTtlC, [ $raI->[1], $raI->[3] ];
1990 }
1991 }
1992 $oBook->{PrintTitle}[$iSheet] =
1993 { Row => \@aTtlR, Column => \@aTtlC };
1994 }
1995 }
1996 else {
1997 my $iName = unpack( 'c', substr( $sWk, 14 ) );
1998 if ( $iName == 6 ) { #PrintArea
1999 my ( $iSheet, $raArea ) =
2000 _ParseNameArea95( substr( $sWk, 15 ) );
2001 $oBook->{PrintArea}[$iSheet] = $raArea;
2002 }
2003 elsif ( $iName == 7 ) { #Title
2004 my ( $iSheet, $raArea ) =
2005 _ParseNameArea95( substr( $sWk, 15 ) );
2006 my @aTtlR = ();
2007 my @aTtlC = ();
2008 foreach my $raI ( @$raArea ) {
2009 if ( $raI->[3] == 0xFF ) { #Row Title
2010 push @aTtlR, [ $raI->[0], $raI->[2] ];
2011 }
2012 else { #Col Title
2013 push @aTtlC, [ $raI->[1], $raI->[3] ];
2014 }
2015 }
2016 $oBook->{PrintTitle}[$iSheet] =
2017 { Row => \@aTtlR, Column => \@aTtlC };
2018 }
2019 }
2020 }
2021}
2022
2023#------------------------------------------------------------------------------
2024# ParseNameArea (for Spreadsheet::ParseExcel) DK: 494 (ptgAread3d)
2025#------------------------------------------------------------------------------
2026sub _ParseNameArea {
2027 my ( $sObj ) = @_;
2028 my ( $iOp );
2029 my @aRes = ();
2030 $iOp = unpack( 'C', $sObj );
2031 my $iSheet;
2032 if ( $iOp == 0x3b ) {
2033 my ( $iWkS, $iRs, $iRe, $iCs, $iCe ) =
2034 unpack( 'v5', substr( $sObj, 1 ) );
2035 $iSheet = $iWkS;
2036 push @aRes, [ $iRs, $iCs, $iRe, $iCe ];
2037 }
2038 elsif ( $iOp == 0x29 ) {
2039 my $iLen = unpack( 'v', substr( $sObj, 1, 2 ) );
2040 my $iSt = 0;
2041 while ( $iSt < $iLen ) {
2042 my ( $iOpW, $iWkS, $iRs, $iRe, $iCs, $iCe ) =
2043 unpack( 'cv5', substr( $sObj, $iSt + 3, 11 ) );
2044
2045 if ( $iOpW == 0x3b ) {
2046 $iSheet = $iWkS;
2047 push @aRes, [ $iRs, $iCs, $iRe, $iCe ];
2048 }
2049
2050 if ( $iSt == 0 ) {
2051 $iSt += 11;
2052 }
2053 else {
2054 $iSt += 12; #Skip 1 byte;
2055 }
2056 }
2057 }
2058 return ( $iSheet, \@aRes );
2059}
2060
2061#------------------------------------------------------------------------------
2062# ParseNameArea95 (for Spreadsheet::ParseExcel) DK: 494 (ptgAread3d)
2063#------------------------------------------------------------------------------
2064sub _ParseNameArea95 {
2065 my ( $sObj ) = @_;
2066 my ( $iOp );
2067 my @aRes = ();
2068 $iOp = unpack( 'C', $sObj );
2069 my $iSheet;
2070 if ( $iOp == 0x3b ) {
2071 $iSheet = unpack( 'v', substr( $sObj, 11, 2 ) );
2072 my ( $iRs, $iRe, $iCs, $iCe ) =
2073 unpack( 'v2C2', substr( $sObj, 15, 6 ) );
2074 push @aRes, [ $iRs, $iCs, $iRe, $iCe ];
2075 }
2076 elsif ( $iOp == 0x29 ) {
2077 my $iLen = unpack( 'v', substr( $sObj, 1, 2 ) );
2078 my $iSt = 0;
2079 while ( $iSt < $iLen ) {
2080 my $iOpW = unpack( 'c', substr( $sObj, $iSt + 3, 6 ) );
2081 $iSheet = unpack( 'v', substr( $sObj, $iSt + 14, 2 ) );
2082 my ( $iRs, $iRe, $iCs, $iCe ) =
2083 unpack( 'v2C2', substr( $sObj, $iSt + 18, 6 ) );
2084 push @aRes, [ $iRs, $iCs, $iRe, $iCe ] if ( $iOpW == 0x3b );
2085
2086 if ( $iSt == 0 ) {
2087 $iSt += 21;
2088 }
2089 else {
2090 $iSt += 22; #Skip 1 byte;
2091 }
2092 }
2093 }
2094 return ( $iSheet, \@aRes );
2095}
2096
2097#------------------------------------------------------------------------------
2098# _subBOOL (for Spreadsheet::ParseExcel) DK: P452
2099#------------------------------------------------------------------------------
2100sub _subWSBOOL {
2101 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
2102 return undef unless ( defined $oBook->{_CurSheet} );
2103
2104 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{PageFit} =
2105 ( ( unpack( 'v', $sWk ) & 0x100 ) ? 1 : 0 );
2106}
2107
2108#------------------------------------------------------------------------------
2109# _subMergeArea (for Spreadsheet::ParseExcel) DK: (Not)
2110#------------------------------------------------------------------------------
2111sub _subMergeArea {
2112 my ( $oBook, $bOp, $bLen, $sWk ) = @_;
2113 return undef unless ( defined $oBook->{_CurSheet} );
2114
2115 my $iCnt = unpack( "v", $sWk );
2116 my $oWkS = $oBook->{Worksheet}[ $oBook->{_CurSheet} ];
2117 $oWkS->{MergedArea} = [] unless ( defined $oWkS->{MergedArea} );
2118 for ( my $i = 0 ; $i < $iCnt ; $i++ ) {
2119 my ( $iRs, $iRe, $iCs, $iCe ) =
2120 unpack( 'v4', substr( $sWk, $i * 8 + 2, 8 ) );
2121 for ( my $iR = $iRs ; $iR <= $iRe ; $iR++ ) {
2122 for ( my $iC = $iCs ; $iC <= $iCe ; $iC++ ) {
2123 $oWkS->{Cells}[$iR][$iC]->{Merged} = 1
2124 if ( defined $oWkS->{Cells}[$iR][$iC] );
2125 }
2126 }
2127 push @{ $oWkS->{MergedArea} }, [ $iRs, $iCs, $iRe, $iCe ];
2128 }
2129}
2130
2131#------------------------------------------------------------------------------
2132# DecodeBoolErr (for Spreadsheet::ParseExcel) DK: P306
2133#------------------------------------------------------------------------------
2134sub DecodeBoolErr {
2135 my ( $iVal, $iFlg ) = @_;
2136 if ( $iFlg ) { # ERROR
2137 if ( $iVal == 0x00 ) {
2138 return "#NULL!";
2139 }
2140 elsif ( $iVal == 0x07 ) {
2141 return "#DIV/0!";
2142 }
2143 elsif ( $iVal == 0x0F ) {
2144 return "#VALUE!";
2145 }
2146 elsif ( $iVal == 0x17 ) {
2147 return "#REF!";
2148 }
2149 elsif ( $iVal == 0x1D ) {
2150 return "#NAME?";
2151 }
2152 elsif ( $iVal == 0x24 ) {
2153 return "#NUM!";
2154 }
2155 elsif ( $iVal == 0x2A ) {
2156 return "#N/A!";
2157 }
2158 else {
2159 return "#ERR";
2160 }
2161 }
2162 else {
2163 return ( $iVal ) ? "TRUE" : "FALSE";
2164 }
2165}
2166
2167###############################################################################
2168#
2169# _decode_rk_number()
2170#
2171# Convert an encoded RK number into a real number. The RK encoding is
2172# explained in some detail in the MS docs. It is a way of storing applicable
2173# ints and doubles in 32bits (30 data + 2 info bits) in order to save space.
2174#
2175sub _decode_rk_number {
2176
2177 my $rk_number = shift;
2178 my $number;
2179
2180 # Check the main RK type.
2181 if ( $rk_number & 0x02 ) {
2182
2183 # RK Type 2 and 4, a packed integer.
2184
2185 # Shift off the info bits.
2186 $number = $rk_number >> 2;
2187
2188 # Convert from unsigned to signed if required.
2189 $number -= 0x40000000 if $number & 0x20000000;
2190 }
2191 else {
2192
2193 # RK Type 1 and 3, a truncated IEEE Double.
2194
2195 # Pack the RK number into the high 30 bits of an IEEE double.
2196 $number = pack "VV", 0x0000, $rk_number & 0xFFFFFFFC;
2197
2198 # Reverse the packed IEEE double on big-endian machines.
2199 $number = reverse $number if $BIGENDIAN;
2200
2201 # Unpack the number.
2202 $number = unpack "d", $number;
2203 }
2204
2205 # RK Types 3 and 4 were multiplied by 100 prior to encoding.
2206 $number /= 100 if $rk_number & 0x01;
2207
2208 return $number;
2209}
2210
2211###############################################################################
2212#
2213# _subStrWk()
2214#
2215# Extract the workbook strings from the SST (Shared String Table) record and
2216# any following CONTINUE records.
2217#
2218# The workbook strings are initially contained in the SST block but may also
2219# occupy one or more CONTINUE blocks. Reading the CONTINUE blocks is made a
2220# little tricky by the fact that they can contain an additional initial byte
2221# if a string is continued from a previous block.
2222#
2223# Parsing is further complicated by the fact that the continued section of the
2224# string may have a different encoding (ASCII or UTF-8) from the previous
2225# section. Excel does this to save space.
2226#
2227sub _subStrWk {
2228
2229 my ( $self, $biff_data, $is_continue ) = @_;
2230
2231 if ( $is_continue ) {
2232
2233 # We are reading a CONTINUE record.
2234
2235 if ( $self->{_buffer} eq '' ) {
2236
2237 # A CONTINUE block with no previous SST.
2238 $self->{_buffer} .= $biff_data;
2239 }
2240 elsif ( !defined $self->{_string_continued} ) {
2241
2242 # The CONTINUE block starts with a new (non-continued) string.
2243
2244 # Strip the Grbit byte and store the string data.
2245 $self->{_buffer} .= substr $biff_data, 1;
2246 }
2247 else {
2248
2249 # A CONTINUE block that starts with a continued string.
2250
2251 # The first byte (Grbit) of the CONTINUE record indicates if (0)
2252 # the continued string section is single bytes or (1) double bytes.
2253 my $grbit = ord $biff_data;
2254
2255 my ( $str_position, $str_length ) = @{ $self->{_previous_info} };
2256 my $buff_length = length $self->{_buffer};
2257
2258 if ( $buff_length >= ( $str_position + $str_length ) ) {
2259
2260 # Not in a string.
2261 $self->{_buffer} .= $biff_data;
2262 }
2263 elsif ( ( $self->{_string_continued} & 0x01 ) == ( $grbit & 0x01 ) )
2264 {
2265
2266 # Same encoding as the previous block of the string.
2267 $self->{_buffer} .= substr( $biff_data, 1 );
2268 }
2269 else {
2270
2271 # Different encoding to the previous block of the string.
2272 if ( $grbit & 0x01 ) {
2273
2274 # Current block is UTF-16, previous was ASCII.
2275 my ( undef, $cch ) = unpack 'vc', $self->{_buffer};
2276 substr( $self->{_buffer}, 2, 1 ) = pack( 'C', $cch | 0x01 );
2277
2278 # Convert the previous ASCII, single character, portion of
2279 # the string into a double character UTF-16 string by
2280 # inserting zero bytes.
2281 for (
2282 my $i = ( $buff_length - $str_position ) ;
2283 $i >= 1 ;
2284 $i--
2285 )
2286 {
2287 substr( $self->{_buffer}, $str_position + $i, 0 ) =
2288 "\x00";
2289 }
2290
2291 }
2292 else {
2293
2294 # Current block is ASCII, previous was UTF-16.
2295
2296 # Convert the current ASCII, single character, portion of
2297 # the string into a double character UTF-16 string by
2298 # inserting null bytes.
2299 my $change_length =
2300 ( $str_position + $str_length ) - $buff_length;
2301
2302 # Length of the current CONTINUE record data.
2303 my $biff_length = length $biff_data;
2304
2305 # Restrict the portion to be changed to the current block
2306 # if the string extends over more than one block.
2307 if ( $change_length > ( $biff_length - 1 ) * 2 ) {
2308 $change_length = ( $biff_length - 1 ) * 2;
2309 }
2310
2311 # Insert the null bytes.
2312 for ( my $i = ( $change_length / 2 ) ; $i >= 1 ; $i-- ) {
2313 substr( $biff_data, $i + 1, 0 ) = "\x00";
2314 }
2315
2316 }
2317
2318 # Strip the Grbit byte and store the string data.
2319 $self->{_buffer} .= substr $biff_data, 1;
2320 }
2321 }
2322 }
2323 else {
2324
2325 # Not a CONTINUE block therefore an SST block.
2326 $self->{_buffer} .= $biff_data;
2327 }
2328
2329 # Reset the state variables.
2330 $self->{_string_continued} = undef;
2331 $self->{_previous_info} = undef;
2332
2333 # Extract out any full strings from the current buffer leaving behind a
2334 # partial string that is continued into the next block, or an empty
2335 # buffer is no string is continued.
2336 while ( length $self->{_buffer} >= 4 ) {
2337 my ( $str_info, $length, $str_position, $str_length ) =
2338 _convBIFF8String( $self, $self->{_buffer}, 1 );
2339
2340 if ( defined $str_info->[0] ) {
2341 push @{ $self->{PkgStr} },
2342 {
2343 Text => $str_info->[0],
2344 Unicode => $str_info->[1],
2345 Rich => $str_info->[2],
2346 Ext => $str_info->[3],
2347 };
2348 $self->{_buffer} = substr( $self->{_buffer}, $length );
2349 }
2350 else {
2351 $self->{_string_continued} = $str_info->[1];
2352 $self->{_previous_info} = [ $str_position, $str_length ];
2353 last;
2354 }
2355 }
2356}
2357
2358#------------------------------------------------------------------------------
2359# _SwapForUnicode (for Spreadsheet::ParseExcel)
2360#------------------------------------------------------------------------------
2361sub _SwapForUnicode {
2362 my ( $sObj ) = @_;
2363
2364 # for(my $i = 0; $i<length($$sObj); $i+=2){
2365 for ( my $i = 0 ; $i < ( int( length( $$sObj ) / 2 ) * 2 ) ; $i += 2 ) {
2366 my $sIt = substr( $$sObj, $i, 1 );
2367 substr( $$sObj, $i, 1 ) = substr( $$sObj, $i + 1, 1 );
2368 substr( $$sObj, $i + 1, 1 ) = $sIt;
2369 }
2370}
2371
2372#------------------------------------------------------------------------------
2373# _NewCell (for Spreadsheet::ParseExcel)
2374#------------------------------------------------------------------------------
2375sub _NewCell {
2376 my ( $oBook, $iR, $iC, %rhKey ) = @_;
2377 my ( $sWk, $iLen );
2378 return undef unless ( defined $oBook->{_CurSheet} );
2379
2380 my $FmtClass = $oBook->{FmtClass};
2381 $rhKey{Type} =
2382 $FmtClass->ChkType( $rhKey{Numeric}, $rhKey{Format}{FmtIdx} );
2383 my $FmtStr = $oBook->{FormatStr}{ $rhKey{Format}{FmtIdx} };
2384
2385 # Set "Date" type if required for numbers in a MulRK BIFF block.
2386 if ( defined $FmtStr && $rhKey{Type} eq "Numeric" ) {
2387
2388 # Match a range of possible date formats. Note: this isn't important
2389 # except for reporting. The number will still be converted to a date
2390 # by ExcelFmt() even if 'Type' isn't set to 'Date'.
2391 if ( $FmtStr =~ m{^[dmy][-\\/dmy]*$}i ) {
2392 $rhKey{Type} = "Date";
2393 }
2394 }
2395
2396 my $oCell = Spreadsheet::ParseExcel::Cell->new(
2397 Val => $rhKey{Val},
2398 FormatNo => $rhKey{FormatNo},
2399 Format => $rhKey{Format},
2400 Code => $rhKey{Code},
2401 Type => $rhKey{Type},
2402 );
2403 $oCell->{_Kind} = $rhKey{Kind};
2404 $oCell->{_Value} = $FmtClass->ValFmt( $oCell, $oBook );
2405 if ( $rhKey{Rich} ) {
2406 my @aRich = ();
2407 my $sRich = $rhKey{Rich};
2408 for ( my $iWk = 0 ; $iWk < length( $sRich ) ; $iWk += 4 ) {
2409 my ( $iPos, $iFnt ) = unpack( 'v2', substr( $sRich, $iWk ) );
2410 push @aRich, [ $iPos, $oBook->{Font}[$iFnt] ];
2411 }
2412 $oCell->{Rich} = \@aRich;
2413 }
2414
2415 if ( defined $_CellHandler ) {
2416 if ( defined $_Object ) {
2417 no strict;
2418 ref( $_CellHandler ) eq "CODE"
2419 ? $_CellHandler->(
2420 $_Object, $oBook, $oBook->{_CurSheet}, $iR, $iC, $oCell
2421 )
2422 : $_CellHandler->callback( $_Object, $oBook, $oBook->{_CurSheet},
2423 $iR, $iC, $oCell );
2424 }
2425 else {
2426 $_CellHandler->( $oBook, $oBook->{_CurSheet}, $iR, $iC, $oCell );
2427 }
2428 }
2429 unless ( $_NotSetCell ) {
2430 $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{Cells}[$iR][$iC] = $oCell;
2431 }
2432 return $oCell;
2433}
2434
2435#------------------------------------------------------------------------------
2436# ColorIdxToRGB (for Spreadsheet::ParseExcel)
2437#
2438# TODO JMN Make this a Workbook method and re-document.
2439#
2440#------------------------------------------------------------------------------
2441sub ColorIdxToRGB {
2442 my ( $sPkg, $iIdx ) = @_;
2443 return ( ( defined $aColor[$iIdx] ) ? $aColor[$iIdx] : $aColor[0] );
2444}
2445
2446
2447###############################################################################
2448#
2449# error().
2450#
2451# Return an error string for a failed parse().
2452#
2453sub error {
2454
2455 my $self = shift;
2456
2457 my $parse_error = $self->{_error_status};
2458
2459 if ( exists $error_strings{$parse_error} ) {
2460 return $error_strings{$parse_error};
2461 }
2462 else {
2463 return 'Unknown parse error';
2464 }
2465}
2466
2467
2468###############################################################################
2469#
2470# error_code().
2471#
2472# Return an error code for a failed parse().
2473#
2474sub error_code {
2475
2476 my $self = shift;
2477
2478 return $self->{_error_status};
2479}
2480
2481
2482###############################################################################
2483#
2484# Mapping between legacy method names and new names.
2485#
2486{
2487 no warnings; # Ignore warnings about variables used only once.
2488 *Parse = *parse;
2489}
2490
24911;
2492
2493__END__
2494
2495=head1 NAME
2496
2497Spreadsheet::ParseExcel - Read information from an Excel file.
2498
2499=head1 SYNOPSIS
2500
2501 #!/usr/bin/perl -w
2502
2503 use strict;
2504 use Spreadsheet::ParseExcel;
2505
2506 my $parser = Spreadsheet::ParseExcel->new();
2507 my $workbook = $parser->parse('Book1.xls');
2508
2509 if ( !defined $workbook ) {
2510 die $parser->error(), ".\n";
2511 }
2512
2513 for my $worksheet ( $workbook->worksheets() ) {
2514
2515 my ( $row_min, $row_max ) = $worksheet->row_range();
2516 my ( $col_min, $col_max ) = $worksheet->col_range();
2517
2518 for my $row ( $row_min .. $row_max ) {
2519 for my $col ( $col_min .. $col_max ) {
2520
2521 my $cell = $worksheet->get_cell( $row, $col );
2522 next unless $cell;
2523
2524 print "Row, Col = ($row, $col)\n";
2525 print "Value = ", $cell->value(), "\n";
2526 print "Unformatted = ", $cell->unformatted(), "\n";
2527 print "\n";
2528 }
2529 }
2530 }
2531
2532
2533=head1 DESCRIPTION
2534
2535The Spreadsheet::ParseExcel module can be used to read information from Excel 95-2003 binary files.
2536
2537The module cannot read files in the Excel 2007 Open XML XLSX format. See the L<Spreadsheet::XLSX> module instead.
2538
2539=head1 Parser
2540
2541=head2 new()
2542
2543The C<new()> method is used to create a new C<Spreadsheet::ParseExcel> parser object.
2544
2545 my $parser = Spreadsheet::ParseExcel->new();
2546
2547It it possible to pass a password to decrypt an encrypted file:
2548
2549 $parser = Spreadsheet::ParseExcel->new( Password => 'secret' );
2550
2551Only the default Excel encryption scheme is currently supported. See L</Decryption>.
2552
2553As an advanced feature it is also possible to pass a call-back handler to the parser to control the parsing of the spreadsheet.
2554
2555 $parser = Spreadsheet::ParseExcel->new(
2556 CellHandler => \&cell_handler,
2557 NotSetCell => 1,
2558 );
2559
2560The call-back can be used to ignore certain cells or to reduce memory usage. See the section L<Reducing the memory usage of Spreadsheet::ParseExcel> for more information.
2561
2562
2563=head2 parse($filename, $formatter)
2564
2565The Parser C<parse()> method returns a L</Workbook> object.
2566
2567 my $parser = Spreadsheet::ParseExcel->new();
2568 my $workbook = $parser->parse('Book1.xls');
2569
2570If an error occurs C<parse()> returns C<undef>. In general, programs should contain a test for failed parsing as follows:
2571
2572 my $parser = Spreadsheet::ParseExcel->new();
2573 my $workbook = $parser->parse('Book1.xls');
2574
2575 if ( !defined $workbook ) {
2576 die $parser->error(), ".\n";
2577 }
2578
2579The C<$filename> parameter is generally the file to be parsed. However, it can also be a filehandle or a scalar reference.
2580
2581The optional C<$formatter> parameter can be an reference to a L</Formatter Class> to format the value of cells. This is useful for parsing workbooks with Unicode or Asian characters:
2582
2583 my $parser = Spreadsheet::ParseExcel->new();
2584 my $formatter = Spreadsheet::ParseExcel::FmtJapan->new();
2585 my $workbook = $parser->parse( 'Book1.xls', $formatter );
2586
2587The L<Spreadsheet::ParseExcel::FmtJapan> formatter also supports Unicode. If you encounter any encoding problems with the default formatter try that instead.
2588
2589
2590=head2 error()
2591
2592The Parser C<error()> method returns an error string if a C<parse()> fails:
2593
2594 my $parser = Spreadsheet::ParseExcel->new();
2595 my $workbook = $parser->parse('Book1.xls');
2596
2597 if ( !defined $workbook ) {
2598 die $parser->error(), ".\n";
2599 }
2600
2601If you wish to generate you own error string you can use the C<error_code()> method instead (see below). The C<error()> and C<error_code()> values are as follows:
2602
2603 error() error_code()
2604 ======= ============
2605 '' 0
2606 'File not found' 1
2607 'No Excel data found in file' 2
2608 'File is encrypted' 3
2609
2610
2611The C<error_code()> method is explained below.
2612
2613Spreadsheet::ParseExcel will try to decrypt an encrypted Excel file using the default password or a user supplied password passed to C<new()>, see above. If these fail the module will return the C<'File is encrypted'> error. Only the default Excel encryption scheme is currently supported, see L</Decryption>.
2614
2615
2616=head2 error_code()
2617
2618The Parser C<error_code()> method returns an error code if a C<parse()> fails:
2619
2620 my $parser = Spreadsheet::ParseExcel->new();
2621 my $workbook = $parser->parse('Book1.xls');
2622
2623 if ( !defined $workbook ) {
2624 die "Got error code ", $parser->error_code, ".\n";
2625 }
2626
2627This can be useful if you wish to employ you own error strings or error handling methods.
2628
2629
2630=head1 Workbook
2631
2632A C<Spreadsheet::ParseExcel::Workbook> is created via the C<Spreadsheet::ParseExcel> C<parse()> method:
2633
2634 my $parser = Spreadsheet::ParseExcel->new();
2635 my $workbook = $parser->parse('Book1.xls');
2636
2637The main methods of the Workbook class are:
2638
2639 $workbook->worksheets()
2640 $workbook->worksheet()
2641 $workbook->worksheet_count()
2642 $workbook->get_filename()
2643
2644These more commonly used methods of the Workbook class are outlined below. The other, less commonly used, methods are documented in L<Spreadsheet::ParseExcel::Worksheet>.
2645
2646
2647=head2 worksheets()
2648
2649Returns an array of L</Worksheet> objects. This was most commonly used to iterate over the worksheets in a workbook:
2650
2651 for my $worksheet ( $workbook->worksheets() ) {
2652 ...
2653 }
2654
2655
2656=head2 worksheet()
2657
2658The C<worksheet()> method returns a single C<Worksheet> object using either its name or index:
2659
2660 $worksheet = $workbook->worksheet('Sheet1');
2661 $worksheet = $workbook->worksheet(0);
2662
2663Returns C<undef> if the sheet name or index doesn't exist.
2664
2665
2666=head2 worksheet_count()
2667
2668The C<worksheet_count()> method returns the number of Worksheet objects in the Workbook.
2669
2670 my $worksheet_count = $workbook->worksheet_count();
2671
2672
2673=head2 get_filename()
2674
2675The C<get_filename()> method returns the name of the Excel file of C<undef> if the data was read from a filehandle rather than a file.
2676
2677 my $filename = $workbook->get_filename();
2678
2679
2680=head2 Other Workbook Methods
2681
2682For full documentation of the methods available via a Workbook object see L<Spreadsheet::ParseExcel::Workbook>.
2683
2684=head1 Worksheet
2685
2686The C<Spreadsheet::ParseExcel::Worksheet> class encapsulates the properties of an Excel worksheet.
2687
2688A Worksheet object is obtained via the L</worksheets()> or L</worksheet()> methods.
2689
2690 for my $worksheet ( $workbook->worksheets() ) {
2691 ...
2692 }
2693
2694 # Or:
2695
2696 $worksheet = $workbook->worksheet('Sheet1');
2697 $worksheet = $workbook->worksheet(0);
2698
2699The most commonly used methods of the Worksheet class are:
2700
2701 $worksheet->get_cell()
2702 $worksheet->row_range()
2703 $worksheet->col_range()
2704 $worksheet->get_name()
2705
2706The Spreadsheet::ParseExcel::Worksheet class exposes a lot of methods but in general very few are required unless you are writing an advanced filter.
2707
2708The most commonly used methods are detailed below. The others are documented in L<Spreadsheet::ParseExcel::Worksheet>.
2709
2710=head2 get_cell($row, $col)
2711
2712Return the L</Cell> object at row C<$row> and column C<$col> if it is defined. Otherwise returns undef.
2713
2714 my $cell = $worksheet->get_cell($row, $col);
2715
2716
2717=head2 row_range()
2718
2719Returns a two-element list C<($min, $max)> containing the minimum and maximum defined rows in the worksheet. If there is no row defined C<$max> is smaller than C<$min>.
2720
2721 my ( $row_min, $row_max ) = $worksheet->row_range();
2722
2723
2724=head2 col_range()
2725
2726Returns a two-element list C<($min, $max)> containing the minimum and maximum of defined columns in the worksheet. If there is no column defined C<$max> is smaller than C<$min>.
2727
2728 my ( $col_min, $col_max ) = $worksheet->col_range();
2729
2730
2731=head2 get_name()
2732
2733The C<get_name()> method returns the name of the worksheet, such as 'Sheet1'.
2734
2735 my $name = $worksheet->get_name();
2736
2737=head2 Other Worksheet Methods
2738
2739For other, less commonly used, Worksheet methods see L<Spreadsheet::ParseExcel::Worksheet>.
2740
2741=head1 Cell
2742
2743The C<Spreadsheet::ParseExcel::Cell> class has the following main methods.
2744
2745 $cell->value()
2746 $cell->unformatted()
2747
2748=head2 value()
2749
2750The C<value()> method returns the formatted value of the cell.
2751
2752 my $value = $cell->value();
2753
2754Formatted in this sense refers to the numeric format of the cell value. For example a number such as 40177 might be formatted as 40,117, 40117.000 or even as the date 2009/12/30.
2755
2756If the cell doesn't contain a numeric format then the formatted and unformatted cell values are the same, see the C<unformatted()> method below.
2757
2758For a defined C<$cell> the C<value()> method will always return a value.
2759
2760In the case of a cell with formatting but no numeric or string contents the method will return the empty string C<''>.
2761
2762
2763=head2 unformatted()
2764
2765The C<unformatted()> method returns the unformatted value of the cell.
2766
2767 my $unformatted = $cell->unformatted();
2768
2769Returns the cell value without a numeric format. See the C<value()> method above.
2770
2771=head2 Other Cell Methods
2772
2773For other, less commonly used, Worksheet methods see L<Spreadsheet::ParseExcel::Cell>.
2774
2775
2776=head1 Format
2777
2778The C<Spreadsheet::ParseExcel::Format> class has the following properties:
2779
2780=head2 Format properties
2781
2782 $format->{Font}
2783 $format->{AlignH}
2784 $format->{AlignV}
2785 $format->{Indent}
2786 $format->{Wrap}
2787 $format->{Shrink}
2788 $format->{Rotate}
2789 $format->{JustLast}
2790 $format->{ReadDir}
2791 $format->{BdrStyle}
2792 $format->{BdrColor}
2793 $format->{BdrDiag}
2794 $format->{Fill}
2795 $format->{Lock}
2796 $format->{Hidden}
2797 $format->{Style}
2798
2799These properties are generally only of interest to advanced users. Casual users can skip this section.
2800
2801=head2 $format->{Font}
2802
2803Returns the L</Font> object for the Format.
2804
2805=head2 $format->{AlignH}
2806
2807Returns the horizontal alignment of the format where the value has the following meaning:
2808
2809 0 => No alignment
2810 1 => Left
2811 2 => Center
2812 3 => Right
2813 4 => Fill
2814 5 => Justify
2815 6 => Center across
2816 7 => Distributed/Equal spaced
2817
2818=head2 $format->{AlignV}
2819
2820Returns the vertical alignment of the format where the value has the following meaning:
2821
2822 0 => Top
2823 1 => Center
2824 2 => Bottom
2825 3 => Justify
2826 4 => Distributed/Equal spaced
2827
2828=head2 $format->{Indent}
2829
2830Returns the indent level of the C<Left> horizontal alignment.
2831
2832=head2 $format->{Wrap}
2833
2834Returns true if textwrap is on.
2835
2836=head2 $format->{Shrink}
2837
2838Returns true if "Shrink to fit" is set for the format.
2839
2840=head2 $format->{Rotate}
2841
2842Returns the text rotation. In Excel97+, it returns the angle in degrees of the text rotation.
2843
2844In Excel95 or earlier it returns a value as follows:
2845
2846 0 => No rotation
2847 1 => Top down
2848 2 => 90 degrees anti-clockwise,
2849 3 => 90 clockwise
2850
2851=head2 $format->{JustLast}
2852
2853Return true if the "justify last" property is set for the format.
2854
2855=head2 $format->{ReadDir}
2856
2857Returns the direction that the text is read from.
2858
2859=head2 $format->{BdrStyle}
2860
2861Returns an array ref of border styles as follows:
2862
2863 [ $left, $right, $top, $bottom ]
2864
2865=head2 $format->{BdrColor}
2866
2867Returns an array ref of border color indexes as follows:
2868
2869 [ $left, $right, $top, $bottom ]
2870
2871=head2 $format->{BdrDiag}
2872
2873Returns an array ref of diagonal border kind, style and color index as follows:
2874
2875 [$kind, $style, $color ]
2876
2877Where kind is:
2878
2879 0 => None
2880 1 => Right-Down
2881 2 => Right-Up
2882 3 => Both
2883
2884=head2 $format->{Fill}
2885
2886Returns an array ref of fill pattern and color indexes as follows:
2887
2888 [ $pattern, $front_color, $back_color ]
2889
2890=head2 $format->{Lock}
2891
2892Returns true if the cell is locked.
2893
2894=head2 $format->{Hidden}
2895
2896Returns true if the cell is Hidden.
2897
2898=head2 $format->{Style}
2899
2900Returns true if the format is a Style format.
2901
2902
2903
2904
2905=head1 Font
2906
2907I<Spreadsheet::ParseExcel::Font>
2908
2909Format class has these properties:
2910
2911=head1 Font Properties
2912
2913 $font->{Name}
2914 $font->{Bold}
2915 $font->{Italic}
2916 $font->{Height}
2917 $font->{Underline}
2918 $font->{UnderlineStyle}
2919 $font->{Color}
2920 $font->{Strikeout}
2921 $font->{Super}
2922
2923=head2 $font->{Name}
2924
2925Returns the name of the font, for example 'Arial'.
2926
2927=head2 $font->{Bold}
2928
2929Returns true if the font is bold.
2930
2931=head2 $font->{Italic}
2932
2933Returns true if the font is italic.
2934
2935=head2 $font->{Height}
2936
2937Returns the size (height) of the font.
2938
2939=head2 $font->{Underline}
2940
2941Returns true if the font in underlined.
2942
2943=head2 $font->{UnderlineStyle}
2944
2945Returns the style of an underlined font where the value has the following meaning:
2946
2947 0 => None
2948 1 => Single
2949 2 => Double
2950 33 => Single accounting
2951 34 => Double accounting
2952
2953=head2 $font->{Color}
2954
2955Returns the color index for the font. The index can be converted to a RGB string using the C<ColorIdxToRGB()> Parser method.
2956
2957=head2 $font->{Strikeout}
2958
2959Returns true if the font has the strikeout property set.
2960
2961=head2 $font->{Super}
2962
2963Returns one of the following values if the superscript or subscript property of the font is set:
2964
2965 0 => None
2966 1 => Superscript
2967 2 => Subscript
2968
2969=head1 Formatter Class
2970
2971Formatters can be passed to the C<parse()> method to deal with Unicode or Asian formatting.
2972
2973Spreadsheet::ParseExcel includes 2 formatter classes. C<FmtDefault> and C<FmtJapanese>. It is also possible to create a user defined formatting class.
2974
2975The formatter class C<Spreadsheet::ParseExcel::Fmt*> should provide the following functions:
2976
2977
2978=head2 ChkType($self, $is_numeric, $format_index)
2979
2980Method to check the type of data in the cell. Should return C<Date>, C<Numeric> or C<Text>. It is passed the following parameters:
2981
2982=over
2983
2984=item $self
2985
2986A scalar reference to the Formatter object.
2987
2988=item $is_numeric
2989
2990If true, the value seems to be number.
2991
2992=item $format_index
2993
2994The index number for the cell Format object.
2995
2996=back
2997
2998=head2 TextFmt($self, $string_data, $string_encoding)
2999
3000Converts the string data in the cell into the correct encoding. It is passed the following parameters:
3001
3002=over
3003
3004=item $self
3005
3006A scalar reference to the Formatter object.
3007
3008=item $string_data
3009
3010The original string/text data.
3011
3012=item $string_encoding
3013
3014The character encoding of original string/text.
3015
3016=back
3017
3018=head2 ValFmt($self, $cell, $workbook)
3019
3020Convert the original unformatted cell value into the appropriate formatted value. For instance turn a number into a formatted date. It is passed the following parameters:
3021
3022=over
3023
3024=item $self
3025
3026A scalar reference to the Formatter object.
3027
3028=item $cell
3029
3030A scalar reference to the Cell object.
3031
3032=item $workbook
3033
3034A scalar reference to the Workbook object.
3035
3036=back
3037
3038
3039=head2 FmtString($self, $cell, $workbook)
3040
3041Get the format string for the Cell. It is passed the following parameters:
3042
3043=over
3044
3045=item $self
3046
3047A scalar reference to the Formatter object.
3048
3049=item $cell
3050
3051A scalar reference to the Cell object.
3052
3053=item $workbook
3054
3055A scalar reference to the Workbook object.
3056
3057=back
3058
3059
3060=head1 Reducing the memory usage of Spreadsheet::ParseExcel
3061
3062In some cases a C<Spreadsheet::ParseExcel> application may consume a lot of memory when processing a large Excel file and, as a result, may fail to complete. The following explains why this can occur and how to resolve it.
3063
3064C<Spreadsheet::ParseExcel> processes an Excel file in two stages. In the first stage it extracts the Excel binary stream from the OLE container file using C<OLE::Storage_Lite>. In the second stage it parses the binary stream to read workbook, worksheet and cell data which it then stores in memory. The majority of the memory usage is required for storing cell data.
3065
3066The reason for this is that as the Excel file is parsed and each cell is encountered a cell handling function creates a relatively large nested cell object that contains the cell value and all of the data that relates to the cell formatting. For large files (a 10MB Excel file on a 256MB system) this overhead can cause the system to grind to a halt.
3067
3068However, in a lot of cases when an Excel file is being processed the only information that is required are the cell values. In these cases it is possible to avoid most of the memory overhead by specifying your own cell handling function and by telling Spreadsheet::ParseExcel not to store the parsed cell data. This is achieved by passing a cell handler function to C<new()> when creating the parse object. Here is an example.
3069
3070 #!/usr/bin/perl -w
3071
3072 use strict;
3073 use Spreadsheet::ParseExcel;
3074
3075 my $parser = Spreadsheet::ParseExcel->new(
3076 CellHandler => \&cell_handler,
3077 NotSetCell => 1
3078 );
3079
3080 my $workbook = $parser->parse('file.xls');
3081
3082 sub cell_handler {
3083
3084 my $workbook = $_[0];
3085 my $sheet_index = $_[1];
3086 my $row = $_[2];
3087 my $col = $_[3];
3088 my $cell = $_[4];
3089
3090 # Do something useful with the formatted cell value
3091 print $cell->value(), "\n";
3092
3093 }
3094
3095
3096The user specified cell handler is passed as a code reference to C<new()> along with the parameter C<NotSetCell> which tells Spreadsheet::ParseExcel not to store the parsed cell. Note, you don't have to iterate over the rows and columns, this happens automatically as part of the parsing.
3097
3098The cell handler is passed 5 arguments. The first, C<$workbook>, is a reference to the C<Spreadsheet::ParseExcel::Workbook> object that represent the parsed workbook. This can be used to access any of the C<Spreadsheet::ParseExcel::Workbook> methods, see L</Workbook>. The second C<$sheet_index> is the zero-based index of the worksheet being parsed. The third and fourth, C<$row> and C<$col>, are the zero-based row and column number of the cell. The fifth, C<$cell>, is a reference to the C<Spreadsheet::ParseExcel::Cell> object. This is used to extract the data from the cell. See L</Cell> for more information.
3099
3100This technique can be useful if you are writing an Excel to database filter since you can put your DB calls in the cell handler.
3101
3102If you don't want all of the data in the spreadsheet you can add some control logic to the cell handler. For example we can extend the previous example so that it only prints the first 10 rows of the first two worksheets in the parsed workbook by adding some C<if()> statements to the cell handler:
3103
3104 #!/usr/bin/perl -w
3105
3106 use strict;
3107 use Spreadsheet::ParseExcel;
3108
3109 my $parser = Spreadsheet::ParseExcel->new(
3110 CellHandler => \&cell_handler,
3111 NotSetCell => 1
3112 );
3113
3114 my $workbook = $parser->parse('file.xls');
3115
3116 sub cell_handler {
3117
3118 my $workbook = $_[0];
3119 my $sheet_index = $_[1];
3120 my $row = $_[2];
3121 my $col = $_[3];
3122 my $cell = $_[4];
3123
3124 # Skip some worksheets and rows (inefficiently).
3125 return if $sheet_index >= 3;
3126 return if $row >= 10;
3127
3128 # Do something with the formatted cell value
3129 print $cell->value(), "\n";
3130
3131 }
3132
3133
3134However, this still processes the entire workbook. If you wish to save some additional processing time you can abort the parsing after you have read the data that you want, using the workbook C<ParseAbort> method:
3135
3136 #!/usr/bin/perl -w
3137
3138 use strict;
3139 use Spreadsheet::ParseExcel;
3140
3141 my $parser = Spreadsheet::ParseExcel->new(
3142 CellHandler => \&cell_handler,
3143 NotSetCell => 1
3144 );
3145
3146 my $workbook = $parser->parse('file.xls');
3147
3148 sub cell_handler {
3149
3150 my $workbook = $_[0];
3151 my $sheet_index = $_[1];
3152 my $row = $_[2];
3153 my $col = $_[3];
3154 my $cell = $_[4];
3155
3156 # Skip some worksheets and rows (more efficiently).
3157 if ( $sheet_index >= 1 and $row >= 10 ) {
3158 $workbook->ParseAbort(1);
3159 return;
3160 }
3161
3162 # Do something with the formatted cell value
3163 print $cell->value(), "\n";
3164
3165 }
3166
3167=head1 Decryption
3168
3169If a workbook is "protected" then Excel will encrypt the file whether a password is supplied or not. As of version 0.59 Spreadsheet::ParseExcel supports decrypting Excel workbooks using a default or user supplied password. However, only the following encryption scheme is supported:
3170
3171 Office 97/2000 Compatible encryption
3172
3173The following encryption methods are not supported:
3174
3175 Weak Encryption (XOR)
3176 RC4, Microsoft Base Cryptographic Provider v1.0
3177 RC4, Microsoft Base DSS and Diffie-Hellman Cryptographic Provider
3178 RC4, Microsoft DH SChannel Cryptographic Provider
3179 RC4, Microsoft Enhanced Cryptographic Provider v1.0
3180 RC4, Microsoft Enhanced DSS and Diffie-Hellman Cryptographic Provider
3181 RC4, Microsoft Enhanced RSA and AES Cryptographic Provider
3182 RC4, Microsoft RSA SChannel Cryptographic Provider
3183 RC4, Microsoft Strong Cryptographic Provider
3184
3185See the following for more information on Excel encryption: L<http://office.microsoft.com/en-us/office-2003-resource-kit/important-aspects-of-password-and-encryption-protection-HA001140311.aspx>.
3186
3187
3188
3189=head1 KNOWN PROBLEMS
3190
3191=over
3192
3193=item * Issues reported by users: L<http://rt.cpan.org/Public/Dist/Display.html?Name=Spreadsheet-ParseExcel>
3194
3195=item * This module cannot read the values of formulas from files created with Spreadsheet::WriteExcel unless the user specified the values when creating the file (which is generally not the case). The reason for this is that Spreadsheet::WriteExcel writes the formula but not the formula result since it isn't in a position to calculate arbitrary Excel formulas without access to Excel's formula engine.
3196
3197=item * If Excel has date fields where the specified format is equal to the system-default for the short-date locale, Excel does not store the format, but defaults to an internal format which is system dependent. In these cases ParseExcel uses the date format 'yyyy-mm-dd'.
3198
3199=back
3200
3201
3202
3203
3204=head1 REPORTING A BUG
3205
3206Bugs can be reported via rt.cpan.org. See the following for instructions on bug reporting for Spreadsheet::ParseExcel
3207
3208L<http://rt.cpan.org/Public/Dist/Display.html?Name=Spreadsheet-ParseExcel>
3209
3210
3211
3212
3213=head1 SEE ALSO
3214
3215=over
3216
3217=item * xls2csv by Ken Prows L<http://search.cpan.org/~ken/xls2csv-1.06/script/xls2csv>.
3218
3219=item * xls2csv and xlscat by H.Merijn Brand (these utilities are part of Spreadsheet::Read, see below).
3220
3221=item * excel2txt by Ken Youens-Clark, L<http://search.cpan.org/~kclark/excel2txt/excel2txt>. This is an excellent example of an Excel filter using Spreadsheet::ParseExcel. It can produce CSV, Tab delimited, Html, XML and Yaml.
3222
3223=item * XLSperl by Jon Allen L<http://search.cpan.org/~jonallen/XLSperl/bin/XLSperl>. This application allows you to use Perl "one-liners" with Microsoft Excel files.
3224
3225=item * Spreadsheet::XLSX L<http://search.cpan.org/~dmow/Spreadsheet-XLSX/lib/Spreadsheet/XLSX.pm> by Dmitry Ovsyanko. A module with a similar interface to Spreadsheet::ParseExcel for parsing Excel 2007 XLSX OpenXML files.
3226
3227=item * Spreadsheet::Read L<http://search.cpan.org/~hmbrand/Spreadsheet-Read/Read.pm> by H.Merijn Brand. A single interface for reading several different spreadsheet formats.
3228
3229=item * Spreadsheet::WriteExcel L<http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm>. A perl module for creating new Excel files.
3230
3231=item * Spreadsheet::ParseExcel::SaveParser L<http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel/SaveParser.pm>. This is a combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel and it allows you to "rewrite" an Excel file. See the following example L<http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#MODIFYING_AND_REWRITING_EXCEL_FILES>. It is part of the Spreadsheet::ParseExcel distro.
3232
3233=item * Text::CSV_XS L<http://search.cpan.org/~hmbrand/Text-CSV_XS/CSV_XS.pm> by H.Merijn Brand. A fast and rigorous module for reading and writing CSV data. Don't consider rolling your own CSV handling, use this module instead.
3234
3235=back
3236
3237
3238
3239
3240=head1 MAILING LIST
3241
3242There is a Google group for discussing and asking questions about Spreadsheet::ParseExcel. This is a good place to search to see if your question has been asked before: L<http://groups-beta.google.com/group/spreadsheet-parseexcel/>
3243
3244
3245
3246
3247=head1 DONATIONS
3248
3249If you'd care to donate to the Spreadsheet::ParseExcel project, you can do so via PayPal: L<http://tinyurl.com/7ayes>
3250
3251
3252
3253
3254=head1 TODO
3255
3256=over
3257
3258=item * The current maintenance work is directed towards making the documentation more useful, improving and simplifying the API, and improving the maintainability of the code base. After that new features will be added.
3259
3260=item * Fix open bugs and documentation for SaveParser.
3261
3262=item * Add Formula support, Hyperlink support, Named Range support.
3263
3264=item * Improve Spreadsheet::ParseExcel::SaveParser compatibility with Spreadsheet::WriteExcel.
3265
3266=item * Improve Unicode and other encoding support. This will probably require dropping support for perls prior to 5.8+.
3267
3268=back
3269
3270
3271
3272=head1 ACKNOWLEDGEMENTS
3273
3274From Kawai Takanori:
3275
3276First of all, I would like to acknowledge the following valuable programs and modules:
3277XHTML, OLE::Storage and Spreadsheet::WriteExcel.
3278
3279In no particular order: Yamaji Haruna, Simamoto Takesi, Noguchi Harumi, Ikezawa Kazuhiro, Suwazono Shugo, Hirofumi Morisada, Michael Edwards, Kim Namusk, Slaven Rezic, Grant Stevens, H.Merijn Brand and many many people + Kawai Mikako.
3280
3281Alexey Mazurin added the decryption facility.
3282
3283
3284
3285=head1 DISCLAIMER OF WARRANTY
3286
3287Because this software is licensed free of charge, there is no warranty for the software, to the extent permitted by applicable law. Except when otherwise stated in writing the copyright holders and/or other parties provide the software "as is" without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the software is with you. Should the software prove defective, you assume the cost of all necessary servicing, repair, or correction.
3288
3289In no event unless required by applicable law or agreed to in writing will any copyright holder, or any other party who may modify and/or redistribute the software as permitted by the above licence, be liable to you for damages, including any general, special, incidental, or consequential damages arising out of the use or inability to use the software (including but not limited to loss of data or data being rendered inaccurate or losses sustained by you or third parties or a failure of the software to operate with any other software), even if such holder or other party has been advised of the possibility of such damages.
3290
3291
3292
3293
3294=head1 LICENSE
3295
3296Either the Perl Artistic Licence L<http://dev.perl.org/licenses/artistic.html> or the GPL L<http://www.opensource.org/licenses/gpl-license.php>
3297
3298
3299
3300
3301=head1 AUTHOR
3302
3303Current maintainer 0.40+: John McNamara jmcnamara@cpan.org
3304
3305Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
3306
3307Original author: Kawai Takanori (Hippo2000) kwitknr@cpan.org
3308
3309
3310
3311
3312=head1 COPYRIGHT
3313
3314Copyright (c) 2009-2011 John McNamara
3315
3316Copyright (c) 2006-2008 Gabor Szabo
3317
3318Copyright (c) 2000-2006 Kawai Takanori
3319
3320All rights reserved. This is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License.
3321
3322
3323=cut