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

Question

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

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

输出将如下所示

​

​