1

I've been working on a web-based application to export database information through PHP. The original version of the application originally generated a single OpenXML worksheet, but ran into the issue that beyond a certain number of rows (approximately 9500), the worksheet generated was too large to import into Excel or OpenOffice.

I subsequently reworked the app to use the php_excel wrapper for libxl, which worked adequately in test, but when deployed to the live server (which had a larger amount of data in comparison to the testing server), the memory usage of the process would hit just a little bit under 2GB and then fail, giving this error:

Call to a member function write() on a non-object in...etc etc.

Now, the interesting thing is that the code works for smaller sets of data, and if I restrict the amount of data being requested, I can collect partial data dumps from the database. From all investigation, this error occurs when the code attempts to reference an object that is not assigned. Here's the code below:

$objPHPExcel = new ExcelBook($rcn, $rcl, true);
for ($i=0;$i<$myCCount;$i++){
    $myPCount = count($mySelection[$i])
    for ($j=1;$j<$myPCount;$j++){
        $myWorkSheet = $myAccountSelection[$i][0] . ' - ' . $myAccountSelection[$i][$j];
        $thisSheet = $objPHPExcel->addSheet($myWorkSheet);
        for ($k=0;$k<count($myQueryArray);$k++){
            $thisSheet->write(0, $k, $titleList[$myQueryArray[$k]]); //Error on this line
        }
    //The rest is database queries and spreadsheet generation.

Again, I'd like to reiterate that this works for smaller sets of data (same number of rows with fewer columns, or same number of columns, but fewer rows) and will run for approximately fifteen minutes before it errors out.

According to the requirements, a full data dump will generate up to a maximum of 924 workbooks (minus workbooks where there would be no actual entries), with ~360 columns in each workbook containing a combined total of 10,000+ rows.

Can anyone help me identify what the problem actually is in this case?

Edit Update: After some logging and digging through the php_excel wrapper's error handling (such as it is), I have determined that the problem is indeed a memory allocation limit being hit - within the wrapper itself. The amount of free memory available to PHP or FastCGI or on the rest of the machine doesn't matter in this case, because once it hits a certain amount (I'm in the process of currently trying to pin down exactly what the limits are). Unless anyone can educate me as to a way to improve the amount of cells that the phpexcel wrapper can handle, I think this is closed as a "unsolvable but known problem".

user2985939
  • 19
  • 1
  • 3
  • Where exactly does the error occur? – Pekka Nov 13 '13 at 04:11
  • 1
    @Pekka웃 I'd say on line #8 in the second code snippet (where it says `//Error on this line`) – Phil Nov 13 '13 at 04:16
  • Then it's likely that `$objPHPExcel->addSheet` fails to return an object. Does PHPExcel have some sort of error handling? That would be the best place to look what goes wrong – Pekka Nov 13 '13 at 04:19
  • This type issue comes with bulk data. I also faced this issue. I used given below settings to overcome this issue ini_set( 'max_execution_time', 0 );: ini_set( 'memory_limit', -1 ); I think this will work in this situation. – Rohit Nov 13 '13 at 04:20
  • Unfortunately, this does not work. My memory limits were set very high to begin with (set to allow up to 3GB) and I'm receiving the error at a little bit under 2 GB of memory used. I certainly did try using unlimited memory and removing the execution time limits, but I'm starting to think that this is a problem with the php_excel wrapper. – user2985939 Nov 13 '13 at 06:26

1 Answers1

0

Under some conditions, the code in this line:

$thisSheet = $objPHPExcel->addSheet($myWorkSheet);

$thisSheet will be null,

change the following code to:

if ($thisSheet) {
    for ($k=0;$k<count($myQueryArray);$k++){
        $thisSheet->write(0, $k, $titleList[$myQueryArray[$k]]); //Error on this line
    }
}

to avoid the error.

The reason why $objPHPExcel->addSheet reutuns null may be that the memory usage is reach limit, you can use ini_set("memory_limit", -1); to set memory usage to unlimited.

srain
  • 8,944
  • 6
  • 30
  • 42
  • 1
    Agreed re what the problem is, but if it were a memory limit issue, the script would crash – Pekka Nov 13 '13 at 04:25
  • Unfortunately, it's not a memory usage limit issue - see above. The available memory is well in excess of what is being used at the time of the error even before setting memory usage to unlimited. Unfortunately, it looks like the problem is occurring in the php_excel wrapper. – user2985939 Nov 13 '13 at 06:34
  • I am using PHP 5.5 and randomly see these errors when running under memory load. – Uday Sawant Jun 15 '16 at 15:59