Error 500 @ writing 2 million cells

Topics: Developer Forum, User Forum
Jul 13, 2012 at 4:02 PM

Hello together,

I have a problem I like to generate a very large excel 2007-file which has round about 10M cells. I know that every row needs about 1k / each cell.

I started with generating a excel workbook with 100k cells and everything worked fine.

After I increased the number of cells I got some errors (memory_limit and max_execution_time). So increased the limits ...

Since 2M cells I only get an error "500 - Internal server error.", no memory or time error like expected.  I found no entry for that in php errorlog or in windows log. What can I do now?

 

My System:

Operating System: Windows 2008 Server SP 2

Webserver:           IIS 7.0

RAM:                   32 GB

CPU:                    8 CPU

 

I use the cell caching method cache_in_memory_serialized. I read in other discussion to use use other methods, but nothing changed except the performance of the writing process ...

Writing 1M worked fine, it took 81 seconds to generate the excel-file with a size of 3,26 MB.

 

My last limits:

memory_limit:  3072M

max_execution_time: 3600

 

Has anybody an idea what I can do now?

Thanks for your help!

 

Regards Tom

Coordinator
Jul 13, 2012 at 4:52 PM
Edited Jul 13, 2012 at 4:53 PM

The most obvious is an Apache timeout or that you're still hitting a time/memory limit.

Larger spreadsheets are slow, and it's a lot better to use CLI or background processes to manage them rather than a browser window.

 

Are you taking advantage of the fluent interface, or using the range methods for setting cells and styles to help performance and memory usage?

Jul 16, 2012 at 6:50 AM

Hello,

I will explain how I insert my data in the sheet:

$dbResults = array with data from DB

$sheet = PHPExcel->setActiveSheetIndex(0)

$style = array with styles

 

# Code

$sheet->fromArray($dbResults, "",  "A2", true);

$sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($style);

 

Can I improve this code?

 

Regards Tom

 

 

 

Jul 16, 2012 at 8:58 AM

Hello,

I found my problem, it was a Activity and RequestTimeout in IIS (Start --> Administrative Tool --> IIS Manager --> FastCGI Settings).

There I raised the limits and it worked for 2M cells.

 

After make I tried 3M cells i got another error:

PHP Fatal error: Out of memory (allocated 1791492096) ....

 

Increase memory limit made no changes to this message. I'm wondering about that because set memory_limit 3GB and this is quiet even less then that. Is it a IIS limitation which I have to change?

 

Regards Tom