Merging slows down script

Topics: Developer Forum, Project Management Forum, User Forum
Apr 5, 2010 at 7:28 PM

Hi,

I'm using PHPExcel on my reporting engine and is working perfect, it writes 590 excel rows with 14 columns in 16 seconds for composing the excel and 7.5 seconds to write the Excel2007 file.

but the main problem is in Merging cells, when i start using this line of code

$this->objPHPExcel->getActiveSheet()->mergeCells("A351:N351");

it takes same composing time, but writing Excel2007 time has dramatically jumped from 7.5 seconds to become 225 seconds.

by examining the merge container with this code

var_dump($this->objPHPExcel->getActiveSheet()->getMergeCells());
'A2:N2' => string 'A2:N2' (length=5)
  'A7:K7' => string 'A7:K7' (length=5)
  'L7:N7' => string 'L7:N7' (length=5)

 

it contains about 70 entries, looks like those following:

'A2:N2' => string 'A2:N2' (length=5)

'A7:K7' => string 'A7:K7' (length=5)

'L7:N7' => string 'L7:N7' (length=5)

 

 

I need to know is that an error in my code, noticing that when commenting merging line, writing Excel2007 speeds up to normal time again, or there is something that i shall do to speed up the performance?!

OS: windows 7 Professional

PHP: 

PHP 5.2.11 (cli) (built: Sep 16 2009 19:39:46)

Copyright (c) 1997-2009 The PHP Group

Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies

    with Xdebug v2.0.5, Copyright (c) 2002-2008, by Derick Rethans

Apache HTTP:
Server version: Apache/2.2.14 (Win32)
Server built:   Sep 28 2009 22:41:08
Beast Regards,
Mohamed Nabil

Apr 5, 2010 at 8:22 PM

Sample code performance results, it might help

 

 

 

When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
//$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
//$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 0.5611422061920166
Excel writing Time = 0.1078641414642334
When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
//$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 16.51956081390381
Excel writing Time = 7.201866865158081
When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
//$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 2.914301156997681
Excel writing Time = 136.3055419921875
When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
//$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 14.79245638847351
Excel writing Time = 207.0156230926514
When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 16.50382709503174

Excel writing Time = 225.4184210300446

 

 

When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

//$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

//$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 0.5611422061920166

Excel writing Time = 0.1078641414642334

 

 

When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

//$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 16.51956081390381

Excel writing Time = 7.201866865158081

 

When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

//$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 2.914301156997681

Excel writing Time = 136.3055419921875

 

When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

//$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 14.79245638847351

Excel writing Time = 207.0156230926514

 

When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 16.50382709503174

Excel writing Time = 225.4184210300446

 

Developer
Apr 5, 2010 at 8:45 PM

Are you using any autosize columns?

Apr 5, 2010 at 8:55 PM
Edited Apr 5, 2010 at 9:02 PM

WOW, Yes i do?

What is the relation!

 

When executing this code:
if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row
$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));
}
$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));
$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);
result:
Excel composing Time = 15.43027901649475
Excel writing Time = 3.040853023529053

Now When executing this code:

if ($this->shallMerge() == true) {// if it is more than 1 comlumn or more than 1 row

$this->objPHPExcel->getActiveSheet()->mergeCells($this->getExcelRange($rowID));

}

$this->objPHPExcel->getActiveSheet()->setSharedStyle($this->getExcelStyle($this->getGroupRowID()), $this->getExcelRange($rowID));

$this->objPHPExcel->getActiveSheet()->setCellValue($this->getExcelCell($rowID), $output);

result:

Excel composing Time = 15.43027901649475

Excel writing Time = 3.040853023529053

 

But column widths are not auto any more

 

Developer
Apr 5, 2010 at 9:53 PM

Work item created:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=12509

Replace this in PHPExcel/Worksheet.php

