vb.net 导出到 Excel 时更改日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42390125/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date Format Changing when Exporting to Excel
提问by David
I've written an export to excel function in my program, which coincides with an import program too.
我已经在我的程序中编写了一个导出到 excel 的函数，它也与一个导入程序相吻合。
The import program is running some validation checks, but some of them are failing because the Excel sheet is formatting them as MM/dd/yyyy, however most of them are being stored as dd/MM/yyyy.
导入程序正在运行一些验证检查，但其中一些失败，因为 Excel 工作表将它们格式化为 MM/dd/yyyy，但其中大部分都存储为 dd/MM/yyyy。
After looking in my exports program, I can see that in the grid, before exporting, the dates are all correctly formatted as dd/MM/yyyy, so the issue is not here.
查看我的导出程序后，我可以看到在网格中，在导出之前，日期格式都正确地为 dd/MM/yyyy，所以问题不在这里。
When opening the exported Excel file, I can see that some of the dates are stored as MM/dd/yyyy, however.
但是，当打开导出的 Excel 文件时，我可以看到某些日期存储为 MM/dd/yyyy。
The regional settings on my PC are correct, set to UK and even after checking the Excel format on the column, I can see it is set to dd/MM/yyyy.
我电脑上的区域设置是正确的，设置为英国，即使检查列上的 Excel 格式，我也可以看到它设置为 dd/MM/yyyy。
So, what is there that could be going wrong? Why are some exported differently?
那么，有什么地方可能会出错？为什么有些出口不同？
The values as seen in the grid (Correct format - See top 2 rows 'Rate One Start', 01/06/2016)
在网格中看到的值（正确格式 - 请参阅前 2 行“Rate One Start”，01/06/2016）
Code in the export routine
导出例程中的代码
Dim formatRange As Excel.Range
formatRange = xlWorksheet.Range("F2", "F99000")
formatRange.NumberFormat = "dd/MM/yyyy"
formatRange = xlWorksheet.Range("I1", "I99000")
formatRange.NumberFormat = "dd/MM/yyyy"
formatRange = xlWorksheet.Range("J1", "J99000")
formatRange.NumberFormat = "dd/MM/yyyy"
formatRange = xlWorksheet.Range("M1", "M99000")
formatRange.NumberFormat = "dd/MM/yyyy"
formatRange = xlWorksheet.Range("N1", "N99000")
formatRange.NumberFormat = "dd/MM/yyyy"
formatRange = xlWorksheet.Range("Q1", "Q99000")
formatRange.NumberFormat = "dd/MM/yyyy"
For k As Integer = 1 To dgvExport.Columns.Count
xlWorksheet.Cells(1, k) = dgvExport.Columns(k - 1).HeaderText
Next
Dim eStr As String = ""
Dim nStr As String = ""
Me.Cursor = Cursors.WaitCursor
For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
Try
eStr = Trim(dgvExport(j, i).Value)
nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
xlWorksheet.Cells(i + 2, j + 1) = nStr
Catch
xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
End Try
Next
Next
The Excel file I exported (Notice some of the dates are dd/MM, whereas some are MM/dd)
我导出的 Excel 文件（注意有些日期是 dd/MM，而有些是 MM/dd）
回答by David
As suggested by @jkpieterse, I just needed to pass the date values as actual date datatypes. To do this, I modified the For Eachloops.
正如@jkpieterse 所建议的，我只需要将日期值作为实际日期数据类型传递。为此，我修改了For Each循环。
Original:
原来的：
For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
Try
eStr = Trim(dgvExport(j, i).Value)
nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
xlWorksheet.Cells(i + 2, j + 1) = nStr
Catch
xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
End Try
Next
Next
Working version:
工作版本：
For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
Try
If j <> 5 AndAlso j <> 8 AndAlso j <> 9 AndAlso j <> 12 AndAlso j <> 13 AndAlso j <> 16 Then
eStr = Trim(dgvExport(j, i).Value)
nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
xlWorksheet.Cells(i + 2, j + 1) = nStr
Else
xlWorksheet.Cells(i + 2, j + 1) = Convert.ToDateTime(dgvExport(j, i).Value).GetDateTimeFormats(Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat)
End If
Catch
xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
End Try
Next
Next
This means, if the index of the current column being evaluated is not one of the columns that contains a date, then export it as normal, but if it is, then convert it to a date time, where the format is the same as the current culture format for the thread.
这意味着，如果正在评估的当前列的索引不是包含日期的列之一，则将其正常导出，但如果是，则将其转换为日期时间，其中格式与线程的当前区域性格式。
This has worked like a dream.
这就像做梦一样。
