EPPlusでExcelのフォーマットを編集する
PowerShellで出力したExcelのフォーマットの直し方
以前、ImportExcelモジュールを使ってExcelデータを集約する記事を書きました。
こんな感じの使用記録表を取り込んでExcel出力したところ、表示が崩れてしまいました。
| No | date | starttime | endtime | duration |
| 1 | 2020/4/1 | 9:00 | 10:00 | 1:00 |
| 2 | 2020/4/3 | 11:00 | 11:30 | 0:30 |
| 3 | 2020/4/5 | 15:00 | 17:00 | 2:00 |
| 4 |
日付や時刻が崩れてしまうようです。具体的には、こんな感じ。
- 日付のみのデータはDoubleの数字で格納される
- 時刻のみのデータは1900/1/0 の付いた時刻に変換される
Export-Excelでは、-PassThruオプションを使うとEPPlusのオブジェクトが返ってきます。
Export-ExcelのHelpの例13あたりに記載があります。
|
1 2 |
$TargetExcel = "C:¥Users¥Owner¥Documents¥Output.xlsx" $Table|Where-Object {$_.date -ne $null}|Export-Excel -Path $TargetExcel -WorksheetName Table -PassThru |sv Excel |
こうすると、$Excel変数にEPPlusオブジェクトが入ります。
svはSet-Variableの略です。
下記でも動くらしいんですが、パイプで送ったものが左に返ってくるのがなんとなく気持ち悪いので慣れません😓
|
1 |
$Excel = $Table|Export-Excel -Path $TargetExcel -PassThru |
Tableシートにデータが収まっているので、日付と時刻を指定します。
数字を日付に戻す
|
1 |
$Excel.Table.Column(2).Style.Numberformat.Format = "yyyy/mm/dd" |
日付の付いた時刻を時刻のみに戻す
|
1 |
$Excel.Table.Column(3).Style.Numberformat.Format = "HH:mm" |
Excelの書式設定で使う文字列ならなんでも行けそうです。
Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について Microsoft help
Saveをお忘れなく。
|
1 |
$Excel.save() |
