使用PHP-Xlswriter扩展导出百万行Excel文件

简介

xlsxwriter 是一个 PHP C 扩展，可用于在Excel 2007及以上版本XLSX文件中写入多个工作表的文本，数字，公式和超链接

https://gitee.com/viest/php-ext-xlswriter
https://github.com/mk-j/PHP_XLSXWriter

导入百万行测试数据

http://www.884358.com/mysql-test/

导入时如果提示错误：

ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'

需要修改employees.sql中的storage_engine为default_storage_engine

   set default_storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

select CONCAT('storage engine: ', @@default_storage_engine) as INFO;

导出测试

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);