none
Not able to print the date in correct format from an excel sheet RRS feed

  • Question

  • hi everyone, 

    i'm trying to get the dates in correct format from my third column and 6th column of an excel sheet  but i'm unable to do it.

    date should be printed as 31/12/2016 but its printing like 4072.

    here is the code i'm using:-

                                                                                                            

    using System;
    using System.Linq;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;    

    namespace Sandbox
    {
        public class GetExcelFile
        {
            public static void Main()
            {

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbooks xlWorkbookS = xlApp.Workbooks;
                Excel.Workbook xlWorkbook = xlWorkbookS.Open(@"C:\Users\Juhi_Sharma\Desktop\CountryHolidaylist.xlsx");

                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;



                int count = 0;

                for (int i = 1; i <= rowCount; i++)
                {
                    var row = "";
                    for (int j = 1; j <= colCount; j++)
                    {



                        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                        {

                            row = string.Format("{0}  {1}  ", row, xlRange.Cells[i, j].Value2.ToString());


                            count = i;
                        }

                    }
                    Console.WriteLine(row);
                }
                Console.WriteLine("Count: " + count);
                Console.ReadLine();
                //cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();

                Marshal.ReleaseComObject(xlRange);
                Marshal.ReleaseComObject(xlWorksheet); 
                xlWorkbook.Close();
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlWorkbookS);
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
        }
    }

    if anyone can help with the logic of printing in for loop.

    thank you

    Tuesday, April 17, 2018 5:55 PM

All replies

  • Hello juhi sharma,

    If the datetime is correct format in your workbook, you could get the cell's text directly instead of value.

    Such as 

    row = string.Format("{0}  {1}  ", row, xlRange.Cells[i, j].Text.ToString());

    If the datetime is not correct format, you need try to convert the value to a date time object and them format it as the format you want.

    Such as 

    row = string.Format("{0}  {1}  ", row, String.Format("{0:MM/dd/yyyy}", DateTime.FromOADate(xlRange.Cells[i, j].Value2)));

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 1:48 AM
  • Hi

    while writing this code it is throwing this exception

    'The best overloaded method match for 'System.DateTime.FromOADate(double)' has some invalid arguments'

    thanks

    Wednesday, April 18, 2018 7:11 AM
  • Hello juhu sharama,

    What's result of using Value instead of Value2 like this?

    row = string.Format("{0}  {1}  ", row, String.Format("{0:MM/dd/yyyy}",xlRange.Cells[i, j].Value));

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 7:34 AM
  • hi

    can u help me with how to connect this excel file to Redis.

    Friday, April 20, 2018 6:08 AM
  • Hello juhi sharma,

    Has your original issue been resolved? If it has, I would suggest you mark helpful reply to close the thread. If not, please feel free to follow up to let us know the current state.

    This forum is issues related to VSTO developements and since Redis is a third-part project, I would suggest you contact Redis support team for help.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 6:17 AM