Apache POI - 无法让折线图值出现在辅助轴中 [英] Apache POI - Cant get Line Chart Values to appear in the Secondary Axis
本文介绍了Apache POI - 无法让折线图值出现在辅助轴中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我无法让折线图值显示在图表的辅助轴上.
当我打开excel文件时，我可以通过右键单击线条系列顶部并选择格式化数据系列"来以我想要的方式获取图表，似乎选择了辅助轴，但是我的图表不是我想要的方式，当我选择主轴然后再次选择次轴时，它就可以工作了.没有意义.
如果有把ValAx放在次轴上的功能就好了，但你只能说Right或Left，这对我没有帮助.
这有点像我的代码
XSSFWorkbook workbook=new XSSFWorkbook();XSSFSheet chartdisplay=workbook.createSheet("ChartDisplay")XSSFDrawing 绘图=chartdisplay.createDrawingPatriarch();ClientAnchor anchor=drawing.createAnchor(0,0,0,0,5,5,13,13);图表图表=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);CTBarSer ctBarSer=ctBarChart.addNewSer();CTSerTx ctSerTx=ctBarSer.addNewTx();CTStrRef ctStrRef=ctSerTx.addNewStrRef();ctStrRef.setF("\"BarSeriesName\"");//条形图的标签ctBarSer.addNewIdx().setVal(0);//0 = 蓝色CTAxDataSource ctAxDataSource=ctBarSer.addNewCat();ctStrRef=ctAxDataSource.addNewStrRef();String labelsRefer="ChartDisplay!B2:B7";//标签所在的Excel范围ctStrRef.setF(labelsRefer);//条形图的值CTNumDataSource ctNumDataSource=ctBarSer.addNewVal();CTNumRef ctNumRef=ctNumDataSource.addNewNumRef();String valuesRefer="ChartDisplay!C2:C7";//barChart 值所在的 Excel 范围ctNumRef.setF(valuesRefer);ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0,0,0});//轴ctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);//猫轴CTCatAx ctCatAx=ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456);//猫轴的idCTScaling ctScaling=ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewDelete().setVal(false);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457);//val轴的idctCatAx.addNewMinorTickMark().setVal(STTickMark.NONE);ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val 左轴CTValAx ctValAx1=ctPlotArea.addNewValAx();ctValAx1.addNewAxId().setVal(123457);//val轴的idctScaling=ctValAx1.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx1.addNewDelete().setVal(false);ctValAx1.addNewAxPos().setVal(STAxPos.L);ctValAx1.addNewCrossAx().setVal(123456);//猫轴的idctValAx1.addNewMinorTickMark().setVal(STTickMark.NONE);ctValAx1.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);ctValAx1.addNewMajorGridlines();//========折线图//val 右轴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=颜色灰色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范围CTNumDataSource ctNumDataSource1=ctLineSer.addNewVal();CTNumRef ctNumRef1=ctNumDataSource1.addNewNumRef();ctNumRef1.setF(values2Refer);//轴ctLineChart.addNewAxId().setVal(1234);//猫轴idctLineChart.addNewAxId().setVal(12345);CTCatAx ctCatAx1=ctPlotArea.addNewCatAx();ctCatAx1.addNewAxId().setVal(1234);//猫轴的idctScaling=ctCatAx1.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx1.addNewDelete().setVal(true);ctCatAx1.addNewAxPos().setVal(STAxPos.B);ctCatAx1.addNewCrossAx().setVal(12345);//val轴的idCTBoolean ctBoolean1=ctCatAx1.addNewAuto();CTValAx ctValAx=ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(12345);//val轴的idctScaling=ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewDelete().setVal(false);ctValAx.addNewAxPos().setVal(STAxPos.R);ctValAx.addNewCrossAx().setVal(1234);//猫轴的idctValAx.addNewMinorTickMark().setVal(STTickMark.NONE);ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//传奇CTLegend ctLegend=ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STlegendPos.B);ctLegend.addNewOverlay().setVal(false); 解决方案
您需要使用
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);
解决方案
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:
这篇关于Apache POI - 无法让折线图值出现在辅助轴中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
