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.

image

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'

image

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

image

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

image

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

image

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.

image

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
}

image

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

image

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.

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.

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

  1. Pingback: PowerShell : CSV – Disk Space Report – Excel | Prashanth Jayaram

  2. MsterBill says:

    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?

  3. Jonathan says:

    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.

  4. brimur69 says:

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

  5. brimur69 says:

    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

  6. Ajay says:

    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

  7. runCMD says:

    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.

  8. Jay says:

    Thanks Great script

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

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

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

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