insertNewRowBefore causes Relative Named Range miscalculation

[jackordman]

This is:

• a bug report
• a feature request
• not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

A relative named range, such as '=$A1' should always point to A, but relative to the row it is being used in.

What is the current behavior?

Inserting x rows makes existing relative ranges point to x rows below where they are being used.

What are the steps to reproduce?

<?php
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$spreadsheet->addNamedRange(new NamedRange('FIRST', $sheet, '=$A1'));
$spreadsheet->addNamedRange(new NamedRange('SECOND', $sheet, '=$B1'));
$spreadsheet->addNamedRange(new NamedRange('THIRD', $sheet, '=$C1'));

$data = [1,2,3,'=FIRST', '=SECOND', '=THIRD'];
$sheet->fromArray($data, null, 'A1');
$sheet->fromArray($data, null, 'A2');
$sheet->fromArray($data, null, 'A3');

$sheet->insertNewRowBefore(1, 4);

$writer = new Xlsx($spreadsheet);
$writer->save('example.xlsx');

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

At least xlsx

Which versions of PhpSpreadsheet and PHP are affected?

PHP 8.2.8
PhpSpreadsheet 1.29.0

[jackordman]

To elaborate on the issue in case I'm being unclear, inserting x rows should change row references by x rows, but it appears the number of rows is being double-counted.

[oleibman]

Confirmed. After the insert, PhpSpreadsheet adjusts the definition for FIRST to $A5; Excel leaves it as $A1. I will need to study the code in question; it might be tricky.

[oleibman]

Oh, this will get messy. Not necessarily for your case, where only a single cell is used in the definition. But, what of a case where there is a range and the "absoluteness" of the parts of the range don't match. You don't even need to insert rows to get in trouble here. Based loosely on ReferenceHelperTest::testInsertRowsWithDefinedNames (which probably does not match Excel's behavior), create a spreadsheet with 'Column1' in A1, then 2, 4, 6, 8, 10, 12, 14, 16 in the subsequent rows in column A. Then define name FIRSTCOLUMN as $A$2:$A6. Then set each of B2, B3, B4, and B5 to =SUM(FIRSTCOLUMN). Excel evaluates B2 as 20, which is the sum of A2:A5. B3 is 30 (A2:A6). B4 is 42 (A2:A7). B5 is 56 (A2:A8). PhpSpreadsheet calculates B2 as 42 (A2:A7), B3 as 56 (A2:A8), and B4 as 72 (A2:A9). I understand what PhpSpreadsheet is doing (goes from absolute address A2 to absolute column A row current row + 6 - 1). I don't understand what Excel is doing. @MarkBaker can you explain it? I can upload code and/or spreadsheet if my description isn't clear.

[jackordman]

Thank you! I had a suspicion it could get messy, from taking a cursory glance at the code for re-calculating ranges.

For anyone else running into this, a simple workaround for now is to add named ranges after inserting rows, as seen below. Although this might not be feasible for every use case, especially more complex ones.

<?php
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$data = [1,2,3,'=FIRST', '=SECOND', '=THIRD'];
$sheet->fromArray($data, null, 'A1');
$sheet->fromArray($data, null, 'A2');
$sheet->fromArray($data, null, 'A3');

$sheet->insertNewRowBefore(1, 4);

$spreadsheet->addNamedRange(new NamedRange('FIRST', $sheet, '=$A1'));
$spreadsheet->addNamedRange(new NamedRange('SECOND', $sheet, '=$B1'));
$spreadsheet->addNamedRange(new NamedRange('THIRD', $sheet, '=$C1'));

$writer = new Xlsx($spreadsheet);
$writer->save('example.xlsx');

[oleibman]

Aha, a semblance of sense. See https://www.ablebits.com/office-addins-blog/excel-named-range/#absolute-relative-names. For relative addressing, the name is defined as relative to the "position of the active cell at the time the name is defined". For my spreadsheet, it is stored as $A$2:$A4; now the results I've seen make sense (perhaps my active cell was A3 rather than A1 when I defined the name), and I can take a stab at fixing the original problem. My working theory, at least to start, is that, for defined names, parts of the address which are absolute should be adjusted, but parts which are relative should not.