Export-Excel -Pivottable -ChartTitle

[szz22]

Discussed in https://github.com/dfinke/ImportExcel/discussions/1241

^{Originally posted by **szz22** September 2, 2022} Hy a want to add Chart Title to Pivot Table generetad witj Export-Excel but it doesn't work, can you give me some advice how can I add ChartTitle to PivotChart from commandline. Thank you,

[dfinke]

Please post a script of what you are trying to do that shows the issue

[szz22]

Script Export-Excel -Path "..." -WorksheetName ... -PivotTableName '...' -PivotRows '...' -PivotData @{... = '...' } -ChartType PieExploded3D -IncludePivotTable -IncludePivotChart -ShowCategory -ShowPercent -NoLegend

the option -ChartTitle wont work

[dfinke]

Try this:

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = './test.xlsx'
Remove-Item $xlfile -ErrorAction SilentlyContinue

$pivotTableParams = @{
    PivotTableName    = "SalesDataByRegion"
    SourceWorkSheet   = 'Sheet1'
    PivotRows         = @("Region")
    PivotData         = @{'Units' = 'sum' }
    IncludePivotChart = $true
    ChartTitle        = "Sales by Region"
    Activate          = $true
}

$ptd = New-PivotTableDefinition @pivotTableParams
$data | Export-Excel $xlfile -TableName SalesData -PivotTableDefinition $ptd -Show

[szz22]

Hy, Thank you for your quick answer it works the Title the script that I made from your example is $xlfile = "......" $pivotTableParams = @{ PivotTableName = "...." SourceWorkSheet = '....' PivotRows = @("....") PivotData = @{'....' = 'Sum' } IncludePivotChart = $true ChartTitle = "....." Activate = $true NoLegend = $true Showpercent = $true ShowCategory = $true } $ptd = New-PivotTableDefinition @pivotTableParams Export-Excel $xlfile -PivotTableDefinition $ptd -HideSheet ....

The other problem I have is where can i define -ChartType PieExploded3D, and where can i rename my pivot columns Header (Row labels, Total) Thank you.

[dfinke]

This is using PowerShell splatting. You can find the other params supported on New-PivotTableDefinition using tab completion.

This module cannot rename those Pivot headers. I believe they are created when Excel opens the file.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = './test.xlsx'
Remove-Item $xlfile -ErrorAction SilentlyContinue

$pivotTableParams = @{
    PivotTableName    = "SalesDataByRegion"
    SourceWorkSheet   = 'Sheet1'
    PivotRows         = @("Region")
    PivotData         = @{'Units' = 'sum' }
    IncludePivotChart = $true
    ChartTitle        = "Sales by Region"
    ChartType         = "PieExploded3D"
    Activate          = $true
}

$ptd = New-PivotTableDefinition @pivotTableParams  -
$data | Export-Excel $xlfile -TableName SalesData -PivotTableDefinition $ptd -Show

[szz22]

Thank you for your answer, My question is if I could a Legend Series Value that works on Excel Practicaly I want 2 columns 3 columns to show: $xlfile = "......" $pivotTableParams = @{ PivotTableName = "...." SourceWorkSheet = '....' PivotRows = @("....") PivotData = @{'....' = 'Sum' ; '....' ='Min'} IncludePivotChart = $true ChartTitle = "....." Activate = $true NoLegend = $true Showpercent = $true ShowCategory = $true } $ptd = New-PivotTableDefinition @pivotTableParams Export-Excel $xlfile -PivotTableDefinition $ptd -HideSheet ....

Line 6 but the result apears enumerated behind PivotRows and not besides 1 2 3 I want 1 2 3

Thank you

[szz22]

Ok, I resolved $PivotDataToColumn = $true Thank you.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.