If you want to manipulate excel files programmatically, you can use Perl Spreadsheet module, which provides an object interface that makes it easier to create and parse Excel files.
Install Spreadsheet WriteExcel Module
Method 1: Standard install using make
Download the zipped tar file of Spreadsheet-ParseExcel and Spreadsheet-WriteExcel from cpan.
Untar and unzip the module as follows:
tar -zxvf Spreadsheet-WriteExcel.tar.gz
cd to the directory the tar creates. Execute the steps below to to install the Spreadsheet-WriteExcel module.
perl Makefile.PL make make test make install
Use the above procedure to install Spreadsheet-ParseExcel also.
Method 2: CPAN.pm install
If you have CPAN.pm configured you can install the module as follows:
perl -MCPAN -e 'install "Spreadsheet::WriteExcel"' perl -MCPAN -e 'install "Spreadsheet::ParseExcel"'
For additional installation help refer to: How To Install Perl Modules Manually and Using CPAN command
Perl Spreadsheet::ParseExcel Module
The Spreadsheet::ParseExcel module can be used to read information from Excel 95-2003 binary files.
The advantage is that Spreadsheet::ParseExcel is compatible with Linux and Windows OS.
The Perl code given below does the following:
- The new() method is used to create a new Spreadsheet::ParseExcel parser object.
- The spreadsheet is parsed into a top-level object called $parser.
- The workbook contains several worksheets; iterate through them by using the workbook worksheets() property.
- Each worksheet has a MinRow and MinCol and corresponding MaxRow and MaxCol properties, which can be used to figure out the range the worksheet can access.
- Cells can be obtained from a worksheet through the Cells property;
#!/usr/bin/perl –w
use strict;
use Spreadsheet::ParseExcel;
my $FileName = “/home/selva/Report.xls";
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($FileName);
die $parser->error(), ".\n" if ( !defined $workbook );
# Following block is used to Iterate through all worksheets
# in the workbook and print the worksheet content
for my $worksheet ( $workbook->worksheets() ) {
# Find out the worksheet ranges
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
# Return the cell object at $row and $col
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value(), "\n";
}
}
}
Perl Spreadsheet::WriteExcel Module
The Spreadsheet::WriteExcel Perl module can be used to create a cross-platform Excel binary file. Multiple worksheets can be added to a workbook and formatting can be applied to cells. Text, numbers, formulas, hyperlinks, images and charts can be written to the cells.
The file produced by this module is compatible with Excel 97-2007 and this module is compatible with Linux, Windows OS.
- The Perl code given below does the following:
- A new Excel workbook is created using the new() constructor which accepts $FileName as a parameter.
- Added the new worksheet named PERL to the workbook
- The add_format() method can be used to create new Format objects which are used to apply formatting to a cell.
- The set_column() method is used to change the default properties of a single column or a range of columns. In this code I changed the width of the column to 20.
- Using Write() method write the data in given row and column.
#!/usr/bin/perl –w
use strict;
use Spreadsheet::WriteExcel;
# Create a new Excel file
my $FileName = “/home/selva/Report.xls";
my $workbook = Spreadsheet::WriteExcel->new($FileName);
# Add a worksheet
my $worksheet1 = $workbook->add_worksheet('PERL’);
# Define the format and add it to the worksheet
my $format = $workbook->add_format(
center_across => 1,
bold => 1,
size => 10,
border => 4,
color => 'black',
bg_color => 'cyan',
border_color => 'black',
align => 'vcenter',
);
# Change width for only first column
$worksheet1->set_column(0,0,20);
# Write a formatted and unformatted string, row and column
# notation.
$worksheet1->write(0,0, "PERL FLAVOURS", $format);
$worksheet1->write(1,0,"Active State PERL");
$worksheet1->write(2,0,"Strawberry PERL");
$worksheet1->write(3,0,"Vennila PERL");
Other than the functions mentioned above, Spreadsheet::WriteExcel has plenty of methods to write Text, numbers, formulas, hyperlinks, images and charts in Excel File.
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Nice article! I have been using Spreadsheet::WriteExcel for sometime now. Recently, I had to switch to Excel::Writer::XLSX for compatibility issue with the latest Excel version.
This is what I get when trying to install Spreadsheet-WriteExcel module.
bla_user# perl Makefile.PL
Checking if your kit is complete…
Warning: the following files are missing in your kit:
META.yml
Please inform the author.
Warning: prerequisite Archive::Zip 1.18 not found.
Warning: prerequisite Spreadsheet::ParseExcel not found.
Writing Makefile for Spreadsheet::XLSX
I didn’t understand how to install the .pm
This is exactly what I was looking for.
Wait, no – strike that. It was *almost* exactly what I was looking for. I also want to be able to programmatically create pivot tables within the spreadsheet based on certain data. So now I’ll use what I learned here, which gets me much further along than I was, and start looking for ways to create the pivot tables.
Hi,
I’ve worked with the above code. Really awesome, Its working good. Thanks.
I’m only getting blank spreadsheets when I use WriteExcel but the ParseExcel object works fine.
I work a lot with all of these modules, but recently I prefer Spreadsheet::Read for reading spreadsheet files, because it reads CSV, XLS(X) and OpenOffice Documents providing the same API for all these different formats.
How can I print the name of worksheet (tab). When I print value of $worksheet it gave me some hex number
Can not cut and paste the sample’s from the webpage. All sorts of strange hidden formatting characters appear when you try to page from the samples here. You have LEFT SINGLE QUOTE and RIGHT DOUBLE QUOTE in your sample. That’s what’s breaking the sample files. PERL does not have LEFT SINGLE QUOTE and RIGHT DOUBLE QUOTE. Or in this case you have RIGHT SINGLE QUOTE and LEFT DOUBLE QUOTE… these are not standard terminal characters and thus will break inside perl.
Also the – (dash) after the perl on the first line is not a standard terminal – and that too does not cut and paste…. too many bad characters in this sample.
Can you tell me dat wat can be the code in perl to download a excel-sheet.
Hi all,
I have also used the above spreadsheet::parseexcel function for reading the cell value which was working fine. But now i want to read the cell comments too.Can someone help me in this context?
Super work.
Can some one help me with the issue of reading the cell comments ?
Thanks
VEnkat
Hello Friends
I have the following Perl code that gets 5 days of data and write to .XLSX file, Except in line 32, rang does not work the way I asked for. It prints (Line 52) all 5 lines the same to Excel. Correct print is as ” say” statement prints on the screen. I have tried so many way of loops but no satisfying results, may be there is some simple way to do it. Can you help?.
Perl Code start here.
#######################
#!/usr/bin/perl
use strict;
use warnings;
use diagnostics;
use feature qw{ say };
use Finance::QuoteHist;
use Win32::OLE;
# Use above Subrutines/Modutes/Metodes/Functions.
#
#Start explist variables
my $q;
my $xlApp = “C:/Users/rassoul/desktop/write_Russ.XLSX”;
my $symbol;
my $stock_counter =1;
my ($date, $open, $high, $low, $close, $volume) ;
my $row;
my $xlBook;
# my $rng;
# my $x;
use strict;
while ( $stock_counter<=3 ) {
print "Enter a valid stock symbol: ";
$symbol = ;
chomp $symbol;
$stock_counter++;
# Call moudle t crat XLSX file
$xlApp = Win32::OLE->new(‘Excel.Application’);
# name $xlApp is maneStock file
$xlApp->{Visible} = 1;
$xlBook = $xlApp->Workbooks->Add;
# Line 32 is below Write all the data at once…
my $rng = $xlBook->ActiveSheet->Range(“A1:F5”);
#############################################################
# Get Quotes also stock_counter++ Increments if you use it
$q = Finance::QuoteHist->new
( symbols => [$symbol], start_date => ‘1 week ago’, # see Date::Manip
end_date => ‘today’,
);
# Get data loop
####################################################################################################
foreach $row ($q->quotes()) {
($symbol, $date, $open, $high, $low, $close, $volume) = @$row;
} # End 2Nd while yes/no
################################################## End 1St while yes/no
while ( my $quote = ) {
chomp $quote;
say $quote;
}
# Start for loop here
foreach $row ($q->quotes()) {
($symbol, $date, $open, $high, $low, $close, $volume) = @$row;
say “$date, $open, $high, $low, $close, $volume”;
# $rng->{Value} =”$date, $open, $high, $low, $close, $volume”;
#Line 52 he line below write all 5 records of array hte same instead of witing as say statment.
$rng->{Value} =[“$date”, “$open”, “$high”, “$low”, “$close”, “$volume”];
}
# Wait for user input…
print “Press to continue…”;
my $x = ;
# Clean up
$xlBook->{Saved} = 1;
$xlBook->{Saved} = 1;
$xlApp->Quit;
$xlBook = 0;
#made 0 to 1
$xlApp = 0;
if($stock_counter== 3){
print “Done w/ data for stock $symbol.\n”;
}
}#
__DATA__
can anyone help me to create pivot table using same codeing stayle
Hi,
i have below code with me but it prints column header alongwith rest of the data from the input excel . i just want rest of the data except column headers to be converted into xml format , can someone please help me to twik the belo code to achive this result:
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use XML::Writer;
use Time::Piece;
my $date = localtime->strftime(‘%Y-%m-%d’);
my $date1 = localtime->strftime(‘%a, %d %b %Y %H:%M:%S +0000’);
my @columns = (‘ID’, ‘Name’);
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse(‘myfile.xls’) or die $parser->error();
my $worksheet = $workbook->worksheet(0);
my ( $row_min, $row_max ) = $worksheet->row_range();
my @data;
for my $row ( $row_min .. $row_max ) {
my %hash;
for my $col (0 .. $#columns) {
my $cell = $worksheet->get_cell( $row, $col );
$hash{$columns[$col]} = $cell->value();
}
push(@data,\%hash)
}
my $writer = XML::Writer->new(OUTPUT => ‘self’, DATA_MODE => 1);
$writer->xmlDecl(“UTF-8”);
$writer->startTag(“BES”, “xmlns:xsi” => “http://www.w3.org/2001/XMLSchema-instance”, “xsi:noNamespaceSchemaLocation” => “BES.xsd”);
$writer->startTag(‘Baseline’);
$writer->dataElement(Title => “new baseline”);
$writer->dataElement(Description => “”);
$writer->dataElement(Relevance => “true”);
$writer->dataElement(Category=> “”);
$writer->dataElement(Source => “Internal”);
$writer->dataElement(SourceID => “”);
$writer->dataElement(SourceReleaseDate => $date);
$writer->dataElement(SourceSeverity => “”);
$writer->dataElement(CVENames => “”);
$writer->dataElement(SANSID => “”);
$writer->startTag(‘MIMEField’);
$writer->dataElement(Name => “x-fixlet-modification-time”);
$writer->dataElement(Value => $date1);
$writer->endTag(‘MIMEField’);
$writer->dataElement(Domain => “BESC”);
$writer->startTag(‘BaselineComponentCollection’);
$writer->startTag(‘BaselineComponentGroup’);
for my $row (@data) {
$writer->startTag(“BaselineComponent”, Name => $row->{‘Name’}, IncludeInRelevance => “true”, SourceSiteURL => “http://sync.bigfix.com/cgi-bin/bfgather/bessecurity”,SourceID => $row->{‘ID’}, ActionName => “Action1”);
$writer->dataElement(ActionScript => “”);
$writer->dataElement(Relevance => “”);
$writer->endTag(‘BaselineComponent’);
}
$writer->endTag(‘BaselineComponentGroup’);
$writer->endTag(‘BaselineComponentCollection’);
$writer->endTag(‘Baseline’);
$writer->endTag(‘BES’);
$writer->end;
print $writer->to_string;