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)