如何设置单元格数据类型 [英] How To Set Cell Data Type

问题描述

我正在尝试设置单元格的数据类型，但是用EPPlus似乎是不可能的.

I am trying to set data type of cell, but it seems that's impossible to do with EPPlus.

如果在单元格中放置数字作为值，则会在导出的Excel文件中获得常规"数据类型.

If I place a number as value in cell, I get General data type in exported Excel file.

如果设置了单元格的NumberFormat，例如:

If set NumberFormat of Cells, for example:

workSheet.Cells[range].Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss";

然后，我仍然不会获得日期或时间.导出的Excel文件会将单元格显示为自定义"数据类型.

Then I still will not get Date or Time. Exported Excel file will show cells as Custom data type.

我只能获得常规或自定义数据类型.我想将Cells的数据类型设置为Time，但是不能.问题是，如果未设置数据类型，则由于某种原因，其他工作表中的数据透视表会将数字排序为字符串:(

I can get General or Custom data types only. I want to set Cells' data type to Time, but I can't. The problem is, if data type is not set, then for some reason Pivot table in other sheet is sorting numbers as strings :(

那么，有什么方法可以设置单元格的数据类型吗?

So, is there any way to set data type of cell please?

推荐答案

数字格式与excel有点怪异.基本上，它是预定义字符串的列表，因此它逐个单元进行匹配.要查看该列表在EPPlus中的外观，请查看源代码中的ExcelNumberFormat.cs类.

The number formatting gets a little weird with excel. Basically, it is a list of predefined strings so it does a match cell by cell. To see what that list looks like in EPPlus check out the ExcelNumberFormat.cs class in the source code.

但是，如果您只需要让Excel在HOME功能区的数字"->数字格式"下拉列表中以某种类型查看"该单元格，那么应该可以:

But if you just need to have Excel "see" the cell as a certain type in the Number -> Number Format dropdown in the HOME ribbon, this should get you it:

[TestMethod]
public void Date_Format_Test()
{
    //http://stackoverflow.com/questions/29473920/how-to-set-cell-data-type

    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var pck = new ExcelPackage(existingFile))
    {
        var ws = pck.Workbook.Worksheets.Add("Content");
        var date = DateTime.Now;

        //Raw date value as number
        ws.Cells["A1"].Value = date;

        //As "Short Date"
        ws.Cells["A2"].Value = date;
        ws.Cells["A2"].Style.Numberformat.Format = "mm-dd-yy";

        //As "Time"
        ws.Cells["A3"].Value = date;
        ws.Cells["A3"].Style.Numberformat.Format = "[$-F400]h:mm:ss\\ AM/PM";

        pck.Save();
    }
}

这篇关于如何设置单元格数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！