php - Phpspreadsheet excel解析抛出500内部服务器错误
问题描述
我正在使用 phpspreadsheet 来解析 excel 文件。我每个都有5
表格和5000
记录。excel 文件中的总25000
记录。我收到内存限制错误,500 内部错误。这是预期的还是我做错了?请在下面找到我的代码。提前致谢。
ini_set('max_execution_time', 0);
ini_set("memory_limit",-1);
$logger = new Logger();
$startTime = date('Y-m-d H:i:s');
$logger->log('Import start time: ' . $startTime);
$client = new Redis();
$client->connect('127.0.0.1', 6379);
$pool = new RedisCachePool($client);
$simpleCache = new SimpleCacheBridge($pool);
$options = LIBXML_COMPACT | LIBXML_PARSEHUGE;
Settings::setCache($simpleCache);
Settings::setLibXmlLoaderOptions($options);
$reader = IOFactory::createReader('Xlsx');
$worksheetNames = $reader->listWorksheetNames($fileName);
$chunkSize = 20;
$totalRow = 6000;
foreach ($worksheetNames as $worksheetName) {
$emptyRowCount = 0;
if (in_array($worksheetName, self::SHEET_BLACKLIST)) {
continue;
}
$logger->log('Processing the Sheet: ' . $worksheetName);
for ($startRow = 1; $startRow <= $totalRow; $startRow += $chunkSize) {
if ($emptyRowCount >= 10) {
$logger->log('Found continuos empty cells. Skipping to next sheet');
continue 2;
}
$chunkFilter = new ChunkReadFilter($startRow, $chunkSize);
$reader->setReadFilter($chunkFilter);
$reader->setLoadSheetsOnly($worksheetName);
$spreadsheet = $reader->load($fileName);
$objWorksheet = $spreadsheet->getActiveSheet();
$chunkSize = 20;
$logger->log('Import Started');
$i = 1;
foreach ($objWorksheet->getRowIterator() as $row) {
$rowIndex = $row->getRowIndex();
$logger->log(' RowIndex: ' . $rowIndex);
if (in_array($rowIndex, self::ROW_BLACKLIST)) {
continue;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
$res = [];
foreach ($cellIterator as $cell) {
$column = $cell->getColumn();
if (in_array($column, self::COLUMN_BLACKLIST)) {
continue;
}
$logger->log('Cordinate: ' . $cell->getCoordinate());
$mergeRange = $cell->getMergeRange();
if ($cell->isInMergeRange()) {
$logger->log('In MERGE RANGE');
if ($cell->isMergeRangeValueCell()) {
$logger->log('it is a MergeRangeValueCell');
$logger->log('Value: ' . $cell->getCalculatedValue());
$res[] = $cell->getCalculatedValue();
} else {
$logger->log('it is not a MergeRangeValueCell');
$mergeRange = $cell->getMergeRange();
$logger->log('Merge range: ' . $mergeRange);
$mergeRange = Coordinate::splitRange($mergeRange);
$firstCellInRange = $mergeRange[0];
$val = $objWorksheet->getCell($firstCellInRange[0])->getCalculatedValue();
$logger->log('Value: ' . $val);
$res[] = $val;
}
} else {
$logger->log('Value: ' . $cell->getCalculatedValue());
$res[] = $cell->getCalculatedValue();
}
}
if (array_filter($res)) {
$emptyRowCount = 0;
$rohreRepository = new RohreRepository();
$title = 'Rohre_' . $worksheetName . '_' . $i;
$logger->log('Title: ' . $title);
$logger->log('Going to create : ' . json_encode($res));
$rohreRepository->create($res, $title);
} else {
$emptyRowCount++;
$logger->log('Empty row: Not creating a post');
}
$i++;
}
}
$sheet = $spreadsheet->getSheetByName($worksheetName);
if ($sheet) {
$sheetIndex = $spreadsheet->getIndex($sheet);
$logger->log('Removing sheet. Sheet index: ' . $sheetIndex);
$spreadsheet->removeSheetByIndex($sheetIndex);
}
}
$endTime = date('Y-m-d H:i:s');
$logger->log('Import end time: ' . $endTime);
$start = new DateTime($startTime);
$totalTimeTaken = $start->diff(new DateTime($endTime));
$minutes = $totalTimeTaken->days * 24 * 60;
$minutes += $totalTimeTaken->h * 60;
$minutes += $totalTimeTaken->i;
$logger->log('Total time: ' . $minutes . 'Minutes');
解决方案
推荐阅读
- python-3.x - 处理对 python 请求的原始 HTTP 请求
- iframe - 带有 iframe 的 Electron 应用程序在全屏模式下获得滚动条
- react-native - 如何在所有抽屉导航器中显示 bottomTabNavigator?
- xslt - 在大量 XML 文本节点中执行文本搜索并确定模式是否出现一次
- azure - 升级到 Microsoft.Azure.Cosmos.Table 以访问 Azure 表存储后性能下降
- javascript - 使用 Node JS 在 firebse 托管中分离路由
- python - NameError:未定义名称“LoginView”
- python - 根据条件创建具有新列名的熊猫数据透视表
- sql-server - 从 0001 开始在现有表中添加新列
- java - 如何在 Java 中使用多行 lambda 表达式对 int[] 进行排序