< Back
Add-ExcelTable
Post
NAME Add-ExcelTable
SYNOPSIS
Adds Tables to Excel workbooks.
SYNTAX
Add-ExcelTable [-Range] <ExcelRange> [[-TableName] <String>] [[-TableStyle] {None | Custom | Light1 | Light2 |
Light3 | Light4 | Light5 | Light6 | Light7 | Light8 | Light9 | Light10 | Light11 | Light12 | Light13 | Light14 |
Light15 | Light16 | Light17 | Light18 | Light19 | Light20 | Light21 | Medium1 | Medium2 | Medium3 | Medium4 |
Medium5 | Medium6 | Medium7 | Medium8 | Medium9 | Medium10 | Medium11 | Medium12 | Medium13 | Medium14 | Medium15
| Medium16 | Medium17 | Medium18 | Medium19 | Medium20 | Medium21 | Medium22 | Medium23 | Medium24 | Medium25 |
Medium26 | Medium27 | Medium28 | Dark1 | Dark2 | Dark3 | Dark4 | Dark5 | Dark6 | Dark7 | Dark8 | Dark9 | Dark10 |
Dark11}] [[-TotalSettings] <Hashtable>] [-ShowHeader] [-ShowFilter] [-ShowTotal] [-ShowFirstColumn]
[-ShowLastColumn] [-ShowRowStripes] [-ShowColumnStripes] [-PassThru] [<CommonParameters>]
DESCRIPTION
Unlike named ranges, where the name only needs to be unique within a sheet, Table names must be unique in the
workbook.
Tables carry formatting and by default have a filter.
The filter, header, totals, first and last column highlights can all be configured.
PARAMETERS
-Range <ExcelRange>
The range of cells to assign to a table.
Required? true
Position? 1
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TableName <String>
The name for the Table - this should be unqiue in the Workbook - auto generated names will be used if this is
left empty.
Required? false
Position? 2
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TableStyle <TableStyles>
The Style for the table, by default "Medium6" is used
Required? false
Position? 3
Default value Medium6
Accept pipeline input? False
Accept wildcard characters? false
-ShowHeader [<SwitchParameter>]
By default the header row is shown - it can be turned off with -ShowHeader:$false.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowFilter [<SwitchParameter>]
By default the filter is enabled - it can be turned off with -ShowFilter:$false.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowTotal [<SwitchParameter>]
Show total adds a totals row. This does not automatically sum the columns but provides a drop-down in each to
select sum, average etc
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TotalSettings <Hashtable>
A HashTable in the form ColumnName = "Average"|"Count"|"CountNums"|"Max"|"Min"|"None"|"StdDev"|"Sum"|"Var" -
if specified, -ShowTotal is not needed.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ShowFirstColumn [<SwitchParameter>]
Highlights the first column in bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowLastColumn [<SwitchParameter>]
Highlights the last column in bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowRowStripes [<SwitchParameter>]
By default the table formats show striped rows, the can be turned off with -ShowRowStripes:$false
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowColumnStripes [<SwitchParameter>]
Turns on column stripes.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
If -PassThru is specified, the table object will be returned to allow additional changes to be made.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.Table.ExcelTable
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName
$WS is a worksheet, and $dataRange is a string describing a range of cells - for example "A1:Z10". This range
which will become a table, named $TableName
-------------------------- EXAMPLE 2 --------------------------
PS\\> Add-ExcelTable -Range $ws.cells[$($ws.Dimension.address)] -TableStyle Light1 -TableName Musictable
-ShowFilter:$false -ShowTotal -ShowFirstColumn
Again $ws is a worksheet, range here is the whole of the active part of the worksheet. The table style and name
are set, the filter is turned off, and a "Totals" row added, and first column is set in bold.
RELATED LINKS
SYNOPSIS
Adds Tables to Excel workbooks.
SYNTAX
Add-ExcelTable [-Range] <ExcelRange> [[-TableName] <String>] [[-TableStyle] {None | Custom | Light1 | Light2 |
Light3 | Light4 | Light5 | Light6 | Light7 | Light8 | Light9 | Light10 | Light11 | Light12 | Light13 | Light14 |
Light15 | Light16 | Light17 | Light18 | Light19 | Light20 | Light21 | Medium1 | Medium2 | Medium3 | Medium4 |
Medium5 | Medium6 | Medium7 | Medium8 | Medium9 | Medium10 | Medium11 | Medium12 | Medium13 | Medium14 | Medium15
| Medium16 | Medium17 | Medium18 | Medium19 | Medium20 | Medium21 | Medium22 | Medium23 | Medium24 | Medium25 |
Medium26 | Medium27 | Medium28 | Dark1 | Dark2 | Dark3 | Dark4 | Dark5 | Dark6 | Dark7 | Dark8 | Dark9 | Dark10 |
Dark11}] [[-TotalSettings] <Hashtable>] [-ShowHeader] [-ShowFilter] [-ShowTotal] [-ShowFirstColumn]
[-ShowLastColumn] [-ShowRowStripes] [-ShowColumnStripes] [-PassThru] [<CommonParameters>]
DESCRIPTION
Unlike named ranges, where the name only needs to be unique within a sheet, Table names must be unique in the
workbook.
Tables carry formatting and by default have a filter.
The filter, header, totals, first and last column highlights can all be configured.
PARAMETERS
-Range <ExcelRange>
The range of cells to assign to a table.
Required? true
Position? 1
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TableName <String>
The name for the Table - this should be unqiue in the Workbook - auto generated names will be used if this is
left empty.
Required? false
Position? 2
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TableStyle <TableStyles>
The Style for the table, by default "Medium6" is used
Required? false
Position? 3
Default value Medium6
Accept pipeline input? False
Accept wildcard characters? false
-ShowHeader [<SwitchParameter>]
By default the header row is shown - it can be turned off with -ShowHeader:$false.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowFilter [<SwitchParameter>]
By default the filter is enabled - it can be turned off with -ShowFilter:$false.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowTotal [<SwitchParameter>]
Show total adds a totals row. This does not automatically sum the columns but provides a drop-down in each to
select sum, average etc
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TotalSettings <Hashtable>
A HashTable in the form ColumnName = "Average"|"Count"|"CountNums"|"Max"|"Min"|"None"|"StdDev"|"Sum"|"Var" -
if specified, -ShowTotal is not needed.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ShowFirstColumn [<SwitchParameter>]
Highlights the first column in bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowLastColumn [<SwitchParameter>]
Highlights the last column in bold.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowRowStripes [<SwitchParameter>]
By default the table formats show striped rows, the can be turned off with -ShowRowStripes:$false
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowColumnStripes [<SwitchParameter>]
Turns on column stripes.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
If -PassThru is specified, the table object will be returned to allow additional changes to be made.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.Table.ExcelTable
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName
$WS is a worksheet, and $dataRange is a string describing a range of cells - for example "A1:Z10". This range
which will become a table, named $TableName
-------------------------- EXAMPLE 2 --------------------------
PS\\> Add-ExcelTable -Range $ws.cells[$($ws.Dimension.address)] -TableStyle Light1 -TableName Musictable
-ShowFilter:$false -ShowTotal -ShowFirstColumn
Again $ws is a worksheet, range here is the whole of the active part of the worksheet. The table style and name
are set, the filter is turned off, and a "Totals" row added, and first column is set in bold.
RELATED LINKS