1

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!

thequeue
  • 67
  • 1
  • 8
  • 1
    Excel is able to read CSV files. Perhaps all you need is to generate a CSV file, and write a short VBA snippet to load these CSV files into one single workbook. – RandomSeed Aug 22 '14 at 15:31
  • You can use VBA to load multiple CSV into the same workbook (see for example: [Is there a way to import data from .csv to active excel sheet?](http://stackoverflow.com/q/12197274/1446005), or [automatically import data from CSV to excel/calc sheet](http://stackoverflow.com/a/7008374/1446005)) – RandomSeed Aug 22 '14 at 15:37
  • Hmm, not familiar with VBA. Was really looking for a PHP solution, but I will investigate your idea. – thequeue Aug 22 '14 at 15:41
  • 1
    There are several alternative PHP/Excel libraries out there, some of them might be less demanding, but all I have tried so far do consume a lot of memory. – RandomSeed Aug 22 '14 at 15:43
  • Since I'm on a Linux box, VBA doesn't appear to be an option. Yes memory problems are abound in working with large XLS docs from what I've read. I am attempting to find a workaround by creating the sheet data as raw MySQL output then just somehow "merging" them as sheets in an xls doc... – thequeue Aug 22 '14 at 15:46

2 Answers2

0

Excel file formats (both BIFF and OfficeOpenXML) are not conducive to writing line-by-line like a CSV, because data isn't stored linearly. This means that all PHP libraries for writing native format Excel files have to work in PHP memory to manage the order of writing data to that file format, which means they will all consume large amounts of memory for larger volumes of data.

Laravel Excel is a wrapper around PHPExcel, which provides some options for reducing memory usage (eg. caching cell data to disk or SQLite database rather than holding it all in PHP memory), albeit at a cost in execution speed. What I don't know is whether Laravel Excel provides calls to enable these caching methods, though I believe some options are available allowing you to configure this.

Your alternative on a Linux platform is using non-PHP solutions like libXl or PUNO with Open/Libre Office

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
0

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!

pj10am
  • 16
  • 1