致命错误：当内存使用率非常高时，会在非对象上调用成员函数write（）

Question

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".

Answer 1

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.