基于PhpExcel封裝的PHPOffice工具類，導出Excel檔案

筆者使用的環境目前為Thinkphp5+PHP7.1

首先composer安裝PHPExcel,

composer require phpoffice/phpexcel
           

注：phpexcel不再維護，推薦使用phpspreadsheet，

composer require phpoffice/phpspreadsheet
           

PhpOffice.php

class PhpOffice
{
    private $excel;
    private $writer;
    private $writer5;
    private $filename;
    static $rows = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',];

    public function __construct() {
        import('PHPExcel', CORE_PATH, '.php');
        $this->excel = new \PHPExcel();
        $this->setConfig();
        //Save Excel 2007 file 儲存
        $this->writer = new \PHPExcel_Writer_Excel2007($this->excel);
        //Save Excel 5 file 儲存
        $this->writer5 = new \PHPExcel_Writer_Excel5($this->excel); //設定儲存版本格式

    }

    /**
     * @title 設定檔案名稱
     * @param string $filename
     * @return $this
     */
    public function setFileName($filename = '') {
        if ($filename) {
            $this->filename = $filename;
        } else {
            $this->filename = date('Y-m-d H:m:s');
        }
        return $this;
    }

    /**
     * @title 設定表單名稱
     * @param $name
     * @return $this
     */
    public function setSheetName($name) {
        $this->excel->getActiveSheet()->setTitle($name);
        return $this;
    }

    /**
     * @title 設定列标題
     * @param $title
     * @param int $height
     * @param int $width
     * @return $this
     */
    public function setSheetTitle($title, $height = 20, $width = 15) {
        if (!is_array($title)) {
            $title = explode(',', $title);
        }
        //循環标題數組
        foreach ($title as $k => $value) {
            $this->excel->setActiveSheetIndex(0)->setCellValue(self::$rows[$k] . '1', $value);
            //設定列寬
            $this->excel->getActiveSheet()->getColumnDimension(self::$rows[$k])->setWidth($width);
            //設定字型
            $this->excel->getActiveSheet()->getStyle(self::$rows[$k] . '1')->getFont()->setSize(15);
        }
        //設定行高
        $this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight($height);
        return $this;
    }

    /**
     * @title 設定寬度
     * @param array $column
     * @return $this
     */
    public function setWidth($column = []) {
        //設定列寬
        foreach ($column as $key => $val) {
            $this->excel->getActiveSheet()->getColumnDimension(strtoupper($key))->setWidth($val);
        }
        return $this;
    }

    /**
     * @title 填充資料
     * @param $data
     * @param int $start
     * @return $this
     */
    public function setData($data, $start = 2) {
        if (!is_array($data)) {
            $data = explode(',', $data);
        }
        foreach ($data as $k => $datum) {
            foreach ($datum as $i => $item) {
                $this->excel->setActiveSheetIndex(0)->setCellValue(self::$rows[$i] . $start, $item);
            }
            $start++;
        }
        return $this;
    }


    /**
     * @title 設定表格基本資訊(選填)
     * @return \PHPExcel
     */
    public function setConfig() {
        $this->excel->getProperties()->setCreator('Sooc')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('xls')
            ->setSubject('')
            ->setDescription('')
            ->setKeywords('')
            ->setCategory('');
        $this->excel->getDefaultStyle()->getFont()->setSize(15);
        $this->setFileName();
        return $this->excel;
    }

    /**
     * @title 測試運作
     */
    public function test_run() {

        $spreadsheet = $this->excel;

        // Add some data
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B1', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D1', 'world!');

        // Miscellaneous glyphs, UTF-8
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

        // Rename worksheet

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        $this->downloadExcel();

    }

    /**
     * @title 導入資料
     * @param string $filepath
     * @return array
     */
    public function loadExcel($filepath = '') {
        $spreadsheet = \PHPExcel_IOFactory::load($filepath);
        $result = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        return $result;
    }


    /**
     * @title 輸出下載下傳檔案
     */
    public function downloadExcel($type='xls') {

        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$this->filename.'.xls"');
        header("Content-Transfer-Encoding:binary");
        if($type == 'xlsx'){
            $this->writer->save('php://output');
        }else{
            $this->writer5->save('php://output');
        }
        exit;
    }

}
           

使用用法

$objPhpExcel = new \PhpOffice();
//待導出資料
$data = [];
//表頭
$data[] = ['序号','項目類型','項目名稱','項目級别','主辦機關','承辦機關','協辦機關','項目簡介'];
foreach ($pro_arr as $k=>$v){
    //處理資料
    $data[] = [
        $k+1,
        getDicName($v['project_type']),
        $v['title'],
        getDicName($v['project_level']),
        $v['org_unit'],
        $v['co_unit'],
        $v['contract_unit'],
        $v['description']
    ];
}
$file_name = '資訊表'.date('Ymd');
$set_width = ['A'=>5,'B'=>15,'C'=>50,'D'=>15,'E'=>20,'F'=>20,'G'=>20];
$objPhpExcel->setFileName($file_name);
$objPhpExcel->setWidth($set_width);
$objPhpExcel->setData($data,1);
$objPhpExcel->downloadExcel();