PHP_MEMORY_LIMIT Ошибка при записи данных в таблицу excesheet с использованием PHPSpreadsheet. ⇐ Php

[Гость]

Я хочу оптимизировать код так, чтобы он занимал меньше памяти и мог выполняться при ограничении памяти PHP в 32 МБ. Пытался сохранить данные и отключить переменную, когда память была использована максимально, но не помогло. Любое предложение или помощь могут быть очень полезны

Код: Выделить всё

public function buildExcel(array $metaData): string
{
// Generate one spreadsheet for all languages

$metaData['numericVersion'] = $this->getNumericVersion($metaData['ddqVersion']);
$metaData['skipped'] = 0;
$this->metaData = $metaData;
$workingPath = $this->getExportPath();
$xlsPath = $workingPath . '/' . $metaData['exportFilename'];
$csvPath = $workingPath . '/' . $metaData['skippedFilename'];
if (is_writable($xlsPath)) {
unlink($xlsPath);
}
if (is_writable($csvPath)) {
unlink($csvPath);
}

// TODO: when phpoffice/phpexcel is replaced by phpoffice/phpspreadsheet this code must be updated

// Document properties
$userName = $this->getAuthUserName();
$this->excel = new Spreadsheet();

$this->excel->getProperties()
->setCreator($userName)
->setLastModifiedBy($userName)
->setTitle(substr($metaData['exportFilename'], 0, -5))
->setDescription('Export form ');

// Determine if PSACompany is defined
$where = [
'caseType' => $metaData['ddqType'],
'ddqQuestionVer' => $metaData['ddqVersion'],
'languageCode' => 'EN_US',
'questionID' => 'TEXT_CLICKHERE_ADDCOMPANY',
];
$this->hasPSACompany = (bool)$this->onlineQuestions->selectValue('id', $where);

// Determine if KeyPersonnel is defined
$where['questionID'] = 'TEXT_CLICKHERE_BUTTON';
$where['pageTab'] = 'Personnel';
$this->hasKeyPersonnel = (bool)$this->onlineQuestions->selectValue('id', $where);
unset($where);

// Ensure client has YesNo list in customSelectList
$this->ensureYesNoListExists();

// Initialize all sheets and their headings
foreach ($this->sheetIndex as $sheet => $title) {
if ($sheet) {
$this->excel->addSheet(new Worksheet($this->excel)); // add another sheet after the first
}
$this->excel->setActiveSheetIndex($sheet)->setTitle($title);
foreach ($this->sheetHeadings[$sheet] as $heading => $columnRef) {
$cellRef = $columnRef . $this->sheetRow[$sheet];
$this->excel->getActiveSheet()->setCellValue($cellRef, $heading);
}
$this->sheetRow[$sheet]++; // move to next row
}

// Define form and version - applies to all languages
$this->defineForm();
$this->defineVersion();

// loop through languages, parse onlineQuestions for each language onto remaining sheets
$languages = array_merge(['EN_US'], $metaData['otherLanguages']);
foreach ($languages as $language) {
$this->metaData['language'] = $language;
if (!($fwLanguageCode = $this->lookupFwLanguageCode($language))) {
continue;  // skip unsupported language code
}
$this->metaData['languageShort'] = $fwLanguageCode;

$this->parseQuestions();
}

// Remove unused sheets
if ($this->dependencyCount === 0) {
$this->excel->removeSheetByIndex(self::DEPEND_SHEET);
}
if ($this->gridCount === 0) {
$this->excel->removeSheetByIndex(self::GRID_SHEET);
}

// Set to first sheet before saving
$this->excel->setActiveSheetIndex(self:: DEFINE_SHEET);
$excelWriter = new Xlsx($this->excel);
touch($xlsPath);
chmod($xlsPath, 0644);
$excelWriter->save($xlsPath);
$this->excel->disconnectWorksheets(); // disconnect all worksheets
unset($this->excel);

// Unset the $spreadsheet object
unset($spreadsheet);
// Log memory usage
if ($this->debug) {
Xtra::track('Current memory usage: ' . (memory_get_usage(true) / 1024 / 1024) .  " MB");
}

return $xlsPath;
}

