PHPExcel save a CSV is adding the webpage HTML as well

Question

I have a webpage that has a radio group as the options for the file format you wish to save. Options are:

.xls
.xlsx
.csv

All work but the .csv as it also adds the page HTML to the bottom of the file.

Here is what I'm trying (code snippets to show functionality):

// Creating the format
$data = $this->getQueryResults();

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle("Report"); 

$objPHPExcel->getProperties()->setCreator("me");     
$objPHPExcel->getProperties()->setLastModifiedBy("me");                 
$objPHPExcel->getProperties()->setSubject("Report Stuff");     
$objPHPExcel->getProperties()->setDescription("Report Stuff");

// Next I iterate through the data array
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);

// check the radio option selected for the file format
if($this->radioXLS->Checked) {
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$excel_name.'.xls"');
    header('Cache-Control: max-age=0');
}

if($this->radioXLSX->Checked) {
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$excel_name.'.xlsx"');
    header('Cache-Control: max-age=0');
}

if($this->radioCSV->Checked) {
    ob_end_clean(); // add/removing this does nothing
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
    $objWriter->setDelimiter(',');
    $objWriter->setEnclosure('');
    $objWriter->setLineEnding("\r\n");
    $objWriter->setSheetIndex(0);
    ob_end_clean(); // add/removing this does nothing

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename="'.$excel_name.'.csv"');
    header('Cache-Control: max-age=0');
}

$objWriter->save('php://output');

Any thoughts as to why it appends the page HTML to the .csv file?

On a side note this is a Prado project if that matters

UPDATE:

A little more...

I have a webpage that generate a report in a tablature format (Think table/grid). On the same page I have the option to save the date in the tablature format to an Excel .xls (somehow .xlsx is not working now, ugh...). The user has the option to save the file in .xls .xlsx .csv, when clicked the file downloads from that page.

Would this cause the webpage already rendered to be added to the output via: php://output?

UPDATE - Solution:

Yep after looking at the excel files it's also adding the webpage HTML. I have also looked at the output buffer PHP functions but still nothing is working

    while(ob_get_level() > 0) {
        ob_end_clean();
    }

    if($this->radioCSV->Checked) {                        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename="'.$excel_name.'.csv"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->setDelimiter(',');
        $objWriter->setEnclosure('');
        $objWriter->setLineEnding("\r\n");
        $objWriter->setSheetIndex(0);
    } elseif($this->radioXLSX->Checked) {
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: no-store, no-cache, must-revalidate");
        header("Cache-Control: post-check=0, pre-check=0", false);
        header("Pragma: no-cache");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$excel_name.'.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    } else {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$excel_name.'.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    } 

    $objWriter->save('php://output');
    exit();

Álvaro González · Accepted Answer · 2011-07-27T18:45:22.223

5

Writing to php://output is exactly the same as doing a plain echo statement. The output of $objWriter->save() will be added to everything else that's echoed or lies outside PHP blocks (<?php ... ?>).

An example:

This is right:

<?php

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle("Report"); 
// ...
$objWriter->save('php://output');

?>

This is wrong:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="es">
<head><title>Export to Excel</title>
</head>
<body>
<?php

echo '<h1>Export to Excel</h1>';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle("Report"); 
// ...
$objWriter->save('php://output');

?>
</body>
</html>

edited Jul 27 '11 at 18:45

answered Jul 27 '11 at 18:17

Álvaro González

142,137
41
261
360

Can I suppress/block the output for the file download somehow? – Phill Pafford Jul 27 '11 at 18:40
I guess my answer was not clear enough. If there is output it's because **you** put it there. Simply, when you are generating an Excel file don't print anything else. I'll try to update my answer with an example. – Álvaro González Jul 27 '11 at 18:43
updated again but still no luck, am I calling the output buffers right? – Phill Pafford Jul 27 '11 at 19:42
Thanks! This lead me to my solution – Phill Pafford Jul 28 '11 at 12:53

score 3 · Answer 2 · answered Jul 27 '11 at 18:13

3

Have you verified that the Excel files don't contain the HTML? They might just be ignored by Excel. Does adding exit(); after the last line change anything?

answered Jul 27 '11 at 18:13

Kaivosukeltaja

15,541
4
40
70

PHPExcel save a CSV is adding the webpage HTML as well

2 Answers2