Export MySQL to Excel OR Creating Excel with PHP

[therealwesfoster]

I have 2 questions.

 

1. How would I go about exporting my MySQL data into a Microsoft Excel file so the user can download it? For instance, the user goes to /download_db.php. I want the file to run a MySQL query which saves all the information to an excel file and allows the user to download it.

 

2. How would I use PHP to read and write to an excel file? So every time someone enters their email address in a form, the PHP should open /file.xls, read it, append the new email address to the file, and save it. That way the user could just go to /file.xls and read/download the information.

 

Please help on both questions if you can. I'm only going to be doing 1 of the 2, whichever is quicker.

 

Thanks,

  Wes

[benphp]

<?php
	$table = $_POST['table'];
	$selectmain = "SELECT * FROM $table ";
	$result = mysql_query($selectmain) or trigger_error("SQL", E_USER_ERROR);
	$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{
				$value = str_replace('"', '', $value); 
				$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; name='excel'");

	header("Content-type: application/octet-stream");
	header("Content-Disposition: attachment; filename=excelfile.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	echo $header."\n".$data;

?>

[soycharliente]

That script puts a random quote in front of everything for every field on every row. Why?

 

Does it do that for anyone else?

[benphp]

This fixes the extra '

 

<?php
$table = "mytablename";

$data = "";
$header = "";
$selectmain = "SELECT * FROM $table ";
$result = mysql_query($selectmain) or trigger_error("SQL", E_USER_ERROR);
$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{
		$value = str_replace('"', '', $value); 
		$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; name='excel'");

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data;
?>

[soycharliente]

Was it supposed to be there? I was just curious. I picked through the code and changed it. I didn't know if it was a formatting issue that I wasn't aware of. I wasn't trying to nitpick your code. Sorry if it came across like that.

[benphp]

I copied the code from a project I did, and there was a reason why it was there, but I forget why. I remember that I was importing/exporting data to/from excel and was diddling with quotation errors - probably had something to do with that.

[rhodesa]

A single quote in front of the field forces Excel to treat it as text (and not try to convert it to a number/date/etc)...

[benphp]

That's it! I didn't want the dates to be converted to excel's shortened version

[therealwesfoster]

Awesome, thanks everyone! Especially you ben. I'll put that to good use

 

Wes

[Chupa85]

OK So I am trying to use this code but I have a problem.  The code is just spitting out the whole file in the web page and isn't generating an excel file.  Any ideas?