2019年9月23日 / 最終更新日時 : 2020年5月22日 sito IT

Export-Excelコマンドレットの使い方【ImportExcel】【Powershell】

Eport-Excelコマンドレットの使い方。コマンドレットヘルプの日本語訳して再構成したものです。

概要

名前
Export-Excel

概要
Exports data to an Excel worksheet.
説明
Excelファイルにデータをエクスポートし、可能な場合はテキストフィールドの数値を変換して、Excelがテキストではなく数値として認識するようにします。結局のところ、Excelは数値の操作と計算に使用されるスプレッドシートプログラムです。数値変換が不要な場合は、パラメーター-NoNumberConversion *を使用します。

サンプル

————————– 例 1 ————————–

PS C:\>;Get-Process | Export-Excel .\Test.xlsx -show

1	PS C:\>;Get-Process \| Export-Excel .\Test.xlsx -show

すべてのプロセスをExcelファイル「Test.xlsx」にエクスポートし、すぐにファイルを開きます。

————————– 例 2 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
Write-Output -1 668 34 777 860 -0.5 119 -0.1 234 788 |
Export-Excel @ExcelParams -NumberFormat '[Blue]$#,##0.00;[Red]-$#,##0.00'

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

Write-Output -1 668 34 777 860 -0.5 119 -0.1 234 788 |

Export-Excel @ExcelParams -NumberFormat '[Blue]$#,##0.00;[Red]-$#,##0.00'

すべてのデータをExcelファイル「Excel.xslx」にエクスポートし、負の値を赤で、正の値を青で色付けします。また、千の区切り文字を使用し、小数点以下2桁まで表示する数値の前にドル記号を追加します。

————————– 例 3 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
[PSCustOmobject][Ordered]@{
Date = Get-Date
Formula1 = '=SUM(F2:G2)'
String1 = 'My String'
String2 = 'a'
IPAddress = '10.10.25.5'
Number1 = '07670'
Number2 = '0,26'
Number3 = '1.555,83'
Number4 = '1.2'
Number5 = '-31'
PhoneNr1 = '+32 44'
PhoneNr2 = '+32 4 4444 444'
PhoneNr3 = '+3244444444'
} | Export-Excel @ExcelParams -NoNumberConversion IPAddress, Number1

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

[PSCustOmobject][Ordered]@{

Date = Get-Date

Formula1 = '=SUM(F2:G2)'

String1 = 'My String'

String2 = 'a'

IPAddress = '10.10.25.5'

Number1 = '07670'

Number2 = '0,26'

Number3 = '1.555,83'

Number4 = '1.2'

Number5 = '-31'

PhoneNr1 = '+32 44'

PhoneNr2 = '+32 4 4444 444'

PhoneNr3 = '+3244444444'

} | Export-Excel @ExcelParams -NoNumberConversion IPAddress, Number1

すべてのデータをExcelファイル「Excel.xlsx」にエクスポートし、「IPAddress」および「Number1」を除くすべての値を可能な限り数値に変換しようとします。これらは、数値に変換されずに「そのまま」シートに保存されます。

————————– 例 4 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
[PSCustOmobject][Ordered]@{
Date = Get-Date
Formula1 = '=SUM(F2:G2)'
String1 = 'My String'
String2 = 'a'
IPAddress = '10.10.25.5'
Number1 = '07670'
Number2 = '0,26'
Number3 = '1.555,83'
Number4 = '1.2'
Number5 = '-31'
PhoneNr1 = '+32 44'
PhoneNr2 = '+32 4 4444 444'
PhoneNr3 = '+3244444444'
} | Export-Excel @ExcelParams -NoNumberConversion *

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

[PSCustOmobject][Ordered]@{

Date = Get-Date

Formula1 = '=SUM(F2:G2)'

String1 = 'My String'

String2 = 'a'

IPAddress = '10.10.25.5'

Number1 = '07670'

Number2 = '0,26'

Number3 = '1.555,83'

Number4 = '1.2'

Number5 = '-31'

PhoneNr1 = '+32 44'

PhoneNr2 = '+32 4 4444 444'

PhoneNr3 = '+3244444444'

} | Export-Excel @ExcelParams -NoNumberConversion *

すべてのデータをExcelファイル「Excel.xlsx」にそのままエクスポートし、数値変換は行われません。これは、Excelが「Export-Excel」関数に渡したデータとまったく同じデータを表示することを意味します。

