saving file to Excel5 takes too long

Topics: Developer Forum, User Forum
Jan 26, 2009 at 9:41 AM
Edited Jan 26, 2009 at 9:42 AM
Hi,

I generate an excel5 with 5 cols and 150 rows and it takes about 5 minutes. (I think it is not a hardware problem. My computer has 4 GB RAM, CPU : Intel Core Duo 2 GHz).
Every thing is ok until the code that generate the excel.

        $objWriter = PHPExcel_IOFactory::createWriter($this->xls, 'Excel5');
        $tmpfname = tempnam('','xls_');
        $objWriter->save($tmpfname);

Do you know how to improve the perfomance of saving the excel?

Thank you.

Developer
Jan 26, 2009 at 9:49 AM
The writers are quite slow, it's something we're trying to solve, but it's very difficult.

5 minutes does sound like a long time for 5 cols and 150 rows. How fast is your script with Excel2007 writer? Are you using images?

Jan 26, 2009 at 10:18 AM

I think i found my problem: I setted the columns dimension auto:

$this->xls->getActiveSheet()->getColumnDimension($cellCol)->setAutoSize(true);

The excel contains 14 cols and 156 rows.

The times for generating the file are:
1. without autosize
Generating Excel2007: 20 sec
Generating Excel5: 1.02 sec


2. With the autosize:
Generating Excel2007: 4.5 minutes
Generating Excel5: 4.45 minutes

My solution was to eliminate the setautosize for columns. Do you know other solution using the auto dimensioning the column?



koyama wrote:

From: koyama

The writers are quite slow, it's something we're trying to solve, but it's very difficult.

5 minutes does sound like a long time for 5 cols and 150 rows. How fast is your script with Excel2007 writer? Are you using images?

Developer
Jan 26, 2009 at 11:23 AM
Thank you for the notice. I wasn't aware that autoSize was *that* slow. Will check up on this!

Developer
Jan 27, 2009 at 1:44 PM
I don't understand why it is so slow. Do you have some code to reproduce this? Or is it possible that you can send me the generated Excel file? (erik at phpexcel dot net)

Jan 27, 2009 at 2:06 PM
i attached the generated xls.
I will try  to extract the code  from my script which generate the xls and I will send it to you.

thank you.


koyama wrote:

From: koyama

I don't understand why it is so slow. Do you have some code to reproduce this? Or is it possible that you can send me the generated Excel file? (erik at phpexcel dot net)

Developer
Jan 27, 2009 at 3:20 PM
I do notice one weirdness with the Excel file: You have many cell merges that are set incorrectly.

I tried this:

$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('sample.xls');

$sheet = $excel->getSheet();
var_dump($sheet->getMergeCells());

