NAME

Spreadsheet::WriteExcel - Write text and numbers to minimal Excel binary file.

VERSION

This document refers to version 0.09 of Spreadsheet::WriteExcel, released Jan 27, 2000.

SYNOPSIS

To write a string and a number to an Excel file called perl.xls:

    use Spreadsheet::WriteExcel;

    $row1 = $col1 = 0;
    $row2 = 1;

    $excel = Spreadsheet::WriteExcel->new("perl.xls");

    $excel->write($row1, $col1, "Hi Excel!");
    $excel->write($row2, $col1, 1.2345);

Or explicitly, without the overhead of parsing:

    $excel->write_string($row1, $col1, "Hi Excel!");
    $excel->write_number($row2, $col1, 1.2345);

The file is closed when the program ends or when it is no longer referred to. Alternatively you can close it as follows:

    $excel->close();

DESCRIPTION

Overview

This module can be used to write numbers and text in the native Excel binary file format. This is a minimal implementation of an Excel file; no formatting can be applied to cells and only a single worksheet can be written to a workbook.

It is intended to be cross-platform, however, this is not guaranteed. See the section on portability below.

Constructor and initialization

A new Excel file is created as follows:

    Spreadsheet::WriteExcel->new("filename.xls");

This will create a workbook called "filename.xls" with a single worksheet called "filename".

Object methods

The following are the methods provided by WriteExcel:

    write(row, column, token)
    write_number(row, column, number)
    write_string(row, column, string)
    close()

Row and column are zero indexed cell locations; thus, Cell A1 is (0,0) and Cell AD2000 is (1999,29). Cells can be written to in any order. They can also be overwritten. (QuickView users refer to the bugs section.)

The method write() calls write_number() if "token" matches the following regex:

    $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/

Otherwise it calls write_string().

The write methods return:

    0 for success
   -1 for insufficient number of arguments
   -2 for row or column out of bounds
   -3 for string too long.
   

See also the section about limits.

The close() method can be called to explicitly close the Excel file. Otherwise the file will be closed automatically when the object reference goes out of scope or the program ends.

Note: The write* methods were previously named xl_write*. The older method names are still available but deprecated.

Example

The following example converts a tab separated file called tab.txt into an Excel file called tab.xls.

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

    open (TABFILE, "tab.txt") or die "tab.txt: $!";

    my $excel = Spreadsheet::WriteExcel->new("tab.xls");
    my $row = 0;
    my $col;

    while (<TABFILE>) {
        chomp;
        my @Fld = split('\t', $_);

        $col = 0;
        foreach my $token (@Fld) {
           $excel->write($row, $col, $token);
           $col++;
        }
        $row++;
    }

Limits

The following limits are imposed by Excel or the version of the BIFF file that has been implemented:

    Description                          Limit   Source
    -----------------------------------  ------  -------
    Maximum number of chars in a string  255     Excel 5
    Maximum number of columns            256     Excel 5, 97
    Maximum number of rows in Excel 5    16,384  Excel 5
    Maximum number of rows in Excel 97   65,536  Excel 97

The Excel "BIFF" binary format

The binary format of an Excel file is referred to as the Excel "Binary Interchange File Format" (BIFF) file format. For details of this file format refer to the "Excel Developer's Kit", Microsoft Press. This module is based on the BIFF5 specification. To facilitate portability and ease of implementation the Compound Document wrapper is not used. This effectively limits the scope of the BIFF file to the records given below.

The following binary records are implemented:

    [BOF]
    [DIMENSIONS]
    [NUMBER]
    [LABEL]
    [EOF]

Each Excel BIFF binary record has the following format:

    Record name   - Identifier, 2 bytes
    Record length - Length of the subsequent data, 2 bytes
    Record data   - Data, variable length

PORTABILITY

WriteExcel.pm will only work on systems where perl packs floats in 64 bit IEEE format. The float must also be in little-endian format but WriteExcel.pm will reverse it as necessary.

Thus:

    print join(" ", map { sprintf "%#02x", $_ } unpack("C*", pack "d", 1.2345)), "\n";

should give (or in reverse order):

    0x8d 0x97 0x6e 0x12 0x83 0xc0 0xf3 0x3f

If your system doesn't support this format of float then WriteExcel will croak with the message given in the Diagnostics section. A future version will correct this, if possible. In the meantime, if this doesn't work for your OS let me know about it.

DIAGNOSTICS

Filename required in WriteExcel("Filename")

A filename must be given in the constructor.

Can't open filename. It may be in use by Excel.

The file cannot be opened for writing or reading. It may be protected or already in use.

Required floating point format not supported on this platform.

Operating system doesn't support 64 bit IEEE float or it is byte-ordered in a way unknown to WriteExcel.

ALTERNATIVES

Depending on your requirements, background and general sensibilities you may prefer one of the following methods of getting data into Excel:

* CSV, comma separated variables or text. If the file extension is csv, Excel will open and convert this format automatically.

* HTML tables. This is an easy way of adding formatting.

* ODBC. Connect to an Excel file as a database.

* Win32::OLE module and office automation. This is very flexible and gives you access to multiple worksheets, formatting, and Excel's built-in functions.

To read data from Excel files try:

* ODBC.

* OLE::Storage, aka LAOLA. This is a Perl interface to OLE file formats, see CPAN.

* Win32::OLE.

Also, if you wish to view Excel files on Windows platforms which don't have Excel installed you can use the free Microsoft Excel Viewer. At the time of writing this was at: http://officeupdate.microsoft.com/downloadDetails/xlviewer.htm

BUGS

The main bug is the lack of a portable way of writing a little-endian 64 bit IEEE float. This is to-do.

QuickView: Excel files written with Version 0.08 are not displayed correctly in MS or JASC QuickView. This is partially fixed in Version 0.09 onwards. However, if you wish to write files that are fully compatible with QuickView it is necessary to write the cells in a sequential row by row order. This does not apply to Excel or to Excel Viewer.

TO DO

If possible, this module will be extended to include multiple worksheets, and formatting for rows, columns and cells.

ACKNOWLEDGEMENTS

The following people contributed to the debugging and testing of WriteExcel.pm:

Mike Blazer.

AUTHOR

John McNamara john.exeng@abanet.it

    I saw two shooting stars last night,
    I wished on them but they were only satellites. 
    It is wrong to wish on space hardware, 
    I wish, I wish, I wish you'd care.
          - Billy Bragg

COPYRIGHT

Copyright (c) 2000, John McNamara. All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.

cpanm Spreadsheet::WriteExcel

perl -MCPAN -e shell
install Spreadsheet::WriteExcel