How can I reduce the memory consumption when executing this script?

Question

CodeIgniter

I wrote a script that collects articles from a mysql database for a certain period and inserts them as an MS Excel file. For this, the PHPExcel library is used. Site on Codeigniter.
The problem is that when there are several thousand articles, the script is not executed because the memory is running out libraries/PHPExcel/Worksheet.php on line 1230, referer: http://****). Tell me how you can reduce memory consumption. Below is the script itself. A request is made to the database. The resulting array is parsed through foreach. At the end, all existing files are removed from the directory and a new file is created.

$this->load->library('PHPExcel');
    $phpexcel = new PHPExcel();
    $page = $phpexcel->setActiveSheetIndex(0);
    
    $page->setCellValue("A1", "data =  ");
    $page->setCellValue("B1", date("d/m/Y h:i", time()));
    
    $page->setCellValue("A2", "id");
    $page->setCellValue("B2", "источник");
    $page->setCellValue("C2", "опубликован");
    $page->setCellValue("D2", "автор");
    $page->setCellValue("E2", "заголовок");
    $page->setCellValue("F2", "текст");
    $page->setCellValue("G2", "Цена");
    $page->setCellValue("H2", "Риелтор");
    $page->setCellValue("I2", "Коллега");
    $page->setCellValue("J2", "категория недв.");
    $page->setCellValue("K2", "цель предложения");
    $page->setCellValue("L2", "комнат");
    $page->setCellValue("M2", "Населенный пункт");
    $page->setCellValue("N2", "Сектор");
    $page->setCellValue("O2", "Улица");
    $page->setCellValue("P2", "Общая площадь");
    $page->setCellValue("Q2", "Жилая площадь");
    $page->setCellValue("R2", "Этаж");
    $page->setCellValue("S2", "Контакты");
    $page->setCellValue("T2", "Ссылка");
    $page->setCellValue("U2", "Планировка");
    $page->setCellValue("V2", "Выс. потолков");
    $page->setCellValue("W2", "Состояние");
    $page->setCellValue("X2", "Туалет");
    $page->setCellValue("Y2", "Отопление");
    
    $this->load->model('model_admin');
    $table = $this->model_parser->get_table('anunturi_importate',$time);
    $i=3;
    $styleArray = array(
      'borders' => array(
        'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => '00000000'),
          ),
        ),
    );
    foreach ($table->result() as $row)
      {  
      $page->setCellValue("A".$i, $row->id_anunturi_importate);

      $page->setCellValue("B".$i, $row->sursa);
      $page->setCellValue("C".$i, date("d/m/Y h:i", $row->data_importare));
      
      
      $page->setCellValue("D".$i, $row->autor);
      
      $page->setCellValue("E".$i, $row->titlu);
      $page->getStyle("E".$i)->getAlignment()->setWrapText(true);

      $page->setCellValue("F".$i, $row->descriere);
      $page->getStyle("F".$i)->getAlignment()->setWrapText(true);
      
      $price = $row->pret .' '.$row->valuta;
      $page->setCellValue("G".$i, $price);
      
      $tip = '';
      if($row->e_rieltor=='0') $tip = 'не риелтор'; else if ($row->e_rieltor=='1') $tip = 'риелтор';
      $page->setCellValue("H".$i, $tip);
      
      $tip = '';
      if($row->autor_coleg == '0') $tip = 'не коллега'; else if ($row->autor_coleg =='1') $tip = 'коллега';
      $page->setCellValue("I".$i, $tip);
      
      $page->setCellValue("J".$i, $row->categoria);
      
      $page->setCellValue("K".$i, $row->scop_oferta);
      
      $page->setCellValue("L".$i, $row->numar_camere);
      
      $page->setCellValue("M".$i, $row->localitate);
      
      $page->setCellValue("N".$i, $row->sector);
      $page->getStyle("N".$i)->getAlignment()->setWrapText(true);
      $page->getColumnDimension('K')->setWidth(15);
      
      $adr = $row->strada . ' '.$row->nr_casa;
      $page->setCellValue("O".$i, $adr);
      $page->getStyle("O".$i)->getAlignment()->setWrapText(true);
      $page->getColumnDimension('O')->setWidth(20);
      
      $page->setCellValue("P".$i, $row->s_totala);
      
      $page->setCellValue("Q".$i, $row->s_locativa);
      
      $eta = $row->etaj .'/'. $row->total_etaje;
      $page->setCellValue("R".$i, $eta);
      
      $page->setCellValue("S".$i, $row->contacte);
      $page->getStyle("S".$i)->getAlignment()->setWrapText(true);
      
      $page->setCellValue("T".$i, $row->url_sursa);
      $page->getStyle("T".$i)->getAlignment()->setWrapText(true);
      
      $page->setCellValue("U".$i, $row->planul);
      
      $page->setCellValue("V".$i, $row->pod);
      
      $page->setCellValue("W".$i, $row->stare);
      
      $page->setCellValue("X".$i, $row->wc);
      
      $page->setCellValue("Y".$i, $row->incalzire);
      
      $page->setCellValue("Z2", "Парковка");
      $page->setCellValue("Z".$i, $row->parcare);
      
      $page->setCellValue("AA2", "Балкон");
      $page->setCellValue("AA".$i, $row->balcon);
      
      $page->setCellValue("AB2", "Материа стен");
      $page->setCellValue("AB".$i, $row->tip_cladire);
      
      $page->setCellValue("AC2", "Мансарда");
      $page->setCellValue("AC".$i, $row->mansarda);
      
      $page->setCellValue("AD2", "Площадь земли");
      $page->setCellValue("AD".$i, $row->s_teren);
      
      $page->setCellValue("AE2", "Дополнительно");
      $page->setCellValue("AE".$i, $row->atribute);
      $page->getStyle("AE".$i)->getAlignment()->setWrapText(true);
      
      $page->setCellValue("AF2", "Площадь кухни");
      $page->setCellValue("AF".$i, $row->bucataria);
      

      
      $page->getRowDimension($i)->setRowHeight(-1);
      $page->getColumnDimension('E')->setWidth(40);
      $page->getColumnDimension('F')->setWidth(50);
      $page->getColumnDimension('C')->setWidth(20);
      $page->getColumnDimension('G')->setWidth(15);
      $page->getColumnDimension('H')->setWidth(15);
      $page->getColumnDimension('I')->setWidth(15);
      $page->getColumnDimension('J')->setWidth(20);
      $page->getColumnDimension('K')->setWidth(20);
      
      $page->getStyle('A'.$i.':AF'.$i)->applyFromArray($styleArray);
      $i++;
      
      unset($row);
      }
      $page->getStyle('A2:AF'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      $page->getStyle('A2:E'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $page->getStyle('G2:AF'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $page->getStyle('E2:F'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    
    $filename = 'Anunturi_imp-'.date('d.m.Y-h:i', time()).'.xlsx';
    $page->setTitle("Anunturi");
    $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel2007');
    
    //stergem toate fisierele deja create
    $files = glob('uploads/export_excel/*'); // get all file names
    foreach($files as $file){ // iterate files
      if(is_file($file))
      unlink($file); // delete file
    }
    //salvam fisierul
    $objWriter->save("uploads/export_excel/".$filename);	
      
    return $filename;

Answer 1

To save in XML, clear Excel'yu. Do all the necessary formatting on the sample Excel sheet, save it in XML (see the list of available formats) and save the data according to the same template (several headings and some styles are fundamental for the correct display of such self-made XML in Excel). If we improve this look a little, then there is already not far from the native XLSX format for Office 2007+.
I use this approach in the corporate report generator, there were no complaints about compatibility. At first, I also used PHPExcel, but abandoned it already when forming XLS for 300-400 lines due to a sharp drop in performance.
PS: of course, all Cyrillic must be converted to UTF-8.
PPS: small example

<?php
# ....
print("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>");
?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <Colors>
   <Color>
    <Index>2</Index>
    <RGB>#E8FDE8</RGB>
   </Color>
   <Color>
    <Index>3</Index>
    <RGB>#FDFDE8</RGB>
   </Color>
   <Color>
    <Index>4</Index>
    <RGB>#FDE8E8</RGB>
   </Color>
  </Colors>
 </OfficeDocumentSettings>
 <Styles>
  <Style ss:ID="s16">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Font ss:Size="12" ss:Color="#000000" ss:Bold="1"/>
   <Interior/>
   <Protection/>
  </Style>
  <Style ss:ID="s17">
   <Alignment ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
   </Borders>
   <Font ss:Color="#000000"/>
   <Interior ss:Color="#E8FDE8" ss:Pattern="Solid"/>
   <Protection/>
  </Style>
  <Style ss:ID="s18">
   <Alignment ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
   </Borders>
   <Font ss:Color="#000000"/>
   <Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
   <Protection/>
  </Style>
  <Style ss:ID="s19">
   <Alignment ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
   </Borders>
   <Font ss:Color="#000000"/>
   <Interior ss:Color="#FDFDE8" ss:Pattern="Solid"/>
   <Protection/>
  </Style>
  <Style ss:ID="s20">
   <Alignment ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
   </Borders>
   <Font ss:Color="#000000"/>
   <Interior ss:Color="#FDE8E8" ss:Pattern="Solid"/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Пример">
  <Table>
   <Column ss:Width="210"/>
   <Column ss:Width="64"/>
   <Column ss:Width="80"/>
   <Column ss:Width="210"/>
   <Column ss:Width="270"/>
   <Row ss:Height="15.75">
    <Cell ss:StyleID="s16"><Data ss:Type="String">Столбец 1</Data></Cell>
    <Cell ss:StyleID="s16"><Data ss:Type="String">Столбец 2</Data></Cell>
    <Cell ss:StyleID="s16"><Data ss:Type="String">Столбец 3</Data></Cell>
    <Cell ss:StyleID="s16"><Data ss:Type="String">Столбец 4</Data></Cell>
    <Cell ss:StyleID="s16"><Data ss:Type="String">Столбец 5</Data></Cell>
   </Row>
<?
  while ($row = mysqli_fetch_assoc($res)) {
?>
   <Row ss:Height="15.75">
    <Cell ss:StyleID="s18"><Data ss:Type="String"><?=$row['str1']?></Data></Cell>
    <Cell ss:StyleID="s18"><Data ss:Type="String"><?=$row['str2']?></Data></Cell>
    <Cell ss:StyleID="s18"><Data ss:Type="String"><?=$row['str3']?></Data></Cell>
    <Cell ss:StyleID="s18"><Data ss:Type="String"><?=$row['str4']?></Data></Cell>
    <Cell ss:StyleID="s18"><Data ss:Type="String"><?=$row['str5']?></Data></Cell>
   </Row>
<?
  }
?>
  </Table>
 </Worksheet>
</Workbook>
<?

Answer 2

PHPExcel itself is not weak.
You do not have special styling, is it not easier to unload in csv, and then run Excel on the server side (with a template or macros) to generate the desired view?
I did this for a long time, until I switched completely to Linux.
I had to write my own library.

Answer 3

Save to CSV and then transfer to excel with some console utility.
For example
manpages.ubuntu.com/manpages/lucid/man1/ssconvert....
ask.libreoffice.org/en/question/10411/convert-csv-...

Answer 4

You can write output in XML and packaging in xlsx yourself, or you can use ready-made libraries like
https://github.com/mk-j/PHP_XLSXWriter