如何创建java poi条形图，结合给定图像的两个条形值? [英] How to create java poi bar chart combining two bars value like given image?

问题描述

我想用 java poi 创建这样的条形图，(有两组条形)，直到现在只能找到单一类型的条形，谁能帮我参考?谢谢.

解决方案

从您的问题中不清楚您已经找到了使用 apache poi 创建条形图的代码.到目前为止，apache poi 只支持折线图和散点图，不直接支持条形图.因此，只能使用底层的低级对象来创建条形图.对于以

此示例需要 ooxml-schemas-1.4.jar 中提到的所有模式的完整 jar 包，如FAQ-N10025.

I want to create bar chart something like this with java poi , (Has two set of bars),Till now could find only for single type of bars, can anyone help me out with any reference?? Thanks.

解决方案

Not clear from your question what code for creating bar chart using apache poi you have found already. Until now apache poi does only supporting line chart and scatter chart but not bar chart directly. So creating a bar chart is only possible using the underlaying low level objects. For a reference do starting with org.openxmlformats.schemas.drawingml.x2006.chart.CTChart.

Example code which creates your example chart:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;

import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;

public class BarChart {

    private static CTChart createDefaultBarChart(XSSFChart chart, CellReference firstDataCell, CellReference lastDataCell, boolean seriesInCols) {

        CTChart ctChart = chart.getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

        int firstDataRow = firstDataCell.getRow();
        int lastDataRow = lastDataCell.getRow();
        int firstDataCol = firstDataCell.getCol();
        int lastDataCol = lastDataCell.getCol();
        String dataSheet = firstDataCell.getSheetName();

        int idx = 0;

        if (seriesInCols) { //the series are in the columns of the data cells

            for (int c = firstDataCol + 1; c < lastDataCol + 1; c++) {
                CTBarSer ctBarSer = ctBarChart.addNewSer();
                CTSerTx ctSerTx = ctBarSer.addNewTx();
                CTStrRef ctStrRef = ctSerTx.addNewStrRef();
                ctStrRef.setF(new CellReference(dataSheet, firstDataRow, c, true, true).formatAsString());

                ctBarSer.addNewIdx().setVal(idx++);
                CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
                ctStrRef = cttAxDataSource.addNewStrRef();

                ctStrRef.setF(new AreaReference(
                        new CellReference(dataSheet, firstDataRow + 1, firstDataCol, true, true),
                        new CellReference(dataSheet, lastDataRow, firstDataCol, true, true),
                        SpreadsheetVersion.EXCEL2007).formatAsString());

                CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
                CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();

                ctNumRef.setF(new AreaReference(
                        new CellReference(dataSheet, firstDataRow + 1, c, true, true),
                        new CellReference(dataSheet, lastDataRow, c, true, true),
                        SpreadsheetVersion.EXCEL2007).formatAsString());

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

            }
        } else { //the series are in the rows of the data cells

            for (int r = firstDataRow + 1; r < lastDataRow + 1; r++) {
                CTBarSer ctBarSer = ctBarChart.addNewSer();
                CTSerTx ctSerTx = ctBarSer.addNewTx();
                CTStrRef ctStrRef = ctSerTx.addNewStrRef();
                ctStrRef.setF(new CellReference(dataSheet, r, firstDataCol, true, true).formatAsString());

                ctBarSer.addNewIdx().setVal(idx++);
                CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
                ctStrRef = cttAxDataSource.addNewStrRef();

                ctStrRef.setF(new AreaReference(
                        new CellReference(dataSheet, firstDataRow, firstDataCol + 1, true, true),
                        new CellReference(dataSheet, firstDataRow, lastDataCol, true, true),
                        SpreadsheetVersion.EXCEL2007).formatAsString());

                CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
                CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();

                ctNumRef.setF(new AreaReference(
                        new CellReference(dataSheet, r, firstDataCol + 1, true, true),
                        new CellReference(dataSheet, r, lastDataCol, true, true),
                        SpreadsheetVersion.EXCEL2007).formatAsString());

                //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);
        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.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(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

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

        return ctChart;

    }

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

        DataFormat format = wb.createDataFormat();

        CellStyle oneDecimal = wb.createCellStyle();
        oneDecimal.setDataFormat(format.getFormat("0.0"));

        //put some data in the sheet
        Row row;
        Cell cell;

        row = sheet.createRow(0);
        row.createCell(0);
        row.createCell(1).setCellValue("Male");
        row.createCell(2).setCellValue("Female");

        for (int r = 1; r < 7; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue(1975 + r * 5);
            cell = row.createCell(1);
            cell.setCellValue(java.util.concurrent.ThreadLocalRandom.current().nextDouble(40.0, 70.0));
            cell.setCellStyle(oneDecimal);
            cell = row.createCell(2);
            cell.setCellValue(java.util.concurrent.ThreadLocalRandom.current().nextDouble(40.0, 70.0));
            cell.setCellStyle(oneDecimal);
        }

        //create empty chart in the sheet
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 7, 8, 20);

        XSSFChart chart = drawing.createChart(anchor);

        //create the references to the chart data
        CellReference firstDataCell = new CellReference(sheet.getSheetName(), 0, 0, true, true);
        CellReference lastDataCell = new CellReference(sheet.getSheetName(), 6, 2, true, true);

        //create a default bar chart from the data
        CTChart ctBarChart = createDefaultBarChart(chart, firstDataCell, lastDataCell, true);

        //now we can customizing the chart

        //legend position:
        ctBarChart.getLegend().unsetLegendPos();
        ctBarChart.getLegend().addNewLegendPos().setVal(STLegendPos.R);

        //data labels:
        CTBoolean ctboolean = CTBoolean.Factory.newInstance();
        ctboolean.setVal(true);
        ctBarChart.getPlotArea().getBarChartArray(0).addNewDLbls().setShowVal(ctboolean);
        ctboolean.setVal(false);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowSerName(ctboolean);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowPercent(ctboolean);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowLegendKey(ctboolean);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowCatName(ctboolean);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowLeaderLines(ctboolean);
        ctBarChart.getPlotArea().getBarChartArray(0).getDLbls().setShowBubbleSize(ctboolean);

        //val axis maximum:
        ctBarChart.getPlotArea().getValAxArray(0).getScaling().addNewMax().setVal(100);

        //cat axis title:
        ctBarChart.getPlotArea().getCatAxArray(0).addNewTitle().addNewOverlay().setVal(false);
        ctBarChart.getPlotArea().getCatAxArray(0).getTitle().addNewTx().addNewRich().addNewBodyPr();
        ctBarChart.getPlotArea().getCatAxArray(0).getTitle().getTx().getRich().addNewP().addNewR().setT("School Year");

        //series colors:
        ctBarChart.getPlotArea().getBarChartArray(0).getSerArray(0).getSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0, 0, (byte) 255});
        ctBarChart.getPlotArea().getBarChartArray(0).getSerArray(1).getSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0, (byte) 255, 0});

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

Result:

This example needs the full jar of all of the schemas ooxml-schemas-1.4.jar as mentioned in the FAQ-N10025.

这篇关于如何创建java poi条形图，结合给定图像的两个条形值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！