Apache Crashes Whilst Saving File

Topics: Developer Forum
Nov 15, 2010 at 1:06 PM

I have a template file that I wish to enter data in to then save. 

There are a number of complex formulas in my template which I think are causing PHPExcel to go in to an infinite loop and crash Apache. I am not sure what formula might be the issue though - there are quite a few to go through and having only just started with PHPExcel hoping someone might know what might be the problem. 

I have seen this previous discussion:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=206923

However I am trying to save to Excel 2007 and don't have any Named Formulas.

The template file can be downloaded here: 

http://www.mikej.co.uk/template.xlsx

The code that I am using to save is fairly simple:

 

$objPHPExcel = new PHPExcel();

$objPHPexcel = PHPExcel_IOFactory::load('template.xlsx');

$objWorksheet = $objPHPexcel->getActiveSheet();
$objWorksheet->getCell('A1')->setValue('John');
$objWorksheet->getCell('A2')->setValue('Smith');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel2007');

$objWriter->save('write.xlsx');

 

Coordinator
Nov 15, 2010 at 1:35 PM

Looking at your template, it contains formulae with links to other worksheets. There were a number of issues with this in the 1.7.4 release that have since been fixed. Can you please try again with the latest SVN code (accessible through the "Source Code" tab on this page, then just click on the download link against the "Latest Version" panel on the right of the page)

Nov 15, 2010 at 2:14 PM

Just downloaded the latest version and it is still crashing the Apache server.

Nov 15, 2010 at 2:24 PM

This is the error that I am getting from the Apache error log:

 

[Mon Nov 15 15:23:41 2010] [notice] Parent: child process exited with status 3221225477 -- Restarting.

Coordinator
Nov 15, 2010 at 5:02 PM
Ekim wrote:

This is the error that I am getting from the Apache error log:

 

[Mon Nov 15 15:23:41 2010] [notice] Parent: child process exited with status 3221225477 -- Restarting.

 The most common cause for this particular error, which is normally only generated when running on a Windows platform) is that the libmysql.dll file is in the wrong directory -- See this link for a potential fix -- although it can generated by a buggy version of any PHP module.

Nov 16, 2010 at 9:09 AM

Ok tried that fix but it didn't help. Apache still crashes - although not every time. I wasn't sure that the above would help as I wasn't using/running MySQL at all.

When Apache did run PHPExcel would still throw back an error, in relation to some of the formulas in the speadsheet. I tried deleting the formulas as I went to see if I could at least get the sheet written to, but once I get to a certain point Apache will crash every time with out fail - using the same error number as before.

I have listed out each of the formulas and the PHPExcel error that is thrown back on each before they are removed from the sheet:

FORMULA 1:
=INDEX(rolesrates!$1:$1048576, MATCH($A71, rolesrates!$A:$A,FALSE), MATCH($A$2, rolesrates!$1:$1,FALSE))

Fatal error:  Uncaught exception 'Exception' with message 'frontsheet!F11 -> info!F104 -> info!F90 -> info!F85 -> info!F76 -> info!D76 -> estimate!C71 -> estimate!C5 -> Formula Error: An unexpected error occured' in C:\xampp\htdocs\inc\PHPExcel\Cell.php:272
Stack trace:
#0 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(964): PHPExcel_Cell->getCalculatedValue()
#1 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'F11', Array, Array)
#2 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 C:\xampp\htdocs\index.php(33): PHPExcel_Writer_Excel2007->save('write.xlsx')
#5 {main}
thrown in C:\xampp\htdocs\inc\PHPExcel\Cell.php on line 272

FORMULA 2:
=IF(data!$B$2+data!$C$2 > 0, SUMPRODUCT((INDIRECT("data!B"&data!$B$2&":B"&data!$C$2)=$C94)*(INDIRECT("data!C"&data!$B$2&":C"&data!$C$2))), 0)

Fatal error:  Uncaught exception 'Exception' with message 'frontsheet!F12 -> info!H104 -> info!H102 -> info!H94 -> Formula Error: Unexpected ,' in C:\xampp\htdocs\inc\PHPExcel\Cell.php:272
Stack trace:
#0 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(964): PHPExcel_Cell->getCalculatedValue()
#1 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'F12', Array, Array)
#2 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 C:\xampp\htdocs\index.php(33): PHPExcel_Writer_Excel2007->save('write.xlsx')
#5 {main}
thrown in C:\xampp\htdocs\inc\PHPExcel\Cell.php on line 272

