PhpSpreadsheet Cell Caching with Laravel Simple Cache

I spent quite a bit of time attempting to implement PhpSpreadsheet Cell Caching as described in their Memory Saving documentation page. I also read numerous related articles including Stack Overflow “Building very large spreadsheet with PHPSpreadsheet” where @andrewtweber claimed the Cell Caching was too slow and instead recommended “box/spout” XLSX/ODS/CSV writer.

Attempt to use the box/sprout Package

We are generating ~1MB Excel spreadsheets with ~25K rows and a total of ~197K cells. I confirmed box/spout claims of being VERY fast and VERY memory efficient, using ~94MB *less* memory than PhpSpreadsheet when generating these particular XLSX sheets. Unfortunately, I learned box/spout can NOT set column widths as of April 2021. I am tempted to deploy a custom box/spout with PR 715 “Not just another custom column widths PR”

Cell Caching Summary

I attempted to implement PhpSpreadsheet Cell Caching in a Laravel app using a few different methods. I agree that each method I attempted was too slow to be useful, primarily due to the fact that PhpSpreadsheet must make a separate call to the cache for EVERY CELL. In our case, that meant ~197,000 calls for ~197K cells. Regardless, I am sharing the examples I used so other devs can quickly determine whether or not Cell Caching is too slow for their use case.

Attempt 1: Use native Laravel Cache

I am not sure this is the best way to acquire a Simple Cache interface from Laravel, but PhpSpreadsheet seemed to be interacting with our Redis cache. However, 197K round trips over the network was going to take far too long. I gave up after waiting 5 minutes.

$cache = new Cache();
$cacheStore = $cache::getStore();
$cacheInterface = new Repository($cacheStore);
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Attempt 2: Use Shieldon Simple Cache with File Backend

I installed the “shieldon/simple-cache” package and attempted to use the File backend. Unfortunately, this had to create 197K temporary files (1 file for each of the ~197K spreadsheet cells). This took too long, even on an SSD. I gave up after waiting 5 minutes.

$path = 'cache/php-spreadsheet/';
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
$storagePath = Storage::path($path);
$cacheInterface = new \Shieldon\SimpleCache\Driver\File([
'storage' => $storagePath,
]);
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Attempt 3: Use Shieldon Simple Cache with SQLite Backend

I used the “shieldon/simple-cache” package again, only this time I attempted to use the Sqlite backend. My hope was that writing each of the ~197K cells to a single SQLite file might be faster. I was wrong. This took too long. I gave up after waiting 5 minutes. NOTE: Although the SQLite file contained 0 rows afterwards, the file was NOT truncated and remained at its peak size of 127MB. If you choose this method, be careful to truncate or delete your SQLite file to prevent a disk full situation.

$rebuildCache = false;
$path = 'cache/php-spreadsheet/';
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
$rebuildCache = true;
}
$storagePath = Storage::path($path);
$cacheInterface = new \Shieldon\SimpleCache\Driver\File([
'storage' => $storagePath,
]);
$cacheInterface = new \Shieldon\SimpleCache\Driver\Sqlite([
'storage' => $storagePath,
]);
if ($rebuildCache) {
$cacheInterface->rebuild();
}
Settings::setCache($cacheInterface);

$spreadsheet = new Spreadsheet();

Other Options

I saw at least one other person mention that they built their own custom Simple Cache interface and that they were writing 500 cells (or 500 rows?) to each file. I’d like to try this since it would reduce the number of files created from ~197K to only 394 files (or 50 files?), but could not find a code sample. Since OP said their attempts were too slow, I abandoned this option.

Leave a Reply

Your email address will not be published. Required fields are marked *