|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
json格式简单规范,用个取巧的方法
- Sub test()
- Dim Arr, Rule, Tmp, Result, N&, I&, Str$, Dic As Object, mTimer#
- mTimer = Timer
- With Worksheets("sheet1")
- Arr = .Range("a1:a" & .Cells(.Rows.Count, 1).End(3).Row).Value
- End With
- Rule = Split("id,gt,sp,glng,glat", ",")
- Set Dic = CreateObject("scripting.dictionary")
- For N = LBound(Rule) To UBound(Rule)
- Dic(Rule(N)) = ""
- Next N
- ReDim Result(LBound(Arr) To UBound(Arr), LBound(Rule) + 1 To UBound(Rule) + 1)
- Tmp = Split(Arr(LBound(Arr), 1), """")
- For I = LBound(Tmp) To UBound(Tmp)
- If Dic.exists(Tmp(I)) Then
- If Tmp(I + 1) = ":" Then
- Dic(Tmp(I)) = I + 2
- Else
- Dic(Tmp(I)) = I + 1
- End If
- End If
- Next I
- Rule = Dic.items
- For N = LBound(Arr) To UBound(Arr)
- Tmp = Split(Arr(N, 1), """")
- For I = LBound(Rule) To UBound(Rule)
- If Rule(I) <> "" And Rule(I) <= UBound(Tmp) Then
- Str = Tmp(Rule(I))
- If Left(Str, 1) = ":" Then Str = Mid(Str, 2, Len(Str) - 2)
- Result(N, I + 1) = Str
- End If
- Next I
- Next N
- With Worksheets("sheet3")
- .Cells.Clear
- .[a1].Resize(UBound(Result), UBound(Result, 2)).Value = Result
- End With
- Set Dic = Nothing
- Debug.Print Timer - mTimer
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|