phpphpspreadsheetphpoffice-phpspreadsheet

insertNewRowBefore using phpspreadsheet not change coordinate of cell name


I have excel file 3 rows. I give name,amount1 for first row first column. Third row is =amount1+A2.

200  ~ amount1
300
---
500
---

Now, I want to insert one row before row 1 and setValue to column 1. I have using phpspreadsheet to do this. The name, amount1 not go together with value 200 to next row. This affect the formulae result.

new row  ~ amount1
200
300
---
error
---

The code as below:

require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
$reader         =\PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$spreadsheet    =$reader->load("File1.xlsx");
$sheet          =$spreadsheet->getActiveSheet();
$sheet->insertNewRowBefore(1);
$sheet->setCellValue('A1', 'new row');
$writer = new Xlsx($spreadsheet);
$writer->save('File1_output.xlsx');

Solution

  • From getNamedRanges(), update $range->setValue('Sheet1!$C$2');

    Sheet1!$C$1->Sheet1!$C$2

    require '../vendor/autoload.php';
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
    $reader         =\PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
    $spreadsheet    =$reader->load("File1.xlsx");
    $sheet          =$spreadsheet->getActiveSheet();
    $sheet->insertNewRowBefore(1);
    $sheet->setCellValue('A1', 'new row');
     foreach ($spreadsheet->getNamedRanges() as $range) {
      if($range->getName()=='amount1'){
        $range->setValue('Sheet1!$C$2');
       }
    
      }
    $writer = new Xlsx($spreadsheet);
    $writer->save('File1_output.xlsx');