Download table from mysql in .xls without Popup in Php | Codeexecute

Download table from mysql in .xls without Popup in Php

For download table from myslq into .xls form without asking have two basic steps:

  1. create connection to the database
  2. create index file to get the data from database, execute code and get desired output ( .xls file )

1.  connection to Database in connection.php

<?php $server="localhost"; 
$dbusername="root"; $dbPassword=""; 
$dbname="Database Name"; 
$connection_error= "your database is not connected";
 if(!mysql_connect($server,$dbusername,$dbPassword) || !mysql_select_db($dbname)) { die($connection_error); }
 ?>

 

2. Then in index.php add this code

<?php // connection connection file to index.php 
include ("connection.php");

 $setCounter = 0; $setData=""; 
$setExcelName = "download_excal_file";

// sql query which brings your table data
 $setSql = "SELECT * FROM `tablename` WHERE 1";

 // executing select query
 $setRec = mysql_query($setSql);

 // counting number of rows in the result of select query 
$setCounter = mysql_num_fields($setRec); $setMainHeader="";

 // creating header for .xls file

 for ($i = 0; $i &lt; $setCounter; $i++)
 {
 $setMainHeader .= mysql_field_name($setRec, $i)."\t"; 
}
 $setMainHeader.="\n"; 
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 = "\nno matching records found\n";
 }

 $setCounter = mysql_num_fields($setRec);

 $myfile = fopen($setExcelName."_Reoprt.xls", "w") or die("Unable to open file!");

 $txt = $setMainHeader. $setData;

 fwrite($myfile, $txt);
 ?>

after adding this code in index file just load the file to the browser and check the root folder of your code you will get a xls file created with file name ending with “_Report.xls”

NOTE: You can give selected fields name if you want to get only some selected column instead of all the fields of table.