XLSX Generator Commands

{
  "report_date": "November 29, 2023",
  "total": 674.98,
  "order": {
    "id": "ORD-2023-987B",
    "customer": { "name": "Innovate Dynamics" },
    "products": [
      {
        "name": "Quantum Widget Pro", "sku": "QW-PRO-001", "description": "Next-generation widget.",
        "total_price": 599.98, "imageUrl": "https://i.imgur.com/Gz7e1E9.png"
      },
      {
        "name": "Maintenance Kit", "sku": "SMK-STD-004", "description": "Includes all necessary tools.",
        "total_price": 75.00, "imageUrl": "https://i.imgur.com/mO2rN3b.png"
      }
    ]
  },
  "benefits": [
    "Premium Quality Materials",
    "3-Year Extended Warranty",
    "24/7 Customer Support Access"
  ]
}

{
  "report_date": "November 29, 2023",
  "order": {
    "id": "ORD-2023-987B",
    "total": 674.98,
    "customer": { "name": "Innovate Dynamics" },
    "products": [
      {
        "name": "Quantum Widget Pro", "sku": "QW-PRO-001", "description": "Widget generasi terbaru.",
        "total_price": 599.98, "imageUrl": "https://i.imgur.com/Gz7e1E9.png"
      },
      {
        "name": "Maintenance Kit", "sku": "SMK-STD-004", "description": "Termasuk semua alat yang diperlukan.",
        "total_price": 75.00, "imageUrl": "https://i.imgur.com/mO2rN3b.png"
      }
    ]
  },
  "benefits": [
    "Bahan Kualitas Premium",
    "Garansi Tambahan 3 Tahun",
    "Akses Dukungan Pelanggan 24/7"
  ]
}

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\ExpressionLanguage\ExpressionLanguage;

class XlsxGenerateCommand extends Command
{
    /**
     * The name and signature of the console command.
     * @var string
     */
    protected $signature = 'xlsx:generate
                            {--template=storage/app/templates/report_template.xlsx : The path to the XLSX template file}
                            {--output=storage/app/output/report : The base path for the generated file (extension is added automatically)}
                            {--data=report_data.json : The path to the JSON data file}
                            {--mode=merge : The output mode (merge, blade, html, pdf)}';

    /**
     * The console command description.
     * @var string
     */
    protected $description = 'Generate XLSX, HTML, or PDF files from an XLSX template and a JSON data file.';

    private $expressionLanguage;

    public function __construct()
    {
        parent::__construct();
        $this->expressionLanguage = new ExpressionLanguage();
    }

    public function handle()
    {
        $mode = $this->option('mode');
        $templatePath = $this->option('template');
        $outputPath = $this->option('output');
        $dataPath = $this->option('data');

        if (!in_array($mode, ['merge', 'blade', 'html', 'pdf'])) { $this->error("Invalid mode '{$mode}'."); return 1; }
        if (!File::exists($templatePath)) { $this->error("Template file not found at: {$templatePath}"); return 1; }

        $finalOutputPath = $this->getFinalOutputPath($outputPath, $mode);
        File::ensureDirectoryExists(dirname($finalOutputPath));

        try {
            if ($mode === 'blade') {
                $this->generateBlade($templatePath, $finalOutputPath);
            } else {
                if (!File::exists($dataPath)) { $this->error("Data file not found at: {$dataPath}"); return 1; }
                $data = json_decode(File::get($dataPath), true);
                if (json_last_error() !== JSON_ERROR_NONE) { throw new \Exception("Invalid JSON in data file: " . json_last_error_msg()); }

                $spreadsheet = IOFactory::load($templatePath);
                $sheet = $spreadsheet->getActiveSheet();

                $tags = $this->scanSheetForTags($sheet);
                $this->processTags($sheet, $tags, $data);
                $this->processPlaceholders($sheet, $data, $mode);

                $this->saveOutput($spreadsheet, $finalOutputPath, $mode);
            }
            $this->info(strtoupper($mode) . " file generated successfully at: {$finalOutputPath}");
        } catch (\Exception $e) {
            $this->error("An error occurred: " . $e->getMessage());
            return 1;
        }
        return 0;
    }

