Spreadsheet::ParseExcel(3pm)

1Spreadsheet::ParseExcelU(s3e)r Contributed Perl DocumentaStpiroenadsheet::ParseExcel(3)
2
3
4

NAME

6       Spreadsheet::ParseExcel - Get information from Excel file
7

SYNOPSIS

9       new interface
10
11           use strict;
12           use Spreadsheet::ParseExcel;
13
14           my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file);
15           foreach my $sheet (@{$excel->{Worksheet}}) {
16               printf("Sheet: %s\n", $sheet->{Name});
17               $sheet->{MaxRow} ⎪⎪= $sheet->{MinRow};
18               foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
19                   $sheet->{MaxCol} ⎪⎪= $sheet->{MinCol};
20                   foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
21                       my $cell = $sheet->{Cells}[$row][$col];
22                       if ($cell) {
23                           printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
24                       }
25                   }
26               }
27           }
28
29       old interface
30           use strict;
31           use Spreadsheet::ParseExcel;
32           my $oExcel = Spreadsheet::ParseExcel->new;
33
34           #1.1 Normal Excel97
35           my $oBook = $oExcel->Parse('Excel/Test97.xls');
36           my($iR, $iC, $oWkS, $oWkC);
37           print "FILE  :", $oBook->{File} , "\n";
38           print "COUNT :", $oBook->{SheetCount} , "\n";
39           print "AUTHOR:", $oBook->{Author} , "\n";
40           for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
41               $oWkS = $oBook->{Worksheet}[$iSheet];
42               print "--------- SHEET:", $oWkS->{Name}, "\n";
43               for(my $iR = $oWkS->{MinRow} ;
44                       defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
45                   for(my $iC = $oWkS->{MinCol} ;
46                                   defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
47                       $oWkC = $oWkS->{Cells}[$iR][$iC];
48                       print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);  # Formatted Value
49                       print "( $iR , $iC ) =>", $oWkC->{Val}, "\n" if($oWkC);  # Original Value
50                   }
51               }
52           }
53

DESCRIPTION

