phpphpexcelspout

box/spout - Massive memory usage when using hex color vs pre-defined colors

I'm using the Box/Spout library and it seems that using StyleBuilder with a custom hex color (e.g. 0000FF for blue) uses a ton of memory compared to using pre-defined colors such as Color::BLUE. Why would that be?

Relevant snippet:

//LOW MEMORY
$row[] = WriterEntityFactory::createCell('test', (new StyleBuilder())->setFontColor(Color::BLUE)->build());

//HIGH MEMORY
$row[] = WriterEntityFactory::createCell('test', (new StyleBuilder())->setFontColor($colorHex)->build());

Output:

setFontColor(Color::BLUE): Peak memory usage: 1666 KB

setFontColor($colorHex): Peak memory usage: 189436 KB

Full code:

(For demo purposes I'm loading a small 250x150 image to provide multiple color values)

<?php

    require_once 'Spout/Autoloader/autoload.php';
    use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
    use Box\Spout\Common\Entity\Style\Color;
    use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;

    //load an image
    $img = imagecreatefrompng('input/test250x150.png');

    $writer = WriterEntityFactory::createXLSXWriter();
    $writer->openToFile('output/MyExcel.xlsx');

    //height of the image
    for($y=0; $y<150; $y++) {

        //create or reset array to hold this row's cells
        $row = [];

        //width of the image
        for($x=0; $x<250; $x++) {

            //gets the pixel color
            $index = imagecolorat($img, $x, $y);
            $colorRGBArr = imagecolorsforindex($img, $index);
            $colorHex = sprintf("%02x%02x%02x", $colorRGBArr['red'], $colorRGBArr['green'], $colorRGBArr['blue']);

            //LOW MEMORY
            //$row[] = WriterEntityFactory::createCell('test', (new StyleBuilder())->setFontColor(Color::BLUE)->build());
            //HIGH MEMORY
            $row[] = WriterEntityFactory::createCell('test', (new StyleBuilder())->setFontColor($colorHex)->build());

        }
        $writer->addRow(WriterEntityFactory::createRow($row));
    }

    $writer->close();

    echo 'Peak memory usage: '.round(memory_get_peak_usage() / 1024).' KB';
?>
Solution

  • tl;dr

    While the Spout can be improved, Excel isn't designed for large quantities of styles, so this isn't really a flaw of the library (you might want to rescind the issue)

    The Story

    Alright, there's a few things at play here.. the code I used to test is at the bottom of my post - the key relevant color functions are jonEg and jonEgQuant (which is a $by variable inside that can be tuned)

    Take Aways

    Testing notes

    Code

    <?php

require_once 'Spout/Autoloader/autoload.php';
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Style\Color;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;

// -- -- Set this to one of the method names on ColorBuilder (that isn't a helper)
$choice='jonEg';
// -- -- 

class ColorBuilder {
    static $defaultBlue=255;
    static function jonEg($x,$y) {return sprintf("%02x%02x%02x", $x, $y, static::$defaultBlue);}
    static function spoutDoc($x,$y) {return Color::rgb($x, $y, static::$defaultBlue);}
    static function fixedEg($x,$y) {return Color::BLUE;}
    static function jonEgQuant($x,$y) {$by=16;return sprintf("%02x%02x%02x", static::_quantize($x,$by),static::_quantize($y,$by), static::_quantize(static::$defaultBlue,$by));}

    //Helpers - don't use these for choice
    static function validate(string $name):bool {
        if ($name==null) return false;//Null or empty
        if (substr($name,0,1)=='_') return false;//Private by convention
        if ($name==='validate') return false;//Not the function you seek
        return method_exists('ColorBuilder',$name);
    }
    private static function _quantize(int $i,int $by=16):int {return round($i/$by)*$by;}
}

function createRow($y,$color) {
    $colCount=100;
    $row = [];

    for($x=0; $x<$colCount; $x++) {
        $row[] = WriterEntityFactory::createCell('*', (new StyleBuilder())->setBackgroundColor(ColorBuilder::$color($x,$y))->build());
    }
    return $row;
}

function buildSheet($name) {
    if (!ColorBuilder::validate($name)) {throw new Error('Invalid color provider');}

    $writer = WriterEntityFactory::createXLSXWriter();
    $writer->openToFile('output/'.$name.'.xlsx');

    for($y=0; $y<150; $y++) {
        $writer->addRow(WriterEntityFactory::createRow(createRow($y,$name)));
    }

    $writer->close();
}

buildSheet($choice);
echo 'Peak memory usage: '.round(memory_get_peak_usage() / 1024).' KB';

    Tech: PHP 7.4.2 CLI, Spout: 3.1.0, Win: 7 x64 (I know), Coffee: Venti Dark