Write 50.000 Rows

Topics: User Forum
Sep 28, 2009 at 6:25 PM
Edited Sep 28, 2009 at 6:26 PM

Hi,

for a Datawarehouse Tool I'm working on i have to provide the possibility to export up to 50k rows and n Columns. I just tested 2007 and BIFF Output with the following result:

Memory: ~600MB

foreach($buffer as $row) {
    
    $c = 1;         
    foreach($row as $val) {         
      $o->getActiveSheet()->setCellValueExplicitByColumnAndRow($c, $i, $val, PHPExcel_Cell_DataType::TYPE_STRING);
      $c++;             
    } 
    $i++;
  }

took ~50 sec.

Saving the file took another 250 sec. with BIFF and 470 sec. with Excel2007. There are no Styles implemented yet!

Any ideas for speeding up that thing?

ty

Developer
Sep 29, 2009 at 2:24 AM

What version of PHPExcel are you using? If you are using PHPExcel 1.7.0 then can you also try with latest source code. It could be faster:

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

Also, do you have formulas in your workbook?

Sep 30, 2009 at 7:11 AM

Hi,

i already used the latest version and no there are no formulas. i tried to optimize some things by breaking some coding standards. i wrote 10.000 rows with 4 columns.

e.g.: accessing Cell:_value directly instead of Cell:getValue() brings 2 seconds.

on line 236 of Cell.php there should be included an if:

if(iconv_strlen($pValue) > 32767)
   $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 32767);
without the if an expensive substring is made on EVERY cell-value. this brings another 2 sec. 
finally i switched to excel com api. the performance is unbeatable: 80.000 rows with formatting in 2-3 secs.
Developer
Oct 1, 2009 at 3:02 AM

Thanks for the tips. We should try to include them even if they only save a couple of seconds.

Well, the original goal of PHPExcel was to provide a simple solution for creating invoices and stuff that would work on cheap shared web hosting solutions. Then came users with their large workbooks trying to use PHPExcel. Only recently have we been trying to push the limits. I would say that 100,000 cells is currently the limit where it was 10,000 cells prior to PHPExcel 1.7.0. If we can bring that number up to 1,000,000 cells then it would great.

Coordinator
Oct 1, 2009 at 8:00 AM

We can't "compete" with the Excel COM API... that will always be able to do everything that Excel can do (and as quickly) because it's actually running Excel itself. But it requires a Windows server, with Excel installed on that server, and most web hosts don't offer that option. What we're trying to provide is as much of the functionality of Excel as we can, as efficiently as we can, with an API that's as easy to use and as powerful as we can, in pure PHP for those developers who can't use COM, or who want a cross-platform option.

With each new release, we're trying to add new functionality, but also trying to improve performance, to run more quickly, to reduce the memory footprint. Suggestions like your length test before calling substring are always useful because they help in that regard, so thanks for the tip

Oct 1, 2009 at 4:54 PM

Hi,

it's clear for me that the lib can't reach the performance of the com api and I my intention was not to make phpexcel bad because of the comparison. I'm still using the library for the pivot-output of my tool (not Excel-Pivottable), because styling is dramaticlly simpler than with the com api and there is no better possibilty to produce such great output with php. Just keep on working on the library - it's very important for a lot of people! Thank you for that.

Oct 5, 2009 at 4:44 AM

Hi, i think this is a very good library but unfortunally I was almost try to do what rokx need.

I found another php library to do that, you can check it at:

http://www.phpclasses.org/browse/package/1919.html

May be you have to read the threads to make it works.

I tested on Linux and I have to add/change some lines of code

Contact me if you want.

TGP

webmastertgp[at]hotmail[dot]com