在Excel中添加图表时,我们通常是选择所有的数据区域,但有些时候不是所有的数据都需要显示在图表中的,比如下面的表格中,我们只需要用到其中有填充颜色的那些行的数据,本文将介绍如何使用Spire.XLS组件实现该功能。
C#
//创建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");
VB.NET
'创建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")
效果图: