C ා create Excel chart with multi-level classification labels

Compared with data, charts can more intuitively reflect the trend of data changes. In the data table, the same data value may represent different data classifications at the same time. In the chart, one data is reflected under multiple data classification labels. Generally, there is only one classification label for the generated chart by default. The following method will introduce how to generate the chart with multi-level classification labels in Excel through programming.  

 

Using tool: fire.xls for. Net

1. Through the official website( https://www.e-iceblue.cn/Introduce/Spire-XLS-NET.html )Download the installation package -- extract install. Add the reference Spire.XLS.dll in the program. The DLL file is in the bin folder under the installation path.

2. Through Nuget website. ( http://www.nuget.org/packages/Spire.XLS/ )

After adding the reference, see the following figure:

C × example code

Step 1: create worksheet

//Establish Workbook Instance, getting worksheet
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: write data to table

 sheet.Range["A2"].Text = "Before export";
 sheet.Range["A5"].Text = "After export";
 sheet.Range["B1"].Text = "Particular year";
 sheet.Range["B2"].Text = "2017 year";
 sheet.Range["B6"].Text = "2018 year";
 sheet.Range["C1"].Text = "quarter";
 sheet.Range["C2"].Text = "1 quarter";
 sheet.Range["C3"].Text = "2 quarter";
 sheet.Range["C4"].Text = "3 quarter";
 sheet.Range["C5"].Text = "4 quarter";
 sheet.Range["C6"].Text = "1 quarter";
 sheet.Range["C7"].Text = "2 quarter";
 sheet.Range["C8"].Text = "3 quarter";
 sheet.Range["C9"].Text = "4 quarter";
 sheet.Range["D1"].Text = "Quarterly output\n(10000 tons)";
 sheet.Range["D2"].Value = "1.56";
 sheet.Range["D3"].Value = "2.3";
 sheet.Range["D4"].Value = "3.21";
 sheet.Range["D5"].Value = "3.5";
 sheet.Range["D6"].Value = "4.8";
 sheet.Range["D7"].Value = "5.2";
 sheet.Range["D8"].Value = "5.79";
 sheet.Range["D9"].Value = "5.58";

Step3: merge cells and set cell alignment

sheet.Range["A2:A4"].Merge();
sheet.Range["A5:A9"].Merge();
sheet.Range["B2:B5"].Merge();
sheet.Range["B6:B9"].Merge();
sheet.Range["A1:D9"].Style.HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1:D9"].Style.VerticalAlignment = VerticalAlignType.Center;

Step4: add chart to Excel

//Add column chart
Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
chart.ChartTitle = "Quarterly production (10000 tons)";//Set chart title     
chart.PlotArea.Fill.FillType = ShapeFillType.NoFill; //Do not fill the drawing area (gray by default)
chart.Legend.Delete();//Delete legend

//Specify the position and width of the column chart in the worksheet
chart.LeftColumn = 5;
chart.TopRow = 1;
chart.RightColumn = 14;

//Set chart series data source
chart.DataRange = sheet.Range["D2:D9"];
chart.SeriesDataFromRange = false;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
chart.Series[0].Format.LineProperties.Color = Color.BlueViolet;

//Set data source of series classification label
ChartSerie serie = chart.Series[0];
serie.CategoryLabels = sheet.Range["A2:C9"];

Step5: set whether to display multi-level classification labels

chart.PrimaryCategoryAxis.MultiLevelLable = true;

Step6: save document

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

 

Chart creation results:

 

All codes:

using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace ChartWithMultiLevelCategoryLabels_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //Establish Workbook Instance, getting worksheet
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];

            //Write data to worksheet
            sheet.Range["A2"].Text = "Before export";
            sheet.Range["A5"].Text = "After export";
            sheet.Range["B1"].Text = "Particular year";
            sheet.Range["B2"].Text = "2017 year";
            sheet.Range["B6"].Text = "2018 year";
            sheet.Range["C1"].Text = "quarter";
            sheet.Range["C2"].Text = "1 quarter";
            sheet.Range["C3"].Text = "2 quarter";
            sheet.Range["C4"].Text = "3 quarter";
            sheet.Range["C5"].Text = "4 quarter";
            sheet.Range["C6"].Text = "1 quarter";
            sheet.Range["C7"].Text = "2 quarter";
            sheet.Range["C8"].Text = "3 quarter";
            sheet.Range["C9"].Text = "4 quarter";
            sheet.Range["D1"].Text = "Quarterly output\n(10000 tons)";
            sheet.Range["D2"].Value = "1.56";
            sheet.Range["D3"].Value = "2.3";
            sheet.Range["D4"].Value = "3.21";
            sheet.Range["D5"].Value = "3.5";
            sheet.Range["D6"].Value = "4.8";
            sheet.Range["D7"].Value = "5.2";
            sheet.Range["D8"].Value = "5.79";
            sheet.Range["D9"].Value = "5.58";
            

            //Merge cells, set cell alignment
            sheet.Range["A2:A4"].Merge();
            sheet.Range["A5:A9"].Merge();
            sheet.Range["B2:B5"].Merge();
            sheet.Range["B6:B9"].Merge();
            sheet.Range["A1:D9"].Style.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1:D9"].Style.VerticalAlignment = VerticalAlignType.Center;


            //Add column chart
            Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
            chart.ChartTitle = "Quarterly production (10000 tons)";//Set chart title     
            chart.PlotArea.Fill.FillType = ShapeFillType.NoFill; //Do not fill the drawing area (gray by default)
            chart.Legend.Delete();//Delete legend

            //Specify the position and width of the column chart in the worksheet
            chart.LeftColumn = 5;
            chart.TopRow = 1;
            chart.RightColumn = 14;

            //Set chart series data source
            chart.DataRange = sheet.Range["D2:D9"];
            chart.SeriesDataFromRange = false;
            chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            chart.Series[0].Format.LineProperties.Color = Color.BlueViolet;

            //Set data source of series classification label
            ChartSerie serie = chart.Series[0];
            serie.CategoryLabels = sheet.Range["A2:C9"];


            //Show multi level category labels
            chart.PrimaryCategoryAxis.MultiLevelLable = true;

            //Save document
            wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("output.xlsx");
        }
    }
}

 

(end of this paper)

Tags: C# Excel Programming

Posted on Tue, 03 Dec 2019 02:09:40 -0500 by Zoud