Outputting Data in Excel .xls format

January 22, 2008 – 5:51 pm

All clients love data! The only thing they like more than data is data in a spreadsheet, and that’s where we would be in all sorts of trouble if we had to rely on special export libraries that in turn have to keep track with the dataformat - whims of Microsoft. Thankfully there’s a more durable solution, so your application will still be happily exporting in the future.

So here’s how you can quickly spit out a table for Excel:

Simply set the header to the Excel content type, as such:

header(”Content-type: application/vnd.ms-excel”);
header(”Content-Disposition: attachment; filename={your_filename_here}.xls;”);

and spit out a simple table that contains your data.

The filename part is optional, but a nice touch is to include the current time in there, to avoid overwriting archived data.

Obviously you have to do this before you send any output to the browser, since you’re tinkering with the header.., or scratch that if you’re ob_buffering().

Either way, what’s happening is that your browser pushes out a “file” and the filetype forces the client to open it with Excel. Now, Excel is very smart about incoming data, and it will do its best to push it all into the right cells. Even basic styling survives.. (background colors, colors and simple borders), and since that’s likely to stay that way, your export is pretty future-proof. Sweet :)

PS: Naturally this only works if Excel is installed. The file will still be created, but it won’t open in Excel automatically.

ADDENDUM:

Ever since the Open XML format used by Microsoft Office documents became open to the public, a number of PHP Excel libraries have popped up that let you do just about everything with an excel file via code (sing, dance, style cells & run formulas). The best one I found is so fittingly called: PHPExcel and I lovit lovit lovit :)


Filed under: Rnadom Sftuf — by Richtermeister

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress