C# 创建含多层分类标签的Excel图表

相较于数据，图表更能直观的体现数据的变化趋势。在数据表格中，同一数据值，可能同时代表不同的数据分类，表现在图表中则是一个数据体现在多个数据分类标签下。通常生成的图表一般默认只有一种分类标签，下面的方法将通过编程的方式来介绍在excel中如何来生成含多层分类标签的图表。 

 

使用工具：spire.xls for .net

1.通过官网（https://www.e-iceblue.cn/introduce/spire-xls-net.html ）下载安装包--解压-安装。在程序中添加引用spire.xls.dll，dll文件在安装路径下的bin文件夹中。

2.通过 nuget 官网。（http://www.nuget.org/packages/spire.xls/ ）

添加引用完成后，如下图：

c#示例代码

 step1:创建工作表

//创建workbook实例，获取工作表
workbook wb = new workbook();
worksheet sheet = wb.worksheets[0];

step2:写入数据到表格

 sheet.range["a2"].text = "出口前";
 sheet.range["a5"].text = "出口后";
 sheet.range["b1"].text = "年份";
 sheet.range["b2"].text = "2017年";
 sheet.range["b6"].text = "2018年";
 sheet.range["c1"].text = "季度";
 sheet.range["c2"].text = "1季度";
 sheet.range["c3"].text = "2季度";
 sheet.range["c4"].text = "3季度";
 sheet.range["c5"].text = "4季度";
 sheet.range["c6"].text = "1季度";
 sheet.range["c7"].text = "2季度";
 sheet.range["c8"].text = "3季度";
 sheet.range["c9"].text = "4季度";
 sheet.range["d1"].text = "季度产量\n（万吨）";
 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:合并单元格并设置单元格对齐方式

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:添加图表到excel

//添加柱状图表
chart chart = sheet.charts.add(excelcharttype.linemarkers);
chart.charttitle = "季度产量（万吨）";//设置图表标题     
chart.plotarea.fill.filltype = shapefilltype.nofill; //不填充绘图区域（默认填充灰色）
chart.legend.delete();//删除图例

//指定柱状图表在工作表中的位置及宽度
chart.leftcolumn = 5;
chart.toprow = 1;
chart.rightcolumn = 14;

//设置图表系列数据来源
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;

//设置系列分类标签数据来源
chartserie serie = chart.series[0];
serie.categorylabels = sheet.range["a2:c9"];

step5:设置是否显示多层分类标签

chart.primarycategoryaxis.multilevellable = true;

step6: 保存文档

wb.savetofile("output.xlsx", excelversion.version2013);

 

图表创建结果：

 

全部代码:

using spire.xls;
using spire.xls.charts;
using system.drawing;

namespace chartwithmultilevelcategorylabels_xls
{
    class program
    {
        static void main(string[] args)
        {
            //创建workbook实例，获取工作表
            workbook wb = new workbook();
            worksheet sheet = wb.worksheets[0];

            //写入数据到工作表
            sheet.range["a2"].text = "出口前";
            sheet.range["a5"].text = "出口后";
            sheet.range["b1"].text = "年份";
            sheet.range["b2"].text = "2017年";
            sheet.range["b6"].text = "2018年";
            sheet.range["c1"].text = "季度";
            sheet.range["c2"].text = "1季度";
            sheet.range["c3"].text = "2季度";
            sheet.range["c4"].text = "3季度";
            sheet.range["c5"].text = "4季度";
            sheet.range["c6"].text = "1季度";
            sheet.range["c7"].text = "2季度";
            sheet.range["c8"].text = "3季度";
            sheet.range["c9"].text = "4季度";
            sheet.range["d1"].text = "季度产量\n（万吨）";
            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";
            

            //合并单元格，设置单元格对齐方式
            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;


            //添加柱状图表
            chart chart = sheet.charts.add(excelcharttype.linemarkers);
            chart.charttitle = "季度产量（万吨）";//设置图表标题     
            chart.plotarea.fill.filltype = shapefilltype.nofill; //不填充绘图区域（默认填充灰色）
            chart.legend.delete();//删除图例

            //指定柱状图表在工作表中的位置及宽度
            chart.leftcolumn = 5;
            chart.toprow = 1;
            chart.rightcolumn = 14;

            //设置图表系列数据来源
            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;

            //设置系列分类标签数据来源
            chartserie serie = chart.series[0];
            serie.categorylabels = sheet.range["a2:c9"];


            //显示多层分类标签
            chart.primarycategoryaxis.multilevellable = true;

            //保存文档
            wb.savetofile("output.xlsx", excelversion.version2013);
            system.diagnostics.process.start("output.xlsx");
        }
    }
}

 

（本文完）