excel - 在 excel 中读取 F5 配置文件(检索不同行中括号之间的值)

Question

我有一个包含许多 block 的配置文件，如下所示(带有嵌套括号)。

ltm pool /Common/This_Is_The_Name {
    load-balancing-mode least-connections-member
    members {
        /Common/Member01 {
            address 1.2.3.4
        }
        /Common/Member02 {
            address 2.3.4.5
        }
    }
    monitor /Common/tcp
}

我试过寻找像 } 这样的字符或 {但它们在文本中出现不止一次，并且宏失败。此外，并非所有配置都相同或顺序相同。

我所做的太长而无法粘贴并且无法正常工作。

如何以某种格式在 excel 中打印它，例如:

POOL NAME | LOAD-BALANCING MODE | MEMBER 01 NAME | MEMBER 01 IP | MEMBER 02 NAME | MEMBER 02 IP | MONITOR
This is the name | Least-connection-member | Member01 | 1.2.3.4 | Member02 | 2.3.4.5 | /common/tcp

我正在从 txt 文件中读取这些值。

Answer 1

尽管可能有其他简单的方法来解析文件，但它是文件的蛮力方法解析。您可以试试这个并根据您的要求进行修改。使用的数组变暗为(1 到 100)以进行快速测试，但您可以使用 Redim以有效利用它。我使输出格式与我的选择不同，也可以根据您的选择进行修改。

显然，只有当每个起始大括号都有相应的结束大括号并正确嵌套时，代码才能正常工作。

代码:

Option Explicit
Sub ParseConfigFile()
Dim Fno As Integer, Xstr As String, Fname As String
Dim lvl As Integer, i As Integer, x As Integer, y As Integer, Rw As Integer
Dim Ws As Worksheet, Xchr As String
Set Ws = ThisWorkbook.ActiveSheet

Fno = FreeFile
Fname = "C:\Users\user\Desktop\ConfigSample.txt"
Open Fname For Input As #Fno
Xstr = Input(LOF(Fno), Fno)
Close #Fno

Xstr = Replace(Xstr, vbCrLf, "")
Xstr = Replace(Xstr, "/", "|")


Dim Snest(1 To 100, 1 To 100) As Variant, Enest(1 To 100, 1 To 100) As Variant
Dim path(1 To 100) As Variant

For x = 1 To 100
For y = 1 To 100
Snest(x, y) = 0
Enest(x, y) = 0
Next
Next

lvl = 1
path(lvl) = 1
Snest(path(lvl), 1) = 1
Rw = 1
    For x = 1 To Len(Xstr)
    Xchr = Mid(Xstr, x, 1)
        If Xchr = "{" Then
        'Debug.Print "{", x, lvl, path(lvl)
        Enest(path(lvl), lvl) = x - 1
            'rw = rw + 1
            'For i = 1 To lvl
            'Ws.Cells(rw, i).Value = Trim(Mid(Xstr, Snest(path(i), i), Enest(path(i), i) - Snest(path(i), i) + 1))
            'Next
        lvl = lvl + 1
        path(lvl) = path(lvl) + 1
        Snest(path(lvl), lvl) = x + 1
        End If

        If Xchr = "}" Then
        'Debug.Print x, lvl, path(lvl)
        Enest(path(lvl), lvl) = x - 1
            Rw = Rw + 1
            For i = 1 To lvl
            Ws.Cells(Rw, i).Value = Trim(Mid(Xstr, Snest(path(i), i), Enest(path(i), i) - Snest(path(i), i) + 1))
            Next
        lvl = lvl - 1
        path(lvl) = path(lvl) + 1
        Snest(path(lvl), lvl) = x + 1
        End If
Next
End Sub

通过复制示例文本 4 次创建的示例文件的输出如下



进一步简化以在 excel 单元格上获得纯树结构

Option Explicit
Sub ParseConfigSimple()
Dim Fno As Integer, Xstr As String, Fname As String
Dim lvl As Integer, i As Integer, x As Integer, y As Integer, Rw As Integer
Dim Ws As Worksheet, Xchr As String
Set Ws = ThisWorkbook.ActiveSheet

Fno = FreeFile
Fname = "C:\Users\user\Desktop\ConfigSample.txt"
Open Fname For Input As #Fno
Xstr = Input(LOF(Fno), Fno)
Close #Fno

Xstr = Replace(Xstr, vbCrLf, "")
Xstr = Replace(Xstr, "/", "|")


Dim Spos As Long, Epos As Long, Plen As Long
Dim path(1 To 100) As Variant


lvl = 1
Spos = 1
Rw = 1
    'Make heading
    For x = 1 To 50
    Ws.Cells(Rw, x).Value = "Level " & x
    Next

    'Parse each item in nested level
    For x = 1 To Len(Xstr)
    Xchr = Mid(Xstr, x, 1)

        If Xchr = "{" Or Xchr = "}" Then
        Epos = x - 1
        Plen = Epos - Spos + 1
        path(lvl) = Trim(Mid(Xstr, Spos, Plen))
            If Len(path(lvl)) > 1 Then
            Rw = Rw + 1
            Ws.Cells(Rw, lvl).Value = path(lvl)
            End If
        Spos = x + 1
        Epos = x - 1
            If Xchr = "{" Then
            lvl = lvl + 1
            Else
            lvl = lvl - 1
            End If
        End If
Next
End Sub

输出将像