StreamlitとElectronで、Excelの内容をチェックするアプリを作った

はじめに

弊社では業務における一部のデータの管理にMicrosoft AccessやExcelが使われており、
VBAによりさまざまな処理が行われています。
このようなデータ管理システムはバージョン管理もされず属人化が深刻化しており、保守の面で大きな課題となっています。

今回、このデータ管理システムに新機能追加の要求が発生したので、
将来的なシステム移行に備え、既存のシステムとは独立しつつ要件を満たせるようなツールを作成しました。

概要

• StreamlitとElectronでデスクトップアプリを作成した。
• アプリからGoogle DriveやExcelファイルにアクセスできるようにした。
• 将来的なシステム移行を考慮してアプリ開発をした。

状況

• Accessで管理しているデータがある。
• 「Accessからデータを取得し、VBAで処理したのち、再度Accessに読み込ませるためのデータを作成する」Excelファイルがある。
  • いろいろ事情がありこうなったらしい。

要件

• Excelファイルで更新したデータを再度Accessに読み込ませる前に、Google Driveで他社から共有されるデータに基づき、チェックしたい。

方針

• 他社データもAccessに集約することはしない。
  • 修正が発生した際の対応工数と、保守のコストが増えるため。
• VBAでの実装はしない。
  • 保守のコストが増えるため。

上記の方針を

• 新規ツールとExcelファイル、他社データは独立させる。
• 既存のAccessとExcelファイルの作業フローはなるべく変えない。

と置き換えて対応しました。

実装

ファイル入力

チェック対象Excelファイル読み込みと表示

import streamlit as st
import pandas as pd

uploaded_excel_file = st.file_uploader("比較したいExcelファイルをアップロードしてください", type=['xlsm', 'xlsx', 'xls'])
excel_sheet_name = st.text_input('Excelのシート名を入力', 'Sheet1')

excel_cols = ['column1', 'column2', 'column3']
df_excel = pd.read_excel(
    uploaded_excel_file,
    sheet_name=excel_sheet_name,
    usecols=excel_cols,
    engine='openpyxl'
)

# 表示
st.dataframe(df_excel)

Google APIのアクセストークン

後述のstlite/desktopの環境からgoogle-auth-oauthlibによるGoogle Cloudサービスアカウント認証のための通信ができなかったので、以下の実装になりました。
credentials.jsonはGoogle Cloudサービスアカウントページでダウンロードできるものです。
今回作成したデスクトップアプリを使用するマシンと作業者は限られているのでそこまで注意しなくてもいいと判断しましたが、本来はcredentials.jsonは厳重に管理する必要があります。
時が来たらwebアプリにしてサーバー側に持たせます。

import json
import time
import jwt
import requests

def get_google_access_token(credentials_path, scope):
    # サービスアカウント情報を読み込む
    with open(credentials_path) as f:
        creds_info = json.load(f)

    token_uri = "https://oauth2.googleapis.com/token"

    # JWT(JSON Web Token)を作成
    now = int(time.time())
    payload = {
        'iss': creds_info['client_email'],
        'scope': scope,
        'aud': token_uri,
        'exp': now + 3600,  # 1時間有効
        'iat': now,
    }
    signed_jwt = jwt.encode(payload, creds_info['private_key'], algorithm='RS256')

    # JWTをGoogleに送り、アクセストークンと交換
    token_response = requests.post(
        token_uri,
        data={
            'grant_type': 'urn:ietf:params:oauth:grant-type:jwt-bearer',
            'assertion': signed_jwt,
        }
    )
    token_response.raise_for_status()  # エラーがあれば例外をスロー
    return token_response.json()['access_token']

scope = "https://www.googleapis.com/auth/drive.readonly"
access_token = get_google_access_token('./credentials.json', scope)

チェックの基準となるExcelファイル（Google Drive）読み込みと表示

import io

def download_file_from_google_drive(file_id, access_token):
    download_url = f"https://www.googleapis.com/drive/v3/files/{file_id}?alt=media"
    headers = {"Authorization": f"Bearer {access_token}"}

    response = requests.get(download_url, headers=headers)
    response.raise_for_status()  # エラーがあれば例外をスロー
    return response.content

