ImportExcel
ImportExcel copied to clipboard
Export-Excel: The Title parameter seems to break multiple aspects of importing tables
I'm trying to export SQL results to Excel spreadsheets with nice titles. My final goal is something lovely like
Import-Module ImportExcel
Import-Module dbatools
$ExcelParams = @{
Path = $SavePath
WorksheetName = $SheetName
AutoFilter = $true
AutoSize = $true
BoldTopRow = $true
FreezeTopRow = $true
Append = $true
Title = "MyTitle"
Style = (New-ExcelStyle -BorderAround Thin -BorderBottom Thin -BorderTop Thin -BorderLeft Thin -BorderRight Thin)
}
Invoke-DbaQuery @SqlParams |
Select * -ExcludeProperty ItemArray, RowError, RowState, Title, HasErrors |
Export-Excel @ExcelParams
This almost works. However,
- I get warnings from
Export-Excel
when my query returns zero rows. I do not get these when not settingTitle
.
WARNING: Failed adding autofilter to worksheet 'worksheetnamehere': The property 'AutoFilter' cannot be found on this object. Verify that the property exists and can be set. WARNING: Failed autosizing column of worksheet 'worksheetnamehere': You cannot call a method on a null-valued expression.
- Row three, i.e. the row after the imported table's column names, become bold. This is not expected behaviour. Without a
Title
, the column names become bold and that is also what I would expect here. - Despite the above,
AutoFilter
,AutoSize
,FreezeTopRow
andStyle
seem to work perfectly.
Reproducible example of points 2 and 3, not needing SQL:
Import-Module ImportExcel
$SavePath = "YourPathHere"
$data = @"
From,To,RDollars,RPercent,MDollars,MPercent,Revenue,Margin
Atlanta,New York,3602000,.0809,955000,.09,245,65
New York,Washington,4674000,.105,336000,.03,222,16
Chicago,New York,4674000,.0804,1536000,.14,550,43
New York,Philadelphia,12180000,.1427,-716000,-.07,321,-25
New York,San Francisco,3221000,.0629,1088000,.04,436,21
New York,Phoneix,2782000,.0723,467000,.10,674,33
"@
$ExcelParams = @{
Path = $SavePath
WorksheetName = "WillHaveRowThreeBold"
AutoFilter = $true
AutoSize = $true
BoldTopRow = $true
FreezeTopRow = $true
Append = $true
Title = "MyTitle"
Style = (New-ExcelStyle -BorderAround Thin -BorderBottom Thin -BorderTop Thin -BorderLeft Thin -BorderRight Thin)
}
$data |
Convert-FromCSV |
Select * |
Export-Excel @ExcelParams
To replicate point 1, delete all of $data
except for the column list.