strprfcrm/app/Services/Export/ExportCommonCatalogService.php

<?php

declare(strict_types=1);

namespace App\Services\Export;

use App\Models\CommonCatalogItem;
use App\Models\File;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportCommonCatalogService
{
    public const HEADERS = [
        'ID',
        'Внешний вид',
        'Артикул',
        'Наименование по калькулятору',
        'Вид',
        'Габаритные размеры',
        'Высота падения',
        'Дополнительные сведения',
        "Единица измерения\nгабаритов",
        'Вес, кг.',
        'Объем, м3',
        'Места',
        'Состав',
        'Возрастная группа',
        'Макс.кол-во пользователей',
        'Ед.',
        'Серия',
        'ТМ',
        'Примечание',
    ];

    public function handle(int $userId): string
    {
        $spreadsheet = new Spreadsheet;
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Каталог общий');
        $sheet->fromArray(self::HEADERS, null, 'A1');

        $sheet->getStyle('A1:S1')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'D9EAF7'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
        ]);
        $sheet->getRowDimension(1)->setRowHeight(42);
        $sheet->freezePane('A2');
        $sheet->setAutoFilter('A1:S1');

        $row = 2;
        CommonCatalogItem::query()
            ->with('imageFile')
            ->orderBy('article')
            ->chunk(200, function ($items) use ($sheet, &$row): void {
                foreach ($items as $item) {
                    $sheet->setCellValue("A{$row}", $item->id);
                    $sheet->setCellValueExplicit("C{$row}", $item->article, DataType::TYPE_STRING);
                    $sheet->setCellValue("D{$row}", $item->calculator_name);
                    $sheet->setCellValue("E{$row}", $item->kind);
                    $sheet->setCellValue("F{$row}", $item->dimensions);
                    $sheet->setCellValue("G{$row}", $item->fall_height);
                    $sheet->setCellValue("H{$row}", $item->additional_info);
                    $sheet->setCellValue("I{$row}", $item->dimension_unit);
                    $sheet->setCellValue("J{$row}", $item->weight);
                    $sheet->setCellValue("K{$row}", $item->volume);
                    $sheet->setCellValue("L{$row}", $item->places);
                    $sheet->setCellValue("M{$row}", $item->composition);
                    $sheet->setCellValue("N{$row}", $item->age_group);
                    $sheet->setCellValue("O{$row}", $item->max_users);
                    $sheet->setCellValue("P{$row}", $item->unit);
                    $sheet->setCellValue("Q{$row}", $item->series);
                    $sheet->setCellValue("R{$row}", $item->trademark);
                    $sheet->setCellValue("S{$row}", $item->note);

                    $this->addImage($sheet, $item, $row);
                    $row++;
                }
            });

        if ($row > 2) {
            $sheet->getStyle('A1:S'.($row - 1))
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(Border::BORDER_THIN);
            $sheet->getStyle('A2:S'.($row - 1))->getAlignment()
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);
        }

        foreach (['A' => 8, 'B' => 18, 'C' => 16, 'D' => 38, 'E' => 24, 'F' => 28] as $column => $width) {
            $sheet->getColumnDimension($column)->setWidth($width);
        }
        foreach (range('G', 'S') as $column) {
            $sheet->getColumnDimension($column)->setWidth(18);
        }

        $directory = 'export/common_catalog';
        $filename = 'common_catalog_'.now()->format('Y-m-d_H-i-s').'.xlsx';
        $relativePath = "{$directory}/{$filename}";
        Storage::disk('public')->makeDirectory($directory);
        (new Xlsx($spreadsheet))->save(Storage::disk('public')->path($relativePath));
        $spreadsheet->disconnectWorksheets();

        $link = url('/storage/'.$relativePath);
        File::query()->create([
            'link' => $link,
            'path' => $relativePath,
            'user_id' => $userId,
            'original_name' => $filename,
            'mime_type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'is_generated' => true,
        ]);

        return $link;
    }

    private function addImage(Worksheet $sheet, CommonCatalogItem $item, int $row): void
    {
        $path = $item->imageFile?->path;
        if (! $path || ! Storage::disk('public')->exists($path)) {
            return;
        }

        $drawing = new Drawing;
        $drawing->setName($item->article);
        $drawing->setPath(Storage::disk('public')->path($path));
        $drawing->setCoordinates("B{$row}");
        $drawing->setHeight(68);
        $drawing->setOffsetX(4);
        $drawing->setOffsetY(4);
        $drawing->setWorksheet($sheet);
        $sheet->getRowDimension($row)->setRowHeight(56);
    }
}