ExcelからINSERT文を生成するアドインを作ってみた

# はじめに
試験工程などで、データ準備のために大量のINSERT文を作成する必要があるかと思います。
そんな時に、INSERT文を簡単に作成できると便利だと思い、ExcelのデータからINSERT文を生成できる独自の関数を作成してみました。

# 作ったもの概要
* VBAでINSERT文を生成する関数
* 作成した関数をアドイン化し、Excel上で`=作成した関数名（引数）`の形式で実行できるようにする
* DBMSはpostgresqlに対応
* 値に関してはデータ型に応じて、`'(シングルクォーテーション)`をつけるなり、適切にキャストさせる

# 使い方
セル上に`=PG_CREATEINSERT`と記載し、引数に以下を指定します。
```:引数指定例
 =PG_CREATEINSERT（空白を無視するかどうか(true or false), テーブル名, カラム名, 値, データ型）
 =PG_CREATEINSERT（空白を無視するかどうか(true or false), テーブル名, カラム名, 値）
```

## 実行例
作成した`PG_CREATEINSERT`関数の実行例は以下です。

![1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/292212/389b9078-231f-73db-54a7-b22aa3141f45.png)


## 実行結果
![2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/292212/c1a85cf3-993c-f21a-b41c-d2cd662f04a3.png)

実行結果を見てもわかる通り、セルの値からINSERT文が作成できていることを確認できました。
値に関しては、指定したデータ型に応じて、出力形式が以下の通りになります。
* **文字型　→　セルの値をシングルクォーテーションで囲い出力**
* **bytea型　→　`'\x` + `セルの値` + `'::bytea`の形式で出力**
* **上記以外　→　セルの値をそのまま出力**


# ソースコード
```vb:pg_createInsert.xlam

'*********************************************************
'[概要]
' 指定したセルの値をもとに、postgresqlに対応したINSERT文を出力します
'
'[使い方]
' =PG_CREATEINSERT（空白を無視するかどうか, テーブル名, カラム名, 値, データ型）
' =PG_CREATEINSERT（空白を無視するかどうか, テーブル名, カラム名, 値）
'
'[引数]
' Ignore：空白を無視するかどうか
' TableName：テーブル名
' par()：可変長引数（カラム名, 値, データ型の順番で指定される想定）
'*********************************************************
Function PG_CREATEINSERT(Ignore As Boolean, TableName As String, ParamArray par() As Variant)
   
    Dim rowVal As String: rowVal = ""
    Dim setVal As String: setVal = ""
    Dim colmnName As String: colmnName = ""
    Dim SetValues As String: SetValues = ""
   
    Dim colCount As Integer: colCount = 0
    Dim valCount As Integer: valCount = 0
    Dim parCount As Integer: parCount = UBound(par) + 1 '可変長引数の数
   
    Dim colmnArr() As String
    Dim typeArr() As String
    Dim valueArr() As String
   
    Const Delim As String = ","
    PG_CREATEINSERT = "" '戻り値

   
    '引数チェック
    If parCount <= 1 Or parCount >= 4 Then
        PG_CREATEINSERT = CVErr(xIErrNum)
        Exit Function
    End If
   
    'カラム名設定
    colmnArr() = setParam(par(0))
    '値設定
    valueArr() = setParam(par(1))
    'データ型設定
    If parCount = 3 Then
        typeArr() = setParam(par(2))
    End If
   
    'INSERT文作成
    'カラム名
    For i = LBound(colmnArr) To UBound(colmnArr)
        If colmnArr(i) <> "" Or Ignore = False Then
            colmnName = colmnName & Delim & colmnArr(i)
            colCount = colCount + 1
        End If
    Next
   
    '値
    For i = LBound(valueArr) To UBound(valueArr)
        If valueArr(i) <> "" Or Ignore = False Then
            rowVal = Replace(valueArr(i), """", "")
            If Not (LCase(rowVal) = "null" Or rowVal = "") Then
                If parCount = 3 Then
                    'データ型が存在する場合、データ型に合わせて値を設定する
                    If typeArr(i) Like "*char*" Or LCase(typeArr(i)) = "text" Then
                        '文字列型
                        setVal = "'" & rowVal & "'"
                    ElseIf LCase(typeArr(i)) = "bytea" Then
                        'bytea型
                        setVal = "'\x" & rowVal & "'" & "::bytea"
                    Else
                        '上記以外（数値型など）
                        setVal = rowVal
                    End If
                Else
                    setVal = "'" & rowVal & "'"
                End If
            Else
                setVal = "NULL"
            End If
            SetValues = SetValues & Delim & setVal
            valCount = valCount + 1
        End If
    Next
   
    If colCount <> valCount Then
        PG_CREATEINSERT = CVErr(xIErrNum)
        Exit Function
    End If
   
    'INSERT文生成
    PG_CREATEINSERT = "INSERT INTO " & TableName _
                    & "(" & Mid(colmnName, Len(Delim) + 1) & ")" _
                    & " VALUES " _
                    & "(" & Mid(SetValues, Len(Delim) + 1) & ")" & ";"

End Function

'*********************************************************
'[概要]
' 可変長引数の各要素を配列に設定して返却する関数
'*********************************************************
Function setParam(paramRange As Variant)
    Dim rtnArr() As String
    If TypeName(paramRange) = "Range" Then
        ReDim rtnArr(paramRange.Columns.Count - 1)
        For i = 0 To UBound(rtnArr)
            rtnArr(i) = paramRange(1, i + 1).Value
        Next
        setParam = rtnArr()
    Else
        ReDim rtnArr(0)
        rtnArr(0) = CStr(paramRange)
        setParam = rtnArr()
    End If

End Function
```

# アドイン設定方法
Excel上で今回作成した関数を`=作成した関数名（引数）`の形式で利用するためには、
アドインを作成して登録する必要があります。

手順に関しては以下のサイトが分かりやすかったため、参考として載せておきます。

https://hirachin.com/post-7298/