We have tried the above lines of code creating a scatter chart using Aspose. Cells, but we need to implement an arrow line with a circle in the scatter chart, as shown in the attached image. Could you please share the C# code to achieve this?
To create a scatter chart with an arrow line and a circle in Aspose.Cells, you can follow the example below. This code builds upon your existing implementation by adding a line with arrowheads and a circle shape to the scatter chart.
Here’s how you can achieve this:
using Aspose.Cells;
using Aspose.Cells.Charts;
using Aspose.Cells.Drawing;
using System.Drawing;
internal class Program
{
static void Main()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Add data for the scatter chart
worksheet.Cells["A1"].PutValue("X-Values");
worksheet.Cells["B1"].PutValue("Y-Values");
worksheet.Cells["C1"].PutValue("Labels");
// Example data
worksheet.Cells["A2"].PutValue(8.0); worksheet.Cells["B2"].PutValue(7.5); worksheet.Cells["C2"].PutValue("Empathy");
worksheet.Cells["A3"].PutValue(8.8); worksheet.Cells["B3"].PutValue(8.0); worksheet.Cells["C3"].PutValue("Charisma");
// Add more data as needed...
int chartIndex = worksheet.Charts.Add(ChartType.Scatter, 5, 0, 25, 10);
Chart chart = worksheet.Charts[chartIndex];
// Add data series
chart.NSeries.Add("B2:B3", true); // Y-Values
chart.NSeries.CategoryData = "A2:A3"; // X-Values
// Customize the series labels and markers
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{
ChartPoint point = chart.NSeries[0].Points[i];
point.DataLabels.Text = worksheet.Cells[$"C{2 + i}"].StringValue; // Add label text
point.DataLabels.Font.Size = 10;
point.Marker.MarkerStyle = ChartMarkerType.Circle; // Set marker type
point.Marker.MarkerSize = 8; // Set marker size
point.Marker.ForegroundColor = Color.Blue; // Set marker color
}
// Add an arrow line
int startX = 5; // X position of the start of the line
int startY = 5; // Y position of the start of the line
int endX = 20; // X position of the end of the line
int endY = 15; // Y position of the end of the line
LineShape line = worksheet.Shapes.AddLine(startX, startY, endX, endY);
line.Line.FillType = FillType.Solid;
line.Line.SolidFill.Color = Color.Red; // Line color
line.Line.Weight = 2; // Line weight
line.Line.EndArrowheadStyle = MsoArrowheadStyle.Arrow; // End arrow style
line.Line.EndArrowheadLength = MsoArrowheadLength.Medium; // End arrow length
line.Line.EndArrowheadWidth = MsoArrowheadWidth.Medium; // End arrow width
// Add a circle at the end of the line
int circleX = endX - 5; // Adjust circle position
int circleY = endY - 5; // Adjust circle position
int circleSize = 10; // Circle size
Shape circle = worksheet.Shapes.AddShape(MsoDrawingType.ELLIPSE, circleX, circleY, circleSize, circleSize);
circle.Fill.FillType = FillType.Solid;
circle.Fill.SolidFill.Color = Color.Blue; // Circle color
// Customize the chart title and axes
chart.Title.Text = "Top 12";
chart.Title.Font.Size = 14;
chart.ValueAxis.Title.Text = "Score";
chart.ValueAxis.Title.Font.Size = 10;
chart.CategoryAxis.Title.Text = "Required";
chart.CategoryAxis.Title.Font.Size = 10;
workbook.Save("D://ScatterChartWithArrows.xlsx");
}
}
Explanation:
Data Preparation: The code initializes a workbook and adds data for the scatter chart.
Chart Creation: A scatter chart is created, and data series are added.
Marker Customization: Each point in the series is customized with labels and circular markers.
Arrow Line Addition: An arrow line is added to the worksheet with specified start and end points, along with arrowhead styles.
Circle Addition: A circle shape is added at the end of the arrow line to visually represent the end point.
This code should help you create the desired scatter chart with an arrow line and a circle. Adjust the positions and sizes as needed to fit your specific requirements.
Sources:
No external sources were referenced in this response.
Could you please manually create your desired chart in MS Excel, save the file, and share the saved Excel file with us? We will then explore and devise code segment on how to achieve the task using Aspose.Cells APIs.
P.S. Please compress the Excel file into a zip before attaching it here.
We have studied the file you provided, have you manually set the position of the label in the file? The line only appears when the label position dragged to a distant place.
Excel will by default place the label next to the point, in which case the line will not appear. Unless the label cannot be placed, excel will change the position of the label to a distant place, or you manually set the position of the label to a distant place. This is when the lines will appear.
Using Aspose.Cells, you can ensure the line is visible by setting the HasLeaderLines property on series to True. Then position the label so that it’s far enough from the point that excel will render the line.
I use the code you provided earlier, and insert 3 lines of code before saving to get the following result. line1.png (8.9 KB)
We have implemented the solution as provided, but we could not achieve the correct appearance of the line due to its high dependency on the label’s position.
Attached are the sample code and the corresponding Excel file for your reference. Kindly assist us in resolving this issue. Your guidance would be greatly appreciated. scatterOutput.zip (10.8 KB)
Hi John,
We have attempted to create a column bar chart using Aspose. Cells but are looking to achieve a Combined Column Chart instead. Could you please provide a sample code in C# for creating a Combined Column Chart using Aspose?Cells?
For reference, I have attached a screenshot illustrating the desired output. Please let us know if you need additional details.
Looking forward to your assistance. ColumnChart.zip (10.1 KB)
@sanjai03
Please plot the second series to secondary axis as the following :
// Create a new workbook
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet worksheet = workbook.Worksheets[0];
// Define your data
object[,] data = new object[,] {
{ "Category", "S-55", "S-65", "S-70", "S-Column" },
{ "0%", 55, 10, 5, 0.12 },
{ "25%", 55, 10, 5, 0.12 }
};
// Get the range of cells where you want to set values
int startRow = 0;
int startColumn = 0;
int numRows = data.GetLength(0);
int numCols = data.GetLength(1);
var range = worksheet.Cells.CreateRange(startRow, startColumn, numRows, numCols);
// Set values from the array to the range
range.Value = data;
// Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 6, 0, 25, 10);
// Accessing the instance of the newly added chart
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.SetChartDataRange("A1:C3", true);
chart.NSeries[1].PlotOnSecondAxis = true;
// Save the workbook
workbook.Save(dir + "dest.xlsx");
Thanks for your Update.
We have attempted the solution you provided, but the expected output does not align with the desired results. Could you please assist us further?
For your reference, I have attached a screenshot/file illustrating the issue.
I checked your Excel file containing the chart but it has external links to other workbook (“LW_Talent_Report_L6 (2).xlsx”) for data source for the chart. Please create an Excel file containing your desired chart, but the source data should be present in the same workbook and not in external source. We will check and then devise code segment to create your desired chart for your requirements.
@sanjai03
Please try the following sample code and check the attachment. column.zip (8.6 KB)
// Create a new workbook
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet worksheet = workbook.Worksheets[0];
// Define your data
object[,] data = new object[,] {
{ "Category", "S-55", "S-65", "S-70", "S-Column" },
{ "0%", 55, 10, 5, 0.12 },
{ "25%", 55, 10, 5, 0.12 }
};
// Get the range of cells where you want to set values
int startRow = 0;
int startColumn = 0;
int numRows = data.GetLength(0);
int numCols = data.GetLength(1);
var range = worksheet.Cells.CreateRange(startRow, startColumn, numRows, numCols);
// Set values from the array to the range
range.Value = data;
// Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 6, 0, 25, 10);
// Accessing the instance of the newly added chart
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.SetChartDataRange("A1:C3", true);
chart.NSeries[1].PlotOnSecondAxis = true;
//change the gap width
chart.NSeries[0].GapWidth = 45;
// Save the workbook
workbook.Save(filePath + "column.xlsx");
@sanjai03
By examining two files, we found that the charts in the files have the same layout, except for slight differences in the data. Would you like to take a screenshot and highlight the differences? We will check it soon.