Ví dụ về API REST của Dịch vụ Excel

Bài đăng trên blog này nhằm mục đích giúp bạn bắt đầu sử dụng API REST của Phân tích lập kế hoạch. Excel sẽ được sử dụng làm giao diện người dùng với VBA để điều khiển truy vấn xử lý kết quả

Excel không phải là giao diện lý tưởng để làm việc với JSON nhưng thông qua việc sử dụng các mô-đun của bên thứ 3, JSON có thể được phân tích cú pháp thành các đối tượng có thể được xử lý trong Excel VBA

Trong bài đăng này, chúng tôi xem xét việc xây dựng các quy trình để truy xuất Phiên và Chủ đề được liên kết cũng như yêu cầu hủy Chủ đề

Có nhiều phương thức tương tác với API REST

• Một dịch vụ sử dụng MSXML2. XMLHTTP60
• Thông qua Đối tượng tự động hóa văn phòng Cognos bằng cách sử dụng đối tượng Báo cáo

Bài đăng này sẽ xem xét việc sử dụng MSXML2. Tùy chọn XMLHTTP60 không yêu cầu bổ trợ Lập kế hoạch Analytics cho Excel. Điểm khác biệt chính mà tôi nhận thấy khi làm việc với Lập kế hoạch Phân tích trên Đám mây (PAOC) là bạn chỉ có thể truy cập API REST thông qua người dùng tự động hóa được cung cấp (tài khoản không tương tác). Sử dụng đối tượng Báo cáo, bạn có thể tương tác với API REST thông qua người dùng được xác thực

Ví dụ của tôi, tôi sẽ kết nối với PAOC thông qua người dùng tự động hóa. Bạn có thể thay đổi điểm cuối cho máy chủ cục bộ của mình. g. http. // tm1server. /api/v1/

Trên trang tính đầu tiên của chúng tôi trong sổ làm việc, chúng tôi sẽ tạo chi tiết truy cập máy chủ

Chúng tôi sẽ sử dụng các tham số sau khi bắt đầu

Sự liên quan. Tên để xác định kết nối

Người phục vụ. Điểm cuối của máy chủ của tôi

cơ sở dữ liệu. Tên cơ sở dữ liệu TM1

tên tài khoản. Người dùng tự động hóa

Mật khẩu. Mật khẩu

Không gian tên CAM. Đối với PAOC, điều này được liên kết với LDAP

Đặt tên cho trang tính của bạn là Cấu hình và thêm từng tham số vào trang tính bắt đầu từ ô B3

Đối với mỗi tham số, hãy thêm một phạm vi đã đặt tên vào sổ làm việc như sau

Máy chủ, Cơ sở dữ liệu, Tên người dùng, mật khẩu, pCAMNamespace

Bạn nên có một trang tính trông giống như bên dưới

Trên trang tính của tôi, tôi có nhiều kết nối và đã liên kết kết nối của mình với danh sách các máy chủ để chọn và chuyển đổi theo yêu cầu. Bạn có thể thêm phần này vào giải pháp của mình sau để mở rộng nó hơn nữa

Chúng tôi sẽ cần 4 thành phần trước khi có thể kiểm tra kết nối;

• Base64Encoder để mã hóa thông tin đăng nhập của chúng tôi
• Trình chuyển đổi JSON để phân tích cú pháp JSON thành các đối tượng VBA mà chúng ta có thể xử lý
• Một hàm để thực hiện truy vấn và trả về đối tượng JSON được phân tích cú pháp
• Một quy trình để kiểm tra kết nối của chúng tôi với máy chủ

3. 1. Bộ mã hóa Base64

Có nhiều chức năng khác nhau có sẵn trên web nhưng tôi đã sử dụng các chức năng sau

