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() } |
とりあえずうまくいっているがもう少しスマートな方法があるのかもしれないので、今後色々と試してみたい。
0 件のコメント:
コメントを投稿