PowershellでCSVをExcelファイルに転記する

状況

DBの特定のテーブルをちょっと結合したりなんかして特定フォーマットのExcelに出力して欲しい

こんな要件が結構あります。

私が入る前はこんな感じの要件１つに付き１つアプリ組んでたんですね。

今回そんな感じのを頼まれたので、できるだけアプリを組まないようにスクリプトで完結させました。

本記事はその時の成果物です。

転記ツール

構成

• Csv2Excel.ps1
• /lib
  • EPPlus.dll

フォルダ構成書きにくい。 今回EPPlusを使っていて、理由は以下です。

• 以前PowershellでComObjectからExcelを使ったところ、色々やってもプロセスが消えなかった。
• サーバー上でロックしている状態で動くので、Comの方のExcelを使うと正常に動かない。

スクリプト

引数

引数名	説明
sourceDataPath	CSVのパス
templatePath	テンプレートとなるファイルのパス
destinationPath	出力先パス
configPath	設定ファイルのパス(json)

設定ファイル

バッチからPowershellスクリプトを実行するので、列になるオブジェクトやバッチ本体を修正せずに調整したい項目はjsonにしました。

{
  "delimiter":",",  # 区切り文字
  "start_location": { # 開始位置
    "row": "2",
    "column": "1"
  },
  "numeric_columns": [10,11] # 数字のカラム(Stringそのまま入れたくない場合指定)
}

ソースコード

param(
    [parameter(mandatory=$true)][string]$sourceDataPath,
    [parameter(mandatory=$true)][string]$templatePath,
    [parameter(mandatory=$true)][string]$destinationPath,
    [parameter(mandatory=$true)][string]$configPath
)

<# ライブラリ参照 #>
[System.Reflection.Assembly]::LoadFrom((Join-Path (Split-Path $MyInvocation.MyCommand.Path) "\lib\EPPlus.dll")) | Out-Null

<# 設定ファイル読む #>

$cfg = Get-Content -Path $configPath -Raw | ConvertFrom-Json

<# Excelのフォーマットに転記して、出力先に名前を付けて保存する #>
$pkg  = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $templatePath
$sht  = $pkg.Workbook[0].Worksheets[1]

$csvRows= Get-Content -Path $sourceDataPath -Encoding Default

for($ri=0; $ri -lt $csvRows.length; $ri++) {
    $csvRowData = ($csvRows[$ri]) -split $cfg.delimiter
    for($ci=0; $ci -lt $csvRowData.length; $ci++) {

        $row = $ri + $cfg.start_location.row
        $col = $ci + $cfg.start_location.column
        $value = if($cfg.numeric_columns.Contains($col)){ [double]($csvRowData[$ci]) } else { [string]$csvRowData[$ci] }
        $sht.Cells.Item($row, $col).Value = $value
    }
}

$pkg.SaveAs($destinationPath)
$pkg.Dispose()

以上！