excel - spout php read new column from xlsx then insert to existing file xlsx

Question

I want to update a column of test.xlsx file from testinput.xlsx file when names are equal.

I have these

test.xlsx

testinput.xlsx

I want this

testnew.xlsx

this is my php code

but i am really confused, i think i am lost in iterating and problem with the $writer->addRow($row);

<?php

require_once 'spout/src/Spout/Autoloader/autoload.php';

use BoxSpoutReaderCommonCreatorReaderEntityFactory;
use BoxSpoutWriterCommonCreatorWriterEntityFactory;

$existingFilePath = 'test.xlsx';
$existingFilePath2 = 'testinput.xlsx';
$newFilePath = 'testnew.xlsx';

// we need a reader to read the existing file...
$reader = ReaderEntityFactory::createReaderFromFile($existingFilePath);
$reader->open($existingFilePath);

$reader2 = ReaderEntityFactory::createReaderFromFile($existingFilePath2);
$reader2->open($existingFilePath2);
//$reader->setShouldFormatDates(true); // this is to be able to copy dates

// ... and a writer to create the new file
$writer = WriterEntityFactory::createWriterFromFile($newFilePath);
$writer->openToFile($newFilePath);


// let's read the entire spreadsheet
foreach ($reader->getSheetIterator() as $sheetIndex => $sheet) {
    // Add sheets in the new file, as you read new sheets in the existing one
    if ($sheetIndex !== 1) {
        $writer->addNewSheetAndMakeItCurrent();
    }

    foreach ($sheet->getRowIterator() as $rowIndex => $row) {
        $name = $row->getCellAtIndex(0);
        $name_index = 0;
        $data = $row->getCellAtIndex(1);
        $data_index = 1;
        $input = $row->getCellAtIndex(2);
        $input_index = 2;
        echo "<br><br>rowIndex = {$rowIndex} name = {$name} <br>";

        foreach ($reader2->getSheetIterator() as $sheetIndex2 => $sheet2) {

            foreach ($sheet2->getRowIterator() as $rowIndex2 => $row2) {
                $name2 = $row2->getCellAtIndex(0);
                $name_index2 = 0;
                $newinput2 = $row2->getCellAtIndex(1);
                $newinput_index2 = 1;

                if ($name == $name2) {


                    $temp_rowIndex = intval($rowIndex)+1;
                    $temp_rowIndex2 = intval($rowIndex2)+1;

                    echo "rowIndex = {$rowIndex} name = {$name} ---- rowIndex2 = {$rowIndex2} name2 = {$name2} <br>";

                    $row->setCellAtIndex(WriterEntityFactory::createCell($newinput2), 3);

                }
            }
        }
        // write the edited row to the new file
        $writer->addRow($row);
    }
}

$reader->close();
$writer->close();

question from:https://stackoverflow.com/questions/65897948/spout-php-read-new-column-from-xlsx-then-insert-to-existing-file-xlsx

Answer 1

Waitting for answers