Fatal error allowed memory size of phpexcel

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.

So i found interesting solution here How to read large worksheets from large Excel files (27MB+) with PHPExcel?

as Addendum 3 in question

edit1: also with this solution, i came to chokepoint with my favourite errr message, but i found something about caching, so i implemented this

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

recently i tested it only for xls files lesser than 10MB, but it seems like to work (also i set $objReader->setReadDataOnly(true);) and it seems like balanced enough to achieve speed and memory consumption. (i will follow my thorny path more, if its possible)

edit2:
So i made some further research and found chunk reader unnecessary in my way. (seems like to me, memory issue is same with chunk reader and without it.) So my final answer to my question is something like that, which reads .xls file (only data from cells, without formating, even filtering out formulas). When i use cache_tp_php_temp im able to read xls files (tested to 10MB) and about 10k rows and multiple columns in matter of seconds and without memory issue

function parseXLS($fileName){

/** PHPExcel_IOFactory */
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel/IOFactory.php';
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel/ChunkReadFilter.php';
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel.php';

    $inputFileName = $fileName;
    $fileContent = "";

    //get inputFileType (most of time Excel5)
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);

    //initialize cache, so the phpExcel will not throw memory overflow
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array(' memoryCacheSize ' => '8MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    //initialize object reader by file type
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);

    //read only data (without formating) for memory and time performance
    $objReader->setReadDataOnly(true);

    //load file into PHPExcel object
    $objPHPExcel = $objReader->load($inputFileName);

    //get worksheetIterator, so we can loop sheets in workbook
    $worksheetIterator = $objPHPExcel->getWorksheetIterator();

    //loop all sheets
    foreach ($worksheetIterator as $worksheet) {    

            //use worksheet rowIterator, to get content of each row
            foreach ($worksheet->getRowIterator() as $row) {
                //use cell iterator, to get content of each cell in row
                $cellIterator = $row->getCellIterator();
                //dunno
                $cellIterator->setIterateOnlyExistingCells(false);      

                //iterate each cell
                foreach ($cellIterator as $cell) {
                    //check if cell exists
                    if (!is_null($cell)) {
                        //get raw value (without formating, and all unnecessary trash)
                        $rawValue = $cell->getValue();
                        //if cell isnt empty, print its value
                        if ((trim($rawValue) <> "") and (substr(trim($rawValue),0,1) <> "=")){
                            $fileContent .= $rawValue . " ";                                            
                        }
                    }
                }       
            }       
    }

    return $fileContent;
}

Reading .xls file via PHPExcel throws Fatal error: allowed memory size. even with chunk reader

im using PHPExcel to read .xls files. I quite a short time i meet

after some googling, i tried chunkReader to prevent this (mentioned even on PHPExcel homesite), but im still stucked with this error.

My thought is, that via chunk reader, i will read file part by part and my memory wont overflow. But there must be some serious memoryleak? Or im freeing some memory bad? I even tried to raise server ram to 1GB. File size, which i trying to read is about 700k, which is not so much (im also reading

20MB pdf, xlsx, docx, doc, etc files without issue). So i assume there can be just some minor troll i overlooked.

Code looks like this

And here is code for chunkReader

3 Answers 3

as Addendum 3 in question

edit1: also with this solution, i came to chokepoint with my favourite errr message, but i found something about caching, so i implemented this

recently i tested it only for xls files lesser than 10MB, but it seems like to work (also i set $objReader->setReadDataOnly(true); ) and it seems like balanced enough to achieve speed and memory consumption. (i will follow my thorny path more, if its possible)

edit2: So i made some further research and found chunk reader unnecessary in my way. (seems like to me, memory issue is same with chunk reader and without it.) So my final answer to my question is something like that, which reads .xls file (only data from cells, without formating, even filtering out formulas). When i use cache_tp_php_temp im able to read xls files (tested to 10MB) and about 10k rows and multiple columns in matter of seconds and without memory issue

