Date format set in Excel is ignored when using Excel Interop

0
votes

I'm using the Microsoft Office C# library (Microsoft.Office.Interop.Excel) to write some values to an existing excel file.

In Excel I've already formatted some cells in the file to be interpreted as a date, as shown in the screenshot: enter image description here

As you can see, the cell is set to a dd/MM/yyyy format.

However, when I then wrote the value using the interop library, like this:

cell.Value2 = "20/06/2019"

I noticed that it wasn't working: when I opened the final file in Excel the cell was being interpreted as plain text, instead of a date.

So I did some debugging, and it turns out that the format I set for the cell is completely ignored, and that the interop library is using the American format, as you can see by inspecting the NumberFormat property of the cell with the visual studio debugger:
enter image description here

What am I doing wrong here? Why is the American date format being forced?
Some additional info:

  • My OS locale (italian) uses the dd/MM/yyyy format by default
  • I did not change the locale of my c# app in any way
  • My copy of Excel is set to italian, which should use the dd/MM/yyyy format by default as well. I checked under Options -> Language, and I can confirm this.

EDIT: and interesting thing I found: if I double-click on a cell and then click away from it, it "magically" fixes the formatting.... why? And can I simulate a double click on the cell using Excel Interop?

1
c#exceloffice-interopexcel-interop

1 Answers

2
votes

Dates in Excel are stored as double representing OLE Automation date. MSDN

In your case, you are setting a string value. A proper way to set a date in Excel is following:

cell.NumberFormat = "dd/MM/yyyy";
cell.Value = new DateTime(2019, 6, 20).ToOADate();