java - Apache POI - 无法让折线图值出现在辅助轴中

Question

我无法让折线图值显示在图表的辅助轴上。

当我打开 excel 文件时，只需用右键单击线系列顶部并选择“格式化数据系列”，我就可以按照我想要的方式获得图表，看起来选择了次轴，但是我的图表不是我想要的方式，当我选择主轴然后再次选择次轴时，它起作用了。这没有意义。

如果有将 ValAx 放在辅助轴上的功能就好了，但你只能说右或左，这对我没有帮助。

这有点像我的代码

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);

Answer 1

您需要使用 CTCrosses 设置值轴与类别轴的交点。默认情况下它穿过 STCrosses.AUTO_ZERO 。但次要轴必须与类别轴的最大值相交。

STAxPos R 并不意味着轴在图表的右侧，而是轴标签在右侧。

例子:

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();
    }
}

结果: