Zenn
🍊

Google Colab から Pleasanter に接続しようとしたらできなかったので JupyterNotebook でなんとかした

2022/12/04に公開
Excel
Python
jupyter
Pleasanter
tech

2022 個人アドベントカレンダー の記事です。

課題感

  • Excel 帳票やめよう
    • DX とかってバズワードはいいから業務をクラウドネイティブに改めるんだ
  • とはいっても Excel 出したいらしい
  • それできるよ Python ならね
    • Colab でやれば環境お手軽では

試したこと

  • Google Colab から demo.pleasanter.org に繋げなかった
  • Jupyter Notebook をコンテナで起動して Excel 帳票出力

Colab

  • 証明書のエラー

普通に requests.post すると ↓ なる

SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1131)

ので

import requests
import json
import urllib3

urllib3.disable_warnings()

request_param = {
    "ApiVersion": 1.1,
    "ApiKey": apikey
}
response = requests.post(
    f'https://demo.pleasanter.org/api/items/123123/get', json=request_param)
print(response.text)

とすると

<html>
<head><title>403 Forbidden</title></head>
<body>
<center><h1>403 Forbidden</h1></center>
<hr><center>Microsoft-Azure-Application-Gateway/v2</center>
</body>
</html>

となり Azure の WAF で遮断されていた…
(リクエスト方法が間違っているのかと、別途ローカルから実施したりして時間を溶かした)

  • 補足
    Local の Jupyter からアクセスしても同じく 403 となった。
    Local なので pip install --upgrade certifi pip install --upgrade requests したが SSL エラー回避できず。
    モヤるが、ここを掘り下げる余裕ないので調査せず。

Jupyter Notebook

環境構築

docker-compose.yaml
version: "3"
services:
  jupyter:
    image: jupyter/minimal-notebook
    user: root
    ports:
      - '8888:8888'
    environment:
      - GRANT_SUDO=yes
      - TZ=Asia/Tokyo
    volumes:
      - ./work:/home/jovyan/work

mkdir work
finch compose up

→ トークン付きの URL を開けばログインできる。

参考

docker-compose.yml レベルでコンテナを分離している場合、compose 外で作成したネットワークに参加させてあげるとよい。このとき、localhost/127.0.0.1 ではなく、コンテナ名でのアクセスになることに注意する。

finch network create share-network

notebook

コード

  • ブロックを 2 つにわけています。ヘルパと設定をわけるため
  • この例では
    • work/Book1.xlsx というブックの sheet1 のシートから
    • {}で囲まれた値の入ったセルを探して
    • データ取得した内容にその名前の項目があれば
  • という処理をしています
import requests

def get_single_record(apikey, id):
    request_param = {
        "ApiVersion": 1.1,
        "ApiKey": apikey,
        "View": {
            "ApiDataType": 'KeyValues'
        }
    }
    response = requests.post(
        f'http://pleasanter-web:5000/api/items/{id}/get', json=request_param, verify=False)
    return response.json()["Response"]["Data"][0]

def is_placeHolder(str):
    return False if str is None else str.startswith("{") and str.endswith("}")


def cell_to_dic(cell):
    return {cell.value[1:-1]: cell.coordinate}


def cells_to_fill(sheet):
    dic = {}
    for row in sheet.iter_rows():
        for cell in row:
            if not (is_placeHolder(cell.value)):
                continue
            dic.update(cell_to_dic(cell))
    return dic

def cells_filled_in(template, data):
    dic = {}
    for k,v in template.items():
        data_value = data.get(k)
        if data_value is None:
            continue
        dic[v] = data_value
    return dic

def write_sheet(sheet,dic):
    for k,v in dic.items():
        sheet[k] = v

import requests
import openpyxl

book_name = './work/Book1.xlsx'
sheet_name = 'Sheet1'
id = 51
apikey = "acb8e29f5c98eff25883989a71a1b1cce24b4e92722ca6c7dde0303266a436941baa36aa1a1d3e28c8615b72192217a75a13bc0ea3d383290db02e72e798bba8"

workbook = openpyxl.load_workbook(book_name)
sheet = workbook[sheet_name]
template_dic = cells_to_fill(sheet)
data = get_single_record(apikey, id)
assigned_dic = cells_filled_in(template_dic,data)
new_sheet = workbook.copy_worksheet(sheet)
new_sheet.title = "コピー"
write_sheet(new_sheet, assigned_dic)
workbook.save(book_name)
workbook.close()

おわりに

  • 環境に苦労して、ユースケースを考えられていない。
  • 実用的な書類にするには、実際の帳票に合わせた調整が必要
    • 書式や罫線、埋められるデータ長によって表示が左右される
    • データ出力用のシートを作って、そこからセル参照して表示ページを作る、が穏当な解決方法かも。
      • データ出力シートで A 列にだけデータを埋めればよい、であれば処理速度も早いかも。
    • こういう手当をしてまで帳票を作らないといけないかは再度検討すべき。
    • → 見せる画面を Web で作るメリット
      • 権限が制御できる
      • 最新ファイルを追わなくていい
      • 共同で編集した内容がその場その場で共有できる
GitHubで編集を提案

Discussion