I am using PowerShell 3 and ClosedXML to write file information into an excel worksheet. In one of the cells I need to set the background color to a light blue (cornflower blue or 219, 229, 249 in the RGB). I have tried a variety of methods and, so far, no luck. According to the ClosedXML site, I should be able to reference the XLColor object. I have also tried numerous variations of setting the background color.
$cell.Style.Fill.BackgroundColor.Color(219, 229, 249)
or $cell.Style.Fill.SetBackgroundColor(6) or $cell.Style.Fill.SetBackgroundColor(XLColor.Blue)
Any suggestions would be greatly appreciated. Most of the examples are C# which usually translate pretty seamlessly but this is one that I have had no luck figuring out.
Thanks Rick Anderson
Update (12:15 AM 4th of July) I cracked the secret, which also helped with working on Borders and am sure will help on other items.
The trick for dealing with colors is to define a variable of type ClosedXML.Excel.XLColor and simultaneously assign the value you want. You can use multiple options in specifying the colors:
$SomeColor = [ClosedXML.Excel.XLColor]::AirForceBlue #Pick a color from the list
$SomeColor = [ClosedXML.Excel.XLColor]::FromArgb() #RGB
$SomeColor = [ClosedXML.Excel.XLColor]::FromColor() #System.Drawing.Color
$SomeColor = [ClosedXML.Excel.XLColor]::FromHtml() #HTML Color
$SomeColor = [ClosedXML.Excel.XLColor]::FromIndex() #numeric index of the colors
$SomeColor = [ClosedXML.Excel.XLColor]::FromKnownColor() #System.Drawing.Color
$SomeColor = [ClosedXML.Excel.XLColor]::FromName() #String name of color
$SomeColor = [ClosedXML.Excel.XLColor]::FromTheme() #XLThemeColor
Here is an example using the FromArgb option:
#Define the color variables I need. In this case a color for the hash column and a
#color for the Title/Header row.
$HashColor = [ClosedXML.Excel.XLColor]::FromArgb(219, 229, 249)
$TitleColor = [ClosedXML.Excel.XLColor]::FromArgb(221, 217, 195)
. . . Do some stuff
#Format the Header Row
$headerRange = $worksheetObject.Range("a1","d1")
$headerRange.Style.Font.Bold=$True
$cell = $worksheetObject.Range($headerRange)
$cell.Style.Fill.BackgroundColor =$TitleColor
. . . Do some stuff
#Write file information row
$row++
$worksheetObject.Cell($row,1).Value=$File.Name
$worksheetObject.Cell($row,2).Value=$FileType
$worksheetObject.Cell($row,3).Value=$strFileLen
$stringRow = $row.ToString()
$FirstCell = "A" + $stringRow
$LastCell = "D" + $stringRow
$Range = $FirstCell + ":" + $LastCell
$cell = $worksheetObject.Range($Range)
$cell.Style.Fill.BackgroundColor =$HashColor
This also works if you need to work with borders but you can do it directly.
$cell.Style.Border.OutsideBorder = [ClosedXML.Excel.XLBorderStyleValues]::Thin
$cell.Style.Border.InsideBorder = [ClosedXML.Excel.XLBorderStyleValues]::Thin
You can also use the same thing to change the border color
$cell.Style.Border.BottomBorderColor = [ClosedXML.Excel.XLColor]::CornflowerBlue
This wasn't the clearest solution but figuring out this method will help a lot with future ClosedXML work in PowerShell. Hopefully this helps someone out there.