I was trying to draw some lines on a chart, where the lines were expected to be start from the data points in the chart, but the behaviour was not quite expected.
Code:
var workbook = new Workbook(@“test.xlsx”);
var chart = workbook.Worksheets[0].Charts[0];
chart.Calculate();
for (int i = 0; i < chart.NSeries[0].Points.Count; ++i)
{
ChartPoint point = chart.NSeries[0].Points[i];
double lineTop = point.ShapeY * (double)chart.ChartObject.Height / 4000;
double lineLeft = point.ShapeX * (double)chart.ChartObject.Width / 4000;
LineShape line = chart.Shapes.AddLine(1, (int)lineTop, 1, (int)lineLeft, 21, 0);
line.HasLine = true;
line.Top = (int)lineTop;
line.Left = (int)lineLeft;
}
workbook.Save(@“out.xlsx”);
I’ve attached the test file, expected file, Aspose output file as well as a screenshot for your reference. In theory the line should start from the chart point location but there seems to be random offset.
Could you please look into this and let me know how to achieve the expected result in the screenshot? image.png (12.0 KB) test.zip (31.4 KB)
Thanks for the sample code with template file and screenshot.
After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found that lines added to the chart based on the location of data points are not accurate I have logged a ticket with an id “CELLSNET-45783” for your issue. We will evaluate your issue in details or may suggest you if your sample code needs some tweak to add lines to the chart as per your desired screenshot.
Once we have an update on it, we will let you know here.
Please set the plot area to InnerMode and AutomaticSize of plot are to false. As the size of plot area of the source Excel file is automatic. Please check the attached InnerPlotArea.png for reference. When you call chart.Calculate() method, we need to calculate blank, title, axis’s labels and son on. Now we cannot do same as Ms Excel. If you specify the plot area to inner mode and customized size, the position of plot will be more exact/accurate.
i.e.,
Regarding what you mentioned " Now we cannot do same as Ms Excel.", actually we are able to use macro to calculate the location of the points based on the vertical and horizontal value, and then inject the lines. And that is the way we used to create the lines.
However, in Aspose when I wanted to calculate the data points it was not accurate so I gave up that solution and used ChartPoint class which was provided by Aspose.
I’ve tried your suggestion using the code:
var workbook = new Workbook(@“test.xlsx”);
var chart = workbook.Worksheets[0].Charts[0];
chart.Calculate();
chart.PlotArea.IsInnerMode = true;
chart.PlotArea.IsAutomaticSize = false;
for (int i = 0; i < chart.NSeries[0].Points.Count; ++i)
{
ChartPoint point = chart.NSeries[0].Points[i];
double lineTop = point.ShapeY * (double)chart.ChartObject.Height / 4000;
double lineLeft = point.ShapeX * (double)chart.ChartObject.Width / 4000;
LineShape line = chart.Shapes.AddLine(1, (int)lineTop, 1, (int)lineLeft, 21, 0);
line.HasLine = true;
line.Top = (int)lineTop;
line.Left = (int)lineLeft;
}
workbook.Save(@“out.xlsx”);
I could see the quality became better but it still has some inaccuracy compared to the file generated by the macro using MS Excel. I’ve attached the test files and screenshot for your reference. You may run the ‘main’ function in testwithmacro.xlsm to start the macro and have the line inserted.
We treat this issue seriously because we are creating an equivalent of the macro using Aspose, and this becomes a blocking point. The line is supposed to be an indicator of the value on the chart, so it has to be pointed to the exact position or it will lead to confusion.
If the current ChartPoint class has limitations, could you please suggest how we can currently create the equivalent of the macro in my sample file using Aspose?
Thanks for the sample files, screenshot and details.
Yes, we can see some improvements. But, you are right, it still is not up to the mark as compared to the file generated by the macro using MS Excel. I have logged your concerns and samples against your issue “CELLSNET-45783” into our database. Please spare us some time as our concerned developer from product team will further evaluate your issue/requirements if we could suggest something else or provide some other way around.
Once we have an update on it, we will let you know here.
We are afraid Aspose.Cells cannot do as the macro because now we cannot calculate exact position for elements of chart. If adding lines is only for indicating the value on the chart, we think you can do using the attached code segment, so please try it. code.zip (614 Bytes)
Thanks for the suggestions and the example code. I understand the code was to use the data markers for each data, however this could not achieve what we want because of the following reasons:
The data lines style was instructed by our client and could not be changed, so we cannot change this to the dots/dialog style in your sample outputs.
The code can only add data labels to all data points or add no data label at all. However, in practice we only need to add lines to some of them (we may only add data lines for even/odd data points etc.)
We were expecting Aspose to be able to simulate what we could achieve in MS Excel, so is there still a way to make it work?
I have logged your concerns against your issue “CELLSNET-45783” into our database. Our concerned developer from product team will evaluate your issue further (if possible) if we could devise some other better way to cope with it.
Once we have an update on it, we will let you know here.
I have tried your code and indeed Shapes.AddShapeInChart provided a much more accurate result than Shapes.AddLine. I’m curious why there’s discrepancy in the accuracy of these two methods, but as it works well for me, I guess this should resolve our issue here for now.
I will let you know if I noticed any side-effect or something else needed. Thanks very much for the help!
Good to know that your issue is sorted out by the suggested code. Please take your time to evaluate your test cases. Hopefully, it will work for your needs.
Hi, I have upgraded my Aspose.Cells version from 18.4.0 to 19.7.0 and used the code from you again, but I found it no longer working. The lines were not added to the chart at all after calling AddShapeInChart().
@ServerSide527,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46837 – Lines are not added to the chart based on the location of data point
We evaluated your issue further.
We found it is not an issue with the APIs. For the method chart.Shapes.AddShapeInChart, the parameter placement type should be passed PlacementType.Move or PlacementType.MoveAndSize. See the following sample code (updated) that I tested, it works fine:
e.g Sample code:
var workbook = new Workbook("e:\\test2\\test.xlsx");
var chart = workbook.Worksheets[0].Charts[0];
chart.Calculate();
//Must set PlotArea to customized size as we can't calulate exact position of PlotArea when it's position is automatic
chart.PlotArea.IsInnerMode = true;
chart.PlotArea.IsAutomaticSize = false;
for (int i = 0; i < chart.NSeries[0].Points.Count; ++i)
{
ChartPoint point = chart.NSeries[0].Points[i];
double lineTop = (int)(point.ShapeY * (double)chart.ChartObject.Height / 4000 + 0.5);
double lineLeft = (int)(point.ShapeX * (double)chart.ChartObject.Width / 4000 + 0.5);
//LineShape line = chart.Shapes.AddLine(1, (int)lineTop, 1, (int)lineLeft, 50, 0);
//Replace AddLine to AddShapeInChart
Aspose.Cells.Drawing.Shape line = chart.Shapes.AddShapeInChart(MsoDrawingType.Line, PlacementType.Move, point.ShapeX, point.ShapeY, point.ShapeX, point.ShapeY + 200);
line.HasLine = true;
line.Top = (int)lineTop;
line.Left = (int)lineLeft;
}
workbook.Save("e:\\test2\\out1.xlsx");
The previous working code suggested was using PlacementType.FreeFloating so I wasn’t aware of that, but I have tried the new code and it is working again now!
Good to know that your issue is sorted out by the suggested code segment. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.