Problems with large excel files

Topics: Developer Forum, User Forum
Jun 2, 2009 at 2:10 PM

Hi everyone,

I developed an large reporting system for an company with PHPExcel. It is one of the best PHP classes I found outthere.
Now, the company contacted me because they have a huge problem.

They have an specific template (2 tables - 1 cover table, 1 entries table) for this excel reports, which allows 40 report entries per page. In my script, I read this template file and add this as an external sheet to the new worksheet and fill in the data.

Now, they have to generate reports with about 2000+ entries. Their requirement is, that every report page should have an own table in the worksheet. With about 2000 entries, we speak about 50 tables in ONE worksheet. And this is a huge problem for PHPExcel.
Normally, PHPExcel can manage 10 - 20 tables in one worksheet without crashing. I set the memory limit on my localhost to 512MB / 1024MB and the script execution to 500 seconds. EVERY time with more than 20 tables to generate, fatal errors occur.

If I won't embed the template file, I can generate this many tables. But my experience is, that PHPExcel have also problems with COMPLEX styling and MANY styling-code.

So my question is:
Is there a clever trick to generate so large excel files with a template?

Even if I take my business logic out of the source code, the script crashes. So here is a bit sourcecode:

<?php
$phpExcel = new PHPExcel();
$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('template/template1.xls');

// Cover
$sheet = $excel->getSheet(0);
$phpExcel->addExternalSheet($sheet);
$phpExcel->removeSheetByIndex(0);
$phpExcel->setActiveSheetIndex(0);

// Entries
$sheetResults = $excel->getSheet(0);
$objPHPExcel->addExternalSheet($sheetResults);
$objPHPExcel->setActiveSheetIndex(1);

for($i=1;$i<=50;$i++) {
$sheetName = 'Entries '.$i;
$cloned = $objPHPExcel->getActiveSheet()->copy();
$cloned->setTitle($sheetName);
$cloned->setShowGridlines(false);

// BUSINESS LOGIC

$phpExcel->addSheet($cloned);
}

$objPHPExcel->removeSheetByIndex(1);
$objPHPExcel->setActiveSheetIndex(0);

$fileNameResult = 'reports/'.$reportName.'.xls';
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
$objWriter->save($fileNameResult);



Do you have any suggestions?

Developer
Jun 2, 2009 at 6:37 PM

What version of PHPExcel are you using?

Jun 3, 2009 at 10:50 AM

I'm using Version 1.6.6

Developer
Jun 3, 2009 at 11:15 AM

It should be possible to get this problem solved as follows.

1. For a start, try with latest source code on your development machine. PHPExcel 1.6.7 won't suffice. Get latest source code from here:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

2. It may turn out that styles are suddenly messed up. I'm not sure, but try and report back the results. Hopefully, things speed up, and script uses less memory.

3. In the meantime, I will look at the styling issues. The problem is that right now addExternalSheet() may break styling.

Developer
Jun 4, 2009 at 5:58 AM

You may try with latest source code when it's available in a few hours. addExternalSheet()should be working now.

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Jun 4, 2009 at 6:48 AM

Thanks in advance.
I'll test it today :-)

Jun 8, 2009 at 6:38 AM

Well, after a weekend full of testing, I can say this:
Adding up to 5 sheets is no problem. But if this number is higher, I'll get an "503 Service Temporarily Unavailable" error.

A typical procedure is this (adding 1 cover sheet and 5 result sheets):

08.06.2009 08:22:38 - start
08.06.2009 08:22:38 - cover
08.06.2009 08:22:39 - result sheets
08.06.2009 08:23:37 - save
08.06.2009 08:23:38 - end

After a look in my server log, I (maybe) found the key for the 503 error: There are many fatal errors like "Out of memory", "Allowed memory size of 1073741824 bytes exhausted", ...

Any suggestions?
Thanks in advance for your great work.

Developer
Jun 8, 2009 at 1:15 PM

Hmm, it sounds quite incredible that the script is using 1GB of memory. I am not sure where $objPHPExcel is coming from in your script?

You are welcome to send me some sample files (template, final generated workbooks), maybe I can get a better understanding. (erik at phpexcel dot net)

Jun 8, 2009 at 4:20 PM

Hmm, i'm having the same kind of problem trying to generate an excel sheet with about 5000 rows x 10 cols

PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 67 bytes) in /usr/share/php5/PHPExcel-1.6.7/Style.php on line 468

I personnaly use this function i've created in a class that extends PHPExcel :

        /**
         * @param $entetes (array) First line of excel sheet
         * @param $data (array(array)) Contains all the lines of the sheet
         */
        public function excelFromArray($data, $entetes = false) {
            $rowNum = 1;//Rows start at 1
            if(is_array($entetes)) {//create the header
                $colNum = 0;
                foreach($entetes as $col) {
                    $cellcoord = PHPExcel_Cell::stringFromColumnIndex($colNum).$rowNum;
                    $this->getActiveSheet()->setCellValue($cellcoord, $col);
                    $colNum++;
                }
                $rowNum++;
                $this->hasEntete = true;
            }
            foreach($data as $row) {//add data
                $colNum = 0;//Cols start at 0, 0=A
                foreach($row as $col) {
                    $cellcoord = PHPExcel_Cell::stringFromColumnIndex($colNum).$rowNum;
                    $this->getActiveSheet()->setCellValue($cellcoord, $col);
                    $colNum++;
                }
                $rowNum++;
            }
            $this->height = $rowNum-1;
            $this->width = $colNum-1;
            $this->defaultStyleIt();//applies default style
        }

Am i doing something wrong or is it just normal ?

Developer
Jun 8, 2009 at 5:42 PM

