Perl with Excel File

Perl has few modules that can deal with excel files. It is able to handle all type of spreadsheets. You can create or read an excel file with the help of Perl very easily.

Create Excel file

To create an excel file, we will use Perl module Excel::Writer::XLSX. The excel file extension is (.xlsx) for this module.

We are creating an excel file myExcel.xlsx with the above module. First we need to load the module. In the new constructor, we'll pass the file name we are creating. The output of new constructor is stored in $workbook.

An excel file consists of one or more than one sheet. Data is found on those sheets. We are creating sheets by calling add_worksheet() method of the $workbook object. Its return value is stored in $worksheet.

The write method adds data to the worksheet. Each call has two parameters. In excel, rows are marked from 1, and columns are marked from letters A, B, C and so on.

Lastly we close the file. But this is not necessary in Perl, as it automatically close file when script ends.

Example

snippet

#!/usr/bin/perl
use strict;
use warnings; 
use Excel::Writer::XLSX;
my $workbook= Excel::Writer::XLSX->new( 'myExcel.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( "A1", "Excel file generated!" );
$worksheet->write( "A2", "This is the second row" );
$workbook->close;

Adding Formula in Excel

Now, we'll print 2's table by using multiplication. Column A will print 2's table.

Example

snippet

#!/usr/bin/perl
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook  = Excel::Writer::XLSX->new( 'myExcel.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( "A1", 2 );
$worksheet->write( "A2", "=A1*2" );
$worksheet->write( "A3", "=A1*3" );
$worksheet->write( "A4", "=A1*4" );
$worksheet->write( "A5", "=A1*5" );
$worksheet->write( "A6", "=A1*6" );
$worksheet->write( "A7", "=A1*7" );
$worksheet->write( "A8", "=A1*8" );
$worksheet->write( "A9", "=A1*9" );
$worksheet->write( "A10", "=A1*10" );
$workbook->close;