Aspose Cell scatter chart data Label overlapping handing

Screenshot 2025-01-02 130351.jpg (86.1 KB)
Excel1.jpg (104.5 KB)

Excel2.jpg (95.5 KB)

when I scatter chart generating the using the Aspose cells some points data labels overlapping to another data label or point how to handle it.

above Excel1 and Excel2 photos my sample values each chart 12 data total 3 charts

public static void ScatterChartNew()
{
    // Step 1: Create a Workbook and Worksheet
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Name = "ScatterChart";

    // Step 2: Add Data for the Scatter Chart
    sheet.Cells["A1"].Value = "Required";
    sheet.Cells["B1"].Value = "Expressed";
    sheet.Cells["C1"].Value = "Point Name";

    // Example Data (You can replace with your own dataset)
    double[] required = { 7.0, 7.5, 8.0, 8.5, 9.0, 7.0, 8.0, 8.5 };
    double[] expressed = { 5, 6, 8, 9, 7, 5, 9, 7 };

    string[] labels = { "Hope", "Empathy", "Charisma", "Cooperation", "Friendliness", "Flexibility", "Friendliness", "Flexibility" };

    //TextFragment[] textFragments = { };

    for (int i = 0; i < required.Length; i++)
    {
        sheet.Cells[$"A{i + 2}"].Value = required[i];
        sheet.Cells[$"B{i + 2}"].Value = expressed[i];
        sheet.Cells[$"C{i + 2}"].Value = labels[i]; // Labels for points
    }

    double tolerance = 1.0;
    Boolean positive = true;

    // Calculate near neighbors
    int[] nearCounts = CalculateNearNeighbors(required, expressed, tolerance);
    int[] arr = new int[nearCounts.Length];

    for(int i = 0; i < nearCounts.Length; i++)
    {
        for (int j = i; j < nearCounts.Length; j++)
        {
            if (nearCounts[i] == nearCounts[j])
            {
                arr[j] += 1;

            }

        }
    }

    int rowCount = labels.Length;
    string dataRange = $"B2:B{rowCount + 1}";
    string categoryRange = $"A2:A{rowCount + 1}";

    int chartIndex = sheet.Charts.Add(ChartType.Scatter, 5, 0, 25, 10);
    Chart chart = sheet.Charts[chartIndex];
    chart.Title.Text = "Top 12";
    chart.Title.Font.Color = ColorTranslator.FromHtml("#2f5597");

    int seriesIndex = chart.NSeries.Add(dataRange, true);
    chart.NSeries.CategoryData = categoryRange;
    Series series = chart.NSeries[seriesIndex];

    series.DataLabels.ShowCategoryName = true;
    series.DataLabels.ShowValue = true;
    series.DataLabels.Position = LabelPositionType.Above;

    ChartCalculateOptions opt = new ChartCalculateOptions();
    opt.UpdateAllPoints = true;
    chart.Calculate(opt);

    for (int i = 0; i < series.Points.Count; i++)
    {
        DataLabels label = series.Points[i].DataLabels;
        label.Text = labels[i];
        label.IsAutoText = false;
        label.Position = LabelPositionType.Above;


        FontSetting fntSetting = label.Characters(0, labels[i].Length);
        fntSetting.Font.Color = Color.Black;
        fntSetting.Font.IsBold = false;
        fntSetting.Font.Size = 8;
        //fntSetting.Font.IsSuperscript = true;

        series.HasLeaderLines = true;
        series.Points[i].Marker.ForegroundColor = ColorTranslator.FromHtml("#2f5597");
        series.Points[i].Marker.BackgroundColor = ColorTranslator.FromHtml("#2f5597");
        series.Points[i].Marker.MarkerStyle = ChartMarkerType.Circle;
        series.Points[i].Shadow = true;
        if (arr[i] != 1)
        {
            if (positive)
            {
                series.Points[i].DataLabels.X += 100 * nearCounts[i];
                series.Points[i].DataLabels.Y += 50 * nearCounts[i];
                positive = false;
            }
            else
            {
                series.Points[i].DataLabels.X -= 100 * nearCounts[i];
                series.Points[i].DataLabels.Y -= 50 * nearCounts[i];
            }
            series.Points[i].DataLabels.Position = LabelPositionType.Above;
        }


    }

    int minValue = (int)Math.Floor(required.Min());
    int maxValue = (int)Math.Ceiling(required.Max());
    minValue = Math.Max(0, minValue - 1);
    maxValue += 1;

    Axis categoryAxis = chart.CategoryAxis;
    categoryAxis.MinValue = minValue;
    categoryAxis.MaxValue = maxValue;

    //Axis valueAxis = chart.ValueAxis;
    //valueAxis.MinValue = 9;
    //valueAxis.MaxValue = 1;



    //chart.NSeries[0].HasLeaderLines = true;
    //chart.NSeries[0].Points[0].DataLabels.X += 100;

    chart.ValueAxis.Title.Text = "Expressed"; // Set the Y-axis title
    chart.ValueAxis.Title.Font.IsBold = true;
    chart.ValueAxis.Title.Font.Size = 12;
    chart.ValueAxis.Title.Font.Color = ColorTranslator.FromHtml("#2f5597");
    chart.ValueAxis.MajorGridLines.Transparency = 0.5;

    // Optional: Set X-Axis Title (if needed)
    chart.CategoryAxis.Title.Text = "Required";
    chart.CategoryAxis.Title.Font.IsBold = true;
    chart.CategoryAxis.Title.Font.Size = 12;
    chart.CategoryAxis.Title.Font.Color = ColorTranslator.FromHtml("#2f5597");

    chart.CategoryAxis.MajorGridLines.IsVisible = true;
    chart.CategoryAxis.MajorGridLines.Color = Color.LightGray;
    chart.CategoryAxis.MajorGridLines.Style = LineType.Solid;
    chart.CategoryAxis.MajorGridLines.Transparency = 0;

    //chart.PlotArea.Area.ForegroundColor = System.Drawing.Color.LightGray;

    chart.ChartArea.Border.IsVisible = false;
    chart.PlotArea.Area.FillFormat.FillType = FillType.None;
    chart.ShowLegend = false;
    chart.PlotArea.Border.IsVisible = false;

    //foreach(int i in arr)
    //{
    //    Console.WriteLine(i);
    //}


    // Step 5: Save the Chart as an Excel File
    string excelFilePath = @"D:\ScatterChartNew.xlsx";
    workbook.Save(excelFilePath);

    Console.WriteLine($"Scatter chart created successfully");
}

