Formatting spreadsheet output

[hmiller10]

I am creating a series of reports based on Active Directory queries. The query results are written to a system.data.datatable. I would like export the datatable to a spreadsheet, using an existing Excel table style but with a few modifications.

Modification # 1 - I would like to format the header row (row 2, below the title row) so that the horizontal alignment is centered.

Modification # 2 - I would like to be able to dynamically create a range of cells that contain actual data, again excluding the title row and header row, and apply separate formatting to that dynamically defined range.

How can I go about doing this?

Examples of the scripts that I am currently using are in my Github.com/hmiller10/PowerShell-Tidbits repo. under the Active Directory folder.

Any advice is much appreciated.

[dfinke]

You can start with Export-Excel and use the -PassThru switch.

$xlpkg = $data | Export-Excel ./reports.xlsx -Title 'The Title' -PassThru

<#
Review this function, parameters and search the Examples folder in the repo
This lets you tweak many of the attributes of the cells in the sheet

Set-ExcelRange $xlpkg.Sheet -Range "C:C" 
#>
Close-ExcelPackage $xlpkg -Show