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.