Excel to DataGridView: Time value from excel becomes decimal values when imported

Question

I have a problem. I can import already datas from my excel file to my Datagridview but as ive notice the time in my excel file becomes a decimal value when imported to my datagridview which is confusing hope u can help me with this problem.

This is my code for importing. NOTE: i trigger my import action from my main form but populate datagridview of my 2nd form.

 private void importWeatherReportToolStripMenuItem_Click(object sender, EventArgs e)
    {
        ImportWeatherData import = new ImportWeatherData();

        if (import.datareport_dgv.Rows.Count > 0)
        {
            openFileDialog1.InitialDirectory = "C:";
            openFileDialog1.Title = "Open Excel File";
            openFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Open(openFileDialog1.FileName);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
                int rcount = worksheet.UsedRange.Rows.Count;                         
                for (int i = 1; i < rcount; i++)
                {
                   DataGridViewRow todayRow2 = new DataGridViewRow();
                   todayRow2.CreateCells(import.datareport_dgv);
                   int index = 0;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 1].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 2].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 3].Value; //time error 
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 4].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 5].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 6].Value; //time error
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 7].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 8].Value; //time error
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 9].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 10].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 11].Value; //time error
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 12].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 13].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 14].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 15].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 16].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 17].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 18].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 19].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 20].Value;
                   todayRow2.Cells[index++].Value = worksheet.Cells[i + 1, 21].Value;

                   //add this row to the grid
                   import.datareport_dgv.Rows.Add(todayRow2);
                   //import.datareport_dgv.Rows.Add(worksheet.Rows[i+1].Cells[j++].Value, worksheet.Rows[i+1].Cells[j++].Value);                        
                }                                         
            }
            import.Show();
        }
    }

I made comments on some lines above which the Time become decimal values.

here is a sample of one row i am importing from excel: I separated it with "|" for you to easily know the cell values.

1/15/2015 12:42 |33.4 |12:42 PM |33.4 |33.4 |12:42 PM |60.8 |12:42 PM |60.8 |60.8 12:42 PM |100632.25 |12:42 PM |100632.25 |100632.25 |12:42 PM |0 |0 |0 |0 |0

While this value are the values showing in my datagridview: This is the imported excel row of the above line.

1/15/2015 12:42:35 PM   |33.40 °C   |0.529166666666667  |33.40 °C   |33.40 °C   |0.529166666666667  |61%    |0.529166666666667  |61%    |61%    |0.529166666666667  |100,632Pa  |0.529166666666667  |100,632Pa  |100,632Pa  |0.529166666666667  |0  |0  |0  |0  |0

Hope you can help me. IF EVER THIS IS A DUPLICATE QUESTION PLEASE DONT GIVE ME NEGATIVE SCORE THANKS

you meant to say that excel showing decimal values instead of time value after import? — Sudhakar Tillapudi
i mean when im importing data from excel to datagridview it gives me "0.529166666666667" instead of "12:42 PM" — Solem

alex.pulver alex.pulver · Accepted Answer · 2015-01-15T08:30:15

MS Excel stores the dates as float values. The integer part represents the days and the fractional part keeps the hours, minutes and seconds.

See my answer to this question: Getting time values from an Excel sheet

Your code should be similar with this one, for each of the time value.

float excelValue = worksheet.Cells[i + 1, 3].Value;

int miliseconds = (int)Math.Round(excelValue*86400000);
int hour = miliseconds/( 60/*minutes*/*60/*seconds*/*1000 );
miliseconds = miliseconds - hour*60/*minutes*/*60/*seconds*/*1000;
int minutes = miliseconds/( 60/*seconds*/*1000 );
miliseconds = miliseconds - minutes*60/*seconds*/*1000;
int seconds = miliseconds/1000;

todayRow2.Cells[index++].Value = hour + ":" + minutes + ":" + seconds;

Excel to DataGridView: Time value from excel becomes decimal values when imported

1 Answers