How to fix memory getting exhausted with PHPExcel?

by

File size isn’t a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP – I’ll assume 32-bit PHP for the moment – so (for example) a worksheet of 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB.

There are a number of options available to help you reduce the memory usage:

Are you using cell caching with PHPExcel?

require_once './Classes/PHPExcel.php';

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access data in your worksheets, and don’t need access to the cell formatting, then you can disable reading the formatting information from the workbook:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access some, but not all of the worksheets in the workbook, you can load only those worksheets:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") );
$objPHPExcel = $objReader->load("test.xlsx");

if you only want to read certain cells within worksheets, you can add a filter:

class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
    public function readCell($column, $row, $worksheetName="") {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }

        return false;
    }
}

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("test.xlsx");

All of these techniques can significantly reduce the memory requirements.

More Related Contents:

  1. PHPExcel — Color part of XLSX table
  2. Alternative for PHP_excel
  3. Allowed memory size of 33554432 bytes exhausted (tried to allocate 43148176 bytes) in php [duplicate]
  4. PHPExcel runs out of 256, 512 and also 1024MB of RAM
  5. Manipulate a string that is 30 million characters long
  6. Allowed memory size of X bytes exhausted
  7. phpexcel to download
  8. Is there garbage collection in PHP?
  9. PHPExcel auto size column width
  10. Tracking Memory Usage in PHP
  11. PHPExcel download using ajax call
  12. Ignore empty cells PHPExcel
  13. How to use phpexcel to read data and insert into database?
  14. How to read large worksheets from large Excel files (27MB+) with PHPExcel?
  15. memory_get_peak_usage() with “real usage”
  16. Correct format for strings / numbers beginning with zero?
  17. phpexcel Allowed memory size of 134217728 bytes exhausted [duplicate]
  18. Scrollable PHP Table [closed]
  19. PHP create new array by value
  20. Get first and second element of array [duplicate]
  21. How do the PHP equality (== double equals) and identity (=== triple equals) comparison operators differ?
  22. mysqli::query(): Couldn’t fetch mysqli
  23. Converting an integer to a string in PHP
  24. PhpMailer vs. SwiftMailer? [closed]
  25. Create programmatically a variable product and two new attributes in WooCommerce
  26. Will enabling XDebug on a production server make PHP slower?
  27. “No such file or directory” or “No such host is known” when running migrations
  28. How to call a closure that is a class variable?
  29. Eloquent model mass update
  30. Changing a global variable from inside a function PHP

Leave a Comment