Coming Soon: GET-IT: Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET-IT: Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET
PowerShell

Integrating Microsoft Excel with PowerShell: Build a Basic Report

Not too long ago I wrote about Integrating Microsoft Word with PowerShell, but I know that after text files, the most used tool for Windows administration is Microsoft Excel. Based on what I see in forums and talking with people at conferences, there is a lot of information tucked away in Excel spreadsheets. There’s no shortage of people who want to save information to Excel or read from it. Normally, I tell people that if they are using PowerShell, they can simply export results to a CSV file and then open that in Excel, but perhaps you really need an Excel file.

I’m going to take the next few columns and explore how to integrate Microsoft Excel with Windows PowerShell. Today we’ll go into building a basic report with Microsoft Excel and Windows PowerShell. In part two, I’ll go over how to create a richer Excel document. Later, in part three I’ll show you how to read data from an Excel file.

How to Integrate Microsoft Excel with PowerShell

Microsoft Excel has a very complex COM object model that we can interact with in Windows PowerShell. Let’s start at the beginning and create an object for the Excel application.

​PS C:\> $xl=New-Object -ComObject "Excel.Application"

Right now Excel is running in the background, even though there isn’t an interactive window.

Sponsored Content

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

​PS C:\> get-process excel

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    203      23    16392      24340   267     0.28   1280 EXCEL

Next, we’ll create a workbook object.

​PS C:\> $wb=$xl.Workbooks.Add()

And from here, we’ll create a worksheet object.

​PS C:\> $ws=$wb.ActiveSheet

You can pipe any of these objects to Get-Member to learn more about them. For now, let’s make the application visible.

​PS C:\> $xl.Visible=$True

When you get to scripting you don’t need this, but it will help to see the results from our PowerShell commands. There are a variety of ways to enter information into the spreadsheet. For simple tasks, I’m going to show how to do this using the cell objects.

​PS C:\> $cells=$ws.Cells

Each cell object can be retrieved as an item using row and column coordinates.

​PS C:\> $cells.item(1,1)

If you try this, you’ll get a lot of information. We’ll go ahead and enter some information into the first cell.

​PS C:\> $cells.item(1,1)=$env:computername

Your computer name should now be in cell A1. Let’s add a bit more data.

​PS C:\> $cells.item(1,2)=$env:username
PS C:\> $cells.item(2,1)=(get-Date)

It’s really that easy. You just need to keep track of where you are. If you need some basic formatting, you can use each cell’s Font property.

​PS C:\> $cells.item(1,1).font.bold=$True
PS C:\> $cells.item(1,2).font.bold=$True
PS C:\> $cells.item(1,1).font.size=16
PS C:\> $cells.item(1,2).font.size=16

When it is time, we can save the file using the SaveAs() method from the WorkBook object.

​PS C:\> $wb.SaveAs("c:\work\test.xlsx")

To fully exit, we’ll close the workbook and quit Excel.

​PS C:\> $wb.Close()
PS C:\> $xl.Quit()

If you check processes, you might still see Excel running but it should exit within 5 to 10 minutes, at least based on my experience. Those are the basic nuts and bolts, but before we wrap up, let me leave you with a sample script you can download from the site to pull all of this material together.

​Param([string]$computer=$env:computername)
​#get disk data
$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"

$xl=New-Object -ComObject "Excel.Application" 

$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet

$cells=$ws.Cells

$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

#define some variables to control navigation
$row=3
$col=1

#insert column headings
"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$drive.Freespace/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Freespace/$drive.size)
    $cells.item($Row,$col).NumberFormat="0.00%"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size
    $cells.item($Row,$col).NumberFormat="0.00%"
}

$xl.Visible=$True

$filepath=Read-Host "Enter a path and filename to save the file"

if ($filepath) {
    $wb.SaveAs($filepath)
}

This is something you might want to do in PowerShell: use WMI to get disk usage information and record it into an Excel spreadsheet. The script takes a computername as a parameter but defaults to the localhost. It then uses Get-WMIObject to retrieve disk information.

The first part of the script should look familiar, as it creates the Excel application and objects. The script inserts a title in A1.

$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

The main part of the script needs to take the data for each logical disk and write a few properties to Excel. Because I’m going to use cell objects with references to rows and columns, I’ll define some variables to help with navigation.

​$row=3
$col=1

