phpexcel脚本耗时太长

Question

i am writing some data to an excel sheet via phpexcel. The resulting file contains 500 lines and about 35 columns. It's taking two minutes + to run the script , is there any way to optimize it ? thank you

Below is my script

    require_once 'lib_phpexcel/PHPExcel.php';
ini_set('memory_limit', "512M");
ini_set('max_execution_time', 800);

$objPHPExcel = new PHPExcel();

// proprietes documents
$objPHPExcel->getProperties()->setCreator(utf8_encode("COCPIT"))
->setTitle(utf8_encode("COCPIT - Cohérence"))
->setSubject(utf8_encode("COCPIT - Cohérence"))
->setDescription(utf8_encode("COCPIT - Cohérence"));

$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();


$index_ligne = 4;
$res = mysql_query("SELECT * FROM $database.TEMP_CatalogueSI_RPS LIMIT 2, 999999999999") or die (mysql_error());
while($row = mysql_fetch_row($res)){
    $index_colonne = 0;
    foreach($row as $value){
        $range_colonne = getColonne(++$index_colonne);
        $id_cell = $range_colonne . $index_ligne;
        $sheet->setCellValue($id_cell, utf8_encode($value));
        $sheet->getStyle($id_cell)->applyFromArray($styleCelluleColonneInfos);

        // Pour les 8 premières colonnes => on est sur des colonnes 'fixes'
        if($index_colonne > 8){
            if(strcasecmp($value, "X") === 0){
                $sheet->getStyle($id_cell)->getFill()->getStartColor()->setRGB('CCFFCC');
            }
            else{
                $sheet->getStyle($id_cell)->getFill()->getStartColor()->setRGB('C0C0C0');
            }
        }
    }

    $index_ligne++;
}

$file = "db/$database/TEMP_CatalogueSI_RPS.xls";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($file);

Answer 1

Step #1

Instead of setting each cell value individually in the foreach($row as $value) loop, use the fromArray() method to write a whole row of cells at a time. That reduces 500x35=17,500 calls to 500.

while($row = mysql_fetch_row($res)) {
    // use array_walk() to utf-encode each value in the row
    array_walk($row, 'utf8_encode');
    // write the entire row to the current worksheet
    $sheet->fromArray($row, NULL, 'A' . $index_ligne);
    // increment row number
    $index_ligne++;
}

Step #2

Don't set each cell style individually with

$sheet->getStyle($id_cell)->applyFromArray($styleCelluleColonneInfos);

but set the entire range of cells in a single call.

$sheet->getStyle('A4:AI503')->applyFromArray($styleCelluleColonneInfos);

That reduces 500x35=17,500 calls to 1.

Step #3

Rather than setting different styles based on

if(strcasecmp($value, "X") === 0){

Use an Excel condition style, and again apply it to the whole range of cells, not to each individual cell.

That reduces 500x27=13,500 calls to 1.