Writing Excel Spreadsheets with Graphs and Charts - From the DB to XLS via Perl
The CPAN Module Spreadsheet::WriteExcel does a great job of generating complex Excel Spreadsheets directly from Perl. Since Excel is such a ubiquitious and flexible tool to transport and allow direct manipulation of tabular data, it is a powerful capability to be able to output data directly to XLS files. This module goes beyond simply outputting tabular data and can also generate charts in your spreadsheets. Alternatively, you could use a template XLS file which has the charting capability already crafted by a human, which is driven off of tabular data in a defined region of the spreadsheet to which you automate the writing of data through Perl. If you are a programmer who frequently interfaces with the Business side and need a convenient way to deliver data, you will really benfit from WriteExcel. Below you will find a basic tutorial which shows you how to generate an XLS file and fill it with tabilar data while performing some text formatting on that data as well. The code is easy to understand and there is a lot of useful information in the code comments.
Spreadsheet::WriteExcel 2.37 on CPAN
Excel file generated by the below demo code:
#!/usr/bin/perl
# File: spreadsheet_writeexcel_demo.pl
# Demonstration of Spreadsheet::WriteExcel
# Code by Jim Mannix. February, 2010.
$VERSION = 1.0.05;
use strict;
use warnings;
use Spreadsheet::WriteExcel;
# This demonstration of Spreadsheet::WriteExcel illustrates a few key issues
# in using this module to create and write to an Excel Spreadsheet.
# We will populate a grid spanning the first 10 columns and rows with labels in
# the cells which identify the cell address and do so with checkerboard colors.
# This will serve to illustrate a few important points.
#
# When you are looking at an XLS file in either Excel or OpenOffice, you will see
# that the columns start at A and increase to the right through Z, then AA, AB, AC,
# onwards through AZ, then BA through BZ etc.
# Well, the first important thing to note about using the WriteExcel module is that
# columns are most easily identified as 0 .. n, where 0 indicates the first A column.
#
# Now regarding the rows. In Excel or OpenOffice, you will see that the rows start
# at #1 and progress downward in ascending integers 1, 2, 3, etc.
# To avoid confusion and potential bugs in your programming it is very important to
# note that:
# Row # 1 is identified by the WriteExcel module as Row Index 0.
# This means that to translate the row identifier as seen in the XLS file to
# your programmatic index value for that row, you subtract 1.
# Remember it like this:
#
# XLS Row Number = Perl WriteExcel Row Index + 1 ... similarly:
# Perl WriteExcel Row Index = XLS Row Number - 1
#
# Now regarding the column identifiers A-Z, AA-AZ, BA-BZ etc.
# As we are doing in this demonstration and as you may also do in your Perl for various
# reasons, such as dynamically composing calculations or more, you may want to convert
# 0 throu n numerical indicies which refer to columns into their alphapetical label
# equivalents.
# If you will be doing this conversion with your own code the you will likely be
# using the Perl function chr(value) which returns the Ascii character character
# corresponding to the integer 'value'.
# In particular, note that in the Ascii table, A-Z spans the values of 65-90.
#
# NOTE: This simple demonstration will not calculate cell addresses/labels beyond
# column Z (col_index 25). We need fancier code if we must go on into column AA, AB etc.
# But fear not, the Spreadsheet::WriteExcel::Utility module has 2 methods for
# converting col, row indices into Excell addresses and back.
# It is a good idea to use Spreadsheet::WriteExcel::Utility in most cases.
#
# We use two formats, a bold and a bold-red to illustrate some other capabilitites of
# the module and we throw in some elegant Perl to generate the checkerboard pattern.
# Create a new Excel workbook in a fresh XLS file
my $workbook = Spreadsheet::WriteExcel->new('perl_generated_spreadsheet1.xls');
# A workbook can contain multiple sheets. Default name for the first sheet is 'Sheet1'.
# If we just stopped here we would see that there is already a 'Sheet1' in this
# newly created workbook, but for Perl we need to explicitly create a new
# worksheet object in the following step.
# Add a worksheet by invoking the add_worksheet method against our new workbook object.
my $worksheet = $workbook->add_worksheet();
# By calling the add_format method against our workbook object we create a new
# format object which we will call fmt_bold and then call methods to set properties
my $fmt_bold = $workbook->add_format(); # Add a format
$fmt_bold->set_bold();
$fmt_bold->set_align('center');
# And also a bold red format
my $fmt_bold_red = $workbook->add_format(); # Add a format
$fmt_bold_red->set_bold();
$fmt_bold_red->set_color('red');
$fmt_bold_red->set_align('center');
# Using the information detailed in all of the comments above,
# iterate over all of the columns and row indices, convert them as needed and create
# the label strings.
foreach my $col_index ( 0 .. 9 ) {
my $col_display = chr( $col_index + 65 ); # Ascii A-Z spans 65-90
foreach my $row_index ( 0 .. 9 ) {
# Module refers to what is 'Row 1' in spreadsheet with the index/row value of 0 so must add 1
my $row_display = $row_index + 1;
my $cell_display = $col_display . $row_display;
# When both row and column index are even we use the red format thus giving a checkerboard.
# The modulous operator % returns the remainder from integer division after
# first converting its operands to integers. This is a simple way to determine odd or even.
# Before performing the modulus, we add 2 to the 0-based indices so that 0 and 1 work correctly.
# If our modulus of index+2 gave us some remainder, 1 specifically, then that was an odd index.
# A remainder of 0 from the modulus of the index+2 means we have an even index.
# There are other ways to generate a checkerboard but this is an elegant, classic approach.
#
# Uncomment the following two lines to see how the modulus odd/even calc works:
# print "col: $col_index (col+2)%2: ", ($col_index + 2) % 2, ' -- ';
# print "row: $row_index (col+2)%2: ", ($row_index + 2) % 2, "\n";
#
my $format; # Declare $format with my before the if-blocks to use it outside of them
if ( (($col_index + 2) % 2) == (($row_index + 2) % 2) ) {
$format = $fmt_bold_red;
} else {
$format = $fmt_bold;
} # end: if col and row are even
#$worksheet->write( $row_index, $col_index, $cell_display, $format );
#
# Alternativly, for our write method, since we have computed the cell name which we use
# for writing a label into the same cell, we can actually address it in this way as
# one might expect. Thus, the following will also work:
my $cell_address = $cell_display; # Just to illustrate this explicitly
$worksheet->write( $cell_address, $cell_display, $format );
} # end: foreach row
} # end: foreach col
print "XLS file generation complete.\n";
exit(0);
##
#
