CODEWITHSUNDEEP
c#asp.net
keerthi
keerthi

Reputation: 59

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

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);
        }
    }
}

Upvotes: 3

Views: 3793

Answers (3)

Dexion
Dexion

Reputation: 1101

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

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

Upvotes: 0

Smit Patel
Smit Patel

Reputation: 3247

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);

Upvotes: 0

Nimmi
Nimmi

Reputation: 690

Try This,

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

Upvotes: 1

Related Questions