PowerShell and Excel: Adding Some Formatting To Your Report

Continuing from my previous Excel article, I will now take the report that I created and make more pleasant to the eyes and really show why this is different from just piping the output into Export-CSV to a CSV file.

If you recall, the finished product from my previous article gave you essentially the same type of report that you would find from a typical CSV report generated by Export-CSV. The report was a common report showing the drive spaces of a system.

There will be some re-hashing of code as I am going to perform the same query and the same type of inputting of values in each cell for Excel. The differences are how I format the cells in Excel to give it a nicer look.

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True

#Add a workbook
$workbook = $excel.Workbooks.Add()

#Remove other worksheets
1..2 | ForEach {
    $Workbook.worksheets.item(2).Delete()
}

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Name = 'DiskInformation'
$serverInfoSheet.Activate() | Out-Null

Nothing new here as I am connecting to the Excel COM object and then removing all of my workbooks.

Instead of first creating my headers for the data for each drive, I want to add a title to announce what type of report this is.

#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$serverInfoSheet.Cells.Item($row,$column)= 'Disk Space Information'

Ok, so I can’t really call this a title by any means. We need to format this and give a more “title” look. I will be using the Font property of the cell object to make this look a lot better.

$serverInfoSheet.Cells.Item($row,$column).Font.Size = 18
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$serverInfoSheet.Cells.Item($row,$column).Font.Name = "Cambria"
$serverInfoSheet.Cells.Item($row,$column).Font.ThemeFont = 1
$serverInfoSheet.Cells.Item($row,$column).Font.ThemeColor = 4
$serverInfoSheet.Cells.Item($row,$column).Font.ColorIndex = 55
$serverInfoSheet.Cells.Item($row,$column).Font.Color = 8210719

Now that is more like it! This gives it a little more pop and better separates itself from the rest of the data in the worksheet. Here I made the font bold, changed the font type to Cambria as well as updating some colors on the font as well. For those paying attention, this is the same as setting the Style to Title, which can also be done by the following code:

$range = $serverInfoSheet.Range("a1","h2")
$range.Style = 'Title'

However, there is just one more thing that I want to do to clean it up a bit.

$range = $serverInfoSheet.Range("a1","g2")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

What I’ve done is I selected an extra row as well as selecting as many columns as I know I will be using with my headers and data. Once I have that, I merged everything and then set the verticalalignment to align at the top. So how in the world did I know how to use the correct value for the top alignment when it is not so easily readable (-4160)?

I first ran the following command to figure out the type of Enum that I needed to work with:

$range | Get-Member -Name verticalalignment

TypeName: System.__ComObject#{00020846-0000-0000-c000-000000000046}

Name              MemberType Definition
—-              ———- ———-
VerticalAlignment Property   Variant VerticalAlignment () {get} {set}

Next I went out to the MSDN site for the excel COM object and looked up the enumeration of this type. Unfortunately, I was not able to get the values that relate to the type of alignment. The solution was to work with the Enum using the following code:

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) | 
select @{n="Name";e={"$_"}},value__

VerticalAlignment

Name	value__
xlVAlignTop	-4160
xlVAlignJustify	-4130
xlVAlignDistributed	-4117
xlVAlignCenter	-4108
xlVAlignBottom	-4107

Now I know exactly what value is needed to get the alignment right! This technique will be used a number of times during this article to quickly find out what is the correct value needed to configure a specific property.

Next up we get to add the headers for each data set, but this time with a few additions to really let it stand out as a header.

#Increment row for next set of data
$row++;$row++

#Save the initial row so it can be used later to create a border
$initalRow = $row

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item($row,$column)= 'Computername'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'DeviceID'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'VolumeName'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'TotalSize(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'UsedSpace(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'FreeSpace(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'PercentFree'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True

Making the font Bold and adjusting the background color (using the Interior.ColorIndex property of each cell) really helps to make the headers appear more prominent on the worksheet.

Now it is time to add the data into the worksheet!

#Increment Row and reset Column back to first column
$row++
$Column = 1

#Get the drives
$diskDrives = Get-CimInstance -ClassName Cim_LogicalDisk

#Process each disk in the collection and write to spreadsheet
ForEach ($disk in $diskDrives) {
    $serverInfoSheet.Cells.Item($row,$column)= $disk.__Server
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.DeviceID
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= $disk.VolumeName
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.Size /1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))
    
    #Check to see if space is near empty and use appropriate background colors
    $range = $serverInfoSheet.Range(("A{0}"  -f $row),("G{0}"  -f $row))
    $range.Select() | Out-Null
    
    #Determine if disk needs to be flagged for warning or critical alert
    If ($disk.FreeSpace -lt 65GB -AND ($disk.FreeSpace / $disk.Size) -lt 75) {
        #Critical threshold 
        $range.Interior.ColorIndex = 3
    } ElseIf ($disk.FreeSpace -lt 80GB -AND ($disk.FreeSpace / $disk.Size) -lt 80) {
        #Warning threshold 
        $range.Interior.ColorIndex = 6
    }
    
    #Increment to next row and reset Column to 1
    $Column = 1
    $row++
}

I am using PowerShell V3 to get the data with Get-CIMInstance; for V2 you should still use Get-WmiObject.

