Strings with ' in them

Topics: Developer Forum, User Forum
Feb 9, 2007 at 3:36 PM
The Cell.Value function seems to dislike any strings with ' in them. Would it be worth having a function called something like Cell.TextValue that does not do any checking on the value?
Mar 5, 2007 at 4:05 PM
I am not sure a function such as this will do much good. I can't seem to get any apostrophe usage to work because the intermediate XML is invalid.

XML attributes may be wrapped as such:

 
   <MyElement  MyAttribute="" />
 
   or
 
   </MyElement  MyAttribute='' />
 

The problem is that if you have to insert one of the characters being used as a text wrapper " or ', then some sort of sequencing is needed or the disable-text-escaping attribute of XSL, etc. I believe that internally you are storing the values to some other type of XML and then transforming them over to the sharedstrings.xml. The sharedstrings.xml would not have a problem with either character as it uses element data, but the previous XML transformation would.

Can you provide a fix for this?
Sep 18, 2007 at 10:20 AM
My solution is to change line 456 of ExcelCell.cs from:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:sid:t='{0}'", Value), _xlWorksheet.NameSpaceManager);

to:
String xPathValue = GetXPathString(Value);
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:sid:t=" + xPathValue + ""), _xlWorksheet.NameSpaceManager);

Moreover it must declare this function:

private static string GetXPathString(string input)
{
if (input.IndexOf("'") > -1 && input.IndexOf("\"") > -1)
return "concat('" + input.Replace("'", "', \"'\", '") + "')";
else if (input.IndexOf("\"") > -1)
return "'" + input + "'";
else
return "\"" + input + "\"";
}

It seems to works fine, any comments?

Diego
May 13, 2008 at 4:53 PM
Edited May 13, 2008 at 4:59 PM
Diego,

I tried this and it does not seem to work.  When I put in that code to the current version of the ExcelCell.cs, then try simple sample code :

...
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
worksheet.Cell(1, 1).Value = "Product";
worksheet.Cell(2, 1).Value = "Broad Beans";
worksheet.Cell(3, 1).Value = "Joe's Carrots";
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";

...

It now dies on worksheet.Cell(1, 1).Value = "Product";, whereas that line worked previously.
Also I am not sure of the purpose of you having
string.Format("//d:sid:t=" + xPathValue + "")
instead of
string.Format("//d:sid:t=" + xPathValue)
(what is the purpose of adding a blank string?)

In either case though it seems to die stating an invalid token.

I ended up with a simpler solution that appears to work.  Just change this line in SetSharedString:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);
to this:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value.Replace("'", "&quot;")), _xlWorksheet.NameSpaceManager);
Jun 29, 2008 at 4:11 AM


lsdeans wrote:
Diego,

I tried this and it does not seem to work.  When I put in that code to the current version of the ExcelCell.cs, then try simple sample code :

...
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
worksheet.Cell(1, 1).Value = "Product";
worksheet.Cell(2, 1).Value = "Broad Beans";
worksheet.Cell(3, 1).Value = "Joe's Carrots";
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";

...

It now dies on worksheet.Cell(1, 1).Value = "Product";, whereas that line worked previously.
Also I am not sure of the purpose of you having
string.Format("//d:sid:t=" + xPathValue + "")
instead of
string.Format("//d:sid:t=" + xPathValue)
(what is the purpose of adding a blank string?)

In either case though it seems to die stating an invalid token.

I ended up with a simpler solution that appears to work.  Just change this line in SetSharedString:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);
to this:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value.Replace("'", "&quot;")), _xlWorksheet.NameSpaceManager);


Wouldn't you want &apos; instead of &quot; for a single quote?
Aug 8, 2008 at 8:27 PM
Edited Aug 12, 2008 at 11:14 PM
Considering special chars in an Xml Doc are {'&', '<', '>', '\"', '\''}, it would be safer to handle all those chars by simply adding the lines below before using it in the method. 

if(!string.IsNullOrEmpty(Value))
    Value = System.Security.
SecurityElement.Escape(Value);

 

However,  when generating the excel file, the equivalent chars are not parsed back (for example "'" escaped with &apos; and it appears as &apos) in the excel cells.

Any one has an idea? Thanks.

Aug 13, 2008 at 6:33 PM

Never mind.

My mistake, had missed the line underneath for setting the inner text also. You may use the line below instead. Thanks.

 

 

XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", System.Security.SecurityElement.Escape(Value)), _xlWorksheet.NameSpaceManager);

 

Mar 19, 2009 at 5:22 PM
The solution provided by atmacam works well. This should have made it into a patched version and saved lots of people the headaches.
Nov 9, 2009 at 3:05 PM
atmacam wrote:
XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", System.Security.SecurityElement.Escape(Value)), _xlWorksheet.NameSpaceManager);

 This worked well for us as well. I agree this should go into a patch or release asap! Would have been a headache if it wasn't for this thread!

Thanks atmacam!

Jan 19, 2010 at 5:59 AM

Further to the accepted solution (it took me a while to figure out how to implement it and that it wasn't just referring to my usage of the release dll).

Download and build the source code (as a class library) with the below change. If your project can't find System.IO.Packaging add a reference to WindowsBase.

If the project wont load from the .sln or .csproj make a new project and copy in the .cs files.

In ExcelCell.cs find the string
         * XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);
         * replace with
         * XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", System.Security.SecurityElement.Escape(Value)), _xlWorksheet.NameSpaceManager);

Reference the built .dll in your project.

Feb 20, 2010 at 12:02 AM

Thanks!  This worked perfectly for me.