phpspreadsheet 工具类导出整合优化,非原创

首先

 composer require phpoffice/phpspreadsheet

然后上代码

配合数据库切片效果更好，但是为了简单方便就直接。。。。

getActiveSheet();

        //设置列
        $rowAr=self::setRowAr($count);
        foreach ($rowAr as $k => $v) {
            if($k>$count) break;
            $sheet->setCellValue($v.'1',$titles[$k]);
        }

        //写入值
        foreach ($datas as $k => $v) {
            foreach ($rowAr as $ke => $ve){
                if($ke>$count) break;
                $sheet->setCellValue($ve.($k+2),$v[$fields[$ke]]);
                $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); //固定列宽
            }
        }
        //冻结首行
        $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);

        //在输出Excel前，缓冲区中处理BOM头
        ob_end_clean();
        ob_start();

        $fileNameArr = explode('.',$file_name);
        $ext = array_pop($fileNameArr);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$file_name");
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, ucfirst($ext));//把后缀名首字母大写
        $objWriter->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        return true;
    }

    /**
     * 设置列一维数组(最多701列)
     * @param int $count
     * @return false|string[]
     * @date 2020-12-11 17:32
     */
    public function setRowAr($count=26){
        $indData='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';
        $indData=explode(',',$indData);
        $curCount=26;
        for ($i=0; $i <26 ; $i++) {
            for ($j=0; $j <26 ; $j++) {
                if($curCount>=$count) return $indData;
                $indData[]=$indData[$i].$indData[$j];
                $curCount++;
            }
        }
        return $indData;
    }
}