c#/vb.net 如何使用不连续的数据区域创建图表-凯发线上登陆下载网址

在excel中添加图表时，我们通常是选择所有的数据区域，但有些时候不是所有的数据都需要显示在图表中的，比如下面的表格中，我们只需要用到其中有填充颜色的那些行的数据，本文将介绍如何使用spire.xls组件实现该功能。

//创建workbook实例
workbook workbook = new workbook();
//加载excel文档
workbook.loadfromfile(@"input.xlsx");
//获取第一个工作表
worksheet sheet = workbook.worksheets[0];
//添加图表
chart chart = sheet.charts.add();
chart.seriesdatafromrange = false;
//设置图表的位置
chart.leftcolumn = 6;
chart.toprow = 1;
chart.rightcolumn = 12;
chart.bottomrow = 13;
//添加三个系列到图表，调用xlsrange.addcombinedrange(cellrange cr)方法来为系列设置多个不连贯的数据区域
var cs1 = (chartserie)chart.series.add();
cs1.name = sheet.range["b1"].value;
cs1.categorylabels = sheet.range["a2:a3"].addcombinedrange(sheet.range["a5:a6"]).addcombinedrange(sheet.range["a8:a9"]);
cs1.values = sheet.range["b2:b3"].addcombinedrange(sheet.range["b5:b6"]).addcombinedrange(sheet.range["b8:b9"]);
cs1.serietype = excelcharttype.linemarkers;
var cs2 = (chartserie)chart.series.add();
cs2.name = sheet.range["c1"].value;
cs2.categorylabels = sheet.range["a2:a3"].addcombinedrange(sheet.range["a5:a6"]).addcombinedrange(sheet.range["a8:a9"]);
cs2.values = sheet.range["c2:c3"].addcombinedrange(sheet.range["c5:c6"]).addcombinedrange(sheet.range["c8:c9"]);
cs2.serietype = excelcharttype.linemarkers;
var cs3 = (chartserie)chart.series.add();
cs3.name = sheet.range["d1"].value;
cs3.categorylabels = sheet.range["a2:a3"].addcombinedrange(sheet.range["a5:a6"]).addcombinedrange(sheet.range["a8:a9"]);
cs3.values = sheet.range["d2:d3"].addcombinedrange(sheet.range["d5:d6"]).addcombinedrange(sheet.range["d8:d9"]);
cs3.serietype = excelcharttype.linemarkers; 
//设置图表标题为空
chart.charttitle = string.empty;
//保存文档
workbook.savetofile("output.xlsx");

'创建workbook实例
dim workbook as new workbook()
'加载excel文档
workbook.loadfromfile("input.xlsx")
'获取第一个工作表
dim sheet as worksheet = workbook.worksheets(0)
'添加图表
dim chart as chart = sheet.charts.add()
chart.seriesdatafromrange = false
'设置图表的位置
chart.leftcolumn = 6
chart.toprow = 1
chart.rightcolumn = 12
chart.bottomrow = 13
'添加三个系列到图表，调用xlsrange.addcombinedrange(cellrange cr)方法来为系列设置多个不连贯的数据区域
dim cs1 = directcast(chart.series.add(), chartserie)
cs1.name = sheet.range("b1").value
cs1.categorylabels = sheet.range("a2:a3").addcombinedrange(sheet.range("a5:a6")).addcombinedrange(sheet.range("a8:a9"))
cs1.values = sheet.range("b2:b3").addcombinedrange(sheet.range("b5:b6")).addcombinedrange(sheet.range("b8:b9"))
cs1.serietype = excelcharttype.linemarkers
dim cs2 = directcast(chart.series.add(), chartserie)
cs2.name = sheet.range("c1").value
cs2.categorylabels = sheet.range("a2:a3").addcombinedrange(sheet.range("a5:a6")).addcombinedrange(sheet.range("a8:a9"))
cs2.values = sheet.range("c2:c3").addcombinedrange(sheet.range("c5:c6")).addcombinedrange(sheet.range("c8:c9"))
cs2.serietype = excelcharttype.linemarkers
dim cs3 = directcast(chart.series.add(), chartserie)
cs3.name = sheet.range("d1").value
cs3.categorylabels = sheet.range("a2:a3").addcombinedrange(sheet.range("a5:a6")).addcombinedrange(sheet.range("a8:a9"))
cs3.values = sheet.range("d2:d3").addcombinedrange(sheet.range("d5:d6")).addcombinedrange(sheet.range("d8:d9"))
cs3.serietype = excelcharttype.linemarkers
'设置图表标题为空
chart.charttitle = string.empty
'保存文档
workbook.savetofile("output.xlsx")

效果图：