/**
     * Calculate widths for auto-size columns
     *
     * @param  boolean  $calculateMergeCells  Calculate merge cell width
     * @return PHPExcel_Worksheet;
     */
    public function calculateColumnWidths($calculateMergeCells = false)
    {
        // initialize $autoSizes array
        $autoSizes = array();
        foreach ($this->getColumnDimensions() as $colDimension) {
            if ($colDimension->getAutoSize()) {
                $autoSizes[$colDimension->getColumnIndex()] = -1;
            }
        }

        // There is only something to do if there are some auto-size columns
        if (count($autoSizes) > 0) {
            // loop though all cells in sheet expand $autoSizes
            foreach ($this->getCellCollection(false) as $cell) {
                if (isset($autoSizes[$cell->getColumn()])) {
                    // Calculated value
                    $cellValue = $cell->getCalculatedValue();

                    // To formatted string
                    $cellValue = PHPExcel_Style_NumberFormat::toFormattedString($cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());

                    foreach ($this->getMergeCells() as $cells) {
                        if ($cell->isInRange($cells) && !$calculateMergeCells) {
                            $cellValue = ''; // do not calculate merge cells
                        }
                    }

                    $autoSizes[$cell->getColumn()] = max(
                        (float)$autoSizes[$cell->getColumn()],
                        (float)PHPExcel_Shared_Font::calculateColumnWidth(
                            $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
                            $cellValue,
                            $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
                            $this->getDefaultStyle()->getFont()
                        )
                    );
                }
            }

            // adjust column widths
            foreach ($autoSizes as $columnIndex => $width) {
                if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth();
                $this->getColumnDimension($columnIndex)->setWidth($width);
            }
        }

        return $this;
    }

With this:

 

/**
     * Calculate widths for auto-size columns
     *
     * @param  boolean  $calculateMergeCells  Calculate merge cell width
     * @return PHPExcel_Worksheet;
     */
    public function calculateColumnWidths($calculateMergeCells = false)
    {
        // initialize $autoSizes array
        $autoSizes = array();
        foreach ($this->getColumnDimensions() as $colDimension) {
            if ($colDimension->getAutoSize()) {
                $autoSizes[$colDimension->getColumnIndex()] = -1;
            }
        }

        // There is only something to do if there are some auto-size columns
        if (!empty($autoSizes)) {

            // build list of cells references that participate in a merge
            $isMergeCell = array();
            foreach ($this->getMergeCells() as $cells) {
                foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }

            // loop through all cells in the worksheet
            foreach ($this->getCellCollection(false) as $cell) {
                if (isset($autoSizes[$cell->getColumn()])) {
                    // Determine width if cell does not participate in a merge
                    if (!isset($isMergeCell[$cell->getCoordinate()])) {
                        // Calculated value
                        $cellValue = $cell->getCalculatedValue();

                        // To formatted string
                        $cellValue = PHPExcel_Style_NumberFormat::toFormattedString($cellValue, $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());

                        $autoSizes[$cell->getColumn()] = max(
                            (float)$autoSizes[$cell->getColumn()],
                            (float)PHPExcel_Shared_Font::calculateColumnWidth(
                                $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont(),
                                $cellValue,
                                $this->getParent()->getCellXfByIndex($cell->getXfIndex())->getAlignment()->getTextRotation(),
                                $this->getDefaultStyle()->getFont()
                            )
                        );
                    }
                }
            }

            // adjust column widths
            foreach ($autoSizes as $columnIndex => $width) {
                if ($width == -1) $width = $this->getDefaultColumnDimension()->getWidth();
                $this->getColumnDimension($columnIndex)->setWidth($width);
            }
        }

        return $this;
    }


Or just wait until tomorrow and download latest source code from here:

http://phpexcel.codeplex.com/SourceControl/list/changesets

Apr 5, 2010 at 10:27 PM
Edited Apr 5, 2010 at 10:28 PM

wonderful code

I've a suggestion regarding this block of code

// build list of cells references that participate in a merge
$isMergeCell = array();
foreach ($this->getMergeCells() as $cells) {
    foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
        $isMergeCell[$cellReference] = true;
    }
}


I suggest if you move it to mergeCells() function, once I merge, the list updated on time.


foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
    $isMergeCell[$cellReference] = true;
}

I think this will reduce the execution time of calculateColumnWidths()