Tuesday, 16 February 2016

How to Export MySQL data to Excel in PHP - PHP Tutorial

           , , , , , ,      65 comments   

This tutorial will learn you How to export Mysql data from web application to Excel file using PHP programming language. This functionality is mostly required in enterprise level web application. There are lots of data are transfer on daily basis and manage that into separate excel file. So, at that time this type of functionality is required in web application. This functionality reduce lots of time to take data into excel file.


In this simple post we have learn something regarding how to export data to Excel in PHP. If you have developed any project then that project you have to required this functionality like Exporting Data to Excel Sheet. So we have developed this tutorial, in which we have make simple PHP Script for Export Data from Web to Excel.



Online Demo


Export MySQL data to Excel in PHP


Name Address City Postal Code Country
Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Avda. de la Construction 2222 Mexico D.F. 5021 Mexico
Antonio Moreno Mataderos 2312 Mexico D.F. 5023 Mexico
Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil


Source Code



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$sql = "SELECT * FROM tbl_customer";  
$result = mysqli_query($connect, $sql);
?>
<html>  
 <head>  
  <title>Export MySQL data to Excel in PHP</title>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
 </head>  
 <body>  
  <div class="container">  
   <br />  
   <br />  
   <br />  
   <div class="table-responsive">  
    <h2 align="center">Export MySQL data to Excel in PHP</h2><br /> 
    <table class="table table-bordered">
     <tr>  
                         <th>Name</th>  
                         <th>Address</th>  
                         <th>City</th>  
       <th>Postal Code</th>
       <th>Country</th>
                    </tr>
     <?php
     while($row = mysqli_fetch_array($result))  
     {  
        echo '  
       <tr>  
         <td>'.$row["CustomerName"].'</td>  
         <td>'.$row["Address"].'</td>  
         <td>'.$row["City"].'</td>  
         <td>'.$row["PostalCode"].'</td>  
         <td>'.$row["Country"].'</td>
       </tr>  
        ';  
     }
     ?>
    </table>
    <br />
    <form method="post" action="export.php">
     <input type="submit" name="export" class="btn btn-success" value="Export" />
    </form>
   </div>  
  </div>  
 </body>  
</html>

export.php



<?php  
//export.php  
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
if(isset($_POST["export"]))
{
 $query = "SELECT * FROM tbl_customer";
 $result = mysqli_query($connect, $query);
 if(mysqli_num_rows($result) > 0)
 {
  $output .= '
   <table class="table" bordered="1">  
                    <tr>  
                         <th>Name</th>  
                         <th>Address</th>  
                         <th>City</th>  
       <th>Postal Code</th>
       <th>Country</th>
                    </tr>
  ';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '
    <tr>  
                         <td>'.$row["CustomerName"].'</td>  
                         <td>'.$row["Address"].'</td>  
                         <td>'.$row["City"].'</td>  
       <td>'.$row["PostalCode"].'</td>  
       <td>'.$row["Country"].'</td>
                    </tr>
   ';
  }
  $output .= '</table>';
  header('Content-Type: application/xls');
  header('Content-Disposition: attachment; filename=download.xls');
  echo $output;
 }
}
?>

