Zero Index Confusion

One method of note, which we saw in the above example, is the Spreadsheet_Excel_Writer_Worksheet::write() method, which you'll be using a lot to add data to cells. It can be slightly confusing if you're used to the way cells are addressed in Excel.

The first argument to write() is the row number. The first row number, at the top of the spreadsheet, is 0 (zero) in PEAR::Spreadsheet_Excel_Writer, not 1, as it is in Excel.

The second argument is the column number. Now, columns in Excel are identified with letters of the alphabet, not numbers, so you'll just have to get used to translating between the two. The letter F is 6th in the alphabet, so the second argument is... 5 (of course!) -- the leftmost column is 0 (zero) in PEAR::Spreadsheet_Excel_Writer, so you need to subtract one to get the column number.

The third argument to write() is the data to put into the cell; there's an optional forth argument, used to apply visual formatting to the cell.

There are many more methods in the Spreadsheet_Excel_Writer_Worksheet class, such as for freezing and thawing parts of the sheet, and formatting the sheet, as a whole, for printing. I'll touch on some of these in later examples but you'll have to explore the majority for yourself.

Adding Cell Formatting

So, how about making the spreadsheet look pretty? We can accomplish this with PEAR::Spreadsheet_Excel_Writer using the addFormat() function to fetch an object of type Spreadsheet_Excel_Writer_Format. We apply the formatting to this object using the (large number of) methods it provides, then pass it the write() method of Spreadsheet_Excel_Writer_Worksheet to assign the formatting to a particular cell we've added.

For the sake of a "real world" example, let's say I want to give users of my online shop, phpPetstore.com, the ability to download a "receipt" for the items they just bought as a Workbook containing a single Worksheet.

I begin my worksheet with the usual stuff:

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

// Create workbook
$xls =& new Spreadsheet_Excel_Writer();

// Create worksheet
$cart =& $xls->addWorksheet('phpPetstore');

Next (blessed with the knowledge that I'll only be using four columns), I'll add a title to the sheet, merging some cells in which to place it. Here, you get your first taste of how formatting is done:

// Some text to use as a title for the worksheet
$titleText = 'phpPetstore: Receipt from ' . date('dS M Y');

// Create a format object
$titleFormat =& $xls->addFormat();

// Set the font family - Helvetica works for OpenOffice calc too...
$titleFormat->setFontFamily('Helvetica');

// Set the text to bold
$titleFormat->setBold();

// Set the text size
$titleFormat->setSize('13');

// Set the text color
$titleFormat->setColor('navy');

// Set the bottom border width to "thick"
$titleFormat->setBottom(2);

// Set the color of the bottom border
$titleFormat->setBottomColor('navy');

// Set the alignment to the special merge value
$titleFormat->setAlign('merge');

// Add the title to the top left cell of the worksheet,
// passing it the title string and the format object
$cart->write(0,0,$titleText,$titleFormat);

// Add three empty cells to merge with
$cart->write(0,1,'',$titleFormat);
$cart->write(0,2,'',$titleFormat);
$cart->write(0,3,'',$titleFormat);

// The row height
$cart->setRow(0,30);

// Set the column width for the first 4 columns
$cart->setColumn(0,3,15);

Notice first that I got the formatting object by calling addFormat() via the $xls object that represents the entire spreadsheet. I then apply some specific formatting to the object (method names like setBold() speak for themselves -- see the API docs for a full list of formatting methods).

Once I'm done formatting, I call write() on the Worksheet object $cart to add to a cell, passing the formatting object as the forth argument.

One unusual thing I've done here is to merge four cells. By calling setAlign('merge') on the formatting object (normally you'd use something like 'left', 'right' or 'center'), I've told Spreadsheet_Excel_Writer that it should merge all cells that have this formatting applied to them. This is why I created three empty cells and applied the formatting to them.

Using setRow() allows me to modify the height of the row, making it bigger than the default Excel row height. This method has further optional formatting arguments that allow you, for example, to apply a format object to an entire row. Likewise, for setColumn(), I can set the column width and optionally apply further formatting. The difference is that setRow() applies to a single row only, while setColumn() is applied to a range of columns.

So far, so good. Now, I need some data to add to the sheet. To avoid complicating the example (by involving a database), I'll use an indexed array of associative arrays, which we can pretend is the result of an SQL select:

$items = array (
 array( 'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1),
 array( 'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2),
 array( 'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10),
);

The "columns in the database" are the keys of the second order arrays; 'description', 'price' and 'quantity', so the next thing we need to do is to add the column headings along with an additional 'Total' heading that I'll use later on:

// Set up some formatting
$colHeadingFormat =& $xls->addFormat();
$colHeadingFormat->setBold();
$colHeadingFormat->setFontFamily('Helvetica');
$colHeadingFormat->setBold();
$colHeadingFormat->setSize('10');
$colHeadingFormat->setAlign('center');

// An array with the data for the column headings
$colNames = array('Item','Price($)','Quantity','Total');

// Add all the column headings with a single call
// leaving a blank row to look nicer
$cart->writeRow(2,0,$colNames,$colHeadingFormat);

You've already seen the formatting. What you haven't seen before is the writeRow() method. This essentially does the same thing as write(), but allows you to insert an array of data, from left to right, beginning at the specified row and column number. It makes a handy short cut to reduce lines of code.

One further thing I want to do is to make sure the column headings will always be visible as we scroll through the list of items. In Excel, this is possible by "freezing" a "pane" -- selecting a block of cells that will "hover" while a user scrolls through the data, allowing them to see the column headings (in this case) that tell them what the data represents. The same is possible with PEAR::Spreadsheet_Excel_Writer:

// The cell group to freeze
// 1st Argument - vertical split position
// 2st Argument - horizontal split position (0 = no horizontal split)
// 3st Argument - topmost visible row below the vertical split
// 4th Argument - leftmost visible column after the horizontal split
$freeze = array(3,0,4,0);

// Freeze those cells!
$cart->freezePanes($freeze);

Note that "freezing" was applied directly via the Worksheet object $cart, rather than via a formatting object, because it applied to a collection of cells. Formatting, on the other hand, is applied to individual cells.

Finally, I loop through the items in the cart, adding the data to the sheet:

// Pseudo data
$items = array (
 array( 'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1),
 array( 'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2),
 array( 'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10),
);

// Use this to keep track of the current row number
$currentRow = 4;

// Loop through the data, adding it to the sheet
foreach ( $items as $item ) {
   // Write each item to the sheet
 $cart->writeRow($currentRow,0,$item);
 $currentRow++;
}

There's nothing particularly new here, except that you'll find it becomes important to keep track of row and column numbers as you loop through data, because these are needed to insert the data into the correct place.

That's basically it. If you're new to OOP in PHP, this may, at first glance, be a little intimidating, but you'll notice that all the methods are well named to the point where you can usually guess their purpose just by looking at them. The notion of fetching one object from another may be new, but when you think about it, it makes sense that you create a Worksheet object by calling the addWorksheetSheet() method of the Workbook object and that you add formatting objects to a cell at the point where you write() to the Worksheet.

(From: sitepoint)