@Laukoko: 1GB memory usage indeed sounds high. It looks like you are using PHPExcel 1.6.7? If you are using styles it may help to use latest source code which contains some optimizations.

Can you download latest source code from here and report back results:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Jun 9, 2009 at 9:15 AM

Here's my report :

Data :

Cols : 28
Rows : 5568

ini_set('memory_limit','1024M');
ini_set('max_execution_time','600');


Using PHPExcel-1.6.7

Treating data: 5.00s
PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 12 bytes) in /usr/share/php5/PHPExcel-1.6.7/Style/Border.php on line 96


Using PHPExcel-27838

Treating data: 5.83s
Generating file: 37.5s
memory_get_peak_usage: 221M


 

Tested about 3 times each. PHPExcel-1.6.7 even crashed once. Your latest source is way more optimized. Hope you'll be releasing a stable version soon.

 

 

Developer
Jun 10, 2009 at 5:36 AM

@Laukoko: Thanks for your feedback. For now you should be able to use latest source. It should be stable. PHPExcel is approximately releases bi-monthly so next release would be around start of July, I think.

Nov 30, 2009 at 10:21 PM

I'm also running into problems with excessive memory usage. I'm using version 1.7.1 to open a 4mb Excel spreadsheet, with about 16 worksheet, each with up to 200 rows... it's using about 332mb

I'm wondering are there a way to just load worksheet separately? so we don't have to use too much memory at once...

thanks

Dec 22, 2009 at 6:39 AM
Edited Dec 22, 2009 at 6:44 AM

Hi All,

I have problem with this error code,

Fatal error: Out of memory (allocated 573046784) (tried to allocate 1008 bytes) in D:\webroot\1.7.1\Classes\PHPExcel\Cell.php on line 996

I'm using version 1.7.1 to open 4MB Excel Spreadsheet 2007, 1 worksheet up to 147710 rows and 6 coloumn.

my php.ini memory_limit = 1024M      ; Maximum amount of memory a script may consume (16MB)

this is my code

 

require_once '../Classes/PHPExcel/Reader/Excel2007.php';

$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setLoadSheetsOnly( "Sheet1" );
$objPHPExcel = $objReader->load("singpost.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
	echo '<tr>' . "\n";
	for ($col = 0; $col <= 6; ++$col) {
		echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, 1)->getValue() . '</td>' . "\n";
	}
	echo '</tr>' . "\n";
for ($row = 2; $row <= 20; ++$row) {
	echo '<tr>' . "\n";
	for ($col = 0; $col <= 6; ++$col) {
		echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
	}
	echo '</tr>' . "\n";
}
echo '</table>' . "\n";

 

Help please...

Thanks..

-waterbomm-

Developer
Jan 20, 2010 at 10:20 AM
waterbomm wrote:

Hi All,

I have problem with this error code,

Fatal error: Out of memory (allocated 573046784) (tried to allocate 1008 bytes) in D:\webroot\1.7.1\Classes\PHPExcel\Cell.php on line 996

I'm using version 1.7.1 to open 4MB Excel Spreadsheet 2007, 1 worksheet up to 147710 rows and 6 coloumn.

my php.ini memory_limit = 1024M      ; Maximum amount of memory a script may consume (16MB)

You have almost 900,000 cells. This is quite big a workbook for PHPExcel. It would probable need 900MB memory to read that (around 1KB per cell). This is something we are aware of. Hopefully, we are getting closer to a solution.

Sep 14, 2010 at 12:00 PM

Hi, I think I have a similar problem when exporting a large file.

I have to export ten files, each with the same formatting and the same columns. Each file contains a single worksheet, of 40 columns, and up to 20,000 lines. If I restrict the objects to 1,000 lines, everything works well. However, your excellent memory_get_peak_usage routine shows me that this uses up almost all of the 128MB I am allocated from the company server.

Unfortunately, I am never going to be able to get 1GB+ out of them!

Is there a way to append to files, so that I could perhaps write 1000 lines at a time?

Coordinator
Sep 14, 2010 at 12:56 PM
Edited Sep 14, 2010 at 12:58 PM

@certando

I've written on this several times before:

20,000 rows and 40 columns = 800,000 cells,  with an average memory requirement of 1k/cell (dependent on string content), and double that figure when writing, plus a code footprint of 10MB-25MB, gives a memory requirement of 782MB * 2 + 25 = 1,589MB.

There is no way to append to files unless you're using the CSV writer. Try using cell caching, which can potentially reduce your memory requirements to about 560MB.

Sep 14, 2010 at 2:22 PM

 

Thanks for your suggestions - I will drop back to the .csv driver if I can't get the .xlsx going - but it would be a shame to miss out on the opportunity to pre-load formulae, formatting, and filtering.

 

In the meantime, I am experimenting with your suggestion of working with caching. It has allowed me to pass the 1000 line mark, but then I reached another problem. The error message is

"Fatal error: Maximum execution time of 60 seconds exceeded in C:\xampp\htdocs\CTP\local\Classes\PHPExcel\Cell.php on line 805". My execution timeout is set to 120sec, confirmed by phpinfo().

 

I apologise for posting this here (I did search in the forum, but failed), but do you know if there is anything in the classes which could override my default execution timeout?

 

Coordinator
Sep 14, 2010 at 2:31 PM

Cell caching is a trade-off between reducing the memory footprint, and increasing the execution time.

PHPExcel doesn't provide any internal mechanism for overriding the default execution timeout because this is built into standard PHP. It can be modified either in php.ini by changing the value for

max_execution_time = 60

or within your code using

set_time_limit(240);

will set the maximum execution time to 240 seconds

set_time_limit(0);

will make it unlimited (although your web server may well have its own limit, typically 300 seconds)