PHPExcel very slow - ways to improve?

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++;
}

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;
    }
}

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.

I am in no means an expert in using PHPExcel, but the OfficeOpenXML format (the format of *.xlsx files) is itself a group of XML files packed in ZIP archive with *.xlsx extension. If you value your performance and know what kind of data you will be passing, maybe it is a better idea to build own XLSX generator, stripped down to the most important functions, maybe making some calculations on database layer etc. instead of parsing the whole document.

To do it, you can begin with analyzing files generated using smaller data sets (by changing extension from *.xlsx into *.zip, unpacking it and browsing through the contents of the single files). That way you could determine what you really need and generate it yourself (by creating appropriate XML files and packing them into ZIP archive, then renaming to have *.xlsx extension).

There is also specification of OfficeOpenXML, which is large (a couple thousands of pages), thus I do not propose reading it unless you really want to. Creating files to match the way they were generated by PHPExcel should be enough.

The solution mentioned above does not include any PHPExcel-related tips, because I am not an expert in it. I have been previously interested in OOXML standarization process however, and would be happy if knowledge about this standard would help you solve your problem.

PHPExcel very slow - ways to improve?

Setting Cell Values

Styling Cells

Tags:

Php

Spreadsheet

Phpexcel

Related

Recent Posts