You must Sign In to post a response.
  • Category: ASP.NET

    How to show date field value of data table in exported excel in dd MMM yyyy format

    I am able to show date field value in gridview in dd MMM yyyy (29 Oct 2015). After export to Excel, the date field value format is changing to dd-MMM-yy ( 29-Oct-15).

    Is it possible to preserve the same format of gridview in exported excel as 29 Oct 2015?

    Could you please any one help to answer my problem.

  • #764380
    Hi krishna,
    You may apply the formatting to specified cell or row according to your need after exporting excel.

    Exported excel adapts regional date and time settings specified by your OS. So you need to specify date time format explicitly in program, follow the code snippet:

    DataTable dtDemo = new DataTable();
    dtDemo.Columns.Add("Date", typeof(DateTime));
    dtDemo.Rows.Add(new object[] { "2/2/2016 11:00:00 PM" });
    Workbook finalBook = new Workbook();
    Worksheet finalSheet = finalBook.Worksheets[0];
    finalSheet.Cells.ImportDataTable(dtDemo, true, 0, 0, dtDemo.Rows.Count, dtDemo.Columns.Count, true, "mm/dd/yyyy hh:mm:ss");
    finalBook.Save("D:\\output.xlsx", SaveFormat.Xlsx);

    OR use this:

    finalSheet.Range["A1", "A65535"].NumberFormat = "mm/dd/yyyy hh:mm:ss";

    OR use this:

    Range rg = (Excel.Range)finalSheet.Cells[1,1];
    rg.EntireColumn.NumberFormat = "MM/DD/YYYY";

    Hope it helps.
    Shashikant Gurav

  • Sign In to post your comments