Creating excel files through PHP

Mattias Geniar, Wednesday, June 4, 2008 - last modified: Sunday, February 19, 2012

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.

If that's not enough, you can look at more extensive libraries such as PHPExcel. As their examples show, it offers more advanced features.


Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek, public speaker and podcaster. If you're interested in keeping up with me, have a look at my podcast and weekly newsletter below. For more updates, follow me on Twitter as @mattiasgeniar.

SysCast podcast

In the SysCast podcast I talk about Linux & open source projects, interview sysadmins or developers and discuss web-related technologies. A show by and for geeks!

cron.weekly newsletter

A weekly newsletter - delivered every Sunday - for Linux sysadmins and open source users. It helps keeps you informed about open source projects, Linux guides & tutorials and the latest news.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!

Comments

Joshua Thursday, June 26, 2008 at 20:29

Nice method very simple and quick, highly recommended

Reply


GwB Thursday, July 3, 2008 at 00:36

You just saved me a lot of time. Very elegant…

Reply


Keith Friday, July 11, 2008 at 06:26

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.

Reply


    iNav Tuesday, December 27, 2011 at 11:16

    what do you mean by entered the filename correctly? what should it be?
    I can’t it to work on Safari or FF (hosting on MAMP)

    Reply


Keith Friday, July 11, 2008 at 06:56

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.

Reply


Matti Friday, July 11, 2008 at 08:11

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 :-)

Reply


    Denis Thursday, December 15, 2011 at 22:42

    Matti,

    Have you seen this?
    http://www.thetechnologytalk.com/2011/05/export-to-excel-2007-xlsx-using-php/

    As you probably know, the .xlsx format is a zipped xml package, so it should be easy to parse the html, output xml language that Excel understands, zip it again with the .xlsx extension and cron the shiznits out of it…
    Then you have a genuine Excel file and not a “I-am-Microsoft-and-I-will-try-and-open-that-file-for-you-and-see-how-it-goes” thing.

    Agreed, it’s not XLS, but it’s almost 2012 so… Time to ditch that format from 10 years ago (my 2 cents).

    Reply


      Denis Thursday, December 15, 2011 at 22:59

      (cancel the phpexcel link, it’s in your original post)

      Reply


      Matti Thursday, December 15, 2011 at 23:05

      The idea of creating own zipped XML files is interesting, have not actually considered it. :-)

      Reply


      Denis Thursday, December 15, 2011 at 23:33

      Yes yes, try it out. Make a new Excel file, put some random data in it, save as .xslx, rename it to .zip and extract it. All readable data (if you don’t put images or other binary data in it)!

      Reply


Tejas Monday, August 11, 2008 at 12:17

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!..

Reply


Matti Monday, August 11, 2008 at 13:00

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.

Reply


kaptenvu Thursday, January 22, 2009 at 04:55

Very nice trick.

Thx a lot ;)

Reply


Aamir Tuesday, February 3, 2009 at 07:14

Great Trick man!!
Awesome!

Reply


- Friday, February 27, 2009 at 10:58

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.

Reply


UngaMan Friday, April 10, 2009 at 03:09

Clean, elegant, efficient and most important: it does the job!

Great post!

Reply


Chris Monday, May 25, 2009 at 11:13

Does this trick work with background colors of tables cells and rows? Thx

Reply


Matti Monday, May 25, 2009 at 12:34

@Chris: it should, as your Excel application will just parse the HTML table and convert it to Excel rows/columns.

Reply


Dale Monday, July 20, 2009 at 21:15

Simply awesome. I nested some CSS and had a brilliant looking Excel file generated from PHP.

Reply


pahan Tuesday, August 4, 2009 at 08:49

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.

Reply


James Monday, September 21, 2009 at 13:18

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?

Reply


Matti Monday, September 21, 2009 at 22:11

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?

Reply


him_aeng Saturday, September 26, 2009 at 06:52

Simply the BEST!!!!!

