Working with Excel in PowerShell

With increasing frequency, I find myself needing to take an existing Excel spreadsheet and needing to generate or query for information and add it back to the spreadsheet. I also found myself getting annoyed with taking the existing spreadsheet, saving it as a CSV, manipulating or gathering information in PowerShell, saving that as a CSV, and then converting the CSV back into Excel. This was error prone and became problematic when cells contained commas or other special characters.

So I decided it was time to learn how to work with Excel in PowerShell.

The Basics

Opening an Excel file

The first step is to setup Excel and open the worksheet.

$ExcelFile = "$pwd\File.xls"
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$Workbook = $excel.workbooks.open($ExcelFile)
$Worksheets = $Workbook.worksheets
$Worksheet = $Workbook.worksheets.Item(1)

 

Note that the $Workbook.worksheets.Item(1) opens the first worksheet in the book. The 1 can be changed to other numbers for other sheets.

Working With Cells

Working with a cell is fairly straightforward, although the syntax isn’t immediately obvious.

$Worksheet.cells.item(<row>,<column>)

Replace <row> and <column> with the appropriate row and column numbers. Note that Excel starts numbering at 1, so the first cell would be (1,1).

What should be obvious to anyone who has worked with PowerShell for any amount of time, but took me some experimenting to figure out, is that PowerShell likes to work with everything as an object, and Excel cells are no different. So, for example, if you want to get the contents of a given cell, you’ll need to specify that property:

$Worksheet.cells.item(1,1).text

By default, to set a value to a cell you do not need to specify the text property, but to query the value of the cell you do.

Working with a Header Row

Unfortunately, working with Excel in an automated or scripted fashion requires knowing where information is. Frequently, I found myself needing to know what column  certain things reside in and I thought to myself, “This is exactly why the first row is all headers!” Since I hate having to remember, check or verify that,for example, column K is for IP addresses and that K is the 11th letter, I decided to find an easier way to work with columns and header rows.

My first use for this was in a server inventory, and it makes a good example of how I approach the problem.

For this example, let’s assume the Excel spreadsheet looks something like this:

ServerName Domain Manufacturer Model IP OS ServicePack
Server1       192.168.0.11    
Server2       192.168.0.12    
Server3       192.168.0.13    
Server4       192.168.0.14    

 

I want to use WMI to query for the blank cells. In theory, this could be done periodically to reflect upgrades or other changes, such as a P2V.

I hate working with column numbers, particularly when working with a spreadsheet interactively in the shell. Furthermore, it is always possible that someone rearranged the columns if the file is shared. So, I wrote a short function that takes an excel worksheet as a parameter and returns a hash table of the headers in the first row and the corresponding column number.

Function Read-Headers {
# Parameters: Excel worksheet
# Returns: Hash table of the contents of each cell in the first row and the
#    corresponding column number
# Note: Processes until first blank cell
    Param ($Worksheet)    
    $Headers =@{}
    $column = 1
    Do {
        $Header = $Worksheet.cells.item(1,$column).text
        If ($Header) {
            $Headers.add($Header, $column)
            $column++
        }
    } until (!$Header)
    $Headers
}

So, if I set a variable to contain the hash information for the sheet I’m working on, like so:

$Headers = Read-Headers $Worksheet
 

I can then reference a column by name, like so:

$Worksheet.cells.item($Row,$Headers["IP"])

where $Row is a variable set to whichever row I am interested in.

Final Thoughts

Hopefully this has helped clarify how to work with Excel in PowerShell. I use the function to grab headers all the time and have found it to make working with big spreadsheets much easier. Please feel free to leave a comment with any questions, suggestions or to share other solutions.

Advertisements

4 thoughts on “Working with Excel in PowerShell

  1. Hi,

    I like your approach to the known and fixed headers row(Row 1). I am interested in finding a way to search for header of every worksheet that is not necessarily residing on row 1. My initial thoughts were that headers are usually residing one row above the data row(1).

    Any ideas?

    Thanks!

    Kong

    1. The problem would be differentiating the data from the headers. At some point you have to make an assumption about the header. I assume it will be in the first row. However, you could assume that it will contain text, the text will be bold, and that the next row will contain something (data). You could then iterate through the rows until you reach that condition.

  2. Instead of Assumtion that sheet will only read until the last non blank column best to get the maximum columns in the sheet dynamically and put it in a For Loop. That way even if someone leave a blank column in the middle of the sheet it will still search for all the possible columns and their respecting column numbers. See below.

    $UsedCol = $ws.UsedRange.Columns.Count

    For($j=1; $j -le $UsedCol; $j++)
    {
    $Header = $ws.cells.item(1, $j).text
    If ($Header) {
    $Headers.add($Header, $j)
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s