Performance Issue

[Gemineye]

Hi,

I just profiled my Excel generation and found out that 50% of the Time gets consumed by 'getCellXfByHashCode'. After digging a bit deeper I gave up because i did not (and want not) understand why everything has to be hashed over and over again. So my simple Question is:

Is it Possible to cache that hash value?

[KinnaT]

Is this not just PHPExcel running through its loop? It looks like it's grabbing each cell (by hash code, so unique ID), then pulling the HashCode (ID) for the object, then acting on it as a unique entity before moving on to the next. I would assume the time comes from running through the loop to find which is the next cell. If you try it with a very short file (~50 rows), does it still act up?

[Gemineye]

Hi,

I tried to figure it out a bit. If I hash out all the Styling my Excel gets generated in a few seconds. But when I enable it it takes 45 Seconds. So it seems that there is a poor performance when styling a bigger Excel file.

Here is an example that takes 30 seconds (I style some result rows with colored background)

        $xls = new \PHPExcel();

        $thinBlackBorders = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
        );

        for ($i = 0; $i < 1000; $i++)
        {
            if ($i % 2 === 0) {
                $xls->getActiveSheet()->getStyle('A'.$i.':IZ'.$i)->getFill()->getStartColor()->setARGB('FF0000FF');
                $xls->getActiveSheet()->getStyle('A'.$i.':IZ'.$i)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $xls->getActiveSheet()->getStyle('A'.$i.':IZ'.$i)->applyFromArray($thinBlackBorders);
            }
        }

[KinnaT]

So I found this: http://stackoverflow.com/questions/5983845/phpexcel-very-slow-ways-to-improve which discusses performance issues. There's a suggestion of this near the top:

If you're setting values for every individual cell in a sheet using

$objPHPExcel->getActiveSheet()->setCellValue('A1',$x);
$objPHPExcel->getActiveSheet()->setCellValue('B1',$y);

use

$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A1',$x);
$sheet->setCellValue('B1',$y);

so that you're only accessing the getActiveSheet() method once; or take advantage of the fluent interface to set multiple cells with only a single call to $objPHPExcel->getActiveSheet()

$objPHPExcel->getActiveSheet()->setCellValue('A1',$x)
                           ->setCellValue('B1',$y);

Considering the code you gave as an example above, it might be worth a shot.

[Gemineye]

Hi,

I did some tests but your mentioned change does not bring any performance boost :(