PHP Export mysql data to excel sheet but display in browser

Question

I am trying to import data from PHP mysql to excel and its working well. but once I click to import button then its display in browser.

header("Content-Disposition: attachment; filename=faculty-export.xls");
header("Content-type: application/vnd-ms-excel; charset=UTF-8;");

I have tested local in my PC (Windows 7, IIS, PHP 5.4) its working fine but when i have upload on web server ( PHP 5.3, Linux, Apache 2.2.3) than export data display in browser (Tested Browser IE 10, 11, Firefox & Chrome).

I am facing problem because we have to change some setting in server or have I do to something in my coding?

Posting the data

 <form method="post" action="export_excel.php">
<input type="hidden" name="excel_name" value="<?php echo $_GET['name']; ?>">
<input type="hidden" name="excel_gender" value="<?php echo $_GET['gender']; ?>">
<input type="hidden" name="excel_college" value="<?php echo $ColName; ?>">
<input type="hidden" name="excel_department" value="<?php echo $_GET['deptf']; ?>">
<input type="hidden" name="excel_nationality" value="<?php echo $_GET['natf']; ?>">
<input type="hidden" name="excel_finalstatus" value="<?php echo $_GET['fstatusf']; ?>">
<input type="hidden" name="excel_fyear" value="<?php echo $_GET['fyearf']; ?>">
  <input style="float:left;" type="submit" value="Save to Excel">
</form>

below code for export_excel.php.

<?php
  error_reporting(0);

  include("connectdb.php"); 
  if (empty($_POST['excel_name']) && empty($_POST['excel_gender']) && empty($_POST['excel_college']) && empty($_POST['excel_department']) && empty($_POST['excel_nationality']) && empty($_POST['excel_finalstatus']) && empty($_POST['excel_fyear']))
{
    /*$query="select * from ab";*/
    $query="select * from ab";

}
 else
    {
         $wheres = array();

          $query = "select * from ab where ";

   if (isset($_POST['excel_name']) and !empty($_POST['excel_name']))
{
    $wheres[] = "name like '%{$_POST['excel_name']}%' ";
} 

if (isset($_POST['excel_gender']) and !empty($_POST['excel_gender']))
{
    $wheres[] = "gender = '{$_POST['excel_gender']}'";
} 

if (isset($_POST['excel_college']) and !empty($_POST['excel_college']))
{
    $wheres[] = "college = '{$_POST['excel_college']}' ";
} 

if (isset($_POST['excel_department']) and !empty($_POST['excel_department']))
{
    $wheres[] = "department = '{$_POST['excel_department']}' ";
} 

if (isset($_POST['excel_nationality']) and !empty($_POST['excel_nationality']))
{
    $wheres[] = "nationality = '{$_POST['excel_nationality']}' ";
} 

if (isset($_POST['excel_finalstatus']) and !empty($_POST['excel_finalstatus']))
{
    $wheres[] = "finalstatus = '{$_POST['excel_finalstatus']}' ";
}

if (isset($_POST['excel_fyear']) and !empty($_POST['excel_fyear']))
{
    $wheres[] = "fyear = '{$_POST['excel_fyear']}' ";
} 

foreach ( $wheres as $where ) 
{
  $query .= $where . ' AND ';   //  you may want to make this an OR
 }
   $query=rtrim($query, "AND "); 

  }

   $result = mysqli_query($conn,$query);

    $xls_filename = 'export_'.date('d-m-Y').'.xls'; // Define Excel (.xls) file name


 header("Pragma: public");
 header("Expires: 0");
 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 header("Content-Type: application/force-download");
 header("Content-Type: application/octet-stream");
 header("Content-Type: application/download");;
 header("Content-Disposition: attachment; filename=$xls_filename");
 header("Content-Transfer-Encoding: binary "); 



echo "<table border='1'><tr><th>الإسم</th><th>تاريخ وصول الملف</th><th>الكلية</th><th>القسم</th><th>العمر</th><th>الجنسية</th><th>الجنس</th><th>الدرجة</th><th>نوع التعيين</th><th>السنة</th><th>البكالوريوس<br>سنة التخرج</th><th>الماجستير<br>سنة التخرج</th><th>الدكتوراه<br>سنة التخرج</th><th>الملاحظات</th><th>تاريخ اللجنة</th>";





