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:

Continue reading →