Chart (45)
How to change the color of data series in an Excel chart
2016-11-24 07:40:20 Written by support iceblueSpire.XLS enables developers to change the color of data series in an Excel chart with just a few lines of code. Once changed, the legend color will also turn into the same as the color you set to the series.
The following part demonstrates the steps of how to accomplish this task. Below picture shows the original colors of the data series in an Excel chart:
Code snippets:
Step 1: Instantiate a Workbook object and load the Excel workbook.
Workbook book = new Workbook();
book.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = book.Worksheets[0];
Step 3: Get the second series of the chart.
ChartSerie cs = sheet.Charts[0].Series[1];
Step 4: Change the color of the second series to Purple.
cs.Format.Fill.FillType = ShapeFillType.SolidColor; cs.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.Purple);
Step 5: Save the Excel workbook to file.
book.SaveToFile("ChangeSeriesColor.xlsx", ExcelVersion.Version2010);
After running the code, the color of the second series has been changed into Purple, screenshot as shown below.
Full code:
using System.Drawing;
using Spire.Xls;
using Spire.Xls.Charts;
namespace Change_Series_Color
{
class Program
{
static void Main(string[] args)
{
Workbook book = new Workbook();
book.LoadFromFile("Sample.xlsx");
Worksheet sheet = book.Worksheets[0];
ChartSerie cs = sheet.Charts[0].Series[1];
cs.Format.Fill.FillType = ShapeFillType.SolidColor;
cs.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.Purple);
book.SaveToFile("ChangeSeriesColor.xlsx", ExcelVersion.Version2010);
}
}
}
Imports System.Drawing
Imports Spire.Xls
Imports Spire.Xls.Charts
Namespace Change_Series_Color
Class Program
Private Shared Sub Main(args As String())
Dim book As New Workbook()
book.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = book.Worksheets(0)
Dim cs As ChartSerie = sheet.Charts(0).Series(1)
cs.Format.Fill.FillType = ShapeFillType.SolidColor
cs.Format.Fill.ForeColor = Color.FromKnownColor(KnownColor.Purple)
book.SaveToFile("ChangeSeriesColor.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
We have demonstrated how to insert textbox to Excel worksheet in C#. Starts from Spire.XLS v7.11.1, we have add a new method of chart.Shapes.AddOval(left,top,right,bottom); to enable developers to add oval shape to excel chart directly. Developers can also add the text contents to the oval shape and format the style for the oval. This article will describe clearly how to insert oval shape to Excel chart in C#.
Step 1: Create a workbook and get the first worksheet from the workbook.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0];
Step 2: Add a chart to the worksheet.
Chart chart = sheet.Charts.Add();
Step 3: Add oval shape to Excel chart.
var shape = chart.Shapes.AddOval(20, 60, 500,400);
Step 4: Add the text to the oval shape and set the text alignment on the shape.
shape.Text = "Oval Shape added by Spire.XLS"; shape.HAlignment = CommentHAlignType.Center; shape.VAlignment = CommentVAlignType.Center;
Step 5: Format the color for the oval shape.
((XlsOvalShape)shape).Line.ForeColor = Color.Blue; ((XlsOvalShape)shape).Fill.ForeColor = Color.Green;
Step 6: Save the document to file.
workbook.SaveToFile("Result.xlsx",ExcelVersion.Version2010);
Effective screenshot of Oval shape added to the Excel chart:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
using System.Drawing;
namespace AddOvalShape
{
class Program
{
static void Main(string[] args)
{
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts.Add();
var shape = chart.Shapes.AddOval(20, 60, 500, 400);
shape.Text = "Oval Shape added by Spire.XLS";
shape.HAlignment = CommentHAlignType.Center;
shape.VAlignment = CommentVAlignType.Center;
((XlsOvalShape)shape).Line.ForeColor = Color.Blue;
((XlsOvalShape)shape).Fill.ForeColor = Color.Green;
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
}
}
}
Use Discontinuous Data Range to Create Chart in Excel
2016-09-22 09:10:09 Written by support iceblueApart from creating chart with continuous data range, Spire.XLS also supports to create chart with discontinuous data range by calling the XlsRange.AddCombinedRange(CellRange cr) method. This example explains a quick solution of how to achieve this task in C# with the help of Spire.XLS.
For demonstration, here we used a template excel document, in which you can see there are some blank rows among the data, in other words, the data range is discontinuous.
Here comes to the detail steps:
Step 1: Instantiate a Wordbook object, load the excel document and get its first worksheet.
Workbook book = new Workbook();
book.LoadFromFile("Sample.xlsx");
Worksheet sheet = book.Worksheets[0];
Step 2: Add a column chart to the first worksheet and set the position of the chart.
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered); chart.SeriesDataFromRange = false; //Set chart position chart.LeftColumn = 5; chart.TopRow = 1; chart.RightColumn = 13; chart.BottomRow = 10;
Step 3: Add two series to the chart, set data source for category labels and values of the series with discontinuous data range.
//Add the first series var cs1 = (ChartSerie)chart.Series.Add(); //Set name of the serie cs1.Name = sheet.Range["B1"].Value; //Set data source for Category Labels and Values of the serie with discontinuous data range 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"]); //Specify the serie type cs1.SerieType = ExcelChartType.ColumnClustered; //Add the second series var cs2 = (ChartSerie)chart.Series.Add(); cs2.Name = sheet.Range["C1"].Value; cs2.CategoryLabels = 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.ColumnClustered;
Step 4: Save the excel document.
book.SaveToFile("Result.xlsx", FileFormat.Version2010);
After executing the above example code, a column chart with discontinuous data range was added to the worksheet as shown below.
Full code:
using Spire.Xls;
using Spire.Xls.Charts;
namespace Assign_discontinuous_range_for_chart
{
class Program
{
static void Main(string[] args)
{
Workbook book = new Workbook();
book.LoadFromFile("Sample.xlsx");
Worksheet sheet = book.Worksheets[0];
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.SeriesDataFromRange = false;
chart.LeftColumn = 5;
chart.TopRow = 1;
chart.RightColumn = 13;
chart.BottomRow = 10;
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.ColumnClustered;
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.ColumnClustered;
chart.ChartTitle = string.Empty;
book.SaveToFile("Result.xlsx", FileFormat.Version2010);
System.Diagnostics.Process.Start("Result.xlsx");
}
}
}
Excel version 2013 added a fantastic feature in Chart Data Label option that you can custom data labels from a column/row of data. The chart below uses labels from the data in cells C2: C5 next to the plotted values. This article will present how to add labels to data points using the values from cells in C#.
Code Snippets:
Step 1: Initialize a new instance of Workbook class and set the Excel version as 2013.
Workbook wb = new Workbook(); wb.Version = ExcelVersion.Version2013;
Step 2: Get the first sheet from workbook.
Worksheet ws = wb.Worksheets[0];
Step 3: Insert data.
ws.Range["A2"].Text = "Product 1"; ws.Range["A3"].Text = "Product 2"; ws.Range["A4"].Text = "Product 3"; ws.Range["A5"].Text = "Product 4"; ws.Range["B1"].Text = "Sales"; ws.Range["B1"].Style.Font.IsBold = true; ws.Range["B2"].NumberValue = 251; ws.Range["B3"].NumberValue = 515; ws.Range["B4"].NumberValue = 454; ws.Range["B5"].NumberValue = 874; ws.Range["C1"].Text = "+/-\nPrevious\nPeriod"; ws.Range["C1"].Style.Font.IsBold = true; ws.Range["C2"].NumberValue = -120; ws.Range["C3"].NumberValue = 31; ws.Range["C4"].NumberValue = -76; ws.Range["C5"].NumberValue = 201; ws.SetRowHeight(1, 40);
Step 4: Insert a Clustered Column Chart in Excel based on the data range from A1:B5.
Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered); chart.DataRange = ws.Range["A1:B5"]; chart.SeriesDataFromRange = false; chart.PrimaryValueAxis.HasMajorGridLines = false;
Step 5: Set chart position.
chart.LeftColumn = 5; chart.TopRow = 2; chart.RightColumn = 13; chart.BottomRow = 22;
Step 6: Add labels to data points using the values from cell range C2:C5.
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];
Step 7: Save and launch the file.
wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Full Code:
using Spire.Xls;
namespace CustomLabels
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.Version = ExcelVersion.Version2013;
Worksheet ws = wb.Worksheets[0];
ws.Range["A2"].Text = "Product 1";
ws.Range["A3"].Text = "Product 2";
ws.Range["A4"].Text = "Product 3";
ws.Range["A5"].Text = "Product 4";
ws.Range["B1"].Text = "Sales";
ws.Range["B1"].Style.Font.IsBold = true;
ws.Range["B2"].NumberValue = 251;
ws.Range["B3"].NumberValue = 515;
ws.Range["B4"].NumberValue = 454;
ws.Range["B5"].NumberValue = 874;
ws.Range["C1"].Text = "+/-\nPrevious\nPeriod";
ws.Range["C1"].Style.Font.IsBold = true;
ws.Range["C2"].NumberValue = -120;
ws.Range["C3"].NumberValue = 31;
ws.Range["C4"].NumberValue = -76;
ws.Range["C5"].NumberValue = 201;
ws.SetRowHeight(1, 40);
Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = ws.Range["A1:B5"];
chart.SeriesDataFromRange = false;
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.LeftColumn = 5;
chart.TopRow = 2;
chart.RightColumn = 13;
chart.BottomRow = 22;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];
wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
How to set the font for legend and datalable in Excel Chart
2016-04-19 01:20:48 Written by support iceblueWith the help of Spire.XLS, developers can easily set the font for the text for Excel chart. We have already demonstrate how to set the font for TextBox in Excel Chart, this article will focus on demonstrating how to set the font for legend and datalable in Excel chart by using the SetFont() method to change the font for the legend and datalable easily in C#.
Firstly, please view the Excel worksheet with chart which the font will be changed later:
Note: Before Start, please download the latest version of Spire.XLS and add Spire.Xls.dll in the bin folder as the reference of Visual Studio.
Step 1: Create a new Excel workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet from workbook.
Worksheet ws = workbook.Worksheets[0]; Spire.Xls.Chart chart = ws.Charts[0];
Step 3: Create a font with specified size and color.
ExcelFont font = workbook.CreateFont(); font.Size =12.0; font.Color = Color.Red;
Step 4: Apply the font to chart Legend.
chart.Legend.TextArea.SetFont(font);
Step 5: Apply the font to chart DataLabel.
foreach (ChartSerie cs in chart.Series)
{
cs.DataPoints.DefaultDataPoint.DataLabels.TextArea.SetFont(font);
}
Step 6: Save the document to file.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
Effective screenshot after changing the text font.
Full codes:
using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;
namespace SetFont
{
class Program
{
static void Main(string[] args)
{
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
Spire.Xls.Chart chart = ws.Charts[0];
ExcelFont font = workbook.CreateFont();
font.Size = 12.0;
font.Color = Color.Red;
chart.Legend.TextArea.SetFont(font);
foreach (ChartSerie cs in chart.Series)
{
cs.DataPoints.DefaultDataPoint.DataLabels.TextArea.SetFont(font);
}
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
}
When we work with Excel pie chart, we may need to separate each part of pie chart to make them stand out. Spire.XLS offers a property of Series.DataFormat.Percent to enable developers to pull the whole pie apart. It also offers a property of Series.DataPoints.DataFormat.Percent to pull apart a single slice from the whole pie chart.
This article is going to introduce the method of how to set the separation width between slices in pie chart in C# by using Spire.XLS.
On MS Excel, We can adjust the percentage of "Pie Explosion" on the Series Options at the "format data series" area to control the width between each section in the chart.
Code Snippet of how to set the separation width between slices in pie chart.
using Spire.Xls;
namespace ExplodePieChart
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
Chart chart = ws.Charts[0];
// Set the separation width between slices in pie chart.
for (int i = 0; i < chart.Series.Count; i++)
{
chart.Series[i].DataFormat.Percent = 20;
}
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
Effective screenshot after pull the whole pie apart.
Code Snippet of how to split a single slice from the whole pie chart.
using Spire.Xls;
namespace ExplodePieChart
{
class Program
{
static void Main(string[] args)
{
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
Chart chart = ws.Charts[0];
chart.Series[0].DataPoints[0].DataFormat.Percent = 20;
workbook.SaveToFile("ExplodePieChart.xlsx", ExcelVersion.Version2013);
}
}
}
}
Effective screenshot after pull a single part from the pie chart apart.
How to set the border color and styles for Excel Chart
2015-12-30 07:52:07 Written by support iceblueThe different border color and styles on the Excel Chart can distinguish the chart categories easily. Spire.XLS offers a property of LineProperties to enables developers to set the color and styles for the data point. This article is going to introduce the method of how to format data series for Excel charts in C# using Spire.XLS.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.xls.dll in the bin folder as the reference of Visual Studio.
Firstly, please check the original screenshot of excel chart with the automatic setting for border.
Code Snippet of how to set the border color and border styles for Excel chart data series.
Step 1: Create a new workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Get the first worksheet from workbook and then get the first chart from the worksheet.
Worksheet ws = workbook.Worksheets[0]; Chart chart = ws.Charts[0];
Step 3: Set CustomLineWeight property for Series line.
(chart.Series[0].DataPoints[0].DataFormat.LineProperties as XlsChartBorder).CustomLineWeight = 1.5f;
Step 4: Set Color property for Series line.
(chart.Series[0].DataPoints[0].DataFormat.LineProperties as XlsChartBorder).Color = Color.Red;
Step 5: Save the document to file.
workbook.SaveToFile("result.xlsx", FileFormat.Version2013);
Effective screenshot after set the color and width of excel chart border.
Full codes:
using Spire.Xls;
using Spire.Xls.Charts;
using Spire.Xls.Core.Spreadsheet.Charts;
using System.Drawing;
namespace SetBoarderColor
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
Chart chart = ws.Charts[0];
(chart.Series[0].DataPoints[0].DataFormat.LineProperties as XlsChartBorder).CustomLineWeight = 1.5f;
(chart.Series[0].DataPoints[0].DataFormat.LineProperties as XlsChartBorder).Color = Color.Red;
workbook.SaveToFile("result.xlsx", FileFormat.Version2013);
}
}
}
How to Adjust the Spaces between Bars in Excel Chart in C#, VB.NET
2015-12-30 07:34:49 Written by support iceblueIn MS Excel, the spaces between data bars have been defined as Series Overlap and Gap Width.
- Series Overlap: Spaces between data series within a single category.
- Gap Width: Spaces between two categories.
Check below picture, you'll have a better understanding of these two concepts. Normally the spaces are automatically calculated based on the date and chart area, the space may be very narrow or wide depending on how many date series you have in a fixed chart area. In this article, we'll introduce how to adjust the spaces between data bars using Spire.XLS.
Code Snippet:
Step 1: Initialize a new instance of Wordbook class and load the sample Excel file that contains some data in A1 to C5.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Create a Column chart based on the data in cell range A1 to C5.
Worksheet sheet = workbook.Worksheets[0]; Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered); chart.DataRange = sheet.Range["A1:C5"]; chart.SeriesDataFromRange = false; chart.PrimaryValueAxis.MajorGridLines.LineProperties.Color = Color.LightGray;
Step 3: Set chart position.
chart.LeftColumn = 5; chart.TopRow = 7; chart.RightColumn = 13; chart.BottomRow = 21;
Step 4: The ChartSerieDataFormat class has two properties - GapWidth property and Overlap property to handle the Gap Width and Series Overlap respectively. The value of GapWidth varies from 0 to 500, and the value of Overlap varies from -100 to 100.
foreach (ChartSerie cs in chart.Series)
{
cs.Format.Options.GapWidth = 200;
cs.Format.Options.Overlap = 0;
}
Step 5: Save and launch the file.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Output:
Full Code:
using Spire.Xls;
using Spire.Xls.Charts;
namespace AdjustSpaces
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("data.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
chart.PrimaryValueAxis.MajorGridLines.LineProperties.Color = Color.LightGray;
chart.LeftColumn = 5;
chart.TopRow = 7;
chart.RightColumn = 13;
chart.BottomRow = 21;
foreach (ChartSerie cs in chart.Series)
{
cs.Format.Options.GapWidth = 200;
cs.Format.Options.Overlap = 0;
}
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Charts
Namespace AdjustSpaces
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("data.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("A1:C5")
chart.SeriesDataFromRange = False
chart.PrimaryValueAxis.MajorGridLines.LineProperties.Color = Color.LightGray
chart.LeftColumn = 5
chart.TopRow = 7
chart.RightColumn = 13
chart.BottomRow = 21
For Each cs As ChartSerie In chart.Series
cs.Format.Options.GapWidth = 200
cs.Format.Options.Overlap = 0
Next
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")
End Sub
End Class
End Namespace
Gridlines are often added to charts to help improve the readability of the chart itself, but it is not a necessary to display the gridlines in every chart especially when we do not need to know the exact value of each data point from graphic. This article will present how to hide gridlines in Excel chart using Spire.XLS.
Code Snippet:
Step 1: Initialize a new instance of Workbook class and load a sample Excel file that contains some data in A1 to C5.
Workbook workbook = new Workbook();
workbook.LoadFromFile("data.xlsx");
Step 2: Create a Column chart based on the data in cell range A1 to C5.
Worksheet sheet = workbook.Worksheets[0]; Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered); chart.DataRange = sheet.Range["A1:C5"]; chart.SeriesDataFromRange = false;
Step 3: Set chart position.
chart.LeftColumn = 1; chart.TopRow = 6; chart.RightColumn = 8 chart.BottomRow = 19;
Step 4: Set the PrimaryValueAxis.HasMajorGridLines property to false.
chart.PrimaryValueAxis.HasMajorGridLines = false;
Step 5: Save and launch the file.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Output:
Full Code:
using Spire.Xls;
namespace HideGridLine
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("data.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 8;
chart.BottomRow = 19;
chart.PrimaryValueAxis.HasMajorGridLines = false;
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Imports Spire.Xls
Namespace HideGridLine
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("data.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("A1:C5")
chart.SeriesDataFromRange = False
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 8
chart.BottomRow = 19
chart.PrimaryValueAxis.HasMajorGridLines = False
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")
End Sub
End Class
End Namespace
The leader line on Excel chart is very helpful since it gives a visual connection between a data label and its corresponding data point. Spire.XLS offers a property of DataLabels.ShowLeaderLines to enable developers to show or hide the leader lines easily. This article will focus on demonstrating how to show the leader line on Excel stacked bar chart in C#.
Note: Before Start, please ensure that you have download the latest version of Spire.XLS (V7.8.64 or above) and add Spire.xls.dll in the bin folder as the reference of Visual Studio.
Here comes to the code snippet of how to show the leader line on Excel stacked bar chart in C#.
Step 1: Create a new excel document instance and get the first worksheet.
Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0];
Step 2: Add some data to the Excel sheet cell range.
sheet.Range["A1"].Value = "1"; sheet.Range["A2"].Value = "2"; sheet.Range["A3"].Value = "3"; sheet.Range["B1"].Value = "4"; sheet.Range["B2"].Value = "5"; sheet.Range["B3"].Value = "6";
Step 3: Create a bar chart and define the data for it.
Chart chart = sheet.Charts.Add(ExcelChartType.BarStacked); chart.DataRange = sheet.Range["A1:B3"];
Step 4: Set the property of HasValue and ShowLeaderLines for DataLabels.
foreach (ChartSerie cs in chart.Series)
{
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
cs.DataPoints.DefaultDataPoint.DataLabels.ShowLeaderLines = true;
}
Step 5: Save the document to file and set the excel version.
book.Version = ExcelVersion.Version2013;
book.SaveToFile("result.xlsx", FileFormat.Version2013);
Effective screenshots:
Full codes:
using Spire.Xls;
using Spire.Xls.Charts;
using Spire.Xls.Core.Spreadsheet.Charts;
using System.Drawing;
namespace ShowLeaderLine
{
class Program
{
static void Main(string[] args)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.Range["A1"].Value = "1";
sheet.Range["A2"].Value = "2";
sheet.Range["A3"].Value = "3";
sheet.Range["B1"].Value = "4";
sheet.Range["B2"].Value = "5";
sheet.Range["B3"].Value = "6";
Chart chart = sheet.Charts.Add(ExcelChartType.BarStacked);
chart.DataRange = sheet.Range["A1:B3"];
foreach (ChartSerie cs in chart.Series)
{
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
cs.DataPoints.DefaultDataPoint.DataLabels.ShowLeaderLines = true;
}
book.Version = ExcelVersion.Version2013;
book.SaveToFile("result.xlsx", FileFormat.Version2013);
}
}
}
