Thanks, John now is working as expected.
Hi Team,
We have tried creating pie charts using Aspose.Cells, but the output does not meet our expectations. Specifically, shadows are appearing in the pie chart display, which differs from our intended result.
Could you please assist us in resolving this issue?
Thank you for your support.
static void Main()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Pie Chart";
string[] categories = { "Auditory Digital", "Auditory", "Kinaesthetic", "Visual" };
int[] percentages = { 45, 18, 18, 18 };
worksheet.Cells["A1"].Value = "Category";
worksheet.Cells["B1"].Value = "Percentage";
for (int i = 0; i < categories.Length; i++)
{
worksheet.Cells[$"A{i + 2}"].Value = categories[i];
worksheet.Cells[$"B{i + 2}"].Value = percentages[i];
}
int rowCount = categories.Length;
string dataRange = $"B2:B{rowCount + 1}";
string categoryRange = $"A2:A{rowCount + 1}";
int chartIndex = worksheet.Charts.Add(ChartType.Pie, 5, 1, 25, 8);
Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(dataRange, true);
chart.NSeries.CategoryData = categoryRange;
//chart.ChartArea.Border.IsVisible = false;
for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Explosion = 1; // Exploded pie chart
chart.NSeries[i].Has3DEffect = true;
}
//chart.NSeries[0].DataLabels.ShowValue = true;
chart.NSeries[0].DataLabels.ShowPercentage = true;
DataLabels dataLabels = chart.NSeries[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercentage = true;
dataLabels.SeparatorValue = ",\n ";
dataLabels.Position = LabelPositionType.OutsideEnd;
dataLabels.Font.Color = System.Drawing.ColorTranslator.FromHtml("#5f79a7");
chart.ShowLegend = false;
string[] sliceColors = { "#e9f0ff", "#c0d3f3", "#9cb5ed", "#375ba0" };
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{
Console.WriteLine(chart.NSeries[0].Points.Count);
chart.NSeries[0].Points[i].Area.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(sliceColors[i]);
}
//chart.PlotArea.Width = 400; // Adjust plot area width to make the chart circular
//chart.PlotArea.Height = 400;
//chart.DepthPercent = 20; // Set the depth of the 3D effect
//chart.Perspective = 30; // Set perspective for a realistic 3D look
//ChartArea chartArea = chart.ChartArea;
//chartArea.Area.Formatting = FormattingType.Custom;
//chartArea.Area.FillFormat.Texture = TextureType.BlueTissuePaper;
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.FirstSliceAngle = 180;
// Save the workbook
string filePath = @"D:\PieChartOutput.xlsx";
workbook.Save(filePath);
System.Console.WriteLine($"Pie chart saved to {filePath}");
PieChartOutput.zip (13.9 KB)
We have attempted to create Doughnut charts using Aspose.Cells, but the output does not align with our expectations. Specifically, we are encountering an issue where shadows are appearing in the Doughnut chart display, which differs from our intended result.
Could you please guide us on resolving this issue or share any relevant code adjustments to achieve the expected output?
Thank you for your support.
Workbook workbook = new Workbook();
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = “Pie Chart 2”;
// Define the data for the chart
string[] categories = { “Expressed”, “Required” };
int[] values = { 177, 270 };
// Add headers
worksheet.Cells[“A1”].Value = “Category”;
worksheet.Cells[“B1”].Value = “Value”;
// Populate data dynamically
for (int i = 0; i < categories.Length; i++)
{
worksheet.Cells[$“A{i + 2}”].Value = categories[i];
worksheet.Cells[$“B{i + 2}”].Value = values[i];
}
int rowCount = categories.Length;
string dataRange = $“B2:B{rowCount + 1}”;
string categoryRange = $“A2:A{rowCount + 1}”;
// Add a doughnut chart
int chartIndex = worksheet.Charts.Add(ChartType.Doughnut, 5, 1, 20, 5);
Chart chart = worksheet.Charts[chartIndex];
int seriesIndex = chart.NSeries.Add(dataRange, true);
chart.NSeries.CategoryData = categoryRange;
Series series = chart.NSeries[seriesIndex];
for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Explosion = 1; // Exploded pie chart
chart.NSeries[i].Has3DEffect = true;
}
// series.Type = ChartType.Line;
// Set the data source for the chart
// chart.NSeries[chartIndex].Area.Formatting = FormattingType.None;
series.DoughnutHoleSize = 10;
// Configure data labels to display inside the chart
DataLabels dataLabels = chart.NSeries[0].DataLabels;
dataLabels.ShowCategoryName = true; // Show category names
dataLabels.ShowValue = true; // Show values
dataLabels.SeparatorValue = “,\n “; // Separator between category and value
dataLabels.Position = LabelPositionType.Center; // Center the labels
chart.ShowLegend = false;
dataLabels.Font.Color = System.Drawing.ColorTranslator.FromHtml(”#000”);
// Set the doughnut hole size to create the circular effect
// chart.d = 60; // Adjust this value for the desired hole size
// Add a total percentage in the center
chart.NSeries[0].DataLabels.ShowPercentage = false; // Show percentage in the center
// Customize the chart appearance
string[] sliceColors = { “#4b7bd0”, “#e8efff” };
for (int i = 0; i < values.Length; i++)
{
chart.NSeries[0].Points[i].Area.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(sliceColors[i]);
}
// Rotate the chart for better alignment
chart.FirstSliceAngle = 10;
Aspose.Cells.Drawing.TextBox textBox = chart.Shapes.AddTextBoxInChart(1600, 1500, 800, 1200);
textBox.Text = “66%”;
textBox.TextHorizontalAlignment = TextAlignmentType.Center;
textBox.TextVerticalAlignment = TextAlignmentType.Center;
// Customize font
textBox.Font.Size = 16;
textBox.Font.Color = System.Drawing.ColorTranslator.FromHtml(“#2f5597”);
textBox.LineFormat.IsVisible = false;
textBox.Font.IsBold = true;
// Save the workbook
string filePath = @“D:\DoughnutChart.xlsx”;
workbook.Save(filePath);
DoughnutChart.zip (14.2 KB)
Please change the line of code:
chart.PlotArea.Area.BackgroundColor = Color.White;
to:
chart.PlotArea.Area.ForegroundColo = Color.White;
it will work Ok.
You may try to set shading color of the chart’s plot area to white and hide the border line of plot area. You may add the following two lines to your code segment.
chart.PlotArea.Area.ForegroundColor = System.Drawing.Color.White;
chart.PlotArea.Border.IsVisible = false;
Thanks for your update but the output does not align with our expectations. Please help us resolve this issue or share any relevant code adjustments to achieve the expected output?
DoughnutChartnew.zip (14.9 KB)
Thanks for the sample Excel file containing the desired chart.
I have written the sample code to accomplish your task. Please refer to it and write/update your code accordingly by yourselves for your custom needs. The code also exhibits on how to apply 3D formattings to the chart series. I have also attached the output Excel file containing the chart for your reference.
e.g.,
Sample code:
// Create a new workbook
Workbook workbook = new Workbook();
// Add sample data to the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("Category");
worksheet.Cells["A2"].PutValue("Required");
worksheet.Cells["A3"].PutValue("Expressed");
worksheet.Cells["B1"].PutValue("Values");
worksheet.Cells["B2"].PutValue(270);
worksheet.Cells["B3"].PutValue(177);
// Add a chart to the worksheet
int chartIndex = worksheet.Charts.Add(ChartType.Doughnut, 5, 1, 23, 7);
Chart chart = worksheet.Charts[chartIndex];
// Set chart title
chart.Title.Text = "Sample Doughnut Chart";
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;
// Add the data series
chart.NSeries.Add("B2:B3", true);
chart.NSeries.CategoryData = "A2:A3";
// Configure data labels to display inside the chart
DataLabels dataLabels = chart.NSeries[0].DataLabels;
dataLabels.ShowCategoryName = true; // Show category names
dataLabels.ShowValue = true; // Show values
dataLabels.SeparatorValue = ",\n "; // Separator between category and value
dataLabels.Position = LabelPositionType.Center; // Center the labels
chart.ShowLegend = false;
dataLabels.Font.Color = System.Drawing.ColorTranslator.FromHtml("#000");
// Get the series
Aspose.Cells.Charts.Series series = chart.NSeries[0];
// Set some properties
series.Explosion = 0;
series.Has3DEffect = true;
series.DoughnutHoleSize = 39;
// Set font color of the data lables in the slices of the doughnut chart
series.DataLabels.Font.Color= System.Drawing.Color.White;
series.DataLabels.Font.IsBold = true;
// Apply the 3-D formatting to the chart series
ShapePropertyCollection spPr = series.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
// Specify Bevel with its height/width
Bevel bevel = fmt3d.TopBevel;
bevel.Type = Aspose.Cells.Drawing.BevelPresetType.Circle;
bevel.Height = 4;
bevel.Width = 4;
// Specify Surface material type
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
// Specify surface lighting type
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
//Apply white shading color to plot area
chart.PlotArea.Area.ForegroundColor = System.Drawing.Color.White;
chart.PlotArea.Border.IsVisible = false;
// Save the workbook
workbook.Save("e:\\test2\\DoughnutChart1.xlsx");
DoughnutChart1.zip (7.9 KB)
Hope, this helps a bit.
Thanks for your support its working fine.
It is good to hear that the code segment meets your requirements. Please don’t hesitate to reach out if you have any additional questions or feedback.
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;
}
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.
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.