Set-SLConditionalFormattingOnText


Synopsis

Apply conditional formatting Iconset to text instead of numbers.

Syntax

Description

Apply conditional formatting Iconset to text instead of numbers.
Excel iconsets are applied on numbers and there is currently no built-in method to apply it on text or strings.
This cmdlet takes a range containing text, inserts a new column before it and then applies conditional formatting on it.
You can only apply text formatting on a column that has 3 or less unique values in a given column. Eg: “Working”,”Stopped”,”Disabled”

Parameters

-WorkBookInstance

Instance of an excel document that can be used for editing.

Required? True
Position? 0
Default value  
Accept pipeline input? true (ByValue)
Accept wildcard characters? False

-WorksheetName

This is the name of the worksheet that contains the cell range where formatting is to be applied.

Required? True
Position? 1
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-Range

The range of cells containing text to which conditional formatting has to be applied.

Required? True
Position? 2
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-IconSet

Built-in Iconset styles.
Use tab or intellisense to select from a range of possible values.
Default value is – ThreeSymbols
Possible values are:
&;39;ThreeArrows&;39;,&;39;ThreeArrowsGray&;39;,&;39;ThreeFlags&;39;,&;39;ThreeSigns&;39;,&;39;ThreeStars&;39;,
&;39;ThreeSymbols&;39;,&;39;ThreeSymbols2&;39;,&;39;ThreeTrafficLights1&;39;,&;39;ThreeTrafficLights2&;39;,&;39;ThreeTriangles&;39;

Required? False
Position? 2
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-Properties

String containing comma seperated text values. EG: “Working,Stopped,Disabled”

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-IconColumnHeader

The header text to be set for the new column contining icons. Default value is – “Icon”

Required? False
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ReverseIconorder

Reverses the order in which icons are applied.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-ShowIconsOnly

Will show just the icons instead of icons and numbers.
The default value is true.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

Inputs

String,SpreadsheetLight.SLDocument

Outputs

SpreadsheetLight.SLDocument

Notes

Examples

Example 1

This will insert a column before column F and the conditional formatting Icon set &;39;ThreeSymbols&;39; will be applied to the new column.
The cell corresponding to value working will be &;39;Green&;39;, stopped will be &;39;yello\orange&;39; and disabled in &;39;Red&;39;.
Note: Column F becomes column G.

Example 2

Servicedata is exported to sheet3, workbook is saved and closed. We then open the workbook to determine the data range for conditionalformatting.
We apply conditional formatting on the state column which has 2 properties “Running” and “stopped” save and close.
We open the document again to determine the datarange corresponding to the startmode column which has 3 properties “Auto”,”Manual” & “Disabled”

Related links