Option Explicit
Function Base64Encode(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As Variant
  Dim objNode As Variant

  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  Base64Encode = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Thêm một mô-đun mới vào dự án VBA của bạn và gọi nó là modRESTAPI hoặc tương tự. Dán chức năng trên vào mô-đun. Tôi muốn thêm Tùy chọn rõ ràng để đảm bảo các biến được xác định chính xác và chúng tôi không gặp bất ngờ

3. 2. Trình chuyển đổi JSON

Tải xuống mô-đun của Tim Hall từ https. //github. com/VBA-tools/VBA-JSON

Nhập mô-đun vào dự án VBA của bạn để cung cấp mô-đun cho các cuộc gọi của chúng tôi

Điều này sử dụng các loại dữ liệu Từ điển và yêu cầu tham chiếu Thư viện Microsoft Scripting được chọn trong Công cụ, Tài liệu tham khảo

3. 3. Hàm truy vấn API REST

Sao chép chức năng bên dưới và thêm vào dự án VBA của bạn trong mô-đun bạn đã tạo trước đó

Function ExecuteQuery(pAction As String, pQueryString As String, Optional strPayload As String) As Object

    Dim TM1Service As New MSXML2.XMLHTTP60
    
    Dim pServer As String
    Dim pDatabase As String
    Dim pUsername As String
    Dim pPassword As String
    Dim pCAMNamespace As String
    
    Dim sBase64Credentials As String
    
    Dim sQueryString As String
    Dim sQueryResult As String
    
    Dim bAsynch As Boolean
    
    Sheets("Config").Calculate
    
    pServer = ThisWorkbook.Names("pServer").RefersToRange
    pDatabase = ThisWorkbook.Names("pDatabase").RefersToRange
    pUsername = ThisWorkbook.Names("pUsername").RefersToRange
    pPassword = ThisWorkbook.Names("pPassword").RefersToRange
    pCAMNamespace = ThisWorkbook.Names("pCAMNamespace").RefersToRange
       
    sBase64Credentials = Base64Encode(pUsername & ":" & pPassword & ":" & pCAMNamespace)

    With TM1Service
            'Query design for PAOC but could add in some code to look for ibmcloud.com and use the below or a local endpoint
            sQueryString = pServer & "tm1/api/" & pDatabase & "/api/v1/" + pQueryString
             
            If UCase(pAction) = "POST" Then
               bAsynch = True
            Else
               bAsynch = False
            End If
             
            .Open pAction, sQueryString, bAsynch
             
            .setRequestHeader "Content-Type", "application/json"
            .setRequestHeader "Accept", "application/json;odata.metadata=none"
            .setRequestHeader "TM1-SessionContext", "TM1 REST API tool"
            .setRequestHeader "Authorization", "CAMNamespace " & sBase64Credentials
           
            .Send strPayload
             
            While .readyState <> 4
                DoEvents
            Wend
            
            If .Status >= 400 And .Status <= 599 Then
                sQueryResult = CStr(.Status) + " - " + .statusText
                If .responseText <> "" Then
                    sQueryResult = sQueryResult + vbCrLf & .responseText
                End If
                MsgBox "Error " + sQueryResult, vbCritical, "Connection"
                GoTo Cleanup
            End If
        
        sQueryResult = .responseText
        
        'This outputs the JSON to the Immediate window.
        'You can copy this to a viewer like http://jsonviewer.stack.hu/ to interrogate the JSON
        Debug.Print sQueryResult
        
    End With
    
    If sQueryResult <> "" Then
        Set ExecuteQuery = JsonConverter.ParseJson(sQueryResult)
    Else
        Set ExecuteQuery = Nothing
    End If
    
Cleanup:
        
End Function

Đảm bảo đi tới Công cụ, Tài liệu tham khảo và chọn Microsoft XML, v6. 0 từ các tham chiếu có sẵn vì TM1Service tận dụng điều này

Hướng dẫn về những gì chức năng làm

Tất cả các tham số máy chủ cần thiết được truy xuất

Tên người dùng, mật khẩu và CAMNamespace được nối sau đó mã hóa Base64 để xác thực

Chuỗi truy vấn được xây dựng dựa trên các tham số được truyền từ quy trình người gọi cơ bản

Tiếp theo, chúng ta cần đặt Tiêu đề yêu cầu cho cuộc gọi mà chúng ta sắp thực hiện. Điều này đặt loại nội dung dự kiến ​​thành JSON và một số cài đặt khác được yêu cầu

Lưu ý rằng tôi đang thiết lập odata. metadata=none vì tôi không cần bất kỳ thông tin bổ sung nào như odata. etag. Điều này cũng có thể được đặt thành tối thiểu hoặc loại bỏ tùy thuộc vào những gì bạn muốn thấy được trả lại

Tôi cũng đã đặt TM1-SessionContext để tôi có thể dễ dàng xem đâu là cuộc gọi từ thử nghiệm của mình so với các phiên khác

Sau đó chúng tôi gửi yêu cầu và nhận được phản hồi. Nếu phản hồi chưa sẵn sàng, chúng tôi thực hiện các sự kiện rồi kiểm tra lại

Phản hồi của chúng tôi sau đó được kiểm tra để xem liệu chúng tôi có thành công hay lỗi. Xem https. //www. w3. org/Giao thức/rfc2616/rfc2616-sec10. html để biết thêm về mã trả lại

Giả sử chúng tôi có mã thành công và một số JSON được trả về từ truy vấn, chúng tôi phân tích cú pháp JSON bằng JSONConverter và trả lại kết quả cho quy trình gọi

Nếu gặp phải lỗi, chúng tôi sẽ hiển thị lỗi và văn bản liên quan để hỗ trợ khắc phục sự cố

3. 4. chức năng kiểm tra

Chức năng kiểm tra của chúng tôi chỉ đơn giản là yêu cầu thông tin máy chủ và trả về hộp thông báo nếu kết nối thành công

Sub GetServer()
    Dim oJSON As Variant
    Dim oKey As Variant
    Dim pQueryString As String
    Dim sResult As String
    
    pQueryString = "Server"
    
    Set oJSON = ExecuteQuery("Get", pQueryString)
    sResult = ""
    
    If Not oJSON Is Nothing Then
        For Each oKey In oJSON
            sResult = sResult & oKey & " - " & oJSON(oKey) & vbCrLf
        Next
        MsgBox sResult, vbInformation, "Server Details"
    End If

End Sub

Bạn sẽ có thể chạy mã để kiểm tra mã hoặc xem từng dòng mã để kiểm tra xem từng bước đang làm gì

JSON trả về được trả về một đối tượng mà trong trường hợp này là một đối tượng từ điển tôi. e. cặp khóa và giá trị

Mã lặp qua từng khóa và trả về giá trị được liên kết thành một chuỗi, sau đó bật kết quả lên trong hộp thông báo

Bây giờ kết nối đã được kiểm tra thành công, chúng ta có thể chuyển sang truy xuất Phiên và Chủ đề

4. Truy xuất phiên và chủ đề

Chúng tôi muốn truy xuất cả Phiên và Chủ đề trong một truy vấn. Chủ đề được liên kết với Phiên, điều đó có nghĩa là chúng tôi có thể mở rộng Phiên để bao gồm Chủ đề bằng cách sử dụng truy vấn như

Phiên?$expand=Chủ đề

Một phiên trong JSON kết quả sẽ trông giống như bên dưới

{
            "ID": 5707,
            "Context": "",
            "Active": true,
            "Threads": [
                {
                    "ID": 13016,
                    "Type": "User",
                    "Name": "LDAP/xxx_tm1_automation ccc_user CAMID(\"LDAP:u:uid=xxx_tm1_automation,ou=people\")",
                    "Context": "",
                    "State": "Run",
                    "Function": "GET /api/v1/Sessions",
                    "ObjectType": "",
                    "ObjectName": "",
                    "RLocks": 2841,
                    "IXLocks": 0,
                    "WLocks": 0,
                    "ElapsedTime": "P0DT00H00M00S",
                    "WaitTime": "P0DT00H00M00S",
                    "Info": ""
                }
            ]
        },...

Chủ đề có thể chứa nhiều bản ghi cho cùng Phiên e. g. nơi đa luồng đang xảy ra. Lưu ý rằng Chủ đề có hình vuông thì mỗi Chủ đề có dấu ngoặc nhọn. Các dấu ngoặc vuông được coi là một mảng trong khi các dấu ngoặc nhọn được hiểu là một từ điển

Thêm một trang tính mới vào sổ Excel để trả về kết quả Phiên và Chủ đề

Trong trang tính của tôi, tôi có một hàng tiêu đề ở hàng 7 và trả về kết quả cho hàng 8 trở đi. Trên hàng 7 tôi có một số khoảng trống để thêm các nút lệnh

Ô A7 chứa ID, B7, Ngữ cảnh sau đó Hoạt động, ID chủ đề, v.v. mỗi JSON ở trên

Đối với Ô A8, tôi đã thêm một phạm vi được đặt tên, nằm trong phạm vi trang tính, được gọi là Phiên và thêm vào văn bản "Bắt đầu". Điều này cho biết bắt đầu phạm vi kết quả của tôi nhưng quan trọng là cũng cập nhật ô cuối cùng được sử dụng cho chức năng rõ ràng

Tôi cũng đã thêm một nút để gọi macro của mình để giúp chạy quy trình dễ dàng hơn

Thêm mã dưới đây vào mô-đun của bạn. Điều này sẽ thực hiện truy vấn và trả về kết quả cho trang tính

Sub GetActiveSessions()
Dim oJSON As Variant
Dim oSession As Variant
Dim oThreads As Variant
Dim oThread As Variant
Dim oThreadDetail As Variant
Dim pQueryString As String
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer
Dim sResultRange As String

Application.EnableEvents = False
Application.ScreenUpdating = False


'Clear old rows
sResultRange = "Sessions"
Range(Range(sResultRange), Range(sResultRange).SpecialCells(xlLastCell)).EntireRow.Clear

pQueryString = "Sessions?$expand=Threads"
Range("qryREST").Value2 = pQueryString

Set oJSON = ExecuteQuery("Get", pQueryString)
sResult = ""

If Not oJSON Is Nothing Then
    iRow = 0
    For Each oSession In oJSON("value")
        iCol = 0
        For Each oThreads In oSession
            If oThreads <> "Threads" Then
                Range(sResultRange).Offset(iRow, iCol).Value2 = oSession(oThreads)
                iCol = iCol + 1
            Else
                'Threads - defined as oJSON("value")()("Threads")()("ID")
                If oSession("Threads").Count > 0 Then
                    For Each oThread In oSession("Threads")
                        iCol = 3
                        For Each oThreadDetail In oThread
                            Range(sResultRange).Offset(iRow, iCol).Value2 = oThread(oThreadDetail)
                            iCol = iCol + 1
                        Next
                        
                        If oThread("ID") <> oSession("Threads")(oSession("Threads").Count)("ID") Then
                            iRow = iRow + 1
                        End If
                    Next
                End If
            End If
        Next
        iRow = iRow + 1
    Next
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Với đoạn mã trên, có thể hữu ích khi thêm một số đồng hồ để hiểu rõ hơn từng đối tượng chứa gì

Theo đoạn mã JSON ở trên, có các thuộc tính liên quan đến Phiên và sau đó là vùng chứa không có nhiều Chủ đề hơn

Bảng Phiên của tôi trông giống như đoạn mã bên dưới sau khi chạy macro

Bạn có thể xem chi tiết Phiên với chi tiết Chủ đề bên cạnh. Khi có nhiều Chủ đề, chúng sẽ được lặp lại bắt đầu từ cột D của các hàng tiếp theo

5. Hủy bỏ một chủ đề

Về cơ bản, chúng tôi muốn chọn một ID chủ đề và chuyển nó tới một hành động API REST sẽ yêu cầu hủy bỏ

Thêm đoạn mã sau vào mô-đun của bạn

Sub ThreadCancel()

    Dim oJSON As Variant
    Dim pQueryString As String
    
    Dim sThread As String
   
    'Thread ID is in column D
    sThread = ActiveCell.EntireRow.Columns(4).Value2
    If sThread = "" Then GoTo Cleanup:
    
    pQueryString = "Threads('" & sThread & "')/tm1.CancelOperation"
    
    Set oJSON = ExecuteQuery("Post", pQueryString)
    
Cleanup:

End Sub

Quá trình sẽ đọc ID luồng từ cột D cho hàng của ActiveCell. Giá trị này sau đó được gửi đến CancelOperation

Bạn có thể thêm một nút khác vào trang tính của mình để hủy Chủ đề bằng cách gán quy trình Hủy Chủ đề cho nó

Để kiểm tra chức năng này, bạn sẽ cần chạy một quy trình từ PAW hoặc có thể tạo một quy trình đơn giản với chức năng Ngủ để giữ cho quy trình chạy trong vài giây

Ảnh chụp màn hình bên dưới hiển thị Phiên và Chủ đề đang hoạt động cùng với quá trình đang chạy

Để hủy Chủ đề, hãy chọn một ô trong hàng có liên quan, sau đó nhấp vào nút Hủy Chủ đề hoặc chạy quy trình của bạn.  

Sau đó, PAW sẽ hiển thị cho bạn một hộp thông báo rằng quy trình đã bị hủy (có thể không hiển thị cho người dùng không phải quản trị viên)

 

6. Tóm lược

Bây giờ bạn có một cách để kiểm tra Phiên và Chủ đề đang hoạt động đang chạy trên một máy chủ được chỉ định

Bạn cũng có cách để hủy Chủ đề nếu có sự cố với quy trình

Bạn có thể mở rộng ví dụ này bằng cách thêm vào bảng cấu hình của mình, phục vụ cho nhiều kết nối. Bạn cũng có thể thay đổi quy trình Hủy Chủ đề của mình để phục vụ cho nhiều Chủ đề đã chọn

Những ví dụ này đã đặt nền tảng cho nhiều dự án truy vấn API REST khác và sẽ cho phép bạn dễ dàng mở rộng để truy vấn khối, kích thước, tập hợp con, v.v.

Vui lòng để lại nhận xét nếu bạn thấy điều này hữu ích hoặc có điều gì khác muốn nói. Hãy cho tôi biết bất kỳ lỗi nào để tôi có thể giải quyết chúng