55       Spreadsheet::ParseExcel makes you to get information from Excel95,
56       Excel97, Excel2000 file.
57
58       Functions
59
60       new $oExcel = Spreadsheet::ParseExcel->new(
61                               [ CellHandler => \&subCellHandler,
62                                 NotSetCell => undef ⎪ 1,
63                               ]);
64
65           Constructor.
66
67           CellHandler (experimental)
68               specify callback function when a cell is detected.
69
70               subCellHandler gets arguments like below:
71
72               sub subCellHandler ($oBook, $iSheet, $iRow, $iCol, $oCell);
73
74               CAUTION : The atributes of Workbook may not be complete.  This
75               function will be called almost order by rows and columns.  Take
76               care almost, not perfectly.
77
78           NotSetCell (experimental)
79               specify set or not cell values to Workbook object.
80
81       Parse
82           $oWorkbook = $oParse->Parse($sFileName [, $oFmt]);
83
84           return "Workbook" object.  if error occurs, returns undef.
85
86           $sFileName
87               name of the file to parse
88
89               From 0.12 (with OLE::Storage_Lite v.0.06), scalar reference of
90               file contents (ex. \$sBuff) or IO::Handle object (inclucdng
91               IO::File etc.) are also available.
92
93           $oFmt
94               "Formatter Class" to format the value of cells.
95
96       ColorIdxToRGB
97           $sRGB = $oParse->ColorIdxToRGB($iColorIdx);
98
99           ColorIdxToRGB returns RGB string corresponding to specified color
100           index.  RGB string has 6 charcters, representing RGB hex value.
101           (ex. red = 'FF0000')
102
103       Workbook
104
105       Spreadsheet::ParseExcel::Workbook
106
107       Workbook class has these methods :
108
109       Parse
110           (class method) : same as Spreadsheet::ParseExcel
111
112       Worksheet
113           $oWorksheet = $oBook->Worksheet($sName);
114
115           Worksheet returns a Worksheet object with $sName or undef.  If
116           there is no worksheet with $sName and $sName contains only digits,
117           it returns a Worksheet object at that position.
118
119       Workbook class has these properties :
120
121       File
122           Name of the file
123
124       Author
125           Author of the file
126
127       Flg1904
128           If this flag is on, date of the file count from 1904.
129
130       Version
131           Version of the file
132
133       SheetCount
134           Numbers of "Worksheet" s in that Workbook
135
136       Worksheet[SheetNo]
137           Array of "Worksheet"s class
138
139       PrintArea[SheetNo]
140           Array of PrintArea array refs.
141
142           Each PrintArea is : [ StartRow, StartColumn, EndRow, EndColumn]
143
144       PrintTitle[SheetNo]
145           Array of PrintTitle hash refs.
146
147           Each PrintTitle is :
148                   { Row => [StartRow, EndRow],
149                     Column => [StartColumn, EndColumn]}
150
151       Worksheet
152
153       Spreadsheet::ParseExcel::Worksheet
154
155       Worksheet class has these methods:
156
157       Cell ( ROW, COL )
158           Return the Cell iobject at row ROW and column COL if it is defined.
159           Otherwise return undef.
160
161       RowRange ()
162           Return a two-element list (MIN, MAX) containing the minimum and
163           maximum of defined rows in the worksheet If there is no row defined
164           MAX is smaller than MIN.
165
166       ColRange ()
167           Return a two-element list (MIN, MAX) containing the minimum and
168           maximum of defined columns in the worksheet If there is no row
169           defined MAX is smaller than MIN.
170
171       Worksheet class has these properties:
172
173       Name
174           Name of that Worksheet
175
176       DefRowHeight
177           Default height of rows
178
179       DefColWidth
180           Default width of columns
181
182       RowHeight[Row]
183           Array of row height
184
185       ColWidth[Col]
186           Array of column width (undef means DefColWidth)
187
188       Cells[Row][Col]
189           Array of "Cell"s infomation in the worksheet
190
191       Landscape
192           Print in horizontal(0) or vertical (1).
193
194       Scale
195           Print scale.
196
197       FitWidth
198           Number of pages with fit in width.
199
200       FitHeight
201           Number of pages with fit in height.
202
203       PageFit
204           Print with fit (or not).
205
206       PaperSize
207           Papar size. The value is like below:
208
209             Letter               1, LetterSmall          2, Tabloid              3 ,
210             Ledger               4, Legal                5, Statement            6 ,
211             Executive            7, A3                   8, A4                   9 ,
212             A4Small             10, A5                  11, B4                  12 ,
213             B5                  13, Folio               14, Quarto              15 ,
214             10x14               16, 11x17               17, Note                18 ,
215             Envelope9           19, Envelope10          20, Envelope11          21 ,
216             Envelope12          22, Envelope14          23, Csheet              24 ,
217             Dsheet              25, Esheet              26, EnvelopeDL          27 ,
218             EnvelopeC5          28, EnvelopeC3          29, EnvelopeC4          30 ,
219             EnvelopeC6          31, EnvelopeC65         32, EnvelopeB4          33 ,
220             EnvelopeB5          34, EnvelopeB6          35, EnvelopeItaly       36 ,
221             EnvelopeMonarch     37, EnvelopePersonal    38, FanfoldUS           39 ,
222             FanfoldStdGerman    40, FanfoldLegalGerman  41, User                256
223
224       PageStart
225           Start page number.
226
227       UsePage
228           Use own start page number (or not).
229
230       LeftMergin, RightMergin, TopMergin, BottomMergin, HeaderMergin, Footer‐
231       Mergin
232           Mergins for left, right, top, bottom, header and footer.
233
234       HCenter
235           Print in horizontal center (or not)
236
237       VCenter
238           Print in vertical center  (or not)
239
240       Header
241           Content of print header.  Please refer Excel Help.
242
243       Footer
244           Content of print footer.  Please refer Excel Help.
245
246       PrintGrid
247           Print with Gridlines (or not)
248
249       PrintHeaders
250           Print with headings (or not)
251
252       NoColor
253           Print in black-white (or not).
254
255       Draft
256           Print in draft mode (or not).
257
258       Notes
259           Print with notes (or not).
260
261       LeftToRight
262           Print left to right(0) or top to down(1).
263
264       HPageBreak
265           Array ref of horizontal page breaks.
266
267       VPageBreak
268           Array ref of vertical page breaks.
269
270       MergedArea
271           Array ref of merged areas.  Each merged area is : [ StartRow,
272           StartColumn, EndRow, EndColumn]
273
274       Cell
275
276       Spreadsheet::ParseExcel::Cell
277
278       Cell class has these properties:
279
280       Value
281           Method Formatted value of that cell
282
283       Val Original Value of that cell
284
285       Type
286           Kind of that cell ('Text', 'Numeric', 'Date')
287
288       Code
289           Character code of that cell (undef, 'ucs2', '_native_') undef tells
290           that cell seems to be ascii.  '_native_' tells that cell seems to
291           be 'sjis' or something like that.
292
293       Format
294           "Format" for that cell.
295
296       Merged
297           That cells is merged (or not).
298
299       Rich
300           Array ref of font informations about each characters.
301
302           Each entry has : [ Start Position, Font Object]
303
304           For more information please refer sample/dmpExR.pl
305
306       Format
307
308       Spreadsheet::ParseExcel::Format
309
310       Format class has these properties:
311
312       Font
313           "Font" object for that Format.
314
315       AlignH
316           Horizontal Alignment.
317
318             0: (standard), 1: left,       2: center,     3: right,
319             4: fill ,      5: justify,    7:equal_space
320
321           Notice: 6 may be merge but it seems not to work.
322
323       AlignV
324           Vertical Alignment.
325
326               0: top,  1: vcenter, 2: bottom, 3: vjustify, 4: vequal_space
327
328       Indent
329           Number of indent
330
331       Wrap
332           Wrap (or not).
333
334       Shrink
335           Display in shrinking (or not)
336
337       Rotate
338           In Excel97, 2000      : degrees of string rotation.  In Excel95 or
339           earlier : 0: No rotation, 1: Top down, 2: 90 degrees anti-clock‐
340           wise,
341                                   3: 90 clockwise
342
343       JustLast
344           JustLast (or not).  I have never seen this attribute.
345
346       ReadDir
347           Direction for read.
348
349       BdrStyle
350           Array ref of boder styles : [Left, Right, Top, Bottom]
351
352       BdrColor
353           Array ref of boder color indexes : [Left, Right, Top, Bottom]
354
355       BdrDiag
356           Array ref of diag boder kind, style and color index : [Kind, Style,
357           Color]
358             Kind : 0: None, 1: Right-Down, 2:Right-Up, 3:Both
359
360       Fill
361           Array ref of fill pattern and color indexes : [Pattern, Front
362           Color, Back Color]
363
364       Lock
365           Locked (or not).
366
367       Hidden
368           Hiddedn (or not).
369
370       Style
371           Style format (or Cell format)
372
373       Font
374
375       Spreadsheet::ParseExcel::Font
376
377       Format class has these properties:
378
379       Name
380           Name of that font.
381
382       Bold
383           Bold (or not).
384
385       Italic
386           Italic (or not).
387
388       Height
389           Size (height) of that font.
390
391       Underline
392           Underline (or not).
393
394       UnderlineStyle
395           0: None, 1: Single, 2: Double, 0x21: Single(Account), 0x22: Dou‐
396           ble(Account)
397
398       Color
399           Color index for that font.
400
401       Strikeout
402           Strikeout (or not).
403
404       Super
405           0: None, 1: Upper, 2: Lower
406

