PhpExcel stops working after setting 20 cell types

Question

I have a script that generates a little xls table (~25x15). It contains percentages and strings and i have an if operator that sets the current cell as percentage type with this code:

$this->objPHPExcel->getActiveSheet()->getStyle($coords)->getNumberFormat()->setFormatCode('0.00%');

But when i export and look at the file I see it only managed to set type and style about 20 cells. And all the rest are with default settings. I debugged it and realized the problem isn't in my logic. I read about increasing php cache memory - tried it but it didn't work. Please help because i need to export at least 15 times larger table. Thanks in advance!

Answer 1

PHPExcel allocates quite some memory

While PHPExcel is a beautiful library, using it may require huge amounts of memory allocated to PHP.

According to this thread, just 5 cells may render 6 MByte of memory usage:

<?php
require_once 'php/PHPExcelSVN/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("php/tinytest.xlsx");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('D2', 50);
echo $objPHPExcel->getActiveSheet()->getCell('D8')->getCalculatedValue() . "
";
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
?>

I get 6MB of memory usage.

Another user even failed with a 256MByte memory setting.

While PHPExcel provides ways to reduce its memory footprint, all reductions turned out to be too small in my case. This page on github provides details of PHPExcel's cache management options. For example, this setting serializes and the GZIPs the cell-structure of a worksheet:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

PHPExcel's FAQ explains this:

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

PHPExcel 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 memorylimit directive in your php.ini file, or by using iniset('memory_limit', '128M') within your code (ISP permitting);

Some Readers and Writers are faster than others, and they also use differing amounts of memory. You can find some indication of the relative performance and memory usage for the different Readers and Writers, over the different versions of PHPExcel, here http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=234150

If you've already increased memory to a maximum, or can't change your memory limit, then this discussion on the board describes some of the methods that can be applied to reduce the memory usage of your scripts using PHPExcel http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=242712

Measurement results for PHP Excel

I instrumented the PHPExcel example file [01simple.php][5] and did some quick testing.

Consume 92 KByte:

for( $n=0; $n<200; $n++ ) {
    $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $n, 'Miscellaneous glyphs');
}

Consumes 4164 KBytes:

for( $n=0; $n<200; $n++ ) {
    $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $n, 'Miscellaneous glyphs');
    $objPHPExcel->getActiveSheet()->getStyle('A' . $n)->getAlignment()->setWrapText(true);
}

If one executes this fragment several times unchanged, each fragment consumes around 4 MBytes.

Checking your app is logically correct

To ensure, that your app is logically correct, I'd propose to increase PHP memory first:

ini_set('memory_limit', '32M');

In my case, I have to export to export result data of an online assessment application. While there are less than 100 cells horizontally, I need to export up to several 10.000 rows. While the amount of cells was big, each of my cells holds a number or a string of 3 characters - no formulas, no styles.

In case of strong memory restrictions or large spreadsheets

In my case, none of the cache options reduced the amount as much as required. Plus, the runtime of the application grew enormously.

Finally I had to switch over to old fashioned CSV-data file exports.

Answer 2

I ran your code locally and found that all the 26 cells you set to this percentage had the right format and a % sign. I had to uncomment lines 136-137 first, of course.

This must be related to your setup. I cannot imagine you'd have too little memory for a spreadsheet of this size.

For your information, I confirmed it worked on PHP Version 5.4.16 with php excel version version 1.7.6, 2011-02-27. I opened the spreadsheet with MS Excel 2007.

Answer 3

<?php
$file = 'output_log.txt';

function get_owner($file)
{
    $stat = stat($file);
    $user = posix_getpwuid($stat['uid']);
    return $user['name'];
}

$format = "UID @ %s: %s\n";

printf($format, date('r'), get_owner($file));

chown($file, 'ross');
printf($format, date('r'), get_owner($file));

clearstatcache();
printf($format, date('r'), get_owner($file));
?>

clearstatcache(); can be useful. Load this function at the start of the php page.