Spreadsheet_Excel_Writer is a tool for creating
Excel files without the need for COM components. The files generated by
the current version of Spreadsheet_Excel_Writer
correspond to the Excel 5 (BIFF5) format, so all functionality until
that version of Excel (but not beyond) should be available.
Using it
The most common use for Spreadsheet_Excel_Writer
will be spitting out large (or not so large) amounts of information
in the form of a spreadsheet, which is easy to manipulate with a fairly
ubiquitous spreadsheet program such as Excel (or OpenOffice).
So let's cut to the chase and see how this is done:
<?php
require_once 'Spreadsheet/Excel/Writer.php';
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
// sending HTTP headers
$workbook->send('test.xls');
// Creating a worksheet
$worksheet =& $workbook->addWorksheet('My first worksheet');
// The actual data
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');
$worksheet->write(1, 0, 'John Smith');
$worksheet->write(1, 1, 30);
$worksheet->write(2, 0, 'Johann Schmidt');
$worksheet->write(2, 1, 31);
$worksheet->write(3, 0, 'Juan Herrera');
$worksheet->write(3, 1, 32);
// Let's send the file
$workbook->close();
?>
The first thing you should notice, is that we created a workbook before any
worksheets. All worksheets are contained within a workbook, and a workbook
may contain several worksheets.
Another important thing, which you should have in mind when programming
with Spreadsheet_Excel_Writer, is that ampersand
sign (&) that appears when we created our worksheet. That ampersand means
we are referencing a Worksheet object instead of copying it. If you don't
know what that means, don't worry, all you have to remember is to always
use ampersands when calling addWorksheet() for creating a worksheet, or
addFormat() for creating a format.
What that means is that we are sending our spreadsheet to a browser.
But what if we just want to save the spreadsheet in our machine? Well, you
just have to omit that line and give a valid file path to the workbook
constructor.
For example, if we wanted to save the same spreadsheet we created in our
first example to a file named 'test.xls', we would do it like so:
<?php
require_once 'Spreadsheet/Excel/Writer.php';
// We give the path to our file here
$workbook = new Spreadsheet_Excel_Writer('test.xls');
$worksheet =& $workbook->addWorksheet('My first worksheet');
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');
$worksheet->write(1, 0, 'John Smith');
$worksheet->write(1, 1, 30);
$worksheet->write(2, 0, 'Johann Schmidt');
$worksheet->write(2, 1, 31);
$worksheet->write(3, 0, 'Juan Herrera');
$worksheet->write(3, 1, 32);
// We still need to explicitly close the workbook
$workbook->close();
?>
More tutorials
If you would like to learn about formatting (fonts, cell color, text
alignment, etc...) with Spreadsheet_Excel_Writer,
you can check the formatting tutorial
here.
