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.