Formatting Tutorial

What is a format?

A format is an object of type Spreadsheet_Excel_Writer_Format. This format can be applied to cells inside a spreadsheet so that these cells inherit the properties of the format (text alignment, background color, border colors, etc...).

Using it

Formats can't be created directly by a new call. You have to create a format using the addFormat() method from a Workbook, which associates your Format with this Workbook (you can't use the Format with another Workbook).

Let's see how addFormat() is used:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

// Creating the format
$format_bold =& $workbook->addFormat();
$format_bold->setBold();

?>

There, we just created a bold format. Notice the ampersand sign (&) that appears when we created our format. If you don't create your format like that it will appear as if all the format's properties you set are ignored.

Making something useful

Well, we just created our first format, but we didn't use it. Not very smart. So let's do something useful with a format.

Let's say you want to make your regular data filled spreadsheet. Only this time, when you proudly present your beautiful creation to your boss, the thing you most dread happens:

Pointy haired boss - Mmmmhhh, seems OK.

You - Yes, I added those totals as you requested.

Pointy haired boss - Mmmmhhh, you know, there's going to be a lot of customers using this spreadsheet...

You - So...

Pointy haired boss - Mmmmhhh, what do you think of changing the style for those headers there?

You - ...

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

// We need a worksheet in which to put our data
$worksheet =& $workbook->addWorksheet();
// This is our title
$worksheet->write(0, 0, "Profits for Dotcom.Com", $format_bold);
// And now the data
$worksheet->write(0, 0, 0);

?>

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('yellow');
$format_title->setPattern(1);
$format_title->setFgColor('blue');

$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// While we are at it, why not throw some more numbers around
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);

$workbook->send('test.xls');
$workbook->close();
?>

Merging cells

If you just tested the previous example you might have noticed that the title would need several cells to be seen correctly, but the format we applied only works for the first cell. So our title does not look very nice.

What can we do to fix that? Well, you could tell your boss that the title looks ok to you, and that he really needs to visit an ophthalmologist. Or you could use cell merging in order to make the title spread over several cells.

For this you have to use the setAlign() method with 'merge' as argument, and create some empty cells so the title can 'use' them as a sort of background (there will be a better way to do this in a future version of Spreadsheet_Excel_Writer).

Applying merging to our example script, we would have this:

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('yellow');
$format_title->setPattern(1);
$format_title->setFgColor('blue');
// let's merge
$format_title->setAlign('merge');

$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// Couple of empty cells to make it look better
$worksheet->write(0, 1, "", $format_title);
$worksheet->write(0, 2, "", $format_title);
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);

$workbook->send('test.xls');
$workbook->close();
?>