概要
Excel ファイルのヘッダ行を基にしてテーブルを自動生成するバッチ処理を作ってみました。
テーブルの管理－エディタでの設定は・・・
プリザンターを使う第一歩としてテーブルを作りますが、「テーブルの管理」－「エディタ」タブにて編集画面に表示する項目を1つ1つ設定していきます。入力項目が少ない簡単なテーブルであれば数十分でテーブルを作ることができますが、画面項目数が多くなれば設定作業にも時間がかかります。プリザンターの利用を検討されている方で「既存のExcelファイル管理をプリザンターに置き換えたいけどExcelのファイル数、シート数が多く、また項目数も多いので1つ1つ項目を設定するのが手間。Excelファイルからテーブルを簡単に作成できないか」という要望がある方は少なからずいらっしゃると思います。
サイト作成 API を使えばいける！
2022 年（もう 2 年前！）に追加されたサイト作成 API を使えば、最低限の情報だけあればテーブルが作れます。
自動化の方針です。
- 既存の Excel ファイルのヘッダ行の各列の名称を表示名とし、**適切な項目（分類、数値など）**を割り当てる
- 設定はエディタおよび一覧とする。編集画面上の並び順はExcelファイルの並び順とし、一覧はExcelヘッダ行全項目と更新者、更新日時を表示する設定とする
Excelファイルから名称を取得して、サイト作成APIにPOSTするjsonファイルを作り、API実行すれば自動化できそうです！
「適切な項目」ってどうしよう？
自動化できそうですが、方針にある「適切な項目」への割り当てが曲者です。プリザンターのテーブルは「分類、数値、日付、説明、チェック、添付ファイル」の6項目の他に期限付きテーブルおよび記録テーブルの基本項目（タイトル、内容等）があります。
構想初期のころは各列のセルの書式をみて分類／数値／日付の3つの項目のどれかに自動割り振りしようと思いましたが、タイトル、内容、状況や期限付きテーブルなら開始、完了などの項目は自動判別が難しく主体的に決める必要があると判断しました。同様に期限付きテーブルにするのか記録テーブルにするのかもExcelファイルを見ただけでは難しいと判断しました。
今回はとりあえず作ってみることを最優先としたので、割り当てる項目を Excelファイルに追記することとし、テーブルの種類はExcelファイル以外の別の方法で指定することにしました。
ということで作ってみました。
先に挙げた内容を踏まえ、以下のようなバッチ処理として作ってみました。
- Excelファイルの1行目のヘッダ行を表示名として使用する
- Excelファイルの2行目に「割り当てる項目」としてプリザンター上の項目を追記する
- テーブル名はExcelファイル名とする
- テーブルの種類とテーブルを作成する場所（親サイト）はバッチ処理の引数で渡す
まずは Excel ファイルの説明です。例として以下のような Excel で管理している内容をプリザンターに置き換えるケースを想定します。
2行目に割り当てる項目を記入します。記入する内容は以下のいずれかとします。
- タイトル、内容、開始、完了、作業量、進捗率、残作業量、状況、管理者、担当者、コメント、分類、数値、日付、説明、チェック、添付ファイル
今回気を付けた点は「追記は最低限にとどめたい」という点です。各列に対して「これは分類A」「こっちは数値C」と設定できれば良いですが、それでは結果として設計していることに近くなり簡単さが損なわれるので「分類なのか数値なのか」くらいの入力にしました。
次にバッチ処理の説明です。今回はpythonでプログラミングしました。バッチ処理は以下のように実行します。
c:\work > python sitecreate.py 商談管理.xlsx 205978 Issues
引数は1番目にExcelファイル名（ファイルパス）、2番目が親サイトID、3番目がテーブルの種類です。上記の例では商談管理.xlsx の内容をサイト ID205978 のフォルダの配下に期限付きテーブルとして作成するという指示になります。
バッチ処理の中身です。今回は動かしてみることを最優先としたので、細かいチェック処理やエラー処理などは実装していません。
import openpyxl
import os
import requests
import sys
# Excel読み込み
def readExcel():
columnName = {
'タイトル': 'Title',
'内容': 'Body',
'開始': 'StartTime',
'完了': 'CompletionTime',
'作業量': 'WorkValue',
'進捗率': 'ProgressRate',
'残作業量': 'RemainingWorkValue',
'状況': 'Status',
'管理者': 'Manager',
'担当者': 'Owner',
'コメント': 'Comments',
'分類':'Class',
'数値':'Num',
'日付':'Date',
'説明':'Description',
'チェック':'Check',
'添付ファイル':'Attachments'
}
settingColumns = ['Class', 'Num', 'Date', 'Description', 'Check', 'Attachments']
suffix = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
# 1行目、2行目を読み込み
book = openpyxl.load_workbook(excelFile)
sheet = book.active
for col in sheet.iter_cols(min_row=1, min_col=1, max_row=2, max_col=sheet.max_column):
column = []
for cell in col:
column.append(cell.value)
columns[column[0]] = column[1]
# カラム名置換
for k,v in columns.items():
if v in columnName:
columns[k] = columnName[v]
for s in settingColumns:
i = 0;
for k,v in columns.items():
if v == s:
columns[k] = s + suffix[i]
i+=1
## SiteSettings作成
def createSs():
editorColumnHash = {
'General': list(columns.values())
}
gridColumns = list(columns.values())
gridColumns.append('Updator')
gridColumns.append('UpdatedTime')
columns_array = []
for k,v in columns.items():
columns_array.append({
'ColumnName': v,
'LabelText': k
})
siteSettings = {
'Version': 1.017,
'ReferenceType': referenceType,
'GridColumns': gridColumns,
'EditorColumnHash': editorColumnHash,
'Columns': columns_array
}
return siteSettings
# サイト作成処理
def createSite():
url = f'http://localhost/api/items/{siteId}/createsite'
apiKey = 'abc...'
title = os.path.splitext(os.path.basename(excelFile))[0]
data = {
'ApiVersion': 1.1,
'ApiKey': apiKey,
'Title': title,
'ReferenceType': referenceType,
'SiteSettings': createSs()
}
response = requests.post(url, json=data)
resJson = response.json()
if response.status_code == 200 :
print(resJson['Message'])
else :
print('Error')
# メイン処理
if __name__ == '__main__':
args = sys.argv # 引数1：Excelファイル名、引数2：親サイトID、引数3：テーブル種類
if len(args) != 4:
print('引数が足りません')
if not args[2].isdigit():
print('第二引数は数値を入力してください')
else:
excelFile = args[1]
siteId = args[2]
referenceType = args[3]
columns = {}
readExcel()
createSite()
Excelファイルの読み込みはopenpyxlを使用しました。Excelファイルの1,2行目を読み込み、2行目に設定した項目名をプリザンターのカラム名に置換します。分類～添付ファイルにおいてはカラム名に置換と合わせてA～Zの項目を割り当てます。
1 行目の表示名と2行目を基にしたカラム名の情報を用いてAPIにPOSTするjsonパラメータを生成します。
実行結果
2行目に項目名を追加したExcelファイルを用いてバッチを実行すると指定したサイトにテーブルが作成されます。
テーブルを開くと一覧画面の表示項目、編集画面の項目がExcelファイルで設定した通りに作成されたことがわかります。
この後、2行目を追加する前のExcelファイルをCSV形式に変換してインポートをすることで、Excelファイルからプリザンターへの置き換えが完了します。
今後の課題・展開
今回はExcelファイルの内容をプリザンターのテーブルに置き換えるためのとりあえずの最短手順としてプログラミングしてみました。エディタでの項目配置までを自動化することで、その後の細かい設定（回り込みの有無や選択肢等々）に注力することができるので多少なりとも省力化できるかと思います。
また自動化をテーブル作成までとしデータ登録はインポート機能を利用することとしたため、作業が2回に分かれてしまいました。openpyxlでExcelファイルの読み込みができましたので、テーブルの作成とデータ取り込みを一回の処理で実現できそうです。
回り込みの有無や選択肢の設定、表示形式などの細かい設定まで自動化できると良いのですが、そのためにはそれらの設定内容もExcelファイルに記載しておく必要があります。ですが、逆に考えると細かい設定内容まで記載しておけば＝設計書として設定内容を書いておけば、サイト作成APIによるテーブル作成の自動化ができるということにもなります。
まとめ
既に管理しているExcelファイルをなるべく手間をかけずにプリザンターに置き換えることを想定して、プリザンターのサイト作成 API を利用した自動化プログラムを作成してみました。やや割り切った点もありますが、第一歩としては十分満足できる結果になりました。
プリザンターにはサイト作成 API の他にも API 機能が各種ありますので、Excel のみならず他システムとの連携を実現できます。
引き続きプリザンターをよろしくお願いします！