Named Ranges - Support Formulas

[dericlima]

This is:

- [x] a feature request

What is the expected behavior?

Creation of a named range that will contain a formula

What is the current behavior?

Formulas aren't supported in named ranges

What are the steps to reproduce?

Create a excel with a named range that contains a formula.

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$spreadsheet->createSheet();
$spreadsheet->getActiveSheet()->fromArray(
    ['Prod A', 'Prod B', 'Prod C', 'Prod D', 'Prod E'],
    null,
    'A1'
);$spreadsheet->getActiveSheet()->fromArray(
    [1, 2, 3, 4, 5],
    null,
    'A2'
);
$spreadsheet->addNamedRange(new NamedRange('sumProducts', $spreadsheet->getActiveSheet(), '=SUM(A2:E2)'));

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

Which versions of PhpSpreadsheet and PHP are affected?

PHPSpreadsheet 1.9.0
PHP 7.2.24

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[MarkBaker]

This is currently work in progress, but it is a fairly major piece of code change, so it may well still take a month or so of work

[dericlima]

@MarkBaker I tried to solve this on my own but without a full solution yet. I'm glad to help if I can....

[MarkBaker]

Perhaps once the branch is more stable and I push it to the repo here. At the moment,it's still very much prototyping code to prove the principle, but it's a fairly major redesign of how defined names are handled, and it will take me a week or two before I'm satisfied enough to create a WIP PR.

Once there, I'll be happy if you want to do some testing against that branch.

[MarkBaker]

You might want to take look at Merge Request #1535 and run some tests; it should provide pretty comprehensive coverage of named formulae, plus a lot of improvements and fixes for the handling of name ranges as well. There are still some cases that aren't covered; but it's pretty comprehensive... have a read of the notes and documentation as well

[dericlima]

@MarkBaker Thanks for this!! It's working like a charm. Feel free to close this issue

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.