php - cakePHP - PHP EXCEL 另存为 .html

Question

我已经在我的 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!');
    }
  }
}

这是我的 Controller :

    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 下载时，它给了我 report.xlsx.html ？除非您将其重命名为 report.xlsx，否则该文件将变得无用，这是怎么回事？

Answer 1

首先，您应该将“layout”设置为“false”，以防止以您网站的默认 (HTML) 布局加载 View 。

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

最后，不要在您的助手中使用 exit();。如果你没有在布局中渲染你的 View ，你不必在输出结果后退出

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

注意正如 Mark Ba​​ker 提到的，XLS 和 XLSX 使用不同的 mime 类型，我的示例使用“经典”XLS 的 mime 类型，如果您正在输出 XLSX，请相应地修改 mime 类型