How to Specify Custom Colors

[fopson]

Hi, I only recently found out about this module. Before then, I used to produce excel reports primarily in Python but this makes my life much easier since powershell is natively supported on the servers where I need to run these reports. Thanks for the great work on this module!

I have unsuccessfully been tring to find a way to specify custom colors for various settings on a sheet.

For example, let's say I am trying to replicate the colors from the "ColorRef" sheet in the following workbook (this is publically available workbook that can be downloaded from the CIS website): CISControlsv8ChangeLog.xlsx

I used the following code to workout the conditional formating colors:

$XLRefFilePath = "CISControlsv8ChangeLog.xlsx"
$Excel = Open-ExcelPackage -path $XLRefFilePath

## Get Conditional Formatting Rules
$excel.Workbook.Worksheets["ColorRef"].ConditionalFormatting

## Get Conditional Formatting Colors
$excel.Workbook.Worksheets["ColorRef"].ConditionalFormatting.Style.Font | Select-Object -ExpandProperty Color

The returned colors follow the format: Color [A=255, R=135, G=82, B=149]

Q1: How would I specify such a color in ImportExcel?

In Excel itself, when checking the background of the first row, it says "Dark Blue". When setting "Dark Blue" as a Background color in ImportExcel, it returns an error.

Q2: Is there a way to convert a non-standard color name from Excel to a compatible value in ImportExcel?

I have also been trying to get the exact style and color value of the border. The style was fairely easy to get with something like: $excel.Workbook.Worksheets["ColorRef"].SelectedRange.Style.Border.Left.Style

But the border is a little trickier to get. I have tried: $excel.Workbook.Worksheets["ColorRef"].SelectedRange.Style.Border.Left.Color but the result does not look very usable.

Q3: Any tips on how to get the border color compatible with ImportExcel?

Many thanks in advance.

[Mike-Crowley]

See this discussion: https://github.com/dfinke/ImportExcel/discussions/1276

[dfinke]

@Mike-Crowley thanks for posting that

@fopson Thanks using ImportExcel. Glad to hear you've used the Python Excel approach and find this useful.

Does that discussion link help?

I haven't gone down the rabbit hole on these sorts of questions.

Happy to help where I can.

For the Dark Blue try DarkBlue. Yeah, the color coordination is a bit diff from PS to XL. There is a bit of friction.