Creating excel files through PHP

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.

The Social Box

You can sign up for more updates via Twitter or Facebook below. On Twitter, I regularly talk about technology or tweet about interesting stories. Topics that don't necessarily make it to this blog. Facebook contains a steady update of blogposts and some more lightweight stories.

The @mattiasrss account has an automated RSS feed of all blogposts that get published.




Write a Comment

Do you care about the markup if your comment? You can use the following HTML tags:

<code>command</code>: command highlighting
<pre>text</pre>: pre-formatted code, can be multi-line (black background, white letters)

example <pre> tag
<blockquote>text</blockquote> quoted text
quoted example


None of this is needed of course, it's all optional!

Comment

*

66 Comments

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

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

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

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

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

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

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

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

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

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

  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?

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

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

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

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

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

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

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

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

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

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

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

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

  22. 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?