Thank you for your rescue. YOU SAVE MY LIFE.

Reply


Emmanuel Wednesday, November 4, 2009 at 12:35

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?

Reply


maddog Thursday, November 26, 2009 at 10:35

How save thats files to zip? Is it posible ?

Reply


Matti Thursday, November 26, 2009 at 10:40

@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.

Reply


Rick Tuesday, December 15, 2009 at 14:58

Thanks for this, didn’t know I could just use html tables this helped me a lot.

Reply


Dragan Saturday, January 9, 2010 at 19:51

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 !

Reply


Bernat Monday, January 18, 2010 at 11:12

Using this way, is it possible to create an excel file with multiple sheets? Thnx.

Reply


ndtwc Tuesday, January 19, 2010 at 06:21

It works perfectly! Thanks!

Reply


Abhishek Goel Friday, February 12, 2010 at 22:31

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

Reply


Matti Sunday, February 14, 2010 at 21:58

@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. :)

Reply


Real Drouin Thursday, March 4, 2010 at 22:54

Very good.
Now the users wants the program to set automatically the header, the bottom, the margin, etc… Is it possible ?

Reply


Matti Friday, March 5, 2010 at 15:12

@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.

Reply


Rishi Saturday, March 20, 2010 at 17:55

Can anyone pls pls send me the php code to export a mySQL table data in Excel (proper formatted). Thanks!!

Reply


Matti Monday, March 22, 2010 at 12:03

@Rishi; take a look at PHPMyAdmin: http://www.phpmyadmin.net

Reply


sofie Monday, May 24, 2010 at 09:11

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?

Reply


Matti Tuesday, May 25, 2010 at 18:10

@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/…

Reply


    Denis Thursday, December 15, 2011 at 22:22

    @Matti,
    I believe the correct syntax for Excel is adding an apostrophe in front of the string, like this (when using PHP to generate the HTML):

    if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
    $str = "'$str";
    }

    There are also other things to account for, such as booleans, double quotes etc.
    This will take care of those pesky “+” and “0” interpretations of Excel.

    Reply


Chillipit Wednesday, July 14, 2010 at 14:52

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.

Reply


Karmel Thursday, July 22, 2010 at 14:31

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 … :)

Reply


shanoger Monday, August 9, 2010 at 17:24

The method worked perfectly, i have a small question nevertheless. How can i save the file locally i.e i am using wamp and want the code to save the excel file on the root folder which is c:/wamp/www in my case. Thanks in advance

Reply


Matti Tuesday, August 10, 2010 at 08:57

@shanoger; that’s more tricky, since it’s your browser that interprets the Excel-header, and handles it as an Excel file. If you want to save it, take a look at fopen() and fwrite() to write data to the local storage. It should be sufficient to ignore the header-data, and simply save the file as HTML and in the .XLS extension.

Reply


Sumedha Adhikari Monday, September 27, 2010 at 12:22

Thanks a lot. Its working smoothly…

Reply


John jairo Tuesday, December 7, 2010 at 18:39

Hello …
sorry wrote in Spanish

Excellent contribution.

The question is can generate an excel file with several pages …. ?? as serious ..??? thank you very much

Reply


Matti Tuesday, December 7, 2010 at 19:18

Hi John,

That’s not possible with this method. It’s a simple HTML parse, nothing more.
If you need more functionality, you should look into advanced Excel parsers/generators.

M.

Reply


david Wednesday, January 5, 2011 at 17:19

Is there a way to auto generate tabs in the excel document and put different information on the different tabs?

Reply


Matti Wednesday, January 5, 2011 at 17:23

@David: see the comment above yours: not with this technique, no.

Reply


Amien Thursday, February 3, 2011 at 23:15

Legend.

Reply


Anton Wednesday, February 16, 2011 at 18:14

Thanks, it really helps me!

Reply


harry Saturday, April 9, 2011 at 09:02

I’m not sure of how to use it. Could you please post an example.

Thanks!

Reply


