A trimmed down version of PHPExcel?

Topics: Developer Forum
Mar 1, 2012 at 3:50 PM

I understand that PHPExcel has a lot of features for spreadsheet generation and manipulation. However, I have an instance where I need to create lots of sheets and files without any bells and whistles, in OOXML format. Unfortunately, for the bare bones, PHPExcel isn't doing so swift.

Is there any alternative writer that does nothing special: just writes values to cells, and exports in OO XML?

To give you an idea my problems with PHPExcel; I ran tests on a single workbook with one worksheet consisting of 6.2k rows, 18 columns, no formulas, and column 18 formatted with $#,##0.00.

Using Spreadsheet_Excel_Writer: ~.5 seconds to write the worksheet. ~.3 to export (->close())

Using PHPExcel: ~10 seconds to write the worksheet. ~30 seconds to export (->save('php://output')) (using Excel5 and Excel2007 writer: no difference in general speed)

Those numbers are very consistent throughout all my tests.

In PHPExcel, I'm using setCellValueExplicit() on every cell -- it's the fastest method I have found. fromArray() actually showed everything down considerably (~30 seconds). I don't use any formulas; it's all direct value writing as number or as string.

How is it that Spreadsheet_Excel_Writer can be, by orders of magnitude, so much faster? Unfortunately, SEW doesn't support OOXML.

Coordinator
Mar 1, 2012 at 6:12 PM
Edited Mar 1, 2012 at 6:14 PM

There are plenty of alternative writers... I maintain a list in response to a question on Stack Overflow, and if all you want is speed without the bells and whistles, then you really are better off using one of those (for the moment) with a few caveats: many of those alternatives were originally written for earlier versions of PHP, and so may use deprecated features.... so you do need to watch out for warnings about this. Unfortunately, with the exception of COM and PUNO, I'm not aware of any other PHP libraries that will write OfficeOpenXML.

Why is something like SEW faster? Because it does a lot less. It doesn't need to test if certain Excel features are being used to decide whether or not to write the appropriate data to the file because it simply doesn't support those features.

Yes, we know PHPExcel is slow, and a large part of my development time is taken up be trying to ensure that any changes don't impact further on speed, and in reducing the execution time still further where I can. There are options that I've been looking at, but all involve a radical rewrite of the underlying code: something I'm not willing to commit to until I've invested in automating all my regression tests... only then will I be able to ensure that such a fundamental rewrite generates correct output without having to spend months doing so.

WRT setting cell values: don't use

for ($i=1; $i <= 1000; ++$i) {
    $objPHPExcel->getActiveSheet()
                ->setCellValueExplicit('A'.$row, 'Test Data', PHPExcel_Cell_DataType::TYPE_STRING);
}

in a loop. Instead get the active sheet first:

$sheet = $objPHPExcel->getActiveSheet();
for ($i=1; $i <= 1000; ++$i) {
    $sheet->setCellValueExplicit('A'.$row, 'Test Data', PHPExcel_Cell_DataType::TYPE_STRING);
}

It saves on 999 calls to getActiveSheet().

And use the fluent interface where you're making several calls to methods that return the same entity. It can make a big difference.

 

Mar 1, 2012 at 6:48 PM

Cool. I'll look over those. And yeah, I do get activeSheet() first, before looping over my data.

Example of the code: 

        $excel = new \PHPExcel();

        $excel->getProperties()->setCreator("PHP Automated Generator");
        $excel->getProperties()->setLastModifiedBy("PHP Automated Generator");

        $activeSheet = $excel->getActiveSheet();
        $activeSheet->setTitle("Commissions");
		
        /* Headers */
        $activeSheet->setCellValueExplicit('A1', 'CP');
		...

        $i = 2; // Row Index
        foreach ( $commissions as $row ) {
            /** @var $row \Models\XSP\Row */

            // Row output here
            $activeSheet->setCellValueExplicit('A'.$i, $row->getUserId(), PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $activeSheet->setCellValueExplicit('B'.$i, $row->getPartnerCompany());
			...
			
            $i++;

        }

        /* Cell Formatting */
        $activeSheet->getStyle('R2:R'.$i)->getNumberFormat()
                                      ->setFormatCode('$#,##0.00');

        /* Have to set width individually */
        $activeSheet->getColumnDimension('A')->setAutoSize(true);
		...
		
        return \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');