Export Data to Excel with PHP and MySQL

How to Export Data to Excel using PHP and MySQL

This tutorial demonstrates you how to export MySQL table data to Excel spreadsheet, when a user clicks a button on a webpage. You can achieve this using PHP, MySQL. For this,
create the files specified in the below, in some folder in Apache server.

 

1. php2excel-tbl.php

2. export.php 

 Now run the first file in the web server. When button clicked, export.php will be executed at backend and generates the Excel file.

php2excel-tbl.php

<?php
/*******EDIT DB Details *******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "root";    //MySQL Username     
$DB_Password = "";        //MySQL Password     
$DB_DBName = "usersdb";   //MySQL Database Name  
$DB_TBLName = "users";    //MySQL Table Name   
$filename = "excel-users-list";         //File Name

/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
 

//create MySQL connection   

$connect = 

@mysqli_connect($DB_Server, $DB_Username, $DB_Password, $DB_DBName)  

or die("Couldn't connect to MySQL:<br>" . mysqli_error() . "<br>" . mysqli_errno());


//DEFINE SQL QUERY:
//edit this to suit your needs
$sql = "Select * from $DB_TBLName";
  
//execute query 
$result = @mysqli_query($connect,$sql) or 

die("Couldn't execute query:<br>" . mysqli_error());   


$records=array(); 
 
?>


<h2>e-trainings demo on Export Data to Excel with PHP and MySQL</h2>
    <div class="well-sm col-sm-12">
        <div class="btn-group pull-right">  
        
        <form action="export.php" method="post">
                        
        <button type="submit" id="export_data" name='export_data' value="Export to excel" class="btn btn-info">Export to excel</button>
        </form>
        </div>
    </div>  
 <?php
 
/*******Start of Formatting for Excel*******/    
    $table ="";
    $table .= '<table border="1"><tr>'; 

    for ($i = 0; $i < mysqli_num_fields($result); $i++) 
     {
        $table  .= '<th>'.mysqli_fetch_field_direct($result, $i)->name . "</th>";
     }
     
    $table  .=  "<tr>";
    while ($row = mysqli_fetch_assoc($result))
    {
    array_push($records,$row);
        foreach($row as $col  =>  $val)
            {
                $table  .= "<td>".$val."</td>";
            
            }
        $table  .=  "</tr>";
    }
    $table  .= '</table>';  

     print(trim($table));
     
 
mysqli_close($connect);

?> 

 Move Top

 

Create another file export.php that will export the table contents to excel on button click

export.php

<?php
 

/*******EDIT DB Details *******/
 

$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "root";    //MySQL Username     
$DB_Password = "";        //MySQL Password     
$DB_DBName = "usersdb";   //MySQL Database Name  
$DB_TBLName = "users";    //MySQL Table Name   

/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
 

//create MySQL connection   

$connect =  

@mysqli_connect($DB_Server, $DB_Username, $DB_Password, $DB_DBName) 

or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysqli_errno());


//edit this to suit your needs
$sql = "Select * from $DB_TBLName";
  
//execute query 
$result = @mysqli_query($connect,$sql) or 

die("Couldn't execute query:<br>" . mysqli_error());   

  $records=array(); 

  while ($row = mysqli_fetch_assoc($result)){
   array_push($records,$row);
 
 }
 
    if(isset($_POST["export_data"])) {  
    $now_date = DATE('m-d-Y H:i');
    $filename = "test_".$now_date . ".xls";         
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"$filename\"");  
    $show_coloumn = false;
    if(!empty($records)) {
     
      foreach($records as $record) {
        if(!$show_coloumn) {
          // display field/column names in first row
          echo implode("\t", array_keys($record)) . "\n";
          $show_coloumn = true;
        }
        echo implode("\t", array_values($record)) . "\n";
      }
    }
    exit;  
} 

mysqli_close($connect);
 

?> 

 Move Top

Result: