cakePHP - PHP EXCEL saves as .html - cakePHP - PHP EXCEL saves as .html

我已经在我的CakePHP应用程序中实现了PHPExcel，这是我的帮助:

<?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!');
    }
  }
}

这是我的控制器

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

这是我的观点:

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

当我尝试在firefox中下载这个时，我得到了正确的文件扩展名，xlsx，但当尝试用safari下载时，它给我报告。xlsx.html ?除非将文件重命名为report.xlsx，否则该文件将变得无用，这是为什么?

首先，你应该将你的'layout'设置为'false'，以防止视图在你网站的默认(HTML)布局中加载。

还将响应类型设置为Excel的类型(正如其他人提到的那样)。您可以通过响应对象(http://book.cakephp.org/2.0/en/controllers/request-response.html#dealing-with-content-types)

最后，不要在helper中使用exit();。如果您没有在布局中呈现视图，则不必在输出结果

后退出。

在你的控制器;

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'));
}

注意正如Mark Baker所提到的，XLS和XLSX使用不同的mime类型，我的示例使用"经典"XLS的mime类型，如果您输出XLSX，请相应地修改mime类型

我在另一个网站上找到的一个可能的解决方案是添加

$this->render(false);

到你的控制器，以防止CakePHP发送它自己的头/响应，可能会覆盖你自己的

您跳过了CakePHP提供的调度请求的所有机制。遵循将字符串作为文件发送的指导原则:

生成相应的Content-Type标头:
```
$this->response->type('xlsx');
```
(xls和xlsx已在CakeResponse::$_mimeTypes中别名)

强制下载并指定名称:

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

或者，只是建议一个名称，但让用户选择:

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

发送一个字符串:

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

防止控制器尝试渲染视图:
```
return $this->response;
```

有一个小故障。如果您使用'php://output'作为文件名，那么PHPExcel将发出输出，CakePHP将不再发送HTTP头以避免警告消息。为了避免这种情况，您需要输出缓冲函数来保存生成的输出:

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

你可以试试

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