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;
}