Combining bar and line chart using Apache POI

Question

I followed the example here:

apache poi: how to create chart with both bar and line?

But, the chart is displayed with two y-axes. It seems that creating the second type of chart causes the double axis. Any ideas how to combine the two types of charts in POI and not have this happen?

Thanks! Geoff

My code is below. Almost identical to the post I'm referring to, but with my data:

File    file = null;
FileOutputStream    fos = null;
XSSFWorkbook    workBook = new XSSFWorkbook();
XSSFSheet   sheet = (XSSFSheet) workBook.createSheet("LineBarChart");

XSSFCellStyle   bmDataStyle;

XSSFRow     row = null;
XSSFCell    cell = null;

// Define a data Font
Font    dataFont = workBook.createFont();
dataFont.setFontHeightInPoints((short)11);
dataFont.setBold(false);

Font    dataFontBold = workBook.createFont();
dataFontBold.setFontHeightInPoints((short)11);
dataFontBold.setBold(true);

// Row 1
// Create FY month Headings (also used as range for X series labels)
bmDataStyle = bmDataStyle(workBook, dataFontBold, null, null, "right", false, false, false, false);
int rowCtr = 0;
int cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
for (int i = 0; i < FYMONTHS.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(CpttUtil.toSentenceCase(FYMONTHS[i]));
    cell.setCellStyle(bmDataStyle);
}

