c# - 在将数据导出到Excel时，为什么日期格式会更改？(Why Date format gets change while exporting data to Excel?)

Question

I am working on window Form Application.

(我正在开发窗口表单应用程序。)

In my form i have GridView which have a column of Type Date.

(在我的表单中，我有GridView，其中有一个Type日期列。)

Date column has Date format dd-MM-YYYY.I am exporting this data into Excel.

(日期列的日期格式为dd-MM-YYYY。我正在将此数据导出到Excel。)

But the problem is date format gets change in Excel.

(但是问题是日期格式在Excel中得到更改。)

Here is my code while exporting the data into the Excel

(这是我将数据导出到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();
                }
            }
        }

    }

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.

(正如您在代码中看到的那样，我正在以字符串格式导出每个数据，因此Excel单元格类型应该没有问题。但是仍然可以转换数据格式。)

What i am doing Wrong ?

(我在做什么错了？)

  ask by Anurag Rag translate from so

Answer 1

There are two very different things here:

(这里有两个非常不同的事情：)

1. How the DateTime is stored

   (如何保存日期时间)

2. How the DateTime is displayed

   (如何显示日期时间)

.NET side

(.NET端)

.NET stores DateTime as the number of Ticks since start of the Unix Epoch.

(.NET将DateTime存储为自Unix Epoch开始以来的Ticks数量。)

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.

(.NET通过从Windows中提取用户区域性设置来显示任何数字值（包括DateTime）。)

This is a very usefull feature, as that is one big part we do not generally have to take care off.

(这是一个非常有用的功能，因为这是我们通常不必担心的很大一部分。)

Excel Side

(Excel方面)

The old Excel Format uses Decimal or float value.

(旧的Excel格式使用小数或浮点值。)

The part before the Decimal Seperator is the deys since 0-jan-1900.

(自1900年1月0日以来，十进制分隔符之前的部分是dey。)

It also has a well known mistake, treating 1900 as a Leap Year.

(它还有一个众所周知的错误，将1900年视为Le年。)

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.

(它如何显示这些值完全取决于您正在其中查看的Excel版本及其设置。)