Applies to PowerShell, Excel, Reporting
Modules needed: ImportExcel
Very frequently, I use PowerShell to extract data. Generating a list of users with expiring passwords, checking O365 licenses, finding stale Active Directory objects- you name it. Sometimes finding this information is easiest using PowerShell. But, if you need to give this data to your boss, or send it out to a manager, you need to extract it somehow. Export it to CSV? Gross!
Doug Finke has created a beautiful PowerShell module that has made the creation of Excel documents with PowerShell possible. (Without COM objects!) ImportExcel is my new favorite module- and I made it a little bit simpler to use for my needs.
I’ve set this script up as a function, because I want to be able to take what I’m currently working on, and quickly turn it into an Excel document. Here’s the code:
function xl { Write-Host Write-Host $getpath = Read-Host "File Name" $path = "c:\temp\" + $getpath + ".xlsx" [int]$sheetcount = Read-Host "Number of sheets" [int]$count = 0 while ($count -lt $sheetcount) { Write-Host $sheetname = Read-Host "Sheet name" $command = Read-Host "Command" Write-Host Write-Host "Include Pivot Table?" Write-Host Write-Host "1) Yes" Write-Host "2) No" Write-Host $selection = Read-Host "Select" switch ($selection) { '1' { Write-Host "Working on it..." -ForegroundColor Yellow $ex = Invoke-Expression $command | Export-Excel -Path $path -AutoSize -FreezeTopRow -TableName $sheetname.Replace(' ','') -TableStyle Medium2 -WorkSheetname $sheetname -IncludePivotTable } '2' { Write-Host "Working on it..." -ForegroundColor Yellow $ex = Invoke-Expression $command | Export-Excel -Path $path -AutoSize -FreezeTopRow -TableName $sheetname.Replace(' ','') -TableStyle Medium2 -WorkSheetname $sheetname } } Write-Host Write-Host "Sheet Completed." -ForegroundColor Green $count = $count + 1 } Write-Host "Exporting to Excel..." -ForegroundColor Yellow Invoke-Item $path }
What it does: