Jump to content

Archived

This topic is now archived and is closed to further replies.

ccmd00d

Extracting Data to Excel Spreadsheet

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.