Источник

PHPExcel throws Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 71 bytes)

I am using PHPExcel (found here: https://github.com/PHPOffice/PHPExcel). If i try to read more than approximately 2000 rows then it shows memory error as follows.

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 71 bytes) in /home/sample/PHPExcelReader/Classes/PHPExcel/worksheet.php on line 89

My Excel data range is A1:X2000

Below is my code used to read the excel.

Could any one please let me know how to overcome this issue ?

2 Answers 2

Consider using cell caching to reduce the memory required to hold the workbook in memory, as described in section 4.2.1 of the developer documentation

And consider not using toArray() and then using that to build another array in memory. doing this is really using a lot of memory to hold duplicated data, when you could simply loop through the rows and columns of the worksheet to do what you need

This error means that the PHP file that you are running has exceeded the allowed size in memory for PHP on your server. You can edit your PHP.ini file to allow your PHP files to allocate more space in memory when they are running, which may assist in this, but at the same time, if you are running a 32 bit Linux OS on your server for whatever reason, there is a hard cape of 3.5GB that the process can take up, so even allocating more than that, it will still fail and therefore cause a similar issue.

In cases such as this, it really comes down to the fact that the amount of data that you are trying to pull is too large and you need to scale it back somehow. It isn’t necessarily an issue with the code, but rather how much data you are actually attempting to show/process.

Источник

How to fix memory getting exhausted with PHPExcel?

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1078799 bytes) in D:xampplitehtdocsScraperPHPExcelReaderExcel2007.php on line 269

My 128M PHP memory limit quickly gets exhausted even when I am only trying to open a small excel file of

350 KB with PHPExcel.

Although, I can increase the memory limit in the configuration but it’ll be great to see if there are any alternatives to fix this.

8 Answers 8

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?

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:

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

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

All of these techniques can significantly reduce the memory requirements.

Источник

Frequently asked questions

There seems to be a problem with character encoding.

It is necessary to use UTF-8 encoding for all texts in PhpSpreadsheet. If the script uses different encoding then you can convert those texts with PHP’s iconv() or mb_convert_encoding() functions.

Fatal error: Allowed memory size of xxx bytes exhausted (tried to allocate yyy bytes) in zzz on line aaa

PhpSpreadsheet holds an «in memory» representation of a spreadsheet, so it is susceptible to PHP’s memory limitations. The memory made available to PHP can be increased by editing the value of the memory_limit directive in your php.ini file, or by using ini_set(‘memory_limit’, ‘128M’) within your code.

Some Readers and Writers are faster than others, and they also use differing amounts of memory.

Protection on my worksheet is not working?

When you make use of any of the worksheet protection features (e.g. cell range protection, prohibiting deleting rows, . ), make sure you enable worksheet security. This can for example be done like this:

Feature X is not working with Reader_Y / Writer_Z

Not all features of PhpSpreadsheet are implemented in all of the Reader / Writer classes. This is mostly due to underlying libraries not supporting a specific feature or not having implemented a specific feature.

For example autofilter is not implemented in PEAR Spreadsheet_Excel_writer, which is the base of our Xls writer.

We are slowly building up a list of features, together with the different readers and writers that support them, in the features cross reference.

Formulas don’t seem to be calculated in Excel2003 using compatibility pack?

This is normal behaviour of the compatibility pack, Xlsx displays this correctly. Use PhpOfficePhpSpreadsheetWriterXls if you really need calculated values, or force recalculation in Excel2003.

Setting column width is not 100% accurate

Trying to set column width, I experience one problem. When I open the file in Excel, the actual width is 0.71 less than it should be.

The short answer is that PhpSpreadsheet uses a measure where padding is included. See how to set a column’s width for more details.

I cannot serialize or json_encode my Spreadsheet

No, you can’t. Consider the Spreadsheet object as a PHP resource, which cannot be serialized.

Источник