HTML Table to Excel File Tutorial

Here is a tutorial on how to export something from your database to an excel file using PHP. It is useful when you want to download a report.

Basically, it displays an HTML table but instead of showing it in the browser, we have set the header to treat the output as an excel file.

$dbhost = ‘localhost’;
$dbuser = ‘root’;
$dbpass = ‘xxx’;
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);
$dbname = ‘employee’;
mysql_select_db($dbname);
$query = ‘SELECT
*
FROM employee_table’;
$result = mysql_query($query);
header("Pragma: public");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: pre-check=0, post-check=0, max-age=0");
header("Pragma: no-cache");
header("Expires: 0");
header("Content-Transfer-Encoding: none");
header("Content-Type: application/vnd.ms-excel;");
header("Content-type: application/x-msexcel");
header("Content-Disposition: attachment; filename=my_excel_file.xls");
echo ‘
<table border="1">’;
echo ‘
<tbody>
<tr>’;
echo ‘
<td><strong>Firt Name</strong></td>
‘;
echo ‘
<td><strong>Last Name</strong></td>
‘;
echo ‘
<td><strong>Username</strong></td>
‘;
echo ‘
<td><strong>Date Hired</strong></td>
‘;
echo ‘
<td><strong>Marital Status</strong></td>
‘;
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo ‘</tr>
<tr>’;
echo ‘
<td>’.$row[‘firstname’].‘</td>
‘;
echo ‘
<td>’.$row[‘lastname’].‘</td>
‘;
echo ‘
<td>’.$row[‘username’].‘</td>
‘;
echo ‘
<td>’.$row[‘datehired’].‘</td>
‘;
echo ‘
<td>’.$row[‘maritalstatus’].‘</td>
‘;
echo ‘</tr>
‘;
}
echo ‘</tbody></table>
‘;

eKini: Web Developer Blog