【Pythonで業務効率化】pythonで面倒な明細書を自動作成！Excel VBA,Googleスプレッドシートと連帯
こんにちは。今回は事務作業を効率化するツールをpythonを使って作成したので、自分のメモ用兼同じことにチャレンジしている方々に向けコードを共有したいと思います！
今回対象とした事務作業は簡単に言うと、必要となる部品をユーザーさんに届けるために行う郵送作業の準備です。 ユーザー情報、及び部品情報の明細書をGoogleドキュメントに手打ちしてから印刷するというような作業を簡略化したいと思いツールを開発しました。
一つ一つの作業は単純です。ただ、簡単な作業ほど量が増え、繰り返し作業が増すとヒューマンエラーも増えてくるのです。共感してくれる方がいれば嬉しいですね。
1. 作成するツールの概要
今回作成するツールは、オンラインで情報を共有できるスプレッドシートに入力されたユーザー情報、部品情報をエクセルのテンプレートファイルを参照して、ユーザー情報ごとに明細書を自動でエクセルファイルに入力していくものです。オプション機能としてスプレッドシートに入力されたコメントをターミナルに表示させたり、ユーザー情報を後で一気に印刷できるようスプレッドシートの別シートに表示させる機能も付けます。
複数人で作業するという点でスプレッドシートを採用し、個人個人の情報にまとめるためにエクセルファイルを使用することにしました。
2. 環境
Excelのバージョン:16.44(20121301)
macOS Catalina バージョン:10.15.7
Python: Python 3.7.6
今回使用したバージョンです。macのExcelをpythonで自動化したのですが、これが少々厄介でした。ファイルにアクセス権を付与したり、システム環境設定で「セキュリティとプライバシー」=>「オートメーション」でターミナルの「Microsoft Excel」にチェックを付けてターミナルからexcelを制御できるようにしておく必要があるので、注意が必要です。また、excelのバージョンによってはmac上で動作しないものもあるようなので、上手くいかない場合はアップデートを試してみてください。上記環境上では動作確認をしました。
3. 参考サイト
スプレッドシートをpythonで使えるようにするための情報
pythonを使ってエクセルで明細書を作成する情報
上記サイトを参考にスプレッドシート、excelをmac環境上でpythonを使って自動化するツールを作成しました。pythonほぼ初心者の私でも作成することができましたので、自分のコードよりも上記サイトをお勧めしましす。ただ、excelとスプレッドシートを混ぜたコードは中々目に留まらないと思うので、下記のコードを参考いただければ幸いです。
4. 作成したコード
import gspread
import json
import datetime as dt
from datetime import datetime, timedelta
import xlwings as xw
import pandas as pd
import os
#ServiceAccountCredentials：Googleの各サービスへアクセスできるservice変数を生成します。
from oauth2client.service_account import ServiceAccountCredentials
#2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
#認証情報設定
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定（秘密鍵、Pythonファイルから読み込みしやすい位置に置く）
credentials = ServiceAccountCredentials.from_json_keyfile_name('ダウンロードしたJSONファイル名', scope)
#OAuth2の資格情報を使用してGoogle APIにログインします。
gc = gspread.authorize(credentials)
#共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。
SPREADSHEET_KEY = 'スプレッドシートキー'
#共有設定したスプレッドシートのresourceを開く
templatepath = 'template.xlsx'
resourcepath = gc.open_by_key(SPREADSHEET_KEY).worksheet('resource')
commentpath = gc.open_by_key(SPREADSHEET_KEY).worksheet('comment')
addresspath = gc.open_by_key(SPREADSHEET_KEY).worksheet('address')
###ここから明細書自動作成プログラム###
class Parts:
def __init__(self, name, cost):
self.name = name
self.cost = cost
tape1 = Parts(“〇〇テープ",700)
sensor = Parts("〇〇センサー",600)
cover = Parts("〇〇カバー”,1000)
Parts_info = [tape1,sensor,cover,]
#シートの読み込み
df = resourcepath.get_all_values()
#エクセルを新しいインスタンスで作成
App = xw.App()
collen = len(df[0])
rowlen = len(df)
count1 = 0
count2 = 0
count3 = 0
for row in df[5:]:
sum = 0
for col in range(collen):
wb = App.books.open(templatepath)
ws = wb.sheets('テンプレート')
startdate = datetime(int(df[2][0]), int(df[2][1]), int(df[2][2]))
enddate = datetime(int(df[2][4]), int(df[2][5]), int(df[2][6]))
dtst = row[1]
date_dt = dt.datetime.strptime(dtst,'%Y/%m/%d')
tax = 0.1
insertline = 14
if col == 0:
ws.cells(insertline + 7, 5).value = row[0]
elif col == 2:
ws.cells(6,1).value = row[col]
elif col == 6:
number6 = row[col]
ws.cells(insertline, 2).value = row[col]
elif col == 7:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline, 1).value = row[col]
ws.cells(insertline, 3).value = member.cost*int(number6)
ws.cells(insertline, 4).value = member.cost*tax*int(number6)
ws.cells(insertline, 5).value = member.cost*int(number6) + member.cost*tax*int(number6)
sum += member.cost*int(number6) + member.cost*tax*int(number6)
elif col == 8:
number8 = row[col]
ws.cells(insertline+1, 2).value = row[col]
elif col == 9:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline+1, 1).value = row[col]
ws.cells(insertline+1, 3).value = member.cost*int(number8)
ws.cells(insertline+1, 4).value = member.cost*tax*int(number8)
ws.cells(insertline+1, 5).value = member.cost*int(number8) + member.cost*tax*int(number8)
sum += member.cost*int(number8) + member.cost*tax*int(number8)
elif col == 10:
number10 = row[col]
ws.cells(insertline+2, 2).value = row[col]
elif col == 11:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline+2, 1).value = row[col]
ws.cells(insertline+2, 3).value = member.cost*int(number10)
ws.cells(insertline+2, 4).value = member.cost*tax*int(number10)
ws.cells(insertline+2, 5).value = member.cost*int(number10) + member.cost*tax*int(number10)
sum += member.cost*int(number10) + member.cost*tax*int(number10)
elif col == 12:
number12 = row[col]
ws.cells(insertline+3, 2).value = row[col]
elif col == 13:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline+3, 1).value = row[col]
ws.cells(insertline+3, 3).value = member.cost*int(number12)
ws.cells(insertline+3, 4).value = member.cost*tax*int(number12)
ws.cells(insertline+3, 5).value = member.cost*int(number12) + member.cost*tax*int(number12)
sum += member.cost*int(number12) + member.cost*tax*int(number12)
elif col == 14:
number14 = row[col]
ws.cells(insertline+4, 2).value = row[col]
elif col == 15:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline+4, 1).value = row[col]
ws.cells(insertline+4, 3).value = member.cost*int(number14)
ws.cells(insertline+4, 4).value = member.cost*tax*int(number14)
ws.cells(insertline+4, 5).value = member.cost*int(number14) + member.cost*tax*int(number14)
sum += member.cost*int(number14) + member.cost*tax*int(number14)
elif col == 16:
number16 = row[col]
ws.cells(insertline+5, 2).value = row[col]
elif col == 17:
for member in Parts_info:
if member.name == row[col]:
ws.cells(insertline+5, 1).value = row[col]
ws.cells(insertline+5, 3).value = member.cost*int(number16)
ws.cells(insertline+5, 4).value = member.cost*tax*int(number16)
ws.cells(insertline+5, 5).value = member.cost*int(number16) + member.cost*tax*int(number16)
sum += member.cost*int(number16) + member.cost*tax*int(number16)
if startdate <=date_dt<=enddate:
ws.cells(insertline+6, 5).value = '合計 '+str(int(sum))+'円'
ws.range('A6').value = row[2] + ' 様'
now = datetime.now()
inserteddate = now.strftime('%Y-%m-%d')
ws.range('F4').value = inserteddate
ws.name = row[2]
filename = inserteddate + '_' + row[2] + '.xlsx'
sum=0
wb.save(filename)
comment_list = commentpath.col_values(1)
print('<——ドキュメントが生成されました！---->')
for comment in comment_list[1:]:
print(comment)
wb.close()
App.quit()
for x,row in enumerate(df[5:]):
for col in range(collen):
if col == 3:
addresspath.update_cell(x + count1*4+1,1,row[col])
count1 = count1 + 1
elif col == 4:
addresspath.update_cell(x + count2*4+2,1,row[col])
count2 = count2 + 1
elif col == 5:
addresspath.update_cell(x + count3*4+3,1,row[col])
count3 = count3 + 1numberの箇所はスプレッドシートで入力される部品の個数を表しており、最後のfor文はおまけ機能のスプレッドシート(ソースとは別のシート)にユーザー情報を3行ごとにスペースを空けて、出力するプログラムです。細かな文法は粗いとは思いますが、そこはご容赦下さい。参考サイトと少々異なりますが、ws.rangeではなくws.cellsにした理由はExcel VBAの文法に則りきちんと入力されるようにしたためです。また、if文ではエクセルに商品の原価、税、定価＊個数が出力されるようにしています。
5. エラーが出たら
上記コードを作成するにあたり、かなり試行錯誤を繰り返しました。環境によっては動作しない場合もあるかもしれません。その場合はexcelのバージョンを確認して、アップデートをするかファイルのアクセス権を調べる。また、コードのfor分が正しくパラメータ値を処理できているかなどを確認してみて下さい。ほとんどのエラーはこれらで解決しました。ですが、python特有のタブ、スペースエラーには悩まされますね...。
この記事が気に入ったらサポートをしてみませんか？