Pie Chart Data label with Category name- Value on custom format and Percentage display

Aspose.Cells Product Family
1

I have attached sample Excel pie chart, mainly in that i want to show data label with custom data formatted vales and percentage.


Please update me as soon as possible.
2

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the following properties to format your Pie Chart Data Labels.

  • DataLabels.ShowCategoryName
  • DataLabels.ShowPercentage
  • DataLabels.ShowValue

If you also want to show Leader Lines, then please use this property

  • Chart.NSeries[0].HasLeaderLines

Please see the following code for your reference. It formats the pie chart data labels. I have also attached the source xlsx file used in this code and output xlsx file generated by this code for you to view. Also attached the screenshot showing the comparison of source and output file.

C#

//Create workbook

Workbook workbook = new Workbook(“source.xlsx”);


//Access the worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Access the Pie Chart

Chart ch = worksheet.Charts[0];


//Format the DataLabels of Chart Series

DataLabels dataLabels = ch.NSeries[0].DataLabels;

dataLabels.ShowCategoryName = true;

dataLabels.ShowPercentage = true;

dataLabels.ShowValue = true;


//Show the Leader Lines

ch.NSeries[0].HasLeaderLines = true;


//Save the workbook

workbook.Save(“output.xlsx”);


3

With The Same i tried But still i am not getting Border for label as show in sample and Number Format for Value how to Change its given cell value only.

4

Hi,

Thanks for your posting and using Aspose.Cells.

The output.xlsx file I provided to you has the same Pie Chart as you have provided in your Test Pie Chart.xlsx.

If you need something else, then please provide us your sample Pie Chart and highlight your issue in a screenshot so that we could look into it precisely and help you asap.

5

Hi,


Please find attachment.

In that you can find label content inside border With White Background.
Same way i want in chart.
6

Hi,


I have added a few lines to set the borders around data labels and specify the background color fill for your needs. I used the similar code segment posted by Shakeel Faiz to update it to add a few lines for your requirements, please refer to it:
e.g
Sample code:

//Create workbook

Workbook workbook = new Workbook(“e:\test2\source.xlsx”);


//Access the worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Access the Pie Chart

Chart ch = worksheet.Charts[0];


//Format the DataLabels of Chart Series

DataLabels dataLabels = ch.NSeries[0].DataLabels;

dataLabels.ShowCategoryName = true;

dataLabels.ShowPercentage = true;

dataLabels.ShowValue = true;



Series series = ch.NSeries[0];
//Set the background color, border lines for the data labels etc.
series.DataLabels.Area.ForegroundColor = Color.White;
series.DataLabels.Border.IsVisible = true;
series.DataLabels.Border.Style = LineType.Solid;
series.DataLabels.Border.Weight = WeightType.SingleLine;
series.DataLabels.Border.Color = Color.Black;
series.DataLabels.Separator = DataLablesSeparatorType.Comma;



//Show the Leader Lines

ch.NSeries[0].HasLeaderLines = true;


//Save the workbook

workbook.Save(“e:\test2\out1.xlsx”);

Hope, it helps you a bit.

Thank you.


7

Hi,

Thanks for your support borders are activated now, but still i have one issue the label data is overlapping one another can you please send related properties to avoid overlapping of labels and possible show pointing lines as i shown in sample above.

Thanks & Regards
Ravi
8

Hi,


Well, you got to adjust chart’s height and width to show data labels properly, the more you set the width and height of data labels frame or even chart’s object, the more chances will be minimized for data labels to be overlapped. Moreover, by default, the trendlines would appear automatically (by MS Excel) when your plot area/chart area would be less, if the area is wide (height and width), the trendlines won’t appear, you may confirm this behavior in MS Excel.

I have added a few lines to my previous code segment using the template file Shakeel Faiz attached, the data labels are not overlapping and also the trendlines are displayed accordingly:
e.g
Sample code:

//Create workbook

Workbook workbook = new Workbook(“e:\test2\source.xlsx”);


//Access the worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Access the Pie Chart

Chart ch = worksheet.Charts[0];


//Format the DataLabels of Chart Series

DataLabels dataLabels = ch.NSeries[0].DataLabels;

dataLabels.ShowCategoryName = true;

dataLabels.ShowPercentage = true;

dataLabels.ShowValue = true;



Series series = ch.NSeries[0];
//Set the background color, border lines for the data labels etc.
series.DataLabels.Area.ForegroundColor = Color.White;
series.DataLabels.Border.IsVisible = true;
series.DataLabels.Border.Style = LineType.Solid;
series.DataLabels.Border.Weight = WeightType.SingleLine;
series.DataLabels.Border.Color = Color.Black;
series.DataLabels.Separator = DataLablesSeparatorType.Comma;

//Show the Leader Lines
ch.NSeries[0].HasLeaderLines = true;


//Specify the chart’s width and height accordingly.
///e.g
ch.ChartObject.Height = 250;
ch.ChartObject.Width = 430;


//Save the workbook
workbook.Save(“e:\test2\out_1.xlsx”);

I have also attached the output Excel file for your reference.

Thank you.
9

Yes this behavior in MS Excel, but how can we achieve in default it should show proper.



10

Hi,


As you confirmed the behavior in MS Excel manually too. Please see my previous post for reference. For data labels, the more you set the width and height of data labels frame or even chart’s object, the more chances will be minimized for data labels to be overlapped. Also to show trendlines with the chart’s data labels, you have to use the line of code:
e.g
Sample code:

//Show the Leader Lines
ch.NSeries[0].HasLeaderLines = true;

but if the chart/ plot area is wide (with more height and width), the trendlines won’t appear.

Aspose.Cells follows MS Excel standards and specifications.

Thank you.


11

Hi,


Can you please let me know how to apply 3-D ROTATION, Pie Chart Options of Excel using aspose component.
12

Hi,


It looks like there is no relevant APIs for 3-D Rotation in Aspose.Cells, we will check it out thoroughly though. If we found the relevant APIs, we will get back to you.

By the way, if you need to specify 3D formattings e.g Bevel with its height/width to datalabels, you may refer to the following sample code (especially the lines of code in bold):
e.g
Sample code:

//Create workbook

Workbook workbook = new Workbook(“e:\test2\source.xlsx”);


//Access the worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Access the Pie Chart

Chart ch = worksheet.Charts[0];


//Format the DataLabels of Chart Series

DataLabels dataLabels = ch.NSeries[0].DataLabels;

dataLabels.ShowCategoryName = true;

dataLabels.ShowPercentage = true;

dataLabels.ShowValue = true;



Series series = ch.NSeries[0];
//Set the background color, border lines for the data labels etc.
series.DataLabels.Area.ForegroundColor = Color.White;
series.DataLabels.Border.IsVisible = true;
series.DataLabels.Border.Style = LineType.Solid;
series.DataLabels.Border.Weight = WeightType.SingleLine;
series.DataLabels.Border.Color = Color.Black;
series.DataLabels.Separator = DataLablesSeparatorType.Comma;
series.DataLabels.RotationAngle = 30;

//Show the Leader Lines
ch.NSeries[0].HasLeaderLines = true;


//Specify 3D formattings e.g Bevel with its height/width to datalabels.
Aspose.Cells.Drawing.ShapePropertyCollection spPr = series.DataLabels.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 10.5;
bevel.Width = 9.5;


//Specify the chart’s width and height accordingly.
///e.g
ch.ChartObject.Height = 250;
ch.ChartObject.Width = 430;


//Save the workbook
workbook.Save(“e:\test2\out1.xlsx”);

Thank you.