strprfcrm/app/Services/ExportScheduleService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\Product;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportScheduleService
{
    private const DATA_START_ROW = 3;
    private const DATA_END_COLUMN = 'K';
    private const PDF_ROWS_PER_PAGE = 24;


    /**
     * @throws \Exception
     */
    public function handle(Collection $schedules, int $userId): string
    {
        $spreadsheet = $this->buildSpreadsheet($schedules);
        $sheet = $spreadsheet->getActiveSheet();
        $header = (string) $sheet->getCell('A1')->getValue();
        $fileName = $header . '.xlsx';

        $writer = new Xlsx($spreadsheet);
        $fd = 'export/schedule/tmp';
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/export/schedule/') . '/tmp/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);
        PdfConverterClient::convert($fp);

        // create zip archive
        $fileModel = (new FileService())->createZipArchive($fd, Str::replace('.xlsx', '.zip', $fileName), $userId);

        //   remove temp files
        Storage::disk('public')->deleteDirectory($fd);

        // return link
        return $fileModel?->link ?? '';

    }

    public function buildSpreadsheet(Collection $schedules): \PhpOffice\PhpSpreadsheet\Spreadsheet
    {
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load('./templates/Schedule.xlsx');
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);

        $row = self::DATA_START_ROW;
        $from = '-';
        $prevInstDate = null;
        $sameDateRows = 0;
        $rowsOnCurrentPage = 0;
        $lastScheduleDate = null;

        foreach ($schedules as $schedule) {
            if ($prevInstDate === null) {
                $from = $schedule->installation_date;
                $prevInstDate = $schedule->installation_date;
                $sameDateRows = 1;
            } elseif ($prevInstDate === $schedule->installation_date) {
                $sameDateRows++;
            } else {
                if ($sameDateRows > 1) {
                    $this->mergeDateColumns($sheet, $row - $sameDateRows, $row - 1);
                }

                if ($rowsOnCurrentPage >= self::PDF_ROWS_PER_PAGE) {
                    $sheet->setBreak('A' . $row, Worksheet::BREAK_ROW);
                    $rowsOnCurrentPage = 0;
                }

                $sameDateRows = 1;
                $prevInstDate = $schedule->installation_date;
            }

            $sheet->setCellValue('A' . $row, DateHelper::getHumanDayOfWeek($schedule->installation_date));
            $sheet->setCellValue('B' . $row, DateHelper::getHumanDate($schedule->installation_date, true));

            $sheet->setCellValue('C' . $row, $schedule->address_code);
            $sheet->setCellValue('D' . $row, $schedule->district->shortname);
            $sheet->setCellValue('E' . $row, $schedule->area->name);
            $sheet->setCellValue('F' . $row, $schedule->object_address);
            $sheet->setCellValue('G' . $row, $schedule->object_type);
            $sheet->setCellValue('H' . $row, Str::trim($schedule->mafs));
            $sheet->setCellValue('I' . $row, $schedule->mafs_count);
            $sheet->setCellValue('J' . $row, $schedule->brigadier->name);
            $sheet->setCellValue('K' . $row, $schedule->comment);

            $sheet->getStyle('C' . $row . ':K' . $row)
                ->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB(Str::replace('#', '', $schedule->brigadier->color));

            $lastScheduleDate = $schedule->installation_date;
            $row++;
            $rowsOnCurrentPage++;
        }

        if ($sameDateRows > 1) {
            $this->mergeDateColumns($sheet, $row - $sameDateRows, $row - 1);
        }

        $lastDataRow = max($row - 1, 2);

        $sheet->getStyle('A2:B' . $lastDataRow)->getAlignment()->setTextRotation(90);
        $sheet->getStyle('A2:' . self::DATA_END_COLUMN . $lastDataRow)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('777777'));
        $sheet->getStyle('A2:' . self::DATA_END_COLUMN . $lastDataRow)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('C2:' . self::DATA_END_COLUMN . $lastDataRow)->getAlignment()->setWrapText(true);

        $this->configurePdfLayout($sheet, $lastDataRow);

        $fromText = DateHelper::isDate($from) ? DateHelper::getHumanDate($from) : '-';
        $toText = DateHelper::isDate((string) $lastScheduleDate) ? DateHelper::getHumanDate($lastScheduleDate) : '-';
        $header = 'График монтажей с ' . $fromText . ' по ' . $toText;

        $sheet->setCellValue('A1', $header);
        $sheet->getStyle('A1')->getFont()->setBold(true);

        return $spreadsheet;
    }

    private function mergeDateColumns(Worksheet $sheet, int $startRow, int $endRow): void
    {
        $sheet->mergeCells('A' . $startRow . ':A' . $endRow);
        $sheet->mergeCells('B' . $startRow . ':B' . $endRow);
    }

    private function configurePdfLayout(Worksheet $sheet, int $lastDataRow): void
    {
        $pageSetup = $sheet->getPageSetup();
        $pageSetup->setPaperSize(PageSetup::PAPERSIZE_A4);
        $pageSetup->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
        $pageSetup->setFitToWidth(1);
        $pageSetup->setFitToHeight(0);

        $sheet->getPageMargins()
            ->setTop(0.4)
            ->setBottom(0.4)
            ->setLeft(0.25)
            ->setRight(0.25);

        $pageSetup->setRowsToRepeatAtTopByStartAndEnd(1, 2);
        $pageSetup->setPrintArea('A1:' . self::DATA_END_COLUMN . $lastDataRow);
    }
}