Formatter class

408       Spreadsheet::ParseExcel::Fmt*
409
410       Formatter class will convert cell data.
411
412       Spreadsheet::ParseExcel includes 2 formatter classes: FmtDefault and
413       FmtJapanese.  You can create your own FmtClass as you like.
414
415       Formatter class(Spreadsheet::ParseExcel::Fmt*) should provide these
416       functions:
417
418       ChkType($oSelf, $iNumeric, $iFmtIdx)
419           tells type of the cell that has specified value.
420
421           $oSelf  Formatter itself
422
423           $iNumeric
424                   If on, the value seems to be number
425
426           $iFmtIdx
427                   Format index number of that cell
428
429       TextFmt($oSelf, $sText, $sCode)
430           converts original text into applicatable for Value.
431
432           $oSelf  Formatter itself
433
434           $sText  Original text
435
436           $sCode  Character code of Original text
437
438       ValFmt($oSelf, $oCell, $oBook)
439           converts original value into applicatable for Value.
440
441           $oSelf  Formatter itself
442
443           $oCell  Cell object
444
445           $oBook  Workbook object
446
447       FmtString($oSelf, $oCell, $oBook)
448           get format string for the $oCell.
449
450           $oSelf  Formatter itself
451
452           $oCell  Cell object
453
454           $oBook  WorkBook object contains that cell
455

