Remove border area in apache poi bar chart in excel

Question

I created bar chart in excel file using apache poi. How can I remove border/margin outside the plotarea? The blue border area in the following image.

I added the blue part by editing the created excel file. Before the edit, it was white. Here is the function code that creates this graph:

    public void createBarChart(double[] values, boolean[] passed, String[] labels,
                           int Dx1, int Dy1, int Dx2, int Dy2,
                           int row1, int col1, int row2, int col2){
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(Dx1, Dy1, Dx2, Dy2, col1, row1, col2, row2);
    Chart chart = drawing.createChart(anchor);


    CTChart ctChart = ((XSSFChart)chart).getCTChart();

    CTPlotArea ctPlotArea = ctChart.getPlotArea();
    CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
    //CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
    //ctBoolean.setVal(false);
    ctBarChart.addNewBarDir().setVal(STBarDir.COL);

    // Add the bars
    int length = values.length;
    for (int s = 0; s < length; s++) {
        CTBarSer ctBarSer = ctBarChart.addNewSer();
        CTSerTx ctSerTx = ctBarSer.addNewTx();
        ctSerTx.setV(labels[s]);
        ctBarSer.addNewIdx().setVal(s);

        CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
        CTNumData ctNumData = ctNumDataSource.addNewNumLit();
        ctNumData.addNewPtCount().setVal(1);
        CTNumVal ctNumVal = ctNumData.addNewPt();
        ctNumVal.setIdx(0);
        ctNumVal.setV("" + values[s]);


        //at least the border lines in Libreoffice Calc ;-)
        CTShapeProperties ctShapeProperties = ctBarSer.addNewSpPr();
        if(passed[s]) {
            // bar color, green: passed
            ctShapeProperties.addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0, (byte)128, 0});
        }
        else {
            // bar color, red: failed
            ctShapeProperties.addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte) 255, 0, 0});
        }
        //ctShapeProperties.addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0, 0, 0}); // black
        ctShapeProperties.addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)255, (byte)255, (byte)255}); // white
    }


    //telling the BarChart that it has axes and giving them Ids

    ctBarChart.addNewAxId().setVal(123456);
    ctBarChart.addNewAxId().setVal(123457);


    //cat axis
    CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
    ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
    CTScaling ctScaling = ctCatAx.addNewScaling();
    ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
    ctCatAx.addNewDelete().setVal(true);
    ctCatAx.addNewAxPos().setVal(STAxPos.B);
    ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
    ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

    //val axis
    CTValAx ctValAx = ctPlotArea.addNewValAx();
    ctValAx.addNewAxId().setVal(123457); //id of the val axis
    ctScaling = ctValAx.addNewScaling();
    ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
    ctValAx.addNewDelete().setVal(true);
    ctValAx.addNewAxPos().setVal(STAxPos.L);
    ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
    ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

    // Remove rounded corner
    if (((XSSFChart)chart).getCTChartSpace().getRoundedCorners() == null){
        ((XSSFChart)chart).getCTChartSpace().addNewRoundedCorners();
    }

    ((XSSFChart)chart).getCTChartSpace().getRoundedCorners().setVal(false);
    //chart area (chartspace) without border line
    ((XSSFChart)chart).getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();


}

Axel Richter Axel Richter · Accepted Answer · 2017-07-08T04:54:06

At first, I suspect you are creating a XSSFChart.

How have you set this thick blue border? Normally there is a thin blue border having rounded corners by default. How get rid of the rounded corners is answered already: Changing the shape of chart generated by apache poi for excel sheet.

But if you wants to format the border, then you need to know that the CTChartSpace has CTShapeProperties.

So:

...
        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);

        Chart chart = drawing.createChart(anchor);

//chart area (chartspace) without border line
((XSSFChart)chart).getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();
...

Maybe the border around the plot area and the gap between plot area and chart area shall be removed, then:

...
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
ctPlotArea.addNewSpPr().addNewLn().addNewNoFill();
org.openxmlformats.schemas.drawingml.x2006.chart.CTManualLayout ctManualLayout = ctPlotArea.getLayout().addNewManualLayout();
ctManualLayout.addNewX().setVal(0);
ctManualLayout.addNewY().setVal(0);
ctManualLayout.addNewW().setVal(1);
ctManualLayout.addNewH().setVal(1);
...

Remove border area in apache poi bar chart in excel

1 Answers