On NetTuts there's a new tutorial showing how to take data from a MySQL database and translate it into a usable Excel file with the help of some simple PHP.

A CSV (Comma Separated Value) file is usually sufficient for exporting MySQL data as an Excel Spreadsheet. These CSV files are data only though. A real Excel spreadsheet has formatting, formulas and perhaps even graphics - the difference between a simple method of data transfer and a professional report. This tutorial shows how to use open source PHP components to create "real" Excel spreadsheets from MySQL SELECT statements.

With the help of the PHPExcel tool, making Excel-formatted files is a simple process. They show how to label columns, pull data out with a "quick and dirty" SQL statement, formatting the results to something a bit more clean and iterate through the pages of data to push them into the spreadsheet (including formulas). You can download the full source to get everything in one shot.

Brian Swan has a new post to his blog that looks at a method for pulling back the reports from a SQL Server instance in something a bit more readable/useful - an Excel document.

One of the most common questions [from his previous post] has been "How do I render a report as an Excel document?" I've been telling folks that this is easy with the SSRS SDK for PHP (and it is easy), but when I sat down to do it, I ran into a problem. So, in this post, I'll show you how to render a SSRS report as an Excel document and how to avoid the one problem that caused me headaches.

This post's a short one with a code snippet (and download) showing how to connect to the server and create a "RenderAsEXCEL" object and request the report information with that in the rendering function. Then it's just as simple as pushing that information out to a file as a ".xls". The included download will also let you pull down the report as HTML or as a PDF.

In a new post to his blog today Ilia Alshanetsky talks about a PHP Excel extension to help with some output generation problems he was seeing (based on the LibXL libraries).

As we are doing more & more Excel output generation, this became a bigger and bigger problem. At first we've tried solving the problem via a newer PHP based Excel library, PHPExcel. Unfortunately, it is massive beast, that is not only slower than the old Spreadsheet Excel Writer/Reader, but memory hog too. [...] So, I turned to Google and found LibXL, which is a small, C++ (with C, C++ interfaces) library that promised really fast Excel reading & writing.

His extension gives you an object-oriented interface to the library that can generate Excel files with a lot less resources than some of the other PHP Excel extensions out there. He includes a sample benchmarking script he used to generate these stats.

On the ThinkPHP blog today there's a new post looking at their experience with PHPExcel to open, modify, save, etc Microsoft Excel files directly from PHP.

EAR's Spreadsheet_Excel_Writer combined with the project Spreadsheet_Excel_Reader on SourceForge was a good helper in the past - but only for BIFF5. BIFF8 support in spreadsheet excel writer has been a problem for a long time, and according to the authors, is still somewhat kludgy in the current version. So I needed an alternative. After a short research I stumbled upon PHPExcel which supports reading and writing of many formats in one API.

He lists some of these input and output formats and includes a simple example of how to use the tool. He creates a basic Excel file with a few different attributes (title, body, keywords) and show how to read in a document to extract the document's content.

New on his blog today Pablo Viquez has this post looking at code that will let your Zend Framework applications export to Microsoft Excel spreadsheets (via the Spreadsheet Excel Writer PEAR component).

Last week, I had to allow the user to export a given report into an MS Excel file format. The application uses Zend Framework 1.9.1 and so far ZF does not support for "Office" formats, so after searching for a nice implementation, I found a PEAR module called Spreadsheet Excel Writter, which looked pretty good, it had very good documentation and the code was clean and well structured so I wanted I give it a shot.

He lays out the structure of the sample application and, using a context switch on the request, he creates a controller that responds to the "/report" by calling a special model/view combination that uses the PEAR package to push out the custom "report.xls" file.

Vinu Thomas has a new post to his blog looking at using the MS-Excel Stream Handler class to push your data out to Excel (in more than just a CSV file).

If you're looking for an easy way to output your data from a PHP script into a Excel file, you've got to check out this script which I came across at the PHP Classes site. What this script essentially does is to implement a stream handler interface to write Microsoft Excel spreadsheet files.

Code snippets are included showing how to structure the data for the import and how to push that generated Excel file out to the user's waiting client.

Juozas Kaziukenas has a new blog post that looks at creating Excel documents on the fly with the Spreadsheet_Excel_Writer PEAR package.

In one of my projects, we need to send emails to our suppliers with new orders as Excel spreadsheets. We used to do it by hand, but when you have 10 suppliers and you make 200 products orders per week, after some time you are just tired of creating Excel spreadsheets, mailing them and so on. So, my task was to implement automatic spreadsheets creation with PHP.

His example creates a basic file with a set of "Items", some formatting and a set of code/title items to fill out each row. The end result can then be packaged up and sent out as a complete Excel XLS file.

Maarten Balliauw has made a new blog post today about a method he's using to help reuse some of the business logic that Excel spreadsheets can have in a PHP script with help from PHPExcel.

In many companies, business logic resides in Excel. This business logic is sometimes created by business analysts and sometimes by business users who want to automate parts of their everyday job using Excel. [...] Did you know you can use PHPExcel to take advantage of the Excel-based business logic without having to worry about duplicate business logic?

He creates a quick example of a script that can take in an Excal file and pull it into a PHPExcel object, ready for manipulation. He fills in values for the already defined fields (like "carColor" or "leatherSeats") and uses the getCalculatedValue method to perform the action on the cell. The output is dropped into a variable that can be echoed out or used later on in the PHP script.

On the Zend Developer Zone, Vikram Vaswani has posted a tutorial that shows hos to "break the language barrier" between PHP and Microsoft's Excel to allow for the reading and writing of spreadsheet data directly from one to the other.

When it comes to playing nice with data in different formats, PHP's pedigree is hard to beat. Not only does the language make it a breeze to deal with SQL result sets and XML files, but it comes with extensions to deal with formats as diverse as Ogg/Vorbis audio files, ZIP archives and EXIF headers. So it should come as no surprise that PHP can also read and write Microsoft Excel spreadsheets, albeit with a little help from PEAR.

After grabbing the different parts needed (the PHP-ExcelReader package and the Spreadsheet_Excel_Writer PEAR package, he shows how to create a simple spreadsheet with just numeric information in it. For something a bit more interesting, he goes the other way and shows spreadsheet data as an HTML table.

Other examples included as well are things like: pushing spreadsheet data into a database, working with formulas and styling it to your liking.

Akash Mehta has pointed out five "PEAR gems" that can help you get your code up and running faster - some helpful bits of code to help you deal with some common issues.

Sifting through the repository is also a challenge; a basic category system is in place, but it's hard to tell what you want when you don't know what's available. Here are some gems from the PEAR repository that you could really find useful.

The five that made his list are:

• Service_Akismet
• HTTP_Request
• Archive_Tar
• Spreadsheet_Excel_Writer
• XML_Parser