————————– 例 5 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
Write-Output 489 668 299 777 860 151 119 497 234 788 |
Export-Excel @ExcelParams -ConditionalText $(
New-ConditionalText -ConditionalType GreaterThan 525 -ConditionalTextColor DarkRed -BackgroundColor Ligh
tPink
)

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

Write-Output 489 668 299 777 860 151 119 497 234 788 |

Export-Excel @ExcelParams -ConditionalText $(

New-ConditionalText -ConditionalType GreaterThan 525 -ConditionalTextColor DarkRed -BackgroundColor Ligh

tPink

)

Excelの条件付き書式設定ルールを持つデータをエクスポートします。値が525を超えるセルを表示する背景の塗りつぶし色は「LightPink」、テキストは「DarkRed」です。条件が満たされない場合、色はデフォルトで、白い背景に黒いテキストになります。

————————– 例 6 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
Get-Service | Select-Object -Property Name, Status, DisplayName, ServiceName |
Export-Excel @ExcelParams -ConditionalText $(
New-ConditionalText Stop DarkRed LightPink
New-ConditionalText Running Blue Cyan
)

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

Get-Service | Select-Object -Property Name, Status, DisplayName, ServiceName |

Export-Excel @ExcelParams -ConditionalText $(

New-ConditionalText Stop DarkRed LightPink

New-ConditionalText Running Blue Cyan

)

すべてのサービスをExcelシートにエクスポートし、値に「Stop」という単語が含まれる場合に背景の塗りつぶし色を「LightPink」に、テキストの色を「DarkRed」に設定する条件付き書式設定ルールを設定します。
値に「実行中」という単語が含まれている場合、背景の塗りつぶしの色は「シアン」、テキストの色は「青」になります。どちらの条件も満たされない場合、色はデフォルトの白の背景に黒のテキストになります。

————————– 例 7 ————————–

