Spreadsheet::ParseExcel - Get information from Excel file
new interface
use strict;
use Spreadsheet::ParseExcel;
my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file);
foreach my $sheet (@{$excel->{Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet->{MaxRow} ||= $sheet->{MinRow};
foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
$sheet->{MaxCol} ||= $sheet->{MinCol};
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
my $cell = $sheet->{Cells}[$row][$col];
if ($cell) {
printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
}
}
}
}
old interface use strict; use Spreadsheet::ParseExcel; my $oExcel = Spreadsheet::ParseExcel->new;
#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); # Formatted Value
print "( $iR , $iC ) =>", $oWkC->{Val}, "\n" if($oWkC); # Original Value
}
}
}
Spreadsheet::ParseExcel makes you to get information from Excel95, Excel97, Excel2000 file.
$oExcel = Spreadsheet::ParseExcel->new( [ 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.
$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.
$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 methods :
$oWorksheet = $oBook->Worksheet($sName);
Worksheet returns a Worksheet object with $sName or undef. If there is no worksheet with $sName and $sName contains only digits, it returns a Worksheet object at that position.
Workbook class has these properties :
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 methods:
Worksheet class has these properties:
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
Spreadsheet::ParseExcel::Cell
Cell class has these properties:
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:
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
Spreadsheet::ParseExcel::Font
Format class has these properties:
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.
converts original text into applicatable for Value.
converts original value into applicatable for Value.
get format string for the $oCell.
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.
Current maintainer: Gabor Szabo szabgab@cpan.org
http://www.szabgab.com/
Original author: 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.
XLSTools: http://perl.jonallen.info/projects/xlstools
- Add tests, and more tests
- Spreadsheet::ParseExcel : Password protected data, Formulas support, HyperLink support, Named Range support
- Spreadsheet::ParseExcel::SaveParser : Catch up Spreadsheet::WriteExce feature, Create new Excel fle
See also:
L<http://www.cpanforum.com/dist/Spreadsheet-ParseExcel> and http://www.perlmonks.org/index.pl?node_id=490656 http://www.perlmonks.org/index.pl?node_id=379743 http://www.perlmonks.org/index.pl?node_id=433192 http://www.perlmonks.org/index.pl?node_id=422147
Copyright (c) 2006-2007 Gabor Szabo Copyright (c) 2000-2006 Kawai Takanori 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, Slaven Rezic, Grant Stevens, and many many people + Kawai Mikako.