线上导出excel的时候,是不是出现无法访问,如下图
因为导出的数据量比较大,我怀疑是内存溢出,于是增加了内存分配值,发现仍然报错,于是我查了下日志,如下
2020-08-14 21:31:12 [192.168.8.12][-][-][error][PhpOffice\PhpSpreadsheet\Calculation\Exception] PhpOffice\PhpSpreadsheet\Calculation\Exception: 2020_08_14!K3314 -> Formula Error: An unexpected error occured in /home/www/root/yii2-advanced/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:274 Stack trace: #0 /home/www/root/yii2-advanced/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xls/Worksheet.php(443): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue() #1 /home/www/root/yii2-advanced/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xls.php(185): PhpOffice\PhpSpreadsheet\Writer\Xls\Worksheet->close() #2 /home/www/root/logtest/admin-lte/components/Utils.php(143): PhpOffice\PhpSpreadsheet\Writer\Xls->save('php://output') #3 /home/www/root/logtest/admin-lte/controllers/VisitInfoController.php(180): app\components\Utils::getExcel('\xB5\xBC\xB3\xF6\xCE\xC4\xBC\xFE_2020_0...', Array, Array) #4 [internal function]: app\controllers\VisitInfoController->actionExport() #5 /home/www/root/yii2-advanced/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array) #6 /home/www/root/yii2-advanced/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array) #7 /home/www/root/yii2-advanced/vendor/yiisoft/yii2/base/Module.php(454): yii\base\Controller->runAction('export', Array) #8 /home/www/root/yii2-advanced/vendor/yiisoft/yii2/web/Application.php(84): yii\base\Module->runAction('visit-info/expo...', Array) #9 /home/www/root/yii2-advanced/vendor/yiisoft/yii2/base/Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request)) #10 /home/www/root/logtest/admin-lte/web/index.php(19): yii\base\Application->run() #11 {main}
发现是使用的包报了错,然后百度一番,找到了原因
导出的单元格内,第一个字符是‘=’,造成PHPExcel解析的时候当成这个单元格的值是由其他单元格计算出来的,后面需要跟着一个合法表达式。如果不是这样,应该在程序中过滤掉。
我们使用ltrim过滤掉就可以了