Quick and Dirty Code to Dump MySQL table to Excel
I had a tough time finding a quick example of dumping a MySQL table to Excel using PEAR Spreadsheet_Excel_Writer so here is what I came up with. Very handy and I hope it helps somebody looking for the same thing.
< ?php require_once 'Spreadsheet/Excel/Writer.php'; require_once 'DB.php'; require_once 'DB/Table.php'; // create a PEAR DB object $dsn = "mysql://user:password@www.host.com/database"; $db = DB::connect($dsn); $select = "SELECT * FROM tablename"; $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) ); $fields = mysql_num_fields ( $export ); $xls =& new Spreadsheet_Excel_Writer(); // Send HTTP headers to tell the browser what's coming $xls->send(“test.xls”);
// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet(‘worksheetname’);
$format_bold =& $xls->addFormat();
$format_bold->setBold();
for ( $i = 0; $i < $fields; $i++ )
{
$sheet->write(0, $i, mysql_field_name($export,$i),$format_bold);
}
$r = 1;
$c = 0;
while( $row = mysql_fetch_row( $export ) )
{
foreach( $row as $value )
{
if ((isset($value)))
{
$sheet->write($r,$c,$value);
$c++;
}
}
$r++;
$c = 0;
}
// Finish the spreadsheet, dumping it to the browser
$xls->close();
?>