NAME
Spreadsheet::WriteExcel - Write text and numbers to minimal Excel binary file.
VERSION
This document refers to version 0.11 of Spreadsheet::WriteExcel, released August 25, 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". You can also redirect the 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 $excel = Spreadsheet::WriteExcel->new("-");
$excel->write(0, 0, "Hi Excel!");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 97The 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 lengthPORTABILITY
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 0x3fIf 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.
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. 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.
WRITING EXCEL FILES
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.
* DBI or ODBC. Connect to an Excel file as a database.
* Win32::OLE module and office automation. This requires a Windows platform and an installed copy of Excel. However, it is easy to use and gives access to the complete range of Excel's features such as: multiple worksheets, charts, cell formatting, macros and the built-in functions. See http://www.activestate.com/Products/ActivePerl/docs/faq/Windows/ActivePerl-Winfaq12.html and http://www.activestate.com/Products/ActivePerl/docs/site/lib/Win32/OLE.html
READING EXCEL FILES
Despite the title of this module the most commonly asked questions are in relation to reading Excel files. To read data from Excel files try:
* HTML tables. If the files are saved from Excel in a HTML format the data can be accessed using HTML::TableExtract http://search.cpan.org/search?dist=HTML-TableExtract
* DBI or ODBC. Connect to an Excel file as a database.
* OLE::Storage, aka LAOLA. This is a Perl interface to OLE file formats. In particular, the distro contains an Excel to HTML converter called Herbert, http://user.cs.tu-berlin.de/~schwartz/pmh/ There is also an open source C/C++ project based on the LAOLA work. Try the Filters Project at http://arturo.directmail.org/filtersweb/ and the xlHtml Project at http://www.xlhtml.org/ The xlHtml filter is more complete than Herbert.
* Win32::OLE module and office automation. This requires a Windows platform and an installed copy of Excel. This is the most powerful and complete method for interfacing with Excel. See http://www.activestate.com/ActivePerl/docs/faq/Windows/ActivePerl-Winfaq12.html and http://www.activestate.com/ActivePerl/docs/site/lib/Win32/OLE.html
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 http://officeupdate.microsoft.com/downloadDetails/xlviewer.htm
BUGS
The lack of a portable way of writing a little-endian 64 bit IEEE float. This is to-do.
Other Spreadsheets: The binary file created by WriteExcel is not a complete Excel file. As a result it is not compatible with Gnumeric, XESS, Applix, Star Office or anything else. This will be fixed in a later version.
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
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:
Arthur@ais, Mike Blazer, CPAN testers, Johan Ekenberg, Paul J. Falbe, Daniel Gardner, Artur Silveira da Cunha, John Wren.
AUTHOR
John McNamara jmcnamara@cpan.org
Writing a poem
In seventeen syllables
Is very diffic
- John Cooper ClarkeCOPYRIGHT
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.