Powershell Tools

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:

Ideally, you would have entered your ps commands before running this script, so you know what your output is going to be.

The script asks for a filename, which it stores in C:\Temp, and adds the .xlsx extension.

It asks for the number of sheets- the use for which is entering multiple commands. I’ll get into that in a moment.

The sheet name is, as it says, the sheet name at the bottom tab of the Excel document.

It then asks for a command. This is where you enter your PowerShell command that you want exported on that sheet. The reason you would enter multiple sheets, is so you can have multiple command outputs in the same document. For example, if your first command is get-host, and your second command is get-service- the document will separate the outputs of these commands into sheets.

This can be extremely helpful if gathering info from multiple sources, say for example:

Sheet 1:

invoke-command -ComputerName PC1 -ScriptBlock {get-service} 

Sheet 2:

invoke-command -ComputerName PC2 -ScriptBlock {get-service} 

This leaves you with all the information you need in one workbook, but separated by tabs, for organization.

Something that I found out about this script only after writing it, was that it actually functions fantastically as a csv converter as well! Say you’ve got a lovely spreadsheet that looks like this:

With a quick command, you can turn this eye-sore into a readable, usable spreadsheet.
This file is located in c:\temp\services.csv

PS C:\> xl

File Name: Services Formatted
Number of sheets: 1

Sheet name: Services
Command: import-csv c:\temp\services.csv

Include Pivot Table?

1) Yes
2) No

Select: 2
Working on it...

Sheet Completed.
Exporting to Excel...

Boom- looks great, and definitely saves some time formatting. Of course, the commands I’ve used here are pretty useless in this format, but the concept of being able to run complex PowerShell commands side-by-side on multiple sheets in one workbook was a cool idea to me, and can potentially be very powerful.