EPPlus - LoadFromCollection - 文本转换为数字

问题描述

我正在用 C# 编写一个程序，需要将 List<MyObject> 导出到 Excel 中，我正在使用 EPPlus 这样做.

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

而且，我需要导出的值之一有一个值，例如，格式为 Prop1 = "123E4".

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

挑战在于 EPPlus LoadFromCollection 方法将其导出到 Excel，但 Excel 使用科学计数法将其转换为数字(输出值 = 1.23E+06 或 <代码>1230000).

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).

我尝试将整个列设置为 .Style.Numberformat.Format = "@" (以及我能想到的任何其他样式)，我什至尝试过设置样式在调用 LoadFromCollection 方法之后.

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.

我正在尝试将我的 List 转换为 DataTable 以便使用 LoadFromDataTable 方法，但即使这样似乎也不起作用.

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

推荐答案

如果你有看起来像数字的字符串，Excel 会在单元格的角落用绿色三角形警告你.这是假设您正在使用 .ToString() 之类的东西将数字(如果它们是数字)转换为字符串.在 Excel 中无法解决此问题，但您可以使用 XML 操作为该条件打开禁用警告消息，因为 EPPlus 本身不具备此功能.

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.

这样的事情可以做到:

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:	emp
umtest.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();
    }
}

这篇关于EPPlus - LoadFromCollection - 文本转换为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！