Add Dynamic Target Line in Excel Bar Charts

Hi,

We need to add dynamic target line in excel bar charts as below.
image.png (32.3 KB)

We need to add both horizontal and vertical target lines as below.

Could you please help us on this.

@sandhyavutukuru,

Could you please also share an Excel file containing your desired chart in it, we will check it.

PS. please zip the Excel file prior attaching here.

Hi,

PFA zip file which has graph details. In the graph, we need to set target line specified in green color. In the graph, we added line for sample. But we need to set this, dynamically based on y axis value. Please let me know if you need any other info

Target Line workbook.zip (13.0 KB)

@sandhyavutukuru,

Thanks for the sample Excel file containing your desired chart with a line above the bars.

I have logged a ticket with an id “CELLSNET-50662” for your task/requirements. We will evaluate and try to devise a sample code snippet to accomplish your exact task to add a line above the bars in the chart.

Once we figure it out, we will update you with sample code (if possible).

@sandhyavutukuru,

Please try the following code for your reference.

Sample code:

 Workbook workbook = new Workbook( "test.xlsx");
            Chart chart = workbook.Worksheets[0].Charts[0];
            chart.Calculate();
            double lineLeftScale = (double)chart.PlotArea.InnerX / 4000;
            double lineRightScale = (double)(chart.PlotArea.InnerX + chart.PlotArea.InnerWidth) / 4000;
            int unitCount = chart.ValueAxis.AxisLabels.Count;
            double lineTopScale = (double)chart.PlotArea.InnerY / 4000 + 2.0 / (unitCount-1) * chart.PlotArea.InnerHeight / 4000 ;
            LineShape shape = (LineShape)chart.Shapes.AddShapeInChartByScale(MsoDrawingType.Line, PlacementType.MoveAndSize, lineLeftScale, lineTopScale, lineRightScale, lineTopScale);
            shape.HasLine = true;
            shape.Line.DashStyle = MsoLineDashStyle.RoundDot;         
            shape.Line.SolidFill.Color = Color.Green;
            shape.Line.Weight = 2.5;
            workbook.Save("Target Line workbook.xlsx");

Thanks a lot Eric for the sample code. Could you please provide same code in java ? Not finding exact methods in java for the given code .

@sandhyavutukuru,

See the corresponding Java code for your requirements for your reference:
e.g.
Sample code:

        Workbook workbook = new Workbook("test.xlsx");
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);
        chart.calculate();
        double lineLeftScale = (double)chart.getPlotArea().getInnerX()/4000;
        double lineRightScale = (double)(chart.getPlotArea().getInnerX() + chart.getPlotArea().getInnerWidth()) / 4000;
        int unitCount = chart.getValueAxis().getAxisLabels().size();
        double lineTopScale = (double)chart.getPlotArea().getInnerY() / 4000 + 2.0 / (unitCount-1) * chart.getPlotArea().getInnerHeight() / 4000 ;
        LineShape shape = (LineShape)chart.getShapes().addShapeInChartByScale(MsoDrawingType.LINE, PlacementType.MOVE_AND_SIZE, lineLeftScale, lineTopScale, lineRightScale, lineTopScale);
        shape.setHasLine(true);
        shape.getLine().setDashStyle(MsoLineDashStyle.ROUND_DOT);
        shape.getLine().getSolidFill().setColor(com.aspose.cells.Color.getGreen());
        shape.getLine().setWeight(2.5);
        workbook.save("Target Line workbook.xlsx");

Hope, this helps a bit.