Displaying of chart labels

Aspose.Cells Product Family
1

Hi,


We need to display chart lables formatted as callouts (pls see attached). It looks like corresponding option is missed in Aspose.Cells .NET API.

Could you suggest any work around?

Thank you in advance!
2

Hi,


Thanks for the screenshot.

I am afraid, adding data labels as Callouts feature (MS Excel 2013) is not supported although you may add/show leaderlines for datalabels in the chart (e.g using chart.NSeries[i].HasLeaderLines = true;) if it suits your needs. Do you still want us to add a ticket for it into our database? We can log a ticket for product team’s investigation upon your response.

Thank you.
3

Hi


That would be great to have such feature in Excel.Cells. Could you add a corresponding ticket?

Thank you!
4

Hi,


Thanks for your confirmation.

I have logged your requested feature with an id “CELLSNET-45311” into our database. Our concerned developer from product team will evaluate it soon.

Once we have an update on it, we will let you know here.

Thank you.
5

Hi,


This is to update you that we will support data label shapes in Q4 2017.

Thank you.
6

@dashfi

Thanks for using Aspose APIs.

We have supported this feature. Please download and try the following fix. Please use the

Series.DataLabels.ShapeType

property for your needs.

Here is sample code for your reference. Please check the source Excel file used in this code and output Excel file generated by the code as well as screenshot showing the effect of the code.

C#

