Date format changing while converting from excel to datatable in asp.net c#

Question

I have tried to import the excel the date column in excel is changing to number format. For example I have 3 column in the excel Name,age and Dob

When I try to convert this excel into the datatable the date in the Dob column is changing to a number.

using (ExcelPackage excelPackage = new ExcelPackage(fi))      
{
    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[sheetName];
    ExcelCellAddress startCell = worksheet.Dimension.Start;
    ExcelCellAddress endCell = worksheet.Dimension.End;
    if (endCell.Row > 0)
    {
        for (int col = startCell.Column; col <= endCell.Column; col++)
        {
            dt.Columns.Add(Convert.ToString(worksheet.Cells[startCell.Row, col].Value));
        }

        for (int row = startCell.Row + 1; row <= endCell.Row; row++)
        {
            DataRow dr = dt.NewRow();
            int x = 0;
            for (int col = startCell.Column; col <= endCell.Column; col++)enter code here
            {
                dr[x++] = worksheet.Cells[row, col].Value;
            }

            dt.Rows.Add(dr);
        }
    }
}

provide the format in excel and the format you got in datatable. — Dr. Stitch, Jun 17 '16 at 05:14
For example : 7/1/2016 The above date while converting to datatable i am getting number 42552 — keerthi, Jun 17 '16 at 05:21
Possible duplicate of [Reading Datetime value From Excel sheet](http://stackoverflow.com/questions/4538321/reading-datetime-value-from-excel-sheet) — Dr. Stitch, Jun 17 '16 at 05:29

Nimmi · Answer 1 · 2016-06-17T05:49:21.227

1

Try This,

double val= double.Parse("42552");
DateTime requiredDate= DateTime.FromOADate(val);

edited Jun 17 '16 at 05:49

answered Jun 17 '16 at 05:42

Nimmi

680
8
18

This answer is helpful for me.Thanks. – keerthi Jun 17 '16 at 06:43

score 0 · Answer 2 · answered Jun 17 '16 at 05:53

Please give a try to this, by changing the format of the Cells.

Range r = (Excel.Range)worksheetobject.Cells[row,column];
r.EntireColumn.NumberFormat = "MM/DD/YYYY";

AND can do this also,

double value = double.Parse(convertedNumber);
DateTime yourData = DateTime.FromOADate(value);

score 0 · Answer 3 · answered Jun 17 '16 at 06:24

0

Set the column type to "Text" and set the format:

worksheet.Cells[row, col].NumberFormat = "@";

answered Jun 17 '16 at 06:24

Dexion

1,101
8
14

Date format changing while converting from excel to datatable in asp.net c#

3 Answers3