EPPlus - LoadFromCollection - Text converted to number

Question

I am writing a program in C# that needs to export a List<MyObject> into Excel and I'm using EPPlus for doing so.

My challenge is that my object has a property:

string Prop1 { get; set; }

And, one of the values I need to export has a value that, for example, is of the form of Prop1 = "123E4".

The challenge is that the EPPlus LoadFromCollection method exports this to Excel, but Excel converts it into a number using scientific notation (Outputted value = 1.23E+06 or 1230000).

I've tried setting the entire column to .Style.Numberformat.Format = "@" (and any other style I could think of) and I've even tried setting the style before and after the LoadFromCollection method is called.

I also tried preceding the string with a ' character, but that actually keeps that character in each cell within that column which then makes the values incorrect for analysis.

I'm playing around with converting my List to a DataTable so as to use the LoadFromDataTable method, but even that seems to not be working.

Any ideas / suggestions on how I can export this as pure text

Mitzi

Answer 1

If you have string that look like numbers Excel will warn you with those green trigangles in the corner of the cells. This is assuming you are converting the numbers (if they are numbers) to string using something like .ToString(). There is not way to get around this in Excel but you could turn on the disable warning message for that condition using XML maniulation since EPPlus does not have the ability natively.

Something like this would do it:

public class TestObject
{
    public int Col1 { get; set; }
    public int Col2 { get; set; }
    public string Col3 { get; set; }
}

[TestMethod]
public void Number_String_Test()
{
    //Throw in some data
    var datalist = new List<TestObject>();

    for (var i = 0; i < 10; i++)
    {
        datalist.Add(new TestObject
        {
            Col1 = i,
            Col2 = i *10,
            Col3 = (i*10) + "E4"
        });
    }

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

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromCollection(datalist);

        //This would be the variable drawn from the desired cell range
        var range = "C1:C11";

        //Get reference to the worksheet xml for proper namspace
        var xdoc = worksheet.WorksheetXml;

        //Create the import nodes (note the plural vs singular
        var ignoredErrors = xdoc.CreateNode(XmlNodeType.Element, "ignoredErrors", xdoc.DocumentElement.NamespaceURI);
        var ignoredError = xdoc.CreateNode(XmlNodeType.Element, "ignoredError", xdoc.DocumentElement.NamespaceURI);
        ignoredErrors.AppendChild(ignoredError);

        //Attributes for the INNER node
        var sqrefAtt = xdoc.CreateAttribute("sqref");
        sqrefAtt.Value = range;

        var flagAtt = xdoc.CreateAttribute("numberStoredAsText");
        flagAtt.Value = "1";

        ignoredError.Attributes.Append(sqrefAtt);
        ignoredError.Attributes.Append(flagAtt);

        //Now put the OUTER node into the worksheet xml
        xdoc.LastChild.AppendChild(ignoredErrors);

        pck.Save();
    }
}

Disforest

Thanks so much for the suggestion, Ernie - My chellenge isn't that, though. It's that Excel actually converts a string such as "123E4" into a number 1230000. Disabling the warning doesn't stop the conversion from happening, sadly :/ – Mitzi

@JohnBustos I see what you mean now. What happens when you run the above test method (I changed it to use LoadFromCollection)? Does it do the same thing? For me, it does not convert to a number - just shows the green triangles if the xml mod is not done. – Disforest

Ernie, thanks for the update, unfortunately, it still converts the string into a number. I use a List<MyObject> whereas you're using a datatable, but even when I try your way with my data, I get the conversion happening.... This is annoyingly frustrating..... – Mitzi

Ernie, My DEEPEST apologies, in reviewing this over and over again, what I discovered is that my data is coming back from SQL Server as a scientific notation number rather than as a string (why, I don't know!!), but this wasn't an epplus issue... I am so sorry, but am grateful for your answer as it was VERY helpful and useful, just stood no chance of actually fixing my specific problem... SO sorry, but I'll definitely mark you as the answer as you did everything right!!! THANK YOU!!!! – Mitzi

@JohnBustos LOL, no big deal. We have all been there. At least you are down to the root of the problem. Good luck with it! – Disforest

Thanks again SO MUCH, Ernie!! – Mitzi