cakePHP - PHP EXCEL saves as .html

Question

I have implemented PHPExcel within my CakePHP application, this is my helper:

<?php
App::uses('AppHelper', 'Helper');

/**
 * Helper for working with PHPExcel class.
 * PHPExcel has to be in the vendors directory.
 */

class PhpExcelHelper extends AppHelper {
  /**
   * Instance of PHPExcel class
   * @var object
   */
  public $xls;
  /**
   * Pointer to actual row
   * @var int
   */
  protected $row = 1;
  /**
   * Internal table params 
   * @var array
   */
  protected $tableParams;

  /**
   * Constructor
   */
  public function __construct(View $view, $settings = array()) {
        parent::__construct($view, $settings);
    }

  /**
   * Create new worksheet
   */
  public function createWorksheet() {
    $this->loadEssentials();
    $this->xls = new PHPExcel();
  }

  /**
   * Create new worksheet from existing file
   */
  public function loadWorksheet($path) {
    $this->loadEssentials();
    $this->xls = PHPExcel_IOFactory::load($path);
  }

  /**
   * Set row pointer
   */
  public function setRow($to) {
    $this->row = (int)$to;
  }

  /**
   * Set default font
   */
  public function setDefaultFont($name, $size) {
    $this->xls->getDefaultStyle()->getFont()->setName($name);
    $this->xls->getDefaultStyle()->getFont()->setSize($size);
  }

  /**
   * Start table
   * inserts table header and sets table params
   * Possible keys for data:
   *  label   - table heading
   *  width - "auto" or units
   *  filter  - true to set excel filter for column
   *  wrap  - true to wrap text in column
   * Possible keys for params:
   *  offset  - column offset (numeric or text)
   *  font  - font name
   *  size  - font size
   *  bold  - true for bold text
   *  italic  - true for italic text
   *  
   */
  public function addTableHeader($data, $params = array()) {
    // offset
    if (array_key_exists('offset', $params))
      $offset = is_numeric($params['offset']) ? (int)$params['offset'] : PHPExcel_Cell::columnIndexFromString($params['offset']);
    // font name
    if (array_key_exists('font', $params))
      $this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setName($params['font_name']);
    // font size
    if (array_key_exists('size', $params))
      $this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setSize($params['font_size']);
    // bold
    if (array_key_exists('bold', $params))
      $this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setBold($params['bold']);
    // italic
    if (array_key_exists('italic', $params))
      $this->xls->getActiveSheet()->getStyle($this->row)->getFont()->setItalic($params['italic']);

    // set internal params that need to be processed after data are inserted
    $this->tableParams = array(
      'header_row' => $this->row,
      'offset' => $offset,
      'row_count' => 0,
      'auto_width' => array(),
      'filter' => array(),
      'wrap' => array()
    );

    foreach ($data as $d) {
      // set label
      $this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset, $this->row, $d['label']);
      // set width
      if (array_key_exists('width', $d)) {
        if ($d['width'] == 'auto')
          $this->tableParams['auto_width'][] = $offset;
        else
          $this->xls->getActiveSheet()->getColumnDimensionByColumn($offset)->setWidth((float)$d['width']);
      }
      // filter
      if (array_key_exists('filter', $d) && $d['filter'])
        $this->tableParams['filter'][] = $offset;
      // wrap
      if (array_key_exists('wrap', $d) && $d['wrap'])
        $this->tableParams['wrap'][] = $offset;

      $offset++;
    }
    $this->row++; 
  }

  /**
   * Write array of data to actual row
   */
  public function addTableRow($data) {
    $offset = $this->tableParams['offset'];

    foreach ($data as $d) {
      $this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset++, $this->row, $d);
    }
    $this->row++;
    $this->tableParams['row_count']++;
  }

  /**
   * End table
   * sets params and styles that required data to be inserted
   */
  public function addTableFooter() {
    // auto width
    foreach ($this->tableParams['auto_width'] as $col)
      $this->xls->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);
    // filter (has to be set for whole range)
    if (count($this->tableParams['filter']))
      $this->xls->getActiveSheet()->setAutoFilter(PHPExcel_Cell::stringFromColumnIndex($this->tableParams['filter'][0]).($this->tableParams['header_row']).':'.PHPExcel_Cell::stringFromColumnIndex($this->tableParams['filter'][count($this->tableParams['filter']) - 1]).($this->tableParams['header_row'] + $this->tableParams['row_count']));
    // wrap
    foreach ($this->tableParams['wrap'] as $col)
      $this->xls->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).($this->tableParams['header_row'] + 1).':'.PHPExcel_Cell::stringFromColumnIndex($col).($this->tableParams['header_row'] + $this->tableParams['row_count']))->getAlignment()->setWrapText(true);
  }

  /**
   * Write array of data to actual row starting from column defined by offset
   * Offset can be textual or numeric representation
   */
  public function addData($data, $offset = 0) {
    // solve textual representation
    if (!is_numeric($offset))
      $offset = PHPExcel_Cell::columnIndexFromString($offset);

    foreach ($data as $d) {
      $this->xls->getActiveSheet()->setCellValueByColumnAndRow($offset++, $this->row, $d);
    }
    $this->row++;
  }

  /**
   * Output file to browser
   */
  public function output($filename = 'export.xlsx') {
    // set layout
    $this->View->layout = '';
    // headers
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');
    // writer
    $objWriter = PHPExcel_IOFactory::createWriter($this->xls, 'Excel2007');
    $objWriter->save('php://output');
    // clear memory
    $this->xls->disconnectWorksheets();
  }

  /**
   * Load vendor classes
   */
  protected function loadEssentials() {
    // load vendor class
    App::import('Vendor', 'PHPExcel/Classes/PHPExcel');
    if (!class_exists('PHPExcel')) {
      throw new CakeException('Vendor class PHPExcel not found!');
    }
  }
}

