(KR)MIDAS Civil with VBA
VBA는 Excel를 다루는 사용자에게 가장 유용하고 접근이 쉬운 개발언어로, 이를 이용하여 MIDAS-Civil API를 사용하는 방법을 알아보도록 하겠습니다.
(본 컨텐츠는 VBA, Json, Restfull API에 간단한 기본지식이 있는 분들이 유용하게 사용할 수 있도록 작성되었습니다.)
Json 다루기!
MIDAS-API에서 데이터교환은 Json format으로 이루어 지나, VBA에서는 Json format을 공식적으로 지원해주지 않습니다. 따라서, 아래와 같이 Dictionary Object와 JsonConverter Library를 이용하여 Json format을 작성합니다.
그럼 Dictionary Object와 JsonConverter가 무엇인지 알아볼까요?
Dictionary Object 란?
Dictionary Object는 VBA에서 사용할 수 있는 변수의 일종으로 Key : Item 형태로 쌍의 데이터를 가지는 객체입니다. 이와 관한 MS의 공식 설명은 아래 링크에서 확인할 수 있습니다.
Dictionary object | Microsoft Learn
Dictionary Object는 2가지 방법으로 사용(선언 및 작성)이 가능합니다.
첫번째, Late Binding 방법으로 선언과 작성을 아래와 같은 코드를 사용합니다.
'late binding method
Dim DicEx As Object 'Declare
Set DicEx = CreateObject("Scripting.Dictionary") 'Create
둘째로, Early Binding 방법이 있습니다.
우선, 메뉴->도구->참조-> Microsoft Scripting Runtime을 활성화하고, 아래와 같이 사용합니다.
'early binding method
Dim DicEx As Dictionary 'Declare
Set DicEx = New Dictionary 'Create
본 컨텐츠는 후자(Early binding)의 방법을 이용하여, 작성하도록 하겠습니다.
후술할 JsonConvert 가 Early Binding이 구성되어 있으므로, 이를 이용해야 정상적인 동작이 가능하며 또한, 더 유연한 코딩 작성이 가능합니다.
JsonConverter 란?
GitHub를 통해 개인개발자가 무료로 배포한 VBA Library 이며, 이를 이용하여 Dictionary Object를 Json format으로 변경하거나, 반대로 Json format을 Dictionary Object로 만들어주는 유용한 툴입니다.
아래 사이트에서 코드를 복사하고, VBA 모듈을 만들고 “JsonConverter” 라는 이름으로 바꾼 후, 복사한 코드를 붙여 넣습니다.
GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA
MIDAS Civil과 데이터 주고받기!
MIDAS Civil과의 데이터는 아래의 Function을 통해 주고 받습니다.
입력변수는 다음과 같습니다.
Method as string
“GET” = 데이터를 출력
“POST” = 데이터를 입력
“PUT” = 입력된 데이터를 수정
Command as string (자세한 내용은 각 설명서 참조)
/doc/xxxx = 파일과 관련된 동작
/db/xxxx = 데이터베이스 입출력 관련 동작
/view/xxxx = 삽도 관련 동작
/post/xxxx = 결과테이블 관련 동작
Body as string
Json format
Function WebRequest(Method As String, Command As String, Body As String) As String
Dim TCRequestItem As Object
Dim URL As String
Dim MAPIKey As Variant
Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
'SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
TCRequestItem.SetTimeouts 200000, 200000, 200000, 200000
'Get MAPI-Key from Sheets
MAPIKey = Range("MAPIKey").Value
'URL & Commend
URL = "https://api-beta.midasit.com:443/civil" & Command
'Request API
TCRequestItem.Open Method, URL, False
TCRequestItem.SetRequestHeader "Content-type", "application/json"
TCRequestItem.SetRequestHeader "MAPI-Key", MAPIKey
TCRequestItem.Send Body
'API Response
WebRequest = TCRequestItem.ResponseText
End Function
MIDAS Civil 에서 Data를 얻어보자!
자, 이제 Civil 파일(Arch.mcb) 로부터 Node 정보를 받아온 후, Excel Sheet에 넣어보겠습니다.
“GET” Method로 /db/node의 정보를 요청합니다.
받아온 정보는 String 형태의 Json format으로, JsonConverter를 이용해 Dictionary Object에 할당합니다.
Dictionary 함수를 이용해 각 배열변수에 좌표 및 절점번호를 할당합니다.
할당된 배열변수를 엑셀시트에 출력합니다.
아래의 코드는 위에 설명을 기초적인 방법으로 구현한 것입니다. VBA에 경험이 많은 사용자라면 더욱 간결한 코드로 구현이 가능합니다.
MIDAS Civil 에 Data를 보내보자!
위에 예제에서 알 수 있듯이 (직접실행창 확인) 절점 데이터는 아래와 같은 구조로 작성됩니다.
{
"NODE":{
"1":{
"X":0,
"Y":0,
"Z":0
}
},
"2":{
"X":5,
"Y":0,
"Z":3.6
}
}
자 이제 절점정보가 어떤 포맷을 가지고 있는지 확인했으니, 새파일을 열고 엑셀에 입력된 절점 정보를 Civil로 보내 보겠습니다.
Sheet의 절점정보를 배열변수에 할당합니다.
배열변수를 Dictionary Object에 할당합니다.
Dctionary Object를 JsonConvert를 이용해 Json format의 String data로 변환합니다.
이를 Post Method를 이용해 Civil에 보내줍니다.
아래의 코드는 위에 설명을 구현한 것입니다.
MIDAS Civil 에 Data를 수정해보자!
자 마지막으로 새파일에 입력된 절점을 직선의 형태로 바꿔보겠습니다.
우선, Excel 데이터를 1m 간격의 직선 형태로 바꾼 후, 위에서 사용한 코드를 그대로 복사한 후 WebReqeust Fuction의 Method만 PUT으로 교체해서 작동시켜보겠습니다.
위에서 살펴본 3가지(GET,POST,PUT)는 Civil-API에서 사용이 가능한 대표적인 동작 방식이며,
이를 Excel이라는 강력한 툴을 이용하여 사용한다면, 여러분이 상상하는 것을 만들어 낼 수 있다고 생각합니다.
이제부터는 더 유용한 사용을 위해 Dictionary Object에 대해 조금 더 얘기해 보고 마치겠습니다.
Dictionary Object 톺아보기
Dictionary는 Key:Item라는 쌍의 데이터를 가지는 변수입니다.
Key와 Item를 어떤 방식으로 사용할 수 있는지 예제를 통해 알아보도록 하겠습니다.
우선 Dictionary Object에 어떤 Function이 있는지 살펴보겠습니다.
Fuction | Example |
|---|---|
item 더하기 (Key가 존재하면 안됩니다.) | dict.add Key, Value (e.g. dict.add “Apples”, 50) |
Item 수정하기 (만약, Key가 존재하지 않으면, 자동으로 더해줍니다.) | dict(Key) = Value (e.g. dict(“Oranges”) = 60) |
Key 값으로 Value 가져오기 | Value= dict(Key) (e.g. applecount = dict(“Apples”) |
Key값이 존재하는지 확인하기 (return as boolean) | dict.Exists(Key) (e.g. if dict.Exists(“Apples”) then) |
Item 지우기 | dict.Remove Key e.g. dict.Remove “Apples” |
모든 Item 지우기 | dict.RemoveAll |
Items 개수 가져오기 | dict.Count |
모든 Item을 확인하기 (for each loop) | Dim key As Variant For Each key In dict.Keys Debug.Print key, dict(key) Next key |
모든 Item을 확인하기 (for loop - early binding only) | Dim i As Long For i = 0 to dict.Count - 1 Debug.Print dict.Keys(i), dict.Items(i) Next i |
모든 Item을 확인하기 (for loop - early and late binding only) | Dim i As Long For i = 0 to dict.Count -1 Debug.Print dict.Keys()(i), dict.Items()(i) Next i |
Key 에서 대소문자를 구분하기 (Dictionary 는 비어져 있어야 합니다.) defalut입니다. | dict.CompareMode = vbBinaryCompare |
Key 에서 대소문자를 구분하지 않기 (Dictionary 는 비어져 있어야 합니다.) | dict.CompareMode = vbTextCompare |
모든 Key을 배열로 반환하기 | Dim KeyValues as Variant KeyValues = dict.Keys |
모든 Item을 배열로 반환하기 | Dim ItemValuesas Variant ItemValues= dict.Items |
Item 등록하고 출력하기
Dim dict As Dictionary
Set dict = New Dictionary
dict.Add "Apple", 50
dict.Add "Banana", 60
dict.Add "Orange", 100
Dim i As Long
For i = 0 To dict.Count - 1
Debug.Print dict.Keys(i), dict.Items(i)
Next i
Apple 50
Banana 60
Orange 100
Item 지우기
Dim dict As Dictionary
Set dict = New Dictionary
dict.Add "Apple", 50
dict.Add "Banana", 60
dict.Add "Orange", 100
dict.Remove "Banana"
Dim i As Long
For i = 0 To dict.Count - 1
Debug.Print dict.Keys(i), dict.Items(i)
Next i
Apple 50
Orange 100
특정Key의 Item값 확인하기
Key,Items 값을 모두 배열로 반환하기
Dim dict As Dictionary
Set dict = New Dictionary
dict.Add "Apple", 50
dict.Add "Banana", 60
dict.Add "Orange", 100
Dim i As Integer
Dim KeyValues, ItemValues As Variant
KeyValues = dict.Keys
ItemValues = dict.Items
For i = 0 To UBound(KeyValues)
Debug.Print KeyValues(i), ItemValues(i)
Next i
Apple 50
Banana 60
Orange 100
단계 구조로 만들기
본 컨텐츠는 개발자가 아닌, Excel VBA를 사용해본 일반 유저가 작성하였습니다.