PHPExcel download not working properly

Question

I am trying to create a page that allows a user to download the contents of an SQL table, into an excel file.

Problem : When I open the excel file, it only contains random gibberish. An example -

PKQ=DG’D²Xð[Content_Types].xml­”MNÃ0…÷œ"ò%nY „švAa
•(0ö¤±êØ–gúw{&i‰@ÕnbEö{ßøyìÑdÛ¸l
mð¥‘×ÁX¿(ÅÛü)¿’òF¹à¡;@1_滘±Øc)j¢x/%ê…Eˆày¦

This is my code -

<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

$download="";
if (isset($_GET['surveyid'])) {
//Survey ID
$download = $_GET['surveyid'];
require_once('../Classes/PHPExcel.php');

$query="SELECT b.question_id as qid,
                a.question as ques,
                b.response as response,
                count(b.response) as cnt
          FROM v3_sai.survey_responses b 
          INNER JOIN v3_sai.survey_questions a 
             ON a.id = b.question_id 
                AND a.survey_id=".intval($download)."
          group by b.response, a.question
          order by b.question_id";
          var_dump($query);
$resultdl= mysql_query($query) or die(mysql_error());
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$rowcount=1;
while($row = mysql_fetch_array($resultdl)){
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $row['qid']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['ques']); 
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, $row['response']);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowcount, $row['cnt']); 
$rowCount++; 
} 
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="file.xls"');
$objWriter->save('php://output');
die();
}

Solution 1

You can use like this

<?php 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=abc".xls");
header("Pragma: no-cache");
header ("Expires: 0");
?>
    <table width="100%" border="1">
        <tr>
            <td>
                <h1> qid</h1>
            </td>
            <td>
                <h1> ques</h1>
            </td>
            <td>
                <h1> response</h1>
            </td>
            <td>
                <h1> cnt</h1>
            </td>
        </tr>

    while($row = mysql_fetch_array($resultdl)){
        <tr>
            <td>
                <?php echo $row['qid']; ?>
            </td>
            <td>
                <?php echo $row['ques']; ?>
            </td>
            <td>
                <?php echo $row['response']; ?>
            </td>
            <td>
                <?php echo $row['cnt']; ?>
            </td>
        </tr>

    <?php } ?>

    </table>

Solution 2

If youre downloading an xls file (BIFF), use the PHPExcel_Writer_Excel5 Writer; if you're downloading an .xlsx file (OfficeOpenXML), use the PHPExcel_Writer_Excel2007 Writer: DON'T mix and match... that's your problem. You're creating an .xlsx (OfficeOpenXML) file using the Excel2007 Writer, but setting the headers to tell the browser to expect an .xls (BIFF) file

Recommended headers for an .xls (BIFF) download are:

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

Recommended headers for an .xlsx (OfficeOpenXML) download are:

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

Note that Content-Type, Content-Disposition, may be treated as case-sensitive by browsers, so Content-Type is not the same as Content-type.... and I believe that may also give you problems

Solution 3

I know i might be responding a bit late to this but none of the above worked for me. It's true using ob_clean(); will remove any cached material so that it does not interfere with the returned headers + file content. What matters is where do you clean of the extra gibberish. So after days of scratching my head i've noticed that you need to ob_clean(); right before the headers. If you do it somewhere else you will have the same problem. Here is a sample code that worked for me.

            ob_clean();
            $fileName = "Test.xlsx";
            # Output headers.
            header("Set-Cookie: fileDownload=true; path=/");
            header("Cache-Control: private");
            header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            header("Content-Disposition: attachment; filename='".$fileName."'");
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
            header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header ('Pragma: public'); // HTTP/1.0
            # Output file.
            $return->save('php://output');
            die();

Comments

[Sainath Krishnan]

Tried this. No change :( Still gibberish

[Mark Baker]

Oh ye gods, accepted answer is to create html markup and pretend that it's an Excel file.... what is the world coming to?

[Sainath Krishnan]

Well it does work... None of the other solutions work :|

[Sainath Krishnan]

Error I get when doing this is - "Excel cannot open the file '01simple-1.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

[Sainath Krishnan]

Excel still opens though

[Mark Baker]

Some versions of Excel will complain about a file format that doesn't match the file extension.... that's why you should send the correct headings that match the file type you're creating: but it's unclear from your question whether you're seeing PKQ=DG’D²Xð[Content_Types].xml­”MNÃ0…÷œ"ò%nY „švAa •(0ö¤±êØ–gúw{&i‰@ÕnbEö{ßøyìÑdÛ¸l mð¥‘×ÁX¿(ÅÛü)¿’òF¹à¡;@1_滘±Øc)j¢x/%ê…Eˆày¦ in the browser, or in the MS Excel when you open the file

[Mark Baker]

It does something completely different, that MS Excel is generous enough to work with.... and that may be adequate for your needs, but it doesn't actually answer your question; and some more recent versions of Excel may complain about the discrepancy between file format and extension

[Mark Baker]

Not to mention the syntax errors in header("Content-Disposition: attachment; filename=abc".xls"); and in header("Pragma: no-cache"); ("Expires: 0");

[Sainath Krishnan]

Ah sorry to not make that clearer, I see that in excel! And specifically, I see it AFTER all the table data (So the actual functionality is working properly now, but the junk data I could do without)

[Mark Baker]

If you see it after the table data, then you're still echoing the table data (ie sending it to php://output) as well as the Excel filestream.... that's resulting in a completely corrupted file. When downloading a file to a browser, make sure you don't output anything else at all, whether through echo/print or even simply spurious newline or space characters outside of the PHP tags.

[Mark Baker]

A lot of people use ob_clean() immediately before the headers to ensure that no additional output can be sent to the browser, and a die() immediately after the save()

[Mark Baker]

Note for future reference, that junk data as you call it is the actual Excel (OfficeOpenXML) filestream.... that is an actual Excel file to Excel; but because you have all the additional table data it's been echoed after that

[Sainath Krishnan]

I added ob_clean() just before the headers, now the excel opens without any issues, but the excel sheet itself is empty! :o

[Mark Baker]

In that case, were you iterating through the database query resultset building html markup, and then iterating through it again to populate the PHPExcel object? If so, you shouldn't have been building the html markup in the first place... your code doesn't show that, but it would explain why the file had apparently "loaded", imported as html with the stream of junk data at the end; and unless you'd reset the resultset so that it looped again at the start (using mysql_data_seek()) then there would be no more rows to loop over...

[Mark Baker]

... so the PHPExcel worksheet would never have been populated, and so would be empty. Using the ob_clean() would eliminate the html markup from being sent to the php://output stream, so the Excel workbook would have been created cleanly as a real OfficeOpenXML file, but without any actual data

[Sainath Krishnan]

Here is the full code with nothing removed. codepad.org/ETVNQudd. I'm actually learning quite a bit from you, thank you!

[Mark Baker]

You are displaying a load of HTML, and not even populating the PHPExcel worksheet with data, so that certainly explains your problems

[Sainath Krishnan]

What would I need to change here? And the HTML is the page's own HTML. Containing a list of surveys, and allowing the user to choose one, which would then download its report. Without ob_clean, it works with the Excel Filestream characters appearing at the end. With it, the page is blank (As you said, I am not actually sending it across). Could you please guide me towards a solution? :)

[Kuya A]

how do I forced download this one?

[Ákos Nikházy]

This, combined with @Mark Barker's answer about "DON'T mix and match" solution worked. ob_clean and die at the end made it work.

[Bikash Ranjan]

Solution for this add "ob_end_clean();" in download script , note : start and end from the header this is the main solution , due to tmp memory observed that issue.