People working as a PowerShell developers have to work a lot with saving data into csv files. Working with CSV files is the most efficient way to save the custom output in readable table format.

However, some requests we receive are to store the output in an Excel file. As we know there are some significant differences between csv and excel like charts, formatting, etc. I don’t want to go into details but they are easily available on the internet.

To work with the Excel file in PowerShell, we need to use the ImportExcel module. To download the ImportExcel module from the PowerShell gallery, use the PS gallery link below.

https://www.powershellgallery.com/packages/ImportExcel/7.4.1

The GitHub repository for this module can be found below location,

https://github.com/dfinke/ImportExcel

Index

• a. Install the ImportExcel module
• b. Save cmdlet output in Excel
• c. Create multiple worksheets in Excel file.
• d. Text Color Formatting
• e. Set table and heading formatting

a. Install ImportExcel Module

Use the below command line to install the ImportExcel module from the PowerShell gallery.

Install-Module ImportExcel -Force -Verbose

To install the specific version, use -RequiredVersion parameter.

Install-Module -Name ImportExcel -RequiredVersion 7.4.1

b. Save cmdlet output in Excel

Once the ImportExcel module is installed, you can save the cmdlet output to excel using the Export-Excel command.

Import-Module ImportExcel
Get-Service | Select Name, DisplayName, StartType, Status | Export-Excel C:\Temp\Services.xlsx

Once you run the above command, the output will be saved to C:\Temp\Services.xlsx file. If you see the excel file, the size is not fitted properly (default).

We need to use the –AutoSize parameter to display the full text.

To add the Worksheet name, use the –WorkSheetName parameter.

Get-Service | Select Name, DisplayName, StartType, Status | Export-Excel C:\Temp\Services.xlsx  -WorkSheetName Services

C. Create Multiple Worksheets in single excel file

In the above example, we have seen how we create an excel file with a worksheet. Now, for example, let’s say we need services, processes, and disk information in the excel file then it is possible. Explained through the below code.

$outputFile = "C:\Temp\WindowsInfo.xlsx"

# Get services information
Get-Service | Select Name, DisplayName, StartType, Status | Export-Excel $outputFile  -WorkSheetName Services -AutoSize

# Get processes information
Get-Process | Select Name, Id, CPU, WorkingSet | Export-Excel $outputFile -WorksheetName Processes -AutoSize

# Get disk information
gwmi win32_logicaldisk | Select DeviceId, @{N='FreeSpace';E={[math]::Round(($_.FreeSpace/$_.Size)*100,2)}},@{N='TotalSize';E={[math]::Round(($_.Size/1GB),2)}} | `
    Export-Excel $outputFile -WorksheetName DiskInformation -AutoSize

You can see in the below output, 3 worksheets are created.

d. Text color formatting

To format the text color, we need to use the New-ConditionalText command. In the above example, we can format the text with foreground and background colors. For instance, for stopped services, we can use Red background color, for running services green, and for the disabled services gray background as shown below.

$text1 = New-ConditionalText -Text "Running" -ConditionalTextColor White -BackgroundColor Green
$text2 = New-ConditionalText -Text "Stopped" -ConditionalTextColor White -BackgroundColor Red
$text3 = New-ConditionalText -Text "Disabled" -BackgroundColor Gray

Get-Service | Select Name, DisplayName, StartType, Status | Export-Excel C:\Temp\Services.xlsx -ConditionalText $text1,$text2,$text3 -AutoSize

You can also color to the specific excel shell range. For example, If we need to color the header then select the header range. For example,

$text4= New-ConditionalText -Range "A1:D1" -BackgroundColor "Yellow"
Get-Service | Select Name, DisplayName, StartType, Status | Export-Excel C:\Temp\Services.xlsx -ConditionalText $text1,$text2,$text3,$text4 -AutoSize

e. Set table and heading formatting

In the above example, we have formatted the text color of the heading by selecting the cell range. There is also an option to format the header and create a table with excel cmdlets.

$outputFile = "C:\Temp\WindowsInfo.xlsx"
Get-Service | Select Name, DisplayName, StartType, Status | `
     Export-Excel $outputFile  -WorkSheetName Services -AutoSize `
       -Title 'Services Info' -TitleBold -TableName 'ServicesTable' -TableStyle Dark1

Get-Process | Select Name, Id, CPU, WorkingSet | `
     Export-Excel $outputFile -WorksheetName Processes -AutoSize `
        -Title 'Processes Info' -TitleBold -TableName 'ProcessTable' -TableStyle Dark3

gwmi win32_logicaldisk | Select DeviceId, @{N='FreeSpace';E={[math]::Round(($_.FreeSpace/$_.Size)*100,2)}},@{N='TotalSize';E={[math]::Round(($_.Size/1GB),2)}} | `
    Export-Excel $outputFile -WorksheetName DiskInformation `
     -Title 'Disk Info' -TitleBold -TableName 'DiskTable' -TableStyle Medium2 -AutoSize

We will continue the remaining below parts in the next articles. Stay tuned….!!!

• Creating charts
• Opening an existing file
• Set border
• Conditional value formatting
• Convert Excel to csv file