And this is my controller:

    public $helpers = array('PhpExcel');
    ...
    public function excel() {
        $this->set('participants', $this->Participant->find('all'));
    }

This is my view:

<?php
    $this->PhpExcel->createWorksheet();
    $this->PhpExcel->setDefaultFont('Calibri', 12);

    // define table cells
    $table = array(
        array('label' => __('id'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Förnamn'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Efternamn'), 'width' => 'auto', 'filter' => true),
        array('label' => __('E-postadress'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Mobiltelefon'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Specialkost'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Enhet'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Seminarium'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Utanför Stockholm'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Dela rum'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Transfer'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Bara där på dagen'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Låt'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Lärare som blivit hyllad'), 'width' => 'auto', 'filter' => true),
        array('label' => __('Kommentar'), 'width' => 'auto', 'filter' => true),
    );

    // heading
    $this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));

    foreach ($participants as $d) {
        $this->PhpExcel->addTableRow(array(
            $d['Participant']['id'],
            $d['Participant']['f_name'],
            $d['Participant']['l_name'],
            $d['Participant']['email'],
            $d['Participant']['mobile_phone'],
            $d['Participant']['special'],
            $d['Participant']['school'],
            $d['Participant']['seminarium_id'],
            $d['Participant']['outside_sthlm'],
            $d['Participant']['share_room'],
            $d['Participant']['transfer'],
            $d['Participant']['only_day'],
            $d['Participant']['song'],
            $d['Participant']['teacher'],
            $d['Participant']['comments']
        ));
    }

    $this->PhpExcel->addTableFooter();
    $this->PhpExcel->output();
    $this->PhpExcel->exit();
?>

When I try to download this in firefox I get the right file extension, xlsx but when trying to download with safari it gives me report.xlsx.html ? and the file becomes useless unless you rename it to report.xlsx, How come?

thaJeztah · Accepted Answer · 2013-03-02T10:05:14.640

First of all, you should set your 'layout' to 'false' to prevent the view being loaded in the default (HTML) layout of your website.

Also set the response-type to the type for Excel (as others have mentioned. You can do so via the response-object (http://book.cakephp.org/2.0/en/controllers/request-response.html#dealing-with-content-types)

And finally, do not exit(); in your helper. If you're not rendering your view in a layout, you don't have to exit after outputting the result

In your controller;

public $helpers = array('PhpExcel');
...
public function excel() {
    // disable the layout
    $this->layout = false;

    // Add/define XLS contenttype
    $this->response->type(array('xls' => 'application/vnd.ms-excel'));

    // Set the response Content-Type to xls
    $this->response->type('xls');

    $this->set('participants', $this->Participant->find('all'));
}

note As Mark Baker mentioned, XLS and XLSX use a different mime-type, my example uses the mime type for 'classic' XLS, if you're outputting XLSX, modify the mime type accordingly

Glad I could help and thank @MarkBaker and Salgua for looking up the right Mime-Types, they are hard to remember :) — thaJeztah, Mar 02 '13 at 15:31

score 1 · Answer 2 · answered Mar 01 '13 at 21:07

1

A possible solution I've found on another site says to add

$this->render(false);

to your controller, to prevent CakePHP from sending its own headers/response that might override your own

answered Mar 01 '13 at 21:07

Mark Baker

209,507
32
346
385

Álvaro González · Answer 3 · 2018-12-27T12:43:39.297

You're skipping all the mechanisms that CakePHP provides to dispatch requests. Following the guidelines from Sending a string as file:

To generate the appropriate Content-Type header:
```
$this->response->type('xlsx');
```
(Both xls and xlsx are already aliased in CakeResponse::$_mimeTypes.)

To force download and assign a name:

$this->response->download($filename);

Alternatively, to just suggest a name but let the user choose:

$this->response->header('Content-Disposition', 'inline; filename="' . $filename . '"');

To send a string:

$this->response->body('data comes here');

To prevent the controller from trying to render a view:
```
return $this->response;
```

There's a glitch, though. If you use 'php://output' as file name then PHPExcel will emit output and CakePHP will no longer send HTTP headers to avoid warning messages. To avoid that you need output buffer functions to hold generated output:

$objWriter = PHPExcel_IOFactory::createWriter($this->xls, 'Excel2007');
ob_start();
$objWriter->save('php://output');
$this->response->body(ob_get_clean());

score 0 · Answer 4 · answered Mar 01 '13 at 14:10

0

You can try

header('Content-type: application/ms-excel');

answered Mar 01 '13 at 14:10

salgua

698
2
10
22

The correct MIME type is application/vnd.ms-excel for .xls files or application/vnd.openxmlformats-officedocument.spreadsheetml.sheet for .xlsx files – Mark Baker Mar 01 '13 at 14:18

cakePHP - PHP EXCEL saves as .html

4 Answers4