PhpSpreadsheet导出excel

namespace service;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExcelService
{
    /**
     * @param string $name excel表的表名
     * @param array $data 要导出excel表的数据，接受一个二维数组
     * @param array $head excel表的表头，接受一个一维数组
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function outdata($name = '', $data = [], $head = [],$format = "xlsx")
    {
        set_time_limit(0);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
//        $sheet->setTitle('表名');
        $letter = 'A';
        foreach($head as $values){
            $sheet->setCellValue($letter.'1', $values);
            ++$letter;
        }
        if(is_array($data)){
            foreach($data as $k=>$v){
                $letter = 'A';
                $k = $k+2;
                reset($head);
                foreach($head as $key=>$value){
                    $testKey = explode('.',$key);
                    if(count($testKey)>1){
                        $val = $v[$testKey[0]][$testKey[1]];
                    }else{
                        $val = $v[$key];
                    }
                    $sheet->setCellValue($letter.$k, $val);
                    ++$letter;
                }
            }
        }
        ob_end_clean();
        if ($format == 'xls') {
            //输出Excel03版本
            header('Content-Type:application/vnd.ms-excel');
            $class = "\PhpOffice\PhpSpreadsheet\Writer\Xls";
        } elseif ($format == 'xlsx') {
            //输出07Excel版本
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $class = "\PhpOffice\PhpSpreadsheet\Writer\Xlsx";
        }
        //输出名称
        header('Content-Disposition:attachment;filename="'.mb_convert_encoding($name,"GB2312", "utf-8").'.'.$format.'"');
        //禁止缓存
        header('Cache-Control: max-age=0');
        $writer = new $class($spreadsheet);
//        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

        $writer->save('php://output');

//        $filePath = env('runtime_path')."temp/".time().microtime(true).".tmp";
//        $writer->save($filePath);
//        readfile($filePath);
//        unlink($filePath);

//        header('Content-Type: application/vnd.ms-excel');
//        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
//        header('Cache-Control: max-age=0');
//        $writer = new Xlsx($spreadsheet);
//        $writer->save('php://output');
//
//        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}

 

使用导出模板进行导出

        $list = [];
        $spreadsheet = IOFactory::load('./tpl/down.xlsx');
        $worksheet = $spreadsheet->getActiveSheet();
        $i = 1;
        foreach ($list as $k=>$vo){
            $worksheet->setCellValueExplicit('A'.$i, $k+1,DataType::TYPE_STRING);
           
            ++$i;
        }

        ob_end_clean();
        
        $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        //输出名称
        header('Content-Disposition:attachment;filename="' . mb_convert_encoding('文件名称', "GB2312", "utf-8") . '.xlsx"');
        //禁止缓存
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;

 

 

说说遇到的问题：

一开始本地导出完全没问题，然后放到线上服务器，可以导出，但导出的文件死活打不开(xlsx)，提示：EXCEL无法打开文件，因为文件格式或文件扩展名无效；导出xls文件打开是乱码

解决办法，在header()导出前添加 

ob_end_clean();

清除缓冲区，避免乱码，重试然后正常导出和打开