PHP export to Microsoft Excel

[DKY]

I have a page in which I would like to export data from a MySQL database into an Excel spreadsheet when a link is pressed. Is that possible?

[crazychris]

export it as a csv file, that will open in excel quite nicely.

[crazychris]

doh! double post

[khendar]

Something like this should work:

$linkID = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $linkID);

$table="table_name";

// Build rows of data
$query = "SELECT * FROM `$table`";
$result = mysql_query($query, $linkID);

while($row = mysql_fetch_array($result))
{
	for($i=0;$i<mysql_num_fields($result);$i++)
 	{
   		$string .= $row[$i];
   		if($i!=mysql_num_fields($result)-1)$string.=','; // Add a comma after all fields except for the last one
  	}
 	$string.="\n"; // Add a new line character at the end of the line

}

// Write $string to file
$filename="database_export.csv";
$fh = fopen($filename, "w+");
if(fwrite($fh, $string)) echo "Write successful";
else echo "Write failed";
fclose($fh);

[hagen00]

If you really want to use excel and not have a csv file (and assuming you're on linux) then you could look at using OpenOffice.org. But that might be overkill! This will require quite a bit of learning. i.e. how to write OOo macros, how to connect OOo to a MySQL database etc etc.

I'm using OOo to generate quite complex excel reports (and PDF's for that matter), but like i said, all the learning you will have to do might not make it worth it. If you're on windows, it should be easy. Just use COM then...

[DKY]

khendar wrote:

Something like this should work:

$linkID = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $linkID);

$table="table_name";

// Build rows of data
$query = "SELECT * FROM `$table`";
$result = mysql_query($query, $linkID);

while($row = mysql_fetch_array($result))
{
	for($i=0;$i<mysql_num_fields($result);$i++)
 	{
   		$string .= $row[$i];
   		if($i!=mysql_num_fields($result)-1)$string.=','; // Add a comma after all fields except for the last one
  	}
 	$string.="\n"; // Add a new line character at the end of the line

}

// Write $string to file
$filename="database_export.csv";
$fh = fopen($filename, "w+");
if(fwrite($fh, $string)) echo "Write successful";
else echo "Write failed";
fclose($fh);

So, then I would just link to this php page?