Issue when saving to SpreadsheetML when there is DoubleQuote in the RefersTo of a Name object

Hi,

I get an error when opening a SpreadsheetML file in Excel when I create a file with the following code…

//Example 1

var workbook = new Workbook();



var rangeName1 = “NamedRange1”;

var value1 = “MID-WESTERN “Pen and Paper”;

var nameIndex1 = workbook.Worksheets.Names.Add(rangeName1);

var name1 = workbook.Worksheets.Names[nameIndex1];

name1.RefersTo = string.Format(”="{0}"", value1);



workbook.Save(“DoubleQuoteNamedRange.xml”);


What the code is doing is adding a Name to the Worksheets.Names collection . The value of the RefersTo property is a string. The issue is that the string contains a single Double Quote. If I open the saved file in Excel, Excel prompts me with a message “Problems came up in the following areas during load: Workbook Setting"

If the value of the RefersTo contains a string with 2 Double Quotes, then there is no problem as in the code below…

//Example 2
var workbook = new Workbook();


var rangeName2 = “NamedRange2”;
var value2 = “Mid-Western “Pen” and Paper”;
var nameIndex2 = workbook.Worksheets.Names.Add(rangeName2);
var name2 = workbook.Worksheets.Names[nameIndex2];
name2.RefersTo = string.Format(”="{0}"", value2);

workbook.Save(“DoubleQuoteNamedRange.xml”);


Can this be fixed?

Thanks

Harry

Hi Harry,

Thanks for pointing out this issue.

I can reproduce it with latest version of Aspose.Cells for .NET 5.2.2.6.

This issue has been logged as CELLSNET-24692.

Hi,

We could not parse the formula as =" MID-WESTERN “Pen and Paper " because we could not know which DoubleQuote is the end of a string .

Please change your code as the following :


Workbook workbook = new Workbook();


string rangeName1 = “NamedRange1”;

string value1 = “MID-WESTERN ““Pen and Paper”;

int nameIndex1 = workbook.Worksheets.Names.Add(rangeName1);

Name name1 = workbook.Worksheets.Names[nameIndex1];

name1.RefersTo = string.Format(”=”{0}”", value1);


workbook.Save(@“D:\FileTemp\DoubleQuoteNamedRange.xml”);

Are you suggesting that I double up on the double quotes in the case where there is an odd number of double quotes in my string? I don’t think that is a good solution/

Hi Harry,

It’s the limitation.

It works as MS Excel.

You will get an exception if you try to enter a formula with an odd number of double quotes.