Hey, I have an issue where copying rows that reference a worksheet-scoped constant value named range into a destination sheet with the same named ranges defined with different constant values will display as having the named range values from the source sheet. I’m using Aspose Cells for .NET 18.10 (although it also replicates in 20.6)
named-range-issue-files.zip (28.9 KB)
Essentially we
- Load an existing workbook, which has
- Sheet “_sheetname_”, with worksheet-scoped range “named.range” set to =“Old value”
- Sheet “firstsheet”, with worksheet-scoped range “named.range” set to =“New value”
- Each sheet has a reference to its named range on the first row.
- Copy “firstsheet” using wb.Worksheets.AddCopy and rename to “secondsheet”
- Copy the first row from “_sheetname_” to “secondsheet”
- Recalculate the workbook and save as HTML
The expected output is to have the data from “firstsheet” and the headers from “sheetname”, such that the first row displays “New value”. I can get this in excel by following the same process and choosing yes at the named range conflict prompt in step 3.
The actual output is that the first row in “secondsheet” displays “Old value” if the workbook is either
- Saved to HTML
- Opened in “protected” view in Excel
- Opened with LibreOffice (6.0.4.2)
Saving as HTML is the most consistent replication of the issue, as excel seems to recalculate assuming the workbook isn’t opened in “protected” mode. When accessed in code the cells referencing the constant named range return “Old value” instead of “New value”, regardless of recalculation
I’ve tried a few combinations of Copy/PasteOptions (particularly the CopyNames option) in CopyRows and explicitly recalculating individual cells but it doesn’t update the calculated values of the cells with the correct named range constant value. The range in “secondsheet” also has IsReferred set to false
Interestingly if I set the cell’s formula value to itself, e.g. “outputWs.Cells[“G1”].Formula = outputWs.Cells[“G1”].Formula;”, it will then calculate with the correct value (this wouldn’t be ideal in my case, as there’s more than one row being copied/cell using the named range).
Here’s some code that replicates the issue, outputting “named range test output 1.html”, which displays the wrong value and “named range test output 2.html”, which displays the correct value after applying the workaround. Not sure if I’m missing a flag in the Copy/Paste options or if it’s a bug.
//1. load existing workbook/worksheets
var wb = new Workbook("named ranges test.xlsx");
var headerSourceWs = wb.Worksheets["_sheetname_"];
var dataSourceWs = wb.Worksheets["firstsheet"];
//2. copy and rename to create "secondsheet"
var outputWs = wb.Worksheets[wb.Worksheets.AddCopy(dataSourceWs.Index)];
outputWs.Name = "secondsheet";
//3. copy the first row from _sheetname_ to secondsheet
outputWs.Cells.CopyRows(headerSourceWs.Cells,0,0,1);
//4. recalculate and save out
wb.CalculateFormula(new CalculationOptions{IgnoreError = true,Recursive = true});
outputWs.Cells["G1"].Calculate(new CalculationOptions{Recursive = true,IgnoreError = true});
//Output 1 - does not recalculate
wb.Save("named range test output 1.html");
wb.Save("named range test output 1.xlsx");
//Output 2 - does recalculate with workaround
outputWs.Cells["G1"].Formula = outputWs.Cells["G1"].Formula;
wb.CalculateFormula();
wb.Save("named range test output 2.html");
wb.Save("named range test output 2.xlsx");
Thanks, Ryan.