How to create Excel 100% stacked bar pyramid chart in C#, VB.NET?
This article explains how to create 100% Stacked Bar Pyramid Chart in Excel using Syncfusion Excel (XlsIO) library.
What is 100% Stacked Bar Pyramid Chart?
A variation on a column/bar chart that uses pyramid shapes instead of rectangular columns/bars is called a Pyramid chart. In a 100% stacked bar pyramid chart, each series is horizontally stacked one after the other and the total(cumulative) of each bars always equals to 100%.
100% Stacked Bar Pyramid Chart
To create 100% stacked bar pyramid chart in Excel using XlsIO, you need to do the following steps.
Steps to create 100% Stacked Bar Pyramid Chart
Step 1: Initialize chart
Create a chart object by calling the worksheet.Charts.Add method and specify the chart type to ExcelChartType.Pyramid_Bar_Stacked_100 enumeration value.
C#
//Create the chart
IChartShape chart = worksheet.Charts.Add();
//Set chart type to Pyramid_Bar_Stacked_100
chart.ChartType = ExcelChartType.Pyramid_Bar_Stacked_100;
Step 2: Assign data
Set a range of data from the worksheet to chart’s DataRange property. To plot the series values in column and categories in row, set chart’s IsSeriesInRows property to false.
C#
//Set region of chart data
chart.DataRange = worksheet["A1:D6"];
//Set chart series in column for assigned data region
chart.IsSeriesInRows = false;
Step 3: Apply basic chart elements
Add the basic elements like chart title, data labels and legend with below list of properties.
- ChartTitle of chart object.
- Set DataLabels via DefaultDataPoint.
- Set TRUE to chart’s HasLegend property, to show the legend.
C#
//Set chart title
chart.ChartTitle = "100% Stacked Bar Pyramid Chart";
//Set data labels
IChartSerie serie1 = chart.Series[0];
serie1.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
//Set legend
chart.HasLegend = true;
chart.Legend.Position = ExcelLegendPosition.Bottom;
Step 4: Apply 3D chart elements
Add the 3D chart elements like rotation and elevation.
- Rotation and Elevation of chart object.
C#
//Set rotation and elevation
chart.Rotation = 50;
chart.Elevation = 20;
Applicable properties for 100% Stacked Bar Pyramid Chart
Below is the list of other common properties applicable for 100% stacked bar pyramid chart.
- GapWidth
- GapDepth
- BarShapeBase & BarShapeTop (to change the shape of column/bar)Note:
Applying properties apart from the mentioned list might throw exception or the changes will not be reflected in the output document because those properties are not related to 100% stacked bar pyramid chart.
To know more about creating charts with various settings using Syncfusion Excel (XlsIO) library, please refer the documentation.
The following C#/ VB.NET complete code snippet shows the creation of 100% stacked bar pyramid chart using XlsIO.
C#
using Syncfusion.XlsIO;
using System.Reflection;
using System.IO;
namespace ChartSample
{
class Program
{
static void Main(string[] args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Open existing workbook with data entered
Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("ChartSample.InputTemplate.xlsx");
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Initialize chart
IChartShape chart = worksheet.Charts.Add();
chart.ChartType = ExcelChartType.Pyramid_Bar_Stacked_100;
//Assign data
chart.DataRange = worksheet["A1:D6"];
chart.IsSeriesInRows = false;
//Apply chart elements
//Set chart title
chart.ChartTitle = "100% Stacked Bar Pyramid Chart";
//Set legend
chart.HasLegend = true;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Set rotation and elevation
chart.Rotation = 50;
chart.Elevation = 20;
//Set data labels
IChartSerie serie1 = chart.Series[0];
IChartSerie serie2 = chart.Series[1];
IChartSerie serie3 = chart.Series[2];
serie1.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serie2.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serie3.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
//Positioning the chart in the worksheet
chart.TopRow = 8;
chart.LeftColumn = 1;
chart.BottomRow = 23;
chart.RightColumn = 8;
//Saving the workbook
Stream stream = File.Create("Output.xlsx");
workbook.SaveAs(stream);
}
}
}
}
VB.NET
Imports Syncfusion.XlsIO
Imports System.Reflection
Imports System.IO
Namespace ChartSample
Class Program
Private Shared Sub Main(ByVal args() As String)
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2016
'Open existing workbook with data entered
Dim assembly As Assembly = GetType(Program).GetTypeInfo.Assembly
Dim fileStream As Stream = assembly.GetManifestResourceStream("ChartSample.InputTemplate.xlsx")
Dim workbook As IWorkbook = application.Workbooks.Open(fileStream)
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Initialize chart
Dim chart As IChartShape = worksheet.Charts.Add
chart.ChartType = ExcelChartType.Pyramid_Bar_Stacked_100
'Assign data
chart.DataRange = worksheet("A1:D6")
chart.IsSeriesInRows = False
'Apply chart elements
'Set chart title
chart.ChartTitle = "100% Stacked Bar Pyramid Chart"
'Set legend
chart.HasLegend = True
chart.Legend.Position = ExcelLegendPosition.Bottom
'Set rotation and elevation
chart.Rotation = 50
chart.Elevation = 20
'Set data labels
Dim serie1 As IChartSerie = chart.Series(0)
Dim serie2 As IChartSerie = chart.Series(1)
Dim serie3 As IChartSerie = chart.Series(2)
serie1.DataPoints.DefaultDataPoint.DataLabels.IsValue = True
serie2.DataPoints.DefaultDataPoint.DataLabels.IsValue = True
serie3.DataPoints.DefaultDataPoint.DataLabels.IsValue = True
'Positioning the chart in the worksheet
chart.TopRow = 8
chart.LeftColumn = 1
chart.BottomRow = 23
chart.RightColumn = 8
'Saving the workbook
Dim stream As Stream = File.Create("Output.xlsx")
workbook.SaveAs(stream)
End Using
End Sub
End Class
End Namespace
