Perl and Excel FAQ - How to read and Excel files and interface with Excel using Perl.
This FAQ deals with aspects of reading and writing Excel files and manipulating Excel using Perl. It is still at a preliminary stage. If you have any information to contribute please send it to: jmcnamara@cpan.org
This section contains faqs in relation to reading Excel files.
This section contains faqs in relation to writing Excel files.
Spreadsheet::WriteExcel is a cross platform Perl module which can be used to write numbers and text in the native Excel binary file format.
Multiple worksheets can be added to a workbook. Formatting of cells is not yet supported. The module will work on the majority of Windows, UNIX and Macintosh platforms. Generated files are also compatible with the Linux/UNIX spreadsheet applications Star Office, Gnumeric and XESS. It is not compatible with Access.
The module is available athttp://search.cpan.org/search?dist=Spreadsheet-WriteExcel
ActivePerl users can download and install the module using PPM as follows:
C:\> ppm
PPM> set repository tmp http://homepage.eircom.net/~jmcnamara/perl
PPM> install Spreadsheet-WriteExcel
PPM> quit
C:\>
Read the Spreadsheet::WriteExcel documentation at http://search.cpan.org/search?dist=Spreadsheet-WriteExcel
The following example demonstrates how to use the Spreadsheet::WriteExcel module to create a workbook with a single worksheet:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
# Row and column are zero indexed
$row1 = $col1 = 0;
$row2 = 1;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");
$worksheet = $workbook->addworksheet();
$worksheet->write($row1, $col1, "Hi Excel!");
$worksheet->write($row2, $col1, 1.2345);
The following example demonstrates how to use the Spreadsheet::WriteExcel module to create a multiple worksheet workbook:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("regions.xls");
# Add some worksheets
my $north = $workbook->addworksheet("North");
my $south = $workbook->addworksheet("South");
my $east = $workbook->addworksheet("East");
my $west = $workbook->addworksheet("West");
# Add a caption to each worksheet
foreach my $worksheet (@{$workbook->{worksheets}}) {
$worksheet->write(0, 0, "Sales");
}
# Write some data
$north->write(0, 1, 200000);
$south->write(0, 1, 100000);
$east->write (0, 1, 150000);
$west->write (0, 1, 100000);
# Set the active worksheet
$south->activate();
You can redirect the Spreadsheet::WriteExcel output to STDOUT using the
special Perl filehandle "-". This can be useful for CGIs which have a Content-type of application/vnd.ms-excel:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
print "Content-type: application/vnd.ms-excel\n\n";
my $workbook = Spreadsheet::WriteExcel->new("-");
$workbook->write(0, 0, "Hi Excel!");
This section contains faqs in relation to interfacing with Excel apart from reading and writing files.
This section contains faqs in relation to the format of the Excel binary file.
Excel data is stored in the ``Binary Interchange File Format'' (BIFF) file format. Details of this format are given in the Excel SDK, the ``Excel Developer's Kit'' from Microsoft Press. It is also included in the MSDN CD library but is no longer available on the MSDN website. Issues relating to the Excel SDK are discussed, occasionally, at news://microsoft.public.excel.sdk
The BIFF portion of the Excel file is comprised of contiguous binary records have different functions and hold different types of data. Each BIFF record is comprised of the following three parts:
Record name; Hex identifier, length = 2 bytes
Record length; Length of following data, length = 2 bytes
Record data; Data, length = variable
The BIFF data is stored along with other data in an OLE Compound File. This is a structured storage which acts like a file system within a file. A Compound File is comprised of storages and streams which, to follow the file system analogy, are like directories and files.
The documentation for the OLE::Storage module, http://user.cs.tu-berlin.de/~schwartz/pmh/guide.html , contains one of the few descriptions of the OLE Compound File in the public domain.
Another useful source the cole libraries which form part of the filters project http://arturo.directmail.org/filtersweb/ .
The source code of the Excel plugin for the Gnumeric spreadsheet also contains information relevant to the Excel BIFF format and the OLE container, http://www.gnumeric.org/ .
John McNamara jmcnamara@cpan.org
Copyright (c) 2000, The Authors (see above). All Rights Reserved. This document may be used, redistributed and/or modified under the same terms as Perl itself.