PowerShell: ◆Excel無しにExcelファイルを作るで環境が出来たので、これを使ったサンプルを作ってみた。
実際にニーズが有りそうだったので以下のような仕様としている。
- 年度当初に各社員の残業予定を課長が入力するためのExelシート
- 対象者は社員マスターから取得
- サーバーで実行するのでExcelの無い環境で動作する
社員マスターはとりあえずこんな感じ。
このマスターから以下のようなシートを作る。
＜実装＞
「ExcelPackage.dll」の使い方は、さしあたってそれほど難しくはないのだが、書式の設定方法にちょっと悩んだ。
オブジェクトを眺めた感じだと、書式に関係しそうなのは「Cell」が持っている「Style」プロパティくらいなもの。
これを見ると、型は「String」になっているので既存のスタイルを指定するくらいしか出来ないように思う。
Excelでスタイルといえば、どう考えても「これ」
なので、「Style」に「良い」とか「悪い」とか入れてあげれば良さそう。
なのだが・・・、これがうまくいかない。
色々と試行錯誤した結果、新規のブックではスタイル設定が効かないが、既にそのスタイルを使用しているブックに対しては、追加で設定できることが判った。
ココらへんの仕組みについて私は詳しくないのであくまでも想像だが、「Style」自体はテンプレート的なもので定義されていて、「ExcelPackage.dll」では新規作成時にそれを参照する機能が無いのではないでしょうか。
調べてみると、幸い「ExcelPackageクラス」のコンストラクタには「テンプレート」が指定できるようです。
確かに、テンプレートを使えば予め各種設定は済ませておき、データだけを流しこんでブックを作れるようになりますので便利ですね。
そこで、以下のようなシートをテンプレート(残業テンプレート.xlsx)として用意しておくこととしました。
これにより、後はデータ量に応じて行を挿入しデータをセットしていけば良いだけという事になります。
ちなみに、計算式については「Cell」オブジェクトの「Formula」プロパティに文字列で指定してあげれば良さそうです。
以上を踏まえて、作ったのが以下のサンプルとなります。
| 001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
| #**********************************************************
# 002社員マスタから残業予定Excelを作る.ps1
#
# Excel作成にExcelPackage.dllを使ったバージョン
#
#**********************************************************
param(
$outPath = [environment]::GetFolderPath("desktop"), #出力先定義
$dataSource = ".\sqlexpress", #サーバー名
$dataBase = "Test" #DB名
)
#対象年度名でフォルダーを作る
$nendo = Get-Date
$targetNendo = Join-Path $outPath ($nendo.ToString("yyyy") + "年度")
if(Test-Path $targetNendo)
{
del $targetNendo\*.xlsx -Confirm
}
else
{
mkdir $targetNendo | Out-Null
}
#社員テーブル取得用SQL
$sqlCommand = @"
SELECT
社員NO
,氏名
,部
,課
FROM [Test].[dbo].[社員]
"@
#変数定義
$scriptPath = Split-Path $myInvocation.MyCommand.path
$templatePath = Join-Path $scriptPath "残業テンプレート.xlsx"
#接続文字列を準備する
$connectionString =
"Data Source=$dataSource;Initial Catalog=$dataBase;Integrated Security=SSPI;"
#データソースに接続し開く
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
$connection.Open()
#結果をフェッチし、接続を閉じる
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
$connection.Close()
#Excelモジュールロード
$dllPath = Join-Path $scriptPath "dll\ExcelPackage" |
Join-Path -ChildPath ExcelPackage.dll
[Reflection.Assembly]::LoadFrom($dllPath) | Out-Null
#Excelシート作成
$dataset.Tables[0].rows | sort 部,課,社員NO | group 課 | %{
$groupname = $_.NAME
$excel = New-Object OfficeOpenXml.ExcelPackage -ArgumentList `
($targetNendo + "\" + $groupname + ".xlsx"),$templatePath
$sheet = $excel.Workbook.Worksheets["残業予定入力"]
$sheet.Name = $nendo.Year.ToString() + "年度" + $sheet.Name
$r = $c = 1
$style = $sheet.Cell(2,1).Style
$colCount = 17 #カラム数
$_ | select -ExpandProperty group | %{
$r++
$sheet.Cell($r,1).Value = $_.部
$sheet.Cell($r,2).Value = $_.課
$sheet.Cell($r,3).Value = $_.社員NO
$sheet.Cell($r,4).Value = $_.氏名
$sheet.Cell($r,17).Formula = "SUM(E$r`:P$r)" #合計式
1..$colCount | %{$sheet.Cell($r,$_).Style = $style}
}
$excel.Save()
$excel.Dispose()
}
|
とりあえずうまくいっているがもう少しスマートな方法があるのかもしれないので、今後色々と試してみたい。