    private function scanSheetForTags(Worksheet $sheet): array
    {
        $tags = [];
        $ifStack = [];
        $foreachStack = [];

        foreach ($sheet->getRowIterator() as $row) {
            $rowIndex = $row->getRowIndex();
            $cellValue = (string) $sheet->getCell('A' . $rowIndex)->getValue();

            if (preg_match('/\${foreach_([a-zA-Z0-9_.]+) as ([a-zA-Z0-9_]+)}/', $cellValue, $matches)) {
                $foreachStack[] = ['start' => $rowIndex, 'arrayKey' => $matches[1], 'itemName' => $matches[2]];
            }
            if (strpos($cellValue, '${endforeach}') !== false) {
                if ($start = array_pop($foreachStack)) {
                    $tags[] = ['type' => 'foreach', 'start' => $start['start'], 'end' => $rowIndex, 'arrayKey' => $start['arrayKey'], 'itemName' => $start['itemName']];
                }
            }
            if (preg_match('/\${if:([^}]+)}/', $cellValue, $matches)) {
                $ifStack[] = ['start' => $rowIndex, 'condition' => trim($matches[1])];
            }
            if (strpos($cellValue, '${endif}') !== false) {
                if ($start = array_pop($ifStack)) {
                    $tags[] = ['type' => 'if', 'start' => $start['start'], 'end' => $rowIndex, 'condition' => $start['condition']];
                }
            }
            if (preg_match('/\${(ul|ol)_([a-zA-Z0-9_.]+)}/', $cellValue, $matches)) {
                $itemPlaceholderCoord = null;
                foreach($row->getCellIterator() as $cell) {
                    if ($cell->getValue() === '${item}') {
                        $itemPlaceholderCoord = $cell->getCoordinate();
                        break;
                    }
                }
                $tags[] = ['type' => 'list', 'start' => $rowIndex, 'listType' => $matches[1], 'key' => $matches[2], 'itemCoord' => $itemPlaceholderCoord];
            }
        }
        usort($tags, fn($a, $b) => $b['start'] <=> $a['start']);
        return $tags;
    }

    private function processTags(Worksheet $sheet, array $tags, array $data)
    {
        foreach ($tags as $tag) {
            if ($tag['type'] === 'foreach') $this->processForeachBlock($sheet, $tag, $data);
            if ($tag['type'] === 'if') $this->processIfBlock($sheet, $tag, $data);
            if ($tag['type'] === 'list') $this->processListBlock($sheet, $tag, $data);
        }
    }

    private function processForeachBlock(Worksheet $sheet, array $tag, array $data)
    {
        $items = Arr::get($data, $tag['arrayKey'], []);
        $templateRowsCount = ($tag['end'] - $tag['start']) + 1;

        if (empty($items)) {
            $sheet->removeRow($tag['start'], $templateRowsCount);
            return;
        }

        $sheet->insertNewRowBefore($tag['start'] + $templateRowsCount, count($items) * $templateRowsCount);

        $currentRow = $tag['start'] + $templateRowsCount;
        foreach ($items as $itemData) {
            for ($i = 0; $i < $templateRowsCount; $i++) {
                foreach ($sheet->getColumnIterator() as $column) {
                    $col = $column->getColumnIndex();
                    $templateCell = $sheet->getCell($col . ($tag['start'] + $i));
                    $targetCell = $sheet->getCell($col . ($currentRow + $i));
                    $targetCell->setXfIndex($templateCell->getXfIndex());
                    $targetCell->setValue($templateCell->getValue());
                }
            }
            for ($i = 0; $i < $templateRowsCount; $i++) {
                foreach ($sheet->getColumnIterator() as $column) {
                    $col = $column->getColumnIndex();
                    $cell = $sheet->getCell($col . ($currentRow + $i));
                    $cellValue = $cell->getValue();
                    if(!is_string($cellValue)) continue;

                    $newValue = preg_replace_callback('/\${' . $tag['itemName'] . '\.(.+?)}/', fn ($m) => Arr::get($itemData, $m[1], ''), $cellValue);
                    $cell->setValue($newValue);
                }
            }
            $currentRow += $templateRowsCount;
        }

        $sheet->removeRow($tag['start'], $templateRowsCount);
    }

    private function processIfBlock(Worksheet $sheet, array $tag, array $data)
    {
        $result = false;
        try {
            $result = $this->expressionLanguage->evaluate($tag['condition'], $data);
        } catch (\Exception $e) { $this->warn("Expression Error evaluating '{$tag['condition']}': {$e->getMessage()}"); }

        if (!$result) {
            $sheet->removeRow($tag['start'], ($tag['end'] - $tag['start']) + 1);
        } else {
            $sheet->getCell('A' . $tag['start'])->setValue('');
            $sheet->getCell('A' . $tag['end'])->setValue('');
        }
    }

