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
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}}
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'
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)'
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
}
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
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.
Learn Powershell | Achieve More 
Pingback: Vulnerability Management Pt. 1 | Infosec Noobz
can we use PSexcel module instead using excel.
Great work. Excellent share.
thanks this was exactly what i was looking for and worked perfectly!
Method invocation failed because [System.__ComObject] does not contain a method named ‘SavesAs’.
I am getting this error
Exception calling “Add” with “0” argument(s): “Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD)
)”
At line:3 char:1
+ $workbook = $excel.Workbooks.Add()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
A null key is not allowed in a hash literal.
At line:4 char:47
+ $i=0;$workbook.Worksheets | %{$i++;[ordered]@{$_.name=$i}}
Tried running in x86 version of powershell.
and i am usine powershell 4.0
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
What version of PowerShell are you running as well as what version of Excel?
PSVersion = 4.0
Microsoft Excel 2013(15.0.4675.1000) MSO(15.0.4675.1002)64-bit
Try method “SavesAs” instead of “SaveAs”.
$workbook.SavesAs(“DiskSpace.xlsx”)
“SaveAs” worked. Thanks
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”}
}
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
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.
Pingback: Working with Excel and Powershell | PowerShell Tutorial
Pingback: Powershell и Excel. Часть 2: форматирование ячеек | SMEARG
Great walk-through! Very easy to follow.
Thank you!
Pingback: Powershell и Excel. Часть 1: заполнение таблицы « SMEARG
Pingback: PowerShell and Excel: Adding a Chart and Header Filter to a Report | Learn Powershell | Achieve More
Pingback: PowerShell and Excel: Adding Some Formatting To Your Report | Learn Powershell | Achieve More
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()
Awesome! Thanks for pointing that out, Shay! That will make things a little easier. 🙂
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.