Spreadsheet制作mysql数据Excel导出

<?php

namespace app\api\controller;

use app\common\controller\Api;
use PhpOffice\PhpSpreadsheet\Spreadsheet;



class Test extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    public function _initialize()
    {
        parent::_initialize();

    }



    public function export($data=null)
    {

            set_time_limit(0);
            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $op = $this->request->post('op');
            $columns = $this->request->post('columns');

            //$excel = new PHPExcel();
            $spreadsheet = new Spreadsheet();

            $spreadsheet->getProperties()
                ->setCreator("FastAdmin")
                ->setLastModifiedBy("FastAdmin")
                ->setTitle("标题")
                ->setSubject("Subject");
            $spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
            $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
            $worksheet = $spreadsheet->setActiveSheetIndex(0);
            $list=$data;
            $total=count($list);
            $first = array_keys($list[0]);
            foreach ($first as $index => $item) {
                $worksheet->setCellValueByColumnAndRow($index, 1, __($item));
            }

//            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(__DIR__ . '/muban/test.xls');  //读取模板
            $worksheet = $spreadsheet->getActiveSheet();     //指向激活的工作表
            $worksheet->setTitle('模板测试标题');
            $tree=['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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
            $length=count($data[0]);
            $keys=array_keys($data[0]);
            $tree=array_slice($tree,0,$length);
            //行头
            for($i=0;$i<$length;$i++){
                $worksheet->setCellValue($tree[$i].'1', $keys[$i]);
            }
            //内容
            for($i=0;$i<$total;$i++){
                $a=$i+2;
                //$i代表当前第几条
                for ($s=0;$s<$length;$s++){
                    //$s代表每个字段插入一次
                    //需要插入的字段
                    $k=$keys[$s];
                    //替换表情
                    $str = preg_replace_callback(
                        '/./u',
                        function (array $match) {
                            return strlen($match[0]) >= 4 ? '' : $match[0];
                        },
                        $data[$i][$k]);
                    $worksheet->setCellValue($tree[$s].$a, $str);   //送入A1的内容
                }

            }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        //下载文档
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'. date('Y-m-d') . '_test'.'.xlsx"');
        header('Cache-Control: max-age=0');
//                $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

            return;
        }



}