static int[] CalculateNearNeighbors(double[] x, double[] y, double tolerance)
{
    int n = x.Length;
    int[] clusterIndices = new int[n];
    bool[] visited = new bool[n];
    int currentCluster = 0;

    for (int i = 0; i < n; i++)
    {
        if (!visited[i])
        {
            clusterIndices[i] = currentCluster;
            visited[i] = true;

            for (int j = i + 1; j < n; j++)
            {
                double distance = Math.Sqrt(Math.Pow(x[i] - x[j], 2) + Math.Pow(y[i] - y[j], 2));

                if (distance <= tolerance)
                {
                    clusterIndices[j] = currentCluster;
                    visited[j] = true;
                }
            }

            currentCluster++;
        }
    }

    return clusterIndices;
}

@sanjeevkumar.v

To handle overlapping data labels in a scatter chart created using Aspose.Cells, you can adjust the position of the labels programmatically based on their proximity to each other. Here’s a general approach you can follow:

  1. Calculate Proximity: Determine how close the data points are to each other. You can use a distance threshold to identify nearby points.

  2. Adjust Label Positions: For points that are too close, you can offset their labels by a certain amount to prevent overlap. This can be done by modifying the X and Y coordinates of the data labels.

Here’s a modified version of your code that includes logic to adjust the label positions based on proximity:

public static void ScatterChartNew()
{
    // Step 1: Create a Workbook and Worksheet
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Name = "ScatterChart";

    // Step 2: Add Data for the Scatter Chart
    sheet.Cells["A1"].Value = "Required";
    sheet.Cells["B1"].Value = "Expressed";
    sheet.Cells["C1"].Value = "Point Name";

    // Example Data
    double[] required = { 7.0, 7.5, 8.0, 8.5, 9.0, 7.0, 8.0, 8.5 };
    double[] expressed = { 5, 6, 8, 9, 7, 5, 9, 7 };
    string[] labels = { "Hope", "Empathy", "Charisma", "Cooperation", "Friendliness", "Flexibility", "Friendliness", "Flexibility" };

    for (int i = 0; i < required.Length; i++)
    {
        sheet.Cells[$"A{i + 2}"].Value = required[i];
        sheet.Cells[$"B{i + 2}"].Value = expressed[i];
        sheet.Cells[$"C{i + 2}"].Value = labels[i];
    }

    double tolerance = 0.5; // Adjust this value as needed
    int[] nearCounts = CalculateNearNeighbors(required, expressed, tolerance);

    int chartIndex = sheet.Charts.Add(ChartType.Scatter, 5, 0, 25, 10);
    Chart chart = sheet.Charts[chartIndex];
    chart.Title.Text = "Top 12";

    string dataRange = $"B2:B{required.Length + 1}";
    string categoryRange = $"A2:A{required.Length + 1}";

    int seriesIndex = chart.NSeries.Add(dataRange, true);
    chart.NSeries.CategoryData = categoryRange;
    Series series = chart.NSeries[seriesIndex];

    for (int i = 0; i < series.Points.Count; i++)
    {
        DataLabels label = series.Points[i].DataLabels;
        label.Text = labels[i];
        label.IsAutoText = false;

        // Adjust label position based on proximity
        if (nearCounts[i] > 1)
        {
            label.X += 10 * nearCounts[i]; // Offset X position
            label.Y += 10 * nearCounts[i]; // Offset Y position
        }
    }

    // Save the chart
    string excelFilePath = @"D:\ScatterChartNew.xlsx";
    workbook.Save(excelFilePath);
    Console.WriteLine($"Scatter chart created successfully");
}

static int[] CalculateNearNeighbors(double[] x, double[] y, double tolerance)
{
    int n = x.Length;
    int[] clusterIndices = new int[n];
    bool[] visited = new bool[n];
    int currentCluster = 0;

    for (int i = 0; i < n; i++)
    {
        if (!visited[i])
        {
            clusterIndices[i] = currentCluster;
            visited[i] = true;

            for (int j = i + 1; j < n; j++)
            {
                double distance = Math.Sqrt(Math.Pow(x[i] - x[j], 2) + Math.Pow(y[i] - y[j], 2));

                if (distance <= tolerance)
                {
                    clusterIndices[j] = currentCluster;
                    visited[j] = true;
                }
            }

            currentCluster++;
        }
    }

    return clusterIndices;
}

In this code, the CalculateNearNeighbors function identifies how many points are close to each other based on a specified tolerance. The label positions are then adjusted based on the number of nearby points, which helps to reduce overlap.

Feel free to adjust the offset values and tolerance to better suit your specific dataset and visual requirements.

@sanjeevkumar.v,

I tested your code snippet and it functions as intended. The chart in the output file looks good based on the data you provided. Some data points may overlap, which is to be expected. For instance, “Hope” and “Flexibility” occupy the same position since both are at the (7,5) coordinate. Please see the attached Excel file. To avoid any overlapping data points, please adjust the data source (values) accordingly, and there will be no overlaps.
ScatterChartNew.zip (8.9 KB)

In case, you still have any issue or confusion, kindly do provide an Excel file containing your expected chart, and we will look into it soon.

This sample data working fine but when I dynamically change the data multiple test cases it’s not working correct output.

I attach sample data and what I got output of this code below zip file.

scatterChart.zip (16.3 KB)

@sanjeevkumar.v,

Please note that if there are numerous values with minimal differences between them, some data points and labels may overlap with each other when rendered/displayed on the X/Y scatter chart due to limited space left for separate rendering. Perhaps you could reduce the font size of the data labels to create some small gaps between them.

I checked your new Excel file (you attached). Could you please specify what issues you are encountering? It would be helpful if you could provide some screenshots to illustrate the main problems. Additionally, please share an Excel file with the chart formatted as per your intended requirements regarding data points and labels rendering, and create it manually in MS Excel. We will review it.

I attache zip file inside expecting file, images and what I got both there. can you check and if need any additional info. i will give to you. my issue is based on my values automatic adjust the datalabe with or without arrow No overlapping label to label and point to labels.
scatterChart.zip (240.3 KB)

@sanjeevkumar.v
After calculating the chart, you can refer to the following example code to modify the position of data labels.

chart.NSeries[0].Points[0].DataLabels.X += deltaX;//Offset X position
chart.NSeries[0].Points[0].DataLabels.Y += deltaY;//Offset Y position

