How can I use the Sunburst chart?

[Gianlucas94]

Hello,
How can I use the Sunburst chart?

I found that can be made using C#.

var ws = package.Workbook.Worksheets.Add("Sunburst & Treemap Chart");
var range = await LoadSalesFromDatabase(connectionString, ws);

var sunburstChart = ws.Drawings.AddSunburstChart("SunburstChart1");
var r1 = range.SkipRows(1).SkipColumns(3);
var r2 = range.SkipRows(1).TakeColumns(3);
var sbSerie = sunburstChart.Series.Add(r1, r2);
sbSerie.HeaderAddress = ws.Cells["D1"];
sunburstChart.SetPosition(1, 0, 6, 0);
sunburstChart.SetSize(800, 800);
sunburstChart.Title.Text = "Sales";            
sunburstChart.Legend.Add();
sunburstChart.Legend.Position = eLegendPosition.Bottom;
sbSerie.DataLabel.Add(true, true);
sunburstChart.StyleManager.SetChartStyle(ePresetChartStyle.SunburstChartStyle3);

[dfinke]

Thanks for the great question @Gianlucas94. I believe the is an O365 chart feature, so, not doable.

[Gianlucas94]

I was able to do it using Excel COM Objects.

# Ensure ImportExcel module is installed
# Install-Module ImportExcel -Scope CurrentUser -Force

# Your provided data
$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
"@

# Define Excel file path
$excelFile = "C:\Temp\SalesSunburstReport.xlsx"

# Export data to Excel
$data | Export-Excel -Path $excelFile -WorksheetName "SalesData" -AutoSize

# Create Sunburst Chart using Excel COM objects
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Open($excelFile)
$worksheet = $workbook.Sheets.Item("SalesData")

# Define the range for the chart (Region and State as hierarchy, Units as value)
$range = $worksheet.Range("A1:C10")  # Columns A (Region), B (State), C (Units)

# Add the Sunburst Chart
$chart = $worksheet.Shapes.AddChart2(251, 120)  # 120 is for Sunburst chart
$chart.Chart.SetSourceData($range)
$chart.Chart.HasTitle = $true
$chart.Chart.ChartTitle.Text = "Units Sold by Region and State"
$chart.Chart.Legend.Position = -4107  # Bottom

# Save and close the workbook
$workbook.Save()
$excel.Quit()

# Release COM objects to prevent memory leaks
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

But I'll not be able to use it because the enviroment where I'll run the script doesn't have Excel. (Azure Automation Account)

[dfinke]

Create a basic sheet and chart with the PS module useing Export-Excel, use the -PassThru switch.

$data = Import-Csv -Path 'D:/mygit/PowerShellAIAssistant-ScratchPad/VSCode-Agent/sales-total.csv'; $chart = New-ExcelChart -Title 'Sales Total' -ChartType ColumnClustered -XRange 'Region' -YRange 'Total'; $data | Export-Excel -Path 'D:/mygit/PowerShellAIAssistant-ScratchPad/VSCode-Agent/sales-total-chart.xlsx' -ExcelChartDefinition $chart

$chart = New-ExcelChart -Title 'Sales Total' -XRange 'Region' -YRange 'Total' # your data
$xlpkg = $data | Export-Excel -PassThru -ExcelChartDefinition $chart

# set a breakpoint here 👇

$idx = 0

Close-ExcelPackage $xlpkg

You $xlpkg has the object model. Poke around to find the chart and see if you can set it to sunburst. Export-Excel has clues how to get to the collections.

[stale]

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.