Formatting a MS Excel note with PowerShell
INFO ABOUT POWERSHELL AND MS EXCEL
Warning!
Comments and Notes are two different things (now). In the commands below we use the Comment property and we change the formatting in the notes of the cells. Yeah it can be confusing. More info about the difference between Comments and Notes in recent versions of MS Excel can you find here.
First you need an existing MS Excel workbook. For testing purposes you can create an empty file with some random data and a note:
After that you can change the contents either with COM objects or the ImportExcel module.
1. COM OBJECT
$FilePath = "C:\Temp\Test.xlsx"
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($FilePath)
$WorkSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Columns.Item(3).Rows.Item(1).Comment.Text()
Output:
You can split it into an array (`n stands for new line):
It’s more interesting to call Get-Member to see what you can do here:
Here you can see all possible methods and properties that Comment has.
We want to play around with the Shape property. If you enter the same command for Shape, you’ll see even more possibilities. If you continue to discover this command, you’ll come eventually to this:
…Comment.Shape.TextFrame.Characters().Font
Then Get-Member:
For example if you want to change the size of the first 5 characters of the text in your note then you can just call:
$WorkSheet.Columns.Item(3).Rows.Item(1).Comment.Shape.TextFrame.Characters(1,5).Font.Size = 9To change the color of your text you have to enter this (3 is the code for the color red – more info about the Microsoft ColorIndex property):
$WorkSheet.Columns.Item(3).Rows.Item(1).Comment.Shape.TextFrame.Characters(1,5).Font.ColorIndex = 3 TIP
Don’t forget to close the opened Excel file, if you finished your work with it!
$Workbook.Save()
$Workbook.Close()
$Excel.Quit()
A working example:
$FilePath = "C:\Temp\Test.xlsx"
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($FilePath)
$WorkSheet = $Workbook.Sheets.Item("Random_number")
# addressing cells method #1:
$WorkSheet.Columns.Item(3).Rows.Item(1).Comment.Shape.TextFrame.AutoSize = $true
# addressing cells method #2:
# Cells.Item(row, column)
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.AutoSize = $true
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(1,5).Font.Bold = $true
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(6,5).Font.Bold = $false
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(1,5).Font.Size = 9
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(1,5).Font.ColorIndex = 5
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(1,5).Font.Name = "Broadway"
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(6,5).Font.Color = [System.Drawing.Color]::DarkSalmon
# Italic: only from the 2. line: Character(11)
$WorkSheet.Cells.Item(1,3).Comment.Shape.TextFrame.Characters(11).Font.Italic = $true
# save and close file
$Workbook.Save()
$Workbook.Close()
# exit the application
$Excel.Quit()
# release the com object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$Excel) | Out-Null
The result should be this:
2. IMPORTEXCEL MODULE
If you want to get the same result with the ImportExcel module, you have to re-create the note in the excel cell.
$FilePath = "C:\Temp\Test.xlsx"
$Excel = Open-ExcelPackage -Path $FilePath
$WorkSheet = $Excel.Workbook.Worksheets["Random_number"]
$myComment = $WorkSheet.Cells["C1"].Comment
$myComment.RichText
# ->
# output:
<#
Text : AdminTips:
PreserveSpace : False
Bold : True
Italic : False
Strike : False
UnderLine : False
VerticalAlign : None
Size : 9
FontName : Tahoma
Color : Color [Empty]
_collection : {Tahoma, Tahoma}
Text :
My first note
PreserveSpace : True
Bold : False
Italic : False
Strike : False
UnderLine : False
VerticalAlign : None
Size : 9
FontName : Tahoma
Color : Color [Empty]
_collection : {Tahoma, Tahoma}
#>
With RichText you get a “_collection” of the Comments / Notes. You can use the Add(), Insert(), Remove(), InsertAt(), RemoveAt() methods to update this collection.
TIP
Insert() doesn’t insert at a specific character
(unlike the COM object approach) – it’s section-based!
The working code:
$FilePath = "C:\Temp\Test.xlsx"
$Excel = Open-ExcelPackage -Path $FilePath
$WorkSheet = $Excel.Workbook.Worksheets["Random_number"]
$myComment = $WorkSheet.Cells["C1"].Comment
# first we remove both parts of the existing note ("AdminTips:" , "My first note"):
$myComment.RichText.RemoveAt(0)
$myComment.RichText.RemoveAt(0)
# part #1
$part1 = $myComment.RichText.Insert(0, "Admin")
$part1.FontName = "Broadway"
$part1.Bold = $true
$part1.Color = [System.Drawing.Color]::Blue
# par #2
$part2 = $myComment.RichText.Insert(1, "Tips:")
# the same:
#$part2 = $myComment.RichText.Add("Tips:")
$part2.FontName = "Tahoma"
$part2.Bold = $false
$part2.Color = [System.Drawing.Color]::DarkSalmon
# part #3
# style is inherited! (e.g. FontName is still "Tahoma")
$part3 = $myComment.RichText.Add("`nMy first note")
$part3.Italic = $true
$part3.Color = [System.Drawing.Color]::Black
# automatically resize the textframe of the note:
$myComment.AutoFit = $true
$Excel.Save()
# close and show the excel file:
Close-ExcelPackage $Excel -Show
And the result:
A really good source of examples is here.
WRAPPING UP / CONCLUSION
It’s quite easy to use the ImportExcel module, but if it comes to changing the formatting of a note in an MS Excel cell, the winner is definitely the COM object method. In my opinion it is much easier with it to position the cursor and select the amount of characters you want to format.
