++ed by:
MJEMMESON KABLAMO AMBS XIAODONG HMBRAND

11 PAUSE users
13 non-PAUSE users.

John McNamara

NAME

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

VERSION

This document refers to version 0.08 of Spreadsheet::WriteExcel, released Jan 16, 2000.

SYNOPSIS

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

    use Spreadsheet::WriteExcel;

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

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

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

Or explicitly, without the overhead of parsing:

    $excel->xl_write_string($row1, $col1, "Hi Excel!");
    $excel->xl_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();

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 $row = 0;
    my $col;

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

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

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

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:

    xl_write(row, column, token)
    xl_write_number(row, column, number)
    xl_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.

The method xl_write() calls xl_write_number() if "token" matches the following regex:

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

Otherwise it calls xl_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.

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.

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

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

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

BUGS

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

AUTHOR

John McNamara (john.exeng@abanet.it)

"Life, friends is boring. We must not say so." - John Berryman.

COPYRIGHT

Copyright (c) 1999-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.