Configure Chart Series

In GcExcel .NET, you can configure chart series using the following in your spreadsheet:

DataPoint

The Points collection in GcExcel .NET is used to represent all the points in a specific series and the indexer notation of the IPoints interface to get a specific point in the series. Also, you can use the DataLabel property of the IPoint interface to get data label of a specific point.

Set the format of DataPoint

Refer to the following example code to set data point format for the chart inserted in your worksheet.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
ISeries series2 = shape.Chart.SeriesCollection[1];
ISeries series3 = shape.Chart.SeriesCollection[2];

series1.Format.Fill.Color.RGB = Color.Blue;
series1.Points[2].Format.Fill.Color.RGB = Color.Green;

Configure secondary section for pie of a pie chart

You can use the SecondaryPlot property of the IPoint interface to set if the point lies in the secondary section of either a pie of pie chart or a bar of pie chart.

Refer to the following example code to configure secondary section for pie of a pie chart.

IShape shape = worksheet.Shapes.AddChart(ChartType.PieOfPie, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

shape.Chart.ChartGroups[0].SplitType = ChartSplitType.SplitByCustomSplit;
series1.Points[0].SecondaryPlot = true;
series1.Points[1].SecondaryPlot = false;
series1.Points[2].SecondaryPlot = true;
series1.Points[3].SecondaryPlot = false;
series1.Points[4].SecondaryPlot = true;

DataLabel

The DataLabels collection in GcExcel .NET is used to represent the collection of all the data labels for the specified series.

You can use the Font property and Format property of the IDataLabel interface to set font style, fill, line and 3-D formatting for all the data labels of the specified series. You can also configure the layout of the data labels using other properties of the IDataLabel interface.

Set all data labels and specific data label format for series

Refer to the following example code to set series' all data labels and specific data label format.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

//set series1's all data label's format.
series1.DataLabels.Format.Fill.Color.RGB = Color.Green;
series1.DataLabels.Format.Line.Color.RGB = Color.Red;
series1.DataLabels.Format.Line.Weight = 3;

//set series1's specific data label's format.
series1.DataLabels[2].Format.Fill.Color.RGB = Color.Yellow;
series1.Points[2].DataLabel.Format.Line.Color.RGB = Color.Blue;
series1.Points[2].DataLabel.Format.Line.Weight = 5;

Customize data label text

Refer to the following example code to customize the text of the data label.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

//customize data lables' text.
series1.DataLabels.ShowCategoryName = true;
series1.DataLabels.ShowSeriesName = true;
series1.DataLabels.ShowLegendKey = true;

type=warning
Note: With version 7.0, the return value of Parent property of IDataLabel interface is changed from IPoint to Object, which will cause the compilation failure or runtime error in your existing projects. To avoid this, you must add explicit conversion of the object to IPoint as follows:
// Following will cause a compilation error.
IPoint pt = series1.DataLabels[0].Parent;
// Add explicit conversion to IPoint.
IPoint pt = (IPoint)series1.DataLabels[0].Parent

Trendline

The Trendlines collection in GcExcel .NET is used to represent a collection of trend lines for a specific series. You can use the Add method of the ITrendlines interface to create a new trendline for a specific series. Also, you can use the indexer notation of the ITrendlines interface to get a specific trend line.

Add trendline for series and configure its style

Refer to the following example code to add trendline for series and configure its style.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.Trendlines.Add();
series1.Trendlines[0].Type = TrendlineType.Linear;
series1.Trendlines[0].Forward = 5;
series1.Trendlines[0].Backward = 0.5;
series1.Trendlines[0].Intercept = 2.5;
series1.Trendlines[0].DisplayEquation = true;
series1.Trendlines[0].DisplayRSquared = true;

Add two trendlines for one series

Refer to the following example code to add two trendlines for one series.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.Trendlines.Add();
series1.Trendlines[0].Type = TrendlineType.Linear;
series1.Trendlines[0].Forward = 5;
series1.Trendlines[0].Backward = 0.5;
series1.Trendlines[0].Intercept = 2.5;
series1.Trendlines[0].DisplayEquation = true;
series1.Trendlines[0].DisplayRSquared = true;


series1.Trendlines.Add();
series1.Trendlines[1].Type = TrendlineType.Polynomial;
series1.Trendlines[1].Order = 3;

Set trendline's name

You can also set the trendline's name in GcExcel using the Name property of ITrendline interface. The trendline's name can also be exported to a PDF document.

Refer to the following example code to add trendline's name in GcExcel.

// Initialize workbook
Workbook workbook = new Workbook();
// Fetch default worksheet
IWorksheet worksheet = workbook.Worksheets[0];

// Add a chart
IShape columnChart = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 300, 10, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
{null, "S1", "S2", "S3"},
{"Item1", 10, 25, 25},
{"Item2", -51, -36, 27},
{"Item3", 52, -85, -30},
{"Item4", 22, 65, 65},
{"Item5", 23, 69, 69}
    };

