title: PHPExcel memory still exhausted even with cell caching - other solutions
body: <pre><code>&lt;b&gt;Fatal error&lt;/b&gt;:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 78 bytes) in &lt;b&gt;/var/www/leanne/api/classes/PHPExcel/CachedObjectStorage/PHPTemp.php&lt;/b&gt; on line &lt;b&gt;66&lt;/b&gt;&lt;br /&gt;
  </code></pre>
  
  <p>Hi,  </p>
  
  <p>I asked <a href="https://stackoverflow.com/questions/16586694/phpexcel-how-to-write-to-file-in-chunks">this question</a> a few days ago and was advised to change my code and to use cell caching. While I have changed my code and attempted to use cell caching, I am still getting a memory error. I am desperate to find a solution to this. </p>
  
  <p>Can anyone advise on which caching method would be best for writing excel files ranging between 1 to 100,000 rows of data?  If cell caching doesn't work, I may need to use another solution that allows me to append to an xls file in the same way I do with the CSV version.</p>
  
  <p>An example of my current code is below:</p>
  
  <pre><code>if ($count_prods &gt; 0) {
  
      $format = strtolower($export_data['output']);
      $temp_file_location = '../temp/exports/products/';
      $filename = 'data_' + $shop-&gt;ID . '_' . $export_id . '_test';
      $separator = ',';
      $endrow = "\n";
  
      $fh = fopen($temp_file_location . $filename . '.csv', 'a');
  
      /*$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
      $cacheSettings = array( ' memoryCacheSize ' =&gt; '8MB');
      PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);*/
  
      $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_sqlite;
      PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
  
      $objPHPExcel = new PHPExcel();
  
      $rowID = 2;
      $counter = 1;
      for ($i = 0; $i &lt; $count_prods; $i += $batchlimit) {
          $csv = '';
          $limit = $batchlimit * $counter;
          $start = $i + 1;
          $productData = $productExport-&gt;getProductData($start, $limit);
  
          if ($counter == 1) {
              //get column names
              if ($format == 'csv') {
                  $column_titles = implode(',', $productExport-&gt;product_fields);
                  $column_no = count($column_titles);
                  $csv = $column_titles . $endrow;
              } else {
                  $objPHPExcel-&gt;getActiveSheet()-&gt;fromArray($productExport-&gt;product_fields, NULL, 'A1');
              }
          }
  
          //loop through data export array
          foreach ($productData as $product_id =&gt; $product_details) {
              $columnID = 'A';
              foreach ($product_details as $key =&gt; $value) {
                  if ($format == 'csv') {
                      $csv .= '"' . str_replace('"', '\'', $product_details[$key]) . '"' . $separator;
                  } else {
                      $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($columnID . $rowID, $product_details[$key]);
                  }
                  $columnID++;
              }
              if ($format == 'csv') {
                  $csv = rtrim($csv, $separator);
                  $csv .= $endrow;
              }
              $rowID++;
          }
          if ($format == 'csv') {
              fwrite($fh, $csv);
              $csv = '';
          }
  
          $counter++;
      }
      if ($format == 'csv') {
          fclose($fh);
      }
  
      //if  XLS file 
      if ($format == 'xls') {
          //$objPHPExcel = $objReader-&gt;load($temp_file_location . $filename . '.csv');
          // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
          //$objWriter-&gt;save($temp_file_location . $filename . '.xls');
          $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
          $objWriter-&gt;save($temp_file_location . $filename . '.xlsx');
      }
  </code></pre>