Hi. After upgrading from version 5 to version 8, my users have noticed a change with the behavior of named ranges. For example, I have an xlsm with a named range that refers to $A$2 in a sheet that gets added programatically server-side. Once the data is generated server-side and the xlsm is opened, the named range gets changed to include all rows in Column A i.e. $A$2:$A$10. I’ve tried several versions ranging from 5.x to 7.x and none of those versions exhibited this behavior. Is this possibly a bug? If not, is there a workaround for this behavior?
Thanks,
Kevin
Hi Kevin,
Thank you for contacting Aspose support.
Please provide us a standalone sample application along with input spreadsheet (if any) to replicate the said problem while using the latest version of Aspose.Cells for .NET 8.5.0. This will help is quickly analyze the presented behavior, and to provide a fix, if found to be a bug on the part of Aspose.Cells APIs.
Hi, here is a working example project showing how the named range “EffectiveDate” gets changed from a specific cell reference to a range following processing by Aspose Cells. This behavior was not present in versions prior to 8.x.
Hi,
Thanks for the sample project.
I have tested your scenario/ case using your sample project with Aspose.Cells for .NET v8.5.0. I think it exhibits the correct behavior for your underlying List object/ Table. The “EffectiveDate” named range marker is inserted into A2 cell and it changes to “=Sheet1!$A$2:$A$7” which is correct behavior as there are 6 records in the DataTable that are pasted when the markers are processed. Do you think it is not correct behavior, could you provide us your correct file that you get using Aspose.Cells version prior 8.x, we will check it soon.
Thank you.
Hi. Here is the same project built against version 7 Aspose Cells. You will see that after processing, the named range “EffectiveDate” is defined as Sheet1!$A$2. If this change in behavior was intentional, was it considered a bug prior to the change or was just an intentional change in behavior? This change is causing issues for some of our customers who have coded macros around the prior behavior.
Thanks for looking into this. I should point out that it is critical for us to have the ability to set a named range to a specific cell and not have it overridden when data is added by Aspose.
Hi,
We think you should use “noadd” and “copystyle” parameters when inserting smart markers to mimic your desired results regarding named ranges. Please change the markers in your template file (in A2 and B2 cells) as following, it works fine and as per your requirements:
A B
… …
&=Data1.StartDate(copystyle, noadd) &=Data1.EndDate(copystyle, noadd)
Thank you.
Hi. This is getting us closer but what we’ve found is (noadd) only helps us if we apply it to all cells. I don’t see a way to have one named range stay referencing only $A$2 while another named range expands to reference $B2:$B (as data grows).
Hi,
Well, yes, you might be right. And, I think there is no better way to cope with it where you require one named range should not grow (you use noadd parameter) while other would grow. But I think as you originally required to mimic your older version’s behavior where the named range won’t extend and stay static, so the “noadd” attribute would work in that case.
Thank you.
The problem is we do have a requirement to have named ranges on the same sheet where some stay static, usually only referencing one cell and others that grow with the data. If there is no way to achieve this behavior it is going to be a pretty major hardship for us. We have lots of macros that require this ability. If the columns that used noadd would stay static and the columns that do not would grow, that would work fine for us. This doesn’t appear to be the case though. We would appreciate any help you can offer us that would help us avoid rewriting a lot of our macros in order to upgrade to version 8 and beyond.
Thanks,
Kevin
Hi,
prudential rawal:
The problem is we do have a requirement to have named ranges on the same sheet where some stay static, usually only referencing one cell and others that grow with the data.
How did you cope with it using older version of the product which does not grow the named ranges with data and only stays static?
We also appreciate if you could create a simple console application (runnable) with a template file (having your desired markers against named ranges pasted in parallel on the sheet) to show the behavior/ issue with latest version of the product, you may zip the project and attach it here. You may create dynamic data tables in the code for the markers as data source to remove any inter dependencies etc. We will look into it if we can do something for it or not.
Thank you.
Hi.
Here is a version compiled against version 7 that demonstrates how our user is getting a named range stay referencing a single cell while another range on the same sheet grows according to the number of rows.
Thanks,
Kevin
Hi,
Thanks for providing us sample project.
I have evaluated your project with older version v7.x and you are right, it is giving different behavior as you pointed out. Well, as a workaround, you may still accomplish your task with latest version of the product i.e., Aspose.Cells for .NET v8.5.x. First you need to update your markers to:
&=Data1.StartDate(noadd, copystyle) &=Data1.EndDate(noadd, copystyle)
then, you need to insert blank rows/range accordingly, so your second range should be extended accordingly for your requirements, you need to add a line of code to your code segment, it would work as per your needs:
e.g
Sample code:
…
designer.Workbook = new Workbook(@"…\demo.xlsm");
designer.SetDataSource(dt.TableName, dt.DefaultView);
int index = 2;
//Insert n blank rows to accomplish your task/ requirements
designer.Workbook.Worksheets[0].Cells.InsertRows(index, dt.Rows.Count - index);
designer.Process(false);
…
Hope, this helps a bit.
Thank you.