Export Mysql Data into MS Excel Using a Link\Button in PHP

Question

I am a newbie to PHP and need help in exporting a selected content from Mysql Table to MS Excel using PHP. I need this done by a click of a button or a link.

Below is a piece of code I have done so far but I am consistently getting a warning as "Cannot modify header information - headers already sent". Also suggest a good way to export table on a click of a button\link. Thanks

//Export Contents

$header = '';

$data = '';

$fields = mysql_num_fields($sql);

//fetch header

for($i=0; $i < $fields; $i++)
{

    $header .= mysql_field_name($sql, $i)."\t";
}

//fetch data each row, store on tabular row data

while($row = mysql_fetch_row($sql))
{

    $line = '';
    foreach($row as $value)
    {
        if(!isset($value) || $value == "")
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace('"', '""', $value);
            $value = '"'.$value.'"'."\t";
        }

        $line .= $value;
    }

    $data .= trim($line)."\n";
    $data = str_replace("\r", "", $data);
}

//Naming the excel sheet

$name = $customerFilter."_".date('d-m-y').".xls";

header("Content-type:application/vnd.ms-excel;name='excel'");

header("Content-Disposition: attachment; filename=$name");

header("Pragma: no-cache");

header("Expires: 0");

//Output Data

echo $header."\n\n".$data;

score 1 · Answer 1 · answered Mar 23 '14 at 08:18

1

The mysql query

SELECT <column list>
FROM <table>
INTO OUTFILE '/tmp/somefilename.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Then redirect to the file

answered Mar 23 '14 at 08:18

Chris Caviness

586
3
10

score 0 · Answer 2 · edited May 23 '17 at 10:25

if you got a "headers already sent" you have some output (whitespaces) before sending your excel headers. Please enshure that the <?php tag is the very first in your file.

Please read this thread: How to fix "Headers already sent" error in PHP

You should use a library like this http://phpexcel.codeplex.com/ to be able to output stable excel files.

Here is a hello world example for phpexcel:

http://phpexcel.codeplex.com/wikipage?title=Examples&referringTitle=Home

Export Mysql Data into MS Excel Using a Link\Button in PHP

2 Answers2

Linked