Search style by identity in PHPExcel_Worksheet::duplicateStyle()

[amironov]

Increases duplicateStyle performance.

Run Examples/40duplicateStyle.php.

before

[Examples]$ php 40duplicateStyle.php
08:35:05 Create new PHPExcel object
08:35:05 Create styles array
08:35:05 Add data (begin)
08:35:18 Add data (end), time: 12.96 s
08:35:18 Write to Excel2007 format
08:35:20 File written to 40duplicateStyle.xlsx
08:35:20 Peak memory usage: 12 MB
08:35:20 Done writing file
File has been created in /builddir/PHPExcel/Examples

after

[Examples]$ php 40duplicateStyle.php
08:36:19 Create new PHPExcel object
08:36:19 Create styles array
08:36:19 Add data (begin)
08:36:23 Add data (end), time: 3.27 s
08:36:23 Write to Excel2007 format
08:36:25 File written to 40duplicateStyle.xlsx
08:36:25 Peak memory usage: 12 MB
08:36:25 Done writing file
File has been created in /builddir/PHPExcel/Examples

[MarkBaker]

Thanks: A nice performance boost, and the PHPExcel::cellXfExists() call can also be used in the applyFromArray() method of PHPExcel_Style as well.

[amironov]

For applyFromArray() this technique is not applicable. Cloned $newStyle is new object and cellXfExists() will always return false.

[karak]

In my use case, this fix causes memory exhaust.
Combined use is safe like following:

    if ($this->_parent->cellXfExists($pCellStyle)) {
        // there is already this cell Xf in our collection
        // note that check by reference is much faster than by hash code
        $xfIndex = $pCellStyle->getIndex();
    } else if ($existingStyle = $this->_parent->getCellXfByHashCode($pCellStyle->getHashCode())) {
        // there is already such cell Xf in our collection
        $xfIndex = $existingStyle->getIndex();
    } else {
        // we don't have such a cell Xf, need to add
        $workbook->addCellXf($pCellStyle);
        $xfIndex = $pCellStyle->getIndex();
    }

[amironov]

Describe your use case. How do you create new styles? Once in program like in 40duplicateStyle.php or every time before duplicateStyle()?

[karak]

My use is 'row copy' like following:

1. get one style from cell 'A1' and duplicate to 'A2:A20'
2. get another style from cell 'B1' and duplicate to B2:B20'
3. and so forth.

Actually I'm also confused that your fix has introduced lack of consistency in equality comparison.

[karak]

@MarkBaker

I adopted an altenative way to boost applyFromArray(),
by using "rough" equality checking before execute getHashCode().

Replace getHashCode() by following getEqualCellXf().
It could give 10x speed to the function statically; it would depend on your use of course.

/**
 * faster version of PHPExcel_Workbook::getCellXfByHashCode() in use case in this class
 * @param PHPExcel $workbook
 */
private static function getEqualCellXf($workbook, $other)
{
    $hashCode = null;
    foreach ($workbook->getCellXfCollection() as $cellXf) {
        if ($cellXf->canBeEqual($other)) {
            if (is_null($hashCode)){
                $hashCode = $other->getHashCode();
            }

            if ($cellXf->getHashCode() == $hashCode) {
                return $cellXf;
            }
        }
    }
    return false;
}

/**
 * rough equality check in order to reject in shorter time.
 */
private function canBeEqual($other) {
    //equality test by sampling -- frequently changed
    return $this->_fill->getFillType() === $other->_fill->getFillType() &&
           $this->_fill->getStartColor() === $other->_fill->getStartColor() &&
           $this->_font->getColor() === $other->_font->getColor() &&
           $this->_borders->getLeft()->getStyle() === $other->_borders->getLeft()->getStyle() &&
           $this->_borders->getTop()->getStyle() === $other->_borders->getTop()->getStyle() &&
           $this->_borders->getRight()->getStyle() === $other->_borders->getRight()->getStyle() &&
           $this->_borders->getBottom()->getStyle() === $other->_borders->getBottom()->getStyle() &&
           $this->_alignment->getHorizontal() === $other->_alignment->getHorizontal() &&
           $this->_alignment->getVertical() === $other->_alignment->getVertical();
}

[amironov]

@karak
The answer was in the first line of duplicateStyle(). See #101.

[amironov]

@karak & @MarkBaker

I adopted an altenative way to boost applyFromArray()...

It is better to extend the interface of PHPExcel_IComparable with equals() method and use it for styles searching.

In general use getHashCode() for equality is very strange.

[karak]

The answer was in the first line of duplicateStyle(). See #101.

Thank you for your fix.
I found "shared component" is used in my case as you indicated.