转：PHP phpspreadsheet 导出大数据 csv excel

最近项目一直需要导出数据到Excel里，使用了phpspreadsheet导出Excel，但是数据量一大的话就卡住了，正常导出大概不到1w条吧，超过1w条NGINX可能就报502超时了，为了解决问题只能百度看看了。

1、composer 安装 phpspreadsheet 

composer require phpoffice/phpspreadsheet

2、composer 安装 phpspreadsheet 这个

普通的Excel根据内存来算 可能不超过4000条

普通的导出xlsx （亲测封顶3000多条）

set_time_limit(0);

for($i;$i<=60000;$i++){ // 这样6万条的话 可能

    $data[] = [

        'id' => $i+1,

        'name' => '用户'.($i+1)

    ];

}

$title = [

    [

        '编号', '用户'

    ],

];

$arrData = array_merge($title, $arrData);

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// 设置单元格格式 可以省略

$styleArray = [

    'font' => [

        'bold' => true,

        'size' => 14,

    ],

];

$spreadsheet->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);

$spreadsheet->getActiveSheet()->fromArray($arrData);

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件

// header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件

header('Content-Disposition: attachment;filename=test.xlsx');//告诉浏览器输出浏览器名称

header('Cache-Control: max-age=0');//禁止缓存

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

优化后的导出xlsx （亲测封顶6万条）

如果加上优化的话 最多极限是6万条，这个是excel的文件限制

原理：其实主要是导出的时候服务器内存的问题导致无法导出大数据，所以要分流导出 并且释放内存 刷新缓冲

set_time_limit(0);

for($i;$i<=60000;$i++){

    $arrData[] = [

        'id' => $i+1,

        'name' => '用户'.($i+1)

    ];

}

$title = [

    [

        '编号', '用户'

    ],

];

$arrData = array_merge($title, $arrData);

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// 设置单元格格式 可以省略

$styleArray = [

    'font' => [

        'bold' => true,

        'size' => 14,

    ],

];

$spreadsheet->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);

$spreadsheet->getActiveSheet()->fromArray($arrData);

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

header('Content-Description: File Transfer');

header('Expires: 0');

header('Cache-Control: must-revalidate');

header('Pragma: public');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename=test.xlsx');

header('Cache-Control: max-age=0');

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

$fp = fopen('php://output', 'a');//打开output流

mb_convert_variables('GBK', 'UTF-8', $columns);

fputcsv($fp, $columns);//将数据格式化为xlsx格式并写入到output流中

$dataNum = count( $arrData );

$perSize = 1000;//每次导出的条数

$pages = ceil($dataNum / $perSize);

for ($i = 1; $i <= $pages; $i++) {

    foreach ($arrData as $item) {

        mb_convert_variables('GBK', 'UTF-8', $item);

        fputcsv($fp, $item);

    }

    //刷新输出缓冲到浏览器

    ob_flush();

    flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。

}

fclose($fp);

exit();

如果使用csv的话可以导出百万级，当然也要内存支持，但是也差不多足够

set_time_limit(0);

for($i;$i<=60000;$i++){

    $arrData[] = [

        'id' => $i+1,

        'name' => '用户'.($i+1)

    ];

}

$title = [

    [

        '编号', '用户'

    ],

];

$arrData = array_merge($title, $arrData);

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// 设置单元格格式 可以省略

$styleArray = [

    'font' => [

        'bold' => true,

        'size' => 14,

    ],

];

$spreadsheet->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);

$spreadsheet->getActiveSheet()->fromArray($arrData);

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);

header('Content-Description: File Transfer');

header('Expires: 0');

header('Cache-Control: must-revalidate');

header('Pragma: public');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename=test.csv');

header('Cache-Control: max-age=0');

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

$fp = fopen('php://output', 'a');//打开output流

mb_convert_variables('GBK', 'UTF-8', $columns);

fputcsv($fp, $columns);//将数据格式化为csv格式并写入到output流中

$dataNum = count( $arrData );

$perSize = 1000;//每次导出的条数

$pages = ceil($dataNum / $perSize);

for ($i = 1; $i <= $pages; $i++) {

    foreach ($arrData as $item) {

        mb_convert_variables('GBK', 'UTF-8', $item);

        fputcsv($fp, $item);

    }

    //刷新输出缓冲到浏览器

    ob_flush();

    flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。

}

fclose($fp);

exit();

导出CSV文件：

//导出

$filename = '实物口令数据';

$format = 'Csv';

//$format = 'Xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));

header('Cache-Control: max-age=0');

$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($newExcel, $format);

//$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Csv($newExcel);

//$objWriter->setDelimiter(';');

//$objWriter->setEnclosure('"');

//$objWriter->setLineEnding("\r\n");

//$objWriter->setSheetIndex(0);

$objWriter->setUseBOM(true);

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

//return $objWriter->save($filename . date('Y-m-d') . '.' . strtolower($format));

参考资料：https://blog.csdn.net/qq_14824885/article/details/82594355