EPPlus - LoadFromCollection - text converted to number

Question

I am writing a C # program that should export List<MyObject>to Excel, and I use EPPlus for this.

My problem is that my object has a property:

string Prop1 { get; set; }

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

The problem is that the EPPlus method LoadFromCollectionexports this to Excel, but Excel converts it to a number using scientific notation (output value = 1.23E+06or 1230000).

I tried to set the entire column to .Style.Numberformat.Format = "@"(and any other style I could think of), and even tried to set the style before and after the method call LoadFromCollection.

I also tried preceding the line with the character ', but actually saves that character in every cell inside this column, and then makes the values ​​incorrect for analysis.

I play with converting my list to a DataTable to use the method LoadFromDataTable, but even this does not seem to work.

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

Answer 1

, , Excel . , ( ) , - .ToString(). Excel , XML, EPPlus .

- :

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();
    }
}