PhpOffice\PhpSpreadsheet导入Excel保存数据库,数据量在几十万,上百万后,读取excel文件内容内存会严重不足,本地测试50M的Excel数据读取,16G内存会立马跑满。
$spreadsheet = IOFactory::load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray('', true, true, true);
excel另存为txt文件处理,内存不受影响
$file = fopen($path, "r");
$data = [];
while(!feof($file)) {
$tmp = fgets($file);
if($tmp !== false) {
$tmp = trim(mb_convert_encoding($tmp, "UTF-8", "GBK"));
//$data[]= trim(iconv('GBK', 'UTF-8', $tmp));//fgets()函数从文件指针中读取一行
if(!empty($tmp)) {
$data[] = explode("\t", $tmp);
}
}
}
fclose($file);
return $data;
header('Content-Type: application/vnd.ms-execl');
header('Content-Disposition: attachment;filename="rter.csv"');
$fp = fopen('php://output', 'a');
foreach ($data as $k => $datum){
foreach($datum as $key => $item) {
$datum[$key] = iconv('UTF-8', 'GBK', $item);
}
fputcsv($fp, $datum);
if($k/10000 == 0){
ob_flush();
flush();
}
}
//关闭句柄
fclose($fp);
ob_flush();
flush();
exit;
header('content-type:application/octet-stream');
header('Accept-Ranges:bytes');
$filesize = filesize($file);
//告诉浏览器返回的文件大小
header('Accept-Length:'.$filesize);
//告诉浏览器文件作为附件处理并且设定最终下载完成的文件名称
header('content-disposition:attachment;filename=fdgd.csv');
//针对大文件,规定每次读取文件的字节数为4096字节,直接输出数据
$read_buffer = 4096;
$handle = fopen($file, 'rb');
//总的缓冲的字节数
$sum_buffer=0;
//只要没到文件尾,就一直读取
while(!feof($handle) && $sum_buffer < $filesize) {
echo fread($handle, $read_buffer);
$sum_buffer+=$read_buffer;
}
//关闭句柄
fclose($handle);
exit;