<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\File;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
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, 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.';
/**
* Execute the console command.
*/
public function handle()
{
$mode = $this->option('mode');
$templatePath = $this->option('template');
$outputPath = $this->option('output');
$dataPath = $this->option('data');
// 1. Validate inputs
if (!in_array($mode, ['merge', 'html', 'pdf'])) {
$this->error("Invalid mode '{$mode}'. Available modes are: merge, html, pdf.");
return 1;
}
if (!File::exists($templatePath)) {
$this->error("Template file not found at: {$templatePath}");
return 1;
}
if (!File::exists($dataPath)) {
$this->error("Data file not found at: {$dataPath}");
return 1;
}
$finalOutputPath = $this->getFinalOutputPath($outputPath, $mode);
File::ensureDirectoryExists(dirname($finalOutputPath));
try {
// 2. Process data and generate the spreadsheet object in memory
$spreadsheet = $this->generateMergedSpreadsheet($templatePath, $dataPath);
// 3. Save the spreadsheet in the requested format
$this->saveSpreadsheet($spreadsheet, $finalOutputPath, $mode);
$this->info(strtoupper($mode) . " file generated successfully at: {$finalOutputPath}");
return 0;
} catch (\Exception $e) {
$this->error("An error occurred: " . $e->getMessage());
return 1;
}
}
/**
* Loads template and data, performs the merge, and returns the Spreadsheet object.
*/
private function generateMergedSpreadsheet(string $templatePath, string $dataPath): Spreadsheet
{
$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);
$worksheet = $spreadsheet->getActiveSheet();
// Process loops first is crucial for correct row indexing
$this->processLoops($worksheet, $data);
// Process simple placeholders in all remaining cells
$this->processPlaceholders($worksheet, $data);
return $spreadsheet;
}
/**
* Saves the final Spreadsheet object to a file in the specified format.
*/
private function saveSpreadsheet(Spreadsheet $spreadsheet, string $path, string $mode)
{
$writer = null;
switch($mode) {
case 'merge':
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
break;
case 'html':
$writer = IOFactory::createWriter($spreadsheet, 'Html');
break;
case 'pdf':
if (!class_exists(\Mpdf\Mpdf::class)) {
$this->error('PDF generation requires mPDF. Please run: composer require mpdf/mpdf');
throw new \Exception('mPDF library not found.');
}
// This tells PhpSpreadsheet to use the mPDF library
$writer = IOFactory::createWriter($spreadsheet, 'Mpdf');
break;
}
$writer->save($path);
}
/**
* Finds and processes all loop markers `${foreach:key}`.
*/
private function processLoops(Worksheet $worksheet, array $data)
{
// This method's logic remains the same as before
$loopMarkers = [];
foreach ($worksheet->getRowIterator() as $row) {
$cell = $worksheet->getCell('A' . $row->getRowIndex());
$cellValue = $cell->getValue();
if (is_string($cellValue) && preg_match('/^\${foreach:(.+)}$/', $cellValue, $matches)) {
$loopMarkers[$row->getRowIndex()] = $matches[1];
}
}
krsort($loopMarkers);
foreach ($loopMarkers as $rowIndex => $dataKey) {
$loopData = Arr::get($data, $dataKey, []);
if (empty($loopData)) {
$worksheet->removeRow($rowIndex);
continue;
}
$numItems = count($loopData);
if ($numItems > 1) {
$worksheet->insertNewRowBefore($rowIndex + 1, $numItems - 1);
}
for ($i = 0; $i < $numItems; $i++) {
$currentItem = $loopData[$i];
$currentRowIndex = $rowIndex + $i;
foreach ($worksheet->getColumnIterator() as $column) {
$columnIndex = $column->getColumnIndex();
$templateCell = $worksheet->getCell($columnIndex . $rowIndex);
$currentCell = $worksheet->getCell($columnIndex . $currentRowIndex);
if ($i > 0) {
$currentCell->setXfIndex($templateCell->getXfIndex());
}
$templateValue = $templateCell->getValue();
if (is_string($templateValue) && strpos($templateValue, '${') !== false) {
$newValue = preg_replace_callback('/\${item\.(.+?)}/', function ($matches) use ($currentItem) {
return Arr::get($currentItem, $matches[1], '');
}, $templateValue);
if (preg_match('/^\${foreach:.+}$/', $newValue)) {
$currentCell->setValue($i + 1);
} else {
$currentCell->setValue($newValue);
}
}
}
}
}
}
/**
* Finds and replaces simple `${key.name}` placeholders.
*/
private function processPlaceholders(Worksheet $worksheet, array $data)
{
// This method's logic remains the same as before
foreach ($worksheet->getRowIterator() as $row) {
foreach ($row->getCellIterator() as $cell) {
$cellValue = $cell->getValue();
if (is_string($cellValue) && strpos($cellValue, '${') !== false) {
$newValue = preg_replace_callback('/\${(?!foreach:)(.+?)}/', function ($matches) use ($data) {
return Arr::get($data, $matches[1], '');
}, $cellValue);
if ($newValue !== $cellValue) {
$cell->setValue($newValue);
}
}
}
}
}
/**
* Determines the final output path with the correct extension.
*/
private function getFinalOutputPath(string $basePath, string $mode): string
{
$extensionMap = [
'merge' => '.xlsx',
'html' => '.html',
'pdf' => '.pdf',
];
return $basePath . ($extensionMap[$mode] ?? '.xlsx');
}
}
