Export MySQL Data to Excel in PHP

Export MySQL data to Excel in PHP

Just Try With The Following :

PHP Part :

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I think this may help you to resolve your problem.

PHP Export mysql data to excel sheet but display in browser

I didn't found the solution so i use java script and its resolve but anybody have solution for this matter please let me know.

function fnExcelReport()
 {
var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
var textRange; var j=0;
tab = document.getElementById('data'); // id of table

for(j = 0 ; j < tab.rows.length ; j++) 
{     
    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
    //tab_text=tab_text+"</tr>";
}

tab_text=tab_text+"</table>";
tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE "); 

if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
{
    txtArea1.document.open("txt/html","replace");
    txtArea1.document.write(tab_text);
    txtArea1.document.close();
    txtArea1.focus(); 
    sa=txtArea1.document.execCommand("SaveAs",true,"Export.xls");
}  
else                 //other browser not tested on IE 11
    sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

return (sa);
}

How do I export MySQL table to Excel with PHP?

Manual

Run tour localhost and log in to phpMyAdmin
Click on your database then click on the table which you want to get
Excel.

then

then press GO button

With Code

define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","DATABASE_NAME");

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

then

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "YOUR SQL QUERY GOES HERE";

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "no matching records found";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Report.xls");
header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";

More About Code

Exporting data from MySQL to Excel with PHP

This code working for me.Try this code and make some changes as you needed.

**excl_1.php**

    <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

**export.php**





     <?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;
         }
        }
        ?>

Test Result
Sample Image

How to export data from MySQL to Excel(.xlsx) but handling the data before exporting

If you want to show specific fields only.

select id,name, ( CASE WHEN table_column = 0 THEN 'day' WHEN table_column = 1 THEN 'night' END ) as table_column from your_table

Output Look Like:

id name table_column

1 Lucas day

Export MySQL Data to Excel in PHP