How to make correct date format when writing data to Excel

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 < dt.Rows.Count; j++)
{
    for (int i = 0; i < 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?

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

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.

This worked for me:

sheet.Cells[currentRow, ++currentColumn] = "'" + theDate.ToString("MM/dd/yy");

Note the tick mark added before the date.

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:

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;

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:

1. opened excel, manually entered a datetime into the first cell of a workbook
2. opened the regions dialog in control panel
3. 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)
4. 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

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 = "<Required Format>"; // 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 = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();

This worked for me:

hoja_trabajo.Cells[i + 2, j + 1] = fecha.ToString("dd-MMM-yyyy").Replace(".", "");

Hope this help

private bool isDate(Range cell)
    {
        if (cell.NumberFormat.ToString().Contains("/yy"))
        {
            return true;
        }
        return false;
    }

isDate(worksheet.Cells[irow, icol])

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;

        }
    }
}

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("StyleDate");
                styleDate.NumberFormat = "mm/dd/yyyy";//remember to include this when setting style property
                styleDate.Font.Size = 10;
                styleDate.Font.Name = "Arial"

                //the function will return datetime value from database or whatever
                DateTime DtVal= GetdatetimeVal(); 

                xlWorkSheet.Cells[Row, Col].Style = styleDate
                xlWorkSheet.Cells[Row, Col] = DtVal;

It depends on the issue at the End. In my case I had both, that required both solutions. Take a look on the following image, in element [4, 3] I've a common Date, so the common C# method DateTime.TryParse(formatted_target, out temptime); works fine.

Elements [4, 5] & [4, 6], are shown on Excel side, with time only, and a custom format (hh:mm so we see only the time in the cell on Excel side, but the cell itself contains a complete date & time object), so we've to parse them considering they are expressed in OLE Automation format, so parsing the string as double before; DateTime.FromOADate(Double.TryParse(item[4, 5]) works fine.

In my case I got dates in text format (dd/MM/yyyy). When I pass it to Excel sometimes it is OK but sometimes it becomes MM/dd/yyyy (when day is less then 12). After few try-and-fail I found a way that works OK.

The format that works for me to pass a date as string to Excel is yyyy/MM/dd.

Here some usefull code samples. How to check if a string is a date and how to reformat it.

    DateTime parsed;
    String dtAsStr = "03/01/2022";
    if (string.IsNullOrWhiteSpace(dtAsStr) == false
        && dtAsStr.Split('/').GetUpperBound(0) == 2
        && DateTime.TryParse(dtAsStr, out parsed))  // looks like a date
...
    parsed.ToString("yyyy/MM/dd"); // this is the way i found for excel to correctly understand a date