phpphpspreadsheet

PhpSpreadsheet Notice: Undefined offset: 4


I have been struggling with finding a way to read excel files in a directory, copy the worksheet called "Division" into a new file, then number the new worksheets increment by one - Division1, Division2, etc...

The following is my code, which I am receiving a warning for undefined index, which is not a big deal, but it only creates a new excel file with only four worksheets, when there should be over 240 worksheets.

        ini_set('display_errors', 1);
        ini_set('display_startup_errors', 1);
        error_reporting(E_ALL);

        use PhpOffice\PhpSpreadsheet\Helper\Sample;
        use PhpOffice\PhpSpreadsheet\IOFactory;

        $exportedFiles = scandir('PhpSpreadsheet/Export');
        $inputFileNames = [];
        $sheetnames = [];
        $outfile = 'all-together-now.xlsx';

        foreach($exportedFiles as $key=> $value) {
            if ($value == '.' || $value == '..') {

            } else {
                array_push($inputFileNames, 'PhpSpreadsheet/Export/' . $value);
                array_push($sheetnames, 'Division');
            }
        }

        $inputFileType = 'Xlsx';
        $reader = IOFactory::createReader($inputFileType);
        $reader->setLoadSheetsOnly($sheetnames);
        $contador = 1;

        foreach ($inputFileNames as $book => $inputFileName) {
            echo ('$inputFileName: ' . $inputFileName) . '</br>'; 

            $reader = IOFactory::createReader("Xlsx");
            $spreadsheet = $reader->load($inputFileName);
            $clonedWorksheet = clone $spreadsheet->getSheetByName('Division');
            $clonedWorksheet->setTitle('Division' . $contador);
            $spreadsheetMain = $reader->load($outfile);
            $spreadsheetMain->addSheet($clonedWorksheet);
            $writer = IOFactory::createWriter($spreadsheetMain, "Xlsx");
            $writer->save($outfile);
            $contador++;
        }

The following are the warning notices:

        Notice: Undefined offset: 4 in
        PhpOffice\PhpSpreadsheet\Writer\Xlsx->save( )
        PhpOffice\PhpSpreadsheet\Spreadsheet->garbageCollect( )

As usual, thanks in advance


Solution

  • Part of the solution was bumping up the memory and the other was to use addSheet()

    The following is the code that works:

    ini_set('memory_limit','32768M');
    
    use PhpOffice\PhpSpreadsheet\IOFactory;
    
    require_once 'PhpSpreadsheet/src/Bootstrap.php';
    
    $exportedFiles = scandir('PhpSpreadsheet/Export');
    
    $inputFileNames = [];
    
    $sheetnames = [];
    
    $outfile = 'all-together-now.xlsx';
    
    foreach($exportedFiles as $key=> $value) {
        if ($value == '.' || $value == '..') {
    
        } else {
            array_push($inputFileNames, 'PhpSpreadsheet/Export/' . $value);
            array_push($sheetnames, 'Division');
        }
    }
    
    $inputFileType = 'Xlsx';
    $reader = IOFactory::createReader($inputFileType);
    $reader->setLoadSheetsOnly($sheetnames);
    $contador = 1;
    
    foreach ($inputFileNames as $book => $inputFileName) {
        echo ('$inputFileName: ' . $inputFileName) . '</br>';
        $reader = IOFactory::createReader("Xlsx");
        $spreadsheet = $reader->load($inputFileName);
        $spreadsheet->getSheetByName('Division')->getStyle('Division');
        $clonedWorksheet = clone $spreadsheet->getSheetByName('Division');
        $clonedWorksheet->setTitle('Division' . $contador);
    
        /* open new file for writing spread sheets to it */
        $spreadsheetMain = $reader->load($outfile);
        $spreadsheetMain->addSheet($clonedWorksheet);
        $writer = IOFactory::createWriter($spreadsheetMain, "Xlsx");
        $writer->save($outfile);
        $contador++;
    }
    $spreadsheet->disconnectWorksheets();
    echo "The process has completed";
    die();
    

    The aforementioned script created an excel workbook with 247 worksheets from the 247 excel files