//Load source Excel file
Workbook wb = new Workbook("Chart.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access first chart
Chart ch = ws.Charts[0];

//Access first series
Series srs = ch.NSeries[0];

//Set the shape type of data labels i.e. Speech Bubble Oval
srs.DataLabels.ShapeType = DataLabelShapeType.WedgeEllipseCallout;

//Save the output Excel file
wb.Save("outChart.xlsx");

Screenshot:

Download Links:

7

Ah, I’m looking for this feature, now I can set callout for my charts.
But I noticed “OutsideEnd” doesn’t work for Doughnut chart. How can I achieved that like Pie chart?

8

@mchen11

Thanks for considering Aspose APIs.

Please provide us your sample code and sample Excel file that we could test at our end and replicate your issue. We will look into it and help you asap.

9

This is what I got for doughnut chart by using callouts and set positions as “OutsideEnd”

image.png (16.3 KB)

But what I expect is
image.png (22.5 KB)

Is this possible to achieve this programmatically?

10

@mchen11

Please provide both Excel files instead of Images because we need to load them in Workbook object and check this issue. Thanks for your understanding and cooperation.

11

DoughnutChartCallouts.zip (30.5 KB)

Please check

12

@mchen11

Thanks for using Aspose APIs.

You must replace the X, Y position of the data labels to see the call out shape properly. You can do it using Microsoft Excel in the same way. Please see the following sample code that explains how to do it using Aspose.Cells.

Please also see the comments inside the code, its output Excel file as well as screenshot for a reference.

Download Output Excel File
outputDougnut.zip (8.1 KB)

C#

var workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells["A1"].PutValue("Type");
worksheet.Cells["A2"].PutValue("Cash");
worksheet.Cells["A3"].PutValue("Equity");
worksheet.Cells["A4"].PutValue("Bank");
worksheet.Cells["A5"].PutValue("Futures");
worksheet.Cells["A6"].PutValue("Options");
worksheet.Cells["A7"].PutValue("Loan");
worksheet.Cells["A8"].PutValue("Bond");
worksheet.Cells["A9"].PutValue("Stock");
worksheet.Cells["A10"].PutValue("Warrant");
worksheet.Cells["A11"].PutValue("Swap");

worksheet.Cells["B1"].PutValue("Alpha");
worksheet.Cells["B2"].PutValue(1.73);
worksheet.Cells["B3"].PutValue(1.34);
worksheet.Cells["B4"].PutValue(2.33);
worksheet.Cells["B5"].PutValue(1.88);
worksheet.Cells["B6"].PutValue(1.53);
worksheet.Cells["B7"].PutValue(2.11);
worksheet.Cells["B8"].PutValue(1.64);
worksheet.Cells["B9"].PutValue(1.43);
worksheet.Cells["B10"].PutValue(1.14);
worksheet.Cells["B11"].PutValue(-1.33);

worksheet.Cells["C1"].PutValue("Beta");
worksheet.Cells["C2"].PutValue(0.11);
worksheet.Cells["C3"].PutValue(-0.12);
worksheet.Cells["C4"].PutValue(0.11);
worksheet.Cells["C5"].PutValue(-0.81);
worksheet.Cells["C6"].PutValue(0.12);
worksheet.Cells["C7"].PutValue(-0.71);
worksheet.Cells["C8"].PutValue(0.11);
worksheet.Cells["C9"].PutValue(0.12);
worksheet.Cells["C10"].PutValue(0.121);
worksheet.Cells["C11"].PutValue(0.51);

worksheet.Cells["D1"].PutValue("Size");
worksheet.Cells["D2"].PutValue(-0.63);
worksheet.Cells["D3"].PutValue(-0.12);
worksheet.Cells["D4"].PutValue(0.3);
worksheet.Cells["D5"].PutValue(0.38);
worksheet.Cells["D6"].PutValue(-0.24);
worksheet.Cells["D7"].PutValue(0.3);
worksheet.Cells["D8"].PutValue(0.33);
worksheet.Cells["D9"].PutValue(0.49);
worksheet.Cells["D10"].PutValue(-0.53);
worksheet.Cells["D11"].PutValue(0.73);

int chartIndex = worksheet.Charts.Add(ChartType.Doughnut, 14, 0, 40, 12);
var chart = worksheet.Charts[chartIndex];

var idx = chart.NSeries.Add("B2:B11", true);
chart.NSeries.CategoryData = "A2:A11";
var series = chart.NSeries[idx];
series.DataLabels.ShapeType = DataLabelShapeType.WedgeRoundRectCallout;
series.DataLabels.ShowValue = true;
series.DataLabels.ShowCategoryName = true;
series.DataLabels.ShowPercentage = true;
series.DataLabels.Position = LabelPositionType.OutsideEnd;
series.DataLabels.Area.ForegroundColor = Color.GreenYellow; //<<<<<<<<<<<<<<<<<<<<<<<<<
series.HasLeaderLines = false;

//----------------------------------------------
//----------------------------------------------
chart.Calculate();

Series srs = chart.NSeries[0];

for (int i = 0; i < srs.Points.Count; i++)
{
    ChartPoint cp = srs.Points[i];

    //These lines are important <<<<<<<<<<<<<<<<<<<<<<<<<<
    cp.DataLabels.X = cp.DataLabels.X + 500;
    cp.DataLabels.Y = cp.DataLabels.Y + 500;
}
//----------------------------------------------
//----------------------------------------------

workbook.Save("outputDougnut.xlsx");

Screenshot:

13

Okay, after checking this in excel, I realized that Microsoft Excel doesn’t support label position in doughnut chart at all.

Thanks for the sample code.

14

One more thing, based on my code, if you change the chart type to Pie chart and set label postion= outsideEnd
then save it as pdf, the borders of the lables will be gone, and the labels background becomes transparent, set BackgroundMode=Opaque doesn’t work at all.

15

@mchen11

Thanks for using Aspose APIs.

Please use the following code to fill them with some color. I have used this code to fill them with greenish color in the code given to you in previous post.

C#

//Fill data labels with some color
series.DataLabels.Area.ForegroundColor = Color.GreenYellow;
16

Thanks!

I also managed to achieve this by below 3 lines of code:

series.DataLables.Area.FillFormat.FillType = FillType.Solid;
series.DataLables.Area.FillFormat.SolidFill.Color = Color.White;
series.DataLables.Area.FillFormat.SolidFill.Transparency = 0;

but looks your approach is easier and the right way to go!

17

@mchen11

Thanks for your feedback. It is good to know that you were able to sort out this issue.

FYI:
Whenever you find some difficulty in sample code, you can create your Excel file manually using Microsoft Excel file (with desired charts, images or objects etc.) and then load it inside Workbook object using Aspose.Cells APIs. After that you can observe your objects and their properties in Debug or Quick Watch or Watches and note their settings and then use those same settings to create/replicate your object programmatically.