admin-erahn/app/Exports/PendSerExport.php

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class PendSerExport implements FromCollection, WithHeadings, WithTitle, WithMapping, WithStyles, WithCustomStartCell, WithColumnFormatting
{
    protected $pendser;
    protected $jenisLaporan;
    protected $counter = 1; // ✅ Fix counter issue

    public function __construct($pendser, $jenisLaporan)
    {
        $this->pendser = $pendser;
        $this->jenisLaporan = strtolower($jenisLaporan); // ✅ Convert to lowercase for array key matching
    }

    public function collection()
    {
        $data = collect($this->pendser);

        // Calculate total amaun
        $totalAmaun = $data->sum(function ($item) {
            return $item['pendahuluan'] > 0 ? $item['pendahuluan'] : $item['serahan'];
        });

        // Append total row
        $data->push([
            'tarikh'   => '',
            'descpt'   => '',
            'teller'   => 'JUMLAH KESELURUHAN',
            'pendahuluan' => 0,
            'serahan' => $totalAmaun,
        ]);

        return $data;
    }

    // ✅ Set Title Row Above The Table
    public function headings(): array
    {
        $reportTitles = [
            'serahan'       => 'LAPORAN SERAHAN KEPADA HQ',
            'serahankepadav3'     => 'LAPORAN SERAHAN KEPADA V3',
            'pendahuluan'   => 'LAPORAN PENDAHULUAN DARIPADA HQ',
            'pendahuluanv3' => 'LAPORAN PENDAHULUAN DARIPADA V2',
            'online'        => 'LAPORAN SERAHAN KEPADA HQ (arrahnPay)',
            'serahanlelong' => 'LAPORAN SERAHAN KEPADA HQ (arrahnBid)',
            'serahanonline' => 'LAPORAN SERAHAN KESELURUHAN',
            'all'           => 'LAPORAN KESELURUHAN HQ'
        ];

        // ✅ Use lowercase key for better matching
        $title = $reportTitles[$this->jenisLaporan] ?? 'LAPORAN TIDAK DIKETAHUI';

        return [
            [$title], // Report title in the first row
            ['Bil', 'Tarikh', 'Keterangan', 'Nama', 'Amaun (RM)'], // Column headers
        ];
    }

    // ✅ Move Table Start Position (Avoid Overlapping with Title)
    public function startCell(): string
    {
        return 'A2'; // Start table from row 2 to leave space for the title
    }

    // ✅ Map Data to Match the Columns
    public function map($item): array
    {
        static $counter = 1;
        static $totalAmaun = 0;

        // Get amount (either "pendahuluan" or "serahan")
        $amount = $item['pendahuluan'] > 0 ? $item['pendahuluan'] : $item['serahan'];

        // Accumulate total amount
        $totalAmaun += $amount;

         // If this is the total row (JUMLAH KESELURUHAN), remove numbering
        if ($item['teller'] === 'JUMLAH KESELURUHAN') {
            return [
                '', // Empty Bil column
                '', // Empty Tarikh column
                '', // Empty Keterangan column
                strtoupper($item['teller']), // "JUMLAH KESELURUHAN" (Uppercase)
                number_format($amount, 2), // Total Amount
            ];
        }
        return [
            $counter++, // Bil
            $item['tarikh'], // Date
            $item['descpt'], // Description
            $item['teller'], // Name
            number_format($amount, 2), // Amount RM (Formatted)
        ];
    }

    // ✅ Set Title for Excel Tab Name
    public function title(): string
    {
        return strtoupper($this->jenisLaporan ?? 'LAPORAN'); // Capitalize for better readability
    }

    public function styles(Worksheet $sheet)
    {
        // Set title and headers bold
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
        $sheet->getStyle('A2:E2')->getFont()->setBold(true)->setSize(12);

        // Auto-size columns A to E
        foreach (range('A', 'E') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        // Get last row index (total row)
        $lastRow = $sheet->getHighestRow();

        // Bold total row
        $sheet->getStyle("A{$lastRow}:E{$lastRow}")->getFont()->setBold(true);

        return [];
    }

    // ✅ Format Column E (Amaun) as a Number with 2 Decimal Places
    public function columnFormats(): array
    {
        return [
            'E' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1, // ✅ Properly format numbers
        ];
    }

    
}