导出excel的几种方式

1.CSV
缺点：在遇到文件内容中包含逗号的时候可能会错乱。。。

    public function export(RequestInterface $request){
        $head = ['name'=>'名字','score'=>'得分'];
        $data = [
            ['name' => '张三', 'score' => '80'],
            ['name' => '李四', 'score' => '90'],
            ['name' => '王五', 'score' => '60'],
        ];
        $head_keys = array_keys($head);
        $head_values = array_values($head);
        $fileData = mb_convert_encoding(implode(',', $head_values),"GBK","UTF-8") . "\n";

        foreach ($data as $value) {
            $temp_arr = [];
            foreach ($head_keys as $key) {
                $temp_arr[] = $value[$key] ?? '';
            }
            ;
            $fileData .= mb_convert_encoding(implode(',', $temp_arr),"GBK","UTF-8") . "\n";
        }
        $contentType = 'text/csv';
        return $this->response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', $contentType)
            ->withHeader('content-disposition', "attachment; filename=test")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'public')
            ->withBody(new SwooleStream($fileData));
    }

2.phpoffice/phpspreadsheet composer包处理
缺点：数据量大的时候很慢

    public function testDownload()
    {
        $fileName ='test.xls';
        $fieldList = ['name'=>'名字','score'=>'得分'];
        $dataList = [
            ['name' => '张三', 'score' => '80'],
            ['name' => '李四', 'score' => '90'],
            ['name' => '王五', 'score' => '60'],
        ];
        $workSheet = new Worksheet();
        //sheet名称定义
        $workSheet->setTitle('sheet1');
        //第一行写入头信息
        $columnIndex = 1;
        foreach ($fieldList as $fieldName){
            $excelKey = Coordinate::stringFromColumnIndex($columnIndex);
            $workSheet->setCellValue($excelKey.'1',$fieldName);
            $columnIndex++;
        }
        //遍历数据写入表格
        foreach ($dataList as $k=>$data){
            $columnIndex = 1;
            $rowIndex = $k+2;
            foreach ($fieldList as $field=>$fieldName){
                $excelKey = Coordinate::stringFromColumnIndex($columnIndex);
                $workSheet->setCellValue($excelKey.$rowIndex,$data[$field]??"");
                $columnIndex++;
            }
        }
        //创建excel文件类
        $spreadsheet = new Spreadsheet();
        $spreadsheet->addSheet($workSheet,0);
        $spreadsheet->setActiveSheetIndex(0);
        $writer = IOFactory::createWriter($spreadsheet,IOFactory::WRITER_XLSX);
        $filePath = BASE_PATH .'/excel/'.$fileName;
        $writer->save($filePath);
        $fileName = rawurlencode($fileName);
        $response = $this->response->withHeader('Content-Type', 'application/octet-stream')
            ->withHeader('Content-Disposition', 'attachment;filename="' . $fileName . '"')
            ->withHeader('Access-Control-Expose-Headers', 'Content-Disposition');

        // 将文件内容写入响应流
        $response->getBody()->write(file_get_contents($filePath));

        return $response;

    }

3.xlswriter扩展
支持百万级数据导出
缺点：数据查询的功能最好效率比较高,最好做成异步

    public function test()
    {

        $header = [
            [
                'field' => 'name',
                'desc' => "姓名"
            ],
            [
                'field' => 'score',
                'desc' => "分数"
            ]
        ];
        $excel = new Excel([
            'path' => BASE_PATH . '/runtime/'
        ]);
        $fileName = date('YmdHis').".xlsx";
        $textFile = $excel->fileName($fileName)->header(array_column($header,'desc'));
        $page = 1;
        $row = 1;
        do{
            $total = $nextPage=0;
            //获取数据与是否有下一页数据
            $dataList = [
                ['name' => '张三', 'score' => '80'],
                ['name' => '李四', 'score' => '90'],
                ['name' => '王五', 'score' => '60'],
            ];
            //遍历数据
            foreach ($dataList as $k=>$item) {
                foreach ($header as $index=>$headerItem){
                    $field = $headerItem['field'];
                    $textFile->insertText($row, $index, $item[$field]??'');
                }
                $row++;
            }

            $next = false;
            if($nextPage>$page){
                $next = true;
                $page = $nextPage;
            }
        }while($next);
        //写入文件
        $filePath = $textFile->output();

        $fileName = rawurlencode($fileName);
        $response = $this->response->withHeader('Content-Type', 'application/octet-stream')
            ->withHeader('Content-Disposition', 'attachment;filename="' . $fileName . '"')
            ->withHeader('Access-Control-Expose-Headers', 'Content-Disposition');

        // 将文件内容写入响应流
        $response->getBody()->write(file_get_contents($filePath));
        return $response;
    }

本作品采用《CC 协议》，转载必须注明作者和本文链接