Setting cell using excel introp changes date

Question

I'm trying to enter a date value into an excel file.

For e.g.

"05/07/19" (July 5th of 2019) and the date formate of the cell in the excel is set to "dd-MM-yy" (regional setting of the computer is also the same). But when my code enters the value into excel, value is getting changed to "07-05-19" (May 7th of 2019).

I want it to stay as "05-07-19" and also excel should consider it as a date.

I have tried different ways to set the value into an excel,

        const string DATE = "05/07/19";
        Application app = new Application();
        Workbook workBook = app.Application.Workbooks.Open(FILE_PATH);

        Worksheet sheet = workBook.Sheets[1];
        Range range = sheet.get_Range("A1");
        range.set_Value(XlRangeValueDataType.xlRangeValueDefault, DATE);

        Range range2 = app.get_Range("A1", "A2");
        range2.Cells[2, 1] = DATE;

        Range range3 = app.get_Range("A3", "A3");
        range3.Value = DATE;

        Range range4 = app.get_Range("A4", "A4");
        //Of course, It is not setting the value as a Date but text.
        range4.Value = new string[] { DATE };

I want my code to behave as per the formate of the cell. If the input can be fit as a date (based on the cell formate for date), it should be considered as a date, else just a text. (The way excel behaves when the user manually input the data)

UPDATE:

So far I got to know that runtime version of excel interop (File version: 15.0.4420.1017) is v2.0 and If I run my code with target framework .Net 3.5, it is working as expected.

But the same code is having the problem if I change the target framework to .Net 4.6

Do we know, if there may any compatible interop available for .Net 4.6?

>> and the date formate of the cell in the excel is set to "dd-MM-yy" Is this happening before you write to excel or after you finish writing? — NeverHopeless, Dec 09 '19 at 08:19
@NeverHopeless I am setting the date formate of the cell even before I run the code. However, please check my update. — Amit, Dec 09 '19 at 08:28
Could you please give Assaf's solution a try: https://stackoverflow.com/questions/3310800/how-to-make-correct-date-format-when-writing-data-to-excel#answer=3310856. Other than that you are on the right track it could be an interop version compatibility issue. — NeverHopeless, Dec 09 '19 at 09:45
@NeverHopeless Yes, If somehow I have a `DateTime` object, then his solution works. Also, I can do `=Date(year,month,day)` in that case. — Amit, Dec 09 '19 at 10:22

Banukobhan Nagendram · Answer 1 · 2019-12-09T06:40:53.263

0

Please set the number format for the relevant cells. As an example, do like this:

range2.NumberFormat = "DD/MM/YYYY";

edited Dec 09 '19 at 06:40

answered Dec 09 '19 at 06:38

Banukobhan Nagendram

2,335
3
22
27

I would, but as I mentioned in the question, I want to consider whatever is currently set as `NumberFormat`. My code should behave accordingly. – Amit Dec 09 '19 at 06:40
Also, as per your suggestion, I tried `range2.NumberFormat = "DD/MM/YYYY";` `range2.Cells[2, 1] = DATE;` but now output is "07-05-2019" ! Date is still getting changed. – Amit Dec 09 '19 at 06:49

score 0 · Answer 2 · answered Jun 15 '20 at 11:25

Ideally excel reacts on values based on default settings and if we wish to retain our own formatting while passing values we have to prepare our destination first then put value.

I would say the code which should work as:

range2.Numberformat="dd-mm-yy"; 
range2.Value=DATE;

Setting cell using excel introp changes date

2 Answers2