portal-api/app/Exports/BrandedReportWriter.php

<?php

namespace App\Exports;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\HttpFoundation\BinaryFileResponse;

final class BrandedReportWriter
{
    private const TEMPLATE = 'Exports/Templates/report_template.xlsx';

    private const COMPANY_ROWS = [1, 2, 3, 4];

    private const TITLE_ROW = 6;

    private const HEADING_ROW = 7;

    private const FIRST_DATA_ROW = 8;

    private const LOGO_COLUMNS_MIN_WIDTH = 37;

    private const MAX_COLUMN_WIDTH = 60;

    private const FONT_WIDTH_FACTOR = 1.2;

    private const FONT = 'Google Sans';

    private const BRAND_COLOR = 'F26522';

    private const BORDER_COLOR = 'D9D9D9';

    private const CHUNK_SIZE = 500;

    public function download(BrandedReport $report, string $filename): BinaryFileResponse
    {
        $path = tempnam(sys_get_temp_dir(), 'report') . '.xlsx';
        $this->write($report, $path);

        return response()->download($path, $filename)->deleteFileAfterSend();
    }

    public function write(BrandedReport $report, string $path): void
    {
        $book = IOFactory::load(app_path(self::TEMPLATE));
        $sheet = $book->getActiveSheet();
        $headings = $report->headings();
        $lastColumn = Coordinate::stringFromColumnIndex(count($headings));

        $this->resetBody($sheet);
        $this->spanCompanyAndTitle($sheet, $lastColumn, $report->title());
        $this->writeHeadings($sheet, $headings, $lastColumn);

        $widths = array_map(fn (string $heading) => mb_strlen($heading), $headings);
        $row = self::FIRST_DATA_ROW;

        $report->query()->chunk(self::CHUNK_SIZE, function ($records) use ($report, $sheet, &$row, &$widths) {
            foreach ($records as $record) {
                foreach (array_values($report->map($record)) as $index => $value) {
                    $this->writeCell($sheet, $index + 1, $row, $value);
                    $widths[$index] = max($widths[$index], $this->displayWidth($value));
                }
                $row++;
            }
        });

        $lastRow = max($row - 1, self::HEADING_ROW);
        $this->styleBody($sheet, $lastColumn, $lastRow, $report->moneyColumns());
        $this->sizeColumns($sheet, $widths);

        $sheet->freezePane('A' . self::FIRST_DATA_ROW);
        $sheet->setAutoFilter('A' . self::HEADING_ROW . ":{$lastColumn}{$lastRow}");
        $sheet->getPageSetup()
            ->setPrintArea("A1:{$lastColumn}{$lastRow}")
            ->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)
            ->setFitToWidth(1)
            ->setFitToHeight(0);
        $sheet->setSelectedCell('A' . self::FIRST_DATA_ROW);

        IOFactory::createWriter($book, 'Xlsx')->save($path);
    }

    private function resetBody(Worksheet $sheet): void
    {
        foreach (array_keys($sheet->getMergeCells()) as $range) {
            $sheet->unmergeCells($range);
        }

        $highestRow = $sheet->getHighestRow();
        if ($highestRow >= self::HEADING_ROW) {
            $sheet->removeRow(self::HEADING_ROW, $highestRow - self::HEADING_ROW + 1);
        }
    }

    private function spanCompanyAndTitle(Worksheet $sheet, string $lastColumn, string $title): void
    {
        $companyLastColumn = Coordinate::columnIndexFromString($lastColumn) > 3 ? $lastColumn : 'E';
        foreach (self::COMPANY_ROWS as $companyRow) {
            $sheet->mergeCells("C{$companyRow}:{$companyLastColumn}{$companyRow}");
        }

        $titleCell = 'A' . self::TITLE_ROW;
        $sheet->setCellValueExplicit($titleCell, mb_strtoupper($title), DataType::TYPE_STRING);
        $sheet->mergeCells("{$titleCell}:{$lastColumn}" . self::TITLE_ROW);
    }

    private function writeHeadings(Worksheet $sheet, array $headings, string $lastColumn): void
    {
        foreach ($headings as $index => $heading) {
            $sheet->setCellValueExplicit([$index + 1, self::HEADING_ROW], $heading, DataType::TYPE_STRING);
        }

        $range = 'A' . self::HEADING_ROW . ":{$lastColumn}" . self::HEADING_ROW;
        $sheet->getStyle($range)->applyFromArray([
            'font' => ['name' => self::FONT, 'size' => 11, 'bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => self::BRAND_COLOR]],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => self::BORDER_COLOR]]],
        ]);
        $sheet->getRowDimension(self::HEADING_ROW)->setRowHeight(30);
    }

    private function writeCell(Worksheet $sheet, int $column, int $row, mixed $value): void
    {
        if ($value === null || $value === '') {
            return;
        }

        if (is_int($value) || is_float($value)) {
            $sheet->setCellValue([$column, $row], $value);

            return;
        }

        $sheet->setCellValueExplicit([$column, $row], (string) $value, DataType::TYPE_STRING);
    }

    private function styleBody(Worksheet $sheet, string $lastColumn, int $lastRow, array $moneyColumns): void
    {
        if ($lastRow < self::FIRST_DATA_ROW) {
            return;
        }

        $sheet->getStyle('A' . self::FIRST_DATA_ROW . ":{$lastColumn}{$lastRow}")->applyFromArray([
            'font' => ['name' => self::FONT, 'size' => 11],
            'alignment' => ['vertical' => Alignment::VERTICAL_TOP, 'wrapText' => true],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => self::BORDER_COLOR]]],
        ]);

        foreach ($moneyColumns as $column) {
            $sheet->getStyle("{$column}" . self::FIRST_DATA_ROW . ":{$column}{$lastRow}")
                ->getNumberFormat()->setFormatCode('#,##0');
        }
    }

    private function sizeColumns(Worksheet $sheet, array $widths): void
    {
        $widths = array_map(fn (int $width) => min((int) ceil($width * self::FONT_WIDTH_FACTOR) + 3, self::MAX_COLUMN_WIDTH), $widths);

        $logoShortfall = self::LOGO_COLUMNS_MIN_WIDTH - ($widths[0] + ($widths[1] ?? 0));
        if ($logoShortfall > 0) {
            $widths[1] = ($widths[1] ?? 0) + $logoShortfall;
        }

        foreach ($widths as $index => $width) {
            $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($index + 1))->setWidth($width);
        }
    }

    private function displayWidth(mixed $value): int
    {
        if (is_int($value) || is_float($value)) {
            return mb_strlen(number_format((float) $value));
        }

        $lines = explode("\n", (string) $value);

        return max(array_map(fn (string $line) => mb_strlen($line), $lines));
    }
}