// Add series
columnChart.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

// Get first series
ISeries series1 = columnChart.Chart.SeriesCollection[0];

// Add a trend line
ITrendline trendline = series1.Trendlines.Add();

// Set trend line's name
trendline.Name = "Theoretical data";

// Save to an excel file
workbook.Save("TrendLineName.xlsx");

Set trendline’s label format

You can also format the trendline equation label and export it to a PDF document, HTML file, or image using DataLabel property in ITrendline interface, which gets the data label associated with the trendline. DataLabel returns value only when DisplayEquation or DisplayRSquared of ITrendline interface is true. If both of them are false, the DataLabel property will return null.

You can use Font, Format, NumberFormat, Orientation, Direction, and AutoText properties of IDataLabel interface to format the trendline equation label. GcExcel also provides Delete method to delete the trendline equation label.

Refer to the following example code to format the data label of the trendline:

// Initialize Workbook.
IWorkbook workbook = new Workbook();

// Create a worksheet.
IWorksheet worksheet = workbook.Worksheets[0];

// Add XYScatter chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.XYScatter, 250, 20, 360, 230);
worksheet.Range["A1:C11"].Value = new Object[,] {
    { null, "Mktng Exp", "Revenue" },
    { "Company 1", 1849, 2911 },
    { "Company 2", 2708, 5777 },
    { "Company 3", 3474, 8625 },
    { "Company 4", 4681, 9171 },
    { "Company 5", 5205, 10308 },
    { "Company 6", 5982, 11779 },
    { "Company 7", 8371, 12138 },
    { "Company 8", 8457, 17074 },
    { "Company 9", 9554, 15729 },
    { "Company 10", 9604, 19610 }
    };
shape.Chart.SeriesCollection.Add(worksheet.Range["B1:C11"], RowCol.Columns, true, true);
ISeries series1 = shape.Chart.SeriesCollection[0];

// Add Trendline.
ITrendline trendline = series1.Trendlines.Add();
trendline.Type = TrendlineType.Linear;

// Display equation for the trendline.
trendline.DisplayEquation = true;

// Format datalabel for trendline.
IDataLabel trendlineDataLabel = trendline.DataLabel;
trendlineDataLabel.Font.Color.RGB = Color.Purple;
trendlineDataLabel.Font.Size = 11;
trendlineDataLabel.Format.Fill.Color.ObjectThemeColor = ThemeColor.Accent4;
trendlineDataLabel.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent2;

// Set paper size for PDF export.
worksheet.PageSetup.PaperSize = PaperSize.A3;

// Save the workbook.
workbook.Save("DataLabelTrendline.xlsx");

// Export the workbook as a PDF document.
workbook.Save("DataLabelTrendline.pdf");

Parent property (ITrendline.DataLabel.Parent) will return the parent object of the specified trendline. Its return value type is an object with ITrendline as the return value.

ITrendline trendline = (ITrendline)trendline.DataLabel.Parent;

type=warning
Note: The trendline equation label does not support the following properties of IDataLabel interface; hence, calling them will throw a NotSupportedException:
Position
Separator
ShowBubbleSize
ShowCategoryName
ShowLegendKey
ShowPercentage
ShowSeriesName
ShowValue
NumberFormatLinked
TextFrame

Limitations

SpreadJS only supports the default equation and R-value; therefore, GcExcel cannot export the trendline data format to JSON and SJS.

Chart Group

Chart Group contains common settings for one or more series. Typically, it is a group of specific featured series.

Set varied colors for column chart with one series

Refer to the following example code to set different colors for a column chart which has only one series.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.SeriesCollection[2].Delete();
shape.Chart.SeriesCollection[1].Delete();
//Chart's series count is 1.
var count = shape.Chart.SeriesCollection.Count;
//set vary colors for column chart which only has one series.
shape.Chart.ColumnGroups[0].VaryByCategories = true;

Set split setting and gap width for pie of a pie chart

