2012年2月28日火曜日

◆WindowsFormのイベントプロシジャで変数の現在値を参照する

久しぶりにWindowsFormを使ってみた。
以下のような感じ。

image

ソースはこの通り。

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

Add-Type -AssemblyName System.Windows.Forms
$appClass = [System.Windows.Forms.Application]
$btnClass = [System.Windows.Forms.Button]
$txtClass = [System.Windows.Forms.TextBox]
$lblClass = [System.Windows.Forms.Label]
$msgbox = [System.Windows.Forms.MessageBox]

$form = New-Object System.Windows.Forms.Form -Property
 @{
  Text
="テストフォーム";Name="testForm"}

$text1 = New-Object $txtClass -Property
 @{
  Left
=10;Top=10;Name="Text1";Width=35;Text=((Get-Date).ToString("yyyy"))}

$button1 = New-Object $btnClass -Property
 @{
  Left
=20;Top=($text1.Top+40);Text="Run"}
$button1.
add_Click({
 
$msgbox::Show("対象は" + $text1.Text + "年度です。"
)
})


$button2 = New-Object $btnClass -Property
 @{
  Left
=$button1.Right+10;Top=($text1.Top+40);Text="Run2"}
$button2.
add_Click({
 
$msgbox::Show("対象は" + $text1.Text + "年度です。"
)
})


Get-Variable -Name label*,text*,button* | %{$form.Controls.Add($_.value)}

$appClass::Run($form)
$form.Dispose()
</

特にどうということは無いのだが、ボタン1のメッセージとボタン2のメッセージ(17行目と23行目)は1つにまとめたい所。

単純に考えると、

001
002
003
004
005
006
007
008
009
010

$text1 = New-Object $txtClass -Property @{
  Left
=10;Top=10;Name="Text1";Width=35;Text=((Get-Date).ToString("yyyy"
))}
 

$targetYear = "対象は" + $text1.Text + "年度です。"

$button1 = New-Object $btnClass -Property
 @{
  Left
=20;Top=($text1.Top+40);Text="Run"}
$button1.
add_Click({
 
$msgbox::Show($targetYear)
}
)

こんな感じに纏めたいところだが、これではテキストボックス値を変更しても反映されない。

どうでもよさそうな話だが、ちょっと迷ってしまう。
ちなみにC#だとどうだろうと思い、ちょっとコーディングしてみた。

こんな感じになりそうだ。
image

PowerShellでもフォーマット演算子を使うのが良いのかも。

一応PowerShellっぽさを出すと以下のようにしても良いのかなぁ。

001
002
003
004
005
006
007
008
009
010

$text1 = New-Object $txtClass -Property @{
  Left
=10;Top=10;Name="Text1";Width=35;Text=((Get-Date).ToString("yyyy"
))}
 

$targetYear = {"対象は" + $text1.Text + "年度です。"}

$button1 = New-Object $btnClass -Property
 @{
  Left
=20;Top=($text1.Top+40);Text="Run"}
$button1.
add_Click({
 
$msgbox::Show((&$targetYear))
})

2012年2月20日月曜日

◆Excel無しにExcelファイルを作る(サンプル)

PowerShell: ◆Excel無しにExcelファイルを作るで環境が出来たので、これを使ったサンプルを作ってみた。

実際にニーズが有りそうだったので以下のような仕様としている。

  • 年度当初に各社員の残業予定を課長が入力するためのExelシート
  • 対象者は社員マスターから取得
  • サーバーで実行するのでExcelの無い環境で動作する

社員マスターはとりあえずこんな感じ。

image

このマスターから以下のようなシートを作る。

image

 

<実装>

「ExcelPackage.dll」の使い方は、さしあたってそれほど難しくはないのだが、書式の設定方法にちょっと悩んだ。

オブジェクトを眺めた感じだと、書式に関係しそうなのは「Cell」が持っている「Style」プロパティくらいなもの。
image

