Error 500 when generating excel file

Topics: User Forum
Nov 13, 2010 at 11:59 PM

I'm trying to generate a Excel file with data validators and functions INDEX and MATCH.

I was able to generate most of my sheets, but the last one isn't working properly. Whenever I try to run the php page, the page loads for 4 or 5 minutes, and then it gives me an Error 500. I've checked php logs, and there's nothing there.

$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,'=INDEX(Empresas!A2:A1066,MATCH(B2,Empresas!B2:B1066,0),1)');

If I change this piece of code to the above, the page takes 2 or 3 minutes to load, but it works. 

$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,'=INDEX(Empresas!A2:A100,MATCH(B2,Empresas!B2:B100,0),1)');

Please not that I use this exact interval in another Sheet, with no problem whatsoever.

So, I guess this is because of the number of items in the interval? If so, isn't that weird? It's just a cell function, so I don't understand why the interval of values is relevant.

 

Thanks,

Eduardo

Coordinator
Nov 14, 2010 at 12:26 AM

If it's close to 5 minutes before you get the 500, then it's likely that the script is timing out: 5 minutes (300 seconds) is the default Apache timeout.

>> So, I guess this is because of the number of items in the interval?
>> If so, isn't that weird? It's just a cell function, so I don't understand why the interval of values is relevant.

It is relevant though, and not so weird. When PHPExcel writes a cell containing a formula, by default it writes both the formula and the result of the calculation... and the more data that is included in that calculation, the longer it will take to calculate the value. You can change this default behaviour, so that PHPExcel doesn't calculate the result when writing.

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('myExcelFile.xls');
Nov 14, 2010 at 12:58 AM
MarkBaker wrote:
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('myExcelFile.xls');

Thank you for the fast response. Unfortunately it didn't work. I still get the Error 500.

I've tried setting max_execution_time and max_input_time to 600 seconds, but it doesn't seem to solve the problem. I'm completely clueless.

Nov 14, 2010 at 1:16 AM

You can check the php script here: http://www.eic.pt/novoreport.txt

Thank you once again for your help.

Coordinator
Nov 14, 2010 at 11:18 PM
Edu115 wrote:

I've tried setting max_execution_time and max_input_time to 600 seconds, but it doesn't seem to solve the problem. I'm completely clueless.

 PHP script execution time is still limited by the Apache/webserver time limit. Increasing PHP's max execution time to 600 seconds won't make much difference if Apache is still limiting you to 300 seconds.

Nov 29, 2010 at 12:45 AM

I managed to get this running in a local server, and things are not looking good for me.

The script has been running for 20 minutes, and no output so far. The only change I made is the one I wrote in the original post. I'm using the setPreCalculateFormulas(false) too.