DataGridView导出Excel时日期、数字数据类型格式无法识别

运行环境 #

• Visual Studio 2015
• 运行于Firebird 3.0下的db.gdb数据库
• 搭配DataSet的.Net Framework 4.6框架

引用组件 #

• FirebirdSql.Data.FirebirdClient 7.10.1.0版本
• Microsoft.Office.Interop.Excel 12.0.0.0版本

异常表现 #

1. 日期导出异常 #

将DataGridView导出至Excel（Office 2007 12版本）时出现格式错位：
DataGridView中Date字段为意大利格式03/06/2022（dd/MM/yyyy，对应2022年6月3日），导出到Excel后变为06/03/2022，被识别为2022年3月6日；符合默认月/日解析规则的日期会被自动转为日期类型，而31/05/2022（意大利格式对应2022年5月31日）这类无法被默认日期规则识别的值，会作为文本类型导出。

2. 数字导出异常 #

整数例如27可正常识别为数字类型导出，而使用逗号作为小数分隔符的27,50无法被识别，会作为文本类型导出。
备注： 本地系统区域设置为意大利/意大利语，日期格式为dd/MM/yyyy，小数分隔符为逗号（,）。此前尝试多种修复方式均未生效。2005年至2021年开发维护同一款程序时，使用db.mdb数据库、Visual Studio .NET 2.0、DataSet，导出功能完全正常，从未出现过此类问题。

相关代码 #

数据库中存储的Date字段为Double格式，相关逻辑代码如下：

Imports ExcelAPP = Microsoft.Office.Interop.Excel

'This shows the column as Date
Private startDate As New Date(1899, 12, 30)
Private Sub DTBollePagateDataGridView_CellFormatting(ByVal sender 
As Object, ByVal e As 
System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles 
DTBOLLEPAGATEDataGridView.CellFormatting

Try

If Me.DTBOLLEPAGATEDataGridView.Columns(e.ColumnIndex).HeaderText 
= "DATA" Then

If e.Value Is DBNull.Value Then Exit Sub
If e.Value IsNot Nothing Then e.Value = 
startDate.AddDays(Convert.ToDouble(e.Value)).ToShortDateString

End If

Catch ex As Exception

Dim lNewVariable2 As String = "mailto:?subject=Invio Dati&body= 
{0}{1}{2}"
        
System.Diagnostics.Process.Start(String.Format(lNewVariable2, 
ex.Message, Environment.NewLine, ex.StackTrace))

End Try
End Sub

'Export to Excel
'Impostazione dei formati (This doesn't work)
'FogliodiLavoro.Range("D1:D200").NumberFormat = "dd/MM/yyyy"   

已尝试在导出时直接设置目标单元格区域的NumberFormat为dd/MM/yyyy，但方案未生效。

问题根源是导出时读取的是DataGridView经过格式化后的字符串值，而非原始的日期/数值类型值。Excel Interop接收字符串写入单元格时，会优先按照自身加载时默认的区域规则自动解析值，解析失败就直接存为文本；提前设置NumberFormat不生效，是因为值写入时已经完成了类型判定，后续修改格式不会重新触发解析流程。

日期问题修复 #

不要在CellFormatting事件中把日期转成ToShortDateString字符串后再导出，按以下逻辑调整：

• 导出时不要读取DataGridView.Rows[i].Cells[j].FormattedValue，直接读取绑定数据源（DataTable/DataRow）中的原始Double类型日期值
• 写入单元格时直接传入startDate.AddDays(原始Double值)对应的DateTime对象，不要转成任何格式的字符串
• 整列值写入完成后，再设置对应列的NumberFormat = "dd/MM/yyyy"，此时单元格存储的是原生日期类型，格式设置会正常生效，不会出现月日错位问题

数字问题修复 #

和日期问题逻辑一致：不要传入带逗号小数分隔符的格式化字符串给Excel，直接传入原始Double/Decimal类型的数值。写入完成后给对应数字列设置匹配意大利区域的NumberFormat即可，例如NumberFormat = "0,00"，即可正常显示逗号作为小数分隔符的数值，不会被识别为文本。

旧版本程序正常的原因 #

2005-2021年使用Access + .NET 2.0的版本，导出逻辑大概率是直接从DataTable读取原始值写入，而非读取DataGridView格式化后的显示字符串，不会触发Excel的字符串自动解析逻辑。同时.NET 2.0的Interop类型封送、旧版本Office Interop的默认解析规则，和.NET 4.6 + 高版本FirebirdClient环境下的行为存在差异，不会强制将传入的日期/数字字符串按en-US区域规则解析。

快速验证方法：先给目标单元格区域设置好NumberFormat，写入值时直接传入原生DateTime、数值类型，不要传任何格式化后的字符串，测试2-3行数据即可看到效果，所有日期错位、数值存为文本的问题都会直接解决。

内容的提问来源于stack exchange，提问作者IronButterfly