使用PHP-Xlswriter扩展导出百万数据
xlsxwriter 是一个 PHP C 扩展,可用于在Excel 2007及以上版本XLSX文件中写入多个工作表的文本,数字,公式和超链接
https://gitee.com/viest/php-ext-xlswriter
https://github.com/mk-j/PHP_XLSXWriter
直接上代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
ini_set('memory_limit','1024M'); set_time_limit(0); function getTmpDir(): string { $tmp = ini_get('upload_tmp_dir'); if ($tmp !== False && file_exists($tmp)) { return realpath($tmp); } return realpath(sys_get_temp_dir()); } $config = [ 'path' => getTmpDir() . '/', ]; $excel = new \Vtiful\Kernel\Excel($config); // Init File $fileName = 'emp.xlsx'; //固定内存模式 $excel = $excel->constMemory($fileName, 'sheet11'); $excel = $excel->header(['id', 'empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm', 'deptno']); for($i=5000;$i<=1000000;$i+=5000){ $start = $i-5000; $limit = 5000; $sth = $this->db->pdo->prepare("SELECT * FROM emp order by id asc limit {$start},{$limit}"); $sth->execute(); $emps = $sth->fetchAll(PDO::FETCH_NUM); $excel = $excel->data($emps); } // Output $filePath = $excel->output(); // Set Header header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); header('Content-Disposition: attachment;filename="' . $fileName . '"'); header('Content-Length: ' . filesize($filePath)); header('Content-Transfer-Encoding: binary'); header('Cache-Control: must-revalidate'); header('Cache-Control: max-age=0'); header('Pragma: public'); ob_clean(); flush(); if (copy($filePath, 'php://output') === false) { // Throw exception exit('copy file to path error'); } // Delete temporary file @unlink($filePath); |
此代码本人并未进行测试,暂时只是拷贝过来做记录,已被后续使用