This project has moved. For the latest updates, please go here.

Powershell: FillStyle & colour

Aug 20, 2012 at 4:23 PM
Edited Aug 20, 2012 at 4:25 PM

I'm having a little difficulty with following statements in Powershell (inputting data, merging and font formatting until here have worked just fine), I wonder if someone could point to me what is going wrongly? Should the statement be declared more expressively?

$WorkSheet.Cells.item("A2:S4").Style.Fill.PatternType = ExcelFillStyle.Solid
$WorkSheet.Cells.item("A2:S4").Style.Fill.BackgroundColor.SetColor(Color.Red)

 - The fillstyle gives this error:

The term 'ExcelFillStyle.Solid' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

+ $WorkSheet.Cells.item("A2:S4").Style.Fill.PatternType = ExcelFillStyle.Solid <<<<
    + CategoryInfo          : ObjectNotFound: (ExcelFillStyle.Solid:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

- The Color.Red:
Missing ')' in method call.

+ $WorkSheet.Cells.item("A2:S4").Style.Fill.BackgroundColor.SetColor( <<<< Color.Red)
    + CategoryInfo          : ParserError: (CloseParenToken:TokenId) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall

Aug 25, 2012 at 11:34 AM

The easy way to do this is to read the epplus.dll with .NET reflector and understand what goes on in the structures.
With that knowledge, the solution is as follows:

$WorkSheet.Cells.item("A2:S4").Style.Fill.PatternType = "Solid" # or you could just write "=1"
$WorkSheet.Cells.item("A2:S4").Style.Fill.BackgroundColor.SetColor("Red")