Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
ReeceGoding opened this issue Oct 4, 2024 · 2 comments
Assignees

Comments

@ReeceGoding
Copy link

ReeceGoding commented Oct 4, 2024

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.

@dfinke
Copy link
Owner

dfinke commented Oct 4, 2024

I need to see the data. A simple repro of the issue.

@ReeceGoding
Copy link
Author

@dfinke I've made an edit. Is that better?

@dfinke dfinke self-assigned this Oct 4, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants
@dfinke @ReeceGoding and others