PHPExcel write rows as is , without any calculation / styling

Question

Is there a way to tell PHPExcel to just write rows supplied from an array, without doing any calculation / apply styling / any other thing it does while writing OR when using fromArray ?

Need this for performance.

        $inputFileName = 'client_files/sample.xlsx';
        $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

        $objPHPExcel->getSheet(0)->setCellValue('D2', '@' . $user . ' followers');
        $objPHPExcel->getSheet(0)->fromArray(
            $followersData,
            NULL,
            'A5'
        );
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->setPreCalculateFormulas(false);

        $objWriter->save(FINAL_FOLDER . '/' . $line[0] . '.xlsx');

Memory consumption isn't an issue. But the above is just taking too much time (2 minutes with 2700 rows)

the ->save() call takes 93 seconds. The ->fromArray() takes 53 seconds

Also is there any other wayy faster Excel library that allows loading existing xlsx and then writing to it ?

Thanks

There's a [lot of alternatives](http://stackoverflow.com/questions/3930975/alternative-for-php-excel) though they won't all read ___and ___ write, or work with xlsx files — Mark Baker, May 05 '15 at 07:13
If you don't want styles, have you looked at applying `setReadDataOnly(true)` to the Reader? — Mark Baker, May 05 '15 at 07:15
Have you considered that if you don't want any kind of styling or calculation, then you probably only want to create a CSV file using standard PHP fputcsv() functions? — Mark Baker, May 05 '15 at 07:16
I want to retain styles of the existing excel file. and paste rows that don't have any style/calculation/formula associated with those rows. — gyaani_guy, May 05 '15 at 09:12
is there absolutely anything that can be done ? I went through the source, but couldn't pinpoint where exactly the issue is.. — gyaani_guy, May 05 '15 at 09:14
No there isn't, an Excel cell must have styling of some kind, even if it's just the default styling (which it will be if you're not explicitly setting any style) — Mark Baker, May 05 '15 at 09:26
Using `$objWriter->setPreCalculateFormulas(false);` will prevent the PHPExcel Writer from checking if a cell contains a formula and hence prevent it from calculating the result to store in the celldata in the resulting file, you're already doing that — Mark Baker, May 05 '15 at 09:28

score 0 · Answer 1 · answered May 06 '15 at 00:27

You can try using Spout. If you don't care about styling/calculation, it should solve your performance problem (it takes only a few seconds).

Something along these lines should work:

$inputFileName = 'client_files/sample.xlsx';
$reader = ReaderFactory::create(Type::XLSX);
$reader->open($inputFileName);

$outputFileName = FINAL_FOLDER . '/' . $line[0] . '.xlsx';
$writer = WriterFactory::create(Type::XLSX);
$writer->openToFile($outputFileName);

$reader->nextSheet();

$rowCount = 0;
while ($reader->hasNextRow()) {
    $row = $reader->nextRow();

    if ($rowCount === 1) {
        $row[1] = '@' . $user . ' followers';
    }

    $followersDataForCurrentRow = $followersData[$rowCount];
    $columnIndexStart = 4; // To add stuff in the 5th column

    foreach ($followersDataForCurrentRow as $followerValue) {
        $row[$columnIndexStart] = $followerValue;
        $columnIndexStart++;
    }

    $writer->addRow($row);

    $rowCount++;
}

$reader->close();
$writer->close();

score 0 · Accepted Answer · answered May 06 '15 at 12:32

I did a bunch of things that resulted in wayyyy faster performance.

ran the script outside the IDE
set memory limit to 3GB
Used a different version of PHP

Fixed memory leak

$objPHPExcel->disconnectWorksheets() ;
unset($objPHPExcel) ;

I am not sure what solved the issue..

PHPExcel write rows as is , without any calculation / styling

2 Answers2