PowerShell CSV / Excel 轉換函式

想在 PowerShell 中將 CSV 轉成 Excel 或將 Excel 轉成 CSV。例如，將以下的 CSV：

轉成以下格式的 Excel：

嚴格來說，這類轉換用手工作也不算難事，Excel 開啟 CSV 置中上色再調成自動寬度就好，但大家知道我性急直逼王藍田，同樣動作多做兩次就想翻桌。心想反正難度不高，有 ClosedXML 在用單手都能寫完，也懶得去找現成程式庫或工具了，直接開工。

除了給檔名讀 CSV，順便復習用 Pipeline 吃物件陣列的進階寫法，寫成 ConvertTo-Excel、ConvertFrom-Excel 兩個函式，程式碼如下：

Function ConvertTo-Excel {
    [CmdletBinding()]
    Param (
        [Parameter(ValueFromPipeline = $true)]
        [PSCustomObject]$csvObject,
        [string]$csvFile,
        [string]$excelFile
    )
    Begin {
        $collection = @()
    }
    Process {
        $collection += $csvObject
    }
    End {
        $ErrorActionPreference = "Stop"
        if (!$collection -or $collection.Count -eq 0) {
            if ($csvFile) {
                if (!(Test-Path $csvFile)) {
                    Write-Error "File not found: $csvFile"
                    return
                }
                $csv = Import-Csv $csvFile
            } 
        }
        else {
            $csv = $collection
        }
        if (!$csv) {
            Write-Error "No CSV data or csvFile specified"
            return
        }
        if (!$excelFile) {
            if (!$csvFile) {
                Write-Error "No excelFile specified"
                return
            }
            $excelFile = $csvFile -replace '\.csv$', '.xlsx'
        }
    
        Add-Type -Path $PSScriptRoot\ClosedXML.dll
        Add-Type -Path $PSScriptRoot\DocumentFormat.OpenXml.dll
        Add-Type -Path $PSScriptRoot\ExcelNumberFormat.dll
    
        $wb = New-Object ClosedXML.Excel.XLWorkbook
        $shtName = 'CsvData'
        if ($csvFile) {
            $shtName = [System.IO.Path]::GetFileNameWithoutExtension($csvFile)
        }
        $ws = $wb.Worksheets.Add($shtName)
        $colIdx = 1
        $csv[0].PSObject.Properties | ForEach-Object {
            $ws.Cell(1, $colIdx++).Value = $_.Name
        }
        $range = $ws.Range($ws.Cell(1, 1), $ws.Cell(1, $colIdx - 1))
        $range.Style.Alignment.Horizontal = [DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues]::Center
        $range.Style.Fill.BackgroundColor = [ClosedXML.Excel.XLColor]::LightGray
        $rowIdx = 2
        $csv | ForEach-Object {
            $colIdx = 1
            $_.PSObject.Properties | ForEach-Object {
                $ws.Cell($rowIdx, $colIdx).Value = $_.Value
                $colIdx++
            }
            $rowIdx++
        }
        $ws.Columns().AdjustToContents() | Out-Null
        $ws.Columns() | ForEach-Object {
            $_.Width += 1
        }
        [System.IO.Directory]::SetCurrentDirectory((Get-Location))
        $wb.SaveAs($excelFile)        
    }
}

Function ConvertFrom-Excel {
    param (
        [Parameter(Mandatory = $true)]
        [string]$excelFile
    )
    $ErrorActionPreference = "Stop"
    if (!$excelFile) {
        Write-Error "No excelFile specified"
        return
    }
    if (!(Test-Path $excelFile)) {
        Write-Error "File not found: $excelFile"
        return
    }
    $excelFile = Resolve-Path $excelFile
    Add-Type -Path $PSScriptRoot\ClosedXML.dll
    Add-Type -Path $PSScriptRoot\DocumentFormat.OpenXml.dll
    Add-Type -Path $PSScriptRoot\ExcelNumberFormat.dll

    $wb = New-Object ClosedXML.Excel.XLWorkbook($excelFile)
    $ws = $wb.Worksheet(1)
    $csv = @()
    [string[]]$colNames = $ws.Rows(1).Cells() | ForEach-Object { $_.Value }
    $rowIdx = 1
    $ws.RowsUsed() | ForEach-Object {
        if ($rowIdx++ -eq 1) {
            return
        }
        $row = New-Object PSObject
        for ($colIdx = 0; $colIdx -lt $colNames.Length; $colIdx++) {
            $row | Add-Member -MemberType NoteProperty -Name $colNames[$colIdx] -Value $_.Cell($colIdx + 1).Value
        }
        $csv += $row
    }
    $csv
}

由於程式會用到 ClosedXML 程式庫，你可以下載 .nupkg 解壓縮取出 DLL 檔，或是開個 .NET 專案參照 ClosedXML NuGet Package 編譯後從 bin 目錄取檔。推薦用 ClosdXML 0.96.0 之前的版本，.NET Formework 4.0 只需要部署 ClosedXML.dll、DocumentFormat.OpenXml.dll、ExcelNumberFormat.dll 三個 DLL，0.97 後 .NET Standard 2.0 需一併部署的檔案增加到 9 個。

沒花時間包成 PowerShell 模組，要使用就在 .ps1 中寫 . <path>\CsvExcelConv.ps1，即可呼叫 ConvertTo-Excel() 及 ConvertFrom-Excel() 函式，範例如下：

. .\CsvExcelConv.ps1
# 吃 CSV 檔
ConvertTo-Excel -csvFile .\sample.csv -excelFile sample1.xlsx
# 吃 ConvertFrom-Csv 結果
Get-Content .\sample.csv | ConvertFrom-Csv | ConvertTo-Excel -excelFile sample2.xlsx
# 吃 Select-Object 或 ForEach-Object 結果
Get-ChildItem . -Filter '*.dll' | ForEach-Object { 
    [PSCustomObject]@{
        檔案路徑 = $_.FullName
        檔案大小 = $_.Size
    }
} | ConvertTo-Excel -excelFile .\dll-list.xlsx
# 驗證結果
ConvertFrom-Excel .\dll-list.xlsx | Format-Table

測試成功。

Due to work requirements, I wrote a simple PowerShell function to convert between CSV and Excel.