PSWriteExcel - PowerShell Module

This PowerShell Module allows you to create Microsoft Excel (.xlsx) document without Microsoft Excel installed. That basically means you can create Excel documents on servers, or shared workstations without Microsoft Office. It also means it can be run in background and doesn't require any COM objects. What is great about it, it runs on Windows, Linux and Mac OS.

Note worthy features

Allows Microsoft Excel documents to be created in PowerShell

Doesn't require Microsoft Excel to be installed

Fairly easy to use

No license needed. Free to use.

Open source

Useful links

Code is published on GitHub

Issues should be reported on GitHub

Code is published as a module on PowerShellGallery

PSWriteExcel is a very basic (at the moment) PowerShell module allowing to create Excel workbooks from PowerShell. It's utilizes an open source module EPPlus that's available on GitHub so if you're into C# / .NET you can use it as well. This PowerShell module was written for PSWinDocumentation and as such is able to take any Object and put it into Excel. There are 2 ways to build Excel:

Where you create new Excel, create WorkSheet and add data to it – New-ExcelDocument, Add-ExcelWorkSheet, Add-ExcelWorksheetData and finally Save-ExcelDocument

Import-Module PSWriteExcel -Force

$FilePath = "$Env:USERPROFILE\Desktop\PSWriteExcel-Example-Test.xlsx"

$Excel = New-ExcelDocument -Verbose

$ExcelWorkSheet = Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'Test 10' -Supress $False -Option 'Replace'
$ExcelWorkSheet2 = Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'Test 2' -Supress $False -Option 'Replace'
$ExcelWorkSheet3 = Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'This is very long title - Will be cut off at some point' -Supress $false -Option 'Replace'
Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'Test 1' -Option 'Replace' -Supress $True
Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'Test 2' -Option 'Skip' -Supress $True
Add-ExcelWorkSheet -ExcelDocument $Excel -WorksheetName 'Test 1' -Option 'Replace' -Supress $True
#Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorkSheet -CellRow 1 -CellColumn 1 -CellValue 'Test'

$myitems0 = @(
    [pscustomobject]@{name = "Joe"; age = 32; info = "Cat lover"},
    [pscustomobject]@{name = "Sue"; age = 29; info = "Dog lover"},
    [pscustomobject]@{name = "Jason another one"; age = 42; info = "Food lover"
    }
)

Add-ExcelWorksheetData -ExcelWorksheet $ExcelWorkSheet -DataTable $myitems0 -AutoFit -AutoFilter
Add-ExcelWorksheetData -ExcelWorksheet $ExcelWorkSheet2 -DataTable $myitems0 -AutoFit -AutoFilter
Add-ExcelWorksheetData -ExcelWorksheet $ExcelWorkSheet3 -DataTable $myitems0 -AutoFit -AutoFilter
Add-ExcelWorksheetData -DataTable $myitems0 -Verbose -AutoFit -AutoFilter
Add-ExcelWorksheetData -DataTable $myitems0 -AutoFit -AutoFilter -ExcelDocument $Excel
Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePath -OpenWorkBook

Simplified version where you simply pipe an object to ConvertTo-Excel command – $Object | ConvertTo-Excel -Path ‘Export.xlsx' -WorkSheetName ‘MyName'

Import-Module PSWriteExcel -Force

$FilePath = "$Env:USERPROFILE\Desktop\PSWriteExcel-Example-Test1.xlsx"

$myitems0 = @(
    [pscustomobject]@{name = "Joe"; age = 32; info = "Cat lover"},
    [pscustomobject]@{name = "Sue"; age = 29; info = "Dog lover"},
    [pscustomobject]@{name = "Jason another one"; age = 42; info = "Food lover"
    }
)

# Standard way
ConvertTo-Excel -DataTable $myitems0 -FilePath $FilePath -ExcelWorkSheetName 'This is my test' -AutoFilter -AutoFit -FreezeTopRow #-Verbose
# pipeline
$myitems0 | ConvertTo-Excel -FilePath $FilePath -ExcelWorkSheetName 'This is my test2' -AutoFilter -AutoFit -Option Skip -FreezeTopRow #-OpenWorkBook

Required prerequisites

Before you are able to use this script you need to do few manual steps. Since this script is published as module… it's quite easy to set this up. Just execute command below (accept warnings) .. and start creating Microsoft Excel document in PowerShell.

Install-Module PSWriteExcel

#Update-Module PSWriteExcel

You can of course install everything manually from GitHub (as everything is published there) but it will be far easier to just use Install-Module.

Required prerequisites - for linux

On Linux (in my case Ubuntu) the steps are very similar. First you need to get PowerShell up and running on your instance thou. Microsoft has you covered on this Installing PowerShell Core on Linux article.

# Download the Microsoft repository GPG keys
wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb

# Register the Microsoft repository GPG keys
sudo dpkg -i packages-microsoft-prod.deb

# Update the list of products
sudo apt-get update

# Install PowerShell
sudo apt-get install -y powershell

# Start PowerShell
pwsh

And then simply similar steps as above

Install-Module PSWriteExcel -Scope CurrentUser

#Update-Module PSWriteExcel

And then just one command… and woila!

Get-Process | ConvertTo-Excel -Path 'ThisIsMyExcel.xlsx' -WorkSheetName 'AndWorksheet' -AutoFilter

Quick fixes / helpful tips

PowerShellGallery – You are installing modules from an untrusted repository

Install-Module: The term Install-Module is not recognized as the name of cmdlet, function, script file or operable program

PSWriteExcel – AutoFit error on Ubuntu – PowerShell Core – The type initializer for Gdip threw an exception.

PSWriteExcel – PowerShell Module