An example of getting WebHMI data from Excel
Чтение данных из лога событий Getting data from event log
Below is a simple example of a query from Excel to the WebHMI API. In this example, data is read from the event log. This example uses an Active-X object and works only on Windows platforms. Additionally, a library is used for parsing JSON responses.
Download the example Файл:API Example.xlsm
Const URl As String = "http://192.168.0.1/api/event-data/1" Sub xmlHttp() Dim xmlHttp As Object Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0") xmlHttp.Open "GET", URl, False xmlHttp.setRequestHeader "Content-Type", "text/xml" xmlHttp.setRequestHeader "Accept", "application/json" xmlHttp.setRequestHeader "Host", "192.168.0.1" xmlHttp.setRequestHeader "Cookie", " " xmlHttp.setRequestHeader "X-WH-APIKEY", "6E51E728896794EBF406E2F070BE7AFBE49E90D4" xmlHttp.setRequestHeader "X-WH-START", "1388948941" xmlHttp.setRequestHeader "X-WH-END", "1399208143" xmlHttp.send Dim JSON As New JSON Dim p As Object Set p = JSON.parse(xmlHttp.ResponseText) i = 2 j = 1 For Each Item In p ' rows If (j = 1) Then Cells(i, j).NumberFormat = "yyyy-mm-dd hh:mm:ss" End If If (j = 2) Then Cells(i, j).NumberFormat = "#.#" '"$#,##0.00_);[Red]($#,##0.00)" End If For Each Item2 In Item 'columns For Each Item3 In Item2 If (j = 1 And i > 1) Then Cells(i, j) = (Item2(Item3) / 86400) + 25569 Else Cells(i, j) = Item2(Item3) End If Cells(1, j) = Item3 Next j = j + 1 Next i = i + 1 j = 1 Next End Sub
Reading/Writing current register values
The second example shows how to read the current values of the registers and write the new value into one of the registers. This example uses the https://github.com/VBA-tools/VBA-Web library
Download the example Файл:WebHMI registers read and write.xlsm
Sub GetRegisters() Dim Client As New WebClient Client.BaseUrl = "http://192.168.1.1/api" Dim Request As New WebRequest Request.Resource = "register-values" Request.Method = WebMethod.HttpGet Request.RequestFormat = WebFormat.JSON Request.ResponseFormat = WebFormat.JSON Request.AddHeader "X-WH-APIKEY", "8DA00F5F9B42A8D070651C58F495DB1C3191AF19" Dim Response As WebResponse Set Response = Client.Execute(Request) Cells.Range("A2:C1000").Clear If Response.StatusCode = WebStatusCode.Ok Then ' Success, parse response Dim RegValue As Object Set RegValues = Response.Data i = 2 For Each strKey In RegValues.Keys() ' rows Cells(i, 1) = RegValues(strKey)("r") Cells(i, 2) = RegValues(strKey)("v") Cells(i, 3) = RegValues(strKey)("s") i = i + 1 Next End If End Sub
Sub WriteValue() Cells(1, 9) = "Writing..." Dim Client As New WebClient Client.BaseUrl = "http://192.168.1.1/api" Dim Request As New WebRequest Request.Resource = "register-values/{Id}" Request.Method = WebMethod.HttpPut Request.RequestFormat = WebFormat.JSON Request.ResponseFormat = WebFormat.JSON Request.AddBodyParameter "value", Cells(2, 7) Request.AddUrlSegment "Id", Cells(1, 7) Request.AddHeader "X-WH-APIKEY", "8DA00F5F9B42A8D070651C58F495DB1C3191AF19" Dim Response As WebResponse Set Response = Client.Execute(Request) If Response.StatusCode = WebStatusCode.Ok Then ' Success Cells(1, 9) = "OK" Else ' Error, display error Cells(1, 9) = "ERROR" End If End Sub
Чтение лога регистров
Третий пример показывает как прочитать данные для регистров с ID=1 и 21 из лога регистров за последние 20 минут. Данный пример использует библиотеку https://github.com/VBA-tools/VBA-Web
Скачать пример: Файл:WebHMI registers log read.xlsm
Sub GetRegistersLog() Dim Client As New WebClient Client.BaseUrl = "http://192.168.1.1/api" Dim Request As New WebRequest Request.Resource = "register-log" Request.Method = WebMethod.HttpGet Request.RequestFormat = WebFormat.JSON Request.ResponseFormat = WebFormat.JSON timeZoneOffset = 2 ' Сейчас минус 20 минут Request.AddHeader "X-WH-START", ((Now - DateSerial(1970, 1, 1)) * 86400 - 60 * 60 * timeZoneOffset - 60 * 20) ' Сейчас Request.AddHeader "X-WH-END", (Now - DateSerial(1970, 1, 1)) * 86400 - 60 * 60 * timeZoneOffset Request.AddHeader "X-WH-APIKEY", "72B17C6D7B956F9F964258DFFCBAE0B67B8DFF7A" Request.AddHeader "X-WH-REG-IDS", "1,21" Dim Response As WebResponse Set Response = Client.Execute(Request) Cells.Range("A4:J1000").Clear If Response.StatusCode = WebStatusCode.Ok Then ' Success, parse response Dim RegValue As Object Set RegValues = Response.Data i = 4 For Each strKey In RegValues.Keys() ' rows Cells(i, 1) = strKey Cells(i, 2) = (strKey / 86400) + 25569 + (timeZoneOffset / 24) Cells(i, 2).NumberFormat = "yyyy-mm-dd hh:mm:ss" j = 1 For j = 1 To RegValues(strKey).Count Cells(i, j * 3) = RegValues(strKey)(j)("r") Cells(i, j * 3 + 1) = RegValues(strKey)(j)("v") Cells(i, j * 3 + 2) = RegValues(strKey)(j)("s") Next i = i + 1 Next End If End Sub