Redis as cache for excel generation using Phpspreadsheet.

 

Generating excel files with Phpspreadsheet library is a common task in many projects. That library holds by default in memory the cells representation. In some cases, when the file to be generated is really BIG that default configuration provoke PHP memory limitation.

Problem

Memory limitation issues when generating really BIG excel files.

The library by default loads the cells representation in memory and at some point it is susceptible to PHP's memory limitations.

 

Fatal error allowed memory size of "M" bytes exhausted tried to allocate "N" bytes in "K" on line "L"

  

Solution

It's created a cache implementation using Redis to be used by Phpspreadsheet for storing the excel cell representation. 

 

How to use:

 

Before the initialization of any Spreadsheet instance it’s needed to indicate Phpspreadsheet that the cell representation should be saved into Redis instead of memory.

 

ExcelRenderSettings::setCache($renderCache);

 

Upon it is done the render can be initialized and used as normal.

 

 $csvRender = new RecurringtrackingExcelRender();

 $csvWriter = new Xlsx($csvRender->render());

 $csvWriter->save($csvFilename);

 

 

FULL CODE

 

1. Cache

 

<?php

namespace Path\To\ExcelRendererCache;

use Redis;
use Exception;
use Psr\SimpleCache\CacheInterface;
use Sonnys\Common\Core\Registry\RedisConnector;
use RedisException;

class ExcelRendererCache implements CacheInterface
{
    private const CACHE_KEY_PREFIX = 'excel_render_cache.';
    private $_redisConn;
    private $_defaultTtl;

    public function __construct(string $redisHost, int $defaultTtl, string $redisPassword = null)
    {
        try {
            $_redisConn = new Redis();
            if (!$_redisConn->connect($redisHost)) {
                throw new RedisException('Unable to connect to Redis.');
            }
            if (null !== $redisPassword && !$_redisConn->auth($redisPassword)) {
                throw new RedisException('Incorrect password provided to authenticate with Redis.');
            }
        } catch (Exception $e) {
            throw new Exception('Failed to connect to Redis.', 0, $e);
        }

        $this->_defaultTtl = $defaultTtl;
    }

    public function get($key, $default = null)
    {
        $this->_sanitizeKey($key);
        $cacheKey = $this->_buildCacheKey($key);
        try {
            return unserialize($this->_redisConn->get($cacheKey));
        } catch (Exception $e) {
            return false;
        }
    }

    public function set($key, $value, $ttl = null)
    {
        $this->_sanitizeKey($key);
        $cacheKey = $this->_buildCacheKey($key);
        try {
            return $this->_redisConn->setex($cacheKey, $ttl ?? $this->_defaultTtl, serialize($value));
        } catch (Exception $e) {
            return false;
        }
    }

    public function delete($key)
    {
        $this->_sanitizeKey($key);
        $cacheKey = $this->_buildCacheKey($key);
        try {
            $this->_redisConn->del($cacheKey);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    public function clear()
    {
        try {
            $matchingKeys = $this->_redisConn->keys(self::CACHE_KEY_PREFIX . '*');
        } catch (Exception $e) { return false; }

        if (empty($matchingKeys)) { return true; }

        try {
            $this->_redisConn->del(...$matchingKeys);
            return true;
        } catch (Exception $e) { return false; }
    }

    public function getMultiple($keys, $default = null)
    {
        $result = [];
        foreach ($keys as $key) {
            $this->_sanitizeKey($key);
            if ($found = $this->get($key, $default)) {
                $result[] = $found;
            }
        }
        return $result;
    }

    public function setMultiple($values, $ttl = null)
    {
        foreach ($values as $pair) {
            foreach ($pair as $key => $value) {
                $this->_sanitizeKey($key);
                $successOnSet = $this->set($key, $values, $ttl);
                if (!$successOnSet) { return false; }
            }
        }
        return true;
    }

    public function deleteMultiple($keys)
    {
        foreach ($keys as $key) {
            $this->_sanitizeKey($key);
            $successOnDelete = $this->delete($key);
            if (!$successOnDelete) { return false; }
        }
        return true;
    }

    public function has($key)
    {
        $this->_sanitizeKey($key);
        $cacheKey = $this->_buildCacheKey($key);
        return (bool)$this->_redisConn->exists($cacheKey);
    }

    private function _buildCacheKey(string $key): string
    {
        return self::CACHE_KEY_PREFIX . $key;
    }

    private function _sanitizeKey($key): void
    {
        if (!is_string($key)) {
            throw new ExcelRenderInvalidArgumentException('The key SHOULD be strings.');
        }
    }
}

 

 

2. Render 

<?php

namespace Path\To\ExcelRender;

use Path\To\ExcelRendererCache;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Settings as ExcelRenderSettings;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;

/**
 * Generates a excel file caching in redis the cell representation
 *
 * @package Path\To\ExcelRender
 */
class ExcelRender
{
    /**
     * @var ExcelRendererCache
     */
    private $_renderCache;

    /**
     * ExcelRender constructor.
     *
     * @param ExcelRendererCache $renderCache Implementation of cache that should be used for cell collection.
     */
    public function __construct(ExcelRendererCache $renderCache)
    {
        ExcelRenderSettings::setCache($renderCache);
    }

    /**
     * @throws WriterException
     */
    protected function export(): void
    {
        $excel     = new Spreadsheet();
        $csvWriter = new Xlsx($excel);
        $csvWriter->save('filename.xlsx');
    }
}

 

3. Use

$redisHost = 'host';
$redisPassword = 'password';
$ttl = 600;
$cache = new ReportExcelCache($redisHost, $ttl, $redisPassword);
$render = new ExcelRender($cache);
$render->export();

 

Conclusion

Although this technique prevents PHP memory throttling issues from also affecting the latency between the web server and the Redis server. I suggest it be used in those cases where the file to be exported is really BIG and causes that kind of problem.

Good luck!

Yenier Jimenez Morales.