KNOWN PROBLEM

457       This module can not get the values of fomulas in Excel files made with
458       Spreadsheet::WriteExcel.  Normaly (ie. By Excel application), formula
459       has the result with it.  But Spreadsheet::WriteExcel writes formula
460       with no result.  If you set your Excel application "Auto Calculation"
461       off.  (maybe [Tool]-[Option]-[Calculation] or something) You will see
462       the same result.
463

AUTHOR

465       Current maintainer: Gabor Szabo szabgab@cpan.org
466
467           http://www.szabgab.com/
468
469       Original author: Kawai Takanori (Hippo2000) kwitknr@cpan.org
470
471           http://member.nifty.ne.jp/hippo2000/            (Japanese)
472           http://member.nifty.ne.jp/hippo2000/index_e.htm (English)
473

TODO

482       - Add tests, and more tests
483
484       - Spreadsheet::ParseExcel :
485        Password protected data, Formulas support, HyperLink support,
486        Named Range support
487
488       - Spreadsheet::ParseExcel::SaveParser :
489        Catch up Spreadsheet::WriteExce feature, Create new Excel fle
490
491       See also:
492
493        L<http://www.cpanforum.com/dist/Spreadsheet-ParseExcel>
494
495        and
496
497        http://www.perlmonks.org/index.pl?node_id=490656
498        http://www.perlmonks.org/index.pl?node_id=379743
499        http://www.perlmonks.org/index.pl?node_id=433192
500        http://www.perlmonks.org/index.pl?node_id=422147
501

COPYRIGHT

503       Copyright (c) 2006-2007 Gabor Szabo Copyright (c) 2000-2006 Kawai
504       Takanori All rights reserved.
505
506       You may distribute under the terms of either the GNU General Public
507       License or the Artistic License, as specified in the Perl README file.
508

ACKNOWLEDGEMENTS

510       First of all, I would like to acknowledge valuable program and modules
511       : XHTML, OLE::Storage and Spreadsheet::WriteExcel.
512
513       In no particular order: Yamaji Haruna, Simamoto Takesi, Noguchi Harumi,
514       Ikezawa Kazuhiro, Suwazono Shugo, Hirofumi Morisada, Michael Edwards,
515       Kim Namusk, Slaven Rezić, Grant Stevens, and many many people + Kawai
516       Mikako.
517
518
519
520perl v5.8.8                       2007-04-09        Spreadsheet::ParseExcel(3)