Mergin => [$oWkS->{LeftMergin}, $oWkS->{RightMergin}, $oWkS->{TopMergin}, $oWkS->{BottomMergin}, $oWkS->{HeaderMergin}, $oWkS->{FooterMergin}, ], # Mergins(Left,Right,Top,Bottom,Header,Footer) HCenter => $oWkS->{HCenter}, # Horizontal Center VCenter => $oWkS->{VCenter}, # Vertical Center Header => $oWkS->{Header}, # Header Footer => $oWkS->{Footer}, # Footer PrintArea => $oBook->{PrintArea}[$iSheet], PrintTitle => {Row => $oBook->{PrintTitle}[$iSheet]->{Row}[0], Column => $oBook->{PrintTitle}[$iSheet]->{Column}[0]}, # PrintTitles(Row, Column) PrintGrid => $oWkS->{PrintGrid}, # Print Gridlines PrintHeaders => $oWkS->{PrintHeaders}, # Print Headings NoColor => $oWkS->{NoColor}, # Print in blcak-white Draft => $oWkS->{Draft}, # Print in draft mode Notes => $oWkS->{Notes}, # Print notes LeftToRight => $oWkS->{LeftToRight}, # Left to Right HPageBreak => $oWkS->{HPageBreak}, # Horizontal Page Breaks VPageBreak => $oWkS->{VPageBreak}, # Veritical Page Breaks );
Spreadsheet::ParseExcel::SaveParser - Expand of Spreadsheet::ParseExcel with Spreadsheet::WriteExcel
use strict; use Spreadsheet::ParseExcel::SaveParser; my $oExcel = new Spreadsheet::ParseExcel::SaveParser; my $oBook = $oExcel->Parse('some.xls'); my $oBWr = Spreadsheet::ParseExcel::SaveParser::Workbook->new($oBook); my $oExcel = new Spreadsheet::ParseExcel; #1.1 Normal Excel97 my $oBook = $oExcel->Parse('Excel/Test97.xls'); my($iR, $iC, $oWkS, $oWkC); print "FILE :", $oBook->{File} , "\n"; print "COUNT :", $oBook->{SheetCount} , "\n"; print "AUTHOR:", $oBook->{Author} , "\n"; for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) { $oWkS = $oBook->{Worksheet}[$iSheet]; print "--------- SHEET:", $oWkS->{Name}, "\n"; for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) { for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) { $oWkC = $oWkS->{Cells}[$iR][$iC]; print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC); } } }
Spreadsheet::ParseExcel makes you to get information from Excel95, Excel97, Excel2000 file.
$oExcel = new Spreadsheet::ParseExcel( [ CellHandler => \&subCellHandler, NotSetCell => undef | 1, ]);
Constructor.
specify callback function when a cell is detected.
subCellHandler gets arguments like below:
sub subCellHandler ($oBook, $iSheet, $iRow, $iCol, $oCell);
CAUTION : The atributes of Workbook may not be complete. This function will be called almost order by rows and columns. Take care almost, not perfectly.
specify set or not cell values to Workbook object.
$oWorkbook = $oParse->Parse($sFileName [, $oFmt]);
return "Workbook" object. if error occurs, returns undef.
name of the file to parse
From 0.12 (with OLE::Storage_Lite v.0.06), scalar reference of file contents (ex. \$sBuff) or IO::Handle object (inclucdng IO::File etc.) are also available.
"Formatter Class" to format the value of cells.
$sRGB = $oParse->ColorIdxToRGB($iColorIdx);
ColorIdxToRGB returns RGB string corresponding to specified color index. RGB string has 6 charcters, representing RGB hex value. (ex. red = 'FF0000')
Spreadsheet::ParseExcel::Workbook
Workbook class has these properties :
Name of the file
Author of the file
If this flag is on, date of the file count from 1904.
Version of the file
Numbers of "Worksheet" s in that Workbook
Array of "Worksheet"s class
Array of PrintArea array refs.
Each PrintArea is : [ StartRow, StartColumn, EndRow, EndColumn]
Array of PrintTitle hash refs.
Each PrintTitle is : { Row => [StartRow, EndRow], Column => [StartColumn, EndColumn]}
Spreadsheet::ParseExcel::Worksheet
Worksheet class has these properties:
Name of that Worksheet
Default height of rows
Default width of columns
Array of row height
Array of column width (undef means DefColWidth)
Array of "Cell"s infomation in the worksheet
Print in horizontal(0) or vertical (1).
Print scale.
Number of pages with fit in width.
Number of pages with fit in height.
Print with fit (or not).
Papar size. The value is like below:
Letter 1, LetterSmall 2, Tabloid 3 , Ledger 4, Legal 5, Statement 6 , Executive 7, A3 8, A4 9 , A4Small 10, A5 11, B4 12 , B5 13, Folio 14, Quarto 15 , 10x14 16, 11x17 17, Note 18 , Envelope9 19, Envelope10 20, Envelope11 21 , Envelope12 22, Envelope14 23, Csheet 24 , Dsheet 25, Esheet 26, EnvelopeDL 27 , EnvelopeC5 28, EnvelopeC3 29, EnvelopeC4 30 , EnvelopeC6 31, EnvelopeC65 32, EnvelopeB4 33 , EnvelopeB5 34, EnvelopeB6 35, EnvelopeItaly 36 , EnvelopeMonarch 37, EnvelopePersonal 38, FanfoldUS 39 , FanfoldStdGerman 40, FanfoldLegalGerman 41, User 256
Start page number.
Use own start page number (or not).
Mergins for left, right, top, bottom, header and footer.
Print in horizontal center (or not)
Print in vertical center (or not)
Content of print header. Please refer Excel Help.
Content of print footer. Please refer Excel Help.
Print with Gridlines (or not)
Print with headings (or not)
Print in black-white (or not).
Print in draft mode (or not).
Print with notes (or not).
Print left to right(0) or top to down(1).
Array ref of horizontal page breaks.
Array ref of vertical page breaks.
Array ref of merged areas. Each merged area is : [ StartRow, StartColumn, EndRow, EndColumn]
Spreadsheet::ParseExcel::Cell
Cell class has these properties:
Method Formatted value of that cell
Original Value of that cell
Kind of that cell ('Text', 'Numeric', 'Date')
Character code of that cell (undef, 'ucs2', '_native_') undef tells that cell seems to be ascii. '_native_' tells that cell seems to be 'sjis' or something like that.
"Format" for that cell.
That cells is merged (or not).
Array ref of font informations about each characters.
Each entry has : [ Start Position, Font Object]
For more information please refer sample/dmpExR.pl
Spreadsheet::ParseExcel::Format
Format class has these properties:
"Font" object for that Format.
Horizontal Alignment.
0: (standard), 1: left, 2: center, 3: right, 4: fill , 5: justify, 7:equal_space
Notice: 6 may be merge but it seems not to work.
Vertical Alignment.
0: top, 1: vcenter, 2: bottom, 3: vjustify, 4: vequal_space
Number of indent
Wrap (or not).
Display in shrinking (or not)
In Excel97, 2000 : degrees of string rotation. In Excel95 or earlier : 0: No rotation, 1: Top down, 2: 90 degrees anti-clockwise, 3: 90 clockwise
JustLast (or not). I have never seen this attribute.
Direction for read.
Array ref of boder styles : [Left, Right, Top, Bottom]
Array ref of boder color indexes : [Left, Right, Top, Bottom]
Array ref of diag boder kind, style and color index : [Kind, Style, Color] Kind : 0: None, 1: Right-Down, 2:Right-Up, 3:Both
Array ref of fill pattern and color indexes : [Pattern, Front Color, Back Color]
Locked (or not).
Hiddedn (or not).
Style format (or Cell format)
Spreadsheet::ParseExcel::Font
Name of that font.
Bold (or not).
Italic (or not).
Size (height) of that font.
Underline (or not).
0: None, 1: Single, 2: Double, 0x21: Single(Account), 0x22: Double(Account)
Color index for that font.
Strikeout (or not).
0: None, 1: Upper, 2: Lower
Spreadsheet::ParseExcel::Fmt*
Formatter class will convert cell data.
Spreadsheet::ParseExcel includes 2 formatter classes: FmtDefault and FmtJapanese. You can create your own FmtClass as you like.
Formatter class(Spreadsheet::ParseExcel::Fmt*) should provide these functions:
tells type of the cell that has specified value.
Formatter itself
If on, the value seems to be number
Format index number of that cell
converts original text into applicatable for Value.
Original text
Character code of Original text
converts original value into applicatable for Value.
Cell object
Workbook object
get format string for the $oCell.
WorkBook object contains that cell
This module can not get the values of fomulas in Excel files made with Spreadsheet::WriteExcel. Normaly (ie. By Excel application), formula has the result with it. But Spreadsheet::WriteExcel writes formula with no result. If you set your Excel application "Auto Calculation" off. (maybe [Tool]-[Option]-[Calculation] or something) You will see the same result.
Kawai Takanori (Hippo2000) kwitknr@cpan.org
http://member.nifty.ne.jp/hippo2000/ (Japanese) http://member.nifty.ne.jp/hippo2000/index_e.htm (English)
XLHTML, OLE::Storage, Spreadsheet::WriteExcel, OLE::Storage_Lite
This module is based on herbert within OLE::Storage and XLHTML.
Copyright (c) 2000-2001 Kawai Takanori and Nippon-RAD Co. OP Division All rights reserved.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
First of all, I would like to acknowledge valuable program and modules : XHTML, OLE::Storage and Spreadsheet::WriteExcel.
In no particular order: Yamaji Haruna, Simamoto Takesi, Noguchi Harumi, Ikezawa Kazuhiro, Suwazono Shugo, Hirofumi Morisada, Michael Edwards, Kim Namusk and many many people + Kawai Mikako.
1 POD Error
The following errors were encountered while parsing the POD:
Unknown directive: =cmmt
To install Spreadsheet::ParseExcel::Workbook, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::ParseExcel::Workbook
CPAN shell
perl -MCPAN -e shell install Spreadsheet::ParseExcel::Workbook
For more information on module installation, please visit the detailed CPAN module installation guide.