How to change position of data labels

Aspose.Cells Product Family
1

I have the problem that the data labels in a pie chart may overlap, depending on the data, and there seems to be no option in Excel to avoid this - at least that's my impression after some googling. So I tried to change the position of overlapping labels in my code.

I use an Excel file that contains the data and the chart already (see the attached Excel file). Here's my sample code, which should change the X position of the first label:

Workbook workbook = new Workbook(Server.MapPath("_AsposeTest.xlsx"));
Chart chart = workbook.Worksheets[0].Charts[0];
ChartPointCollection chartPoints = chart.NSeries[0].Points;

chartPoints[0].DataLabels.X += 50; // should shift the label to some amount to the right.

workbook.Save(Response, "_AsposeTest2.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions());

But the label position does not change. And, what's really strange, after code execution all data labels include the data series name (see the attached screenshot), although the original Excel file does not show these names, because this is disabled the charts' options in the Excel file.

What am I doing wrong? Or is this a bug? I'm using Aspose.Cells 7.3.1.0 for ASP.NET in a web application.

[Edit:] I just found out that the behaviour shown in the screenshot occurs in Excel 2010, but not in Excel 2007.

2

Hi,


How did you create such a file, did you use Aspose.Cells to create the file with that chart having labels overlapping? Please give us the code to create the chart if you are using Aspose.Cells. Also provide your expected chart that you may manually set or adjust the labels to be not overlapped in MS Excel. We will check your issue soon.

Thank you.
3

Thank you for your feedback!

My actual project is a web application that allows users to enter various data and that offers a downloadable Excel file containing a summary report. To isolate the problem, I have created a sample solution in Visual Studio 2010 which I have attached as ZIP archive. Please ignore the samples from my first post.

The sample solution contains:

  • 'ExcelFile_Before.xlsx': The source file that is manipulated in the solution using Aspose.Cells for ASP.NET. It was manually created with Excel 2010 and contains some sample data and a pie chart. As you can see, the chart data labels for "Red" and "Blue" overlap. This overlapping should be fixed by the code in 'ExcelFile.aspx.cs'.
  • 'Default.aspx': Start page. Click on the button to run the code and open the resulting Excel file 'ExcelFile_After.xlsx' in a new browser window.
  • 'ExcelFile.aspx': opens 'ExcelFile_After.xlsx'. Manipulation of source Excel file is done in code-behind method Page_Load(). See notes below for details.
  • 'ExcelFile_After_Mine.xlsx': Not part of the solution, but the file I get as result.

Notes concerning the code in 'ExcelFile.aspx.cs':
To fix the label overlapping in the source Excel file, I open the file with Aspose.Cells for ASP.NET and try to move the "Blue" label to the right of the "Red" label.

The expected behaviour is:

  1. In the resulting file 'ExcelFile_After.xlsx', the "Blue" label should have been moved to a position somewhere right to the "Red" label - the exact resulting position does not matter in this sample.
  2. The text of the "Blue" label should have been changed to "This label has been moved.". This is not really required in my actual application, but it's a test to check if my code accesses the correct label at all.

The actual behaviour can be seen in the file 'ExcelFile_After_Mine.xlsx':

  1. Not OK: The "Blue" label has not changed its position.
  2. OK: The text of the "Blue" label has changed as expected.
  3. Strange: If you open the file in Excel 2010, the data series name has been added to the "Red" and "Green" data labels, which is not set as an option in the source Excel file. That is really strange, because my code does not access these two labels at all.
    However, if you open the resulting file in Excel 2007, the "Red" and "Green" data labels are unchanged (as expected).
    This behaviour does also not occur in Excel 2010 if you comment out the code line "chartPoints[0].DataLabels.X += 100;", so I suspect it is caused by Aspose.Cells somehow.

4

Hi,


Thanks for the sample project with details.

After an initial test, I can notice the issue as you have mentioned. I cannot move the data labels in the Pie chart as you have mentioned by running your project.

I have logged a ticket with an id “CELLSNET-41478” for your issue. We will look into your issue soon.

Thank you.
5

Hi,

Please download and try this fix: Aspose.Cells for .NET (Latest Version)

Kindly run and refer to the following code segments:

Chart c = sheet.Charts[j];

//For getting the default position of data labels

c.Calculate();

ChartPointCollection chartPoints = c.NSeries[0].Points;

//Moves the labels

chartPoints[0].DataLabels.X += 1000;

Thank you.

6

Hi,

thank you very much! That solved the problems.

7

Hi,

Thanks for your feedback and using Aspose.Cells.

We are pleased to know that your issue is resolved. If you face any other issue, please feel free to post on our forums, we will be glad to assist you further.

8

The issues you have found earlier (filed as CELLSNET-41478) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.