PowerShell CSV / Excel 轉換函式
| | 4 | |
想在 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.Worksheets[0]
$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.
Comments
# by Anonymous
csvtk 也可以做到 csv 轉 xlsx https://bioinf.shenwei.me/csvtk/usage/#csv2xlsx
# by Joy
板大: 不好意思借用此版問個問題 powershell能設定動態存取嗎?我請chetgpt寫,一直寫不出來, 我想設定D:\aa這個目錄,domain users能夠修改,條件是部門屬性是MIS才真的能夠改這個目錄
# by Jeffrey
to Joy, 聽起來像 NTFS 資料夾權限設定問題,權限授與對象必須是 AD 帳號或群組,PowerShell 只能批次設定權限,無法在存取時跳出來檢查存取者的的部門屬性。我想到比較接近的解法是用 PowerShell 去找 MIS 部門屬性的帳號放進一個特定群組,再授權該群組可以更改。
# by Joy
板大您好 謝謝回覆,您說的這個方式有試出來,可是這樣安全性內的使用者太多了 所以我才想用下條件的方式,本來是可以用動態存取策略,可是要每個資料夾都要設屬性,太麻煩了,所以才這樣設,比如說MIS這個資料夾,安全性派給domain users,進階條件部門屬性是MIS,才真的有權限。這樣安全性裡面只會看到domain users,也不用另建群組