    private function processListBlock(Worksheet $sheet, array $tag, array $data)
    {
        $items = Arr::get($data, $tag['key'], []);
        $startRow = $tag['start'];

        if (empty($items) || !$tag['itemCoord']) {
            $sheet->removeRow($startRow, 1);
            return;
        }

        [$itemCol, $itemRow] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($tag['itemCoord']);

        if (count($items) > 1) {
            $sheet->insertNewRowBefore($startRow + 1, count($items) - 1);
        }

        foreach($items as $index => $item) {
            $prefix = ($tag['listType'] === 'ol') ? ($index + 1) . '. ' : '• ';
            $targetRow = $startRow + $index;
            if ($index > 0) {
                foreach ($sheet->getColumnIterator() as $column) {
                    $templateCell = $sheet->getCell($column->getColumnIndex() . $itemRow);
                    $targetCell = $sheet->getCell($column->getColumnIndex() . $targetRow);
                    $targetCell->setXfIndex($templateCell->getXfIndex());
                }
            }
            $sheet->getCell($itemCol . $targetRow)->setValue($prefix . $item);
        }
        $sheet->getCell('A' . $startRow)->setValue('');
    }

    private function processPlaceholders(Worksheet $sheet, array $data, string $mode)
    {
        foreach ($sheet->getRowIterator() as $row) {
            foreach ($row->getCellIterator() as $cell) {
                $value = $cell->getValue();
                if (!is_string($value) || strpos($value, '${') === false) continue;

                $value = preg_replace_callback('/\${img_([a-zA-Z0-9_.]+)}/', function ($matches) use ($cell, $sheet, $data, $mode) {
                    $imagePath = Arr::get($data, $matches[1]);
                    if (!$imagePath) return '';
                    if ($mode === 'html') return $imagePath;

                    $isUrl = preg_match('/^https?:\/\//', $imagePath);
                    $localPath = $this->getLocalImagePath($imagePath, $isUrl);
                    if ($localPath) {
                        $drawing = new Drawing();
                        $drawing->setPath($localPath);
                        $drawing->setCoordinates($cell->getCoordinate());
                        $drawing->setOffsetX(5)->setOffsetY(5);
                        $drawing->setHeight(60);
                        $drawing->setWorksheet($sheet);
                        $sheet->getRowDimension($cell->getRow())->setRowHeight(50);
                        if ($isUrl) File::delete($localPath);
                    }
                    return '';
                }, $value);

                $value = preg_replace_callback('/\${(?!if|foreach|end|ul|ol|item)([a-zA-Z0-9_.]+)}/', fn ($m) => Arr::get($data, $m[1], $m[0]), $value);
                $cell->setValue($value);
            }
        }
    }

    private function generateBlade($templatePath, $finalOutputPath)
    {
        $spreadsheet = IOFactory::load($templatePath);
        $writer = IOFactory::createWriter($spreadsheet, 'Html');
        $html = $writer->generateHTML();
        $blade = preg_replace_callback('/\${(.*?)}/', fn ($m) => "{{ $" . str_replace('.', '->', $m[1]) . " ?? '' }}", $html);
        File::put($finalOutputPath, $blade);
        $this->info("Blade file generated. Note: Blade conversion is a basic HTML export.");
    }

    private function saveOutput(Spreadsheet $spreadsheet, string $finalOutputPath, string $mode)
    {
        $writerType = ucfirst($mode === 'merge' ? 'Xlsx' : ($mode === 'pdf' ? 'Mpdf' : 'Html'));
        if ($mode === 'pdf' && !class_exists(\Mpdf\Mpdf::class)) {
            throw new \Exception('PDF generation requires mPDF. Please run: composer require mpdf/mpdf');
        }
        $writer = IOFactory::createWriter($spreadsheet, $writerType);
        $writer->save($finalOutputPath);
    }

    private function getLocalImagePath(string $path, bool $isUrl): ?string
    {
        if ($isUrl) {
            $contents = @file_get_contents($path);
            if ($contents === false) { $this->warn("Could not download image..."); return null; }
            $tempPath = storage_path('app/temp/' . Str::random(16) . '.' . pathinfo(parse_url($path, PHP_URL_PATH), PATHINFO_EXTENSION));
            File::ensureDirectoryExists(dirname($tempPath));
            File::put($tempPath, $contents);
            return $tempPath;
        }
        if (File::exists($path)) { return $path; }
        $this->warn("Local image file not found...");
        return null;
    }

    private function getFinalOutputPath(string $basePath, string $mode): string
    {
        $extensionMap = ['merge' => '.xlsx', 'html' => '.html', 'pdf' => '.pdf', 'blade' => '.blade.php'];
        return $basePath . ($extensionMap[$mode] ?? '.xlsx');
    }
}