Asked  5 Months ago    Answers:  5   Viewed   279 times

I am using phpexcel to write quite a large excel file.

I am writing it from an array that looks like

array(
  [0] => stdClass Object
        (
            [sent] => Mar 31st, 2011 02:10PM
            [to_number] => 64211111111
            [additional_txt] => 
            [misc_data] => 
        )
  ...
  [14058] => stdClass Object
        (
            [sent] => Mar 31st, 2011 02:10PM
            [to_number] => 64211111111
            [additional_txt] => 
            [misc_data] => 
        )
)

The loop is

$r = 0;
foreach ($replies_obj as $row) {
  $c = 'A';
  foreach ($row as $col)
    $xlsx->getActiveSheet()->setCellValue($c++ . $r, $col);
  $r++;
}

and the error is

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 105 bytes) in /home/webspace/xxxx/test/htdocs/application/libraries/PHPExcel/Worksheet.php on line 961

Is this a phpexcel problem or am I doing something wrong?

How can I fix this?

 Answers

13

There's a lot been written about PHPExcel and memory use, and I'm not going to repeat it all here.

Try reading some of the threads on the PHPExcel discussion board discussing the issue, such as this one; or previous answers here on SO such as this one or this one

Tuesday, July 6, 2021
 
Slinky
answered 5 Months ago
32

As another point to note, you're building the PHPExcel object by looping through an array called $data.... a 2d array, that I'm guessing is built from looping through the results of a database query.

That $data array is also going to be using a large part of your memory, more and more memory each day as the number of results grows.

It would be more efficient if, instead of looping through the database resultset to build a large array and then looping through that array to build the PHPExcel data, you looped through the database resultset and built the PHPExcel data directly. That eliminates the memory overhead of $data, and reduces 2 loops to 1.

Wednesday, March 31, 2021
 
cyber_truite
answered 9 Months ago
46

No, PHP doesn't implement this like a C style array. Php arrays are associative containers, as the php article on arrays states.

An array in PHP is actually an ordered map. A map is a type that associates values to keys.

Since order is preserved, the array will likely be some kind of binary search tree. If you're unfamiliar with binary search trees I suggest picking up a good data structures book to learn more or check out this wikipedia article for a rundown. Your example above would yield a binary search tree with two nodes -- one for data at key 6000, the other for key 7891.

Saturday, May 29, 2021
 
Palladium
answered 7 Months ago
43

Here are three methods to increase the limit on shared hosting:

  1. Just add this below line to before line of you getting error in your file

     ini_set('memory_limit', '-1');
    
  2. If you have access to your PHP.ini file, change the line in PHP.ini If your line shows 32M try 64M: memory_limit = 64M ; Maximum amount of memory a script may consume (64MB)

  3. If you don't have access to PHP.ini try adding this to an .htaccess file: php_value memory_limit 64M

Also make sure your file pointer is valid, and pass "r" (= reading) for mode.

Saturday, May 29, 2021
 
THEK
answered 7 Months ago
77

If you want a real indexed array, use SplFixedArray. It uses less memory. Also, PHP 5.3 has a much better garbage collector.

Other than that, well, PHP will use more memory than a more carefully written C/C++ equivalent.

Memory Usage for 1024x1024 integer array:

  • Standard array: 218,756,848
  • SplFixedArray: 92,914,208

as measured by memory_get_peak_usage()

$array = new SplFixedArray(1024 * 1024); // array();
for ($i = 0; $i < 1024 * 1024; ++$i)
  $array[$i] = 0;

echo memory_get_peak_usage();

Note that the same array in C using 64-bit integers would be 8M.

As others have suggested, you could pack the data into a string. This is slower but much more memory efficient. If using 8 bit values it's super easy:

$x = str_repeat(chr(0), 1024*1024);
$x[$i] = chr($v & 0xff); // store value $v into $x[$i]
$v = ord($x[$i]);        // get value $v from $x[$i]

Here the memory will only be about 1.5MB (that is, when considering the entire overhead of PHP with just this integer string array).

For the fun of it, I created a simple benchmark of creating 1024x1024 8-bit integers and then looping through them once. The packed versions all used ArrayAccess so that the user code looked the same.

                   mem    write   read
array              218M   0.589s  0.176s
packed array       32.7M  1.85s   1.13s
packed spl array   13.8M  1.91s   1.18s
packed string      1.72M  1.11s   1.08s

The packed arrays used native 64-bit integers (only packing 7 bytes to avoid dealing with signed data) and the packed string used ord and chr. Obviously implementation details and computer specs will affect things a bit, but I would expect you to get similar results.

So while the array was 6x faster it also used 125x the memory as the next best alternative: packed strings. Obviously the speed is irrelevant if you are running out of memory. (When I used packed strings directly without an ArrayAccess class they were only 3x slower than native arrays.)

In short, to summarize, I would use something other than pure PHP to process this data if speed is of any concern.

Saturday, July 10, 2021
 
Indranil
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share