ImportExcel icon indicating copy to clipboard operation
ImportExcel copied to clipboard

Export-Excel: The Title parameter seems to break multiple aspects of importing tables

Open ReeceGoding opened this issue 4 months ago • 2 comments

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,

  1. I get warnings from Export-Excel when my query returns zero rows. I do not get these when not setting Title.

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.

  1. 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.
  2. Despite the above, AutoFilter, AutoSize, FreezeTopRow and Style 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.

ReeceGoding avatar Oct 04 '24 15:10 ReeceGoding