徒然日記

徒然なるままに書いていきます 固めのものからゆるい日常まで書きたいものを

研究室入退室管理システム Excel編

前回このような記事を作成しました。
cobaltic.hatenablog.com

…が結局API取得しなくちゃならないのは非常に面倒ですし、まとまって見ることが出来ません。
なによりも研究室に行かず家でダラダラしていると友人から「早くlabo inしろや」 というメッセージが届くようになって限界を感じました。

というわけで今回はExcelファイルに入退室記録を書き込みようなものを作成したいと思います。これで家でダラダラしていても煽られることはないはずです。

完成イメージはこんな感じです。
f:id:cobaltic:20190813183721p:plain

目次

プログラム

labo in

labo in の方のコードは以下のようなものです。下準備としてやることはlabo_manage.xlsxを作成するだけです。

import openpyxl
import datetime 
import os
from openpyxl.styles import numbers
from openpyxl.styles.fonts import Font
from openpyxl.styles import borders
os.startfile('C:\\***\\***\\labo_manage.xlsx')
  
d = datetime.datetime.today()
youbi = ["月","火","水","木","金","土","日"]
#d.weekday()では数字が吐き出されるのでそれに対応した曜日の配列を用意しておく
wb =openpyxl.load_workbook('labo_manage.xlsx')
sheet = wb.active
#初期設定-
if  sheet.cell(column = 1, row = 1 ). value == None :
    sheet.cell(column = 1, row = 1 ). value = '日付'
    sheet.cell(column = 2, row = 1 ). value = '曜日'
    sheet.cell(column = 3, row = 1 ). value = 'laboin'
    sheet.cell(column = 4, row = 1 ). value = 'laboout'
    sheet.cell(column = 5, row = 1 ). value = 'ラボ滞在時間'
    sheet.cell(column = 7, row = 1 ). value = '経過日数'
    sheet.cell(column = 7, row = 2 ). value = '滞在日数'
    sheet.cell(column = 7, row = 3 ). value = '総滞在時間'
    sheet.cell(column = 7, row = 4 ). value = '平均滞在時間(経過日数)'
    sheet.cell(column = 7, row = 5 ). value = '平均滞在時間(滞在日数)'
       
    for i in range (1,5):
        font = Font(b = True, sz = 12)
        sheet.cell(column = i, row = 1).font = font
        sheet.cell(column = 7, row = i).font = font
        sheet.cell(column = i ,row = 1).border = borders.Border(bottom=borders.Side(style=borders.BORDER_THICK, color='000000'))
    
    sheet.column_dimensions['A'].width = 13
    sheet.column_dimensions['C'].width = 11
    sheet.column_dimensions['D'].width = 11
    sheet.column_dimensions['E'].width = 13
    sheet.column_dimensions['G'].width = 22
    
    
    sheet.cell(column = 8, row = 1 ).number_format = numbers.FORMAT_GENERAL 
    sheet.cell(column = 8, row = 2 ).number_format = numbers.FORMAT_GENERAL 
    sheet.cell(column = 8, row = 3 ).number_format = numbers.FORMAT_DATE_TIME6
    sheet.cell(column = 8, row = 4 ).number_format = numbers.FORMAT_DATE_TIME6
    sheet.cell(column = 8, row = 5 ).number_format = numbers.FORMAT_DATE_TIME6

#-初期設定

for j in range(2,1000):
    if  sheet.cell(column = 1, row = j ).value == None :
            sheet.cell(column = 1, row = j ). value = '%s-%s-%s' % (d.year, d.month, d.day)
            sheet.cell(column = 2, row = j ). value = '%s曜日' % (youbi[d.weekday()])
            sheet.cell(column = 3, row = j ). value = '%s:%s:%s'  %(d.hour, d.minute, d.second)
            
            #それぞれのセルの書式設定をいじる
            sheet.cell(column = 1, row = j ).number_format = numbers.FORMAT_DATE_YYMMDD
            sheet.cell(column = 3, row = j ).number_format = numbers.FORMAT_DATE_TIME6
            sheet.cell(column = 4, row = j ).number_format = numbers.FORMAT_DATE_TIME6
            sheet.cell(column = 5, row = j ).number_format = numbers.FORMAT_DATE_TIME6
            
            
            print('%s時%s分%s秒%s曜日\n' % (d.hour, d.minute, d.second, youbi[d.weekday()]) + 'C' + str(j) + 'のセルに入力しました。')
            break   
wb.save('labo_manage.xlsx')

