使用EPPlus我怎么能生成一个电子表格中的数字是数字不是文本 [英] Using EPPlus how can I generate a spreadsheet where numbers are numbers not text

问题描述

我创建从列表℃的电子表格，对象[]> 使用 LoadFromArrays

数组的第一项是一个标题，其它项是可能的数字，文字或日期（但同样为列表中的每个阵列）。

The first entry of the array is a title, the other entries are possibly numbers, text or dates (but the same for each array in the list).

生成Excel表有绿色三角形警告说，号码格式为文本。

The generated Excel sheet has the green triangle warning that numbers are formatted as text.

我遍历所有单元格并设置其格式为数字像这样 ws.Cells [I，J] .Style.Numberformat.Format =0;

I loop through all the cells and set their format to Number like so ws.Cells[i, j].Style.Numberformat.Format = "0";

不过问题仍然存在，我仍然看到绿色的警告，即使当我看在格式单元格的数字格式设置为数字... 对话。

However the problem remains and I still see the green warning, even though the number format is set to number when I look in the Format Cell... dialogue.

什么是我选择这里？它可以让我知道更多一点的是在每列是什么类型的，但我怎么然后设置列标题？

What are my options here? It is possible for me to know a bit more about what type is in each column, but how do I then set a column title?

难道还有比EPPlus一个更好的解决方案？或电子表格，我可以下载它之前做的一些后处理？

Is there a better solution than EPPlus? or some more post processing of the spreadsheet I can do before downloading it?

推荐答案

由于您使用的对象数组，他们可以包含数字和字符串看起来像数字，你将不得不通过每个对象，并确定其类型：

Since you are using objects arrays they can contain numbers and strings that look like numbers you will have to go through each object and determine its type:

[TestMethod]
public void Object_Type_Write_Test()
{
    //http://stackoverflow.com/questions/31537981/using-epplus-how-can-i-generate-a-spreadsheet-where-numbers-are-numbers-not-text
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    //Some data
    var list = new List<Object[]>
    {
        new object[]
        {
            "111.11",
            111.11,
            DateTime.Now
        }
    };

    using (var package = new ExcelPackage(existingFile))
    {
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells[1, 1, 2, 2].Style.Numberformat.Format = "0";
        ws.Cells[1, 3, 2, 3].Style.Numberformat.Format = "[$-F400]h:mm:ss\\ AM/PM";

        //This will cause numbers in string to be stored as string in excel regardless of cell format
        ws.Cells["A1"].LoadFromArrays(list);

        //Have to go through the objects to deal with numbers as strings
        for (var i = 0; i < list.Count; i++)
        {
            for (var j = 0; j < list[i].Count(); j++)
            {

                if (list[i][j] is string)
                    ws.Cells[i + 2, j + 1].Value = Double.Parse((string) list[i][j]);
                else if (list[i][j] is double)
                    ws.Cells[i + 2, j + 1].Value = (double)list[i][j];
                else
                    ws.Cells[i + 2, j + 1].Value = list[i][j];

            }
        }

        package.Save();
    }
}

有了上面，你看到下面的图片作为输出注意用绿色箭头的左上角单元格，因为它是被写了 LoadFromArray 一个字符串，它看起来像一个数字：

With the above, you see the image below as the output Note the upper left corner cell with the green arrow because it was a string that was written by LoadFromArray which looks like a number:

这篇关于使用EPPlus我怎么能生成一个电子表格中的数字是数字不是文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！