≡ Menu

Perl and Excel: How to Read, Write, Parse Excel Files using Perl

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.

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • Uma January 5, 2012, 9:31 am

    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.

  • rmvnc June 27, 2012, 1:50 pm

    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

  • Sarit July 31, 2012, 7:14 am

    I didn’t understand how to install the .pm

  • whall September 11, 2012, 4:27 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.

  • Vel October 9, 2012, 9:16 am

    Hi,

    I’ve worked with the above code. Really awesome, Its working good. Thanks.

  • Cal October 16, 2012, 5:07 pm

    I’m only getting blank spreadsheets when I use WriteExcel but the ParseExcel object works fine.

  • Phil February 28, 2013, 7:18 am

    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.

  • Syed March 6, 2013, 12:44 pm

    How can I print the name of worksheet (tab). When I print value of $worksheet it gave me some hex number

  • Brad Allison April 2, 2013, 8:27 am

    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.

  • NNP April 9, 2013, 5:42 am

    Can you tell me dat wat can be the code in perl to download a excel-sheet.

  • Venkat April 23, 2013, 4:30 am

    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?

  • Victor Guo May 10, 2013, 2:35 pm

    Super work.

  • Venkat May 12, 2013, 10:58 pm

    Can some one help me with the issue of reading the cell comments ?

    Thanks
    VEnkat

  • Russ Aleshi July 18, 2013, 11:43 pm

    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__

  • mayank August 13, 2013, 1:17 pm

    can anyone help me to create pivot table using same codeing stayle

  • omkar February 16, 2015, 5:57 am

    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;