PowerShell and Excel: Creating Your Initial Report

One of the most common methods to generating a report (at least for me) is to write out the data to a CSV file using Export-CSV. While this is a simple and effective method of getting most data out to a report that can be sent to others, there are other ways to accomplish this and provide it in a nicer fashion or combine multiple reports into one workbook that can be sent out to others. This alternative is using Excel to create and format reports prior to being sent out. This article will dive into creating the excel object and then writing data to a worksheet.

Using excel with PowerShell is made by using the available excel.application COM object that will allow us to work with excel to add data and format that data.

$excel = New-Object -ComObject excel.application

While you do not have to do this, but for this example I will make excel visible so it is easier to troubleshoot and see what is happening when I begin adding and formatting data.

$excel.visible = $True

image

Ok, so having Excel open without a workbook or worksheet doesn’t do us much good, so we will dig into the workbooks property and use its Add() method to add a workbook into Excel.

$workbook = $excel.Workbooks.Add()

This adds a default  of 3 worksheets to the workbook that was created. For reference, if you want add another worksheet, you can use the Add() method that is in the Worksheets property of the workbook object we created.

$workbook.Worksheets.Add()

Removing a worksheet requires the knowledge of what worksheet you want to remove and its order in the workbook. This can be confusing when you add a new worksheet into the workbook as shown in the example below.

$i=0;$workbook.Worksheets | %{$i++;[ordered]@{$_.name=$i}}

image

Sheet4, which was added is now the first item in the workbook. Lets say I want to remove sheet1 for whatever reason. We can do that by using the Delete() method.

$excel.DisplayAlerts = $False
$workbook.Worksheets.Item(2).Delete()

Before I attempt to delete this worksheet, I set the DisplayAlerts to $False, otherwise you would receive a confirmation to delete the worksheet. Note that you would not see the confirmation if you had kept the excel visibility to hidden.

Writing to each cell is as simple as knowing the coordinates of the cell you want to write to.  But before that we can simplify what worksheet we are going to write to by getting to that object.

$diskSpacewksht= $workbook.Worksheets.Item(1)

As you can tell by my variable, I am going to go with a common item that is usually reported: Disk Space.

Lets go ahead and rename this worksheet to something that will be more in line with what I am writing to it.

$diskSpacewksht.Name = 'DriveSpace'

image

Since I am looking at drivespace, I will look at the following properties that will be used as the header for the worksheet: DeviceID, VolumeName, Size and FreeSpace.

Starting with the first row and column, I will create the headers in the worksheet using the Cells.Item(<row number>,<column number>) property.

$diskSpacewksht.Cells.Item(1,1) = 'DeviceID'
$diskSpacewksht.Cells.Item(1,2) = 'VolumeName'
$diskSpacewksht.Cells.Item(1,3) = 'Size(GB)'
$diskSpacewksht.Cells.Item(1,4) = 'FreeSpace(GB)'

image

For the header, I had to manually input the cell coordinates, but when we start writing out the data for the report, I can set up a counter for both the row and column to make things a little easier.

$row = 2
$column = 1
Get-CimInstance -ClassName Cim_LogicalDisk | ForEach {
    #DeviceID
    $diskSpacewksht.Cells.Item($row,$column) = $_.DeviceID
    $column++
    #VolumeName
    $diskSpacewksht.Cells.Item($row,$column) = $_.VolumeName
    $column++
    #Size
    $diskSpacewksht.Cells.Item($row,$column) = ($_.Size /1GB)
    $column++
    #FreeSpace
    $diskSpacewksht.Cells.Item($row,$column) = ($_.FreeSpace /1GB)
    #Increment to next Row and reset Column
    $row++
    $column = 1
}

image

I increment each column so that I am able to write each value in the correct cell. At the end of writing all of the data for the object, I then increment the row and reset the column value back to 1 and start the process over again until completion.

This report is just about finished, but before I save it off I want to clean it up just a bit by auto-sizing everything so the cells display everything.

$usedRange = $diskSpacewksht.UsedRange						
$usedRange.EntireColumn.AutoFit() | Out-Null

image

That is better, now we can save this off to a file that you can send or review later on.

$workbook.SaveAs("C:\temp\DiskSpace.xlsx")
$excel.Quit()

So there you have it, you now have a simple report saved to an excel spreadsheet. I know, it is not really that exciting yet and you can do the same thing with Export-CSV, but my next article on this will show you how to format the data to make it more impressive looking and future articles will show how to write to other worksheets, making a link to other worksheets as well as creating graphs to further enhance your report! Until then feel free to use my code and experiment on your own.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in Excel, Office, powershell and tagged , , , , . Bookmark the permalink.

