Optimizing excel generation from php?

S

someoneisusingmyusualnick2012-08-19 03:06:13

PHP

someoneisusingmyusualnick, 2012-08-19 03:06:13

Essence.
You need to create a report in Excel.
I am using phpexcel.
It copes with its task, but ...
An Excel file must be generated from a CSV created by a third-party code, and then a couple of tabs should be added, decorated with colors, etc.
Actually, using the lib above, everything is generally simple.
But as it is written on their website, for 1 cell of the file you need ~ 1kb of memory. Now I already have 1.5GB (I set the corresponding limit in php.ini). In the future, there will be more data. The current dev server only has 3GB of RAM(sic!). And from the experience of working with the customer, there is a suspicion that this dev server will then become a production server.
The built-in options for caching in memory (memcache, etc.) are not suitable because there is not enough memory anyway.
In the latest version, caching appeared in SQLite - I tried it, the Report was generated in 70 minutes.
Considering that report generation is the root of all functionality, this is never acceptable - it needs to generate faster for a bunch of users at the same time.

There is nothing special to optimize in the code - everything is done quickly until it reaches the file generation.

There is an idea to generate several csv files (one per tab), and then connect them with an exec pearl script - but I don’t know if it makes sense.

Advise how to optimize the process or maybe there are others who can do it faster?

Reply

Answer the question

In order to leave comments, you need to log in

4 answer(s)

I

Ilya Plotnikov, 2012-08-19
@someoneisusingmyusualnick

I recommend throwing out PHPExcel and collecting xml by hand according to the specification.

A

alexeyshockov, 2012-08-19
@alexeyshockov

Not sure about xls, but csv is very easy to stream, line by line.

A

Alexander Korotkov, 2012-08-19
@smagen

Also, as an option, write a macro for LibreOffice / OpenOffice, which will do the conversion with all the necessary transformations. Here is a topic on translating xls to csv
www.oooforum.org/forum/viewtopic.phtml?t=52942
you can do it back.

S

softm, 2012-08-19
@softm

It is necessary to “persuade” the client to a smaller excel.
Can you point out what specifics make it necessary to have such a large file?