Cacher is the code snippet organizer for pro developers

    public function getBuildingTypeComparingReport(
        $cities,
        $buildingType,
        $startTextDate1,
        $finishTextDate1,
        $startTextDate2,
        $finishTextDate2,
        $isShowLimits = false,
        $isShowRenters = false
    )
    {
        $startDate1 = new \DateTime(date('d-m-Y H.i', strtotime($startTextDate1)));
        $startDate2 = new \DateTime(date('d-m-Y H.i', strtotime($startTextDate2)));
        $finishDate1 = new \DateTime(date('d-m-Y H.i', strtotime($finishTextDate1)));
        $finishDate2 = new \DateTime(date('d-m-Y H.i', strtotime($finishTextDate2)));
        $this->phpExcelObject = $this->container->get('phpexcel')->createPHPExcelObject();
        $this->phpExcelObject->getProperties()->setCreator("liuggio")
            ->setLastModifiedBy("user")
            ->setTitle("comparing_report".$startTextDate1."...".$finishTextDate1."")
            ->setSubject("comparing_report".$startTextDate1."...".$finishTextDate1."")
            ->setDescription("comparing_report".$startTextDate1."...".$finishTextDate1."")
            ->setKeywords("office")
            ->setCategory("Result file")
        ;
        $objWorkSheetFirstPeriod = $this->phpExcelObject->createSheet(0);
        $this->makeTitleAndStyleComparingReport($objWorkSheetFirstPeriod,
                                                date('d-m-Y', strtotime($startTextDate1)),
                                                date('d-m-Y', strtotime($finishTextDate1))
                                                );
        $dataFirstPeriod = $this->makeBuildingTypePeriodComparingReport(
            $objWorkSheetFirstPeriod,
            $cities,
            $buildingType,
            $startDate1,
            $finishDate1,
            $isShowLimits,
            $isShowRenters
        );
        $this->makeDataComparingReport($dataFirstPeriod['content'], $objWorkSheetFirstPeriod);

        $objWorkSheetSecondPeriod = $this->phpExcelObject->createSheet(1);
        $this->makeTitleAndStyleComparingReport($objWorkSheetSecondPeriod,
                                                date('d-m-Y', strtotime($startTextDate2)),
                                                date('d-m-Y', strtotime($finishTextDate2))
                                                );
        $dataSecondPeriod = $this->makeBuildingTypePeriodComparingReport(
            $objWorkSheetSecondPeriod,
            $cities,
            $buildingType,
            $startDate2,
            $finishDate2,
            $isShowLimits,
            $isShowRenters
        );

        $this->makeDataComparingReport($dataSecondPeriod['content'], $objWorkSheetSecondPeriod);

        // create the writer
        $writer = $this->container->get('phpexcel')->createWriter($this->phpExcelObject, 'Excel5');
        // create the response
        $response = $this->container->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $dispositionHeader = $response->headers->makeDisposition(
            ResponseHeaderBag::DISPOSITION_ATTACHMENT,
            "export_comparing_".$startTextDate1."_".$finishTextDate2."".'.xls'
        );
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');
        $response->headers->set('Content-Disposition', $dispositionHeader);

        return $response;
    }

    protected function makeBuildingTypePeriodComparingReport(
        \PHPExcel_Worksheet $sheet,
        $cities,
        $buildingType,
        $startDate,
        $finishDate,
        $isShowLimits = false,
        $isShowRenters = false
    )
    {
        $renters = $this->em->getRepository('AppBundle:Counters')->findRenterForCompare(
            $cities,
            $buildingType,
            $startDate,
            $finishDate
        );

        $withoutRenters = $this->em->getRepository('AppBundle:Counters')->findWithoutRenterForCompare(
            $cities,
            $buildingType,
            $startDate,
            $finishDate
        );

        $limits = $this->em->getRepository('AppBundle:CounterLimit')->findLimitsBysourceTypeAndValueForCompare(
            $cities,
            $buildingType,
            $startDate,
            $finishDate
        );

        $buildingsInfo = [];

        foreach ($limits as $limit) {
            $buildingName = $limit['buildingName'];
            $sourceName = $limit['sourcesName'];

            if ($isShowLimits || ($isShowRenters && $isShowLimits)) {
                if (!isset($buildingsInfo[$buildingName][$sourceName.'_without_renter'])) {
                    $buildingsInfo[$buildingName][$sourceName.'_without_renter'] = null;
                }

                if (!isset($buildingsInfo[$buildingName][$sourceName.'_renters'])) {
                    $buildingsInfo[$buildingName][$sourceName.'_renters'] = null;
                }

                $buildingsInfo[$buildingName][$sourceName.'_limit'] = $limit["value"];
            }
        }
        foreach ($renters as $renter) {
            $buildingName = $renter['buildingName'];
            $sourceName = $renter['sourcesName'];

            if ($isShowRenters || ($isShowRenters && $isShowLimits)) {
                if (!isset($buildingsInfo[$buildingName][$sourceName.'_without_renter'])) {
                    $buildingsInfo[$buildingName][$sourceName.'_without_renter'] = null;
                }

                if (!isset($buildingsInfo[$buildingName][$sourceName.'_limit'])) {
                    $buildingsInfo[$buildingName][$sourceName.'_limit'] = null;
                }

                if(empty($renter['renterName'])) {
                    $buildingsInfo[$buildingName][$sourceName.'_renters'] = null;
                } else {
                    $buildingsInfo[$buildingName][$sourceName.'_renters'] = $renter['diffValue'];
                }
            }
        }

        foreach ($withoutRenters as $withoutRenter) {
            $buildingName = $withoutRenter['buildingName'];
            $sourceName = $withoutRenter['sourcesName'];
            $buildingsInfo[$buildingName][$sourceName.'_without_renter'] = $withoutRenter['diffValue'];

            if (!isset($buildingsInfo[$buildingName][$sourceName.'_renters'])) {
                $buildingsInfo[$buildingName][$sourceName.'_renters'] = null;
            }

            if (!isset($buildingsInfo[$buildingName][$sourceName.'_limit'])) {
                $buildingsInfo[$buildingName][$sourceName.'_limit'] = null;
            }
        }

        $sources = $this->em->getRepository('AppBundle:SourceType')->findSourceType();

        $i = -1;
        $expSources = [];
        $expSources['content'] = [];


        foreach ($sources as $source) {
            $sourceName = $source["name"];
            $expSources['header'][$sourceName.'_without_renter'] = $sourceName;

            if ($isShowRenters) {
                $expSources['header'][$sourceName.'_renters'] = "Орендарі-\n".$sourceName;
            }

            if ($isShowLimits) {
                $expSources['header'][$sourceName.'_limit'] = "Ліміти-\n".$sourceName;
            }

            if ($isShowLimits && $isShowRenters) {
                $expSources['header'][$sourceName.'_renters'] = "Орендарі-\n".$sourceName;
                $expSources['header'][$sourceName.'_limit'] = "Ліміти-\n".$sourceName;
            }
        }

        foreach ($expSources['header'] as $sourceName => $value) {
            foreach ($buildingsInfo as $buildingName => $buildingData) {
                if (array_key_exists($sourceName, $buildingData)) {
                    $expSources['content'][$buildingName][$i++] = $buildingData[$sourceName];
                }
                else {
                    $expSources['content'][$buildingName][$i++] = 0;
                }
            }
        }

        $sheet->fromArray($expSources['header'], null, "B3");

        if ($isShowLimits == false && $isShowRenters == false) {
            $sheet
                ->getStyle('A1:M2')
                ->applyFromArray($this->getStylingComparingReport()['general_style'])
            ;
            $sheet
                ->getStyle('A1:M'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('M2:M3')->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('M1:M'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['date_style'])
            ;
            $sheet
                ->mergeCells('A1:M1')
            ;

            $sheet
                ->mergeCells('A2:M2')
            ;
        }

        if ($isShowLimits || $isShowRenters) {
            $sheet
                ->getStyle('A1:Y2')
                ->applyFromArray($this->getStylingComparingReport()['general_style'])
            ;
            $sheet
                ->getStyle('A1:Y'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('Y2:Y3')
                ->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('Y1:Y'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['date_style'])
            ;
            $sheet
                ->mergeCells('A1:Y1')
            ;

            $sheet
                ->mergeCells('A2:Y2')
            ;
        }

        if ($isShowLimits && $isShowRenters) {
            $sheet
                ->getStyle('A1:AK2')
                ->applyFromArray($this->getStylingComparingReport()['general_style'])
            ;
            $sheet
                ->getStyle('A1:AK'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('AK2:AK3')
                ->applyFromArray($this->getStylingComparingReport()['array_style'])
            ;
            $sheet
                ->getStyle('AK1:AK'.self::QUANTITY_HEADER_ROWS_COMPARING_REPORT.'')
                ->applyFromArray($this->getStylingComparingReport()['date_style'])
            ;
            $sheet
                ->mergeCells('A1:AK1')
            ;

            $sheet
                ->mergeCells('A2:AK2')
            ;
        }

        for ($i = 1; $i < self::QUANTITY_HEADER_ROWS_COMPARING_REPORT; $i++) {
            $sheet
                ->getRowDimension($i)
                ->setRowHeight(30)
            ;
        }

        return $expSources;
    }

    private function makeDataComparingReport($data, \PHPExcel_Worksheet $sheet)
    {
        $row = self::QUANTITY_HEADER_ROWS_COMPARING_REPORT;

        foreach ($data as $buildingName => $buildingData) {
            $sheet->setCellValue('A'.$row, $buildingName);
            $sheet->fromArray($buildingData, null, "B".$row);
            $row++;
        }

        return $sheet;
    }

    private function makeTitleAndStyleComparingReport(\PHPExcel_Worksheet $sheet, $startTextDate, $finishTextDate)
    {
        $sheet
            ->setCellValue('A1', $startTextDate.'...'.$finishTextDate)
            ->setCellValue('A2', 'Порівняння')
            ->setCellValue('A3', 'Будівля')
        ;
        $sheet->setTitle($startTextDate."...".$finishTextDate);

        return $sheet;
    }

    private function getStylingComparingReport()
    {
        $result = [];
        $result['general_style'] = array(
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );

        $result['array_style'] = array(
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_TOP,
                'wrap' => true
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                )
            )
        );

        $result['date_style'] = array(
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'wrap' => true
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                )
            )
        );

        return $result;
    }