With these, I can insert my table headings.

​"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

Each time through the loop, $col is increased by one so I keep “moving” to the right. Now I need to iterate through the collection of disks. Each time through, I need to move “down” one row and make sure I start at the first column.

​foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
...

I then insert the appropriate WMI property into the appropriate cell. By incrementing the column, I can “move” to the left and continue the process. Notice I’m also formatting the value of each cell using the NumberFormat property. One way to discover things like this is to record an Excel macro making all the changes you want, and then look at the resulting VBA code. With a little practice, you can translate the commands into PowerShell.

When the script has finished writing the data to the spreadsheet, I make it visible and prompt the user for a file name. If one is entered, then the file is saved. Otherwise, you can adjust the spreadsheet further and then save it manually. This demo script does not automatically close Excel. The script should produce something like Figure 1.

PowerShell export to Excel

Figure 1: A PowerShell Generated Excel Report

Conclusion

I know this is a lot to tackle, so I’ll wrap it up for now. In Part 2, we’ll look at some additional formatting options and other items that take advantage of Microsoft Excel. If you are going to create Excel documents from PowerShell, you might as well make the most of it.

Related Topics:

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (12)

12 responses to “Integrating Microsoft Excel with PowerShell: Build a Basic Report”

  1. […] Adobe Acrobat X Standard Upgrade form Acrobat Standard 7/8/9 Search and reuse scanned content with greater accuracy through improved optical character recognition.Easily save data in PDF documents to Microsoft Excel spreadsheets to expedite reuse of content.Seamlessly integrate PDF files into Microsoft SharePoint workflows.Convert PDF files to Microsoft Word documents with improved formatting and layout preservation.Search and reuse scanned content with greater accuracy through improved optical character recognitionEasily save data in PDF documents to Microsoft Excel spreadsheets to expedite reuse of contentSeamlessly integrate PDF files into Microsoft SharePoint workflows Experience the power of Acrobat Dynamic PDF to create and shareAcrobat X Standard — B0046DMZH8 Upgrade to Adobe Acrobat X Professional To install an upgrade version of Acrobat X Standard software, you must have an eligible earlier version of Adobe Adobe Acrobat X Standard Upgrade form Acrobat Standard 7/8/9 Search and reuse scanned content with …t-Standard-Upgrade-form/dp/B0046DFEVM%3FSubscriptionId%3DAKIAIA7L7WLQJ2J4QTDA%26tag%3Dthwedecebl-20%26linkCode%3Dxm2%26camp%3D2025%26creative%3D165953%26creativeASIN%3DB0046DFEVM" rel="nofollow">Adobe Acrobat X Standard Upgrade form Acrobat Standard 7/8/9 Search and reuse scanned content with greater accuracy through improved optical character recognition.Easily save data in PDF documents to Microsoft Excel spreadsheets to expedite reuse of content.Seamlessly integrate PDF files into Microsoft SharePoint workflows.Convert PDF files to Microsoft Word documents with improved formatting and layout preservation.Search and reuse scanned content with greater accuracy through improved optical character recognitionEasily save data in PDF documents to Microsoft Excel spreadsheets to expedite reuse of contentSeamlessly integrate PDF files into Microsoft SharePoint workflows Experience the power of Acrobat Dynamic PDF to create and shareAcrobat X Standard — B0046DMZH8 Upgrade to Adobe Acrobat X Professional To install an upgrade version of Acrobat X Standard software, you must have an eligible earlier version of Adobe WordPress › Error html { background: #f9f9f9; } body { background: #fff; color: #333; font-family: sans-serif; margin: 2em auto; padding: 1em 2em; -webkit-border-radius: 3px; border-radius: 3px; border: 1px solid #dfdfdf; max-width: 700px; } #error-page { margin-top: 50px; } #error-page p { font-size: 14px; line-height: 1.5; margin: 25px 0 20px; } #error-page code { font-family: Consolas, Monaco, monospace; } ul li { margin-bottom: 10px; font-size: 14px ; } a { color: #21759B; text-decoration: none; } a:hover { color: #D54E21; } .button { font-family: sans-serif; text-decoration: none; font-size: 14px !important; line-height: 16px; padding: 6px 12px; cursor: pointer; border: 1px solid #bbb; color: #464646; -webkit-border-radius: 15px; border-radius: 15px; -moz-box-sizing: content-box; -webkit-box-sizing: content-box; box-sizing: content-box; background-color: #f5f5f5; background-image: -ms-linear-gradient(top, #ffffff, #f2f2f2); background-image: -moz-linear-gradient(top, #ffffff, #f2f2f2); background-image: -o-linear-gradient(top, #ffffff, #f2f2f2); background-image: -webkit-gradient(linear, left top, left bottom, from(#ffffff), to(#f2f2f2)); background-image: -webkit-linear-gradient(top, #ffffff, #f2f2f2); background-image: linear-gradient(top, #ffffff, #f2f2f2); } .button:hover { color: #000; border-color: #666; } .button:active { background-image: -ms-linear-gradient(top, #f2f2f2, #ffffff); background-image: -moz-linear-gradient(top, #f2f2f2, #ffffff); background-image: -o-linear-gradient(top, #f2f2f2, #ffffff); background-image: -webkit-gradient(linear, left top, left bottom, from(#f2f2f2), to(#ffffff)); background-image: -webkit-linear-gradient(top, #f2f2f2, #ffffff); background-image: linear-gradient(top, #f2f2f2, #ffffff); } […]

  2. […] Integrating Microsoft Excel with PowerShell: Basic Reports Is there a faster way to parse an excel document with Powershell How Can I Use Windows PowerShell to Delete All the .TMP Files on a Drive? – Hey, Scripting Guy! Blog Hey, Scripting Guy! How can I use Windows PowerShell to locate and delete all the .tmp files on a drive? grep – How do I output lines that do not match 'this_string' using Get-Content and Select-String in PowerShell Each one of us has our own style in the way we accomplish tasks, scripting is no different. Modular Scripting in PowerShell I recently had the need to go through a text file and prepend a string to every line, and while that's ok for a couple of lines it doesn't work for a file with hundreds and thousands of lines. So here's the powershell command to do that: get-content file.txt | foreach-object {add-content output.txt "text_to_add $_"} Powershell script for prepending and appending text Les bases de PowerShell | My-PowerShell Aide Mémoire PowerShell Séquences d’échappement Le caractère d’échappement Windows PowerShell est l’accent grave (`). Comparing Python and PowerShell DBA Scripting Av rating: Total votes: 46 Total comments: 15 Comparing Python and PowerShell DBA Scripting 03 August 2007 Topic: replace special characters | PowerShell.org by willbs at 2013-04-06 11:52:14 PowerShell commands | PowerShell Hello, How do I use Powershell to join rows (lines) from a text file to create only one line? 10 fundamental concepts for PowerShell scripting – TechRepublic PowerShell can save you a lot of time on Windows admin tasks — but to use it effectively, you need to understand how it works. Here's a crash course in PowerShell basics to get you started. Windows PowerShellDu beau boulot Windows PowerShell: Du beau boulot PowerShell SI5_PowerShell PowerShell Cheat Sheets & Bootdisks,ect.. powershell [+] ExempleScript connexion objet numérique Hey, Scripting Guy! powershell adventures library Informatique ajout premier regmaker PowerShell tutorial centre objects Python class tutorial Tutoriels dynamisez programmation programmes informatiques pour apprendre logiciel linotte code vidéo Online Coding Tutorials algorithm problets Learning foundations Tuto useful tutoriels sites Des techniques vidéo tutoriel Site du Zér0 dynamisez apprenez créer JavaScript dynamisez accueil tutoriel Code : apprendre learn alsacréations javascript Programmations pour le web dynamisez concevez validation Developpement webdev Creation de site web portail joomla Cours Javascript réintroduction JavaScript cours référence SDZ débutez dynamisez Design Programmation sites réaliser javascript ajouter référence Javascript dynamisez bonnes sites Référence javascript mozilla API/MASHUP tutorial Javascript berseth control référence programmation cours conseils sites Python interactive electric Python tutorial Python online Prof geek javascript flipbook Tutorials & Lecture websites reference Tech and internet raspberry linear promises Javascript Informatique home • iPhone & iPad app • android app • contact • blog • facebook • twitter to experience pearltrees activate javascript. […]

  3. It’s generally a good idea to keep your variables (creating a variable, then using it somewhere) in the same case.
    E.g. in your examples you use “$row++” then in the rest of it using “cells.item($Row,$col)=$drive.Freespace/1GB”

Leave a Reply

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: