nhance-enrollment/app/Helpers/ExcelMergeHelper.php

<?php
namespace App\Helpers;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Exception;

class ExcelMergeHelper {
    /**
     * Main merge function with auto-retry
     *
     * @param array $filePaths Array of file paths with optional sheets to merge.
     * @param string $outputPath Path to save the merged Excel file.
     * @return string|null Returns output path on success, null on failure.
     */
    public static function mergeExcelFiles(array $filePaths, string $outputPath): ?string
    {
        try {
            log_message('debug', 'Attempting merge with original file order');
            return self::processFiles($filePaths, $outputPath);
        } catch (Exception $e) {
            log_message('error', 'First attempt failed: ' . $e->getMessage() . ' in ' . $e->getFile() . ' on line ' . $e->getLine());
            log_message('debug', 'Retrying with reversed file order');
            
            // Reverse the file order and try again
            $reversedFiles = array_reverse($filePaths);
            
            try {
                return self::processFiles($reversedFiles, $outputPath);
            } catch (Exception $e2) {
                log_message('error', 'Both attempts failed. Last error: ' . $e2->getMessage() . ' in ' . $e2->getFile() . ' on line ' . $e2->getLine());
                return null;
            }
        }
    }

    /**
     * Process files to merge spreadsheets
     *
     * @param array $filePaths
     * @param string $outputPath
     * @return string
     * @throws Exception
     */
    private static function processFiles(array $filePaths, string $outputPath): string
    {
        log_message('debug', 'Starting Excel merge process');
        log_message('debug', 'Files to process: ' . json_encode($filePaths));
        
        if (empty($filePaths)) {
            throw new Exception("No files provided to merge");
        }

        // Initialize an empty merged spreadsheet
        $mergedSpreadsheet = new Spreadsheet();
        $mergedSpreadsheet->removeSheetByIndex(0); // Remove the default empty sheet
        
        // Process the base file
        $firstFile = array_shift($filePaths);
        self::processSingleFile($firstFile, $mergedSpreadsheet);

        // Process remaining files
        foreach ($filePaths as $index => $fileInfo) {
            self::processSingleFile($fileInfo, $mergedSpreadsheet, $index);
        }

        // Save the merged file
        log_message('debug', "Saving merged file to: {$outputPath}");
        $writer = IOFactory::createWriter($mergedSpreadsheet, 'Xlsx');
        $writer->setPreCalculateFormulas(false);
        $writer->save($outputPath);
        
        // Clean up
        $mergedSpreadsheet->disconnectWorksheets();
        unset($mergedSpreadsheet);
        gc_collect_cycles();
        
        log_message('debug', 'Excel merge process completed successfully');
        return $outputPath;
    }

    /**
     * Process a single file to merge its sheets into the merged spreadsheet
     *
     * @param array $fileInfo
     * @param Spreadsheet $mergedSpreadsheet
     * @param int|null $index
     * @throws Exception
     */
    private static function processSingleFile(array $fileInfo, Spreadsheet $mergedSpreadsheet, int $index = null)
    {
        if (!isset($fileInfo['file_path']) || !file_exists($fileInfo['file_path'])) {
            $path = $fileInfo['file_path'] ?? 'undefined';
            log_message('error', "File " . ($index ?? 'base') . ": Invalid or missing file path: {$path}");
            return;
        }

        log_message('debug', "Processing file " . ($index ?? 'base') . ": " . $fileInfo['file_path']);
        
        try {
            // Load the source spreadsheet
            $sourceSpreadsheet = IOFactory::load($fileInfo['file_path']);
            
            // Get all worksheets
            $worksheets = $sourceSpreadsheet->getAllSheets();
            $totalSheets = count($worksheets);
            log_message('debug', "Total sheets in file: " . $totalSheets);

            $sheetsToMerge = $fileInfo['sheets'] ?? [];

            // Process each worksheet
            foreach ($worksheets as $sheetIndex => $worksheet) {
                if (empty($sheetsToMerge) || in_array($sheetIndex, $sheetsToMerge)) {
                    try {
                        $sheetName = $worksheet->getTitle();
                        log_message('debug', "Processing sheet: {$sheetName}");

                        // Generate unique sheet name before cloning
                        $newName = $sheetName;
                        $counter = 1;
                        
                        while (in_array($newName, $mergedSpreadsheet->getSheetNames())) {
                            $newName = $sheetName . "_" . $counter++;
                            log_message('debug', "Sheet name already exists. Trying new name: {$newName}");
                        }

                        // Clone the worksheet and set the new name
                        $clonedSheet = clone $worksheet;
                        $clonedSheet->setTitle($newName);

                        // Add as external sheet
                        $mergedSpreadsheet->addExternalSheet($clonedSheet);

                        log_message('debug', "Successfully added sheet: {$newName}");
                    } catch (Exception $e) {
                        log_message('error', "Error processing sheet {$sheetName} as new name {$newName}: " . $e->getMessage() . ' in ' . $e->getFile() . ' on line ' . $e->getLine());
                    }
                }
            }
            
            // Clean up source spreadsheet
            $sourceSpreadsheet->disconnectWorksheets();
            unset($sourceSpreadsheet);
            gc_collect_cycles();
            
        } catch (Exception $e) {
            log_message('error', "Error processing file {$fileInfo['file_path']}: " . $e->getMessage() . ' in ' . $e->getFile() . ' on line ' . $e->getLine());
        }
    }
}