excel - 我想从 excel 导出格式化我的 csv 文件

Question

我对新导出的 csv 文件中的日期格式有疑问。
如果我从 excel 文件中导出一个范围，那么日期格式是 dd/mm/yyyy，但我需要 dd/mm/yyyy hh:nn。
如果我将原始 excel 文件中的格式更改为正确的格式，则 csv 中的大多数值都会显示正确的格式，但日期值除外，例如18.08.2021 00:00。
因此，如果时间是 00:00，那么只有 dd/mm/yyyy 格式出现在该行，并且这种格式与数据库不兼容。
(我在excel和编辑器中打开它，它出现了同样的问题)
有人能帮我吗？

    Dim ws As Worksheet, fd As FileDialog, rngTest As Range, rngExport As Range, fltr As FileDialogFilter
    Dim start As Long
    start = 2
    
    
    'Nach jeweiliger Zeit wird Datenreihe (start ab) ausgewählt
    If Time < TimeValue("11:15") Then
        Do Until Daten.Range("ov" & start) = Date + 1
        start = start + 1
        Loop
    ElseIf Time < TimeValue("11:15") Then
        Do Until Daten.Range("ov" & start) = Date + 2
        start = start + 1
        Loop
    Else: start = 2
    End If
    
    
    
   start = start + 1

    'Worksheet auf dem die Daten stehen
    Set ws = Worksheets("Daten")
     
    
    'Zelle die auf Inhalt überprüft werden soll
    Set rngTest = ws.Range("ov2")
    'Bereich der exportiert wird
    Set rngExport = ws.Range("ov" & start & ":ow10000")
'    ws.Range("ov" & start & ":ov5000").NumberFormat = "dd/mm/yyyy hh:mm"
    If rngTest.Text <> "" Then
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        'Filename
        fd.InitialFileName = "LG" & " " & Diagramm.Range("a5").Value & " " & "RZ" & " " & Format(Date, "mmmm") & " " & Format(Date, "yyyy") & "_" & "MW" & "_" & "ab" & " " & Daten.Range("ov" & start - 1).Value
       ' Application.Dialogs(xlDialogSaveAs).Show filenameComplete
        With fd
            .Title = ""
            'Filterindex für CSV-Dateien ermitteln
            For i = 1 To .Filters.count
                If .Filters(i).Extensions = "*.csv" Then
                    .FilterIndex = i
                    Exit For
                End If
            Next
            'Wenn OK geklickt wurde starte Export
            If .Show = True Then
                ExportRangeAsCSV rngExport, ";", .SelectedItems(1)
                
            End If
        End With
    End If
End Sub

'Hier werden die Werte in eine CSV-Datei eingefügt und gespeichert
Sub ExportRangeAsCSV(ByVal rng As Range, delim As String, filepath As String)
    Dim arr As Variant, line As String, csvContent As String, fso As Object, csvFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set csvFile = fso.OpenTextFile(filepath, 2, True)
    
    arr = rng.Value 'Filter
    If IsArray(arr) Then
    
            ' um die Überschrift im CSV oben einzufügen
        Dim col As Range
        For Each col In rng.Columns
           If Len(line) > 0 Then line = line & delim
           line = line & """" & rng.Parent.Cells(1, col.column) & """"
        Next
        csvContent = line & vbNewLine
    
    
            'um die Werte ins CSV einzufügen
        For r = 1 To UBound(arr, 1)
            line = ""
            For c = 1 To UBound(arr, 2)
                If c < UBound(arr, 2) Then
                    line = line & """" & arr(r, c) & """" & delim
                Else
                    line = line & """" & arr(r, c) & """"
                End If
            Next
          
            csvContent = csvContent & line & vbNewLine
           
        Next
        
        csvFile.Write (csvContent)
        csvFile.Close
    Else
        MsgBox "Bereich besteht nur aus einer Zelle!", vbExclamation
    End If
    Set fso = Nothing
    Set csvFile = Nothing
End Sub

Range("ov").value 是日期， Range("ow").value 是金额( double )

Answer 1

由于您已经在手动编写 CSV 文件，我建议引入一个将单元格内容转换为字符串的函数。您可以将此例程用于多种目的，例如格式化数字(小数、位数...)、删除字符串中不需要的字符(例如换行符、分号、引号字符)...
只是给你一个想法:

Function FormatCellValue(v As Variant) As String
    If IsDate(v) Then
        FormatCellValue = Format(v, "dd.mm.yyyy hh:mm")
    ElseIf VarType(v) = vbDecimal Then
        FormatCellValue = Format(v, "#####.00")
    ElseIf VarType(v) = vbString Then
        v = Replace(v, vbCr, " ")
        v = Replace(v, vbLf, " ")
        v = Replace(v, ";", ",")
        v = Replace(v, """", "'")
        FormatCellValue = v
    Else
        FormatCellValue = v
    End If
End Function

在您现有的代码中，只需编写

line = line & """" & FormatCellValue(arr(r, c)) & """"

更新
如果你只想写带引号和日期(和数字)的字符串，你可以在 FormatCell 函数中添加引号:在 Vartype = vbString - 分支，写

 FormatCellValue = """" & v & """" 

并将调用更改为

 line = line & FormatCellValue(arr(r, c))