从Excel读取日期时输入字符串的格式不正确 [英] Input string was not in a correct format while reading date from excel

问题描述

我正在尝试上传Excel工作表。现在，日期列出现错误。我可以根据此处的一些建议将其修复。这是excel 20/4/2020上的日期格式。

I am trying to upload an excel sheet. Now I am getting an error for the date column. I was able to fix it up to this point base on some suggestions here. This is the date format on the excel 20/4/2020

但是我现在无法弄清楚。我不断。我能够从excel工作表中获取值，并存储在字符串日期

But I couldn't figure it out from this point. I kept getting. I was able to get the Value from the excel sheet and stored in string date

输入字符串不在正确的格式。 System.Double.ThrowOverflowOrFormatException（ParsingStatus status，
TypeCode type）在System.Double.Parse（String s）处的

Input string was not in a correct format. at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type) at System.Double.Parse(String s)

下面是我的代码

//be on the first column [r,c]
int row = 2;
for (int i = 2; i <= noOfRow; i++)   //start from the second row
{
    if (!string.IsNullOrEmpty(workSheet.Cells[i, 3].Text))
    {
        string date = workSheet.Cells[i, 3].Value.ToString();

        try
        {
            double d = double.Parse(date);//Error is coming from here
            DateTime conv = DateTime.FromOADate(d);

        }
        catch (Exception ex)
        {}
    }
}

如果有人可以帮助我
，我将不胜感激。

I will appreciate if someone can help me out Thanks

推荐答案

出现问题是因为变量 date = 11/5/2020 中的值不是 double 值。这是 DateTime 的值。为了解决您的问题，您应该使用下面的代码将 date 变量中的值转换为 DateTime 值：

The problem occurs because value in the variable date = "11/5/2020" is not a double value. It is a DateTime value. To fix you problem you should use the next code to convert value in the date variable into DateTime value:

DateTime d = DateTime.ParseExact(date, "d/M/yyyy", CultureInfo.InvariantCulture);

方法 DateTime.ParseExact 允许您设置格式

Method DateTime.ParseExact allows you to set the format of the parsed date.

上面的代码在您与我共享的那天对我有用。但是现在我是
，收到此错误 {无法识别字符串'6/3/2020 12:00:00 AM'
为有效的DateTime。} 我试图在
stackoverflow上实现几乎所有内容。

The above code worked for me the day you shared with me. But now I am getting this error {"String '6/3/2020 12:00:00 AM' was not recognized as a valid DateTime."} I have tried implementing almost everything on stackoverflow.

如果Excel文件中的日期字符串可以在不同的格式，则可以使用方法 DateTime.ParseExact ，它支持指定几种解析格式。例如：

If date string in your excel file can be in different formats, then you can use overload of the method DateTime.ParseExact that supports specifying several parse formats. For example:

string[] formats = {"d/M/yyyy", "d/M/yyyy hh:mm:ss tt"};
DateTime d = DateTime.ParseExact(date, formats, CultureInfo.InvariantCulture, DateTimeStyles.None);

这里是完整示例，它演示了方法 DateTime.ParseExact 的重载。

Here is complete sample that demostrates this overload of the method DateTime.ParseExact.

这篇关于从Excel读取日期时输入字符串的格式不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！