How to make correct date format when writing data to Excel

Question

Iam exporting a DataTable to an Excel-file using office interop  The problem is  that Excel does not recognize dates as such  but instead it displays numbers  In another case I pass a string which it then recognizes as a date  In both cases the data is messed up   I tried NumberFormat   which is supposed to store the cell in text format  but it didn t work either   Application app   new Application    app Visible   false  app ScreenUpdating   false  app DisplayAlerts   false  app EnableAnimations   false  app EnableAutoComplete   false  app EnableSound   false  app EnableTipWizard   false  app ErrorCheckingOptions BackgroundChecking   false    Workbook wb   app Workbooks Add XlWBATemplate xlWBATWorksheet   Worksheet ws    Worksheet wb Worksheets 1    for  int j   0  j  lt  dt Rows Count  j          for  int i   0  i  lt  dt Columns Count  i                  Range rng   ws Cells j 2  i 1 as Range          rng Value2   dt Rows j  i  ToString            rng NumberFormat                               wb SaveAs filename  Missing Value  Missing Value  Missing Value  Missing Value         Missing Value  XlSaveAsAccessMode xlExclusive  Missing Value  Missing Value  Missing Value  Missing Value  Missing Value    wb Close false  Missing Value  Missing Value                app Workbooks Close    app Application Quit    app Quit        System Runtime InteropServices Marshal ReleaseComObject ws   System Runtime InteropServices Marshal ReleaseComObject wb   System Runtime InteropServices Marshal ReleaseComObject app   ws   null  wb   null  app   null  GC Collect      Why doesn t my NumberFormat   work  Shouldn t Textformat display everything the same as I put it in

User · Answer

I know this question is old but populating Excell Cells with Dates via VSTO has a couple of gotchas.

Formatting the entire column did NOT work for me.
Not even this approach from Microsoft worked: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.numberformat.aspx

I found Formula's don't work on dates with yyyy-mmm-dd format - even though the cells were DATE FORMAT! You have to translate Dates to a dd/mm/yyyy format for use in formula's.

For example the dates I am getting come back from SQL Analysis Server and I had to flip them and then format them:

using (var dateRn = xlApp.Range["A1"].WithComCleanup())
{
    dateRn.Resource.Value2 = Convert.ToDateTime(dateRn.Resource.Value2).ToString("dd-MMM-yyyy");
}


using (var rn = xlApp.Range["A1:A10"].WithComCleanup())
{
    rn.Resource.Select();
    rn.Resource.NumberFormat =  "d-mmm-yyyy;@";
}

Otherwise formula's using Dates doesn't work - the formula in cell C4 is the same as C3:

enter image description here

User · Answer

Old question but still relevant  I ve generated a dictionary that gets the appropriate datetime format for each region  here is the helper class I generated   https   github com anakic ExcelDateTimeFormatHelper blob master FormatHelper cs  FWIW this is how I went about it    opened excel  manually entered a datetime into the first cell of a workbook opened the regions dialog in control panel used Spy to find out the HWND s of the regions combobox and the apply button so I can use SetForegroundWindow and SendKey to change the region  couldn t find how to change region through the Windows API  iterated through all regions and for each region asked Excel for the NumberFormat of the cell that contained the date  saved this data to into a file

User · Answer

This worked for me   sheet Cells currentRow    currentColumn          theDate ToString  MM dd yy      Note the tick mark added before the date

User · Answer

Expanding slightly on  Assaf answer  to apply formatting correctly I also had to convert the DateTime via the  ToOADate   function before the formatting took effect  You can do this on a cell by cell basis   xlWorkSheet Cells Row  Col  NumberFormat     lt Required Format gt       e g  dd-MMM-yyyy xlWorkSheet Cells Row  Col    DateTimeObject ToOADate      Or you can apply the formatting to the entire column   xlWorkSheet Cells Row  Col  EntireColumn NumberFormat     lt Required Format gt       e g  dd-MMM-yyyy xlWorkSheet Cells Row  Col    DateTimeObject ToOADate

User · Answer

To format by code Date in Excel cells try this   Excel Range rg    Excel Range xlWorkSheet Cells numberRow  numberColumn    rg NumberFormat   CultureInfo CurrentCulture DateTimeFormat ShortDatePattern    After this you can set the DateTime value to specific cell  xlWorkSheet Cells numberRow  numberColumn    myDate    If you want to set entire column try this      Excel Range rg    Excel Range xlWorkSheet Cells numberRow  numberColumn    rg EntireColumn NumberFormat        CultureInfo CurrentCulture DateTimeFormat ShortDatePattern

User · Answer

This is an old thread  By this time  people either use OpenXML  OpenXML is much better  Well  many people like me are stuck because the initial developers use the interops  I had same struggle for couple hours  I have tried everything here and other usage  It still gave me numerical representation  Then I found out that I set the style  The style property ruined everything  I just added the NumberFormatproperty Here is what I did  It works                   set the font style and size                 Excel Style styleDate   MyBook Styles Add  quot StyleDate quot                    styleDate NumberFormat    quot mm dd yyyy quot    remember to include this when setting style property                 styleDate Font Size   10                  styleDate Font Name    quot Arial quot                     the function will return datetime value from database or whatever                 DateTime DtVal  GetdatetimeVal                      xlWorkSheet Cells Row  Col  Style   styleDate                 xlWorkSheet Cells Row  Col    DtVal

User · Answer

This worked for me   hoja trabajo Cells i   2  j   1    fecha ToString  dd-MMM-yyyy   Replace

User · Answer

Did you try formatting the entire column as a date column  Something like this   Range rg    Excel Range worksheetobject Cells 1 1   rg EntireColumn NumberFormat    MM DD YYYY     The other thing you could try would be putting a single tick before the string expression before loading the text into the Excel cell  not sure if that matters or not  but it works when typing text directly into a cell

User · Answer

Try this solution  in my softwarew work very well   if  obj    null        if  obj is DateTime                if  DateTime MinValue      DateTime obj                          xlWorkSheet Cells x y    String Empty                     else                        dynamic opp     DateTime obj               xlWorkSheet Cells x y     DateTime opp

User · Answer

Try using  DateTime ToOADate     And putting that as a double in the cell  There could be issues with Excel on Mac Systems  it uses a different datetime-- double conversion   but it should work well for most cases    Hope this helps

User · Answer

Hope this help  private bool isDate Range cell                if  cell NumberFormat ToString   Contains   yy                          return true                    return false         isDate worksheet Cells irow  icol

[c#] How to make correct date format when writing data to Excel

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to office-interop