21 Responses to PowerShell and Excel: Creating Your Initial Report

  1. santhoshkumar V says:

    Great work. Excellent share.

  2. thanks this was exactly what i was looking for and worked perfectly!

  3. Saquib says:

    Method invocation failed because [System.__ComObject] does not contain a method named ‘SavesAs’.

  4. Ed Dhyne says:

    Everything works for me except the SaveAs

    Save it to a file so you can send or review it.

    $workbook.SavesAs(“C:\temp\DiskSpace.xlsx”)
    $excel.Quit()
    Method invocation failed because [System.__ComObject] does not contain a method named
    ‘SavesAs’.
    At line:38 char:1
    + $workbook.SavesAs(“C:\temp\DiskSpace.xlsx”)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (SavesAs:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

  5. maxflipz@live.com says:

    Seems there’s a problem with $diskSpacewksht= $workbook.Worksheets.Item(1), when I tried running the script with that it would fail. I changed $workbook to $excel and it works as expected.

    Also, I added another Foreach, changed the Get-Ciminstance to Get-WmiObject to read the same data from many remote machines.

    $servers = GC C:\MyFile.Txt
    $excel = New-Object -ComObject excel.application
    $excel.visible = $True
    $workbook = $excel.Workbooks.Add()
    $sheet= $excel.Worksheets.Item(1)
    $sheet.Cells.Item(1,4) = ‘FreeSpace(GB)’
    $row = 2
    $column = 1

    ForEach ($server in $servers){
    $sheet.Name = ‘DriveSpace’
    $sheet.Cells.Item(1,1) = ‘DeviceID’
    $sheet.Cells.Item(1,2) = ‘VolumeName’
    $sheet.Cells.Item(1,3) = ‘Size(GB)’
    $sheet.Cells.Item($row,$column) = $server
    $row++
    $column++

    Try{
    GWMI -Class Win32_LogicalDisk -ComputerName $server | ForEach {
    #DeviceID
    $sheet.Cells.Item($row,$column) = $.DeviceID
    $column++
    #VolumeName
    $sheet.Cells.Item($row,$column) = $
    .VolumeName
    $column++
    #Size
    $sheet.Cells.Item($row,$column) = ($.Size /1GB)
    $column++
    #FreeSpace
    $sheet.Cells.Item($row,$column) = ($
    .FreeSpace /1GB)
    #Increment to next Row and reset Column
    $row++
    $column = 1
    }}
    Catch {
    Write-Warning “Something broke on $server”}
    }

  6. Andrew says:

    Hi Boe,

    Great Post!. I would need some more of your guidance as I am not a scripter.

    What I would need to do via Powershell is to read the many columns (Servername, IP, Disk Size, etc) of a worksheet from one workbook which house the data (one row per server name with about 600 rows) and copy those columns to another workbook. I would like to create one new worksheet per row using the server name as reference. However in the new worksheets the order is reversed by that I mean the rows will contain new titles (Host, IP Address, HDD, etc) and the columns will contain the data.

    How can I do this?

    Would you be open to exchanging some emails? I could sent you the excel files if you would like.

    Many thanks,
    Andrew

  7. Walter Francia says:

    I’m trying to use this to gather OS info from a few servers. How do I run this against multiple machines? I tried to do something like “Get-Content C:\Servers.txt | ForEach-Object {Get-WmiObject -Class Win32_OperatingSystem -Namespace root/cimv2 -ComputerName $_}” , but can’t get it to loop through the listed servers from the text file. It works when I just run it against local machine.

  8. Pingback: Working with Excel and Powershell | PowerShell Tutorial

  9. Pingback: Powershell и Excel. Часть 2: форматирование ячеек | SMEARG

  10. Dongwei says:

    Great walk-through! Very easy to follow.

  11. Pingback: Powershell и Excel. Часть 1: заполнение таблицы « SMEARG

  12. Pingback: PowerShell and Excel: Adding a Chart and Header Filter to a Report | Learn Powershell | Achieve More

  13. Pingback: PowerShell and Excel: Adding Some Formatting To Your Report | Learn Powershell | Achieve More

  14. Shay Levy says:

    Excellent, as always. One thing though, you don’t have to rely on sheets order, you can refer to them, and remove them, by Name instead of their index.

    $workbook.Worksheets.Item(‘Sheet4’).Delete()

    • Boe Prox says:

      Awesome! Thanks for pointing that out, Shay! That will make things a little easier. 🙂

    • megamorf says:

      True, that works, but it’s not usable fot non-English versions of Office. I had a hard time automating a German Office 2007/2010 because Microsoft don’t use English properties and terms in their program. Therefore, Boe Prox’ solution is better in my opinion.

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