CakePHP - Export data to Excel - The easy way!
Oct16
I am posting this method here just so we never ever ever ever…
forget what Albert Einstein once said
Everything should be made as simple as possible, but not simpler.
Exporting data to Excel files from a database can be done in so many different ways, using so many libraries and classes such as PEAR’s Spreadsheet_Excel_Writer
But here comes the easiest and simplest alternative if you are too busy to read too much instructions, or can’t install any modules or libraries to PHP on your host.
Large code snippets are great, but to save you some copying and pasting just download the source.
In the sample code, my model is called “Myview”, although not included in the zip as it can be any model you want, nothing goes in there.
You will also find the controller, layouts and views related.
All you need to is call that export_xls() method in myview_controller.php
All it does is:
- fetch your data with find(’all’)
- Renders the layout called “export_xls.ctp” in view/layouts
- Renders the view called “export_xls.ctp” in view/myview
REMEMBER:
- Excel document grid is a simple html table
- You can stylize and format your table using CSS
That’s it!
Enjoy this article?
Consider subscribing to our RSS feed!
3:17 am on November 12th, 2009
dude… its a brilliant art of programming… nice job man
5:14 am on November 24th, 2009
Thanks for such an clear and simple method. I second the brilliant comment above.
12:14 am on November 26th, 2009
This was AMAZING! I have literally spent a whole month trying to make the export work using another method. The quote from Albert Einstein is among my most favorite expressions. I was also able to make this solution work coming from an ExtJS grid.
Now if I can conquer filtering with CakePHP and ExtJS, my project will be complete.
Thank you again!
2:22 am on December 8th, 2009
This is great, but what about exporting 17000+ records? This results in a 17 MB file, which takes forever to load, but goes down to 3.5 MB when re-saving in Excel.
2:06 am on December 13th, 2009
Frederick: Glad it helped. I must admit I spent as much time as you thinking there must be a simpler way
Josh: yeah that can be troublesome when exporting large scale data. The reason why re-saving from Excel results in a much smaller file size, is that Excel converted text to binary. Try to open the new file in a text editor you will notice what i mean.
If you really need to export large set of data, then it would be worth investing the time and setting up the Pear’s spreadsheet excel writer I mentioned.
10:46 pm on January 5th, 2010
This seems to be the solution I’ve been looking for. I’ve installed everything as instructed but nothing happens. In Firefox, I’m redirected to /views/home.ctp for some reason and in Safari nothing happens at all. It’s definitely calling the function, but I’m never prompted to save the file.
Sorry so vague….any ideas?
10:21 am on January 6th, 2010
hey Brett, not sure why that is happening, as long as the model exists and your are rendering the proper view with the proper layout it should work as expected.
If you can zip up the sample code you are working with and send it to info at dnamique dot com, I will be able to help you out.
3:42 pm on February 2nd, 2010
Excellet, thank you very much
10:31 am on February 9th, 2010
Indeed, simply the best!
12:04 pm on February 16th, 2010
Thanks; works great!
One thing though: Excel gives the following error “the file you are trying to open ‘report.xls’ is in a different format than specified by the file extension.” ; when I open it it works perfectly, but still doesn’t look too good.
Any suggestions? Thanks.
10:53 am on February 19th, 2010
Great idea! Never thougt it would be so easy!
3:47 pm on February 19th, 2010
Gert: yeah I had that problem as well in latest versions of Excel. It doesn’t prevent the doc from opening though like you mentioned.
As in regards to how it looks, you can use CSS to style it if you want.
11:01 pm on February 25th, 2010
Thanks, mate, good work
2:37 pm on March 3rd, 2010
Very nice and good post!
9:38 am on March 13th, 2010
brilliant implementation of KISS methodology.
6:54 am on March 25th, 2010
works like a charm
hahaha, this is actually fooling the browser by set the header as xls format
nice one!!