<?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');
}
}
