PHP Excel performance is slow

1.8k views Asked by At

I want to generate an excel sheet which contain some default fields and variable number of comment fields and data related to comment. I want to set specific width and wrap text for comment fields.Righ now I am using this code

 $excelObj = new \PHPExcel();     
  $ews = $excelObj->getSheet(0);
  $ews->setTitle('SurveyDetails');

  $ews->fromArray($header, ' ', 'A1'); //Write the header from array
  $ews->fromArray($content_arr, ' ', 'A2'); // Write the content from array

  $header_style = array(
      'font' => array('bold' => true,),
      'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER),
  );

  $content_style = array(
      'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER),
  );

  $start_end_columns = $excelObj->setActiveSheetIndex(0)->calculateWorksheetDimension();
  preg_match_all('!\d+!', $start_end_columns, $matches);
  $numbers = implode(':', $matches[0]);
  $columns = explode(":", $numbers);
  $header_end_column_no = $columns[1];
  $header_start_end = str_replace($header_end_column_no, 1, $start_end_columns); // A1:G1

  $ews->getStyle($header_start_end)->applyFromArray($header_style);
  $start_end_columns = str_replace("A1", "A2", $start_end_columns); // Start column of content changed A1 to A2
  $ews->getStyle($start_end_columns)->applyFromArray($content_style);

  $cols = explode(":", $start_end_columns);
  $endColmn = $cols[1];
  $endColmn = preg_replace('/[0-9]+/', '', $endColmn);

  $activeSheetObj = $excelObj->getActiveSheet();

  //Set the autosize height for all the cells
  $activeSheetObj->getDefaultRowDimension()->setRowHeight(-1);

  $this->log(sprintf("Before set hyper link excel projectId : %s, surveyId : %s", $projectId, $survey_id), LogLevel::INFO);
  //Set the hyperlink in the path field
  $i = 2;
  $count = count($content_arr);
  foreach ($content_arr as $content) {
    $activeSheetObj->getCell('F' . $i)->getHyperlink()->setUrl($content['path']);
    $i++;
  }

  $activeSheetObj->getStyle('G'.'2:'.'G'.$count)->getAlignment()->setWrapText(true); 
  $this->log(sprintf("After setting hyper link projectId : %s, surveyId : %s", $projectId, $survey_id), LogLevel::INFO);
  //Set specific width for the note and comment fields
  $activeSheetObj->getColumnDimension('G')->setWidth(35);
  $l = 0;     

  if (strcmp('G', $endColmn) != 0) {
    for ($col = 'J'; $col != $endColmn; $col++) {
      if ($l % 3 == 0) {
        $activeSheetObj
                ->getColumnDimension($col)
                ->setWidth(35);
        $activeSheetObj->getStyle($col.'2:'.$col.$count)->getAlignment()->setWrapText(true); 
      }
      $l++;
    }

    $activeSheetObj->getColumnDimension($endColmn)->setWidth(35);
    $activeSheetObj->getStyle($endColmn.'2:'.$endColmn.$count)->getAlignment()->setWrapText(true); 
  }
  unset($content_arr);
  $this->log(sprintf("After setting width projectId : %s, surveyId : %s", $projectId, $survey_id), LogLevel::INFO);


  $writer = \PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
  //Save to perticular location      
  $keyname = "videos/Export/" . $projectId . "/" . $fileName;
  $writer->save('/home/senchu/Documents/Infrass.xlsx');

But it takes about 3 seconds to complete the process on 10 columns and 400 rows. Is there any way to optimize this code so that to increase the performance. When I tried caching I didn't get much performance improvement.

Instead of iterating each row and setting hyper link like

$activeSheetObj->getCell('F' . $i)->getHyperlink()->setUrl($content['path']); 

is there any method to set hyper link from array.so that we can avoid this much number of iterations.

0

There are 0 answers