これを見ると、型は「String」になっているので既存のスタイルを指定するくらいしか出来ないように思う。

Excelでスタイルといえば、どう考えても「これ」
image

なので、「Style」に「良い」とか「悪い」とか入れてあげれば良さそう。
なのだが・・・、これがうまくいかない。

色々と試行錯誤した結果、新規のブックではスタイル設定が効かないが、既にそのスタイルを使用しているブックに対しては、追加で設定できることが判った。
ココらへんの仕組みについて私は詳しくないのであくまでも想像だが、「Style」自体はテンプレート的なもので定義されていて、「ExcelPackage.dll」では新規作成時にそれを参照する機能が無いのではないでしょうか。

調べてみると、幸い「ExcelPackageクラス」のコンストラクタには「テンプレート」が指定できるようです。
image

確かに、テンプレートを使えば予め各種設定は済ませておき、データだけを流しこんでブックを作れるようになりますので便利ですね。

そこで、以下のようなシートをテンプレート(残業テンプレート.xlsx)として用意しておくこととしました。
image

これにより、後はデータ量に応じて行を挿入しデータをセットしていけば良いだけという事になります。

ちなみに、計算式については「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()                   
}

とりあえずうまくいっているがもう少しスマートな方法があるのかもしれないので、今後色々と試してみたい。

2012年2月17日金曜日

◆Excel無しにExcelファイルを作る

サーバーでExcelファイルを作る必要があり、先日のPowerShell Advent Calendar 2011 : ATNDPowerShell で Open XML ドキュメントを作成する - Hello Another World!が紹介されていましたので使わせて頂きました。

<PowerTools for Open XML>

  1. 環境
    • 以下から「Open XML SDK 2.0 for Microsoft Office」をインストール。とりあえず、「OpenXMLSDKv2.msi」だけインストールすれば良さそう。
    • 続いて、以下から「PowerTools for Open XML」をダウンロード。
      PowerTools for Open XML
    • 解凍するとC#のソリューションが出てくるのでVisualStudioでビルドする。
    • 「C:\Windows\System32\WindowsPowerShell\v1.0\Modules」に「OpenXmlPowerTools」フォルダーを作りビルドして出来上がった「OpenXmlPowerTools.dll」と「OpenXmlPowerTools.dll-Help.xml」を入れる。
      コマンドラインで「ipmo OpenXmlPowerTools」と打ってエラーにならなければ環境はOK
  2. 使い方
    • 以下のような感じで「Export-OpenXmlSpreadsheet」にオブジェクトをパイプしてやれば簡単にExcelファイルの出来上がり。
      ps | Export-OpenXmlSpreadsheet -OutputPath .\test.xlsx

実に簡単に使える。
非常に素晴らしい。

しかし、これ以降がよく判らない。
単純にこれまでCSVに出力していたものをExcelに変更するだけであればこれで問題ないのだが、せっかくExcelなので多少は書式を設定したり計算式を入れたりしたくなるのが人情というもの。
なんとなく以下のページを見ると、ほとんどがWord用に思えてしまう。
InfoQ: コマンドラインからOfficeドキュメントを生成する

完全に行き詰ってしまったので、ちょっと方向転換して以下のツールを試して見ることとした。
PowerShell で Excel 2007 のシートを操作する - Shigeya Tanabe's blog - Site Home - TechNet Blogs

なんとなくこちらはExcelのVBAライクに使えそうに見える。


<ExcelPackage.dll>

  1. 環境
  2. 使い方
    • dllをロードしておき、VBAと同様に「workbook」オブジェクトや「worksheets」オブジェクト「Cell」オブジェクトなどなんとなく見慣れたオブジェクトを使えば良さそう。
      田辺さんのサンプルをそのまま掲載しておきます。
      image

プロパティとかを見ると色々出来そうなので、あとでもう少し本格的に使ってみようと思います。