I have this method:
public function createExcel(Task $task = null, Project $project = null, $projectTitle = null)
{
$this->spreadsheet = new Spreadsheet();
$default_border = array(
'style' => Border::BORDER_THIN,
'color' => array('rgb' => '1006A3')
);
$style_header = array(
'borders' => array(
'bottom' => $default_border,
'left' => $default_border,
'top' => $default_border,
'right' => $default_border,
),
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => 'E1E0F7'),
),
'font' => array(
'bold' => true,
'size' => 16,
)
);
$style_content = array(
'borders' => array(
'bottom' => $default_border,
'left' => $default_border,
'top' => $default_border,
'right' => $default_border,
),
'fill' => array(
'type' => Fill::FILL_SOLID,
'color' => array('rgb' => 'eeeeee'),
),
'font' => array(
'size' => 12,
)
);
/**
* Header information of the excel sheet
*/
$this->spreadsheet->setActiveSheetIndex(0)
->setCellValue('A1', 'Zoekterm')
->setCellValue('B1', 'Website')
->setCellValue('C1', 'Pagina')
->setCellValue('D1', 'Paginatitel')
->setCellValue('E1', 'Paginabeschrijving')
->setCellValue('F1', 'KvK-nummer')
->setCellValue('G1', 'Vestigingsnummer')
->setCellValue('H1', 'Adresgegevens');
$this->spreadsheet->getActiveSheet()->getStyle('A1:H1')->applyFromArray($style_header); // give style to header
$dataku = [];
if ($projectTitle == \BoolState::TRUE) {
foreach ($project->tasks as $task)
if ($task->status_id == TaskStatus::CLOSED)
foreach ($task->activeResults as $result)
$dataku[] = [$result->task->search_query, $result->page_url, $result->page_link, $result->page_title, $result->page_description, $result->coc_number, $result->branch_code, $result->address];
} else {
foreach ($task->activeResults as $result)
$dataku[] = [$result->task->search_query, $result->page_url, $result->page_link, $result->page_title, $result->page_description, $result->coc_number, $result->branch_code, $result->address];
}
$firststyle = 'A2';
for ($i = 0; $i < count($dataku); $i ) {
$urut = $i 2;
$search_query = 'A' . $urut;
$page_url = 'B' . $urut;
$page_link = 'C' . $urut;
$page_title = 'D' . $urut;
$page_description = 'E' . $urut;
$coc_number = 'F' . $urut;
$branch_code = 'G' . $urut;
$address = 'H' . $urut;
$this->spreadsheet->setActiveSheetIndex(0)
->setCellValue($search_query, $dataku[$i][0])
->setCellValue($page_url, $dataku[$i][1])
->setCellValue($page_link, $dataku[$i][2])
->setCellValue($page_title, $dataku[$i][3])
->setCellValue($page_description, $dataku[$i][4])
->setCellValue($coc_number, $dataku[$i][5])
->setCellValue($branch_code, $dataku[$i][6])
->setCellValue($address, $dataku[$i][7]);
$laststyle = $page_description;
}
$this->spreadsheet->getActiveSheet()->getStyle($firststyle . ':' . $laststyle)->applyFromArray($style_content); // give style to header
$writer = $this->outType == 'xlsx' ? new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->spreadsheet) : new Xls($this->spreadsheet);
var_dump($this->stream);
if (!$this->stream) {
$writer->save($this->filename);
} else {
$this->filename = $this->filename == null ? $this->title : $this->filename;
$this->cleanOutput();
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Cache-Control: max-age=0');
header('Content-Disposition: attachment;filename="NVWA HDT ' . ucfirst($projectTitle ? $task->project->description : $task->search_query) . '.Xls"'); // file name of excel
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');
Yii::app()->end();
}
}
So the upper part is for then layout of the excel sheet. But is it cleaner to have that in a seperate method?
Thank you
CodePudding user response：
Here is a suggestion. I do not know in which class is your method createExcel(), that is why I put all new defined methods and properties into MyClass.
Please pay attention, that this code contains simple refactorings like avoiding array() keyword in defining arrays. I also avoided to use else{...} blocks if possible by returning in if{...} block. By this way you can reduce the deepness of your if-else logic and the core becomes more readable.
class MyClass {
private $spreadsheet;
private $task = null;
private $project = null;
private $projectTitle = null;
private $style_header = [];
private $style_content = [];
private $dataku = [];
public function createExcel(Task $task = null, Project $project = null, $projectTitle = null) {
$this->initializeProperties($task, $project, $projectTitle);
$this->setHeaderContent();
$this->setMainContent($task, $project, $projectTitle);
$this->doOutput();
}
/*-----------------------------------------------------------------
PRIVATE METHODS
-----------------------------------------------------------------*/
/**
* Initializes properties
*
*
*/
private function initializeProperties(Task $task, Project $project, $projectTitle){
$this->spreadsheet = new Spreadsheet();
$this->task = $task;
$this->project = $project;
$this->projectTitle = $projectTitle;
$default_border = [
'style' => Border::BORDER_THIN,
'color' => ['rgb' => '1006A3']
];
$this->style_header = [
'borders' => [
'bottom' => $default_border,
'left' => $default_border,
'top' => $default_border,
'right' => $default_border,
],
'fill' => [
'type' => Fill::FILL_SOLID,
'color' => ['rgb' => 'E1E0F7'],
],
'font' => [
'bold' => true,
'size' => 16,
]
];
$this->style_content = [
'borders' => [
'bottom' => $default_border,
'left' => $default_border,
'top' => $default_border,
'right' => $default_border,
],
'fill' => [
'type' => Fill::FILL_SOLID,
'color' => ['rgb' => 'eeeeee'],
],
'font' => [
'size' => 12,
]
];
}
/**
* Sets the header information of the excel sheet
*
*
*/
private function setHeaderContent(){
$this->spreadsheet->setActiveSheetIndex(0)
->setCellValue('A1', 'Zoekterm')
->setCellValue('B1', 'Website')
->setCellValue('C1', 'Pagina')
->setCellValue('D1', 'Paginatitel')
->setCellValue('E1', 'Paginabeschrijving')
->setCellValue('F1', 'KvK-nummer')
->setCellValue('G1', 'Vestigingsnummer')
->setCellValue('H1', 'Adresgegevens');
$this->spreadsheet->getActiveSheet()->getStyle('A1:H1')->applyFromArray($this->style_header); // give style to header
}
/**
* Sets the main excel content
*
*
*/
private function setMainContent(){
$this->prepareDataku();
$firststyle = 'A2';
$dataku = &$this->dataku;// define alias
for ($i = 0; $i < count($dataku); $i ) {
$urut = $i 2;
$search_query = 'A' . $urut;
$page_url = 'B' . $urut;
$page_link = 'C' . $urut;
$page_title = 'D' . $urut;
$page_description = 'E' . $urut;
$coc_number = 'F' . $urut;
$branch_code = 'G' . $urut;
$address = 'H' . $urut;
$this->spreadsheet->setActiveSheetIndex(0)
->setCellValue($search_query, $dataku[$i][0])
->setCellValue($page_url, $dataku[$i][1])
->setCellValue($page_link, $dataku[$i][2])
->setCellValue($page_title, $dataku[$i][3])
->setCellValue($page_description, $dataku[$i][4])
->setCellValue($coc_number, $dataku[$i][5])
->setCellValue($branch_code, $dataku[$i][6])
->setCellValue($address, $dataku[$i][7]);
$laststyle = $page_description;
}
$this->spreadsheet->getActiveSheet()->getStyle($firststyle . ':' . $laststyle)->applyFromArray($this->style_content);
}
/**
* Prepares dataku array needed by setMainContent()
*
*
*/
private function prepareDataku() {
// Pay attention to "!=". This block is the else block in your code
if ($this->projectTitle != \BoolState::TRUE) {
foreach ($this->task->activeResults as $result){
$this->dataku[] = [$result->task->search_query, $result->page_url, $result->page_link, $result->page_title, $result->page_description, $result->coc_number, $result->branch_code, $result->address];
}
return;
}
foreach ($this->project->tasks as $task) {
if ($task->status_id == TaskStatus::CLOSED) {
foreach ($task->activeResults as $result) {
$this->dataku[] = [$result->task->search_query, $result->page_url, $result->page_link, $result->page_title, $result->page_description, $result->coc_number, $result->branch_code, $result->address];
}
}
}
}
/**
* Saves the generated excel file on the local disc or writes it to the output stream.
*
*
*
*/
private function doOutput(){
$writer = $this->outType == 'xlsx' ? new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->spreadsheet) : new Xls($this->spreadsheet);
// var_dump($this->stream);
# Save the excel file on the disc
if (!$this->stream) {
$writer->save($this->filename);
return;
}
# Print the excel to the output stream
$this->filename = $this->filename == null ? $this->title : $this->filename;
$filename = "NVWA HDT ". ucfirst($this->projectTitle ? $this->task->project->description : $this->task->search_query) . '.Xls';
$this->cleanOutput();
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Cache-Control: max-age=0');
header('Content-Disposition: attachment;filename="' . $filename . '"'); // file name of excel
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');
Yii::app()->end();
}
}