Set-SLTableStyle


Synopsis

Excel offers to style your data tables via some built-in styles. This cmdlet help the user choose a built-in table style.

Syntax

Description

Excel offers to style your data tables via some built-in styles. This cmdlet helps the user choose a built-in table style.
In order to set a tablestyle excel would need to know the startrowindex,startcolumnindex,endrowindex and endcolumnindex,
or simply the range eg: A1:B10

If you want to apply a style to existing data in a worksheet then you would need to obtain the table values namely
startrowindex,startcolumnindex,endrowindex,endcolumnindex or the range and then feed those values to the parameters.

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

Name of the Worksheet that is to be processed.

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

-TableStyle

There are about 55 built-in styles to choose from, ranging from light-dark.
While there is a way to set a table style there isnt however a method to remove an applied style.
Use tab or intellisense to choose from a list of possible values:
&;39;light1&;39;,&;39;light2&;39;,&;39;light3&;39;,&;39;light4&;39;,&;39;light5&;39;,&;39;light6&;39;,&;39;light7&;39;,&;39;light8&;39;,&;39;light9&;39;,&;39;light10&;39;,&;39;light11&;39;,&;39;light12&;39;,&;39;light13&;39;,&;39;light14&;39;,&;39;light15&;39;,&;39;light16&;39;,&;39;light17&;39;
,&;39;light18&;39;,&;39;light19&;39;,&;39;light20&;39;,&;39;light21&;39;,&;39;Medium1&;39;,&;39;Medium2&;39;,&;39;Medium3&;39;,&;39;Medium4&;39;,&;39;Medium5&;39;,&;39;Medium6&;39;,&;39;Medium7&;39;,&;39;Medium8&;39;,&;39;Medium9&;39;,&;39;Medium10&;39;,&;39;Medium11&;39;,&;39;Medium12&;39;,&;39;Medium13&;39;,&;39;Medium14&;39;
,&;39;Medium15&;39;,&;39;Medium16&;39;,&;39;Medium17&;39;,&;39;Medium18&;39;,&;39;Medium19&;39;,&;39;Medium20&;39;,&;39;Medium21&;39;,&;39;Medium22&;39;,&;39;Medium23&;39;,&;39;Medium24&;39;,&;39;Medium25&;39;,&;39;Medium26&;39;,&;39;Medium27&;39;,&;39;Medium28&;39;
,&;39;Dark1&;39;,&;39;Dark2&;39;,&;39;Dark3&;39;,&;39;Dark4&;39;,&;39;Dark5&;39;,&;39;Dark6&;39;,&;39;Dark7&;39;,&;39;Dark8&;39;,&;39;Dark9&;39;,&;39;Dark10&;39;,&;39;Dark11&;39;

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

-TotalRowFunction

Choose from the following as one of the valid options for a totalrowfunction.
Possible Values : &;39;Sum&;39;,&;39;Count&;39;,&;39;Average&;39;,&;39;Product&;39;,&;39;Maximum&;39;,&;39;Minimum&;39;,&;39;CountNumbers&;39;,&;39;StandardDeviation&;39;,&;39;Variance&;39;
Note: Excel 2007 does not contain the &;39;Product&;39; function.

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

-TotalColumnIndex

The data column to which the totalrowfunction has to be applied.Valid values start from 1 irrespective of the column from which the data table starts..

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

-TotalRowLabel

The label text indicating the calculated value from the total row function Eg. &;39;Average Sales Revenue&;39;.

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

-TotalRowLabelColumnIndex

The column index to set the TotalRowLabel on Eg. 2 or 4 . values start from 1 irrespective of the column from which the data table starts.

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

-StartRowIndex

Row number which marks the start of the data table.

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

-StartColumnIndex

Column number which marks the start of the data table.

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

-EndRowIndex

Row number which marks the end of the data table.

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

-EndColumnIndex

Column number which marks the end of the data table.

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

-Range

The range that constitutes the table data eg: A1:b10.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
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

An instance of MyFirstDoc is stored in a variable named doc.
Service data is first exported to sheet2 and then a built-in tablestyle named &;39;Dark10&;39; is set on the table.
This can probably be condensed into a oneliner but for the sake of simplicity the activity has been broken down into several steps.

Example 2

Disk Information is exported to a worksheetnamed &;39;disk&;39;. The &;39;sum&;39; function is applied to the contents of the column 6(FreeSpace)

Example 3

An instance of MyFirstDoc is stored in a variable named doc.
Service,process and disk data from the localcomputer is then exported to worksheets
&;39;service&;39;,&;39;process&;39; & &;39;Disk&;39; with styles Medium16,17 & 28 applied respectively.

Example 4

Get-Service data is piped to group-object to be grouped by the status property and the results are stored in a variable named &;39;service&;39;.
export the running services starting from column number 2 to 5.
Stopped services are exported to columns 6-8 in the same worksheet.
Since we are applying two different table styles to the same worksheet we need to manually find out the table values which is why we
save the document open it find out the start and end values for the running and stopped service ranges and then apply our style.
Note: We cannot pipe export-sldata to set-sltablestyle because the start and end values are calculated for the entire worksheet
and so the same style will be applied to both tables.

Related links