yii2框架使用phpoffice/phpspreadsheet

一、composer包地址
https://packagist.org/packages/phpoffice/phpspreadsheet

二、github地址
https://github.com/PHPOffice/PhpSpreadsheet

三、导出的公共方法

导出csv文件

    /**
     * 导出CSV
     * @author Joker
     * @param string $title 页面标题
     * @param array $header 标题数组
     * eg:
     * $header = [
     *   '姓名',
     *   '手机'
     *  ];
     * @param $data
     * eg:
     * $data = [
     *  ['张三',13012344321],
     *  ['李四',13012344321]
     * ];
     * */
    public static function exportCsv( $title, $header, $data) {
        $spreadsheet = new Spreadsheet();

        $objSheet = $spreadsheet->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle($title);  //设置当前sheet的标题

        foreach ($header as $index => $item) {
            //设置第一栏的标题
            $objSheet->setCellValue(Coordinate::stringFromColumnIndex($index + 1) . "1", $item);
        }

        //第二行起，第一行是标题
        foreach ($data as $key => $values) {
            $key = $key + 2;
            $values = array_values($values);
            foreach ($values as $k => $v) {
                $objSheet->setCellValue(Coordinate::stringFromColumnIndex($k + 1) . $key, $v);
            }
        }

        $spreadsheet->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $title . date('Ymd') . '.csv');
        header('Cache-Control: max-age=0');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = new Csv($spreadsheet);
        $writer->save('php://output');
        exit;
    }

导出xlsx文件

   /**
     * 导出 .xlsx
     * @author Joker
     * @param string $title 文件名
     * @param array $header 行头，eg:['姓名'，'性别', '年龄']
     * @param array $data 数据，eg:
     * [
     *      [
     *          '王大',
     *          '男',
     *          15,
     *      ],
     *      [
     *          '王大',
     *          '男',
     *           15,
     *      ],
     * ]
     */
    static public function export($title, $header, &$data)
    {
        $spreadsheet = new Spreadsheet();

        $spreadsheet->getProperties()
            ->setTitle($title);
        $handle = $spreadsheet->setActiveSheetIndex(0);
        foreach ($header as $index => $item) {
            $handle->setCellValue(Coordinate::stringFromColumnIndex($index + 1) . "1", $item);
        }

        $loop = 2;
        foreach ($data as $values) {
            foreach ($values as $index => $val) {
                $val = is_array($val) ? implode('，', $val) : $val;
                $handle->setCellValue(Coordinate::stringFromColumnIndex($index + 1) . $loop, $val);
            }
            $loop++;
        }

        $spreadsheet->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $title . '.xlsx');
        header('Cache-Control: max-age=0');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        //注意如果当前程序是utf-8,请记加上此行，不然会出现乱码
        $writer->setUseBOM('UTF-8');
        $writer->save('php://output');
        exit;
    }