We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

CopyRows does not use destination named range values in formulae

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

  1. 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.
  2. Copy “firstsheet” using wb.Worksheets.AddCopy and rename to “secondsheet”
  3. Copy the first row from “_sheetname_” to “secondsheet”
  4. 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.

@ryanmcarthur,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47415 - CopyRows does not use destination named range values in formula

@ryanmcarthur,

We will throw a warning regarding DuplicateDefinedName in the next fix (we have resolved the issue now). Please impement IWarningCallback interface to process such an issue. See the following sample code for your reference:
e.g
Sample code:

    var wb = new Workbook(dir + "named ranges test.xlsx");
    wb.Settings.WarningCallback = new CustomWarningCallback(wb);

    ```
   class CustomWarningCallback : IWarningCallback
       {
            Workbook wb;
            internal CustomWarningCallback(Workbook workbook)
           {
                this.wb = workbook;
            }
            public void Warning(WarningInfo warningInfo)
           {
                switch (warningInfo.WarningType)
               {
                    case WarningType.DuplicateDefinedName:
                        int index = (int)warningInfo.ErrorObject;
                        Name name = wb.Worksheets.Names[index];
                        int index1 = wb.Worksheets.Names.Add(name.Text + "_1");
                        wb.Worksheets.Names[index1].RefersTo = name.RefersTo;
                        warningInfo.CorrectedObject = index1;
                        return;
                   default:
                        break;
                }
            }
        }

Thanks guys, that fix looks like it should cover the issue. I’ll post back here if I run into any issues once the fix is available.

@ryanmcarthur,
Good to know that your issue is sorted out. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

@ryanmcarthur,
Please try our latest version/fix: Aspose.Cells for .NET v20.6.1:
Aspose.Cells20.6.1 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.6.1 For .Net4.0.Zip (5.4 MB)

Your issue should be fixed in it.

Let us know your feedback.

Thanks for that, I’ve re-tested with the updated dlls and it seems to be working well.

I did need to modify the warning callback implementation detailed above to specifically use the range from the output sheet instead of using the range from the old sheet with a non-conflicting name, but it seems to work okay for both cases.

Thanks for the quick response!

@ryanmcarthur,

Good to know that the new fix/version fixes your issue for your scenario/ cases. Feel free to write us back if you need further help or have some queries or comments, we will be happy to assist you soon.

The issues you have found earlier (filed as CELLSNET-47415) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui