Export MySQL to Excel (.xls) using PHP

The below code will export every column name and value from your database into an excel document (.xls).

Note: Delete the line spaces if you receive a parse error.

PHP Script to export MySQL to Excel. (.xls) #php #snippet Click To Tweet

85 thoughts on “Export MySQL to Excel (.xls) using PHP

  1. Hi,
    I have a error: Parse error: syntax error, unexpected ‘;’, expecting ‘)’
    which piece of code should I change?

    • Dipak C. Gajjar

      Didn’t you get line number with the error?

      I suggest you make sure you’ve copied the above code properly. I just checked and can’t replicate the error you’ve mentioned.

      ~ Dipak G.

      • I am using the code everything works good but when i change the query to complex one creating joins on multiple tables in the same database then it doesnot works. I will be really thankful if you suggest me solution to that.

  2. Yes, it works. By the way, how to select only several column and rename the column header

    • Dipak C. Gajjar

      To select only specific columns, you have to change the query at line 12 in the above code.

      Let’s say you want to display only 2 columns first_name and last_name by renaming it’s title ‘First Name’ and ‘Last Name’ in the results, you can use something like…

      $sql = “Select first_name as ‘First Name’, last_name as ‘Last Name’ from $DB_TBLName”;

      I hope this helps.

      ~ Dipak G.

  3. Hi Dipak,
    These codes are perfectly worked.by the way exported xls file dont show D-M-Y. how can i do that??
    Thank you.

  4. Thank you for the codes sir!!! 🙂 It helped a lot!

  5. Hi. I used this code and it works. But when i open file it says- ” The file you are trying to open is in different format than specified by the file extension.”

    Second – Date and time shows like this – ####. But when I make the column broad. It appear perfectly.

    Third when I see its print preview it doesn’t show the borders.While if we create a excel file from scratch it shows border in print preview. That mean if client want to print. He can’t get the border which is important for the column distinguish.

    Any help be appreciated.

    Thanks

    • Dipak C. Gajjar

      Hi Jimmy,

      The best place to get answers of your questions is Google.

      Thanks,
      ~ Dipak G.

    • hey did u get ur ans??i had same problem please tell me

    • You have to put the borders manually in excel because even the mysql table had no borders during the creation of the table.

  6. Harold Stuart

    Thank you !!

  7. Hello, thanks for the post it is indeed very helpful. My main problem is that the produced .xls file instead of showing the contents of the selected table it only shows the contents of my main page (text, metadata etc). I’m using this in my wordpress site.

    Do you have any idea if i have to make any changes?

    Thank you for your kind help.

    • Dipak C. Gajjar

      Can you post a query-statement of line 12 ?

      • this is my query
        $result = $wpdb->get_results(“SELECT * FROM jma_users”);

        if i just echo the $result array i get perfectly correct answers. It’s just that the produced .xls file containts my First Page and not those results!! any ideas? Thank you in advance.

        • Dipak C. Gajjar

          So you mean you have updated line 12 in the above script to something like…

          $sql = “SELECT * FROM jma_users”;

  8. If i got your question right, my query results are correct. if i just echo them i get correct results

  9. Thanks, help me a lot!

  10. hi! your code is very helpful 😀 i have a problem 🙁 i have a lot of table to export in excel 🙁 i dunno the code to add to read the other table 🙁 can you help me?

    • Dipak C. Gajjar

      To do any MySQL related modifications, you have to update a query at line 12 in the above code.

      P.S: These tutorials are meant to be helpful, but please note that I can not and will not be able to help with any implementations or modifications in FREE. You can hire me to do it for you.

      • i have to connect 9 php to one php file for exporting to excel but it only read one php file in exporting 🙁 so you can’t help me?:(

  11. thanks anyway 😀

  12. its doesn’t create a download link but displays everything in the browser… what changes i have to make.

  13. thank you it works great .. but i have an error with encoding the Arabic results !
    the xls file replaces the Arabic letters with ‘????’ marks .. can you help me with this ?

  14. hello dipak , i want to export data from webpage to excel pls help me

  15. i want to select all fields but with custom fields name.
    i have all fields name in array, how i can print them to excel file.

  16. thank you, it works.. 🙂

  17. It looks like your code is working…. but where i could find the xls file and how i could insert it in a html button download?

  18. Abhinav Bansal

    Thank you it saved a lot of my work going back and forth to php my admin to accomplish certain tasks

  19. Possible to capitalize all table cells before output? Willing to pay for assistance.

    (For example: First, Last, Location becomes FIRST, LAST, LOCATION)

    • Nevermind, strtoupper worked perfectly for my needs.

      • Dipak C. Gajjar

        Great to hear that you’ve fixed it already!

  20. Hello, I adapted your code to mysqli

    But I can’t find a solution, all my data appears together in line. For example; I exported some data and it appears like this:

    (field) id,name,email,city
    (field2) 12,john,mail@ex,california

    but no columns… is there a way to put all data in each cell?

  21. Yuri Gagarin

    Can I use your code for my convenience???, I need something like your code. Thanks.

    • Dipak C. Gajjar

      Yes, you’re free to use this code as per your needs.

  22. Prashant Bhatt

    it is working properly but i want to export selected records into excel. how it’s possible?

    • Dipak C. Gajjar

      I already answered the same question above.

      Read here to know how to export selected records from MySQL to excel.

      • Prashant Bhatt

        me read ur reply that me ask u on April 6, 2015
        but my requirement is like that
        not a specific Coloumn i want to export specific records with this example it’s export every records of that table but i want to export selected recoerd..

  23. Prashant Bhatt

    i want to export selected data from that table how it’s possible and also i want to give own fields name in excel….

  24. Hi, it works perfect, such a good job !

    I was wondering if i can add a border on excel using this code ?

  25. Yuri Gomes

    thanks u =D

  26. masamune

    Hey, it works almost perfect.
    The problem is that data exported to excel is missing the leading zero. So instead of number 012345 i get 12345.

  27. kumaresan

    Hi,How can I give color to each row in the excel through this code.?

  28. this is not properly working when me export into excel it’s give me all fields in one column

    • Dipak C. Gajjar

      You’re the only person who having issues with the above code all the times.

      If you do not understand it, hire me to customise it for you.

  29. just perfect mate, well done

  30. Supun Buddharage

    thanks,
    how to add report name on excel heet

  31. thanks,
    how to make that read Russian letters?

  32. HI Thanks for the code it’s really nice,Can you please advice how to mark cell at color like if export data value less then 2 then it will mark red if data>2 it will mark green if data==2 then there are no color code. Please advice thanks.

  33. Hello,

    How i can export to XLSX format?

  34. I have to join two tables together, how can i view the details of the two tables together in excel, do reply quick

  35. Hey, this is great! I was wondering if you can add data to separate sheets this way?

  36. it works well but i use multiple Mobile numbers in a cell it separates by comma
    98,416,379,529,801,000,000

  37. How can I download the created file directly from the browser?

  38. Changing different browsers doesn’t help me as mentioned in one of the comments above.
    Please suggest some way to implement the download.

  39. Will J B Hus

    Same issue as dndautoes. All the data appear in the browser, but no download. I tried several browsers. I didn’t change anything in the code, except the necessary inlog en query data.

    • Dipak C. Gajjar

      Could you try to replace headers?

      Replace this (lines 20-23)…

      header("Content-Type: application/xls");
      header("Content-Disposition: attachment; filename=$xls_filename");
      header("Pragma: no-cache");
      header("Expires: 0");

      with this…

      header("Content-Type: application/vnd.ms-excel; charset=utf-8");
      header("Content-Disposition: attachment; filename=$xls_filename");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Cache-Control: private",false);

      Let me know how this goes?

  40. great tutorial thanx

  41. thank you so much

  42. hi Dipak
    i have problem

    Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 159

    Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 160

    Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 161

    Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 162

    Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 163
    id pname pdisc delar dp mrp pamt pqty alert scode date mfdate epdate item remark 29 test1 reguler xyz 9 11 10 70 30 2015-10-28 00:10:20 1970-01-01 1970-01-01 0 26 test2 reguler xyz 10 12 11 100 10 2015-10-27 21:15:10 1970-01-01 1970-01-01 0 23 test8 reguler xyz 10 12 11 50 20 2015-10-27 21:15:17 1970-01-01 1970-01-01 0

    can you help me

    thanx
    rajkumar

  43. hi guys, it worked in browser but not in server. What should I do If I want to save it in server automatically?

  44. woww!! woww!! you are great man. saved me a lot of time. Not even Stackoverflow could do this. It worked without any disturbance. actually, since i had many tables in the database, i made a variable for table name and got it using $_Get from the multiselect on the previous page.

  45. gauri deshmukh

    Hey can u tell me how to export complete data from particular database using php.

  46. Wow.This code works amazingly.Thank you so much.

  47. Sir,
    how can i export data to excel from two tables

  48. Hai,

    when i use this code all the values are printing on web page.

    How to export those values to excel. i am a beginner to php can u please explain me how to export those values into excel.

  49. Cool , thanks bro.. i need this , save my time for my new project, but i have some problem with number, my data fields is 00021100332 , result in export excel is 21100332 .. please help whit this

  50. Hello.
    I have a problem … i use this script, but he risult is displaying in TXT in the browser window …. not in a xls filename. Have you a solution ? I have PHP 5.5.
    Thank you.

  51. g9848438

    Hi buddy, I have just found your post (almost 2 years after you created it!) It’s a very nice piece of code and it works for me… but when openning the exported file, Excel shows this error: “The file you are trying to open, ‘name.ext’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”. If clicking ‘Yes’, the import text wizard is ‘triggered’ and after the import process, the exported data table is shown.

    Is this the intended behaviour when using your code? Or it should be loaded as an Excel file directly (not as a imported text file)?

    • Dipak C. Gajjar

      That warning is just a security feature of Excel. (you can ignore it.)

      In such questions, Google is your best friend.

      • g9848438

        Yeah buddy, I know that it’s an Excel warning and I already googled it before asking here. The point is that your code generates a csv file, not a proper Excel file (and that’s why Excel triggers the warning), so I wonder if this is the code’s intended behaviour, or it should be generated as a proper Excel file without warnings

  52. Hi Dipak,
    your php script is exactly what i need. So many thanks for that.
    It works like a charme, but when it comes to a column with multiple values looking like that [“8″,”9”]
    it gives me [“,8” in the right column and everything what comes after in the next. So not only “9”] but every value that follows without creating the new colums. Is it a problem with seperator?

    • Sorry, just needed to add [ ] as seperator.
      Again so many thanks
      Jens

  53. Dipak C. Gajjar

    Jens, I am glad that it worked for you.

  54. thanks for the info , it really helped me.
    * link removed *

Comments are closed for this article.