// Row 2
// Create Projected Obligation Plan dollars data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Projected Obligation Plans ($K)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "currency", "right", false, false, false, false);
for (int i = 0; i < POP_DOLLARS.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(POP_DOLLARS[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

// Row 3
// Create Projected Obligation Plan percent data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Projected Obligation Plans (%)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "pct0", "right", false, false, false, false);
for (int i = 0; i < POP_PCT.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(POP_PCT[i]);
    sheet.autoSizeColumn(i);
    cell.setCellStyle(bmDataStyle);
}

// Row 4
// Create Actual Obligations dollars data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Actual Obligations ($K)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "currency", "right", false, false, false, false);
for (int i = 0; i < OBL_ACTUALS.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(OBL_ACTUALS[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

// Row 5
// Create Actual Obligations percent data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Actual Obligations (%)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "pct0", "right", false, false, false, false);
for (int i = 0; i < OBL_PCT.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(OBL_PCT[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

// Row 6
// Create Cash Allocation Plan data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Cash Allocation Plan");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "currency", "right", false, false, false, false);
for (int i = 0; i < CASH_ALLOC.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(CASH_ALLOC[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

// Row 7
// Create Cash Disbursement dollars data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("Cash Disbursements ($K)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "currency", "right", false, false, false, false);
for (int i = 0; i < CASH_DISBURSE.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(CASH_DISBURSE[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

// Row 8
// Create OSD Benchmarks dollars data
bmDataStyle = bmDataStyle(workBook, dataFont, null, null, null, false, false, false, false);
cellCnt = 0;
row = sheet.createRow(rowCtr++);
cell = row.createCell(cellCnt++);
cell.setCellValue("OSD Benchmarks ($K)");
cell.setCellStyle(bmDataStyle);
sheet.autoSizeColumn(0);
bmDataStyle = bmDataStyle(workBook, dataFont, null, "currency", "right", false, false, false, false);
for (int i = 0; i < BENCH_DOLLARS.length; i++) {
    cell = row.createCell(cellCnt++);
    cell.setCellValue(BENCH_DOLLARS[i]);
    cell.setCellStyle(bmDataStyle);
    sheet.autoSizeColumn(i);
}

try {
    // Create a drawing canvas on the worksheet
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    // Define anchor points in the worksheet to position the chart
    XSSFClientAnchor    anchor = drawing.createAnchor(0, 0, 0, 0, 0, 12, 13, 40);

    // Create the chart object based on the anchor point
    XSSFChart   chart = drawing.createChart(anchor);

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

    String  monthsRefer = "LineBarChart!$B$1:$M$1";  // Range where months are

    // Bar Chart
    CTBarChart  ctBarChart = ctPlotArea.addNewBarChart();
    CTBoolean   ctBoolean = ctBarChart.addNewVaryColors();
    ctBoolean.setVal(false);
    ctBarChart.addNewBarDir().setVal(STBarDir.COL);

    // Name the series
    CTBarSer    ctBarSer = ctBarChart.addNewSer();
    CTSerTx     ctSerTx = ctBarSer.addNewTx();
    CTStrRef    ctStrRef = ctSerTx.addNewStrRef();
    ctStrRef.setF("LineBarChart!$A$6");
    ctBarSer.addNewIdx().setVal(0);  // 0 = blue

    // Labels for Bar Chart
    CTAxDataSource  ctAxDataSource = ctBarSer.addNewCat();
    ctStrRef = ctAxDataSource.addNewStrRef();
    ctStrRef.setF(monthsRefer);

    // Values for Bar Chart
    CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
    CTNumRef        ctNumRef = ctNumDataSource.addNewNumRef();
    String  valuesRefer = "LineBarChart!$B$6:$M$6";  // Range where values are
    ctNumRef.setF(valuesRefer);
    rgb = CTSRgbColor.Factory.newInstance();

ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)157,(byte)195,(byte)230});

    // Name the series
    CTBarSer    ctBarSer2 = ctBarChart.addNewSer();
    CTSerTx     ctSerTx2 = ctBarSer2.addNewTx();
    CTStrRef    ctStrRef2 = ctSerTx2.addNewStrRef();
    ctStrRef2.setF("LineBarChart!$A$7");
    ctBarSer2.addNewIdx().setVal(2);  // 1 = gray

    // Labels for Bar Chart
    CTAxDataSource  ctAxDataSource2 = ctBarSer2.addNewCat();
    ctStrRef2 = ctAxDataSource2.addNewStrRef();
    ctStrRef2.setF(monthsRefer);

    // Values for Bar Chart
    CTNumDataSource ctNumDataSource2 = ctBarSer2.addNewVal();
    CTNumRef        ctNumRef2 = ctNumDataSource2.addNewNumRef();
    String  valuesRefer2 = "LineBarChart!$B$7:$M$7";  // Range where values are
    ctNumRef2.setF(valuesRefer2);
ctBarSer2.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)219,(byte)219,(byte)219});

    // 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(true);
    ctCatAx.addNewAxPos().setVal(STAxPos.L);
    ctCatAx.addNewCrossAx().setVal(123457);  // ID of the Val axis
    //ctCatAx.addNewMinorTickMark().setVal(STTickMark.NONE);
    //ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

    // Val Left Axis
    CTValAx ctValAx = ctPlotArea.addNewValAx();
    ctValAx.addNewAxId().setVal(123457);
    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 Val axis
    ctValAx.addNewMinorTickMark().setVal(STTickMark.NONE);
    //ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
    ctValAx.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("LineBarChart!$A2");
    ctLineSer.addNewIdx().setVal(4);  // 2 = gray
    CTAxDataSource  ctAxDataSource1 = ctLineSer.addNewCat();
    ctStrRef1 = ctAxDataSource1.addNewStrRef();
    ctStrRef1.setF(monthsRefer);   // Months
    ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)0,(byte)0,(byte)0});

    String  values2Refer = "LineBarChart!$B$2:$M$2";   // Range for POP values
    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);   // ID of the Val axis

    CTCatAx ctCatAx1 = ctPlotArea.addNewCatAx();
    ctCatAx1.addNewAxId().setVal(1234);      // Cat axis
    CTScaling   ctScaling1 = ctCatAx1.addNewScaling();
    ctScaling1.addNewOrientation().setVal(STOrientation.MIN_MAX);
    ctCatAx1.addNewDelete().setVal(true);
    ctCatAx1.addNewAxPos().setVal(STAxPos.L);
    ctCatAx1.addNewCrossAx().setVal(12345);   // Val axis
    //CTBoolean ctBoolean1 = ctCatAx1.addNewAuto();

    CTValAx ctValAx2 = ctPlotArea.addNewValAx();
    ctValAx2.addNewAxId().setVal(12345);       // Val axis
    ctScaling1 = ctValAx2.addNewScaling();
    ctScaling1.addNewOrientation().setVal(STOrientation.MIN_MAX);
    ctValAx2.addNewDelete().setVal(false);
    ctValAx2.addNewAxPos().setVal(STAxPos.L);
    ctValAx2.addNewCrossAx().setVal(1234);    // Cat axis
    ctValAx2.addNewMinorTickMark().setVal(STTickMark.NONE);
    //ctValAx2.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);


    // Legend
    // Define legends for the line chart and set the position of the legend
    CTLegend    ctLegend = ctChart.addNewLegend();
    ctLegend.addNewLegendPos().setVal(STLegendPos.L);
    ctLegend.addNewOverlay().setVal(true);

    // Finally output the file
    file = new File(fileName);
    fos = new FileOutputStream(file);
    workBook.write(fos);
} catch (IOException ioe) {
    System.out.println("Caught a: " + ioe.getClass().getName());
    System.out.println("Message: " + ioe.getMessage());
    System.out.println("Stacktrace follows:.....");
    ioe.printStackTrace(System.out);
} finally {
    try {
        if (fos != null) {
            fos.close();
            fos = null;
        }
        workBook.close();
    } catch (IOException ioe) {
        System.out.println("Caught a: " + ioe.getClass().getName());
        System.out.println("Message: " + ioe.getMessage());
        System.out.println("Stacktrace follows:.....");
        ioe.printStackTrace(System.out);
    }
}

Augustas · Accepted Answer

Y Axis for both charts is controlled by CTValAx class. For Y Axis scale not to be displayed set ctValAx1.addNewDelete().setVal(true);. It means Y Axis for chart is created, but deleted vertical representation.

Combining bar and line chart using Apache POI

Answers (1)

Related Questions