65 comments:

  1. Unknown23 February 2016 at 20:46

    Hi!

    i'd like to THANK YOU, i've been looking for an answer like this a long time ago!!

    i have a problem, hope you can help me...
    i use the whole code, everything you posted, but when i am going to open it says that the format doesnt match the filetype... hope you can help me.
    regards!

    ReplyDelete
    Replies
    1. volkansam8 March 2017 at 01:04

      me too...

      Delete
    2. volkansam8 March 2017 at 01:04

      Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\alp_deneme\kontrol\excelindir.php:1) in C:\xampp\htdocs\alp_deneme\kontrol\excelindir.php on line 34

      Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\alp_deneme\kontrol\excelindir.php:1) in C:\xampp\htdocs\alp_deneme\kontrol\excelindir.php on line 35

      Delete
  2. Unknown22 March 2016 at 01:55

    Superb !!!!!!

    ReplyDelete
  3. IndiBlogHub6 April 2016 at 06:29

    Nice one. You can check more tutorials here
    http://skillinfinity.com/blog/topics/php/library

    ReplyDelete
  4. MikkMac30 April 2016 at 07:57

    Thanks - just what i needed!

    ReplyDelete
  5. Unknown26 May 2016 at 00:29

    Hi Thanks for the tutorial can you please tell me how we can format the table like changing color of the table

    ReplyDelete
    Replies
    1. Thikshanya28 May 2021 at 00:11

      give color inside the table...its like inline style in css ex: <table style=" background-color: white>;

      Delete
  6. Unknown10 July 2016 at 06:44

    how can we change the extension of the file.

    ReplyDelete
  7. Unknown20 July 2016 at 23:12

    Hey Thanks for sharing. When I export to excel.php I just get a blank page. It doesn't download, copied code just as you haveit on the page.

    ReplyDelete
    Replies
    1. nico_aso28 December 2017 at 16:37

      if your code goes like this ex. -> $row['example'] use this instead $row["example"].

      Delete
  8. Unknown16 August 2016 at 03:22

    i have used your code but the data is not getting displayed.but in download file data is there

    ReplyDelete
  9. Unknown17 August 2016 at 09:49

    excellent Code.... Thanks

    ReplyDelete
  10. ocusfend19 August 2016 at 17:10

    I found it easy to understand and useful, thanks

    ReplyDelete
  11. Unknown23 August 2016 at 03:02

    This comment has been removed by the author.

    ReplyDelete
  12. Unknown9 November 2016 at 22:09

    Great...Thankyou Very much

    ReplyDelete
  13. lossangle5 December 2016 at 20:15

    thanks great. i want to know how to export in pdf

    ReplyDelete
  14. Unknown28 April 2017 at 00:09

    <3

    ReplyDelete
  15. Unknown20 July 2017 at 01:52

    thanks great code, one thing I am facing, if the address is in 2 or more line, can the exported data will come in single cell?

    ReplyDelete
  16. Unknown18 August 2017 at 02:13

    Hi,
    When i tried your code i get this Error : Allowed memory size of 134217728 bytes exhausted (tried to allocate 133693506 bytes).
    Can someone help please ?

    ReplyDelete
  17. Leo7 November 2017 at 12:55

    Nice Tutorial, Thank you

    I also want know how to export in pdf

    ReplyDelete
  18. Christian Oyardo13 January 2018 at 10:43

    it works! but no border in excel :(

    ReplyDelete
  19. Unknown18 January 2018 at 11:09

    Not working!!!

    ReplyDelete
  20. Unknown14 March 2018 at 13:34

    Thanks Sir, it helped me a lot, could you please tell me how to import excel data to database table

    ReplyDelete
  21. surendra khadka21 April 2018 at 19:57

    I have a problem,downloaded excel sheet has white background and no grid.could anyone suggest me how to ressolve this issue?

    ReplyDelete
  22. surendra khadka21 April 2018 at 20:00

    Could anyone suggest?
    When i dowload excel then it shows white backgroud and no grid lines in excel.how can i remove those issue.

    ReplyDelete
  23. Esencewb IT Training & Services27 April 2018 at 19:27

    Hello how can i download excel file using ajax in code-igniter

    ReplyDelete
  24. uttam Patel1 June 2018 at 03:17

    cool its working

    ReplyDelete
  25. Unknown1 June 2018 at 05:30

    Thats great perfectly working for me thanks lot

    ReplyDelete
  26. Unknown3 July 2018 at 02:37

    Good Site,,Thanks guysm,you are doing a great job

    ReplyDelete
  27. Unknown7 July 2018 at 22:25

    When ever I am trying to open csv file I got a warning: "The file you are trying to open download.csv, is in different format than specified bt the file extension. verify that the file is not corrupted and is from a trusted source before opening the file." Please help me in this regard.

    ReplyDelete
  28. Unknown1 August 2018 at 23:11

    its very easy and useful

    great

    ReplyDelete
  29. Vivzon.in7 August 2018 at 01:44

    thanks very helpfull

    ReplyDelete
  30. Anonymous28 August 2018 at 23:57

    The file type and its extension has a mismatch, pls tell us what is the file type which is downloaded

    ReplyDelete
  31. Unknown15 September 2018 at 02:42

    Hi, script is not working for me, I get "header already send error.
    What can I do?

    This is the error:

    Warning: Cannot modify header information - headers already sent by (output started at /customers/c/4/e/dansclubvarya.be/httpd.www/Ledenadministratie/export.php:1) in /customers/c/4/e/dansclubvarya.be/httpd.www/Ledenadministratie/export.php on line 61 Warning: Cannot modify header information - headers already sent by (output started at /customers/c/4/e/dansclubvarya.be/httpd.www/Ledenadministratie/export.php:1) in /customers/c/4/e/dansclubvarya.be/httpd.www/Ledenadministratie/export.php on line 62

    ReplyDelete
  32. Unknown13 October 2018 at 10:31

    Thanks

    ReplyDelete
  33. 24অনলাইন আয়27 October 2018 at 11:29

    thanks sir its perfectly working for me!!!! thanks

    ReplyDelete
  34. Unknown5 November 2018 at 23:05

    Hello Good day, the code works perfectly thanks. I would just like to ask something; what if we try and add a search function and data reflected by the search would be the ones printed into the excel file. how do we do it?

    ReplyDelete
  35. Nelson D. Costa3 December 2018 at 07:14

    How can i make this auto? Using cronjob? any ideia?

    ReplyDelete
  36. Unknown31 December 2018 at 02:41

    Worked very well for me, thank you ;)

    ReplyDelete
  37. Unknown26 February 2019 at 04:38

    it's very useful, it works immediately, thanks so much

    ReplyDelete
  38. Unknown3 March 2019 at 17:03

    This is quite wonderful. You saved a soul. As a developer, I just needed to be very sure of this:

    header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename=download.xls');
    echo $output;




    and its workability. Wow, It worked. Bravo!

    ReplyDelete
  39. Unknown3 March 2019 at 17:07

    Also I wish to ask, is there a simpler way to do pdf exports of datatable contents?

    ReplyDelete
  40. Unknown26 March 2019 at 11:57

    how to export search data result in excel ?

    ReplyDelete
  41. Unknown26 March 2019 at 11:58

    how to export search data result in excel ?

    ReplyDelete
  42. Unknown2 April 2019 at 23:15

    Heeeeey very nice code but when it download the file into it show this message

    Notice: Undefined index: telefono in public_html/qr/admin/export.php on line 26

    Notice: Undefined index: telefono in public_html/qr/admin/export.php on line 26

    Notice: Undefined index: telefono in public_html/qr/admin/export.php on line 26

    Into the excel file... SO If can read it could be very helpful

    ReplyDelete
  43. Unknown13 April 2019 at 14:33

    SUPER AWESOME , Thank you

    ReplyDelete
  44. Unknown12 September 2019 at 09:31

    i have a problem, hope you can help me...
    i use the whole code, everything you posted, but when i am going to open it says that the format doesnt match the filetype... hope you can help me.

    ReplyDelete
  45. PK Ray23 October 2019 at 05:59

    Thanks :) It working fine. BTW, is there any opportunity to download the file as .xlsx format.

    Thanks

    ReplyDelete
  46. Unknown15 December 2019 at 22:50

    Thanks, more blessing !!!

    ReplyDelete
  47. kartik21 December 2019 at 00:02

    thanks it really good

    ReplyDelete
  48. Unknown31 December 2019 at 23:36

    it not working my code has below only display table not CREATE EXCEL FILE


    if(isset($_POST["export"]))
    {

    if($results1 > 0)
    {
    $output .= '

    foreach ($results1 as $key => $value) {
    $output .= '
    $value->rclient_name.$value->email.$value->health_condition.$value->changes.$value->image';
    }
    $output .= '';
    header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename=download.xls');
    echo $output;
    }
    }

    ReplyDelete
  49. Unknown17 February 2020 at 04:53

    thank you so much

    ReplyDelete
  50. Unknown27 February 2020 at 00:53

    thanks a lot.

    ReplyDelete
  51. Unknown30 August 2020 at 19:33

    hi thanks a lot it works so good on localhost but when I run it on a real server , it didn't work

    ReplyDelete
  52. Yasas7 September 2020 at 23:49

    Great, Thank you so much. it works 100%

    ReplyDelete
  53. Aneesh Joshi17 September 2020 at 06:51

    Can I export 2 sql tables data in a single excel file ?

    ReplyDelete
  54. yacin2 October 2020 at 06:41

    thank you

    ReplyDelete
  55. Unknown17 November 2020 at 05:41

    Its great, it is working

    ReplyDelete
  56. karayeemiratecouncil6 January 2021 at 06:06

    Nice one its working perfectly

    ReplyDelete
  57. Unknown10 January 2021 at 01:32

    Thanks for this wonderful piece of code which is easy to understand. I am able to download the data but the excel sheet has semicolons in first 100 rows followed by the table and the grid view is missing

    ReplyDelete
  58. Unknown19 March 2021 at 00:42

    thank's simple way....export excel

    ReplyDelete
  59. Vinayaga27 August 2021 at 00:02

    It is working in XAMPP but not working in live deploy. Any changes to be made. Give me some idea.

    Thanks in advance

    ReplyDelete
  60. mantol9 January 2023 at 21:53

    Hi, exported excel file and open file with message "the file format and extension of 'download.xls ' don't match.

    ReplyDelete
  61. mantol9 January 2023 at 21:54

    why can't open as *.xlsx?

    ReplyDelete