How to make correct date format when writing data to Excel

user_44952 0
Viewed 124685

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?

office-interop excel .net c#
13 Answers

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_34675320

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

Hope this help

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

isDate(worksheet.Cells[irow, icol])
user_106943740

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_34675320

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

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.

enter image description here

user_117711980

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
user_50637390
Related