I want the output of my PHP to be opened in EXCEL or OpenOfficeSpreadsheet. How can I do it?
May 3rd, 2010 | 
Author: I have one PHP program which use mysql database. I want t the output to be opened in EXCEL/OpenOfficeSpreadsheet(in a separate window) when I click on a link provided. Any option to do it other than to save it as a CSV?The problem with CSV is that it opens as a single column and I have to use text to columns option to convert into columns. Thanks in advance.
CSV is Comma Separated Values
Means if you use values with comma as separator, Excel will open in in multiple columns
like this
"Value 1" , "Value2 " , "The long value of 3rd column"
VBAXLMan
Posted in programming in php
<?php
//your code here to create your sql statement…we’ll call it $finalSQL
//go get the data we need…
$Result=mysql_db_query($DBName,$finalSQL,$Link);
//fetching each row as an array and placing it into a holder array ($aData)
while($row = mysql_fetch_assoc($Result)){
$aData[] = $row;
}
//feed the final array to our formatting function…
$contents = getExcelData($aData);
$filename = "myExcelFile.xls";
PHP Export to Excel Snippet
//prepare to give the user a Save/Open dialog…
header ("Content-type: application/octet-stream");
header ("Content-Disposition: attachment; filename=".$filename);
//setting the cache expiration to 30 seconds ahead of current time. an IE 8 issue when opening the data directly in the browser without first saving it to a file
$expiredate = time() + 30;
$expireheader = "Expires: ".gmdate("D, d M Y G:i:s",$expiredate)." GMT";
header ($expireheader);
//output the contents
echo $contents;
exit;
?>
<?php
function getExcelData($data){
$retval = "";
if (is_array($data) && !empty($data))
{
$row = 0;
foreach(array_values($data) as $_data){
if (is_array($_data) && !empty($_data))
{
if ($row == 0)
{
// write the column headers
$retval = implode("\t",array_keys($_data));
$retval .= "\n";
}
//create a line of values for this row…
$retval .= implode("\t",array_values($_data));
$retval .= "\n";
//increment the row so we don’t create headers all over again
$row++;
}
}
}
return $retval;
}
?>
References :
http://www.dougboude.com/blog/1/2009/06/PHP-Export-to-Excel-Snippet.cfm
CSV is Comma Separated Values
Means if you use values with comma as separator, Excel will open in in multiple columns
like this
"Value 1" , "Value2 " , "The long value of 3rd column"
VBAXLMan
References :
I have had some success with exporting html, and then setting the file extension to .xls. Excel knows how to interpret html docs, and the only down side is the backgrounds end up being white instead of the usual grey. This worked great with Excel 2003. I just opened a similar doc yesterday with Excel 2007, and there was an "are you sure you want to do this?" error message, but it opened just fine as well.
Hope that helps.
References :