Extracting Data to Excel Spreadsheet

[ccmd00d]

Hi Everyone,

 

I am working on a senior design project, which happens to be an online course evaluation system. One of my requirements to download evaluation data into excel format. Could anyone point me in the right direction on how to do this. I am having no problems retrieving and displaying information, i now just need to convert it.

 

Thanks,

 

Derek

[ryanlwh]

you can convert to csv format, which could be opened in excel.

 

csv denotes comma separated values. You just implode all the columns with a comma, and use double quotes to enclose strings and escape double quotes. the newline character "\n" denotes end of a row.

 

"hi,",1234,"testing"\n
"hi again",4567,"test""ing"\n

result (in excel)

|hi,     |1234|testing |
|hi again|4567|test"ing|

 

use the header function to make this script downloadable as .csv and echo the rows out.

[ccmd00d]

hi,

 

thanks for the reply...i was able to download into excel using a tutorial on this site..my question is...

 

How can you format the results of the data in excel?

 

here is my code.

 

<?php
$select = "SELECT * FROM evaluations WHERE eval_ID='8'";                 
$export = mysql_query($select); 
$fields = mysql_num_fields($export);

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

while($row = mysql_fetch_row($export)) { 
   	$line = ''; 
   	foreach($row as $value) {                                             
       	if ((!isset($value)) OR ($value == "")) { 
           	$value = "\t"; 
       	} else { 
           	$value = str_replace('"', '""', $value); 
           	$value = '"' . $value . '"' . "\t"; 
       	} 
       	$line .= $value; 
   	} 
   	$data .= trim($line)."\n"; 
} 
$data = str_replace("\r","",$data);

//Download into excel format
header("Content-type: application/x-msdownload");  
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache"); 
header("Expires: 0");
print "$header/n$data";  
?>

 

 

Thanks Again,

 

Derek