Spreadsheet::ParseExcel::SaveParser - Rewrite an existing Excel file.
#1. Write an Excel file with previous data use strict; use Spreadsheet::ParseExcel::SaveParser; my $oExcel = new Spreadsheet::ParseExcel::SaveParser; my $oBook = $oExcel->Parse('temp.xls'); #1.1.Update and Insert Cells my $iFmt = $oBook->{Worksheet}[0]->{Cells}[0][0]->{FormatNo}; $oBook->AddCell(0, 0, 0, 'No(UPD)', $oBook->{Worksheet}[0]->{Cells}[0][0]->{FormatNo}); $oBook->AddCell(0, 1, 0, '304', $oBook->{Worksheet}[0]->{Cells}[0][0]); $oBook->AddCell(0, 1, 1, 'Kawai,Takanori', $iFmt); #1.2.add new worksheet my $iWkN = $oBook->AddWorksheet('Test'); #1.3 Save $oExcel->SaveAs($oBook, 'temp.xls'); # as the same name $oExcel->SaveAs($oBook, 'temp1.xls'); # another name #2. Create new Excel file (most simple) use strict; use Spreadsheet::ParseExcel::SaveParser; my $oEx = new Spreadsheet::ParseExcel::SaveParser; my $oBook = $oEx->Create(); $oBook->AddFormat; $oBook->AddWorksheet('NewWS'); $oBook->AddCell(0, 0, 1, 'New Cell'); $oEx->SaveAs($oBook, 'new.xls'); #3. Create new Excel file(more complex) #!/usr/local/bin/perl use strict; use Spreadsheet::ParseExcel::SaveParser; my $oEx = new Spreadsheet::ParseExcel::SaveParser; my $oBook = $oEx->Create(); my $iF1 = $oBook->AddFont( Name => 'Arial', Height => 11, Bold => 1, #Bold Italic => 1, #Italic Underline => 0, Strikeout => 0, Super => 0, ); my $iFmt = $oBook->AddFormat( Font => $oBook->{Font}[$iF1], Fill => [1, 10, 0], # Filled with Red # cf. ParseExcel (@aColor) BdrStyle => [0, 1, 1, 0], #Border Right, Top BdrColor => [0, 11, 0, 0], # Right->Green ); $oBook->AddWorksheet('NewWS'); $oBook->AddCell(0, 0, 1, 'Cell', $iFmt); $oEx->SaveAs($oBook, 'new.xls');
new interface...
use strict; use Spreadsheet::ParseExcel::SaveParser; $oBook = Spreadsheet::ParseExcel::SaveParser::Workbook->Parse('Excel/Test97.xls'); my $oWs = $oBook->AddWorksheet('TEST1'); $oWs->AddCell(10, 1, 'New Cell'); $oBook->SaveAs('iftest.xls');
Spreadsheet::ParseExcel::SaveParser : Expand of Spreadsheet::ParseExcel with Spreadsheet::WriteExcel
$oExcel = new Spreadsheet::ParseExcel::SaveParser();
Constructor.
$oWorkbook = $oParse->Parse($sFileName [, $oFmt]);
return "Workbook" object. if error occurs, returns undef.
name of the file to parse (Same as Spreadsheet::ParseExcel)
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.
$oWorkbook = $oParse->Create([$oFmt]);
return new "Workbook" object. if error occurs, returns undef.
$oWorkbook = $oParse->SaveAs( $oBook, $sName);
save $oBook image as an Excel file named $sName.
An Excel Workbook object to save.
Name of new Excel file.
Spreadsheet::ParseExcel::SaveParser::Workbook
Workbook is a subclass of Spreadsheet::ParseExcel::Workbook. And has these methods :
$oWorksheet = $oBook->AddWorksheet($sName, %hProperty);
Create new Worksheet(Spreadsheet::ParseExcel::Worksheet).
Name of new Worksheet
Property of new Worksheet.
$oWorksheet = $oBook->AddFont(%hProperty);
Create new Font(Spreadsheet::ParseExcel::Font).
$oWorksheet = $oBook->AddFormat(%hProperty);
Create new Format(Spreadsheet::ParseExcel::Format).
Property of new Format.
$oWorksheet = $oBook->AddCell($iWorksheet, $iRow, $iCol, $sVal, $iFormat [, $sCode]);
Create new Cell(Spreadsheet::ParseExcel::Cell).
Number of Worksheet
Number of row
Value of the cell.
Number of format for use. To specify just same as another cell, you can set it like below:
ex.
$oCell=$oWorksheet->{Cells}[0][0]; #Just a sample $oBook->AddCell(0, 1, 0, 'New One', $oCell->{FormatNo}); #or $oBook->AddCell(0, 1, 0, 'New One', $oCell);
Character code
Spreadsheet::ParseExcel::SaveParser::Worksheet
Worksheet is a subclass of Spreadsheet::ParseExcel::Worksheet. And has these methods :
$oWorksheet = $oWkSheet->AddCell($iRow, $iCol, $sVal, $iFormat [, $sCode]);
$oCell=$oWorksheet->{Cells}[0][0]; #Just a sample $oWorksheet->AddCell(1, 0, 'New One', $oCell->{FormatNo}); #or $oWorksheet->AddCell(1, 0, 'New One', $oCell);
Please visit my Wiki page. I'll add sample at : http://www.hippo2000.info/cgi-bin/KbWikiE/KbWiki.pl
-Only last print area will remain. (Others will be removed)
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) 2009 John McNamara
Copyright (c) 2006-2008 Gabor Szabo
Copyright (c) 2000-2002 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.
To install Spreadsheet::ParseExcel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::ParseExcel
CPAN shell
perl -MCPAN -e shell install Spreadsheet::ParseExcel
For more information on module installation, please visit the detailed CPAN module installation guide.