For those PHPers out there who are doing an export to Excel, I thought I'd share the solution I came up with. I realize there are already a gabillion examples out there, but I merged some of the better approaches from a few of them that made it fairly elegant, I think (such as leveraging the implode, array_keys, and array_values functions).
Without further adieux...
<?php $filename = "myExcelFile.xls"; //prepare to give the user a Save/Open dialog... //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 //output the contents <?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);
header ("Content-type: application/octet-stream");
header ("Content-Disposition: attachment; filename=".$filename);
$expiredate = time() + 30;
$expireheader = "Expires: ".gmdate("D, d M Y G:i:s",$expiredate)." GMT";
header ($expireheader);
echo $contents;
exit;
?>
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;
}
?>
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Tom
sorry for bad typing
However, on Excel 2007, it generated a warning message: "The file you are trying to open, is in a different format than specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
I did open the file and needed to save it in version 2007 xlsx format.
Ben
Access denied for user 'loanmod1'@'localhost' (using password: NO)
My best guess based on what you shared is that your query is failing due to a permissions issue. I would look at the database link you're creating and make sure the credentials are correct. Make sense?
Doug
and if possible then plz inform me about export to pdf
Great script! Thanks for posting. I am curious why the exports all begin on row 11 in Excel for me as opposed to row 1. Any ideas? Thanks again...this is great.
Tom
---
Any blank lines outside of your tags will add a line to excel.
For Example the below would start with "title" on row 4 of excel due to the 3 blank lines above the word title:
some code here
?>
Title
some more code
?>