動きを簡単に説明すると#初期設定-から#-初期設定の部分ではコメントの通り初期設定をしています。
A1のセルが空白なら初期設定(セルの書式設定やフォントなど)をやってくれます。
f:id:cobaltic:20190813183850p:plain
これが
f:id:cobaltic:20190813183857p:plain
こう

labo out

import openpyxl
import datetime 
from openpyxl.styles import numbers
import os
os.startfile('C:\\***\\***\\labo_manage.xlsx')

   
d = datetime.datetime.today()
youbi = ["月","火","水","木","金","土","日"]
#d.weekday()では数字が吐き出されるのでそれに対応した曜日の配列を用意しておく

wb =openpyxl.load_workbook('labo_manage.xlsx')
sheet = wb.active
 

for i in range(2,1000):
    if sheet.cell(column = 3, row = i ).value == None :
            sheet.cell(column = 4, row = i-1 ). value = '%s:%s:%s'  %(d.hour, d.minute, d.second)
            sheet.cell(column = 5, row = i-1 ). value = '=D' + str(i-1) + '-C' + str(i-1) 
            sheet.cell(column = 8, row = 1). value = '=A' + str(i-1) + '-A2+1' 
            sheet.cell(column = 8, row = 2). value = '=SUMPRODUCT(1/COUNTIF(A2,A' +str(i-1) +'))'
            sheet.cell(column = 8, row = 3). value = '=SUM(E2:E' + str(i-1) +')'
            sheet.cell(column = 8, row = 4). value = '=H3/H1' 
            sheet.cell(column = 8, row = 5). value = '=H3/H2'        
            
            print('%s時%s分%s秒%s曜日\n' % (d.hour, d.minute, d.second, youbi[d.weekday()]) + 'D' + str(i) + 'のセルに入力しました。')
            break
                     
wb.save('labo_manage.xlsx')


labo in側の列で入力がない場所の右上にlabo outの時間を書き込みます。
計算式もゴリ押しで代入していきます。計算式については最後の「参考にしたもの」の部分を参照してください。
問題点としては日付が変わるとうまくいきません。24時を越えての入退室はないと信じて。
…とはいえ一応対策できないことはなくて、日付も入力した上でセルの書式設定を後からhh:mm:ssなどにすれば、表示されるのは時刻だけだけど日付もちゃんと入っているので計算は出来ます。ただ書式設定を入力とは別で完全に後からいじらねばならず、使用頻度に対して手間がかかるだけだと思ったので見送りました。

実装

やることは前回記事と全く変わらないので完全にコピペしています。そのためファイル名が若干違いますがご容赦を。
細かいステップを説明すると
1. laboin.pyを発動するようなbatファイルの作成
2. laboin.batをグループポリシーでログオンスクリプトとして登録
3. laboin.pyをログオンディレクトリに置いてくる
になります。

1. laboin.pyを発動するようなbatファイルの作成
これの3番目の方法を参考にbatファイルを作成します。
WindowsでPythonの実行する4つの方法 - ちょっと便利なてっちーノート


2. laboin.batをグループポリシーでログオンスクリプトとして登録
これが一番わかりやすいと思います。
グループ・ポリシーを使って、コンピュータの終了時にコマンドを実行する:Tech TIPS - @IT

自分はシャットダウン時ではなくてログオフ時にしたかったので実際の登録場所は以下の記事を参考にしました。
Windows 2012 でログオン・ログオフ時間をイベントへ記録する。


そしてこれらの記事を参考にログオンスクリプト・ログオフスクリプトとして登録します。

…それだけのはずなのに何度やってもうまく発動しないので、


3. laboin.pyをログオンディレクトリに置いてくる
のステップをやります。

以下の図はログオンスクリプトにlaboin.batを登録しているだけの状態です。
f:id:cobaltic:20190628204850p:plain

他の設定を少しいじって(後述)実行すると以下のようなメッセージが吐かれます。

f:id:cobaltic:20190628204913p:plain

多分
①ログオンスクリプトのbatファイルが開く
②batファイルの処理としてpyファイルを開こうとするがないので開けない

というような流れなので参照ボタンを押して出てくる場所に以下のように実行されるべきpyファイルを置いてきたところ
f:id:cobaltic:20190628204932p:plain
上手くいくようになりました。

ちなみにログオンスクリプトに登録して、仮に失敗したとしても親切にエラーを吐いてくれません。
[GP]「実行中のレガシ ログオン スクリプトを表示しない」で利用されているレジストリ | Windowsレジストリ置き場
を参考にしてログオンスクリプトの実行を表示する に設定することで原因が明らかになりました。

この記事を書く上では以下の記事にお世話になりました。
はてなブログにソースコードを色付けして貼り付ける方法 - sonickun.log