php数据导出插件phpspreadsheet内存优化

实际应用项目：http://github.crmeb.net/u/long

设置缓存

节省内存: https://phpspreadsheet.readthedocs.io/en/latest/topics/memory_saving/#memory-saving

这里我们使用

use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use Symfony\Component\Cache\Psr16Cache;
... 
$adapter = new FilesystemAdapter('excel', 60*60*10, storage_path('framework/cache/data'));
$cache = new Psr16Cache($adapter);
\PhpOffice\PhpSpreadsheet\Settings::setCache($cache);

指定读取工作表

因为默认的情况下我们只需要读取某个工作表，如果您有多个工作表，但不需要全部加载，则可以使用setLoadSheetsOnly（）方法来限制Reader将加载的工作表。加载单个命名工作表

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #2'; 
/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

或者可以通过传递名称数组来一次调用setLoadSheetsOnly（）来指定多个工作表

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetnames = array('Data Sheet #1','Data Sheet #3'); 
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader of which WorkSheets we want to load **/ 
$objReader->setLoadSheetsOnly($sheetnames); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

循环读取excel的时候，断开sheet的链接

$m = memory_get_usage();
dump("初始化内存: " . $m);

$f = '/www/wwwroot/trmk_service/storage/app/excel/app_75535bab9a72a/2021-04-07-feedback.xls';
$f2 = '/www/wwwroot/trmk_service/storage/app/excel/app_75535bab9a72a/2021-04-08-feedback.xls';
$f3 = '/www/wwwroot/trmk_service/storage/app/excel/app_75535bab9a72a/2021-04-09-feedback.xls';

$s = \PhpOffice\PhpSpreadsheet\IOFactory::load($f);
$s1 = memory_get_usage();
$s->disconnectWorksheets();
unset($s);
dump("内存使用率 s1: " . $s1);

$s = \PhpOffice\PhpSpreadsheet\IOFactory::load($f2);
$s2 = memory_get_usage();
$s->disconnectWorksheets();
unset($s);
dump("内存使用率 s2: " . $s2);

$s = \PhpOffice\PhpSpreadsheet\IOFactory::load($f);
$s3 = memory_get_usage();
$s->disconnectWorksheets();
unset($s);
dump("内存使用率 s3: " . $s3);


$s = \PhpOffice\PhpSpreadsheet\IOFactory::load($f3);
$s4 = memory_get_usage();
$s->disconnectWorksheets();
unset($s);
dump("内存使用率 s4: " . $s4);

# 输出
> "初始化内存: 20429136"
> "内存使用率 s1: 25156120"
> "内存使用率 s2: 25264104"
> "内存使用率 s3: 25213496"
> "内存使用率 s4: 25234112"
> "exit"

参考

https://stackoverflow.com/questions/4817651/phpexcel-runs-out-of-256-512-and-also-1024mb-of-ram