2

I am using Excelpackage.codeplex.com to create xlsx.

When writing a string with apostrophe (') I get a System.Xml.XPath.XPathException exception.

How can I write an xlsx cell with ' using that package?

private void ExportApostrphoe()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Name");

        dt.Rows.Add(1, "Ben");
        dt.Rows.Add(2, "Joe's");
        dt.Rows.Add(3, "Mike");

        FileInfo newFile = new FileInfo(@"c:\1.xlsx");
        using (ExcelPackage xlPackage = new ExcelPackage(newFile))
        {
            OfficeOpenXml.ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");
            for (int x = 0; x < dt.Columns.Count; ++x)
            {
                DataColumn column = dt.Columns[x];
                worksheet.Cell(1, 1 + x).Value = column.Caption;

            }
            for (int x = 0; x < dt.Columns.Count; ++x)
            {
                for (int y = 0; y < dt.Rows.Count; ++y)
                {
                    worksheet.Cell(2 + y, 1 + x).DataType = "text";
                    worksheet.Cell(2 + y, 1 + x).Value = Convert.ToString(dt.Rows[y][x]);

                }

            }
            xlPackage.Save();
        }
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michael Blok
  • 221
  • 1
  • 4
  • 15
  • I expect you have to use an escape character on the '. What that is for C# or codeplex, i am not sure. – Pynner Nov 16 '12 at 01:53

2 Answers2

0

Try to update follwoing code in ExcelCell.cs Search for XmlNode stringNode and update same with below code. Create a dll. It worked for me.

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

The Codeplex site for the library has some posts that reference a fix by making changes to the source and recompiling the library, but for a quicker fix I've been able to work around it by replacing single quotes with "right single quotes".

So "Bob's" ends up as "Bob′s" - which is totally acceptable for my purposes.

I wrote it as an extension method:

public static string ReplaceSingleQuote(this string s) {
  if (!string.IsNullOrEmpty(s))
    return s.Replace('\u0027', '\u2032');
  else
    return s;
}

so for example you'd change the line:

worksheet.Cell(2 + y, 1 + x).Value = Convert.ToString(dt.Rows[y][x]);

to :

worksheet.Cell(2 + y, 1 + x).Value = Convert.ToString(dt.Rows[y][x]).ReplaceSingleQuote();
msulis
  • 555
  • 5
  • 17