EPPlus LoadFromDataTable() is double escaping ampersands

Question

Here's my code:

ExcelPackage pck = new ExcelPackage(stream);
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Rules");           
ws.Cells["A1"].LoadFromDataTable(_rules, true);
ws.Cells[ws.Dimension.Address].AutoFitColumns();
ws.Cells[ws.Dimension.Address].Style.WrapText = true;
pck.Save();

The _rules DataTable has rows whose text contains an ampersand: &. Let's say AT&T for example. When viewing the file in Excel, the text appears as AT&T. I drilled down on the sharedStrings.xml and found the text was double-escaped to AT&T.

How can I prevent this behavior? I've tried wrapping the text in double-quotes as well as commenting out the AutoFitColumns() and WrapText lines.

Answer 1

Are you positive it is not in the source table? Since shared strings is xml it has to escape any ampersand. So what you are seeing is exactly what you would expect if the source table has AT&T already in it.

So, here when I alternate between good and bad formatted text you see the same thing:

[TestMethod]
public void Amp_String_Test()
{
    //http://stackoverflow.com/questions/32569450/epplus-loadfromdatatable-is-double-escaping-ampersands

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow(); row[0] = i; row[1] = i * 10;
        //Alternate text
        row[2] = i%2 == 0 ? "AT&amp;T": "AT&T"; 
        datatable.Rows.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\amptest.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);
        pck.Save();
    }
}