Is there any way to avoid label overlaps when using ColumStacked chart with DataLabels.ShowValue = true?

Aspose.Cells Product Family
1

I’ve searched for a while but can’t seem to find any solution, I want to confirm if it’s possible or not

Is there a way to avoid the labels from overlapping when setting ShowValue = true?

I’d like to be able to set a minimal height for each series that would allow for the text to always fit inside the column subsection dedicated to each value.

If it can help, I don’t really mind if the spacing on the Y axis ends up being uneven as a result since I usually hide the axis.

Here’s a code snippet that shows my issue, I’ve attached the resulting chart image as well

Workbook workbook = new Workbook();
int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];

worksheet.Cells[“A1”].PutValue(2000);
worksheet.Cells[“A2”].PutValue(50);
worksheet.Cells[“B1”].PutValue(50);
worksheet.Cells[“B2”].PutValue(25);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1:B2”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
}

chart.ToImage(“DataLabelOverlap.png”);

2

Hi,


Thanks for your posting and using Aspose.Cells.

If it is possible with Microsoft Excel, then it will also be possible with Aspose.Cells in most cases. Please provide us your actual output excel and expected output excel file. You can create expected output excel file in Microsoft Excel and then we will load it inside Aspose.Cells Workbook object and inspect its various properties and provide you a sample code. You can do the same thing yourself. Just load your source excel file and then check the modified properties/values and then write a sample code to achieve the similar thing via Aspose.Cells API.

3

Hi

There doesn’t seem to be any trivial way to do what I want in Excel

So far the only idea I have would be to use a different set of data series for the labels and for the data used in the graph.

Essentially the labels would use the real data, and I’d use a 2nd set of data to draw the graph columns themselves. I’d apply a minimum on this 2nd set of data so that each section is big enough to fit the text properly.

It’s kind of a big hack but since nothing else seems to be available out of the box…

For now it should be good enough

Thank you for your feedback

4

Hi,


Thanks for your posting and using Aspose.Cells.

You can change the position of your data labels via Aspose.Cells API, but there is no way to know if they are overlapping or not.

Please see the following sample code, its source excel file and the output excel file for a reference.

C#
Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

Chart ch = ws.Charts[0];
ch.Calculate();

Series srs = ch.NSeries[0];

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

o.DataLabels.X += 150;

}

wb.Save(“output.xlsx”);