#!/usr/bin/perl -w
###############################################################################
#
# A template for submitting a bug report.
#
# Run this program and read the output from the command line.
#
# reverse('(c)'), March 2004, John McNamara, jmcnamara@cpan.org
#
use strict;
print << 'HINTS_1';
REPORTING A BUG OR ASKING A QUESTION
Feel free to report bugs or ask questions. However, to save time
consider the following steps first:
Read the documentation:
The Spreadsheet::WriteExcel documentation has been refined in
response to user questions. Therefore, if you have a question it is
possible that someone else has asked it before you and that it is
already addressed in the documentation. Since there is a lot of
documentation to get through you should at least read the table of
contents and search for keywords that you are interested in.
Look at the example programs:
There are over 70 example programs shipped with the standard
Spreadsheet::WriteExcel distribution. Many of these were created in
response to user questions. Try to identify an example program that
corresponds to your query and adapt it to your needs.
HINTS_1
print "Press enter ..."; <STDIN>;
print << 'HINTS_2';
If you submit a bug report here are some pointers.
1. Put "WriteExcel:" at the beginning of the subject line. This helps
to filter genuine messages from spam.
2. Describe the problems as clearly and as concisely as possible.
3. Send a sample program. It is often easier to describe a problem in
code than in written prose.
4. The sample program should be as small as possible to demonstrate the
problem. Don't copy and past large sections of your program. The
program should also be self contained and working.
A sample bug report is generated below. If you use this format then it
will help to analyse your question and respond to it more quickly.
Please don't send patches without contacting the author first.
HINTS_2
print "Press enter ..."; <STDIN>;
print << 'EMAIL';
=======================================================================
To: John McNamara <jmcnamara@cpan.org>
Subject: WriteExcel: Problem with something.
Hi John,
I am using Spreadsheet::WriteExcel and I have encountered a problem. I
want it to do SOMETHING but the module appears to do SOMETHING_ELSE.
Here is some code that demonstrates the problem.
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("reload.xls");
my $worksheet = $workbook->add_worksheet();
$worksheet->write(0, 0, "Hi Excel!");
__END__
I tested using Excel XX (or Gnumeric or OpenOffice.org).
My automatically generated system details are as follows:
EMAIL
print "\n Perl version : $]";
print "\n OS name : $^O";
print "\n Module versions: (not all are required)\n";
my @modules = qw(
Spreadsheet::WriteExcel
Spreadsheet::ParseExcel
OLE::Storage_Lite
Parse::RecDescent
File::Temp
Digest::MD4
Digest::Perl::MD4
Digest::MD5
);
for my $module (@modules) {
my $version;
eval "require $module";
if (not $@) {
$version = $module->VERSION;
$version = '(unknown)' if not defined $version;
}
else {
$version = '(not installed)';
}
printf "%21s%-24s\t%s\n", "", $module, $version;
}
print << "BYE";
Yours etc.,
A. Person
--
BYE
__END__

An autofilter is a way of adding drop down lists to the headers of a 2D range of worksheet data. This is turn allow users to filter the data based on simple criteria so that some data is shown and some is hidden.

Excel provides a function called Autofit (Format->Columns->Autofit) that adjusts column widths to match the length of the longest string in a column. Excel calculates these widths at run time when it has access to information about string lengths and font information. This function is *not* a feature of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.

However, we can make an attempt to simulate it by keeping track of the longest string written to each column and then adjusting the column widths prior to closing the file.

We keep track of the longest strings by adding a handler to the write() function. See add_handler() in the S::WE docs for more information.

The main problem with trying to simulate Autofit lies in defining a relationship between a string length and its width in a arbitrary font and size. We use two approaches below. The first is a simple direct relationship obtained by trial and error. The second is a slightly more sophisticated method using an external module. For more complicated applications you will probably have to work out your own methods.

Source code for this example:

