CODEWITHSUNDEEP
c#excelc#-4.0datagridview
Anurag Rag
Anurag Rag

Reputation: 35

Why Date format gets change while exporting data to Excel?

I am working on window Form Application. In my form i have GridView which have a column of Type Date. Date column has Date format dd-MM-YYYY.I am exporting this data into Excel. But the problem is date format gets change in Excel.

Here is my code while exporting the data into the Excel

    private void btnSaveDetails_Click(object sender, EventArgs e)
    {

        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
        app.Visible = true;
        worksheet = workbook.ActiveSheet;
        for (int i = 1; i < dataGridViewDetails.Columns.Count + 1; i++)
        {
            worksheet.Cells[1, i] = dataGridViewDetails.Columns[i - 1].HeaderText;
        } 
        for (int i = 0; i < dataGridViewDetails.Rows.Count; i++)
        {
            for (int j = 0; j < dataGridViewDetails.Columns.Count; j++)
            {
                if (j == 3)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridViewDetails.Rows[i].Cells[j].Value.ToString().Split (' ')[0];

                }
                else
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridViewDetails.Rows[i].Cells[j].Value.ToString();
                }
            }
        }

    }

Here is the Image of GridView

Here is Image of Excel Data after Exporting from GridView

As you see in code i am exporting each data in String format So Excel Cell Type Should not be problem.But still data format gets convert. What i am doing Wrong ?

Upvotes: 1

Views: 7444

Answers (3)

falcala
falcala

Reputation: 1

This is an old question; however, I’m leaving this answer here, hoping it helps someone.

I had exactly the same problem you have. Here in Mexico, we use the “day/month/year” date format. It is a very common issue to have misinterpreted dates in Excel and other information systems, where “3/6/2020” (June 3rd in Mexican notation) is translated to “6/3/2020” (March 6th) when pasted into Excel or exported to a legacy system.

I have a WPF C# application that exported info to Excel. That same issue happened with ambiguous dates (16/10/2020, for example, is always correctly interpreted as October 16th, because it is not ambiguous). I tried the same solution as you, creating a pre-formatted string and then exporting it. The result, however, was exactly the same. I think Excel internally applies the same process as when you type something into a cell, and Excel tries to figure out which type of info is it (a date, a number, currency, etc.) So, by exporting a string, Excel still tries to understand the info and, figuring out it is a date, translates into a date cell.

I also noticed that Interop IGNORES Windows and Excel regional settings. If, for example, I start Excel and directly type “3/6/2020”, that info is correctly interpreted as a date, and as the correct date (June 3rd, 2020). By using Interop, however, writing “3/6/2020” to Excel appears as “6/3/2020”; changing cell format to “Long date” shows “March 6th, 2020” (in Spanish, of course), so it is not only a presentation issue: the dates are actually changed. It is evident that Excel applies regional settings when it’s too late: after interpreting the exported date.

I think the “correct” solution is to configure the workbook’s regional settings via Interop commands; however, I found two faster and easier solutions. Both of them work.

First option: force Excel to manage the date as a text string

This is the quick and dirty solution. A method so simple that it seems insultingly silly: just add an apostrophe (‘) at the beginning of the string. Yes, the good ol’ method we were using in Excel for ages.

DateTime my_date = DateTime.Today;
string my_string_date = my_date.ToString("dd/mm/yyyy");
string my_fixed_date = "'" + my_string_date;
excelsheet.Cells[1, 1] = my_fixed_date;

Done! Excel will show the date exactly as you intended. However, this method has a drawback: the cell contains a text string, not a date. A better solution should keep the cell as a date; the user could want to use the cell’s data in formulas as a date value.

Second option (best solution, in my opinion): format the cell with the correct date format

In this solution, the actual date variable is exported. But before doing that, the cell is formatted with the date format that matches the way we want to display the date. In this case, we do not need a preformatted string.

DateTime my_date = DateTime.Today;
excelsheet.Cells[1, 1].NumberFormat = “dd/mm/aaaa”;
excelsheet.Cells[1, 1] = my_date;

That’s it. As tested using WPF C# in Visual Studio 2019, this works.

Notice that I’m using the Spanish date format (“dd/mm/aaaa”), so you might need to change it to a string your Excel’s locale can understand. This is the solution’s drawback: it’s locale-dependent. Excel has a tendency to translate formulas and settings to the regional language, something very handy for casual users but a pain for programmers.

Also notice that I'm changing the cell format BEFORE inserting the value. I tried to apply the cell format after inserting the date value and that didn't work.

This second solution could be improved by finding a way to define cell format in a locale-independent way. If I found how to do it, I’ll change the answer.

Upvotes: 0

Nagarjuna
Nagarjuna

Reputation: 11

I know i am an year late in answering this. I hope it might help anyone else who might be looking for similar solutions.

The default format of date in excel is determined based on the language selected in the system. if you need by default DD-MM-YYYY, then changing the language to English(India) would fix it.

Please note: The date format will change according to the parent system where the file is being opened. So in you PC/ laptop it might show DD-MM-YYYY, but when i open the same file in my system then it will be MM-DD-YYYY as the language set in my workstation is English(US).

Upvotes: 1

Christopher
Christopher

Reputation: 9804

There are two very different things here:

  1. How the DateTime is stored
  2. How the DateTime is displayed

.NET side

.NET stores DateTime as the number of Ticks since start of the Unix Epoch. Every other property and the String Representation are a interpretation of this value.

.NET Displays any numeric value - including DateTime - by extracting the users Culture settings from Windows. This is a very usefull feature, as that is one big part we do not generally have to take care off.

Excel Side

The old Excel Format uses Decimal or float value. The part before the Decimal Seperator is the deys since 0-jan-1900. It also has a well known mistake, treating 1900 as a Leap Year. The parts after the seperator denote the time of the day. But it might still have seperate types for date and time (these fell out of favor, for being almsot useless in pracitce).

How it displays those values is entirely up to the Excel Version you are viewing it in and the settings it is set to.

Upvotes: 3

Related Questions