How to export big data to excel?

Question

PHP

Good afternoon. Maybe someone came across with exporting a large amount of data (more than 100k) to excel (xlsx format). Can you please tell me how to implement something like this? The data is taken from a MySQL database. The system itself that initiates the export is written in php.

Answer 1

There is a special package for large volume export, you can try it.
https://github.com/box/spout

Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 3MB).

Answer 2

Do not listen to advisers with PHPExcel. It already at 5-10k lines will start consuming 100+ MB of RAM.
People's candidate: box/spout . Writes to a file on the fly. Minus: almost complete lack of formatting. Doesn't know how to do neither column width nor edge alignment... Only font/borders.

Answer 3

Purely from experience in Excel, with volumes of hundreds of thousands of lines and more or less complex calculations, it starts to get very stupid and recalculate sheets for 30 seconds. I would have thought a hundred times how to use it later) It can merge at least somehow aggregated data

Answer 4

Or use the PHPExcel library to generate an excel file

Answer 5

Or maybe there is an easier option? like ETL (it is currently used, but such a number of lines cannot be written to a file)

Answer 6

Just in case, I'll add that the modern version of Excel can take data directly from MySQL just fine. Maybe, well, his, "intermediate links"? ...

Answer 7

https://github.com/sni10/importHelper
You can connect any Lib to work with the body of the file, even box/spout , even PHPExcel