Manipulating Excel with Windows PowerShell

By Nate Kasco

The need to read, modify, or output to an Excel document seems to be something that commonly occurs in the Powershell world. Whether it’s needing to simply append logs or maybe something more complex where there is data on a number of different sheets within a single workbook. Maybe an external client sends you an Excel sheet and you don’t even have Excel installed. Let’s go over 2 potential ways to manipulate Excel data with Windows PowerShell:

  1. Leveraging the ImportExcel Module
  2. Using a COM Object

Leveraging the ImportExcel Module:

First, Doug Finke has already created an awesome module to manipulate Excel data that isn’t even dependent on Excel being installed. To get it installed simply open an elevated console and run:

Install-Module -Name ImportExcel

Now that you have the module installed you can start leveraging it’s cmdlets. To get started go ahead and run:

Get-Command -Module ImportExcel

Feel free to check it out for yourself at: https://github.com/dfinke/ImportExcel.

Using a COM Object:

If you work in an environment where security is a bit higher and it can be more difficult than it’s worth to get the module installed. If you already have Excel installed you can simply create a COM object and have direct access to do whatever might be needed. Let’s take a look at an example where we simply need to import our data into Powershell:

$WorkbookPath = "C:\Users\Nate\Desktop\Book1.xlsx"

#Create COM object & open our workbook
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelWorkbook = $ExcelObj.Workbooks.Open($WorkbookPath)

#Select the first sheet
$ExcelWorksheet = $ExcelWorkbook.Sheets.Item(1)

#Get the used range
$UsedRange = $ExcelWorksheet.UsedRange

#Create a PS Custom Object to store data in
$ExcelData = [PSCustomObject]@{}

$Column = 1
while($Column -le 3){
    $Data = $UsedRange.Cells.Item(1,$Column).EntireColumn.value2 | Select-Object -Skip 1
    $Header = $UsedRange.Cells.Item(1,$Column).EntireColumn.value2 | Select-Object -First 1

    $ExcelData | Add-Member -type NoteProperty -Name $Header -Value $Data

    $Column++
}

#Close Excel
$excelObj.Quit()

Now we have all of our data imported into Powershell as $ExcelData to do whatever we need to do. I encourage you to leverage Get-Member on $ExcelWorksheet and don’t forget to run it on sub-members as well to identify all possible ways to manipulate data in the way that is needed. Take a look at the output below compared to our actual Excel document:

By Nate Kasco