加快导出phpExcel的速度

Question

我需要帮助，我有许多10到30列和20000或更多行的报告，mi驱动程序是mongodb，结果查询非常快，但当使用库PHPExcel创建文档工作表太慢时，大约需要5到10分钟或更长时间，我的代码只有一个桶：

    $row++;
    $contUser = 0;
    foreach ($data as $index => $objUser) {
        $backgroundColor = $colorOdd;
        if ($contUser % 2 == 0) {
            $backgroundColor = $colorPair;
        }
        $field = $objUser->getFields();
        $column = 0;
        //case 'N°':
        $sheet->setCellValueByColumnAndRow($column, $row, $contUser + 1);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));

        $column++;
        //case matricula:
        $sheet->setCellValueByColumnAndRow($column, $row, $objUser->getChrUsername());
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        //case matricula:
        $sheet->setCellValueByColumnAndRow($column, $row, $field['appaterno']['value']);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        //case matricula:
        $sheet->setCellValueByColumnAndRow($column, $row, $field['apmaterno']['value']);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        //case matricula:
        $sheet->setCellValueByColumnAndRow($column, $row, $objUser->getChrFirstname());
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));

        //$field['dni']['value']
        $column++;
        $type = PHPExcel_Cell_DataType::TYPE_STRING;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['dni']['value'], $type);
        //$sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        //$field['sociedad']['value']
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['sociedad']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['gerenciacentral']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['division']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['area']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['servicio']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['funcion']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['fecha']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['divpersonal']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['subpersonal']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['superior']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['matsuperior']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['estado']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['iniestado']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($field['finestado']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($objUser->getChrEmail(), $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit(empty($field['agencia']['value'])?'':$field['agencia']['value'], $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));

        $date = '';
        $time = '';
        if(!empty($this->lastAccessQuiz) && $this->lastAccessQuiz>0){
            $date = date("Y-m-d", $this->lastAccessQuiz);
            $time = date("H:i:s", $this->lastAccessQuiz);
        }
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($date, $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $column++;
        $sheet->getCellByColumnAndRow($column, $row)->setValueExplicit($time, $type);
        $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        $sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($styleArray);
        $sheet->getStyleByColumnAndRow($column, $row)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => $backgroundColor)));
        $row++;
        $contUser++;
    }

Answer 1

首先，不要为编写的每个单元格设置列样式(如setAutoSizing true)。写入所有数据，然后对该列应用自动调整大小。

与单元格样式类似...它们都是相同的，所以不要将它们应用于每个单独的单元格，而是在完成单个单元格的编写后应用于整个范围。如果它是行的背景色，请应用于行范围，但不是每个单独的单元格。