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');
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');