I'm trying to print out mysql table data into excel. I found code on
web but it outputs everything into one cell instead of spacing out on
spreadsheet. I have no clue how to fix.
<?PHP
$result = mysql_query("SE LECT * FROM $db_table3");
$count = mysql_num_field s($result);
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_nam e($result, $i)."\t";
}
while($row = mysql_fetch_row ($result)){
$line = '';
foreach($row as $value){
if(!isset($valu e) || $value == ""){
$value = "\t";
}
else
{
# important to escape any quotes to preserve them in the data.
# 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 excelfile.xls with whatever you want the filename to default
to
header("Content-Disposition: attachment; filename=excelf ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
echo $header."\n".$d ata;
?>
web but it outputs everything into one cell instead of spacing out on
spreadsheet. I have no clue how to fix.
<?PHP
$result = mysql_query("SE LECT * FROM $db_table3");
$count = mysql_num_field s($result);
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_nam e($result, $i)."\t";
}
while($row = mysql_fetch_row ($result)){
$line = '';
foreach($row as $value){
if(!isset($valu e) || $value == ""){
$value = "\t";
}
else
{
# important to escape any quotes to preserve them in the data.
# 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 excelfile.xls with whatever you want the filename to default
to
header("Content-Disposition: attachment; filename=excelf ile.xls");
header("Pragma: no-cache");
header("Expires : 0");
echo $header."\n".$d ata;
?>
Comment