Exporting an array to Excel

Question

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.

where is $data being defined? Is it possible that $data has gone out of scope? — superultranova
– superultranova, Commented Sep 28, 2011 at 19:42
I've echoed data after that foreach loop to make sure it contains the data that i want it to contain. Otherwise, i clear $data before it enters the loops and do nothing to it afterwards. — IceBlueFire
– IceBlueFire, Commented Sep 28, 2011 at 19:47
So you can echo the data? Doesn't that solve your problem? Or is it only a problem when the headers are set? — superultranova
– superultranova, Commented Sep 28, 2011 at 19:48
It's not displaying in Excel. I can echo it to the webpage, but not into the excel document. Only the $headers show up in the document. — IceBlueFire
– IceBlueFire, Commented Sep 28, 2011 at 19:51

Marc B · Accepted Answer · 2011-09-28 19:38:52Z

2

Why not just fputcsv() 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.

answered Sep 28, 2011 at 19:38

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rene Pot · Accepted Answer · 2011-09-28 19:40:45Z

1

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

answered Sep 28, 2011 at 19:40

Rene Pot

24.8k7 gold badges70 silver badges92 bronze badges

4 Comments

IceBlueFire Over a year ago

Yeah, i've traced to make sure that the $data variable actually holds what i want it to hold. It just doesn't get put into excel, which is the issue i'm having.

hakre Over a year ago

"Print causes loads of overhead" - LOL.

IceBlueFire Over a year ago

Also, changing to echo and separating out the echo like you have, still doesn't work.

Rene Pot Over a year ago

alright, I was wrong about overhead. But it is a little bit slower :)

RPDeshaies · Accepted Answer · 2014-03-14 20:48:05Z

I tested your code and it seems that your trim() 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;

Collectives™ on Stack Overflow

Exporting an array to Excel

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related