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:


This section contains faqs in relation to reading Excel files.

1.1 Text

1.2 Database

1.3 OLE

1.4 OLE::Storage

1.5 Filters


This section contains faqs in relation to writing Excel files.

2.1 Text

2.2 Database

2.3 Win32::OLE

2.4 Spreadsheet::WriteExcel

2.4.1 What is Spreadsheet::WriteExcel?

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.

2.4.2 Where can I get Spreadsheet::WriteExcel?

The module is available at

ActivePerl users can download and install the module using PPM as follows:

    C:\> ppm
    PPM> set repository tmp
    PPM> install Spreadsheet-WriteExcel
    PPM> quit

2.4.3 Where can I get more information?

Read the Spreadsheet::WriteExcel documentation at

2.4.4 Spreadsheet::WriteExcel Example: Single worksheet

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);

2.4.5 Spreadsheet::WriteExcel Example: Multiple workbook

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

2.4.6 Spreadsheet::WriteExcel Example: Output to browser

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/

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;
    print "Content-type: application/\n\n";
    my $workbook = Spreadsheet::WriteExcel->new("-");
    $workbook->write(0, 0, "Hi Excel!");

2.5 HTML

2.6 XML


This section contains faqs in relation to interfacing with Excel apart from reading and writing files.

3.1 Win32::OLE


This section contains faqs in relation to the format of the Excel binary file.

4.1 The BIFF file format

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

4.2 The OLE Compound document format

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, , 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 .

The source code of the Excel plugin for the Gnumeric spreadsheet also contains information relevant to the Excel BIFF format and the OLE container, .



John McNamara


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.