Easily Output Excel Files From a Symfony Application

[update]: I published a new article with other (and in my opinion way better) methods on how to do those things.

In the application we are creating we need to output some of out content as excel documents. Most of the times the actions that are going to be used are the same as when we output html content.

You have a list of contacts for a given company, but the user wants to get it as an excel file so he can manipulate it a bit, same goes for the contracts list, the products list etc etc etc…

There are a few solution that exists if you want to create Excel files from php and html code. I will describe some of them and why I didn’t use those methods, and explain the method I used, why it suits me, and where I am still looking for possible improvement.

Possible (but dismissed) solutions

Output HTML, change the content-type

This is a very convenient, easy and fast solution. You just output a standard HTML formatted table, set the headers so that the content type appears to be Excel. On the user side, the browser will detect the content as being of a certain type that it does not know how to deal with, ask the OS “hey how do we work with this, you got something?”. The OS will say “oh, this opens with Excel”. So far so good.

However, Excel will give the user a warning that the file format is incorrect and ask if he really wants to open the file (this is not necessary a big issue, but certainly does not look pretty).

The main problem with this solution comes when you want to have multiple worksheet in this Excel document. You have to play some tricky things with the mime-type to send your response correctly.

All in all, we are looking, if possible for something more clean, but that would keep the simplicity as much as possible.

OpenXML

That was my second idea: Office documents are supposed to be XML based since the 2007 version. If I could write XML (like a standard symfony template) and then transform it into an Excel file, that would be perfect.

I didn’t find any simple way to do this. ‘xlsx’ documents are a zipped set of XML documents. However those documents are not simple to use. I didn’t study the OpenXML format for too long, but it seems that all the values used in a sheet need to be referenced from another XML document that stores all the values for all the sheets. It means I would need to write not one but many templates, and the format does not seem so easy to use.

PHPExcel

Part of this solution is used in my final solution, but to use PHPExcel in order to create an Excel workbook, a lot of code needs to be written. I want something more like a template that would just be from a different format. I want the ease I can have when I want to output xml, json or html for the same action.

Office XML 2003

So far this is the best solution I have found. This format is a single XML file for the whole workbook. Using PHPExcel, I can easily transform an XML file into an Excel 2007 file, I just need to make it clean in Symfony so that I don’t need to deal with special cases in every action. And writing the XML result is going to be just like writing an xml template.

Let’s do this!

My action here will do absolutely nothing. Just return the template. I’ll include a little bit of PHP code in the template, just to be sure that it still works, but that’s it!

We need to create a new format for symfony since xlsx is not a standard format already included in the default symfony configuration. For this, we will add a line to factory.yml, it will give the name of our new format and also the content-type that will be returned.

Obviously, this is not enough yet. The routes that are going to use this need to be built with the special sf_format variable in order for us to be able to request http://myurl.html and http://myurl.xlsx and get different output. We could for example have a route like this:

Everything seems almost ready, now we need to add the template, mine is like this:

From within Excel, you can save any excel file you already have built in the Excel XML Spreadsheet 2003 format to have the complete file. This way you can make all your modifications of style, color etc… Through Excel instead of completely writing the XML yourself.

Now if this was enough to make it work it would be incredibly great. But Excel can’t directly open this XML format. This is when PHPExcel comes in. With PHPExcel, we can open and XML Spreadsheet 2003 document and output a xlsx Office 2007 one. To do this, we need to know when we are dealing with Excel format, and in that case, we will intercept the response and modify it.

The symfony event system comes to our rescue, there is an event fired when the format is set, so we’ll know when we are dealing with Excel format. And one once the response content has been generated. I created a class called MyExcel with 2 methods that will subscribe to these two events. Subscribing to the events is done in the ProjectConfiguration class:

In the setFormat, we will only set a static variable with the value of the format. In setContent, we check that the format is xlsx or not. If so, we use PHPExcel to create a real excel file instead of the XML.

This is when I have a problem. PHPExcel does not seem to have any method to directly load an XML string. However it can load an XML file. I found the lines of code responsible for this, but they are buried in a very lengthy function. Inheriting this file reader class to override that method and change just those lines does not work. Changing them directly in the class file does work, but this is not something I want to do because everything is going to break once I update to a new version of PHPExcel if I ever need to.

So right now I have to save my XML response in a file and then load that file with PHPExcel and send it. The code is like this:

Normally, events called through the ‘filter’ method, like ‘response.filter_content’ have to return the modified response. However in our case, we need to output everything ourselves and need to avoid any content to be written after ours so we don’t return anything.

We only include the libraries of PHPExcel at the last time, they are not needed for any other part of our application, there is no reason to load them all the time.

And this time that really is it. The post is very long, but the actual content is pretty simple. Now Excel files are just like any other format. You just write your SpreadsheetML, you can use slots, layout, components and everything as long as you include the format in your url.

As usual, I hope this helps, and for this article in specific, feedback is welcome as I feel things could be made even better.