PDA

View Full Version : Exporting data to excel file


predator
09-16-2009, 09:13 PM
Please re-direct if this thread exists but I have searched both forum and various other places.

I have added a simple code that allows me to download db data to an excel file, all done from admin. It saves to my computer nicely and displays all related data, but the excel file displays all fields into one column. Each field name starts with a boxed question mark? The rows with values are fine. I assume this is due to format but I'm out of ideas.

Any help would be great!

Below is the php code:

$find_db = ('SELECT `NAME`, `EMAIL` FROM `PLD_USER` WHERE `U_RANKING` = 4');
$result = mysql_query($find_db);
$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/vnd.ms-excel");
# replace excelfile.xls with whatever you want the filename to default to
$filename = $file."_".date("Y-m-d");
header("Content-Disposition: attachment; filename=Practitioner_Email_List".$filename.".csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data;
?>