How to create a Chart with a discontinuous range?

8 Dec 202311 minutes to read

The following code example illustrates creating a chart with discontinuous data ranges.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Entering the data for the chart.
  worksheet.Range["A1"].Text = "Texas books Unit sales";
  worksheet.Range["A1:D1"].Merge();
  worksheet.Range["A1"].CellStyle.Font.Bold = true;
  worksheet.Range["B2"].Text = "Jan";
  worksheet.Range["C2"].Text = "Feb";
  worksheet.Range["D2"].Text = "Mar";
  worksheet.Range["A3"].Text = "Austin";
  worksheet.Range["A4"].Text = "Dallas";
  worksheet.Range["A5"].Text = "Houston";
  worksheet.Range["A6"].Text = "San Antonio";
  worksheet.Range["B3"].Number = 53.75;
  worksheet.Range["B4"].Number = 52.85;
  worksheet.Range["B5"].Number = 59.77;
  worksheet.Range["B6"].Number = 96.15;
  worksheet.Range["C3"].Number = 79.79;
  worksheet.Range["C4"].Number = 59.22;
  worksheet.Range["C5"].Number = 10.09;
  worksheet.Range["C6"].Number = 73.02;
  worksheet.Range["D3"].Number = 26.72;
  worksheet.Range["D4"].Number = 33.71;
  worksheet.Range["D5"].Number = 45.81;
  worksheet.Range["D6"].Number = 12.17;
  worksheet.Range["F1"].Number = 26.72;
  worksheet.Range["F2"].Number = 33.71;
  worksheet.Range["F3"].Number = 45.81;
  worksheet.Range["F4"].Number = 12.17;

  //Discontinuous range.
  IRanges rangesOne = worksheet.CreateRangesCollection();
  rangesOne.Add(worksheet.Range["B3:B6"]);
  rangesOne.Add(worksheet.Range["F1:F2"]);
  IRanges rangesTwo = worksheet.CreateRangesCollection();
  rangesTwo.Add(worksheet.Range["D3:D6"]);
  rangesTwo.Add(worksheet.Range["F3:F4"]);

  //Adding a New (Embedded chart) to the Worksheet.
  IChartShape shape = worksheet.Charts.Add();
  shape.PrimaryCategoryAxis.Title = "City";
  shape.PrimaryValueAxis.Title = "Sales (in Dollars)";
  shape.ChartTitle = "Texas Books Unit Sales";

  //Setting the Series Names in a Legend.
  IChartSerie serieOne = shape.Series.Add();
  serieOne.Name = "Jan";
  serieOne.Values = rangesOne;
  IChartSerie serieTwo = shape.Series.Add();
  serieTwo.Name = "March";
  serieTwo.Values = rangesTwo;

  //Setting the (Rows & Columns) Property for the Embedded chart.
  shape.BottomRow = 40;
  shape.TopRow = 10;
  shape.LeftColumn = 3;
  shape.RightColumn = 15;

  FileStream stream = new FileStream("DiscontinuousRange.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  workbook.Close();
  excelEngine.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Entering the data for the chart.
  worksheet.Range["A1"].Text = "Texas books Unit sales";
  worksheet.Range["A1:D1"].Merge();
  worksheet.Range["A1"].CellStyle.Font.Bold = true;
  worksheet.Range["B2"].Text = "Jan";
  worksheet.Range["C2"].Text = "Feb";
  worksheet.Range["D2"].Text = "Mar";
  worksheet.Range["A3"].Text = "Austin";
  worksheet.Range["A4"].Text = "Dallas";
  worksheet.Range["A5"].Text = "Houston";
  worksheet.Range["A6"].Text = "San Antonio";
  worksheet.Range["B3"].Number = 53.75;
  worksheet.Range["B4"].Number = 52.85;
  worksheet.Range["B5"].Number = 59.77;
  worksheet.Range["B6"].Number = 96.15;
  worksheet.Range["C3"].Number = 79.79;
  worksheet.Range["C4"].Number = 59.22;
  worksheet.Range["C5"].Number = 10.09;
  worksheet.Range["C6"].Number = 73.02;
  worksheet.Range["D3"].Number = 26.72;
  worksheet.Range["D4"].Number = 33.71;
  worksheet.Range["D5"].Number = 45.81;
  worksheet.Range["D6"].Number = 12.17;
  worksheet.Range["F1"].Number = 26.72;
  worksheet.Range["F2"].Number = 33.71;
  worksheet.Range["F3"].Number = 45.81;
  worksheet.Range["F4"].Number = 12.17;

  //Discontinuous range.
  IRanges rangesOne = worksheet.CreateRangesCollection();
  rangesOne.Add(worksheet.Range["B3:B6"]);
  rangesOne.Add(worksheet.Range["F1:F2"]);
  IRanges rangesTwo = worksheet.CreateRangesCollection();
  rangesTwo.Add(worksheet.Range["D3:D6"]);
  rangesTwo.Add(worksheet.Range["F3:F4"]);

  //Adding a New (Embedded chart) to the Worksheet.
  IChartShape shape = worksheet.Charts.Add();
  shape.PrimaryCategoryAxis.Title = "City";
  shape.PrimaryValueAxis.Title = "Sales (in Dollars)";
  shape.ChartTitle = "Texas Books Unit Sales";

  //Setting the Series Names in a Legend.
  IChartSerie serieOne = shape.Series.Add();
  serieOne.Name = "Jan";
  serieOne.Values = rangesOne;
  IChartSerie serieTwo = shape.Series.Add();
  serieTwo.Name = "March";
  serieTwo.Values = rangesTwo;

  //Setting the (Rows & Columns) Property for the Embedded chart.
  shape.BottomRow = 40;
  shape.TopRow = 10;
  shape.LeftColumn = 3;
  shape.RightColumn = 15;

  workbook.SaveAs("DiscontinuousRange.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Entering the data for the chart.
  worksheet.Range("A1").Text = "Texas books Unit sales"
  worksheet.Range("A1:D1").Merge()
  worksheet.Range("A1").CellStyle.Font.Bold = True
  worksheet.Range("B2").Text = "Jan"
  worksheet.Range("C2").Text = "Feb"
  worksheet.Range("D2").Text = "Mar"
  worksheet.Range("A3").Text = "Austin"
  worksheet.Range("A4").Text = "Dallas"
  worksheet.Range("A5").Text = "Houston"
  worksheet.Range("A6").Text = "San Antonio"
  worksheet.Range("B3").Number = 53.75
  worksheet.Range("B4").Number = 52.85
  worksheet.Range("B5").Number = 59.77
  worksheet.Range("B6").Number = 96.15
  worksheet.Range("C3").Number = 79.79
  worksheet.Range("C4").Number = 59.22
  worksheet.Range("C5").Number = 10.09
  worksheet.Range("C6").Number = 73.02
  worksheet.Range("D3").Number = 26.72
  worksheet.Range("D4").Number = 33.71
  worksheet.Range("D5").Number = 45.81
  worksheet.Range("D6").Number = 12.17
  worksheet.Range("F1").Number = 26.72
  worksheet.Range("F2").Number = 33.71
  worksheet.Range("F3").Number = 45.81
  worksheet.Range("F4").Number = 12.17

  'Discontinuous range.
  Dim rangesOne As IRanges = worksheet.CreateRangesCollection()
  rangesOne.Add(worksheet.Range("B3:B6"))
  rangesOne.Add(worksheet.Range("F1:F2"))
  Dim rangesTwo As IRanges = worksheet.CreateRangesCollection()
  rangesTwo.Add(worksheet.Range("D3:D6"))
  rangesTwo.Add(worksheet.Range("F3:F4"))

  'Adding a New (Embedded chart) to the Worksheet.
  Dim shape As IChartShape = worksheet.Charts.Add()
  shape.PrimaryCategoryAxis.Title = "City"
  shape.PrimaryValueAxis.Title = "Sales (in Dollars)"
  shape.ChartTitle = "Texas Books Unit Sales"

  'Setting the Series Names in a Legend.
  Dim serieOne As IChartSerie = shape.Series.Add()
  serieOne.Name = "Jan"
  serieOne.Values = rangesOne
  Dim serieTwo As IChartSerie = shape.Series.Add()
  serieTwo.Name = "March"
  serieTwo.Values = rangesTwo

  'Setting the (Rows & Columns)Property for the Embedded chart.
  shape.BottomRow = 40
  shape.TopRow = 10
  shape.LeftColumn = 3
  shape.RightColumn = 15

  workbook.SaveAs("DiscontinuousRange.xlsx")
End Using

See Also

• How to define discontinuous ranges?
• How to add chart labels to scatter points?
• How to change data point label color of a Waterfall chart?
• How to set data range to chart?
• How to access discontinuous ranges?
• How to create a chart?
• How to create a pie chart in Excel?
• How to create a sparkline from a named range?