matt colley matt colley - 26 days ago 14
MySQL Question

HTML TABLES EXCEL PHP MYSQL

I am attempting to export mysql data into excel. I have tried the phpexcel class, and cannot use this on my host, as they do not have the zip function installed. I cannot export the data to csv, as the client 'wants to see purty colors...' so formatting is a must. So it looks like I am stuck building a table to pass to Excel. (unless anyone has any ideas on other classes!)

I am having an issue with the code, it exports just fine to excel and the formatting is correct for the first row. On all subsequent rows, all data is 'dumped' into one cell.
Here is the code:

$table .= '<table border="0" cellpadding="0" cellspacing="0"><tr>';
$table .= '<td style="background-color:#000099;color:#FFFFFF;">Date</td>';
$table .= '<td style="background-color:#000099;color:#FFFFFF;">Name</td>';
$table .= '<td style="background-color:#000099;color:#FFFFFF;">Address</td>';
$table .= '<td style="background-color:#000099;color:#FFFFFF;">City</td>';
$table .= '</tr>';
while($row=mysql_fetch_array($result)){
$table .= '<tr>';
$table .= '<td style="background-color:#FFFCCC">'.$row['date'].'</td>';
$table .= '<td style="background-color:#FFFCCC">'.$row['name'].'</td>';
$table .= '<td style="background-color:#FFFCCC">'.$row['address'].'</td>';
$table .= '<td style="background-color:#FFFCCC">'.$row['city'].'</td>';
$table .= '</tr>';
$table .= '</table>';
}
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=Itinerary-$today.xls ");
header("Content-Transfer-Encoding: binary ");
echo $table;


Appreciate any thoughts anyone can share on this subject!

Answer

Try moving this line:

$table .= '</table>';

to outside the loop.

The last few lines of the loop should looke like:

    $table .= '</tr>';
}
$table .= '</table>';

You opened a table before the loop, you should close the table after the loop.

Comments