No need to use complicated or extensive libraries (such as PEAR) to create Excel pages. Just take advantage of the “smart” part of Excel; its ability to parse an HTML table to a nice Excel page.
Just create a regular .PHP file, where you output your data in a nice little html-table. Then place the following snippet in the top of that file (no output can happen before these lines, as they change your headers – so place these all the way at the top):
header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=excel.xls");
And it’s just that easy. If you open the page, you’ll see a download-window asking you where to place the file. The headers will tell your computer that it’s an Excel-file, which will parse your html-table (assuming you’ve written it without errors) and display it nicely.
Nice method very simple and quick, highly recommended
You just saved me a lot of time. Very elegant…
It seems to work in IE, but doesn’t work in Safari or Firefox. Is there something else I need for those browsers?
Thanks for the great tip. Certainly a lot easier then other solutions I’ve found.
Never mind. It worked beautifully once I entered the filename correctly on the second header line.
Thanks again for such a huge simple piece of code.
Hi Keith, thanks for the comment. It is indeed one of the easier solutions, but it only works when browsing directly to a page, and downloading the file.
I have yet to find a very simple method for exporting it on a regular basis (through cronjobs), without having user interaction.
If anyone has any idea on how to do so, please let me know :-)
really nice method..
but i have one question…
can I create multiple excel files using single Header() in single PHP code..???
if possible, please reply me. I need it very urgent.
Thank You!..
Hi Tejas,
You can only create one file at a time using this method, allthough you could open multiple windows which call this page multiple times. A main page with some iFrames that load your PHP Excel page?
This method really is only useful for a quick export of your data, not for multiple files that you want to store on your hard disk. It’ll be better to use dedicated classes for that.
Very nice trick.
Thx a lot ;)
Great Trick man!!
Awesome!
It works and I’ve used it with some simple data. Problem with this system is that you can’t control how excel interprets your data. You will end up with numbers converted to dates and some other problems.
There are some free components for doing this so you’re safer using those.
Clean, elegant, efficient and most important: it does the job!
Great post!
Does this trick work with background colors of tables cells and rows? Thx
@Chris: it should, as your Excel application will just parse the HTML table and convert it to Excel rows/columns.
Simply awesome. I nested some CSS and had a brilliant looking Excel file generated from PHP.
nice and helpful article. I tried so meny method to this and this is the best thing but all Excel sheets give an error in opening.(Asks whether it is from a trusted source.) but Grideline are not visible. Any idea about making these guidelines visible?
thank you.
I tried this for word 2007 and it throws an error that you have to click through in order to get to the excel page. Anyway around this?
I doubt it, but haven’t tried it yet. It’s a work-around for simple Excel exports, but it’s flawed in many ways since it will have to parse HTML code, where it was expecting properly formatted Excel code. You might be able to trick it by passing some headers, to simulate Excel 2003?
Simply the BEST!!!!!
Thank you for your rescue. YOU SAVE MY LIFE.
Thank you so much for the simple but nice and efficient tutorial. How ever, am having a question. How can I set the generated Excel to be in read & write mode?
How save thats files to zip? Is it posible ?
@Maddog; you could use PHP’s ZIP functionality, to process the Excel output and run it through a compression filter; http://be.php.net/zip. Not the recommended way to do this, though.
Thanks for this, didn’t know I could just use html tables this helped me a lot.
I am having trouble exporting some latin characters i.e PHP code works great but OpenOffice failes to show all the characters.. .any help on this? Thanks !
Using this way, is it possible to create an excel file with multiple sheets? Thnx.
It works perfectly! Thanks!
You’re a lifesaver. I spent two days struggling with packages like phpexcel for doing something which you showed to be as simple as adding two lines to existing code.
Thanks.
Abhishek
@Dragan; I guess that would also depend on the character encoding of the server. No experience with exporting to OpenOffice though, so I’m sorry I can’t be of more help.
@Bernat; no, only one sheet is possible, since it’s more of a dirty work-around/hack.
@Others; Thanks for the feedback, appreciate it. :)
Very good.
Now the users wants the program to set automatically the header, the bottom, the margin, etc… Is it possible ?
@Real: you can do all your mark-up with HTML, and Excell with parse it (or at least, some of it). Nothing fancy, just use CSS/headers in HTML and watch how Excel handles it.
Can anyone pls pls send me the php code to export a mySQL table data in Excel (proper formatted). Thanks!!
@Rishi; take a look at PHPMyAdmin: http://www.phpmyadmin.net
i’ve implemented this coding. It’s working fine for excel 2007, however for excel 2003, some output columns get a weird format in Excel. Any idea why this difference?
@Sofie; what kind of output are you getting? Are they date or numeric values? You could try putting a ” ” (empty space) in front of it, so Excel doesn’t parse it as a number/date/expression/…
Thank you very much. This has got be one of the most useful articles I’ve come across in a very long time. So simple yet it never would have crossed my mind.
Great script … !!!
Very very helpful.
>> I have one case related to DATABASE-&-EXCEL.
I have a form in excel which already designed as a request.
Can we just get the data from database and put it into the pointed cell? And save the excel file in the end of process?
Can anyone help me? Really need your help/advice.
Thanks in advance … :)