Hello,
I just recently noticed a regression in the functionality for the Documents.Excel.NamedReference object between the older WinForms 15.2 release and the latest service release of WinForms 16.2
I am using the method NamedReference .ReferencedRegion to resolve Excel Names that define a region of cells. In particular, I have found that if the formula that defines the ReferencedRegion contains nested Names, NamedReference .ReferencedRegion return null.
For example, if an Excel Name is defined by the formula below, the corresponding NamedReference .ReferencedRegion return null:Column_1 = OFFSET('Complete Revision List'!$A$5,0,0,PageLength, 9)
If I then replace the Name ‘PageLength’ in the formula with its single cell reference, (see below) NamedReference .ReferencedRegion returns a valid WorksheetRegion: Column_1 = OFFSET('Complete Revision List'!$A$5,0,0,D$4$, 9)
Interestingly, I have found I can cause a NamedReference to recalculate and NamedReference.ReferencedRegion to return a non-null value if I first ‘tickle’ the object using: namedReference.SetFormula(namedReference.Formula);
Questions:- Is this a known issue with NamedReference objects in the latest release of WinForms 16.2?- Should I be concerned about the parsing of other cell values that contain nested Name formulas?
This use of NamedReference .ReferencedRegion is key to our application correctly interpreting Excel spreadsheets. Let me know if I can provide more information and spreadsheets and/or sample code to reproduce the issue.
Thanks,Ben Bishoff
Hi Ben,Can you send me a sample application where the issue can be reproduced?
Thanks,Josheela
Josheela,
Yes, I can send a sample application with some data to reproduce the issue. I'll post the files in the next day or two.Thanks for offering to take a look.Cheers,Ben
Hi Josheela,Please find attached a sample project and spreadsheet that can recreate the issue.
In summary, I am finding difference in resolving complex NamedReference and Cell values depending on the Infragistics WinForms versions (see details below).
Thanks again for looking, let me know if I can answer any questions.
Cheers,Ben
Version 15.2.20152.2023 • NamedReference.ReferencedRegion always resolved • Cell.Value == #NAME
Version 16.2.20162.2040 • WorkbookLoadOptions(null, null): o NamedReference.ReferencedRegion NOT resolved o Cell.Value == #NAME • Other WorkbookLoadOptions: o NamedReference.ReferencedRegion resolved o Cell.Value resolved UNLESS NamedReference.ReferencedRegion is used THEN Cell.Value == #NAME
Hi Ben,
I am trying to reproduce the issue using the sample attached and will bring into the attention of a developer if its a regression issue introduced between the releases.
Will update you as soon as I have all the details.
Thanks.
I have investigated your issue, and I have asked our engineering staff to examine this further. To ensure that it will receive attention, I have logged this behavior in our internal tracking system with a Development ID of 233375. This will be assigned to a developer in a future sprint to review my investigation and confirm my findings or to offer a fix, or other resolution.
I will leave this case open and update you with any new information after the review. You can also continue to send updates to this case at any time.
You can view the status of the development issue connected to this case by selecting the "Development Issues" tab when viewing this case on the web site.
Please let me know if you need more information.
Thanks for your investigations and passing this on to development, I appreciate your prompt attention.
This is a high priority issue for us. Our biggest concern is that there is other regressive behavior in the calculation of formulas that we have not yet encountered.
For your information, what follows is the approach we are taking for our next release.
We decided to use version 16.2 and stick with our current workbook load method:
System.Security.SecureString secureString = new System.Security.SecureString();WorkbookLoadOptions options = new WorkbookLoadOptions(secureString, null, false);Workbook infragisticsWorkbook = Workbook.Load(fileStream, options);
As you saw in my sample code, this leads to situations where no WorksheetRegion is found for a NamedReference defined by a "complex" formula. In these cases I found a workaround - forcing the NamedReference to recalculate will find the associated WorksheetRegion:
if (namedReference.ReferencedCell == null && namedReference.ReferencedRegion == null && namedReference.ReferencedRegions == null) { namedReference.SetFormula(namedReference.Formula); ...
Unfortunately, again as shown in my sample code, this will cause certain Cell.Value to return the value #NAME.
Thanks again for your attention to this issue.
Josheela and all,
I have finally had a break in my production cycle to test the fix for this issue added in the March 2018 SR of 2017.1 and 2017.2.
Thank you, all is working as expected! The formulas for complex NamedReferences are now being evaluated without forcing their value using the workaround code:namedReference.SetFormula(namedReference.Formula);
Thanks to you and your dev team for prompt attention to this issue!!