2

I'm developing a program in C#, is a desktop application. I have a problem, I'm using excellibrary to open an excel and copy the data to a datatable, but I have a cell with date in format mm/dd/yyyy, but when i display the datatable in datagridview this information change to a number, for example:

02/07/1984 -> 30865

this is my code, I hope somebody could help me!

private DataTable ConvertToDataTable(string FilePath)
        {
            string file = FilePath;
            // open xls file
            Workbook book = Workbook.Open(file);
            Worksheet sheet = book.Worksheets[0];

            DataTable dt = new DataTable();
            // traverse rows by Index
            for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
            {

                Row row = sheet.Cells.GetRow(rowIndex);
                object[] temparray = new object[row.LastColIndex + 1];
                for (int colIndex = row.FirstColIndex;
                   colIndex <= row.LastColIndex; colIndex++)
                {
                    Cell cell = row.GetCell(colIndex);
                    temparray[colIndex] = cell.Value;
                }
                if (rowIndex == 0)
                {
                    foreach (object obj in temparray)
                    {
                        dt.Columns.Add(obj.ToString());
                    }
                }
                else
                {
                    dt.Rows.Add(temparray);
                }

            }

            return dt;
        }
Randall Sandoval
  • 237
  • 1
  • 6
  • 21

1 Answers1

3

The number you see is OLE Automation Date, you need to convert that number to DateTime by using DateTime.FromOADate Method :

DateTime dt = DateTime.FromOADate(30865); //dt = {02/07/1984 12:00:00 AM}

To use that method in your current code, you have to go through each column's value and for date index, parse the value to DateTime and then add it to the DataTable

Habib
  • 219,104
  • 29
  • 407
  • 436