Because as data changes, no calculation rule can adapt to all relationships between data labels and icons. As for where to adjust to meet your needs. Please adjust the position of data labels as needed. That is to say, using APIs to generate connection lines like dragging data labels on the interface.

Hi @sanjeevkumar.v
As the data changes dynamically, the position of the points will also change, and the labels may overlap, which can also happen in Excel. You use the following code to get the location, size, and position information of each point’s label. This information will tell you where the labels are, how big are, and you can refer to this information to set the position. Please try the following code:

        //1, set all params for series, points..
        //You can put your code here.
        //2, After setting is all done, Do Calculate.
        chart.Calculate();
        //3, Get position information of Points, for example: Point[0] and Point[1]
        string s0 = series.Points[0].DataLabels.Text;
        int x0 = series.Points[0].DataLabels.X * chart.ChartObject.Width / 4000;
        int y0 = series.Points[0].DataLabels.Y * chart.ChartObject.Height / 4000;
        int width0 = series.Points[0].DataLabels.Width * chart.ChartObject.Width / 4000;
        int height0 = series.Points[0].DataLabels.Height * chart.ChartObject.Height / 4000;
        LabelPositionType pos0 = series.Points[0].DataLabels.Position;

        string s1 = series.Points[1].DataLabels.Text;
        int x1 = series.Points[1].DataLabels.X * chart.ChartObject.Width / 4000;
        int y1 = series.Points[1].DataLabels.Y * chart.ChartObject.Height / 4000;
        int width1 = series.Points[1].DataLabels.Width * chart.ChartObject.Width / 4000;
        int height1 = series.Points[1].DataLabels.Height * chart.ChartObject.Height / 4000;
        LabelPositionType pos1 = series.Points[1].DataLabels.Position;

That is to say, our API can provide a set of multiple coordinates and location information, and can also change their positions. However, how to collisions between them may involve some algorithms within the mathematical coordinate system, which you can design yourself. I hope this will be helpful to you.

What is “deltaX” and “deltaY” values?

@sanjeevkumar.v
The deltaX and deltaY values are offset values that need to be calculated according to specific requirements, and only when there is a distance between the data label and the chart will connecting lines appear.

Okay, can you send clear code. I didn’t understood this code.

@sanjeevkumar.v
Please try the sample code provided by @leoluo . Hope it’s helpful to you.

It’s working 80% to 85% only but two or three data label overlapping.
I attached zip file along with code can you check it.

scatterChart_.zip (162 Bytes)

@sanjeevkumar.v
I’m sorry. We have downloaded the attachment. No files were found in the compressed file. Would you like to provide your sample files and complete test code? We will check it soon.

I’m sorry. I sent wrong zip file. I attached zip file can you check it.
scatterChart_.zip (12.3 KB)

Hi @sanjeevkumar.v
I checked your code, you might have misunderstood my meaning.

My code is an example of how to get the default position information of two points.

If you have 10 points, first you need to get the position information of the 10 points. After collecting the position information in the coordinate system, you need to design an algorithm to ensure that these labels do not collide in space.

1, set all params for series, points, use your code.
2, Do chart.Calculate(). (You must do this after step 1, this is wrong in your code,
If the points haven’t been set yet, you won’t be able to get the correct default position.).
3, get all the position information , for example , into an “Class Array”. In this step, you can use my code.
4, Use this “Class Array”, You need to find an algorithm to detect if these labels have collided with each other, how to adjust them, and whether they will still collide after adjusting.

Hi @leoluo
Ho, By Mistake I misunderstood. I tried many ways but It’s collided.

Hi @sanjeevkumar.v
I have written a code snippet to illustrate the recommended steps.
Please check if it meets your requirements and ideas.
sample.zip (1.8 KB)

Hi @leoluo
I followed you code snippet. How find out position of point? like finding data label position x, y, width and height same.
I attached the image for your understanding.
Screenshot 2025-01-17 131121.jpg (61.4 KB)

Hi @sanjeevkumar.v
Please try to use the following attributes to get the location of the icon (such as a dot) that marks the point, the unit is pixel:

int pointX = series.Points[i].ShapeXPx;
int pointY = series.Points[i].ShapeYPx;

You can compare it with the previously obtained labels position to see if it meets your expectations.

1 Like