excel - 将CSV导入Excel

我需要将一些CSV导入Excel电子表格，每个CSV的行/列号都不同。问题是某些值是长数字字符串，例如
341235387313289173719237217391，

Excel会将这些值视为（双）数字，然后导致数据丢失。

解决该问题的方法是使用以下vba函数完成此工作：

Sub readCSV(f As TextStream, sh As Worksheet)
    i = 1
    Do
        l = Trim(f.ReadLine)
        If l = "" Then Exit Sub 'skip the last empty line(s)
        l = Mid(l, 2, Len(l) - 1)
        ss = Split(l, """,""")
        For j = LBound(ss) To UBound(ss) 'j starts from 0
            Dim a As Range
            With sh.Cells(i, j + 1)
                .NumberFormat = "@" 'Force to text format
                .Value = ss(j)
            End With
            DoEvents 'Avoid blocking the GUI
        Next j
        i = i + 1
    Loop Until f.AtEndOfStream
End Sub

问题是性能。这比通过Data-> From Text或直接打开CSV导入数据要慢得多。

有什么办法可以更有效地做到这一点？

最佳答案

您可以一口气格式化/写入每一行：

Sub readCSV(f As TextStream, sh As Worksheet)

     Dim i As Long
     Dim ss, l
     i = 1

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Do
        l = Trim(f.ReadLine)
        If l = "" Then Exit Sub 'skip the last empty line(s)
        l = Mid(l, 2, Len(l) - 1)
        ss = Split(l, """,""")

        With sh.Cells(i, 1).Resize(1, (UBound(ss) - LBound(ss)) + 1)
            If (i-1) Mod 100 = 0 Then .Resize(100).NumberFormat = "@"
            .Value = ss
        End With

        i = i + 1
    Loop Until f.AtEndOfStream

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With


End Sub

编辑：经过测试，真正的性能杀手是将单元格格式设置为文本修订的代码，以100行（而不是每行）为单位进行设置。

关于excel - 将CSV导入Excel，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11874865/