Support > Wagby Developer Network(R7) > REST API usage guide > [Application example] Operate Wagby from Excel via REST API [2] CRUD processing
ja | en

Here is an example of manipulating Wagby from Excel via REST API. R7.5.1

Before reading this page,Operate Wagby from Excel via REST API [1] Basic formPlease look through.

Expansion of customer model

Here we prepared another customer model "customer 1".As shown in Fig. 1, date type items and file type items are added.I also add repeating containers.Because there is a file type item, when operating with the REST API, it must be treated as a multipart form.

Figure 1 Extended customer model definition

After building, start the application.Register the sample data shown in Figure 2 in advance.

Figure 2 Sample data

Start Excel

Open the Excel file that contains the program (VBA) that uses the REST API. At this time, make sure that the Wagby application is running.This is for HTTP communication from Excel.

Figure 3 Starting the sample application

Search

Firstly, press the "Search" button without specifying the search conditions.

Figure 3 Starting the sample application

One data is searched.It is displayed in the list display area.

Figure 4 List display area

update

You can change the value of any cell of the corresponding data.Here, change the phone number and press the "Update" button.The line with the cursor is the target.

Figure 5 Changing phone number

I am trying to display a confirmation dialog.When "OK" is pushed, call the REST API for change.

Figure 6 Update confirmation dialog

Try to acquire the target data from the Web again.You can see that the data is updated.

Figure 7 Data has been updated

Registration

Register new data.In this time, we prepared the customer model to use the order in the primary key.In this case, the primary key field is blank.We will set values ​​for other items.

This demo application is not supported for file uploading.Please leave the file type field blank.
Fig. 8 Enter data to be newly registered

Target line with cursor.By pressing "Register" button, a confirmation dialog will be displayed.When you click "OK", you call the REST API for registration.

Figure 9 Registration confirmation dialog

At the data registration timing, the primary key is automatically assigned.

Figure 10 Automatic numbering of primary keys

Check from the web screen.You can see that data has been added.

Figure 11 Confirmation of new registration data from web screen

Delete

It deletes data.Move the cursor to the target line and press the "Delete" button.

Figure 12 Deleting data

We will display a confirmation dialog for deletion.If you press "OK", the REST API for data deletion is called.

Figure 13 Data Delete Confirmation Dialog

download

We will introduce the function to download file type items.Move the cursor to the file you want to download and press the "download" button.

Figure 14 File download

A save dialog box is prepared.When you press the "Save" button, the file will be downloaded.

Figure 15 File Save Dialog

This sample application also supports file type items in repeating containers.

Figure 16 Downloading files in a repeating container

The sample application is written in Excel VBA.I will explain some points.

Update using multipart form

In the sample application, since the target customer model of this time contains file type items, we call the updating API in the multipart form.

Sub updateOneData()
  rownum = ActiveCell.Row
  pkey = getPrimaryKey(rownum)
  desc = getUpdateDataDescription(rownum)
  If pkey = "" Or desc = "" Then
    Exit Sub
  End If
  ok = MsgBox(desc & " を更新してよろしいですか?", vbOKCancel)
  If ok = vbCancel Then
    Exit Sub
  End If
  '--------------------------------------------------------------------
  '  変数定義
  '--------------------------------------------------------------------
  Dim strJSON As String              ' レスポンスで受け取るJSONデータ
  Dim objJSON As Object              ' レスポンスの JSON 文字列をパースした情報を格納
  Dim strURL As String               ' アクセス先URL
  Dim data As String
  data = createUpdateParameter(rownum)
  'MsgBox (data)
  
  '------------------------------------------------------------------
  ' 検索文字付きURLを作成する
  '------------------------------------------------------------------
  strURL = ModelId & "/edit/" & pkey
  Dim contentType As String
  contentType = "multipart/form-data; boundary=" & MultipartBoudary
  Dim headerkey As New Collection
  Dim headervalue As New Collection
  headerkey.Add item:="Content-Type"
  headervalue.Add item:=contentType, key:="Content-Type"
  strJSON = callRest2(strURL, "PUT", data, headerkey, headervalue)
  If strJSON = "" Then
    Exit Sub
  End If
  strJSON = replaceLongValue(strJSON)
  Set objJSON = parseJSON(strJSON)
    
  If objJSON Is Nothing Then
    Exit Sub
  End If
  If IsNull(objJSON.item("entity")) Then
    Exit Sub
  End If

  setContentsP rownum - RowStart, objJSON.item("entity"), objJSON.item("entityp")
End Sub
  • In the second line, select the target line.
  • In the 3rd and 4th lines we will get the key and value.Here is a different code for each model.[To be described later]
  • Confirmation dialog processing is carried out on line 8.
  • The 27th line specifies the multipart form.It is set as the following constant at the top of the form definition.
    Const MultipartBoudary As String = "----WebKitFormBoundary4biuCV4d9GMPOr5D"
    

Handling of long values

In the sample application, the "datetime 1" item of the customer model is assumed to be date time type.Here, the library called JSONLib used for handling JSON in Excel does not correspond to the long value (in version at the time of writing this document).

Therefore, we prepared a function called replaceLongValue in the sample application.Using regular expression libraries that can be used with VBA, numeric type items are enclosed in double quotation marks and expressed as a string representation.This circumvents the constraint of JSONLib.

Function replaceLongValue(strJSON As String) As String
    Dim items As Variant
    items = Array("datetime1")
    
    For Each itemstr In items
        Set reobj = CreateObject("VBScript.RegExp")
        With reobj
          .Pattern = "(""" & itemstr & "_"":)([0-9]*),"
          .Global = True
        End With
        strJSON = reobj.Replace(strJSON, "$1""$2"",")
    Next
    replaceLongValue = strJSON
End Function
This code is unnecessary when future restrictions of JSONLib are removed and this constraint is abolished.Or you may have the option of using other libraries (without such restrictions).Please respond according to the judgment of the developer.

You can download the complete repository, sample data, sample application (Excel file) introduced here.

In order to use JSONLib, you need to import the jsonlib class module into the target Excel. Since the Excel file that can be downloaded on this page has already been imported, we recommend you develop it by editing it.

"REST API utilization guide> logon/logoff> automatic logon> SecurityPlease read.

In the following part of VBA, you may encounter "user defined type is not defined" error.

Dim objHttpReq As MSXML2.XMLHTTP   ' XMLHTTP オブジェクト
次のように修正してお試しください。
Dim objHttpReq As Object   ' XMLHTTP オブジェクト

You can download the file that made this fix.