Apache POI - Cant get Line Chart Values to appear in the Secondary Axis

4
votes

I can't get the Line Chart Values to appear on the Secondary Axis of the chart.

When I open the excel file, I can get the chart the way I want just by clicking on top of the line series with the right button and select "Format Data Series",It appears that the secondary axis is selected, but my chart isn't the way I want, and when I select primary axis and then again secondary axis, it works. It doesn't make sense.

It would be good if there was a function for putting the ValAx in the Secondary Axis, but you can only say Right or Left, which doesnt help me.

This is somewhat what my code looks like 

XSSFWorkbook workbook=new XSSFWorkbook();
XSSFSheet chartdisplay=workbook.createSheet("ChartDisplay")
XSSFDrawing drawing=chartdisplay.createDrawingPatriarch();
ClientAnchor anchor=drawing.createAnchor(0,0,0,0,5,5,13,13);
Chart chart=drawing.createChart(anchor);

CTChart ctChart=((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea=ctChart.getPlotArea();
//Bar Chart
CTBarChart ctBarChart=ctPlotArea.addNewBarChart();
CTBoolean ctBoolean=ctBarChart.addNewVaryColors();
ctBoolean.setVal(false);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
CTBarSer ctBarSer=ctBarChart.addNewSer();
CTSerTx ctSerTx=ctBarSer.addNewTx();
CTStrRef ctStrRef=ctSerTx.addNewStrRef();
ctStrRef.setF("\"BarSeriesName\"");
//Labels For Bar Chart

ctBarSer.addNewIdx().setVal(0); //0 = Color Blue
CTAxDataSource ctAxDataSource=ctBarSer.addNewCat();
ctStrRef=ctAxDataSource.addNewStrRef();
String labelsRefer="ChartDisplay!B2:B7";//Excel Range where the Labels Are
ctStrRef.setF(labelsRefer);
//Values For Bar Chart
CTNumDataSource ctNumDataSource=ctBarSer.addNewVal();
CTNumRef ctNumRef=ctNumDataSource.addNewNumRef();
String valuesRefer="ChartDisplay!C2:C7";//Excel range where values for barChart are
ctNumRef.setF(valuesRefer);
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0,0,0});
// Axis
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(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
ctCatAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

//val Left Axis
CTValAx ctValAx1=ctPlotArea.addNewValAx();
ctValAx1.addNewAxId().setVal(123457); //id of the val axis
ctScaling=ctValAx1.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx1.addNewDelete().setVal(false);
ctValAx1.addNewAxPos().setVal(STAxPos.L);
ctValAx1.addNewCrossAx().setVal(123456); //id of the cat axis
ctValAx1.addNewMinorTickMark().setVal(STTickMark.NONE);
ctValAx1.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctValAx1.addNewMajorGridlines();

// =======Line Chart
//val Right Axis
CTLineChart ctLineChart=ctPlotArea.addNewLineChart();
CTBoolean ctBooleanLine=ctLineChart.addNewVaryColors();
ctBooleanLine.setVal(false);
CTLineSer ctLineSer=ctLineChart.addNewSer();
CTSerTx ctSerTx1=ctLineSer.addNewTx();
CTStrRef ctStrRef1=ctSerTx1.addNewStrRef();
ctStrRef1.setF("\"LineSeriesName\"");
ctLineSer.addNewIdx().setVal(2); //2= Color Grey
CTAxDataSource ctAxDataSource1=ctLineSer.addNewCat();
ctStrRef1=ctAxDataSource1.addNewStrRef();
ctStrRef1.setF(labelsRefer);
ctLineSer.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0,0,0});

String values2Refer="ChartDisplay!D2:D7"; //Excel Range Where Values for Line Values are
CTNumDataSource ctNumDataSource1=ctLineSer.addNewVal();
CTNumRef ctNumRef1=ctNumDataSource1.addNewNumRef();
ctNumRef1.setF(values2Refer);

//Axis
ctLineChart.addNewAxId().setVal(1234);//id of the cat axis
ctLineChart.addNewAxId().setVal(12345);

CTCatAx ctCatAx1=ctPlotArea.addNewCatAx();
ctCatAx1.addNewAxId().setVal(1234);// id of the cat Axis
ctScaling=ctCatAx1.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx1.addNewDelete().setVal(true);
ctCatAx1.addNewAxPos().setVal(STAxPos.B);
ctCatAx1.addNewCrossAx().setVal(12345); //id of the val axis
CTBoolean ctBoolean1=ctCatAx1.addNewAuto();


