PowerShellでGUIアプリを作って、勤怠を少しだけ効率化した話

この記事はPowerShellアドベントカレンダー2019に参加しています。

Excel開いて勤怠表入力するのすごく面倒

私のアルバイト先では、アルバイト学生の勤怠は各自が既定のExcelシートに毎日出勤時間、休憩入り、休憩終了時刻、退勤時間を記入し、それを月末に提出するという形をとっています。しかし、朝出勤してエクセルを開いて記入し、休憩の前にエクセルを開いて記入し、退勤前にエクセルを開いて記入するという作業は非常に面倒です。そこでこの面倒な作業を効率化し、さらにGUIアプリを作ったことがなかったので何か一つ小さなものを作ってみようと思い立ちました。それもPowerShellで...

なぜPowerShellなのか

実は当初は自分だけで使うつもりだったので、Pythonとかで書けばいいかな～と考えていました。しかし学生バイトも多くいて、そういった人たちにも使ってもらいたいことを考えると、Windows標準で動くPowerShellで書いたほうがいいかなと思ったことと、その勤怠表にはいくつかの関数が入っていて、PythonのOpenPyXLではどういうわけかうまく動いてくれなかったのでPowerShellで書くことにしました。また、VBAという手段もあったのですが、どうしても書き方が好きになれませんでした(もちろん多くの業務を効率化できるVBAはそれ自体本当に素晴らしいツールだとは思っていますが)。

完成したもの

もう先に完成したものをおみせしたほうが話が早いと思ったので先にどんなものができたのか披露したいと思います。

• まず、作成したスクリプトを展開します
  

• どこに展開するか聞かれます
  

• 展開するとデスクトップにショートカットアイコンが作成されます
  

• 設定アイコンをダブルクリックし必要情報を入力し
  

• 出勤！
  

• 休憩！
  

• 退勤!!!!
  

そしてこれらの勤怠記録はすべて既定のエクセルシートに書き込まれているのです!便利!!!!

解説

ディレクトリ構成的な

勤怠スクリプト/
        ┣  展開.bat
        ┣ .deploy.ps1 #展開スクリプト
        ┗ psAttendance/
                 ┣ config.bat
                 ┣ start.bat
                 ┣ lunch.bat
                 ┣ finish.bat
                 ┣ scripts/　#上の階層の各.batは以下のスクリプトを呼び出している
                 ┃     ┣ automateAttendance.ps1
                 ┃     ┣ configForm.ps1
                 ┃     ┗ scriptCinfig/
                 ┃              ┗ scriptConfig.xml　#設定を保存する.xml
                 ┃
                 ┗ ico/ #アイコン
                     ┣ configico.ico
                     ┣ startico.ico
                     ┣ lunchico.ico
                     ┗ finishico.ico

このアプリ？の主な機能は、デスクトップのアイコンをクリックすることによって、今まで手入力だった勤怠入力を効率化するものです。また、展開やデスクトップへのアイコンの設置もクリック一つで行えます。

PowerShellで作成したスクリプトをワンクリックで動かす

PowroShellのスクリプト.ps1は、セキュリティ上の観点から、ワンクリックで実行することができません。しかしそれではあまりに不便なので、.batファイルを作成し、そこからPowerShellスクリプトを呼びだしています。

展開.bat

powershell -WindowStyle hidden -ExecutionPolicy Bypass -command ".\deploy.ps1"

WindowStyleプロパティは.batをクリックしたときの画面表示(コマンドの黒いウインドウ)の状態を設定します。hidden(非表示)以外にもnormal, minimized, maxmizedがあります。今回はフォームを表示したいので非表示です。

ExecutionPolicyプロパティはスクリプトを実行する際のポリシーを表します。

実行ポリシー	説明
Restricted	すべてのスクリプトの実行ができない(初期設定)
AllSigned	署名されているスクリプトのみ実行可能
RemoteSigned	外部からダウンロードしてきたスクリプトには署名が必要
Bypass	すべてのスクリプトが実行可能

このようになっています。では今回のようにBypassに設定しては危険だと思われるかもしれません。しかし、このような.batで実行するような場合、実行ポリシーの設定はこのスコープに限られるため、実行が終了すれば実行ポリシーは元の設定に戻ります。

展開

上記展開スクリプトは、実行されると画面を表示し、スクリプト類をインストール先にコピーし、デスクトップにショートカットアイコンを作成します(インストールとかかっこつけてるけどやってることただのコピー)。

    [System.Windows.Forms.MessageBox]::Show($targetTextBox.Text+"にインストールします。", "インストール")
    copy-item ".\psAttendance" $targetTextBox.Text -recurse
    $where = $targetTextBox.Text+"\psAttendance"
    createShortCut config.bat "設定"　$where configIco.ico
    createShortCut start.bat "出勤"　$where startIco.ico
    createShortCut lunch.bat "休憩"　$where lunchIco.ico
    createShortCut finish.bat "退勤"　$where finishIco.ico