Refer to the following example code to set split setting and gap width for pie of a pie chart.

IShape shape = worksheet.Shapes.AddChart(ChartType.PieOfPie, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

shape.Chart.PieGroups[0].SplitType = ChartSplitType.SplitByValue;
shape.Chart.PieGroups[0].SplitValue = 20;
shape.Chart.PieGroups[0].GapWidth = 350;

Set gap width of column chart and overlap

Refer to the following example code in order to set the gap width of the column chart along with overlap.

//Set column chart's gap width and overlap
IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

shape.Chart.ColumnGroups[0].GapWidth = 120;
shape.Chart.ColumnGroups[0].Overlap = -20;

Configure the layout of the bubble chart

Refer to the following example code to configure the layout of the bubble chart as per your preferences.

//Configure bubble chart's layout
IShape shape = worksheet.Shapes.AddChart(ChartType.Bubble, 250, 20, 360, 230);
worksheet.Range["A1:C10"].Value = new object[,]
 {
    {"Blue", null, null },
    {125, 750, 3 },
    {25, 625, 7 },
    {75, 875, 5 },
    {175, 625, 6},
    {"Red",null,null },
    {125 ,500 , 10 },
    {25, 250, 1 },
    {75, 125, 5 },
    {175, 250, 8 },
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A2:C5"], RowCol.Columns);
shape.Chart.SeriesCollection.Add(worksheet.Range["A7:C10"], RowCol.Columns);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

shape.Chart.XYGroups[0].BubbleScale = 150;
shape.Chart.XYGroups[0].SizeRepresents = SizeRepresents.SizeIsArea;
shape.Chart.XYGroups[0].ShowNegativeBubbles = true;

Configure the layout of the doughnut chart

Refer to the following example code to configure the layout of the doughnut chart as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.Doughnut, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

ISeries series1 = shape.Chart.SeriesCollection[0];
series1.HasDataLabels = true;

shape.Chart.DoughnutGroups[0].FirstSliceAngle = 50;
shape.Chart.DoughnutGroups[0].DoughnutHoleSize = 20;

Dropline, HiLoline and SeriesLine

You can use the HasDropLines property, HasHiLoLines property, HasSeriesLines property, DropLines property,HiLoLines property, SeriesLines property of the IChartGroup interface to configure Dropline, HiLoline and Series lines in a chart.

Configure the drop lines of the line chart

Refer to the following example code to configure the drop lines of the line chart as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.Line, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.LineGroups[0].HasDropLines = true;
shape.Chart.LineGroups[0].DropLines.Format.Line.Color.RGB = Color.Red;

Configure the high-low lines of the line chart

Refer to the following example code to configure the high-low lines of the line chart as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.Line, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.LineGroups[0].HasHiLoLines = true;
shape.Chart.LineGroups[0].HiLoLines.Format.Line.Color.RGB = Color.Red;

Configure the series lines for column chart

Refer to the following example code to configure the column chart's series lines as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnStacked, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.ColumnGroups[0].HasSeriesLines = true;
shape.Chart.ColumnGroups[0].SeriesLines.Format.Line.Color.RGB = Color.Red;

Configure the connector lines for pie of a pie chart

Refer to the following example code to configure the connector lines for pie of a pie chart as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.PieOfPie, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.PieGroups[0].HasSeriesLines = true;
shape.Chart.PieGroups[0].SeriesLines.Format.Line.Color.RGB = Color.Red;

Up-Down Bars

You can use the HasUpDownBars property, DownBars property and UpBars property of the IChartGroup interface up-down bars in a chart to configure the style of the up bars and the down bars as per your preferences.

Configure the up-down bars for the line chart

Refer to the following example code to configure the up-down bars for the line chart as per your preferences.

IShape shape = worksheet.Shapes.AddChart(ChartType.Line, 200, 100, 300, 300);
worksheet.Range["A1:D6"].Value = new object[,]
{
    {null, "S1", "S2", "S3"},
    {"Item1", 10, 25, 25},
    {"Item2", -51, -36, 27},
    {"Item3", 52, -85, -30},
    {"Item4", 22, 65, 65},
    {"Item5", 23, 69, 69}
};
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:D6"], RowCol.Columns, true, true);

shape.Chart.LineGroups[0].HasUpDownBars = true;
shape.Chart.LineGroups[0].UpBars.Format.Fill.Color.RGB = Color.Green;
shape.Chart.LineGroups[0].DownBars.Format.Fill.Color.RGB = Color.Red;