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();

Solution 1

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>
  

Solution 2

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?

Comments

[Phill Pafford]

Can I suppress/block the output for the file download somehow?

[Álvaro González]

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.

[Phill Pafford]

updated again but still no luck, am I calling the output buffers right?

[Phill Pafford]

Thanks! This lead me to my solution