FORMULA 3:
=IF(data!$B$1+data!$C$1 > 0, SUMPRODUCT((INDIRECT("data!B"&data!$B$1&":B"&data!$C$1)=$C76)*(INDIRECT("data!C"&data!$B$1&":C"&data!$C$1))), 0)

Fatal error:  Uncaught exception 'Exception' with message 'frontsheet!F12 -> info!H104 -> info!H90 -> info!H85 -> info!H76 -> info!G76 -> Formula Error: Unexpected ,' in C:\xampp\htdocs\inc\PHPExcel\Cell.php:272
Stack trace:
#0 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(964): PHPExcel_Cell->getCalculatedValue()
#1 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'F12', Array, Array)
#2 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 C:\xampp\htdocs\index.php(33): PHPExcel_Writer_Excel2007->save('write.xlsx')
#5 {main}
thrown in C:\xampp\htdocs\inc\PHPExcel\Cell.php on line 272

FORMULA 4:
=INDEX(rolesrates!$1:$1048576, MATCH($B2, rolesrates!$A:$A,FALSE), MATCH(estimate!$A$2, rolesrates!$1:$1,FALSE))

Fatal error:  Uncaught exception 'Exception' with message 'people!C2 -> Formula Error: An unexpected error occured' in C:\xampp\htdocs\inc\PHPExcel\Cell.php:272
Stack trace:
#0 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(964): PHPExcel_Cell->getCalculatedValue()
#1 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'C2', Array, Array)
#2 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 C:\xampp\htdocs\inc\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 C:\xampp\htdocs\index.php(33): PHPExcel_Writer_Excel2007->save('write.xlsx')
#5 {main}
thrown in C:\xampp\htdocs\inc\PHPExcel\Cell.php on line 272

After this Apache crashes every time without fail - although a file is being written. The file looks right, apart from each of the worksheets has been duplicated and the name appended with a '1'.

Any ideas what the issue might be?

Thanks

 

Nov 16, 2010 at 9:31 AM

Just tried it again with all of the above formulas removed. It didn't crash, the file that was written still had the duplicate sheets but this was output on the page:

Token is a named range
Named Range is PEOPLE
Token is a named range
Named Range is PEOPLE
Token is a named range
Named Range is PEOPLE
Token is a named range
Named Range is PEOPLE
Coordinator
Nov 16, 2010 at 9:08 PM

<blush>

Token is a named range
Named Range is PEOPLE

is some diagnostics that I inadvertently left in the code while testing named ranges in multibyte character sets. I've commented it out now in the SVN code.

Using $1:$1048576 as a range won't be good for the system: PHPExcel will check every single cell between rows 1 and 1048576 (whether it exists or not) which is both a big processing and a big memory overhead, which could well cripple the web server. It might be better to restrict the range to rows that you are actually using.

Nov 16, 2010 at 9:13 PM

I thought it might be something to do with that. I can tweak the formula to not be so heavy, will that fix all of the above formula issues do you think?

Any ideas on why it might be producing duplicate sheets?

Coordinator
Nov 16, 2010 at 9:29 PM
Ekim wrote:

I thought it might be something to do with that. I can tweak the formula to not be so heavy, will that fix all of the above formula issues do you think?

 No guarantees, but eliminating the "volume of data" problem would make it easier to diagnose any other problems that might exist.

Ekim wrote:

Any ideas on why it might be producing duplicate sheets?

 No ideas at all. There is a work item (14261) raised for this, but I have never managed to replicate the problem on any of my test servers.

Nov 17, 2010 at 9:10 AM

Ok, I did some testing. I removed all of the forumlas and it was still crashing. I think the issue was coming on some data validation that I had on one of the sheets. The next issue however was that all of the sheets were being duplicated.

I removed all of the sheets from the template and added them one by one. The first two went in ok (although without formulas) I added in one sheet which was blank, barring they cells were filled with white, after it had been run through the script however the first row would be filled with black - which was odd.

After I add the fifth sheet however it started duplicating all of the sheets again. I think there is something in the sheet that is creating the error. I can't find what it is yet though. Would you be interested in me sending you the file to see if you can replicate it? It's odd as although the sheet looks empty - if you try and delete the sheet excel complains that there is something you are about to delete.

I am going to start my template from scratch I think - will make life easier.

Thanks for your help.