EPPlus:日期列返回int而不是实际显示的文本值 [英] EPPlus : date column returning int rather than the actual displayed text value

问题描述

我正在使用EPPlus读取Excel数据.我需要在网格中按原样向用户显示excel单元格.

I am using EPPlus to read excel data. I need to display the excel cells as-is in the grid to the users.

在读取日期列时，EPPlus给出了日期的 OADate int值.

while reading date column EPPlus is giving the OADate int value for the date.

有没有一种方法可以将值读取为字符串，当用户打开excel时通常会看到该值.即应用excel格式后的值.

Is there a way I can read the value as string, which users normally see when they open the excel. i.e. the value after applying the excel formats.

CELL类上似乎没有任何提供valueAsString或valueAsDisplayed等的函​​数.

There does not seem to be any function on the CELL class which provides valueAsString or valueAsDisplayed, etc...

推荐答案

如果日期以double格式(如果没有时间成分，则为整数)存储在excel中，这将是正确"的方式，您已经在代码中重新创建了日期，并重新应用了在Excel中应用的日期格式.唯一的麻烦是excel的格式与.net略有不同，尤其是在区分大小写方面，因此您应该在此处进行检查以确保MMM不是mmm(这会给您分钟的.net时间).所以这样的事情有效:

If the dates are stored in excel as doubles (or a whole number if there is no time component) with formatting applied, which would be the "correct" way, you have recreate the date in code and reapply the date format applied in Excel. The only wrinkle is excel's formatting is slightly different then .net especially when it comes to case sensitivity so you should put a check in there to make sure MMM is not mmm (which would give you minutes in .net). So something like this works:

[TestMethod]
public void OADate_Test()
{
    //http://stackoverflow.com/questions/28046069/epplus-date-column-returning-int-rather-than-the-actual-displayed-text-value
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();


    using (var package = new ExcelPackage(existingFile))
    {
        //.NET is case sensive so MMM must be capital but Excel might not be
        const string FORMATDATE = "dd-MMM-yyyy";
        var mydate = new DateTime(2015, 1, 1);
        var datedouble = mydate.ToOADate();

        var worksheet = package.Workbook.Worksheets.Add("Newsheet");

        worksheet.Cells["A1"].Value = "As Date";

        worksheet.Cells["A2"].Value = datedouble;
        worksheet.Cells["A2"].Style.Numberformat.Format = FORMATDATE;

        package.Save();
    }

    using (var package = new ExcelPackage(existingFile))
    {
        var worksheet = package.Workbook.Worksheets["Newsheet"];

        worksheet.Cells["B1"].Value = "As String";

        var datedouble = (double) worksheet.Cells["A2"].Value;
        worksheet.Cells["B2"].Value = DateTime.FromOADate(datedouble).ToString(worksheet.Cells["A2"].Style.Numberformat.Format);

        package.Save();

    }
}

这篇关于EPPlus:日期列返回int而不是实际显示的文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！