EPPlus - LoadFromCollection - 文本转换为数字 [英] EPPlus - LoadFromCollection - Text converted to number

问题描述

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

我的挑战是我的对象有一个属性：

  string Prop1 {get;组; } 
  

而且，我需要导出的值之一有一个值，例如， Prop1 =123E4的形式。

挑战是EPPlus LoadFromCollection 方法将此方法导出到Excel中，但Excel会使用科学符号将其转换为数字（输出值= 1.23E + 06 或 1230000 ）。

我已经尝试将整个列设置为 .Style.Numberformat.Format =@ （以及我可以想到的任何其他风格），甚至在 LoadFromCollection 方法调用之前和之后也尝试设置样式。 / p>

我还尝试使用'字符前面的字符串，但实际上该字符在该列中的每个单元格中保留我将在列表中将数据表转换为数据表，以便使用 LoadFromDataTable

code>方法，但是即使这似乎不起作用。

有关如何将其导出为纯文本的任何想法/建议

解决方案

如果您的字符串看起来像数字Excel将警告您在单元格角落的那些绿色三角形。这是假设你正在使用类似 .ToString（）的数字（如果它们是数字）转换为字符串。在Excel中没有办法解决这个问题，但是您可以使用XML进行打开，因为EPPlus本身并不具备这种能力。

像这样做：

  public class TestObject 
 {
 public int Col1 {get;组; } 
 public int Col2 {get;组; } 
 public string Col3 {get;组; 
} 
 
 [TestMethod] 
 public void Number_String_Test（）
 {
 //抛出某些数据
 var datalist = new List< ;&的TestObject GT;（）; 
 
 for（var i = 0; i< 10; i ++）
 {
 datalist.Add（new TestObject 
 {
 Col1 = 
 Col2 = i * 10，
 Col3 =（i * 10）+E4
}）; 
} 
 
 //创建一个测试文件
 var fi = new FileInfo（@c：\temp\\\
umtest.xlsx）; 
 if（fi.Exists）
 fi.Delete（）; 
 
使用（var pck = new ExcelPackage（fi））
 {
 var worksheet = pck.Workbook.Worksheets.Add（Sheet1）; 
 worksheet.Cells.LoadFromCollection（datalist）; 
 
 //这将是从所需单元格范围绘制的变量
 var range =C1：C11; 
 
 //获取对工作表的引用xml适当的namspace 
 var xdoc = worksheet.WorksheetXml; 
 
 //创建导入节点（注意复数vs单数
 var ignoredErrors = xdoc.CreateNode（XmlNodeType.Element，ignoredErrors，xdoc.DocumentElement.NamespaceURI）; 
 var ignoredError = xdoc.CreateNode（XmlNodeType.Element，ignoredError，xdoc.DocumentElement.NamespaceURI）; 
 ignoredErrors.AppendChild（ignoredError）; 
 
 // INNER节点的属性
 var sqrefAtt = xdoc.CreateAttribute（sqref）; 
 sqrefAtt.Value = range; 
 
 var flagAtt = xdoc.CreateAttribute（numberStoredAsText）; 
 flagAtt。 Value =1; 
 
 ignoredError.Attributes.Append（sqrefAtt）; 
 ignoredError.Attributes.Append（flagAtt）; 
 
 //现在放OUTER节点进入工作表xml 
 xdoc.LastChild.AppendChild（ignoredErrors）; 
 
 pck.Save（）; 
} 
} 
  

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

解决方案

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

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