Excel Interop和CSV文件的日期转换问题 [英] Date conversion issue with Excel Interop and CSV file

问题描述

我有一个CSV文件，其日期格式为m / dd / yyyy，如下所示

I have a CSV file which contains dates formatted as m/dd/yyyy as follows

1/06 / 2013,15,1,1 / 06/2013 0：15,1,6.44

1/06/2013,15,1,1/06/2013 0:15,1,6.44

当我在Excel中打开文件时，日期被正确转换 - 1/06/2013有数值41,426.00和MONTH函数返回6。

When I open the file in Excel, the dates are correctly converted - 1/06/2013 has numeric value 41,426.00 and the MONTH() function returns 6.

当我使用Microsoft.Office.Interop.Excel使用Open（）或OpenText（）方法打开同一个文件时，日期被转换为41,280.00（6/01/2013）。

When I use Microsoft.Office.Interop.Excel to open the same file using either the Open() or OpenText() method the date gets converted to 41,280.00 (6/01/2013).

您可以使用Microsoft.Office.Interop.Excel打开一个CSV文件，以便打开相同的，就像用户打开

Can you open a CSV file using Microsoft.Office.Interop.Excel so that it opens the same as if the user opened the file from the windows shell?

推荐答案

找到答案自己埋在这个问题的底部 - 编辑文件时打开Excel VBA日期格式/值更改

Found the answer myself buried in the bottom of this question - Excel VBA date formats/values change when file is opened programatically

workbook = workbooks.Open(filename, Local: true)

Open（）有一个Local属性，假定不明确日期的mdy顺序，当它为true时，它使用澳大利亚的本地机器设置dmy

Open() has a Local property, when false it assumes the mdy order for ambiguous dates, when true it uses the local machine setting which in Australia is dmy

这篇关于Excel Interop和CSV文件的日期转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！