drive_excel_url = st.text_input('Google Drive上のExcelファイルのURLまたはIDを入力')

file_id = drive_excel_url.split('/d/')[1].split('/')[0]

file_content = download_file_from_google_drive(file_id, access_token)
file_buffer = io.BytesIO(file_content)
drive_excel_cols = ['column1', 'column2', 'column3']
drive_excel_sheet_name = 'Sheet1'
df_drive_excel = pd.read_excel(
    file_buffer,
    sheet_name=drive_excel_sheet_name,
    usecols=drive_excel_cols,
    engine='openpyxl'
)

# 表示
st.dataframe(df_drive_excel)

あとはいい感じにpd.mergeして、カラム間の比較を行うだけです。

UI: Streamlit

Streamlitは、データ分析などのタスク向けに対話的なWebアプリケーションを簡単に構築できるライブラリらしいです。
今回はデータをチェックする機能だけを実装しましたが、今後データの変換処理などもアプリに移行していきたいのでちょうど良いかと思い採用しました。
https://streamlit.io/

いろんな要素を簡単に設置できます。

import streamlit as st

st.title('データチェックアプリ')

st.header('ファイルのアップロード')

st.write("これは普通の文章")

st.file_uploader("ファイルをアップロードしてください", type=['xlsm', 'xlsx', 'xls'])

st.text_input('なんかを入力', '初期値')

st.selectbox('種類を選択してください', hoge_list)

st.date_input('なんかの日', default_date)

st.button('なんかのボタン')

st.dataframe(df_hoge)

st.info('hoge')
st.success('hoge')
st.warning('hoge')
st.error('hoge')

こんな感じのUIになりました。

デプロイ？: Electron

今回作ったツールに対応する作業は重要だが頻度はそれほど高くない内容だったので、
社内向けwebアプリとしてホストするのはコスト的に少しもったいない感じでした。

探したら、Streamlitをデスクトップアプリ化できるライブラリがありました。
内部ではElectronが使われているらしいです。
https://www.npmjs.com/package/@stlite/desktop

基本的にはpackage.jsonに以下のような内容を追加して、ビルドするだけです。

{
  "stlite": {
    "desktop": {
      "files": ["app.py", "credentials.json", "lib/*"],
      "entrypoint": "app.py",
      "dependencies": ["pandas", "hoge", "fuga"]
    }
  }
}

# build用ファイル出力
npm run dump

# build
npm run app:dist -- --win --x64

これでdistディレクトリにインストーラが作成されます。

システム移行について

今回、AccessとExcelからなるシステムに新機能を追加するのではなく、独立したツールに機能を持たせました。
作成したデスクトップアプリはファイルの内容をチェックするだけなので、従来の作業フローに影響を与えずに使うことができます。

今後新機能はアプリに追加したり、既存のVBAの処理内容をアプリに移行したりしていくことで、
Accessを脱却していこうと考えています。

考え方はストラングラーフィグパターンに近いのかなと思います。
https://learn.microsoft.com/ja-jp/azure/architecture/patterns/strangler-fig

ただし、現状はシステムとして統合的なUIがなく、作業者がAccessやExcelからVBAを実行している状態です。
ファサードは設置しづらく、かつ運用作業自体は止められない、そして改善対象範囲がそこそこ広い💀
ので作業手順が急激に変わらないようにする配慮も必要になりそうです。

現実的には、

• データの読み込み・更新の処理を集約する
• 作業に使用するAccess、Excelの切り替えを減らす

のような観点で現状の作業フローを整理しながら部分的にアプリに移行していくことになりそうです。

おわりに

当初の要求は「ファイルの内容を簡単にチェックできるようにしたい」だけでしたが、
その作業に関わる周辺のシステムを見直すことで、今まで気づかなかった （目を逸らしていた） 課題とその改善案について考えることができました。
システム全体の移行となると、より正確に作業フローを理解する必要がありますが地道にやっていきたいと思います。