#!/usr/bin/perl -w
##############################################################################
#
# Simulate Excel's autofit for column widths.
#
# Excel provides a function called Autofit (Format->Columns->Autofit) that
# adjusts column widths to match the length of the longest string in a column.
# Excel calculates these widths at run time when it has access to information
# about string lengths and font information. This function is *not* a feature
# of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.
#
# However, we can make an attempt to simulate it by keeping track of the
# longest string written to each column and then adjusting the column widths
# prior to closing the file.
#
# We keep track of the longest strings by adding a handler to the write()
# function. See add_handler() in the S::WE docs for more information.
#
# The main problem with trying to simulate Autofit lies in defining a
# relationship between a string length and its width in a arbitrary font and
# size. We use two approaches below. The first is a simple direct relationship
# obtained by trial and error. The second is a slightly more sophisticated
# method using an external module. For more complicated applications you will
# probably have to work out your own methods.
#
# reverse('(c)'), May 2006, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('autofit.xls');
my $worksheet = $workbook->add_worksheet();
###############################################################################
#
# Add a handler to store the width of the longest string written to a column.
# We use the stored width to simulate an autofit of the column widths.
#
# You should do this for every worksheet you want to autofit.
#
$worksheet->add_write_handler(qr[\w], \&store_string_widths);
$worksheet->write('A1', 'Hello');
$worksheet->write('B1', 'Hello World');
$worksheet->write('D1', 'Hello');
$worksheet->write('F1', 'This is a long string as an example.');
# Run the autofit after you have finished writing strings to the workbook.
autofit_columns($worksheet);
###############################################################################
#
# Functions used for Autofit.
#
###############################################################################
###############################################################################
#
# Adjust the column widths to fit the longest string in the column.
#
sub autofit_columns {
my $worksheet = shift;
my $col = 0;
for my $width (@{$worksheet->{__col_widths}}) {
$worksheet->set_column($col, $col, $width) if $width;
$col++;
}
}
###############################################################################
#
# The following function is a callback that was added via add_write_handler()
# above. It modifies the write() function so that it stores the maximum
# unwrapped width of a string in a column.
#
sub store_string_widths {
my $worksheet = shift;
my $col = $_[1];
my $token = $_[2];
# Ignore some tokens that we aren't interested in.
return if not defined $token; # Ignore undefs.
return if $token eq ''; # Ignore blank cells.
return if ref $token eq 'ARRAY'; # Ignore array refs.
return if $token =~ /^=/; # Ignore formula
# Ignore numbers
return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;
# Ignore various internal and external hyperlinks. In a real scenario
# you may wish to track the length of the optional strings used with
# urls.
return if $token =~ m{^[fh]tt?ps?://};
return if $token =~ m{^mailto:};
return if $token =~ m{^(?:in|ex)ternal:};
# We store the string width as data in the Worksheet object. We use
# a double underscore key name to avoid conflicts with future names.
#
my $old_width = $worksheet->{__col_widths}->[$col];
my $string_width = string_width($token);
if (not defined $old_width or $string_width > $old_width) {
# You may wish to set a minimum column width as follows.
#return undef if $string_width < 10;
$worksheet->{__col_widths}->[$col] = $string_width;
}
# Return control to write();
return undef;
}
###############################################################################
#
# Very simple conversion between string length and string width for Arial 10.
# See below for a more sophisticated method.
#
sub string_width {
return 0.9 * length $_[0];
}
__END__
###############################################################################
#
# This function uses an external module to get a more accurate width for a
# string. Note that in a real program you could "use" the module instead of
# "require"-ing it and you could make the Font object global to avoid repeated
# initialisation.
#
# Note also that the $pixel_width to $cell_width is specific to Arial. For
# other fonts you should calculate appropriate relationships. A future version
# of S::WE will provide a way of specifying column widths in pixels instead of
# cell units in order to simplify this conversion.
#
sub string_width {
require Font::TTFMetrics;
my $arial = Font::TTFMetrics->new('c:\windows\fonts\arial.ttf');
my $font_size = 10;
my $dpi = 96;
my $units_per_em = $arial->get_units_per_em();
my $font_width = $arial->string_width($_[0]);
# Convert to pixels as per TTFMetrics docs.
my $pixel_width = 6 + $font_width *$font_size *$dpi /(72 *$units_per_em);
# Add extra pixels for border around text.
$pixel_width += 6;
# Convert to cell width (for Arial) and for cell widths > 1.
my $cell_width = ($pixel_width -5) /7;
return $cell_width;
}
__END__

Example of how to use the Spreadsheet::WriteExcel module to send an Excel file to a browser in a CGI program.

On Windows the hash-bang line should be something like:

#!C:\Perl\bin\perl.exe

The "Content-Disposition" line will cause a prompt to be generated to save the file. If you want to stream the file to the browser instead, comment out that line as shown below.

#!/usr/bin/perl -w
###############################################################################
#
# Example of how to use the Spreadsheet::WriteExcel module to send an Excel
# file to a browser in a CGI program.
#
# On Windows the hash-bang line should be something like:
#
# #!C:\Perl\bin\perl.exe
#
# The "Content-Disposition" line will cause a prompt to be generated to save
# the file. If you want to stream the file to the browser instead, comment out
# that line as shown below.
#
# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
# Set the filename and send the content type
my $filename ="cgitest.xls";
print "Content-type: application/vnd.ms-excel\n";
# The Content-Disposition will generate a prompt to save the file. If you want
# to stream the file to the browser, comment out the following line.
print "Content-Disposition: attachment; filename=$filename\n";
print "\n";
# Create a new workbook and add a worksheet. The special Perl filehandle - will
# redirect the output to STDOUT
#
my $workbook = Spreadsheet::WriteExcel->new(\*STDOUT);
my $worksheet = $workbook->add_worksheet();
# Set the column width for column 1
$worksheet->set_column(0, 0, 20);
# Create a format
my $format = $workbook->add_format();
$format->set_bold();
$format->set_size(15);
$format->set_color('blue');
# Write to the workbook
$worksheet->write(0, 0, "Hi Excel!", $format);
__END__

Example of formatting using the Spreadsheet::WriteExcel module via property hashes.

Setting format properties via hashes of values is useful when you have to deal with a large number of similar formats. Consider for example a chess board pattern with black squares, white unformatted squares and a border.

This relatively simple example requires 14 separate Format objects although there are only 5 different properties: black background, top border, bottom border, left border and right border.

Using property hashes it is possible to define these 5 sets of properties and then add them together to create the 14 Format configurations.

Example of how to add data validation and dropdown lists to a Spreadsheet::WriteExcel file.

Data validation is a feature of Excel which allows you to restrict the data that a users enters in a cell and to display help and warning messages. It also allows you to restrict input to values in a drop down list.

Simple example of merging cells using the Spreadsheet::WriteExcel module.

This example merges three cells using the "Centre Across Selection" alignment which was the Excel 5 method of achieving a merge. For a more modern approach use the merge_range() worksheet method instead. See the merge3.pl - merge6.pl programs.

Source code for this example:

#!/usr/bin/perl -w
###############################################################################
#
# Simple example of merging cells using the Spreadsheet::WriteExcel module.
#
# This example merges three cells using the "Centre Across Selection"
# alignment which was the Excel 5 method of achieving a merge. For a more
# modern approach use the merge_range() worksheet method instead.
# See the merge3.pl - merge6.pl programs.
#
# reverse('(c)'), August 2002, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
# Create a new workbook and add a worksheet
my $workbook = Spreadsheet::WriteExcel->new("merge1.xls");
my $worksheet = $workbook->add_worksheet();
# Increase the cell size of the merged cells to highlight the formatting.
$worksheet->set_column('B:D', 20);
$worksheet->set_row(2, 30);
# Create a merge format
my $format = $workbook->add_format(center_across => 1);
# Only one cell should contain text, the others should be blank.
$worksheet->write (2, 1, "Center across selection", $format);
$worksheet->write_blank(2, 2, $format);
$worksheet->write_blank(2, 3, $format);

Simple example of merging cells using the Spreadsheet::WriteExcel module

This example merges three cells using the "Centre Across Selection" alignment which was the Excel 5 method of achieving a merge. For a more modern approach use the merge_range() worksheet method instead. See the merge3.pl - merge6.pl programs.

The PerlHandler name above and the package name below *have* to match.

###############################################################################
#
# Example of how to use the Spreadsheet::WriteExcel module to send an Excel
# file to a browser using mod_perl 1 and Apache
#
# This module ties *XLS directly to Apache, and with the correct
# content-disposition/types it will prompt the user to save
# the file, or open it at this location.
#
# This script is a modification of the Spreadsheet::WriteExcel cgi.pl example.
#
# Change the name of this file to Cgi.pm.
# Change the package location to where ever you locate this package.
# In the example below it is located in the WriteExcel directory.
#
# Your httpd.conf entry for this module, should you choose to use it
# as a stand alone app, should look similar to the following:
#
# <Location /spreadsheet-test>
# SetHandler perl-script
# PerlHandler Spreadsheet::WriteExcel::Cgi
# PerlSendHeader On
# </Location>
#
# The PerlHandler name above and the package name below *have* to match.
# Apr 2001, Thomas Sullivan, webmaster@860.org
# Feb 2001, John McNamara, jmcnamara@cpan.org
package Spreadsheet::WriteExcel::Cgi;
##########################################
# Pragma Definitions
##########################################
use strict;
##########################################
# Required Modules
##########################################
use Apache::Constants qw(:common);
use Apache::Request;
use Apache::URI; # This may not be needed
use Spreadsheet::WriteExcel;
##########################################
# Main App Body
##########################################
sub handler {
# New apache object
# Should you decide to use it.
my $r = Apache::Request->new(shift);
# Set the filename and send the content type
# This will appear when they save the spreadsheet
my $filename ="cgitest.xls";
####################################################
## Send the content type headers
####################################################
print "Content-disposition: attachment;filename=$filename\n";
print "Content-type: application/vnd.ms-excel\n\n";
####################################################
# Tie a filehandle to Apache's STDOUT.
# Create a new workbook and add a worksheet.
####################################################
tie *XLS => 'Apache';
binmode(*XLS);
my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
my $worksheet = $workbook->add_worksheet();
# Set the column width for column 1
$worksheet->set_column(0, 0, 20);
# Create a format
my $format = $workbook->add_format();
$format->set_bold();
$format->set_size(15);
$format->set_color('blue');
# Write to the workbook
$worksheet->write(0, 0, "Hi Excel!", $format);
# You must close the workbook for Content-disposition
$workbook->close();
}
1;

Example of how use Spreadsheet::WriteExcel to generate Excel outlines and grouping.

Excel allows you to group rows or columns so that they can be hidden or displayed with a single mouse click. This feature is referred to as outlines.

Outlines can reduce complex data down to a few salient sub-totals or summaries.

This feature is best viewed in Excel but the following is an ASCII representation of what a worksheet with three outlines might look like. Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at level 1. The lines at the left hand side are called outline level bars.

Clicking the minus sign on each of the level 2 outlines will collapse and hide the data as shown in the next figure. The minus sign changes to a plus sign to indicate that the data in the outline is hidden.

Excel has a row limit of 65536 rows. Sometimes the amount of row data to be written to a file is greater than this limit. In this case it is a useful technique to wrap the data from one worksheet onto the next so that we get something like the following:

In order to achieve this we use a single worksheet reference and reinitialise it to point to a new worksheet when required.

Source code for this example:

#!/usr/bin/perl -w
##############################################################################
#
# Demonstrates how to wrap data from one worksheet onto another.
#
# Excel has a row limit of 65536 rows. Sometimes the amount of row data to be
# written to a file is greater than this limit. In this case it is a useful
# technique to wrap the data from one worksheet onto the next so that we get
# something like the following:
#
# Sheet1 Row 1 - 65536
# Sheet2 Row 65537 - 131072
# Sheet3 Row 131073 - ...
#
# In order to achieve this we use a single worksheet reference and
# reinitialise it to point to a new worksheet when required.
#
# reverse('(c)'), May 2006, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('row_wrap.xls');
my $worksheet = $workbook->add_worksheet();
# Worksheet formatting.
$worksheet->set_column('A:A', 20);
# For the sake of this example we will use a small row limit. In order to use
# the entire row range set the $row_limit to 65536.
my $row_limit = 10;
my $row = 0;
for my $count (1 .. 2 * $row_limit +10) {
# When we hit the row limit we redirect the output
# to a new worksheet and reset the row number.
if ($row == $row_limit) {
$worksheet = $workbook->add_worksheet();
$row = 0;
# Repeat any worksheet formatting.
$worksheet->set_column('A:A', 20);
}
$worksheet->write($row, 0, "This is row $count");
$row++;
}

Example of how to add a user defined data handler to the Spreadsheet:: WriteExcel write() method.

The following example shows how to add a handler for a 7 digit ID number. It adds an additional constraint to the write_handler1.pl in that it only filters data that isn't in the third column.

Source code for this example:

#!/usr/bin/perl -w
###############################################################################
#
# Example of how to add a user defined data handler to the Spreadsheet::
# WriteExcel write() method.
#
# The following example shows how to add a handler for a 7 digit ID number.
# It adds an additional constraint to the write_handler1.pl in that it only
# filters data that isn't in the third column.
#
#
# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("write_handler2.xls");
my $worksheet = $workbook->add_worksheet();
###############################################################################
#
# Add a handler for 7 digit id numbers. This is useful when you want a string
# such as 0000001 written as a string instead of a number and thus preserve
# the leading zeroes.
#
# Note: you can get the same effect using the keep_leading_zeros() method but
# this serves as a simple example.
#
$worksheet->add_write_handler(qr[^\d{7}$], \&write_my_id);
###############################################################################
#
# The following function processes the data when a match is found. The handler
# is set up so that it only filters data if it is in the third column.
#
sub write_my_id {
my $worksheet = shift;
my $col = $_[1];
# col is zero based
if ($col != 2) {
return $worksheet->write_string(@_);
}
else {
# Reject the match and return control to write()
return undef;
}
}
# This format maintains the cell as text even if it is edited.
my $id_format = $workbook->add_format(num_format => '@');
# Write some numbers in the user defined format
$worksheet->write('A1', '0000000', $id_format);
$worksheet->write('B1', '0000001', $id_format);
$worksheet->write('C1', '0000002', $id_format);
$worksheet->write('D1', '0000003', $id_format);
__END__

An example of writing an Excel file to a Perl scalar using Spreadsheet:: WriteExcel and the new features of perl 5.8.

For an examples of how to write to a scalar in versions prior to perl 5.8 see the filehandle.pl program and IO:Scalar.

#!/usr/bin/perl -w
##############################################################################
#
# An example of writing an Excel file to a Perl scalar using Spreadsheet::
# WriteExcel and the new features of perl 5.8.
#
# For an examples of how to write to a scalar in versions prior to perl 5.8
# see the filehandle.pl program and IO:Scalar.
#
# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
require 5.008;
# Use perl 5.8's feature of using a scalar as a filehandle.
my $fh;
my $str = '';
open $fh, '>', \$str or die "Failed to open filehandle: $!";
# Or replace the previous three lines with this:
# open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
# Spreadsheet::WriteExce accepts filehandle as well as file names.
my $workbook = Spreadsheet::WriteExcel->new($fh);
my $worksheet = $workbook->add_worksheet();
$worksheet->write(0, 0, "Hi Excel!");
$workbook->close();
# The Excel file in now in $str. Remember to binmode() the output
# filehandle before printing it.
binmode STDOUT;
print $str;
__END__

Simple program to convert a CSV comma-separated value file to an Excel file. This is more or less an non-op since Excel can read CSV files. The program uses Text::CSV_XS to parse the CSV.

Usage: csv2xls.pl file.csv newfile.xls

NOTE: This is only a simple conversion utility for illustrative purposes. For converting a CSV or Tab separated or any other type of delimited text file to Excel I recommend the more rigorous csv2xls program that is part of H.Merijn Brand's Text::CSV_XS module distro.

NOTE: This is only a simple conversion utility for illustrative purposes. For converting a CSV or Tab separated or any other type of delimited text file to Excel I recommend the more rigorous csv2xls program that is part of H.Merijn Brand's Text::CSV_XS module distro.

NOTE: An easier way of writing dates and times is to use the newer write_date_time() Worksheet method. See the date_time.pl example.

Demonstration of writing date/time cells to Excel spreadsheets, using UNIX/Perl time as source of date/time.

UNIX/Perl time is the time since the Epoch (00:00:00 GMT, 1 Jan 1970) measured in seconds.

An Excel file can use exactly one of two different date/time systems. In these systems, a floating point number represents the number of days (and fractional parts of the day) since a start point. The floating point number is referred to as a 'serial'.

This is an example of how to extend the Spreadsheet::WriteExcel module.

Code is appended to the Spreadsheet::WriteExcel::Worksheet module by reusing the package name. The new code provides a write() method that allows you to use Excels A1 style cell references. This is not particularly useful but it serves as an example of how the module can be extended without modifying the code directly.