Properly deleting named ranges in Aspose.Cells (C#)

Aspose.Cells Product Family
1

Hi,

in previous versions of Aspose.Cells we were able to properly remove named ranges by just calling Remove() on the NameCollection. I understand that this no longer works due to the introduced sanity checks of formulae referencing named ranges and in this case, the name would not be removed, but only the cell references (ReferTo). So, in order to properly / fully remove referenced named ranges, I tried the following

  1. Identify the range / formulae referencing to be deleted named ranges

  2. Call ClearRange on the named range that holds references to other named ranges

  3. Remove the named range that used to hold the formula incl references to other named ranges

  4. Remove the named ranges that were previously referenced

I guess #1 and #2 work just fine, as I can see in the debugger, but when calling Remove() for the no longer referenced named ranges, all that Aspose.Cells does is clear the ReferTo but not remove the named ranges altogether.

Please advise.

Thanks

Kai

2

@Kai_Iske,

Thanks for providing us details.

Could you create a simple sample console application, zip the project (excluding Aspose.Cells library) and post us here to reproduce the issue on our end, we will check it soon. Also, attach template files (if any). Moreover, some screenshots would also be helpful to understand your issue precisely and to figure it out soon.

3

Hi, I did create a sample, using the following code. Note the range “INTEREST_EN” will reference some other ranges that I hoped to get rid of, but clearing the range’s contents.

Template.zip (32.6 KB)

using System.Linq;
using Aspose.Cells;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			var file = new Workbook(@"..\..\Template.xls");

			var interestRange = file.Worksheets.GetRangeByName("INTEREST_EN");
			file.Worksheets[2].Cells.ClearRange(interestRange.FirstRow, interestRange.FirstColumn, interestRange.FirstRow + interestRange.RowCount - 1, interestRange.FirstColumn + interestRange.ColumnCount - 1);
			file.Worksheets.Names.Remove("INTEREST_EN");

			// First remove the non _EN ranges
			foreach(var name in from name in file.Worksheets.Names.Cast<Name>().Where(range => !range.Text.EndsWith("_EN")).ToArray() let range = file.Worksheets.GetRangeByName(name.Text) where range != null && (range.Worksheet.Name.Contains("Deckblatt") || range.Worksheet.Name.Contains("Cover")) select name)
			{
				file.Worksheets.Names.Remove(name.Text);
			}

			// Next, rename _EN to non _EN
			foreach(var name in from name in file.Worksheets.Names.Cast<Name>().Where(range => range.Text.EndsWith("_EN")).ToArray() let range = file.Worksheets.GetRangeByName(name.Text) where range != null && (range.Worksheet.Name.Contains("Deckblatt") || range.Worksheet.Name.Contains("Cover")) select name)
			{
				name.Text = name.Text.Substring(0, name.Text.Length - 3);
			}
		}
	}
}
4

@Kai_Iske,

Thanks for the template file and sample code.

After an initial test, I am able to observe the issue as you mentioned by using your sample code with your template file. I found an exception “The defined name[xxxxxx] already exists” when renaming named ranges containing name text “_EN” back to “non _EN”. I have logged a ticket with an id “CELLSNET-45750” for your issue. We will check your issue thoroughly if your sample code needs tweak or it is an issue so it should be fixed.

Once we have an update on it, we will let you know here.

5

Sure, the name is already present, as the previous “Remove” did not remove the range, but only clear its ReferTo property

Thanks for passing on to dev support

6

@Kai_Iske,

We did evaluate your issue thoroughly.
I am afraid, we could not remove the named range and only can remove reference of the defined name if it’s referred by some formulas in the cells. For example, some named ranges are referred by Deckblatt!G14.

7

You’re right. I should have checked the names properly first. Removing the correct formula and making sure to clean up properly before deleting the named ranges, solved the issue.

Thanks

8

@Kai_Iske,

Good to know that your issue is sorted out now. Feel free to write us back if you have further comments or questions, we will be happy to assist you soon.