while($res = mysqli_fetch_assoc($result)){ 
echo "<tr><td>".$res['name']."</td><td>".$res['DOA']."</td><td>".$res['college']."</td><td>".$res['department']."</td><td>".$res['DOB']."</td><td>".$res['nationality']."</td><td>".$res['gender']."</td><td>".$res['fwtype']."</td><td>".$res['finalstatus']."</td><td>".$res['fyear']."</td>";

$query2 = "SELECT * FROM abc where ".$res['id']." = DId";
$result1 = mysqli_query($conn,$query2);

if (mysqli_num_rows($result1) > 0) {
    while($row = mysqli_fetch_assoc($result1)) {
    echo "<td>".$row['DUName']."<br>".$row['DYEAR']."</td>";    
    }
    }
    }
  echo "</tr></tr></table>";


    exit();

 ?>

Possible duplication: http://stackoverflow.com/questions/1465573/forcing-to-download-a-file-using-php — Pavel Petrov, May 11 '16 at 11:10
Try enabling mod_mime if you haven't and then insert the AddType directive for application/vnd-ms-excel in your apache config where is appropriate. — Pavel Petrov, May 12 '16 at 06:39

Sanooj T · Answer 1 · 2016-05-12T12:23:49.727

Hi I changed few things in here

<?php
$xls_filename = 'export_'.date('d-m-Y').'.xls'; // Define Excel (.xls) file name
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$xls_filename");
header("Content-Transfer-Encoding: binary ");

include("connectdb.php");
$query="select * from ab WHERE 1=1 ";
if (isset($_POST['excel_name']) and !empty($_POST['excel_name']))
 {
  $query.= " AND name like '%{$_POST['excel_name']}%' ";
 }
if (isset($_POST['excel_gender']) and !empty($_POST['excel_gender']))
 {
  $query.= " AND gender = '{$_POST['excel_gender']}'";
 }
if (isset($_POST['excel_college']) and !empty($_POST['excel_college']))
 {
 $query.= " AND college = '{$_POST['excel_college']}' ";
 }
if (isset($_POST['excel_department']) and !empty($_POST['excel_department']))
 {
 $query.= " AND department = '{$_POST['excel_department']}' ";
 }

 if (isset($_POST['excel_nationality']) and !empty($_POST['excel_nationality']))
 {
 $query.= " AND nationality = '{$_POST['excel_nationality']}' ";
 }

if (isset($_POST['excel_finalstatus']) and !empty($_POST['excel_finalstatus']))
 {
  $query.= " AND finalstatus = '{$_POST['excel_finalstatus']}' ";
 }

if (isset($_POST['excel_fyear']) and !empty($_POST['excel_fyear']))
 {
  $query.= " AND fyear = '{$_POST['excel_fyear']}' ";
 }
 $result = mysqli_query($conn,$query);

 echo "<table border='1'>
 <tr>
 <th>الإسم</th><th>تاريخ وصول الملف</th><th>الكلية</th><th>القسم</th>   <th>العمر</th>
 <th>الجنسية</th><th>الجنس</th><th>الدرجة</th><th>نوع التعيين</th><th>السنة</th>
 <th>البكالوريوس<br>سنة التخرج</th><th>الماجستير<br>سنة التخرج</th>
 <th>الدكتوراه<br>سنة التخرج</th><th>الملاحظات</th><th>تاريخ اللجنة</th>
 </tr>";
 while($res = mysqli_fetch_assoc($result)){
  echo "<tr><td>".$res['name']."</td><td>".$res['DOA']."</td><td>".$res['college']."</td><td>".$res['department']."</td><td>".$res['DOB']."</td><td>".$res['nationality']."</td><td>".$res['gender']."</td><td>".$res['fwtype']."</td><td>".$res['finalstatus']."</td><td>".$res['fyear']."</td>";

 $query2 = "SELECT * FROM abc where ".$res['id']." = DId";
 $result1 = mysqli_query($conn,$query2);

if (mysqli_num_rows($result1) > 0) {
    while($row = mysqli_fetch_assoc($result1)) {
        echo "<td>".$row['DUName']."<br>".$row['DYEAR']."</td>";
    }
}
echo "</tr>";
}
echo "</table>";
exit();
?>

Please use this code in your export_excel.php and if error occurs please tell me

I get the report but without any data. I will get back you soon and let you know the status. — Salman Karim, May 12 '16 at 12:38
Sorry for lately reply: can i use this jquery plugin for my scenario? https://www.filamentgroup.com/lab/jquery-plugin-for-requesting-ajax-like-file-downloads.html — Salman Karim, May 15 '16 at 10:55

score 0 · Accepted Answer · answered May 17 '16 at 20:38

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

PHP Export mysql data to excel sheet but display in browser

2 Answers2