使用Excel VBA导入大型.dat文件时的数值格式异常问题

问题背景 #

• 测量工具导出的.dat文件已转换为分号分隔列、逗号作为小数位的txt格式，共11000行
• Power Query导入无异常，但使用VBA将Variant数组批量写入工作表时，第660行出现格式错误：单元格自动设为「数字」格式，数值显示异常，而数组内的原始数据是正常的

核心原因 #

Excel批量写入Variant数组时，会依据当前系统区域设置自动识别单元格格式。若系统默认小数分隔符为点（.），而文件中用逗号（,）作为小数分隔符，Excel会将这类字符串错误识别为文本或触发异常格式转换，导致部分行显示异常。

解决方案 #

方案1：提前设置目标区域为常规格式（推荐） #

在写入数组前，强制将目标区域的单元格格式设为「常规」，避免Excel自动触发格式推断：

With Sheets(parSheetName)
    .Cells.ClearContents
    ' 提前设置目标区域格式为常规
    .Cells(4, 1).Resize(UBound(Data, 1), UBound(Data, 2)).NumberFormat = "General"
    ' 批量写入数组
    .Cells(4, 1).Resize(UBound(Data, 1), UBound(Data, 2)) = Data
End With

方案2：转换数组内数值为系统兼容格式 #

在getDataFromFile函数中，将文件内的逗号小数位字符串转换为符合系统区域的数值类型，从根源避免格式识别错误：
修改函数中赋值的代码块：

Else
  For I = 1 To locNumRows
    For J = 0 To UBound(locLinesList(I), 1)
      ' 将逗号替换为系统默认小数分隔符
      Dim tempVal As String
      tempVal = Replace(locLinesList(I)(J), ",", Application.DecimalSeparator)
      ' 判断是否为数值，是则转换为数值类型，否则保留原字符串
      If IsNumeric(tempVal) Then
          locData(I, J + 1) = CDbl(tempVal)
      Else
          locData(I, J + 1) = locLinesList(I)(J)
      End If
    Next J
  Next I
End If

方案3：逐单元格写入（备选，效率稍低） #

若批量写入仍有异常，可改用逐单元格写入，彻底规避Excel的自动格式推断：

' 替换原批量写入代码
For I = 1 To UBound(Data, 1)
    For J = 1 To UBound(Data, 2)
        With Sheets(parSheetName).Cells(I + 3, J)
            .Value = Data(I, J)
            .NumberFormat = "General"
        End With
    Next J
Next I

验证步骤 #

1. 运行修改后的VBA代码
2. 检查第660行的单元格格式是否为「常规」
3. 确认数值显示与原文件内容完全一致

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