Center and Middle Align all cells in an Excel worksheet-CodePudding

I am using the ImportExcel PowerShell module to export data from a SQL server to an Excel spreadsheet. I've got the entire process worked out but I would like to have the entire worksheet - Sheet1 as the default - to be both center and middle aligned.

I am looking at the FormatCellStyles examples listed in their Git but none make sense to me as how to apply these two alignments to the entire sheet.

ApplyFormatInScriptBlock

ApplyStyle

PassInScriptBlock

The code is pretty straightforward until I get to the alignment issue - and I get stuck. Is it possible to center and middle align an entire worksheet using the ImportExcel module?

Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Excel -Path $ExcelFile -FreezeTopRow -BoldTopRow -AutoSize

CodePudding user response：

I use the ImportExcel module often, but never for formatting so I decided to have a little fun and take on your challenge. Unfortunately I did not find a way to apply the alignments using Export-Excel directly, however you can do the following afterwards to the saved workbook

$excelPath = 'C:\temp\formattest.xlsx'
$excel = Open-ExcelPackage $excelPath
$excel.Workbook.Worksheets['Sheet1'].Cells | Set-Format -HorizontalAlignment Center -VerticalAlignment Center
Close-ExcelPackage $excel

Looking over some of the examples you provided it looks like something like this may be the more expected approach:

$excelpkg = Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | 
    Select-Object * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | 
        Export-Excel -FreezeTopRow -BoldTopRow -AutoSize -PassThru
$ws = $excelpkg.Workbook.Worksheets['Sheet1']
$wsUsedRange = $ws.Dimension.Address
Set-ExcelRange -Worksheet $ws -Range $wsUsedRange -HorizontalAlignment Center -VerticalAlignment Center
Close-ExcelPackage $excelpkg -Show -SaveAs $ExcelFile

Passthru the output from Export-Excel to a variable,
apply the changes you would like using Set-ExcelRange
and then save and close the workbook using Close-ExcelPackage