How to add a Chart correctly?

[monoeagle]

Hi,

that is the Goal I want to build:

In the Examples are the following:

`Import-Module c:\temp\importExcel\ImportExcel.psd1 -Force
$data = Import-CSV -Path C:\temp\EXPORT_NEW.csv -Delimiter ";"
$data | Export-Excel -Path C:\temp\EXCEL\EXPORT_NEW.xlsx -WorksheetName "AllData" -AutoSize -AutoFilter

$xlSourcefile = "C:\temp\EXCEL\EXPORT_NEW.xlsx"
Export-Excel $xlSourcefile -WorksheetName 'Status' -AutoSize -AutoFilter

$xlpkg = Export-Excel $xlSourcefile -WorksheetName AllData -AutoNameRange -PassThru

$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Summary -Activate

$params = @{
Worksheet = $xlpkg.Summary
Title = "Summe Aktionen"
ChartType = 'ColumnClustered'

# XRange    = "Data!A2:A10"
# YRange    = "Data!C2:C10"

XRange    = "AllData!B1:B10"
YRange    = "AllData!A1:A10"

#XRange    = 'Data!Region'
#YRange    = 'Data!Units'

}

Add-ExcelChart @params

Close-ExcelPackage $xlpkg`

XRange = "Data!B1:B10" YRange = "Data!A1:A10"

Was just a test to see what happened. I just get an empty Chart.

$xlpkg = Export-Excel $xlSourcefile -WorksheetName AllData -AutoNameRange -PassThru

Is it necessary to pipe data to Export-Excel as in the example?
If I do so I also get an empty Chart. That's why I removed the "$data |"

The data is already in the Worksheet AllData?

The finished Table is a lot larger. From A -> AA.
On other Tabs I want to build Charts with spezific Charts, for example Column "C" and "D" or "E" till "I".

But the Question is the same.

How to define the XRange and the YRange in the Script?

greetz

Works:
single column
XRange = "AllData!Name"
YRange = "AllData!Count"

Fails:
multiple columns

[monoeagle]

Multiple Columns works as well. ;)

$c = New-ExcelChartDefinition -Title "Status"
-ChartType ColumnClustered -XRange "Processes[Name]"
-YRange "Processes[Status_Active]","Processes[Status_Cancelled]" `
-SeriesHeader "Status_Active","Status_Cancelled"

$c1 = New-ExcelChartDefinition -Title "Aktionen" -ChartType ColumnClustered
-XRange "Processes[Name]" -YRange "Processes[Action_Ordered]","Processes[Action_Modified]","Processes[Action_Cancelled]","Processes[Action_Failure]","Processes[Action_Empty]"
-SeriesHeader "Action_Ordered","Action_Modified","Action_Cancelled","Action_Failure","Action_Empty"

$data | Export-Excel -Path $xlSourcefile -AutoSize -TableName Processes -ExcelChartDefinition $c,$c1 -WorksheetName Auswertung -AutoFilter
`

And looking at the new-excelchartdefinition ich returned to add-excelchart. ;)

`
$xlpkg = $data | Export-Excel $xlSourcefile -WorksheetName AllData -AutoNameRange -PassThru -AutoFilter
$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Status -Activate

$paramsChart1 = @{
Worksheet = $xlpkg.Status
Title = "Status"
ChartType = 'ColumnClustered'
XRange = "AllData!Name"
YRange = "AllData!Status_Active","AllData!Status_Cancelled"
SeriesHeader = "Status_Active","Status_Cancelled"
}

Add-ExcelChart @paramsChart1
$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Count -Activate

$paramsChart2 = @{
Worksheet = $xlpkg.Count
Title = "Count"
ChartType = 'ColumnClustered'
XRange = "AllData!Name"
YRange = "AllData!Count"
SeriesHeader = "Count"
}

Add-ExcelChart @paramsChart2
$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Actions -Activate

$paramsChart3 = @{
Worksheet = $xlpkg.Actions
Title = "Aktionen"
ChartType = 'ColumnClustered'
XRange = "AllData!Name"
YRange = "AllData!Action_Ordered","AllData!Action_Modified","AllData!Action_Cancelled","AllData!Action_Failure","AllData!Action_Empty"
SeriesHeader = "Action_Ordered","Action_Modified","Action_Cancelled","Action_Failure","Action_Empty"
}
Add-ExcelChart @paramsChart3
Close-ExcelPackage $xlpkg
`

And all worked fine! With the params Block.

Sometimes writedown a second time is usefull. ;)

[dfinke]

@monoeagle Thanks for trying the PowerShell Excel module, glad you got it resolved!

We had an interesting and rather unconventional approach to problem-solving – we called it Rubber duck debugging.

We would explain our coding or design issue to an inanimate but empathetic listener, our 'Rubber Duck.' 😆