Cmdlet

Add-OfficeExcelTable

Aliases: ExcelTable

Module: PSWriteOffice
Aliases: ExcelTable
Inputs: OfficeIMO.Excel.ExcelDocument System.Object
Source: AddOfficeExcelTableCommand.cs:38 Edit on GitHub

Writes tabular data to the current worksheet and formats it as an Excel table.

Syntax

Add-OfficeExcelTable
[-Address <String>]
[-CollectionSeparator <String>]
[-ColumnFormat <Hashtable>]
[-Culture <CultureInfo>]
[-Document <ExcelDocument>]
[-FlattenOptions <OfficeObjectFlattenOptions>]
[-InputObject] <Object>
[-Name <String>]
[-NoAutoFilter]
[-NoAutoFit]
[-NoFreeze]
[-NoHeader]
[-NoRowStripes]
[-NoTable]
[-NoTableStyle]
[-PassThru]
[-Sheet <OfficeSheetSelector>]
[-ShowColumnStripes]
[-ShowFirstColumn]
[-ShowLastColumn]
[-TableStyle <ExcelTableStyle>]
[-View <Normal|Transpose>]
[<CommonParameters>]

Parameter set: Default

Description

Accepts objects, dictionaries, DataTable/DataView/IDataReader inputs, or DataRow sequences and writes them into an Excel table with optional styling.

Examples

Write objects as a table

New-OfficeExcel -Path .\Services.xlsx {
    ExcelSheet 'Services' {
        ExcelTable (Get-Service)
    }
}

Writes the services as a themed table with a frozen header, filters and fitted columns.

Place two named tables on a sheet

$targets = Import-OfficeCsv -Path .\Data\Q3-Targets.csv
$actuals = Import-OfficeCsv -Path .\Data\Q3-Actuals.csv
New-OfficeExcel -Path .\Reports\Q3-Performance.xlsx {
    ExcelSheet 'Performance' {
        ExcelTable $targets -Name Targets -Address A1
        ExcelTable $actuals -Name Actuals -Address G1 -ColumnFormat @{ Attainment = 'Percent' }
    }
}

Writes this quarter's targets and actuals side by side as named tables, so charts, formulas and later imports can refer to them by name.

Parameters

-Address String optional: A1 address of the target cell.
-CollectionSeparator String optional: Text inserted between collection values flattened into one cell.
-ColumnFormat Hashtable optional: Maps column headers to number-format presets, format codes or per-column option hashtables.
-Culture CultureInfo optional: Culture used for value conversion and number-format presets.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-FlattenOptions OfficeObjectFlattenOptions optional: Nested-value projection policy; accepts a native options object or hashtable.
-InputObject Object requiredposition: 0pipeline: True (ByValue): Objects, dictionaries or native tabular data supplied directly or through the pipeline.
-Name String optional: Name to assign to the table.
-NoAutoFilter SwitchParameter optional: Omits table filter buttons.
-NoAutoFit SwitchParameter optional: Retains native column widths without fitting them to the data.
-NoFreeze SwitchParameter optional: Keeps the header row scrollable.
-NoHeader SwitchParameter optional: Writes data without a header row.
-NoRowStripes SwitchParameter optional: Disables alternating row bands. An explicit false value enables them.
-NoTable SwitchParameter optional: Writes plain cells without creating a native Excel table.
-NoTableStyle SwitchParameter optional: Creates the table without a named visual style.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-ShowColumnStripes SwitchParameter optional: Enables alternating column bands; explicit false clears them.
-ShowFirstColumn SwitchParameter optional: Emphasizes the first table column; explicit false removes the emphasis.
-ShowLastColumn SwitchParameter optional: Emphasizes the last table column; explicit false removes the emphasis.
-TableStyle ExcelTableStyle optional: Built-in table style to apply.; Possible values: TableStyleLight1, TableStyleLight2, TableStyleLight3, TableStyleLight4, TableStyleLight5, TableStyleLight6, TableStyleLight7, TableStyleLight8, TableStyleLight9, TableStyleLight10, TableStyleLight11, TableStyleLight12, TableStyleLight13, TableStyleLight14, TableStyleLight15, TableStyleLight16, TableStyleLight17, TableStyleLight18, TableStyleLight19, TableStyleLight20, TableStyleLight21, TableStyleMedium1, TableStyleMedium2, TableStyleMedium3, TableStyleMedium4, TableStyleMedium5, TableStyleMedium6, TableStyleMedium7, TableStyleMedium8, TableStyleMedium9, TableStyleMedium10, TableStyleMedium11, TableStyleMedium12, TableStyleMedium13, TableStyleMedium14, TableStyleMedium15, TableStyleMedium16, TableStyleMedium17, TableStyleMedium18, TableStyleMedium19, TableStyleMedium20, TableStyleMedium21, TableStyleMedium22, TableStyleMedium23, TableStyleMedium24, TableStyleMedium25, TableStyleMedium26, TableStyleMedium27, TableStyleMedium28, TableStyleDark1, TableStyleDark2, TableStyleDark3, TableStyleDark4, TableStyleDark5, TableStyleDark6, TableStyleDark7, TableStyleDark8, TableStyleDark9, TableStyleDark10, TableStyleDark11
-View OfficeTableView optional: Projection to apply before writing the table.; Possible values: Normal, Transpose

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.