PHPExcel very slow - ways to improve?

Question

I am generating reports in .xlsx using PHPExcel. It was okay in the initial testing stages with small data sets (tens of rows, 3 sheets), but now when using it on a real production data with over 500 rows in each sheet, it becomes incredibly slow. 48 seconds to generate a file, and when running a report that combines more information, the whole thing fails with Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041. Sometimes it's in another PHPExcel file, so I doubt the exact location is that relevant.

Ideally, I would want to speed it up somehow, if possible. If not, then at least increase the execution limit for this script.

The only suggestions I have seen so far was to style in ranges instead of individual cells. Unfortunately, I already do my styling in ranges and it is rather minimal too. Any other suggestions?

Answer 1

Is it populating the worksheet? or saving? that you find too slow?

How are you populating the spreadsheet with the data?

• Using the fromArray() method is more efficient than populating each individual cell, especially if you use the Advanced Value Binder to set cell datatypes automatically.

• 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);
  

You've commented on applying styles to ranges of cells:

• You also have the option to use applyFromArray() to set a whole variety of style settings in one go.
• It's a lot more efficient if you can apply styles to a column or a row rather than simply to a range

If you're using formulae in your workbook, when saving:

• Use

  $objWriter->setPreCalculateFormulas(false)
  

  to disable calculating the formulae within PHPExcel itself.

Those are just a few hints to help boost performance, and there's plenty more suggested in the forum threads. They won't all necessarily help, too much depends on your specific workbook to give any absolutes, but you should be able to improve that slow speed. Even the little notebook that I use for development can write a 3 worksheet, 20 column, 2,000 row Excel 2007 file faster than your production server.

EDIT

If it was possible to simply improve the speed of PHPExcel itself, I'd have done so long ago. As it is, I'm constantly performance testing to see how its speed can be improved. If you want faster speeds than PHPExcel itself can give, then there's a list of alternative libraries here.

Answer 2

I ran into this issue as well. Thought I'd throw my two cents in since this question gets so many views.

Setting Cell Values

Instead of setting the value for each cell individually, use the fromArray() method. Taken and modified from the wiki.

$arrayData = array(
array(NULL, 2010, 2011, 2012),
array('Q1',   12,   15,   21),
array('Q2',   56,   73,   86),
array('Q3',   52,   61,   69),
array('Q4',   30,   32,    0),
);

$as = $objPHPExcel->getActiveSheet();

$as->fromArray(
    $arrayData,  // The data to set
    NULL,        // Array values with this value will not be set
    'C3'         // Top left coordinate of the worksheet range where
                 //    we want to set these values (default is A1)
);

Styling Cells

Static

It is also quicker to apply the styles for a range, than to set the style for each cell individually (noticing a pattern??).

$default_style = array(
    'font' => array(
        'name' => 'Verdana',
        'color' => array('rgb' => '000000'),
        'size' => 11
    ),
    'alignment' => array(
        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
    ),
    'borders' => array(
        'allborders' => array(
            'style' => \PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => 'AAAAAA')
        )
    )
);

// Apply default style to whole sheet
$as->getDefaultStyle()->applyFromArray($default_style);

$titles = array(
    'Name',
    'Number',
    'Address',
    'Telephone'
);

$title_style = array(
    'font' => array(
        'bold' => true
    ),
    'fill' => array(
        'type' => \PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array('rgb' => '5CACEE')
    ),
    'alignment' => array(
        'wrap' => true
    )
);

$as->fromArray($titles, null, 'A1'); // Add titles

$last_col = $as->getHighestColumn(); // Get last column, as a letter

// Apply title style to titles
$as->getStyle('A1:'.$last_col.'1')->applyFromArray($title_style);

Dynamically

I use PHPExcel to check the data given in the spreadsheet with the current data in the database. Since each cell is checked individually, I put the styles in an array (null for no style), and used the loop below to get the range of cells to apply the style to.

/*
 * $row is previously set in a loop iterating through each 
 *     row from the DB, which is equal to a spreadsheet row.
 * $styles = array(0 => 'error', 1 => 'error', 2 => null, 3 => 'changed', ...);
 */
$start = $end = $style = null;
foreach ($styles as $col => $s) {
    if (!$style && !$s) continue;
    if ($style === $s) {
        $end = $col;
    } else {
        if ($style) {
            $array = null;
            switch ($style) {
                case 'changed':
                    $array = $this->changed_style;
                    break;
                case 'error':
                    $array = $this->error_style;
                    break;
                case 'ignored':
                    $array = $this->ignored_style;
                    break;
            }
            if ($array) { 
                $start = \PHPExcel_Cell::stringFromColumnIndex($start);
                $end = \PHPExcel_Cell::stringFromColumnIndex($end);
                $as->getStyle($start.$row.':'.$end.$row)->applyFromArray($array);
            }
        }
        $start = $end = $col;
        $style = $s;
    }
} 

Answer 3

I was running into the same issue - had about 450 rows with 11 columns of data that I was trying to write, and I kept running up against the 30-second timeout. I was able to get the execution time down to 2 seconds or less by adding all of my new rows in bulk, and then going through and setting the cell content after the fact. In other words, I insert 450 rows in one call to insertNewRowBefore(), and then loop through and set content within those rows later.

Like so:

$num_rows = count($output_rows);
$last_row = $sheet->getHighestRow();
$row = $last_row + 1;
$sheet->insertNewRowBefore($row, $num_rows);
// Now add all of the rows to the spreadsheet
foreach($output_rows as $line) {
    $i = 0;
    foreach($line as $val) {
        // Do your setCellValue() or setCellValueByColumnAndRow() here
        $i++;
    }
    $row++;
}