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.