Adding Excel Functions
Now, you're able to make a spreadsheet that looks nice but, as any Excel Pro can tell you, simply displaying raw data isn't very useful. Life gets really interesting when you start using Excel's functions (and perhaps you own) to perform calculations on the raw data and turn it into something more interesting.
Now I'm not an Excel master (and this is not about to turn into an Excel tutorial) but it is clear that my shopping cart receipt needs to be cleverer, so I need to add some calculations based on the data I've already added. For each row, I want to display the "total item cost" -- the raw data contains the unit price on the item and the number of items purchased:
"total item cost" = "unit price" * "number of items purchased"
In terms of Excel, to calculate the total for the item on the fifth row, the formula might be:
[Cell D5] =PRODUCT(B5:C5)
To accomplish this with PEAR::Spreadsheet_Excel_Writer, I need to modify slightly the code that loops through the data:
// 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);
// Remember Excel starts counting rows from #1!
$excelRow = $currentRow + 1;
// Create a PHP string containing the formula
$formula = '=PRODUCT(B' . $excelRow . ':C' . $excelRow .')';
// Add the formula to the row
$cart->writeFormula($currentRow,3,$formula);
$currentRow++;
}
Adding the formula itself is pretty easy -- we simply use the writeFormula() method. But most important (and confusing) is what I mentioned earlier -- Excel begins to count rows from 1, while PEAR::Spreadsheet_Excel_Writer begins at 0 (zero), so, when creating functions, I need to remember this or I'll be referring to the wrong cells. This is why I created the variable $excelRow, which is the $currentRow plus one. You may think this a design flaw on behalf of the authors, but remember: in PHP, like most programming languages, indexed arrays begin with a zero index. Trying to bump them forward by one just to play nice with Excel would likely have lead to many bugs and maintenance headaches. If it really annoys you, knock up some functions to translate between the two.
So, now my sheet displays the item totals on each row. But what about totaling the totals, so the customers can see the figure that will appear on their credit card bill? For this, it's simply a matter of adding all the item totals together and displaying the result in another cell.
In Excel terms, I need to use the SUM() function to add item totals, which appear in column D:
[Grand Total Cell] =SUM(D5:D7)
To fit this into the spreadsheet, after the loop has finished, I add the following:
// The first row as Excel knows it - $currentRow was 4 at the start
$startingExcelRow = 5;
// The final row as Excel
// (which is the same as the currentRow once the loop ends)
$finalExcelRow = $currentRow;
// Excel formal to sum all the item totals to get the grand total
$gTFormula = '=SUM(D'.$startingExcelRow.':D'.$finalExcelRow.')';
// Some more formatting for the grand total cells
$gTFormat =& $xls->addFormat();
$gTFormat->setFontFamily('Helvetica');
$gTFormat->setBold();
$gTFormat->setTop(1); // Top border
$gTFormat->setBottom(1); // Bottom border
// Add some text plus formatting
$cart->write($currentRow,2,'Grand Total:',$gTFormat);
// Add the grand total formula along with the format
$cart->writeFormula($currentRow,3,$gTFormula,$gTFormat);
Again it gets even more exciting, but keeping track of the Excel row numbers is mostly a case of remembering to add one to whichever variable has been tracking the PEAR::Spreadsheet_Excel_Writer row numbers. Notice also that I can apply formatting to the output produced by the formula.
Finally, I finish of my shopping cart receipt by sending the spreadsheet straight to the browser:
// Send the Spreadsheet to the browser
$xls->send("phpPetstore.xls");
$xls->close();
?>
Filename: phpPetstore.php
That's it. The spreadsheet is ready to download. The finished code is available here.
Wrap Up
As you've seen, PEAR::Spreadsheet_Excel_Writer offers pretty much everything you need to build a useful spreadsheet, including formatting and functions. And, because you're tapping into Excel's functionality as well, you've got a lot of power at your disposal.
The API is tidy and, once you get used to it, easy to work with. The classes are also well structured, so scaling what I've done here up to a workbook that contains numerous, interrelated sheets is relatively easily accomplished. Be aware, though, that as you've seen here, you can end up with some pretty lengthy scripts if you're not careful, particularly as formatting has to be defined in fine detail. If you have a need to do some serious work with PEAR::Spreadsheet_Excel_Writer it's worth considering the opportunities for building in re-use early. You may find there's a particular cell format that keeps cropping up all over, and could be better placed in a class and re-used. If you're building a workbook containing many similar worksheets (e.g. Sales figures broken down with a worksheet for each region), writing classes to act as a template (design pattern hint) that generates the sheets may save a lot of effort.
Overall, PEAR::Spreadsheet_Excel_Writer is great addition to your PHP toolbox you're your users are bugging you because they can't get the "view" they want on the data you deliver to them with HTML, Spreadsheet_Excel_Writer provides a handy alternative to implementing a never-ending list of new features. What's more, it creates a "wow" factor with which you can impress a potential client -- particularly if the client in question uses Excel as their daily bread and butter.
The subject of PHP and Excel becomes even more interesting when you consider Jedox's Worksheet Server, a tool for reading (here we were just writing) Excel spreadsheets, and generating PHP applications from them. But that's a story for another time... (From: sitepoint)
|
Sitemap Links Advertising
Copyright © 2003-2005 downcodes.comregistration numbers:Hebei ICP: 05003145
