Scroll Top
Evotec Services sp. z o.o., ul. Drozdów 6, Mikołów, 43-190, Poland
+48 502 469 760
contact@evotec.pl

PSWriteExcel – PowerShell Module

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

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

PSWriteExcel

  • 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