3

I'm using PHPExcel and a php orm to generate excel5 spreadsheets for reports on a web application transactions.

Having to write more, I would like to know if a perl solution would be less memory hungry and faster for generating that excel file?

For example, creating 186 rows and columns up to BG with resized columns, with data type settings like dates, numbers and strings on PHPExcel took 91mb of memory and 62 seconds on a 64 bit, 6 core, ssd drive and too much gb of memory, linux server. (numbers excludes orm)

bksunday
  • 715
  • 5
  • 21
  • 4
    I doubt if anyone here has done a relevant comparison. You should write the code and compare them. Consider also `Excel::Writer::XLSX` which has superseded `Spreadsheet::WriteExcel` for Excel 2007 and later. – Borodin Dec 06 '12 at 06:18
  • 1
    Have a look at this, might help you to improve the speed http://stackoverflow.com/questions/5983845/phpexcel-very-slow-ways-to-improve . Personally I generate XML, then convert it to Excel (old format) using a perl script. This runs pretty fast (few seconds) but doesn't involve styling and type conversions – Michel Feldheim Dec 06 '12 at 06:53
  • 1
    In my experience, the Perl solution is very fast. The box you're describing sounds like a real workhorse. It shouldn't take that long. I'm offering XLS files for downloading as an addition to the HTML view of the web app, and these XLS files are generated with every run. The DB queries are heavy, and sometimes there are a lot more rows, though not much formatting. Still, it's all very user-friendly and happens in a matter of seconds, including the queries. – simbabque Dec 06 '12 at 08:44
  • Thanks for all the comments here, I ended up improving PHPExcel code as @MichelFeldheim link, and creating a perl script to do the same thing. (results in an answer below) – bksunday Dec 06 '12 at 19:56

2 Answers2

3

I resorted to test it out myself after googling too many basic perl usage, functions and such so here it is:

Test notes

Stats shown here varies a lot from my question because I had an external api call for each row which took a lot of time.

Memory stats for php are peak usage difference between not creating any excel file and creating it, and with perl I used valgrind. (here too, ignore memory stats from my question)

Also, I did not used piping to pass values from my php orm to perl (which could have been faster) nor did I execute queries within perl as I did not want to dig too deep into perl for this test. I merely wrote a json_encoded string into a file from php, called a perl script using exec(), then read the file and decoded the json string in perl.

And I used the latest versions of both libraries

Data tested

Creating one header row with a basic bold formatting,
180 more rows of data with columns up to BG,
with a bunch of dates and numeric formats applied on half of the columns,
and auto-resizing of columns.

The answer / Results (avg. from few runs)

With PHPExcel: 4.94 seconds, 60,191 mb
With Perl Spreadsheet::WriteExcel: 0.01 seconds, 13,193 mb

bksunday
  • 715
  • 5
  • 21
1

You are going to have to compare like with like to get a definitive answer but here are some pointers about Spreadsheet::WriteExcel:

  • Spreadsheet::WriteExcel was written to optimize throughput speed and minimise memory usage.
  • Formulas are slow in Spreadsheet::WriteExcel due to a slow parser. Avoid them if possible or use the store_formula() and repeat_formula() methods to speed them up. See the Improving performance when working with formulas section of the docs for more information.
  • The performance and memory usage of Excel::Writer::XLSX is not as good as Spreadsheet::WriteExcel unless you use the set_optimization() option. See Speed and Memory Usage in the Excel::Writer::XLSX docs.
  • Use the Spreadsheet::WriteExcel Examples document to get started.
  • You can use a variant of the bigfile.pl example for performance testing.
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 1
    Thanks for the pointers. I haven't yet used formulas through xls generators but I'll keep that in mind. I haven't looked at bigfile.pl either but autofit.pl was great to hook up an observer on writes! – bksunday Dec 06 '12 at 23:41