PS> $ExcelParams = @{
Path = $env:TEMP + '\Excel.xlsx'
Show = $true
Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

$Array = @()

$Obj1 = [PSCustomObject]@{
Member1 = 'First'
Member2 = 'Second'
}

$Obj2 = [PSCustomObject]@{
Member1 = 'First'
Member2 = 'Second'
Member3 = 'Third'
}

$Obj3 = [PSCustomObject]@{
Member1 = 'First'
Member2 = 'Second'
Member3 = 'Third'
Member4 = 'Fourth'
}

$Array = $Obj1, $Obj2, $Obj3
$Array | Out-GridView -Title 'Not showing Member3 and Member4'
$Array | Update-FirstObjectProperties | Export-Excel @ExcelParams -WorksheetName Numbers

PS> $ExcelParams = @{

Path = $env:TEMP + '\Excel.xlsx'

Show = $true

Verbose = $true

}

Remove-Item -Path $ExcelParams.Path -Force -EA Ignore

$Array = @()

$Obj1 = [PSCustomObject]@{

Member1 = 'First'

Member2 = 'Second'

}

$Obj2 = [PSCustomObject]@{

Member1 = 'First'

Member2 = 'Second'

Member3 = 'Third'

}

$Obj3 = [PSCustomObject]@{

Member1 = 'First'

Member2 = 'Second'

Member3 = 'Third'

Member4 = 'Fourth'

}

$Array = $Obj1, $Obj2, $Obj3

$Array | Out-GridView -Title 'Not showing Member3 and Member4'

$Array | Update-FirstObjectProperties | Export-Excel @ExcelParams -WorksheetName Numbers

プロパティ「Member3」および「Member4」を追加して、配列の最初のオブジェクトを更新します。
その後、すべてのオブジェクトがExcelファイルにエクスポートされ、すべての列ヘッダーが表示されます。

————————– 例 8 ————————–

PS C:\>Get-Process | Export-Excel .\test.xlsx -WorksheetName Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM

1	PS C:\>Get-Process \| Export-Excel .\test.xlsx -WorksheetName Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM

————————– 例 9 ————————–

PS C:\>Get-Process | Export-Excel .\test.xlsx -WorksheetName Processes -ChartType PieExploded3D -IncludePivotChart -
IncludePivotTable -Show -PivotRows Company -PivotData PM

1 2	PS C:\>Get-Process \| Export-Excel .\test.xlsx -WorksheetName Processes -ChartType PieExploded3D -IncludePivotChart - IncludePivotTable -Show -PivotRows Company -PivotData PM

————————– 例 10 ————————–

PS C:\>Get-Service | Export-Excel 'c:\temp\test.xlsx' -Show -IncludePivotTable -PivotRows status -PivotData @{statu
s='count'}

1 2	PS C:\>Get-Service \| Export-Excel 'c:\temp\test.xlsx' -Show -IncludePivotTable -PivotRows status -PivotData @{statu s='count'}

————————– 例 11 ————————–

PS> $pt = [ordered]@{}
$pt.pt1=@{ SourceWorkSheet = 'Sheet1';
PivotRows = 'Status'
PivotData = @{'Status'='count'}
IncludePivotChart = $true
ChartType = 'BarClustered3D'
}
$pt.pt2=@{ SourceWorkSheet = 'Sheet2';
PivotRows = 'Company'
PivotData = @{'Company'='count'}
IncludePivotChart = $true
ChartType = 'PieExploded3D'
}
Remove-Item -Path .\test.xlsx
Get-Service | Select-Object -Property Status,Name,DisplayName,StartType | Export-Excel -Path .\test.xlsx -AutoSize
Get-Process | Select-Object -Property Name,Company,Handles,CPU,VM | Export-Excel -Path .\test.xlsx -AutoSize -WorksheetName 'sheet2'
Export-Excel -Path .\test.xlsx -PivotTableDefinition $pt -Show

PS> $pt = [ordered]@{}

$pt.pt1=@{ SourceWorkSheet = 'Sheet1';

PivotRows = 'Status'

PivotData = @{'Status'='count'}

IncludePivotChart = $true

ChartType = 'BarClustered3D'

}

$pt.pt2=@{ SourceWorkSheet = 'Sheet2';

PivotRows = 'Company'

PivotData = @{'Company'='count'}

IncludePivotChart = $true

ChartType = 'PieExploded3D'

}

Remove-Item -Path .\test.xlsx

Get-Service | Select-Object -Property Status,Name,DisplayName,StartType | Export-Excel -Path .\test.xlsx -AutoSize

Get-Process | Select-Object -Property Name,Company,Handles,CPU,VM | Export-Excel -Path .\test.xlsx -AutoSize -WorksheetName 'sheet2'

Export-Excel -Path .\test.xlsx -PivotTableDefinition $pt -Show

この例では、2つのピボットテーブルを定義しています。次に、Export-Excelへの1回の呼び出しでサービスデータをSheet1に配置し、Export-Excelへの2回目の呼び出しでsheet2にプロセスデータを配置します。 3番目の最後の呼び出しは、2つのピボットテーブルを追加し、スプレッドシートをExcelで開きます。

————————– 例 12 ————————–

PS> Remove-Item -Path .\test.xlsx
$excel = Get-Service | Select-Object -Property Status,Name,DisplayName,StartType | Export-Excel -Path .\test.xlsx -PassThru
$excel.Workbook.Worksheets["Sheet1"].Row(1).style.font.bold = $true
$excel.Workbook.Worksheets["Sheet1"].Column(3 ).width = 29
$excel.Workbook.Worksheets["Sheet1"].Column(3 ).Style.wraptext = $true
$excel.Save()
$excel.Dispose()
Start-Process .\test.xlsx

PS> Remove-Item -Path .\test.xlsx

$excel = Get-Service | Select-Object -Property Status,Name,DisplayName,StartType | Export-Excel -Path .\test.xlsx -PassThru

$excel.Workbook.Worksheets["Sheet1"].Row(1).style.font.bold = $true

$excel.Workbook.Worksheets["Sheet1"].Column(3 ).width = 29

$excel.Workbook.Worksheets["Sheet1"].Column(3 ).Style.wraptext = $true

$excel.Save()

$excel.Dispose()

Start-Process .\test.xlsx

この例では-PassThruを使用します。サービス情報をワークブックのsheet1に入れ、ExcelPackageオブジェクトを$ Excelに保存します。次に、パッケージオブジェクトを使用して書式設定を適用し、ブックを保存してオブジェクトを破棄してから、Excelにドキュメントを読み込みます。モジュール内の他のコマンドにより、この方法でパッケージオブジェクトを直接操作する必要がなくなります。
————————– 例 13 ————————–

PS> Remove-Item -Path .\test.xlsx -ErrorAction Ignore
$excel = Get-Process | Select-Object -Property Name,Company,Handles,CPU,PM,NPM,WS | Export-Excel -Path .\test.xlsx -ClearSheet -WorksheetName "Processes" -PassThru
$sheet = $excel.Workbook.Worksheets["Processes"]
$sheet.Column(1) | Set-ExcelRange -Bold -AutoFit
$sheet.Column(2) | Set-ExcelRange -Width 29 -WrapText
$sheet.Column(3) | Set-ExcelRange -HorizontalAlignment Right -NFormat "#,###"
Set-ExcelRange -Address $sheet.Cells["E1:H1048576"] -HorizontalAlignment Right -NFormat "#,###"
Set-ExcelRange -Address $sheet.Column(4) -HorizontalAlignment Right -NFormat "#,##0.0" -Bold
Set-ExcelRange -Address $sheet.Row(1) -Bold -HorizontalAlignment Center
Add-ConditionalFormatting -WorkSheet $sheet -Range "D2:D1048576" -DataBarColor Red
Add-ConditionalFormatting -WorkSheet $sheet -Range "G2:G1048576" -RuleType GreaterThan -ConditionValue "10485760
0" -ForeGroundColor Red
foreach ($c in 5..9) {Set-ExcelRange -Address $sheet.Column($c) -AutoFit }
Export-Excel -ExcelPackage $excel -WorksheetName "Processes" -IncludePivotChart -ChartType ColumnClustered -NoLe
gend -PivotRows company -PivotData @{'Name'='Count'} -Show

PS> Remove-Item -Path .\test.xlsx -ErrorAction Ignore

$excel = Get-Process | Select-Object -Property Name,Company,Handles,CPU,PM,NPM,WS | Export-Excel -Path .\test.xlsx -ClearSheet -WorksheetName "Processes" -PassThru

$sheet = $excel.Workbook.Worksheets["Processes"]

$sheet.Column(1) | Set-ExcelRange -Bold -AutoFit

$sheet.Column(2) | Set-ExcelRange -Width 29 -WrapText

$sheet.Column(3) | Set-ExcelRange -HorizontalAlignment Right -NFormat "#,###"

Set-ExcelRange -Address $sheet.Cells["E1:H1048576"] -HorizontalAlignment Right -NFormat "#,###"

Set-ExcelRange -Address $sheet.Column(4) -HorizontalAlignment Right -NFormat "#,##0.0" -Bold

Set-ExcelRange -Address $sheet.Row(1) -Bold -HorizontalAlignment Center

Add-ConditionalFormatting -WorkSheet $sheet -Range "D2:D1048576" -DataBarColor Red

Add-ConditionalFormatting -WorkSheet $sheet -Range "G2:G1048576" -RuleType GreaterThan -ConditionValue "10485760

0" -ForeGroundColor Red

foreach ($c in 5..9) {Set-ExcelRange -Address $sheet.Column($c) -AutoFit }

Export-Excel -ExcelPackage $excel -WorksheetName "Processes" -IncludePivotChart -ChartType ColumnClustered -NoLe

gend -PivotRows company -PivotData @{'Name'='Count'} -Show

これは、Set-ExcelRangeのさまざまな使用方法と条件付き書式の追加を示す、前の例のより洗練されたバージョンです。最後のコマンドで、PivotChartが追加され、ブックがExcelで開かれます。
————————– 例 14 ————————–

PS C:\>0..360 | ForEach-Object {[pscustomobject][ordered]@{X=$_; Sinx="=Sin(Radians(x)) "} } | Export-Excel -now -LineChart -AutoNameRange

1	PS C:\>0..360 \| ForEach-Object {[pscustomobject][ordered]@{X=$_; Sinx="=Sin(Radians(x)) "} } \| Export-Excel -now -LineChart -AutoNameRange

0〜360度のXの値に対してSin（x）の値を示す折れ線グラフを作成します。

————————– 例 15 ————————–

PS> Invoke-Sqlcmd -ServerInstance localhost\DEFAULT -Database AdventureWorks2014 -Query "select * from sys.tables"
-OutputAs DataRows |
Export-Excel -Path .\SysTables_AdventureWorks2014.xlsx -WorksheetName Tables

PS> Invoke-Sqlcmd -ServerInstance localhost\DEFAULT -Database AdventureWorks2014 -Query "select * from sys.tables"

-OutputAs DataRows |

Export-Excel -Path .\SysTables_AdventureWorks2014.xlsx -WorksheetName Tables

SQL Serverデータベースに対してクエリを実行し、-OutputAsパラメーターを使用して結果の行DataRowsを出力します
.
その後、結果はExport-Excel関数にパイプされます。
注：Invoke-Sqlcmdコマンドレットの-OutputAsパラメーターを取得するには、PowerShellギャラリーからSqlServerモジュールをインストールする必要があります。