private function parseQuestions(): void
{
$metaData = $this->metaData;
$result = $this->getOnlineQuestionsIdListChunk();
$params = [
':cid' => $this->clientID,
':type' => $metaData['type'],
':ver' => $metaData['version'],
':lang' => $metaData['language'],
':types' => implode(',', $result['controlTypes']),
];
$chunker = new ChunkResults($this->DB, $result['sql'], $params);
while($idList = $chunker->getRecord()) {
if ($this->missingCompanySubDivision($metaData['type'], $metaData['version'], $metaData['language'])) {
// tack on the a fake id for the fake companySubDivision element
$elementID = self::FAKE_SUBDIVISION_ID;
}
$elementID = $idList['id'];
unset($idList);
$cannotDelete = ['name', 'subByName', 'subByEmail'];

// Item sheet - all elements
$this->excel->setActiveSheetIndex(self::ITEM_SHEET);

// Iterate through onlineQuestions records

if ($elementID !== self::FAKE_SUBDIVISION_ID) {
$element = $this->getFormElement($elementID);
} else {
$element = $this->fakeMissingCompanySubDivision();
}

// Handle page headers - TEXT_*_TAB, KeyPersonnel and PSACompany elements
if (array_key_exists($element['questionID'], $this->pageHeaders)) {
$pageHeader = $this->pageHeaders[$element['questionID']];
$this->setItemProperties([['identifier' => $pageHeader, 'name' => 'Notes', 'value' => false]]);
if ($this->hasKeyPersonnel && $element['questionID'] === 'TEXT_PERSONNEL_TAB') {
$this->addKeyPersonnel();
} elseif ($this->hasPSACompany &&  $element['questionID'] === 'TEXT_RELATION_TAB') {
$this->addPSACompany();
}
continue;
}

if (!($mappedControlType = $this->mapControlType($element))) {
// Detect unsupported controlType and skip element
$this->skipElement($element);
continue;
}

// ParentType - QuestionnaireVersion
$this->setCellString('ParentType', $this->itemOverrides['ParentType'] ?? 'Questionnaire');

// ParentIdentifier - numeric ddqVersion
$this->setCellInt(
'ParentIdentifier',
$this->itemOverrides['ParentIdentifier'] ?? $metaData['numericVersion']
);

// Name - questionID
$this->setCellString('Name', $this->itemOverrides['Name'] ?? $element['questionID']);

// Text - labelText
$indent = false;
$labelText = $this->removeLeadingNbsp($element['labelText']);
$indent = $labelText !== $element['labelText'] || $element['controlType'] === 'tarbYes';
$this->setCellString('Text', $this->itemOverrides['Text'] ?? $labelText);

// Type - controlType
$this->setCellString('Type', $this->itemOverrides['Type'] ?? $mappedControlType);

// Order - tabOrder
$this->setCellInt('Order', $element['tabOrder']);

// Indent - True, if tarbYes or labelText started with  
$this->setCellBool('Indent', $indent);

// HasSeparator - false
$this->setCellBool('HasSeparator', false);

// ValidationMessage - errorMsg
$this->setCellString(
'ValidationMessage',
$this->removeLeadingNbsp($element['errorMsg'])
);

// Required - qStatus === 1
$this->setCellBool('Required', $element['qStatus'] === 1);

// ParentName
$this->setCellString('ParentName', $this->itemOverrides['ParentName'] ?? '');

// ParentValue
$this->setCellString('ParentValue', $this->itemOverrides['ParentValue'] ?? '');

// OptionsRefDomain
$this->setCellString('OptionsRefDomain', $this->itemOverrides['OptionsRefDomain'] ?? '');

// Language
$this->setCellString('Language', $metaData['languageShort']);

// GenericRefCodeName - usually blank
$this->setCellString('GenericRefCodeName', $this->itemOverrides['GenericRefCodeName'] ?? '');

// RefCodeName - questionID
$this->setCellString('RefCodeName', $this->itemOverrides['RefCodeName'] ?? $element['questionID']);

// CanBeDeleted
$this->setCellBool('CanBeDeleted', !in_array($element['questionID'], $cannotDelete));

// RefCodeDomain - pageTab
$this->setCellString(
'RefCodeDomain',
$this->itemOverrides['RefCodeDomain'] ?? $this->mapPageTab($element)
);

// RefCodeAgency
$this->setCellInt('RefCodeAgency', 0);

// Update sheet row pointers to next row
$this->sheetRow[self::ITEM_SHEET]++;
unset($element);
}
unset($metaData);
unset($result);
unset($params);
unset($chunker);
}

Я пытался освободить память и отключить настройку ($this->excel), когда израсходовано 31 МБ памяти, но это не сработало.
Примечание. Выделенный PHP_MEMORY_LIMIT составляет 32 МБ и не может быть превышен
п>

Подробнее здесь: https://stackoverflow.com/questions/781 ... phpspreads