HTML TABLES EXCEL PHP MYSQL

Question

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!

FrustratedWithFormsDesigner · Accepted Answer · 2011-06-28 15:20:50Z

3

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.

answered Jun 28, 2011 at 15:20

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

matt colley Over a year ago

DOH! Palm slap to my forehead! Many Thanks!

FrustratedWithFormsDesigner Over a year ago

@matt colley: The technical term for that is "face-palm" ;)

ʍǝɥʇɐɯ · Accepted Answer · 2011-06-28 15:21:08Z

0

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.

answered Jun 28, 2011 at 15:21

ʍǝɥʇɐɯ

4,0128 gold badges34 silver badges53 bronze badges

Comments

Albireo · Accepted Answer · 2011-06-28 15:21:42Z

0

You're closing the table in the wrong place.

It should be outside of the while.

answered Jun 28, 2011 at 15:21

Albireo

11.1k14 gold badges66 silver badges96 bronze badges

Collectives™ on Stack Overflow

HTML TABLES EXCEL PHP MYSQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related