CODEWITHSUNDEEP
phpmysqlexport-to-excel
matt colley
matt colley

Reputation: 173

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!

Upvotes: 0

Views: 1584

Answers (3)

Albireo
Albireo

Reputation: 11095

You're closing the table in the wrong place.

It should be outside of the while.

Upvotes: 0

ʍǝɥʇɐɯ
ʍǝɥʇɐɯ

Reputation: 4022

Export it with CSV and then import it on your localhost, now re-export it and have pretty export functions working. Maybe you need to install a LAMP stack on your PC, handy to have anyway.

Upvotes: 0

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27536

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.

Upvotes: 3

Related Questions