Adding lines / textboxes to a chart in relation to specifc category values

[eoe]

Dear all,

First of all I would like to thank you for the great support during the last few weeks. With your quick replays to all kind of questions I was able to nearly complete my current project.

Finally, there is only one kind of chart left to be implemented. Thereby the chart is not the real problem (it consists only of simple lines). The main issue with this kind of report is the need of additional controls indicating specific points in time. Please take a look at the attached file where you can find a small sample report page. I know the size of the diagram and I know the first and the last value of the category axis. It is possible to add vertical lines and textboxes in relation to a given category value to a chart or at least to an excel sheet using aspose? I would appreciate any kind of help to solve this final problem.

Thanks in advance

Erik

[amjad.sahi]

Hi Erik,

Well, you may add controls like labels and text boxes on the chart using Aspose.Cells for .NET APIs, see the topic for reference: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/inserting-controls-into-charts.html
But, to place the controls at specific location/position in relation with category axis values/points, it is hard to define. Anyways, we will get back to you soon.

Thank you.

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells and refer the following sample code as per your requirement:

Workbook workbook = new Workbook();

workbook.Open(@"F:\FileTemp\book2.xls");

Chart chart = workbook.Worksheets[0].Charts[0];

chart.Calculate();

int tickSpacing = chart.CategoryAxis.TickLabelSpacing;

int dataCount = chart.NSeries[0].CountOfDataValues;

int x = chart.PlotAreaWithoutTickLabels.X;

int width = chart.PlotAreaWithoutTickLabels.Width;

int unit = (int)(width / (dataCount / (double)tickSpacing) + 0.5);

chart.Shapes.AddShapeInChart(MsoDrawingType.Line, PlacementType.MoveAndSize, x + unit * 2, 0, x + unit * 2 + 1, 4000, null);

workbook.Save(@"F:\FileTemp\dest.xls");

Thank You & Best Regards,

[eoe]

Hi,

sorry for the late reply but I was on holiday. Thank you for the new feature. My first impression is that the new functionality works great. Nevertheless I have got a few questions.

1. If you consider the line of code where you calculate the unit value, why do you add the value 0.5?
2. In some cases I’m forced to add a lot of textboxes above the plot area. To avoid interleaving textboxes, I would like to arrange them in two or three rows. Therefore I need more space between the top of the plot area and the top of the chart area. Is it possible to do this? Otherwise can you show me a workaround to solve the issue?

Thanks in advance

Erik

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

We will get back to you regarding your queries soon.

Thank You & Best Regards,

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

eoe:

If you consider the line of code where you calculate the unit value, why do you add the value 0.5?

0.5 has been added in order to get the rounded unit value.

eoe:

In some cases I’m forced to add a lot of textboxes above the plot area. To avoid interleaving textboxes, I would like to arrange them in two or three rows. Therefore I need more space between the top of the plot area and the top of the chart area. Is it possible to do this? Otherwise can you show me a workaround to solve the issue?

Please set the Chart.PlotArea.Y property to set more space between plot area and chart area.

Thank You & Best Regards,

[eoe]

Hi,

I tried to change the y value of the chart plot area. But it seems that this has no influence on the gab between chart and plot area. I set the property after calling chart.Calculate(). Is this correct (changing the order of calls leads also to no visible result)?

Regards

Erik

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

Well, I tried your scenario and it works fine with Chart.PlotArea.Y. Following is my sample code:

Workbook workbook = new Workbook();
workbook.Open(@"C:\Test_Temp\DemoChart.xls");
Chart chart = workbook.Worksheets[0].Charts[0];
chart.PlotArea.Y = 500;
chart.Calculate();
int tickSpacing = chart.CategoryAxis.TickLabelSpacing;
int dataCount = chart.NSeries[0].CountOfDataValues;
int x = chart.PlotAreaWithoutTickLabels.X;
int width = chart.PlotAreaWithoutTickLabels.Width;
int unit = (int)(width / (dataCount / (double)tickSpacing) + 0.5);
chart.Shapes.AddShapeInChart(MsoDrawingType.Line, PlacementType.MoveAndSize, x + unit * 2, 0, x + unit * 2 + 1, 4000, null);
//Also it works fine if I put the code here after Calculate & adding the Shape
// chart.PlotArea.Y = 500;
workbook.Save(@"C:\dest2.xls");

Please share your template file and sample code to show the issue. We will check it soon.

Thank You & Best Regards,

[eoe]

Hi,

I just tested the provided code within my application. It doesn’t work until I also adapted the height of the plot area. If I apply the following two lines of code everything is ok.

//shrink plotarea first
chart.PlotArea.Height = chart.PlotArea.Height -250;
//move the top edge of the plotarea towards the category axis
chart.PlotArea.Y = chart.PlotArea.Y + 350;

Thanks and best regards

Erik