Create Multi-Level Category Chart in Excel in C#, VB.NET

Multi-level category chart is a chart type that has both main category and subcategory labels. This type of chart is useful when you have figures for items that belong to different categories. In this article, you will learn how to create a multi-level category chart in Excel using Spire.XLS with C# and VB.NET.

Step 1: Create a Workbook instance and get the first worksheet.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: Write data to cells.

sheet.Range["A1"].Text = "Main Category"; 
sheet.Range["A2"].Text = "Fruit";
sheet.Range["A6"].Text = "Vegies";
sheet.Range["B1"].Text = "Sub Category";
sheet.Range["B2"].Text = "Bananas";
sheet.Range["B3"].Text = "Oranges";
sheet.Range["B4"].Text = "Pears";
sheet.Range["B5"].Text = "Grapes";
sheet.Range["B6"].Text = "Carrots";
sheet.Range["B7"].Text = "Potatoes";
sheet.Range["B8"].Text = "Celery";
sheet.Range["B9"].Text = "Onions";
sheet.Range["C1"].Text = "Value";
sheet.Range["C2"].Value = "52";
sheet.Range["C3"].Value = "65";
sheet.Range["C4"].Value = "50";
sheet.Range["C5"].Value = "45";
sheet.Range["C6"].Value = "64";
sheet.Range["C7"].Value = "62";
sheet.Range["C8"].Value = "89";
sheet.Range["C9"].Value = "57";

Step 3: Vertically merge cells from A2 to A5, A6 to A9.

sheet.Range["A2:A5"].Merge();
sheet.Range["A6:A9"].Merge();

Step 4: Add a clustered bar chart to worksheet.

Chart chart = sheet.Charts.Add(ExcelChartType.BarClustered);
chart.ChartTitle = "Value";   
chart.PlotArea.Fill.FillType = ShapeFillType.NoFill;
chart.Legend.Delete();

Step 5: Set the data source of series data.

chart.DataRange = sheet.Range["C2:C9"];
chart.SeriesDataFromRange = false;

Step 6: Set the data source of category labels.

ChartSerie serie = chart.Series[0];
serie.CategoryLabels = sheet.Range["A2:B9"];

Step 7: Show multi-level category labels.

chart.PrimaryCategoryAxis.MultiLevelLable = true;

Step 8: Save the file.

wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);

Output:

Create Multi-Level Category Chart in Excel in C#, VB.NET

Full Code:

[C#]

using Spire.Xls;
using Spire.Xls.Charts;
namespace CreateMutilLevelChart
{

    class Program
    {

        static void Main(string[] args)
        {
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];

            sheet.Range["A1"].Text = "Main Category";
            sheet.Range["A2"].Text = "Fruit";
            sheet.Range["A6"].Text = "Vegies";
            sheet.Range["B1"].Text = "Sub Category";
            sheet.Range["B2"].Text = "Bananas";
            sheet.Range["B3"].Text = "Oranges";
            sheet.Range["B4"].Text = "Pears";
            sheet.Range["B5"].Text = "Grapes";
            sheet.Range["B6"].Text = "Carrots";
            sheet.Range["B7"].Text = "Potatoes";
            sheet.Range["B8"].Text = "Celery";
            sheet.Range["B9"].Text = "Onions";
            sheet.Range["C1"].Text = "Value";
            sheet.Range["C2"].Value = "52";
            sheet.Range["C3"].Value = "65";
            sheet.Range["C4"].Value = "50";
            sheet.Range["C5"].Value = "45";
            sheet.Range["C6"].Value = "64";
            sheet.Range["C7"].Value = "62";
            sheet.Range["C8"].Value = "89";
            sheet.Range["C9"].Value = "57";
            sheet.Range["A2:A5"].Merge();
            sheet.Range["A6:A9"].Merge();
            sheet.AutoFitColumn(1);
            sheet.AutoFitColumn(2);

            Chart chart = sheet.Charts.Add(ExcelChartType.BarClustered);
            chart.ChartTitle = "Value";
            chart.PlotArea.Fill.FillType = ShapeFillType.NoFill;
            chart.Legend.Delete();
            chart.LeftColumn = 5;
            chart.TopRow = 1;
            chart.RightColumn = 14;
            chart.DataRange = sheet.Range["C2:C9"];
            chart.SeriesDataFromRange = false;
            ChartSerie serie = chart.Series[0];
            serie.CategoryLabels = sheet.Range["A2:B9"];
            chart.PrimaryCategoryAxis.MultiLevelLable = true;
            wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);
        }
    }
}

[VB.NET]

Imports Spire.Xls
Imports Spire.Xls.Charts
Namespace CreateMutilLevelChart

	Class Program

		Private Shared Sub Main(args As String())
			Dim wb As New Workbook()
			Dim sheet As Worksheet = wb.Worksheets(0)

			sheet.Range("A1").Text = "Main Category"
			sheet.Range("A2").Text = "Fruit"
			sheet.Range("A6").Text = "Vegies"
			sheet.Range("B1").Text = "Sub Category"
			sheet.Range("B2").Text = "Bananas"
			sheet.Range("B3").Text = "Oranges"
			sheet.Range("B4").Text = "Pears"
			sheet.Range("B5").Text = "Grapes"
			sheet.Range("B6").Text = "Carrots"
			sheet.Range("B7").Text = "Potatoes"
			sheet.Range("B8").Text = "Celery"
			sheet.Range("B9").Text = "Onions"
			sheet.Range("C1").Text = "Value"
			sheet.Range("C2").Value = "52"
			sheet.Range("C3").Value = "65"
			sheet.Range("C4").Value = "50"
			sheet.Range("C5").Value = "45"
			sheet.Range("C6").Value = "64"
			sheet.Range("C7").Value = "62"
			sheet.Range("C8").Value = "89"
			sheet.Range("C9").Value = "57"
			sheet.Range("A2:A5").Merge()
			sheet.Range("A6:A9").Merge()
			sheet.AutoFitColumn(1)
			sheet.AutoFitColumn(2)

			Dim chart As Chart = sheet.Charts.Add(ExcelChartType.BarClustered)
			chart.ChartTitle = "Value"
			chart.PlotArea.Fill.FillType = ShapeFillType.NoFill
			chart.Legend.Delete()
			chart.LeftColumn = 5
			chart.TopRow = 1
			chart.RightColumn = 14
			chart.DataRange = sheet.Range("C2:C9")
			chart.SeriesDataFromRange = False
			Dim serie As ChartSerie = chart.Series(0)
			serie.CategoryLabels = sheet.Range("A2:B9")
			chart.PrimaryCategoryAxis.MultiLevelLable = True
			wb.SaveToFile("output.xlsx", ExcelVersion.Version2013)
		End Sub
	End Class
End Namespace

Create Multi-Level Category Chart in Excel in C#, VB.NET

See Also