Download MySQL Data To Excel

[tomyknoker]

I have no issue in getting my data to download into MySQL, but can you

A) Style the info? So like make a certain font and size?

B) It isn't displaying my table column names as the heading any clues?

This is what I have



<?php
include 'library/config.php';
include 'library/opendb.php';

$query = "SELECT * FROM tbl WHERE `Member`='A'";
$result = mysql_query($query) or die('Error, query failed');

$tsv = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[] = implode("\t", $row);
$html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>";
}

$tsv = implode("\r\n", $tsv);
$html = "<table>" . implode("\r\n", $html) . "</table>";

$fileName = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");

echo $tsv;
//echo $html;

include 'library/closedb.php';
?>

[NXArmada]

A long time ago I made as a small project a script that exports MySQL Data and outputs to an excel files and I use it now in all my PHP based programs for exporting to Excel.

Heres my code for everyone to use:



<?PHP

include('include/config.php');
include('include/opendb.php');

$result = mysql_query('SELECT * FROM tbl WHERE `Member`=\'A\'');
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
//if ($data == "") {
// $data = "\nno matching records found\n";
//}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace $dbname.xls with whatever you want the filename to default to
# Default $dbname uses the Database name you are exporting from
header("Content-Disposition: attachment; filename=$dbname.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

?>