PowerShellでユーザーフォームを作る

控えめに言って苦行

PowerShellはWindows formsを使ってGUI画面を作ることができます(WPFやUWPも利用することはできるらしいです...本当かよ...)。

しかし、特に画面をデザインしてくれるエディタがあるわけでもないので、位置や大きさなどをPowerShellで定義する必要があります。頭の中で位置や大きさをイメージしながら試行錯誤する作業は非常につらかったです...
それぞれの詳しい解説はいくつかのサイトで紹介されているので、詳しくは説明しませんが以下が展開時に表示される画面のコードです。雰囲気でもつらみを感じ取っていただければ...

deploy.ps1

#windows formsのアセンブリ読み込み
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

#フォームの作成
$form = New-Object System.Windows.Forms.Form
$form.Text = "展開"
$form.Size = New-Object System.Drawing.Size(620,300)

#ラベルの設定
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(230,10)
$label.AutoSize = $True
$label.TextAlign = "TopCenter"
$label.Text = "ファイル展開"
$Font1 = New-Object System.Drawing.Font(“メイリオ”,15,[System.Drawing.FontStyle]::Bold)
$label.Font = $Font1

function labelConfig($txt, $fontSize, $xAxis, $yAxis){
    $conf= New-Object System.Windows.Forms.Label
    $conf.Location = New-Object System.Drawing.Point($xAxis,$yAxis)
    $conf.AutoSize = $True
    $conf.Text = $txt
    $itemFont = New-Object System.Drawing.Font(“メイリオ”,$fontSize,[System.Drawing.FontStyle]::Bold)
    $conf.Font = $itemFont
    return $conf
}

function txtBoxConfig($xAxis, $yAxis, $xSize, $ySize){
    $conf= New-Object System.Windows.Forms.TextBox
    $conf.Location = New-Object System.Drawing.Point($xAxis,$yAxis)
    $conf.Size = New-Object System.Drawing.Size($xSize,$ySize)
    return $conf
}

$default = $home

$targetLabel = labelConfig "インストール先" 10 20 100
$targetTextBox = txtBoxConfig 125 100 400 50
$targetTextBox.Text = $default

#参照ボタン
$buttonFolderPath = New-Object System.Windows.Forms.Button
$buttonFolderPath.Location = New-Object System.Drawing.Point(530,100)
$buttonFolderPath.Size = New-Object System.Drawing.Size(40,20)
$buttonFolderPath.Text = "参照"

$buttonFolderPath.add_click{

    #ダイアログを表示しファイルを選択する
    $Dialog = New-Object System.Windows.Forms.folderBrowserDialog
    if($Dialog.ShowDialog() -eq "OK"){
        $targetTextBox.Text = $Dialog.SelectedPath
    }
}

#OKキャンセル
# OKボタンを作る
$OKButton = new-object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Point(230,230)
$OKButton.Size = New-Object System.Drawing.Size(70,20)
$OKButton.Text = "OK"
$OKButton.DialogResult = "OK"

# キャンセルボタンを作る
$CancelButton = new-object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Point(310,230)
$CancelButton.Size = New-Object System.Drawing.Size(70,20)
$CancelButton.Text = "キャンセル"
$CancelButton.DialogResult = "Cancel"

#作成したフォームの枠やラベル、ボタンなどをここで一つに合わせる
$Form.Controls.AddRange(@($label,$targetLabel,$targetTextBox,$buttonFolderPath,$OKButton,$CancelButton))

$result = $form.ShowDialog()


