Phpspreadsheet: Defined Name Range from template file not copying to file

[awadekaustubh]

This is:

- [ x] 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?

I am trying to read the xlsx report template file with dynamic charts using defined name range. When data is added from db in defined name range the chart should dynamically update.

What is the current behavior?

After reading the template file and populating the data in column, the output xlsx file shows the chart with error because the defined name range for the column is missing from the newly generated file.

What are the steps to reproduce?

To produce this issue create a simple xlsx template file and add defined name range using the reference formula.

=OFFSET(Worksheet!$B$4,0,0,COUNTA(Worksheet!$B:$B),1)

Insert a simple line chart and select named range in data series using
=Worksheet!NameRangeIdentifer
Save the template and read it using following code, populate values in defined name and save it to new file.

<?php

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

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'report_template.xlsx';
$reader = IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($inputFileName);

// $valArray is array from mysql query execution 
$columnArray = array_chunk($valArray, 1);

$spreadsheet->getSheet(0)->fromArray($columnArray,NULL, "B4");

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->save($fileName);

// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

phpspreadsheet version 1.0.0
Php Version 5.6.34

[rf1234]

Got stuck with that bug too. This is really terrible for me because it looks like I can't finish my project! Are there any work arounds? Does going back to PHP Excel help to avoid this serious bug? Please help!

I am using the latest available version of PHP Spreadsheet.

[stale[bot]]

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.

[vialcollet]

This is still a bug. Can this be reopened?

[azine]

This might be related to #928. So a workaround for @rf1234 might be to avoid spaces in worksheet names or to use a version before commit 50a9bc8 => e.g. v1.4.1

[jhazf]

I had a similar problem. I was using VLOOKUP with defined names. When I read the template using PhpSpreadsheet, I get a "#REF!" value in those fields that are using VLOOKUP and defined names. At first I though it was a VLOOKUP issue. Then with some digging, the issue was caused by defined names. I rolled back from the latest version to version 1.4.1. That fixed the issue. I am now able to get the correct values

[azine]

also see my PR that fixes the problem (for me) within master. #930