[/caption]Note: I may seem a bit harsh on Excel, but I actually think it's an amazing piece of technology, I just don't like how people abuse it.
Web development for business people follows a fairly predictable path. Here is a generalized form of it.
- Business person encounters problem
- Grabs Excel, Excel being the magic fix for everything
- Creates abomination of a solution in Excel, it grows too large, concurrency and consistency issues creep in
- At this point normally a web developer will be brought in to "Make this Excel sheet work on the internets"
- Work work work work work...
- Everyone is delighted
- Hey is there anyway we can dump these value back into Excel?!
- At some point they will use the dump to create some new spreadsheet, go back to step 3.
Now of course it's not always this bad and normally there are really good reasons to dump things back into Excel, instead of bothering the web developer with a bunch of business analysis, they can dump the data and perform the Excel wizardry they love so much. I actually think this is a good idea, instead of me baking in calculated values or reinventing Averages and Means and all the other cool stuff Excel can already do, I just drop them off some nice raw data and let them have their business fun. They get exactly what they need, I get exactly what I want (less fragile code with less crazy business rules) and everyone is happy.
Now in PHP there are various solutions to dumping out Excel. The first would be the venerable Spreadsheet_Excel_Writer. I've used it before and it works, it's a port of a Perl library called Spreadsheet::WriteExcel, but there are a few drawbacks. The first is that it's a PEAR module, you use PEAR to install it and it brings along all kind of PEAR stuff like PEAR_Exception and the like. The second is that on the homepage for Spreadsheet_Excel_Writer is this very happy message, "Spreadsheet_Excel_Writer is outdated and needs a complete rewrite. If you want to help with this task please get in touch with us. Otherwise we don't recommend this package for new development." So in short, we should avoid Spreadsheet_Excel_Writer.
The next thing you might find in your googling is PHPExcel which writes out OpenXML 2007 files. This is another large and much more well maintained library for working with Excel files. If you need any advanced features I would highly suggest looking into this one.
But I want something simpler than this, I just want to dump some data out. I don't need to have all these bells and whistles. Also I hate the fact that to write out a few cells in most of these Excel Libraries requires the following.
[php]
$objPHPExcel = new PHPExcel();
// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('D2', 'world!');
[/php]
The way these libraries work are just, ugh. They are very heavy and when you are just trying to plop some values into a spreadsheet so someone can have at them, they are overkill. I found myself rewriting the same solution again and again, it's a fancy little solution using some pack() magic that I found over here. Here's what a simple little spreadsheet looks like.
[php]
require_once('Excel.php');
$sheet = new Excel('My Awesome Spreadsheet');
$sheet->label('Hello'); //Write out 'Hello'
$sheet->left(); //Move the cursor left
$sheet->label('World'); //Write out 'World'
$sheet->down(); //Move the cursor down
$sheet->home(); //Head as far left as possible
$sheet->number(1337); //We are so 1337
$sheet->send(); //Melt the user's face off
[/php]
This has met my needs and you can find it as a Gist right over here. I wanted an object oriented way to use those functions and I wanted some help in moving the cursor around and some simple sanity checks on things so that I didn't ruin anything. It's got plenty of comments in code and should be pretty simple to pick up. You can write out labels or numbers and you can move the cursor all around, left, right, up, down, top, and home (top being the first row, home being the first column).
For right now this is meeting all my needs and more, I've recently been playing around with the fact that Excel will happily turn HTML Tables into Spreadsheets, I've got a library cooking for this but have been playing around with some interesting ideas for styling and other advanced features. Until then hopefully this will help any PHP programmer who just wants to dump out some data without going into dependency hell.
Happy Coding
