Create Named Range with Refers to Worksheet Name having Special Char in C#

Aspose.Cells Product Family
,
1

How to create named Range with Refers to worksheet name having special char in C# using Aspose.Cells APIs. If I have a worksheet named “Q're”, I cannot add a name to it.

Aspose Cells:

workbook.Worksheets.Names.Add(@"'Q''re'!Anything");

throws error : Invalid text for the defined name: ’
yD2N2CCJHF.png (18.8 KB)

I have no problem adding the name using Excel:

ActiveWorkbook.Names.Add "'Q''re'!Anything", refersTo

cQNAFzI2ib.png (1.5 KB)

2

@Moonglum,

Since your worksheet’s name is “Q're” so, you should write this (exact) name while adding named range. Also, when you set RefersTo for that worksheet’s cell(s), then you got to set it properly (e.g. it should be properly encoded and escaped). See the following workaround sample code that works fine as I tested:
e.g.

Sample Code to Create Named Range with Refers to Worksheet Name having Special Char in C#:

Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.Worksheets.Add("Q're");
NameCollection names = workbook.Worksheets.Names;
Name name = names[names.Add(@"Q're!Range1")];
name.RefersTo = "='Q''re'!$A$1";
workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

But it works in Aspose the way above if the sheet name is different:

        workbook.Worksheets.Add("Test");
        var name = workbook.Worksheets.Names[workbook.Worksheets.Names.Add("Test!B_07_2_OtherTaxableIncome_Table_Row1")];

So this appears to be a bug.

4

Please correct this to keep in line with current excel functionality, as we build shared libraries to utilize in both excel and aspose

6

@Moonglum,

After further evaluation of your issue, we found there is an issue (as you described) with Aspose.Cells APIs and it just does not work the same way as MS Excel does. So, we have logged a ticket with an id “CELLSNET-49608”. We will look into it soon.

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

7

@Moonglum,

This is to inform you that we have fixed your issue now. We will provide you the fixed version within the next few days after performing QA and incorporating other enhancements and fixes.

8

@Moonglum,
Please try our latest fix:
Aspose.Cells21.10.4 For .Net2_AuthenticodeSigned.Zip (5.6 MB)
Aspose.Cells21.10.4 For .Net4.0.Zip (5.6 MB)
Aspose.Cells21.10.4 For .NetStandard20.Zip (5.6 MB)

9

The issues you have found earlier (filed as CELLSNET-49608) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao