How to read a Date column from excel to a string in c#?

Question

I am writing a C# console application which reads data from Excel spreadsheet using the SmartXLS library. I am able to read all the other column data except the 'usage date'column. Kindly, suggest me a way to solve this problem.

Date column in Excel:

usage date

Oct-15
Oct-15
Oct-15

Function:

public void GetData()
        {

            int count = 0;

            DeskTokens = new List<Token>();

            string directory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string path = Path.Combine(directory, @"C:\projects\Product_Usage_Year.xlsx");

            SmartXLS.WorkBook WB = new WorkBook();
            WB.readXLSX(path);

            DataTable dt = WB.ExportDataTable();

            string CurrentType = string.Empty;
            string CurrentCategory = string.Empty;

            DataRow dr;
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                dr = dt.Rows[i];
                var tkn = new Token();

                tkn.Usagedate = dr[0].ToString(); //error in reading the date column
                tkn.Product_name = dr[1].ToString();
                tkn.Product_Version = dr[2].ToString();
                tkn.Userid = dr[3].ToString();
                tkn.User_name = dr[4].ToString();

                DeskTokens.Add(tkn);
                count++;
                Console.WriteLine("Read : " + count);

                Console.WriteLine("    Reading : " + tkn.Usagedate + "," + tkn.Product_name + "," + tkn.Product_Version + "," + tkn.Userid + "," + tkn.User_name);

            }
        }

Token Class:

 class Token

{
    public string Usagedate { get; set; }
    public string Product_name { get; set; }
    public string Product_Version { get; set; }
    public string Userid { get; set; }
    public string User_name { get; set; }
}

@Paulo It is reading the Usagedate as "42278". – Rev4 Aug 14 '17 at 17:41 — Rev4, Aug 14 '17 at 17:41

Paulo · Accepted Answer · 2017-08-14T18:16:34.510

1

Try to use DateTime.FromOADate method to convert between Excel and .net:

DateTime.FromOADate(Double.Parse(dr[0].ToString()));

edited Aug 14 '17 at 18:16

answered Aug 14 '17 at 17:57

Paulo

577
3
8
23

It is giving an error"cannot convert from string to double" when I write " tkn.Usagedate = DateTime.FromOADate(dr[0].ToString()); " Do I have to change the date type in my Token class? – Rev4 Aug 14 '17 at 18:11
1

Works perfect. Thanks alot @Paulo – Rev4 Aug 14 '17 at 18:21

How to read a Date column from excel to a string in c#?

usage date

1 Answers1