How to speed up the creation of a large price list?

Question

PHP

Good afternoon, I create an xlsx price list from the mysql database using the PhpSpreadsheet library, after creation the price list is sent to clients, each client has its own price list with different positions and prices (I do everything with a query in mysql and php is used to create prices), each price list is about 300 thousand positions. The task starts cron according to the schedule The
question is, the price list is created in the order of +-40 minutes, how is it possible to speed up the creation of the price list?
Memcache is also used, but it has only 1 gig, this is not enough (maximum on hosting)

Answer 1

40 minutes - it sounds quite wild, you need to profile and understand what exactly takes so long.
PhpSpreadsheet has never been faster, especially for large files, I recommend going to at least https://github.com/box/spout . Recently there was an interesting article on Habré about generating large and complex Excel, check out: https://habr.com/post/422059/

Answer 2

IMHO, the general approach (regardless of the library used):
Almost any library can fill one cell, or maybe a range.
It is necessary to form a range (ranges) in the database, and then fill it in Excel with one (several) operation.
From experience, on large files, this will give a performance boost by several orders of magnitude.

Answer 3

From the database to CSV, and there you can open it directly with Excel and watch it in the same way (like a regular Excel file) or do something already with the finished file (convert).
Since CSV is just text, the generation should be fast.

Answer 4

As an option, generate csv and overtake it in the desired format with a utility such as libraoffice

Answer 5

There is also an interesting hack - to form html with < table >< / table >, save it as xls - any excel opens such a file without sneezing. True, I did not test it on such volumes.

Answer 6

And there is another hack.
Think of XLSX as a zip archive of a set of XML files, which is what it is.
PHP handles XML pretty well, unlike XLSX.
But this is quite dreary, you have to analyze the methodology of how the whole thing is formed. But it might seem interesting.

Answer 7

Finally got there! Thank you all for the discussion, I noted the solution to this issue, it can be useful to someone. Creating a price list of 300 thousand lines (17mb) now takes two minutes! Solution in library https://github.com/box/spout