Export to csv speed issue

Oct 6, 2009 at 7:55 AM

Halp please! :s

I'm trying to export to csv with 6,500+ rows and around 5 columns.. using upto this line of code here:

    // [DATA VALUE ROWS] - place the row values
    $rsData = clsDBManager::executeQuery(array('sql' => $strContentSQL, 'hostname' => $strHostname));
    while($rowVal = assoc_data($rsData))
    {
        $col = 0;
        foreach($arrRowFields as $key)
        {
            $fieldprops = $arrFieldProperties[$key];
            $value = $rowVal[$key];
            if(is_null($value) && $fieldprops['t'] != 'text' && $fieldprops['t'] != 'primary')
                $value = '--'; //if null
            if($key == 'datemonth' || $key == 'dateday' || !is_numeric($value))
                $value = '" '.$value.'"'; //rowtrend
            $value = utf8_encode($value); //TODO: UTF-8 support
            if('none' !== $fieldprops['d'] && $fieldprops['d'] > 0 )
                $value = sprintf('%0.'.$fieldprops['d'] . 'f', $value);
            if($fieldprops['t'] == 'text' || $fieldprops['t'] == 'primary')
               $objActiveSheet->setCellValueExplicitByColumnAndRow($col++, $row, $value);
            else
               setCellValue($objActiveSheet, $col++, $row, $value );
        }
        $row++;
    }
    // Save Excel5 file - these 2 lines of code should always be in the end of the user of this
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
    $objWriter->setDelimiter(',');
    $objWriter->setEnclosure('');
    $objWriter->setLineEnding("\r\n");
    $objWriter->setSheetIndex(0);
    $objWriter->save('php://output');

I'm not sure if it's because of the way i coded it.. but i'm getting execution times of upto 20+ seconds for this.. which is alright, but not too good.
With intentionally removing the setCellValue( ) and setCellValueExplicitlyByColumnAndRow( ) i get 0.7+ seconds.
stopping right before the $objWriter->save( ) i get 9+ seconds.
stopping after $objWriter->save( ) i get 20+ seconds.

currently running this on Windows XP
2.00 GHz CPU
1GB Ram
PHPExcel - 28930

strongly need your advice/help :(

please note that i believe phpexcel is by far the best export library i've seen. i'm not complaining about the above, i just need help on how i can improve on it :)

thanks in advance.

Coordinator
Oct 6, 2009 at 10:37 AM

To be honest, if all you're trying to do is export data from a db query into a csv file, you'd be better advised to do this directly using fputcsv() rather than using PHPExcel.

Oct 6, 2009 at 10:59 AM

hi mark,

thanks i'll try that. :)

sadly, the export module i'm trying to do allows the user to export to Excel5 and csv.. i used the above code to test the csv simply for the performance (since csv's don't use styling).. i may use fputcsv( ) for the export to csv alone.. but problem is .. well the problem would still occur for the export to Excel5 since both of them use the "$objActiveSheet->setCellValueExplicitByColumnAndRow($col++, $row, $value);" part :(

thanks for the tip though