Tuesday 16 February 2016

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


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. 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. 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. Nice one. You can check more tutorials here
    http://skillinfinity.com/blog/topics/php/library

    ReplyDelete
  3. Thanks - just what i needed!

    ReplyDelete
  4. 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. give color inside the table...its like inline style in css ex: <table style=" background-color: white>;

      Delete
  5. how can we change the extension of the file.

    ReplyDelete
  6. 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. if your code goes like this ex. -> $row['example'] use this instead $row["example"].

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

    ReplyDelete
  8. I found it easy to understand and useful, thanks

    ReplyDelete
  9. This comment has been removed by the author.

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

    ReplyDelete
  11. 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
  12. 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
  13. Nice Tutorial, Thank you

    I also want know how to export in pdf

    ReplyDelete
  14. it works! but no border in excel :(

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

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

    ReplyDelete
  17. 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
  18. Hello how can i download excel file using ajax in code-igniter

    ReplyDelete
  19. Thats great perfectly working for me thanks lot

    ReplyDelete
  20. Good Site,,Thanks guysm,you are doing a great job

    ReplyDelete
  21. 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
  22. its very easy and useful

    great

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

    ReplyDelete
  24. 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
  25. thanks sir its perfectly working for me!!!! thanks

    ReplyDelete
  26. 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
  27. How can i make this auto? Using cronjob? any ideia?

    ReplyDelete
  28. Worked very well for me, thank you ;)

    ReplyDelete
  29. it's very useful, it works immediately, thanks so much

    ReplyDelete
  30. 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
  31. Also I wish to ask, is there a simpler way to do pdf exports of datatable contents?

    ReplyDelete
  32. how to export search data result in excel ?

    ReplyDelete
  33. how to export search data result in excel ?

    ReplyDelete
  34. 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
  35. 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
  36. Thanks :) It working fine. BTW, is there any opportunity to download the file as .xlsx format.

    Thanks

    ReplyDelete
  37. 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
  38. hi thanks a lot it works so good on localhost but when I run it on a real server , it didn't work

    ReplyDelete
  39. Great, Thank you so much. it works 100%

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

    ReplyDelete
  41. 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
  42. thank's simple way....export excel

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

    Thanks in advance

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

    ReplyDelete