可以在PHP中将大型csv文件写为xls表吗？

Question

So I have a situation where I need to offer the user a multi-sheet excel document where each sheet has thousands of rows and ~10 columns. Data is coming from multiple MySQL queries.

Currently using "Laravel Excel" library to do this, but it uses up way too much memory and is giving me huge scalability problems.

So I have an idea to use MySQL OUTFILE to write a set of csv files on disk, one for each sheet, and then to create an xls document and write the previously written csv data as sheets in the xls.

Is there a method to accomplish writing the csv contents to a sheet "in bulk" per-say without iterating line-by-line through the csv or using up a large amount of memory (like writing to disk directly perhaps?)

Thanks for any help!

Answer 1

I had a very similar problem recently. My solution was to use the very lightweight PHP library PHP_XLSXWriter.

You can find it here: https://github.com/mk-j/PHP_XLSXWriter

It streams the output so it doesn't have to retain as much in memory.

In my usage case, I broke apart the "writeStream" method into three methods: one for each the header and footer, and one for the sheet content (i.e. the actual rows). This way I could write the header and then use Laravel's "chunking" feature, to get even more gradual with the writes.

The time increased slightly, but executing the script went from ~200Mb of RAM usage to under 15Mb!