Some big changes here. Instead of just having plain old text with no color. I decided to track which drives might be having issues with space. Ok, I know that this is not the best representation of running out of space, but as you can see in the code, I can set a threshold based off of free space and percentage free to make a determination if the disk space is Critical or a Warning and highlight the entire row accordingly. I also hard code the range of the row as it will never change to make it easier to highlight the row. This makes it easier now to quickly locate drives that need some attention and resolve the space issue.

We are not done yet! I want to add some borders to this to give it a cleaner look.

$row--
$dataRange = $serverInfoSheet.Range(("A{0}"  -f $initalRow),("G{0}"  -f $row))
7..12 | ForEach {
    $dataRange.Borders.Item($_).LineStyle = 1
    $dataRange.Borders.Item($_).Weight = 2
}

How did I know which Border index to use to make the borders look like this? By using the same trick that I used with the VerticalAlignment property.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | 
select @{n="Name";e={"$_"}},value__

Border Index

Name	value__
xlDiagonalDown	5
xlDiagonalUp	6
xlEdgeLeft	7
xlEdgeTop	8
xlEdgeBottom	9
xlEdgeRight	10
xlInsideVertical	11
xlInsideHorizontal	12

The same method applies when it comes to deciding what the LineStyle and Weight are for the borders.

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLLineStyle]) | 
select @{n="Name";e={"$_"}},value__

LineStyle

Name	value__
xlContinuous	1
xlDashDot	4
xlDashDotDot	5
xlSlantDashDot	13
xlLineStyleNone	-4142
xlDouble	-4119
xlDot	-4118
xlDash	-4115

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLBorderWeight]) | 
select @{n="Name";e={"$_"}},value__

BorderWeight

Name	value__
xlHairline	1
xlThin	2
xlThick	4
xlMedium	-4138

The last thing that needs to be done is to auto fit everything so there will be no need for re-sizing of the columns.

#Auto fit everything so it looks better
$usedRange = $serverInfoSheet.UsedRange	
$usedRange.EntireColumn.AutoFit() | Out-Null

Much better now! Now we have a nice report that makes it easier to read and identify drives that are in need of attention. Now to save it off and clean up the excel COM object connection and we are set.

Save the file
$workbook.SaveAs("C:\temp\DiskSpace.xlsx")

#Quit the application
$excel.Quit()

#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null

Hopefully this helps you out with creating a nice report using Excel and PowerShell. My next article will touch on adding some more features to this report such as a chart and filtering headers.

12 Responses to PowerShell and Excel: Adding Some Formatting To Your Report

Pingback: PowerShell : CSV – Disk Space Report – Excel | Prashanth Jayaram
MsterBill says:

January 8, 2016 at 11:53 am

This information is great, but for some reason the ‘range’ lines are not working for me at all when formatting the title. Continual out of range errors. I’m not sure if anything as changed in a few years. Any thoughts?

Jonathan says:

September 3, 2015 at 9:31 am

Hi I used your code to create an audit of our servers but the spreadsheet is incorrectly formatted with missing percentages and the chart in the wrong position, also I does not create a new spreadsheet for the chart and instead merges both the audit results and chart to one sheet. Please help as I obtained the script from the below link.
https://gallery.technet.microsoft.com/PowerShell-OS-Inventory-cc4a1664
I am happy to send you a copy of the output results to see for yourself.

brimur69 says:

July 14, 2015 at 9:16 am

Ah, got it, it should be “A{0}” -f $initalRow,”G{0}” -f $row

brimur69 says:

July 14, 2015 at 9:13 am

Is there a typo here? => “A{0f $initalRow),(“G{0}” -f $row))
I get an error with the formatting but I dont know what it should look like. Thanks

Ajay says:

July 11, 2015 at 10:49 am

Great Post!!!

I Would like to know how i can add color to nth char of a cell.
example : @ cell a1 the value is 5678_1.
@cell a2 the value is 7865_2
I want to add color last char (_1) as green
&
add color last char (_2) as red

runCMD says:

September 4, 2014 at 10:49 pm

What an awesome dip in the PowerShell deep-end. export-csv just can’t do what I’m looking for. I followed your tutorial and can reproduce your results. One method I’m not mentally translating well from the $range | gm output – is the property that could be set to direct excel to format cells in the header row – to a 90 degree orientation … i.e. instead of horizontally presenting – I need it to be vertical.
My header row names are from physical memory size – and provide the processor, dim slot locations – like PROC1DIMM1D. The data columns are presented in Gig – so I would only see 1 or 2 digits per cell – I.E. 4, 8, or 16 GB. To compress that visual representation in Excel – I select the header row – right-click, choose format cells, and then choose Alignment – and set the bar pointing up – or 90 degrees. Gives me everything I need in a concise small visual chunk.
From your experience with the methods provided – which gives you the ability to set alignment of the header or – or cell text alignment?
Thanks – really first rate work.

- runCMD says:
  
  September 5, 2014 at 3:51 pm
  
  Hiding in plain sight – found it.
  
  Orientation Property Variant Orientation () {get} {set}
  
Jay says:

March 7, 2014 at 10:28 am

Thanks Great script

Pingback: Powershell и Excel. Часть 2: форматирование ячеек | SMEARG
Pingback: Powershell и Excel. Часть 1: заполнение таблицы « SMEARG
Pingback: PowerShell and Excel: Adding a Chart and Header Filter to a Report | Learn Powershell | Achieve More