powershellclosedxml

Need to set a cell background color with ClosedXML in a PowerShell script

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

Solution

  • 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.