php - Exporting an array to Excel
Get the solution ↓↓↓I'm trying to export an array of arrays to excel. I have it set up to be a header variable, and a data variable that basically builds a giant string to be executed in the export. However, only the header variable is going through. Let me show some code:
This is setting the parameters:
str_replace(" ", "_", $getData['name']);
$filename = $getData['name']."_summary.xls";
header("Content-Type: application/x-msdownload");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");
Which goes to a function to get the information:
foreach($tempRS as $key=>$value)
{
foreach($value as $iKey=>$iValue)
{
if($count == 6)
{
$iValue = str_replace('"', '""', $iValue);
$iValue = '"'.$iValue.'"'."\n";
$data .= trim($iValue);
$count = 0;
}
else
{
$iValue = str_replace('"', '""', $iValue);
$iValue = '"'.$iValue.'"'."\t";
$data .= trim($iValue);
$count++;
}
}
}
$header = "ROW HEADER 1\tROW HEADER 2\tROW HEADER 3\tROW HEADER 4\tROW HEADER 5\tROW HEADER 6\n";
print "$header\n$data";
I can't seem to figure out why i'm losing the $data variable on the export.
Answer
Solution:
Why not justfputcsv()
to generate that CSV data for you? Or better yet, instead of making a .csv masquerade as an Excel file, you can use PHPExcel to output a native .xls/.xlsx and actually use formatting and formulae in the generated spreadsheet.
Answer
Solution:
first of all, use echo instead of print. Print causes loads of overhead as it does both return and echo the data.
Secondly, don't put the variables within quotes, use
echo $header ."\n".$data;
To get to your question, does the foreach loops actually loop? Have you checked the $data if it contains any data?
A better solution might be this:
$header = '';
echo $header;
foreach() loop here {
//echo the data here instead of putting it in a variable
}
Or maybe better, use http://nl.php.net/fputcsv
Answer
Solution:
I tested your code and it seems that yourtrim()
method is trimming your\n
and\t
.
If you remove it, your code should be fine.
Here's my code to export a array of product in excel.
Only one little problem with this code : Excel doesn't want to open it because of a format problem, but if you click "yes" when it promps an error message, you'll be ok...
No problem with open office though
Working on a fix...
$filename = "products_exports " . date("Y-m-d H_i_s") . ".xls";
/**
* Tell the browser to download an excel file.
*/
header("Content-Type: application/x-msdownload; charset=utf-8");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");
/**
* The header of the table
* @var string
*/
$header = "";
/**
* All the data of the table in a formatted string
* @var string
*/
$data = "";
//We fill in the header
foreach ($productArray as $i => $product) {
$count = 0;
foreach ($product as $key => $value) {
if($count == count((array)new Product("")) - 1){
$header.= $key;
}
else{
$header.= $key . "\t";
$count++;
}
}
break; /*One loop is enough to get the header !*/
}
//And then the data by the product and all the categories
/*Where $productArray = This can be your custom array of object. eg.
array[0] = new YouCustomObject(your params...);
*/
foreach ($productArray as $i => $product) {
$count = 0;
foreach ($product as $key => $value) {
if($count == count((array)new Product("")) - 1){
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\n";
$data .= $value;
$count = 0;
}
else{
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
$data .= $value;
$count++;
}
}
}
echo $header ."\n". $data;
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: using $this when not in object context
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.