#デスクトップにショートカットを作成する関数
function createShortcut($batFile, $name, $whereTo, $icon){
    #PowerShellでさえなくWSHオブジェクトを呼び出しているよ...
    $shell = New-Object -ComObject WScript.Shell
    $link = $shell.CreateShortcut("$Home\Desktop\"+ $name +".lnk")
    $link.TargetPath = $whereTo+"\"+$batFile
    $link.WorkingDirectory = $whereTo

    $link.IconLocation = $where+"\ico\"+$icon
    $link.Save()
}

if($result -eq "OK"){
    [System.Windows.Forms.MessageBox]::Show($targetTextBox.Text+"にインストールします。", "インストール")
    copy-item ".\psAttendance" $targetTextBox.Text -recurse
    $where = $targetTextBox.Text+"\psAttendance"
    createShortCut config.bat "設定" $where configIco.ico
    createShortCut start.bat "出勤" $where startIco.ico
    createShortCut lunch.bat "休憩" $where lunchIco.ico
    createShortCut finish.bat "退勤" $where finishIco.ico


}

設定の保存

このスクリプトを動作させるためには、既定の表の関係上本人の名前と、作成した勤怠表の保存場所、勤怠表の下となるマスター(とはいっても、それをコピーし毎月タイトルと中身の日付をその月のものに書き換える程度)が必要です。

こういった設定を今回はxmlファイルに保存してみました。

scriptConfig.xml

<?xml version="1.0" encoding="utf-8"?>
<root>
  <name></name>
  <targetPath></targetPath>
  <master></master>
</root>

まず、設定を保存するためにこのようなひな型を作成します。何が何を表すかは見て明らかですね。

以下は設定画面のコードを抜粋したものです。

configForm.ps1

#windows formsのアセンブリ読み込み
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

#設定を保存するxmlのPathを指定。$PSScriptRootはこのスクリプトの場所を表す自動変数です。
$xmlPath = "$PSScriptRoot\scriptConfig\scriptConfig.xml"
#xmldocumentオブジェクトを使用
$xml = [XML](Get-Content $xmlPath)
#するとこのように要素にアクセスできるようになります
$name = $xml.root.name
$target = $xml.root.targetPath
$master = $xml.root.master

######中略(フォームの実装)######

#フォームでOKボタンが押されたときに、xmlに値を保存します
if($result -eq "OK"){
    $xml.root.name = $nameTxtBox.Text
    $xml.root.targetPath = $targetTextBox.Text
    $xml.root.master = $masterFileTextBox.Text
    $xml.Save("$xmlPath")
}

PowerShellでのxmlの操作は便利で好きです。

ファイル作成、Excelへの書き込み

一番重要な機能ですね。出退勤、休憩それぞれのためにスクリプトを分けるのは面倒だったので、一つのスクリプトにすべての機能を記述し、コマンドライン引数で挙動を分けることにしました。

引数はこんな感じです

引数	機能
-start	出勤時刻記入
-lunch	休憩開始時刻、終了時刻を記入
-finish	退勤時刻記入

PowerShellではコマンドライン引数は$Args[]変数の中に格納されます。

下準備、ファイルの存在確認

このスクリプトは、当月分の勤怠表が保存先にすでにある場合はそのブックに記入しますが、存在しない場合(月初など)は新たにその月の勤怠表を作成してくれます。便利!!!

それを実現してくれているのがtest-pathコマンドレットです。まあ単にファイルなどの存在を確認してくれるだけなのですが割と多用します。

• 定義など

#メッセージボックスを使用する
Add-Type -Assembly System.Windows.Forms

$xmlPath = "$PSScwiptRoot\scriptConfig\scriptConfig.xml"
$xml = [XML](Get-Content $xmlPath)
$name = $xml.root.name
$target = $xml.root.targetPath
$master = $xml.root.master

#今月の日付
$thisMonth = get-date -format "yyyy_MM"
#出退勤表の場所
$filePath = $target+"/"
$fileName = "勤怠表【"+$name+"】" + $thisMonth + ".xlsx"
#シート名=個人名
$yourName = $name

$file= ${filePath}+${fileName}
#ファイルの有無の確認
$testResult = test-path $file

• 新規にブックを作成する関数

後ほど説明しますが、エクセルを呼び出しています

function createNewAttendanceBook($master, $name, $saveAs){
    $excel = New-Object -ComObject Excel.Application
    #見えなくていいのでFalse
    $excel.Visible = $false
    $book = $excel.Workbooks.Open($master)
    $sheet = $book.WorkSheets.Item($name)
    #現在の月の初日と月末の日付
    $from = (get-date -Day 1).ToString("yyyy/MM/dd")
    $to = (get-date -Day 1).AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd")

    #書き込み
    $sheet.Cells.Item(4,4) = $from
    $sheet.Cells.Item(4,7) = $to
    $book.SaveAs($saveAs)

    $excel.Quit()
    $excel = $null
    [GC]::Collect()

}

これで書き込むブックは用意できました。

時間の取得、四捨五入

PowerShellではGet-Dateコマンドレットで現在時刻を取得できます。-Formatオプションで出力する書式を設定できます。
この勤怠表の時刻入力は5分刻みなので、現在時刻を取得した後に四捨五入をする必要があります。以下はその関数です。

##$targetには取得した現在時刻が入ります

#実際には別の関数の中でこのように呼び出している
$now = get-date -Format "HH:mm"
$now = roundOffMinute $now


function roundOffMinute($target) {
    $right = $target.Substring(4,1)
    if([int]$right -lt 5) {
       $right=0
    } else {
       $right=5
    }
    $target = $target.Remove(4,1)
    $target= $target+[string]$right
    return $target
}

ここではsubstringメソッドを使用しています。これは、引数にスタート値、文字数を指定することで特定の文字列から文字を選択するのが容易になります。HH:mmの5目の値は最後のmなので、この値を四捨五入しています。
if文内ではこの値が5より小さければ変数に0を代入し、それ以外の場合5を代入しています。

最終的にはこの値を結合して返しています。

Excelへの書き込み

function timeCard($workBook,$name, $status){
    #エクセルの使用
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $book = $excel.Workbooks.Open($workBook)
    $sheet = $book.WorkSheets.Item($name)
    #A列に日付が記入
    $arr = @($sheet.Range("A1:A45")).text
　　#今日の日付をM月dd日形式に変換
    $today = get-date -Format "M"
    #検索
    $idx = [Array]::IndexOf($arr,$today)
    #インデックス+1が行
    $row = $idx+1

    $now = get-date -Format "HH:mm"
    $now = roundOffMinute $now

　　#コマンドライン引数がstart、finish、lunchの時それぞれ出勤、退勤、休憩
    if ($status -eq "start"){
        $sheet.Cells.Item($row,4) = $now
        [System.Windows.Forms.MessageBox]::Show($today+" "+$now+"に出勤しました。", "出勤")
    }
    elseif($status -eq "finish") {
        $sheet.Cells.Item($row,7) = $now
        [System.Windows.Forms.MessageBox]::Show($today+" "+$now+"に退勤しました。", "退勤")
    }
    elseif($status -eq "lunch"){
        $sheet.Cells.Item($row,5) = $now
        #休憩は1時間とることになっているので現在時刻に1時間足したものを変数に代入
        $after = (get-date).AddHours(1).ToString("HH:mm")
        $after = roundOffMinute $after
        $sheet.Cells.Item($row,6) = $after
        [System.Windows.Forms.MessageBox]::Show($now+"から休憩にはいります。戻ってくるのは"+$after+"です。", "休憩")
    }
    #ブックの保存
    $book.Save()
    $excel.Quit()
    $excel = $null
    [GC]::Collect()
}

PowerShellでExcelを操作したいときはNew-Object -ComObject Excel.ApplicationでExcelを起動し操作します。メソッドもopenだったりcloseだったりで分かりやすいのですが、一点だけ注意点があります。それはPowerShellで起動し終了したときに正しく終了させないといつまでもプロセスに残り続けてしまうことです。
そのため、最後にこのようにガベージコレクションを走らせてプロセスを解放してあげる必要があります。

       #変数を初期化
       $excel = $null
       [GC]::Collect()

さて、日付を取得し、時間を取得し、ブックを開いたのでいよいよ書き込みです。どのように値が書き込まれるかは勤怠表にもよるのですが今回の勤怠表はこのような形になっています。

ということは日付の行がわかればあとは列を指定してあげれば書き込みができますね。
今日の日付を取得し、勤怠表から日付を検索してくるためにまず勤怠表の特定の列を配列に格納し、そこからindexを返してもらうようにしました。

    $sheet = $book.WorkSheets.Item($name)
    #A列に日付が記入
    $arr = @($sheet.Range("A1:A45")).text
　　#今日の日付をM月dd日形式に変換
    $today = get-date -Format "M"
    #検索
    $idx = [Array]::IndexOf($arr,$today)
    #インデックス+1が行
    $row = $idx+1

ここまで来たらもう引数に従って書き込んで、閉じるだけです。お疲れPowerShell!

まとめ

PowerShellは当然シェルみたいなものなので、GUIをプログラムするのには全く向いていませんでした。まああと冷静にプログラムの書き方がよくないですね...言い訳するようですが結構前に暇な時間に作ったので...。それでもそれなりに使えるものが作れて便利だなあと思います。記法もVBAよりもわかりやすくまた自由度も高いように思うのでEcxelに搭載されればいいのにと思います。初めて触った言語がPowerShellなので微妙な親近感を抱いています。みんな退屈なことはPythonじゃなくてPowerShellにやらせたらいいと思います

実際に使ってみて

• デスクトップのアイコンをクリックすればいいので便利
• しかしその分打刻を忘れやすい
• いちいちExcelを起動しているので動作は緩慢
• その日何時間働いたかがわかりづらい

便利になった分それに伴って相応の問題は生じました。今後はイベントビューアーを監視して出退勤を入力しなくてもいいようなものを作れたらなあと思います。
PowerShellで書くのはつらそうなのでC#とかで...(やったことないけど)

最後までお読みいただきありがとうございました