Data caption in chart?

[azra1l]

Hi there,

Is it possible to have export-excel add data captions to a chart?

Like, for charttype BarStacked100:

$chart = New-ExcelChartDefinition -Title values `
    -ChartType BarStacked100 `
    -XRange 'values[Number]' `
    -YRange 'values[X]', 'values[Y]' `
    -SeriesHeader 'x', 'y'
$values | Export-Excel -Path $file -TableName values -AutoSize -ExcelChartDefinition $chart -Show

show the actual values in all segments of the chart:

[dfinke]

Please could you post a complete script with data and I can better investigate

[azra1l]

Sure,
this is the full example code, is this enough?

$file = "$($Env:Temp)\test.xlsx"
$values = @(
    [PSCustomObject] @{
        'Number' = 'one'
        'X'      = 50
        'Y'      = 232
        'total'  = 582
    }
    [PSCustomObject] @{
        'Number' = 'two'
        'X'      = 10
        'Y'      = 35
        'total'  = 45
    }
    [PSCustomObject] @{
        'Number' = 'three'
        'X'      = 3
        'Y'      = 7
        'total'  = 10
    }
)
$chart = New-ExcelChartDefinition -Title values `
    -ChartType BarStacked100 `
    -XRange 'values[Number]' `
    -YRange 'values[X]', 'values[Y]' `
    -SeriesHeader 'x', 'y'
$values | Export-Excel -Path $file -WorksheetName values -ClearSheet -TableName values -AutoSize -ExcelChartDefinition $chart -Show

[azra1l]

By the way, the feature is called "Data Labels" in the english version of Excel (2019 in this case):

[dfinke]

Bummer, didn't surface the a -ShowValue on the chart definition.

Would need to do that and plum the property setting logic.

Workaround:

$file = "$PSScriptRoot\script.xlsx"
Remove-Item $file -ErrorAction SilentlyContinue

$values = @(
    [PSCustomObject] @{
        'Number' = 'one'
        'X'      = 50
        'Y'      = 232
        'total'  = 582
    }
    [PSCustomObject] @{
        'Number' = 'two'
        'X'      = 10
        'Y'      = 35
        'total'  = 45
    }
    [PSCustomObject] @{
        'Number' = 'three'
        'X'      = 3
        'Y'      = 7
        'total'  = 10
    }
)


$excel = $values | Export-Excel -Path $file -WorksheetName values -ClearSheet -TableName values -AutoSize -PassThru

# $chart = New-ExcelChartDefinition -Title values `
#     -ChartType BarStacked100 `
#     -XRange 'values[Number]' `
#     -YRange 'values[X]', 'values[Y]' `
#     -SeriesHeader 'x', 'y'

$p = @{
    Title        = 'values' 
    ChartType    = 'BarStacked100 '
    XRange       = 'values[Number]' 
    YRange       = 'values[X]', 'values[Y]' 
    SeriesHeader = 'x', 'y'
}

$chart = Add-ExcelChart @p -Worksheet $excel.values -PassThru

$chart.Series.DataLabel | ForEach-Object { $_.ShowValue = $true }

Close-ExcelPackage $excel -Show

[azra1l]

Hi, yes that works for me. Thanks! :)