And got this:
array(2184) { ["B2:A2"]=> string(5) "B2:A2" ["C2:A2"]=> string(5) "C2:A2" ["D2:A2"]=> string(5) "D2:A2" ["E2:A2"]=> string(5) "E2:A2" ["F2:A2"]=> string(5) "F2:A2"

...

Can you check your script for cell merges and eliminate them?

Jan 27, 2009 at 7:58 PM
Yes, you are right. I made a mistake in my script. Sorry for making you lose time.
It's working perfect.

thanks.


koyama wrote:

From: koyama

I do notice one weirdness with the Excel file: You have many cell merges that are set incorrectly.

I tried this:

$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('sample.xls');

$sheet = $excel->getSheet();
var_dump($sheet->getMergeCells());

And got this:
array(2184) { ["B2:A2"]=> string(5) "B2:A2" ["C2:A2"]=> string(5) "C2:A2" ["D2:A2"]=> string(5) "D2:A2" ["E2:A2"]=> string(5) "E2:A2" ["F2:A2"]=> string(5) "F2:A2"

...

Can you check your script for cell merges and eliminate them?

May 6, 2009 at 10:43 AM
I think autoSave is quite slow actually... I tested this with the same code, only difference is with or without autosize on all 13 columns.
This file has about 1350 rows and one worksheet.

12:36:40 Create new PHPExcel object without autosize
12:36:42 Write to Excel2007 format
12:36:42 Save as
12:36:46 Peak memory usage: 25.5 MB
12:36:46 Done writing file.
Action took 6,17801 seconds

12:36:46 Create new PHPExcel object WITH autosize
12:36:49 Write to Excel2007 format
12:36:49 Run: objWriter->save() 
12:37:44 Done writing file.
12:37:44 Peak memory usage: 101.25 MB
Action took 58,13133 seconds

12:37:44 Create new PHPExcel object without autosize
12:37:47 Write to Excel5 format
12:37:47 Run: objWriter->save() 
12:37:49 Done writing file.
12:37:49 Peak memory usage: 114 MB
Action took 4,79141 seconds

12:37:49 Create new PHPExcel object WITH autosize
12:37:52 Write to Excel5 format
12:37:52 Run: objWriter->save() 
12:38:11 Done writing file.
12:38:11 Peak memory usage: 256 MB
Action took 22,24719 seconds

Developer
May 6, 2009 at 12:06 PM
@TimW: As far as I remember, what caused the script to slow down was that one needs e.g.

$objWorksheet->mergeCells('A1:B2');

rather than

$objWorksheet->mergeCells('B2:A1');

That means from upper-left cell to lower-right cell.

Can you check for this kind of reversed merge order in your script?

May 6, 2009 at 12:11 PM
I dont use mergeCells at all actually.
May 6, 2009 at 12:11 PM
Hi,

My script had an error merging cells. I fixed that error and now it's working fine.

koyama wrote:

From: koyama

@TimW: As far as I remember, what caused the script to slow down was that one needs e.g.

$objWorksheet->mergeCells('A1:B2');

rather than

$objWorksheet->mergeCells('B2:A1');

That means from upper-left cell to lower-right cell.

Can you check for this kind of reversed merge order in your script?

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

May 6, 2009 at 1:39 PM
Maybe also interesting to know, I am migrating from the (old pear) xls writer to phpExcel.
Exactly same xls file creation script takes: "Action took 1,31069 seconds " with old xls writer...

You can compare this with the third timing I posted earlier: 4,79 sec.
Developer
May 6, 2009 at 1:57 PM
>> I dont use mergeCells at all actually.

Ok, then the problem must be different from Ovidiu's. I'm afraid that I may need to see the script + the generated Excel file. Can you send them to me? (erik at phpexcel dot net).

>> Maybe also interesting to know, I am migrating from the (old pear) xls writer to phpExcel.
>> Exactly same xls file creation script takes: "Action took 1,31069 seconds " with old xls writer...
>> You can compare this with the third timing I posted earlier: 4,79 sec.

If you are using many styles, speed and memory performance is likely to get much better with PHPExcel 1.7.0, you can already now try latest source code and compare with PHPExcel 1.6.7. Improvement depends on that you style many cells at once (e.g. using duplicateStyleArray()) instead of styling one cell at a time.

Developer
May 9, 2009 at 10:40 PM

I did some testing with your scripts and I was able to reproduce the behavior with setAutosize() using PHPExcel 1.6.7. As you noted yourself, this is no longer happening with latest source code so I didn't investigate this too much.

With that said, I think the high memory usage was caused by "orphan" style objects being created when autoSizes were calculated. This should no longer be happening since we are now using shared styles in PHPExcel.

Memory.
The lower memory usage by PEAR::Spreadsheet_Excel_Writer is primarily possible because it uses disk caching as opposed to PHPExcel which uses a full in-memory representation of the Excel file. We are currently discussing the possibility of offering optional disk caching and database caching. This would also allow processing of large Excel files.
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=6857

Speed.
After looking at your script, I do have a single tip for you: Whenever possible (and it normally is), use setValueExplicit() instead of setValue(). When you have many cells, this can speed up your script by 30%.

May 11, 2009 at 8:32 AM

Thanks Koyoma for your detailed answer!
setValueExplicit indeed speeds up the script, I also noticed a significant difference between getCellByColumnAndRow (~30% faster) and getCell (slower).

Jun 16, 2010 at 2:45 PM
Hello, Thought I'd just add to this existing thread as I am having the same problem. I have a sheet that is generated containing about 100columns and about 6500 rows. It takes about 11minutes to generate. Is this normal?