Jump to content


Photo

Extracting Data to Excel Spreadsheet


  • Please log in to reply
2 replies to this topic

#1 ccmd00d

ccmd00d
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 05 October 2005 - 01:06 PM

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

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 05 October 2005 - 03:32 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 ccmd00d

ccmd00d
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 05 October 2005 - 03:35 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users