CTValAx ctValAx=ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(12345); //id of the val axis
ctScaling=ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.R);
ctValAx.addNewCrossAx().setVal(1234); //id of the cat axis
ctValAx.addNewMinorTickMark().setVal(STTickMark.NONE);
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

//Legend
CTLegend ctLegend=ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
1
javaexcelapachechartsapache-poi

1 Answers

5
votes

You need to set where the value axis crosses the category axis using the CTCrosses. By default it crosses at STCrosses.AUTO_ZERO. But the secondary axis must cross at max value of the category axis.

The STAxPos R does not means that the axis is at the rigth side of the chart but that the axis labels are right side.

Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;

import org.openxmlformats.schemas.drawingml.x2006.chart.*;

public class BarAndLineChart {

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");

        Row row;
        Cell cell;

        row = sheet.createRow(0);
        row.createCell(0);
        row.createCell(1).setCellValue("Bars");
        row.createCell(2).setCellValue("Lines");

        for (int r = 1; r < 7; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue("C" + r);
            cell = row.createCell(1);
            cell.setCellValue(new java.util.Random().nextDouble());
            cell = row.createCell(2);
            cell.setCellValue(new java.util.Random().nextDouble()*10d);
        }

        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);

        Chart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();  
        CTPlotArea ctPlotArea = ctChart.getPlotArea();

        //the bar chart
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

        //the bar series
        CTBarSer ctBarSer = ctBarChart.addNewSer();
        CTSerTx ctSerTx = ctBarSer.addNewTx();
        CTStrRef ctStrRef = ctSerTx.addNewStrRef();
        ctStrRef.setF("Sheet1!$B$1");
        ctBarSer.addNewIdx().setVal(0);  
        CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
        ctStrRef = cttAxDataSource.addNewStrRef();
        ctStrRef.setF("Sheet1!$A$2:$A$7"); 
        CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
        CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
        ctNumRef.setF("Sheet1!$B$2:$B$7");

        //at least the border lines in Libreoffice Calc ;-)
        ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   

        //telling the BarChart that it has axes and giving them Ids
        ctBarChart.addNewAxId().setVal(123456); //cat axis 1 (bars)
        ctBarChart.addNewAxId().setVal(123457); //val axis 1 (left)

        //the line chart
        CTLineChart ctLineChart = ctPlotArea.addNewLineChart();
        ctBoolean = ctLineChart.addNewVaryColors();
        ctBoolean.setVal(true);

        //the line series
        CTLineSer ctLineSer = ctLineChart.addNewSer();
        ctSerTx = ctLineSer.addNewTx();
        ctStrRef = ctSerTx.addNewStrRef();
        ctStrRef.setF("Sheet1!$C$1");
        ctLineSer.addNewIdx().setVal(1);  
        cttAxDataSource = ctLineSer.addNewCat();
        ctStrRef = cttAxDataSource.addNewStrRef();
        ctStrRef.setF("Sheet1!$A$2:$A$7"); 
        ctNumDataSource = ctLineSer.addNewVal();
        ctNumRef = ctNumDataSource.addNewNumRef();
        ctNumRef.setF("Sheet1!$C$2:$C$7");

        //at least the border lines in Libreoffice Calc ;-)
        ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   

        //telling the LineChart that it has axes and giving them Ids
        ctLineChart.addNewAxId().setVal(123458); //cat axis 2 (lines)
        ctLineChart.addNewAxId().setVal(123459); //val axis 2 (right)

        //cat axis 1 (bars)
        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(false);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //val axis 1 (left)
        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(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
        ctValAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zero
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //cat axis 2 (lines)
        ctCatAx = ctPlotArea.addNewCatAx(); 
        ctCatAx.addNewAxId().setVal(123458); //id of the cat axis
        ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewDelete().setVal(true); //this cat axis is deleted
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(123459); //id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

         //val axis 2 (right)
        ctValAx = ctPlotArea.addNewValAx(); 
        ctValAx.addNewAxId().setVal(123459); //id of the val axis
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewDelete().setVal(false);
        ctValAx.addNewAxPos().setVal(STAxPos.R);
        ctValAx.addNewCrossAx().setVal(123458); //id of the cat axis
        ctValAx.addNewCrosses().setVal(STCrosses.MAX); //this val axis crosses the cat axis at max value
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //legend
        CTLegend ctLegend = ctChart.addNewLegend();
        ctLegend.addNewLegendPos().setVal(STLegendPos.B);
        ctLegend.addNewOverlay().setVal(false);

        FileOutputStream fileOut = new FileOutputStream("BarAndLineChart.xlsx");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }
}

Result:

enter image description here