harry Saturday, April 9, 2011 at 09:19

Not an issue. Got it. Works very well and solves my purpose.

The only issue I’m facing is with phone number and excel is showing long phone number as “9.17417E+11”, is there any way to make to make it numeric. Thanks a lot!

Reply


    Matti Sunday, April 10, 2011 at 13:22

    It’s because Excel will parse those as numbers, instead of actual “string” values consisting of numbers. You could “cheat” by prefixing that field with a “+” sign, or a space, so Excel doesn’t see it as a parseable integer.

    Reply


Anton Boutkam Wednesday, June 22, 2011 at 15:40

Hm, thats a funny sollution!
Unfortunately Open office does not support this way of generating spreadsheets.

Reply


parth Monday, November 7, 2011 at 08:55

Does this trick work for windows 7 64-bit version. Coz it worked on an xp machine but somehow the excel file doesnt get generated on a windows 7 machine .Please help me out .Thanks in advance

Reply


Jo Tuesday, November 15, 2011 at 13:18

Got this working great bar two things:

– numbers are losing their decimal places if they contain a zero (e.g. 5.00 is now 5 or 5.10 is now 5.1)
I can see from the comments above that adding a space or something before to trick Excel into not seeing it as a parseable integer but this didn’t work – is there a way to tell Excel to format that field as numerical?

– a message shows saying ‘the file you are trying to open is in a different format than specified by the file extension’ each time
I can see from the comments above that a solution to the message that shows is to trick Excel to open in 2003 mode but I can’t find a way to do this – do you know of a way?

Thanks!

Reply


Alex Monday, December 12, 2011 at 22:09

@Jo
For point #1, please keep in mind that Excel is only kindly parsing HTML, with no indication of format whatsoever. That means that any formatting you desire needs to have been performed while producing the HTML contents.

Regarding your point #2, although I cannot test it myself currently, I would suggest you go
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=excel.html”);
First header would lead your browser to invoke Excel, second header *should* prevent Excel from mourning – am suggesting this only below control of Matti :)

OBTW @Matti: I’m used to saying “simplest is the best”, and you help me keeping thinking so – awesome :)

Best 2U all

Reply


André Thursday, January 5, 2012 at 09:50

Hi,
great!
But i have a Problem with UTF8 cyrillic :(
just strange Signs :(

added
header(“Content-Type: text/html; charset=utf-8”);
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=excel.xls”);

excel.php is saved as UTF8

still not working :(

any solutions??

THX

Reply


    Matti Thursday, January 5, 2012 at 10:01

    I think you’ll need a proper Excel wrapper for that. For basic, UTF8/Latin9, usage this trick will work fine, but you’re relying on Excel to parse the content. Strange non-unicode characters will definetely make that difficult.

    Reply


    Pavel Monday, July 16, 2012 at 09:42

    Add Bom:

    $csv = pack(“CCC”, 0xef, 0xbb, 0xbf) . $csv;

    Reply


      olivM Tuesday, February 3, 2015 at 16:52

      thansk Pavel for this trick !!

      Reply


nick Monday, February 6, 2012 at 22:44

Using this or other code like it I can make the file pop up on the browser to open or save. But how can I attach the file to an email and send it instead of opening it or saving it?

Reply


    Andy Thursday, November 8, 2012 at 16:59

    Did you find a resolution to this? I am also interested in it.

    Reply


paris Friday, July 20, 2012 at 15:49

Thank you . I left hope before coming to your site. Gave a last try and it worked thanks a lot

Reply


Egbert O'Foo Friday, September 14, 2012 at 22:03

You’ll need some way to communicate to your downloader that he/she should expect to see a warning when Excel (2010) detects it has to do a conversion.

But, it works Just Fine(tm) otherwise. Thank you!

Reply


John Saturday, October 6, 2012 at 20:21

If I style my html table will the colors and stuff go along with it?

Reply


Leave a Reply

Your email address will not be published. Required fields are marked *