While Excel files are not the best way to communicate between computers, if the intended audience have human traits, it can be a good way to send reports.
For example, I had a client that used Perl to generate reports from their database. I'd run huge and long running SQL queries, build a multi-sheet Excel file, and send it as an email-attachment.
The following example works on Windows, Linux, Unix and probably also on other operating systems as well, though I have not tried them.
We are going to use the excellent Excel::Writer::XLSX module written by John McNamara and we are going to see several use-cases, based on the examples supplied with the package.
Let's start with the most basic file.
- #!/usr/bin/perl
- use strict;
- use warnings;
- use Excel::Writer::XLSX;
- my $workbook = Excel::Writer::XLSX->new( 'simple.xlsx' );
- my $worksheet = $workbook->add_worksheet();
- $worksheet->write( "A1", "Hi Excel!" );
- $worksheet->write( "A2", "second row" );
- $workbook->close;
Obviously we need to load the module first. The new constructor should get the name of the file we are creating. It is recommended to use the xlsx extension for files created with this module.
The return value of the new call is an object representing the whole Excel file. It is usually called $workbook, though you can name it any way you like it.
Every Excel file consists of one or more sheets. The data can be found in those sheets. The next step thus, is to create the first sheet. We do that by calling the add_worksheet method of the $workbook object. This both adds the sheet to the workbook and returns the object representing this sheet. We usually assign it to a variable called $worksheet.
Then we need to call the write method to add data to the worksheet. Each call has 2 parameters. The first one is the cell address. As you might recall the rows in Excel are numbered from 1, and the columns are marked by letters A, B, C... A1 being the top-left corner.
The last thing we need to to is to close the workbook. Strictly speaking this is not necessary as the file will be automatically saved and closed when the script ends or when the variable representing it goes out of scope, but it can be a nice hint of our intentions to the maintenance programmer.
Once the file is saved you can open it with Microsoft Excel or any other software that can handle this format. For example, I don't have Excel installed on my computer so I used the Calc application of Libre Office.
This is how it looks like:
Adding formulas
Having fixed content in the Excel file can be already very useful in a report, but the real power of Excel is in adding formulas. So we replaced the two calls to write in the previous example with 4 new calls.
The first two will put the values 3 and 4 in the first and second row of column A. The other two calls add two formulas. The first one is a simple addition, the second one is a call to the built-in SUM function of Excel.
- $worksheet->write( "A1", 3 );
- $worksheet->write( "A2", 4 );
- $worksheet->write( "A3", "=A1+A2" );
- $worksheet->write( "A4", "=SUM(A1:A3)" );
The result can be seen here:
Adding color using formats
People, especially in management, like to have colors in their reports. Just as from within Excel, we too can use formats to change how individual cells look like.
Again we replace the write calls in the earlier example by the following code:
- my $error_format = $workbook->add_format(
- color => 'red',
- );
- $worksheet->write( "A2", "Status" );
- $worksheet->write( "B2", "Broken", $error_format );
The first step we do is create a format object. It is going to be part of the $workbook, that represents the whole file, and it is also returned to a scalar variable. We can use this variable as the 3rd parameter of the write method.
The resulting file will look like this:
Address cells using coordinates
While the cell names A1, A2, B1, etc.. used so far might be convenient in some cases, there are other cases when we would like to be able to handle the two axis as independent coordinates.
The Excel::Writer::XLSX module allows us to do this using the same write method.
Going back to the first example, we could have written it this way as well:
- $worksheet->write( 0, 0, "Hi Excel!" );
- $worksheet->write( 1, 0, "second row" );
- $worksheet->write( 2, 3, "(2,3)" );
In this case the first parameter is the row number, the second parameter is the column number and the third is the value to be added. The 4th would be the format.
The top left corner or A1 is (0,0). A2 is (1,0), B1 is (0, 1) etc.
At first it can be a bit confusing, but there are applications when it is easier to access the cells using the coordinates.
