依赖版本

Apache poi 4.1.1
poi-ooxml-4.1.1.jar
poi-ooxml-schemas-4.1.1.jar
poi-excelant-4.1.1.jar
commons-codec-1.13.jar
commons-collections4-4.4.jar
commons-compress-1.19.jar
commons-logging-1.2.jar
commons-math3-3.6.1.jar
poi-4.1.1.jar
xmlbeans-3.1.0.jar
JDK 1.8
# 由于POI官方提供的example为使用模版的方法,并且文档并不全面,所以本文所产生的代码并不是准确解决方案,仅作参考

截止4.1.1版本POI支持图表大概16种
下文将持续更新所支持的图表实现

用于填充表格

private void buildChartExcelData(XSSFWorkbook wb, JChartData data) {
        int position = 0;// 表格从第0行开始
        Sheet sheet = wb.createSheet(data.getName());
        List<Map<String, Object>> dataList = data.getData();

        List<String> seriesNameArr = data.getSeriesName();
        List<String> seriesTitleArr = data.getSeriesTitle();
        List<Integer> scaleArr = data.getScale();
        List<Boolean> isPercentArr = data.getIsPercent();
        CellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN);// 左边框
        style.setBorderTop(BorderStyle.THIN);// 上边框
        style.setBorderRight(BorderStyle.THIN);// 右边框
        style.setAlignment(HorizontalAlignment.CENTER);

        CellStyle style1 = wb.createCellStyle();
        style1.setBorderBottom(BorderStyle.THIN); // 下边框
        style1.setBorderLeft(BorderStyle.THIN);// 左边框
        style1.setBorderTop(BorderStyle.THIN);// 上边框
        style1.setBorderRight(BorderStyle.THIN);// 右边框
        style1.setAlignment(HorizontalAlignment.CENTER);

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐方式
        // cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//垂直对齐方式

        // 根据数据创建excel第一行标题行
        for (int i = 0; i < seriesTitleArr.size(); i++) {
            if (sheet.getRow(position) == null) {
                sheet.createRow(position).createCell(i)
                        .setCellValue(seriesTitleArr.get(i) == null ? "" : seriesTitleArr.get(i));

            } else {
                sheet.getRow(position).createCell(i)
                        .setCellValue(seriesTitleArr.get(i) == null ? "" : seriesTitleArr.get(i));
            }
            // 标题行创建背景颜色
            sheet.getRow(position).getCell(i).setCellStyle(style);
        }

        // 遍历数据行
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> baseFormMap = dataList.get(i);// 数据行
            // fldNameArr字段属性
            for (int j = 0; j < seriesNameArr.size(); j++) {
                if (sheet.getRow(position + i + 1) == null) {
                    if (j == 0) {
                        try {
                            sheet.createRow(position + i + 1).createCell(j)
                                    .setCellValue(((String) baseFormMap.get(seriesNameArr.get(j))) == null ? ""
                                            : ((String) baseFormMap.get(seriesNameArr.get(j))));
                        } catch (Exception e) {
                            if ((String) baseFormMap.get(seriesNameArr.get(j)) == null) {
                                sheet.createRow(position + i + 1).createCell(j).setCellValue("");
                            } else {
                                sheet.createRow(position + i + 1).createCell(j)
                                        .setCellValue((RichTextString) baseFormMap.get(seriesNameArr.get(j)));
                            }
                        }
                    }
                    // 标题行创建背景颜色
                    sheet.getRow(position + i + 1).getCell(j).setCellStyle(style1);
                } else {
                    BigDecimal b = (BigDecimal) baseFormMap.get(seriesNameArr.get(j));
                    double value = 0d;
                    if (b != null) {
                        value = b.doubleValue();
                    }
                    if (value == 0) {
                        sheet.getRow(position + i + 1).createCell(j);
                    } else {
                        sheet.getRow(position + i + 1).createCell(j)
                                .setCellValue(((BigDecimal) baseFormMap.get(seriesNameArr.get(j))).doubleValue());
                    }
                    if (isPercentArr.get(j)) {// 是否设置百分比
                        // 设置Y轴的数字为百分比样式显示
                        StringBuilder sb = new StringBuilder();

                        if (scaleArr.get(j) == 0) {// 保留几位小数
                            sb.append("0");
                            if (isPercentArr.get(j)) {// 是否百分比
                                sb.append("%");
                            }
                        } else {
                            sb.append("0.");
                            for (int k = 0; k < scaleArr.get(j); k++) {
                                sb.append("0");
                            }
                            if (isPercentArr.get(j)) {// 是否百分比
                                sb.append("%");
                            }
                        }
                        cellStyle.setDataFormat(wb.createDataFormat().getFormat(sb.toString()));
                        sheet.getRow(position + i + 1).getCell(j).setCellStyle(cellStyle);
                    } else {
                        // 是否设置百分比
                        // 设置Y轴的数字为百分比样式显示
                        StringBuilder sb = new StringBuilder();

                        if (scaleArr.get(j) == 0) {// 保留几位小数
                            sb.append("0");
                        } else {
                            sb.append("0.");
                            for (int k = 0; k < scaleArr.get(j); k++) {
                                sb.append("0");
                            }
                        }
                        cellStyle.setDataFormat(wb.createDataFormat().getFormat(sb.toString()));
                        sheet.getRow(position + i + 1).getCell(j).setCellStyle(cellStyle);
                    }
                }
            }

        }
    }

word图和表内容初始似乎是分别设置,在word或者wps中编辑之后才会同步,所以初试需要重复赋值

private void fillViewData(int i, CTStrData strData, CTNumData numData ,List<Map<String, Object>> dataList,
        List<String> seriesNameArr,
        List<String> seriesTitleArr,
        List<Integer> scaleArr,
        List<Boolean> isPercentArr ) {
        long idx = 0;
        for (int j = 0; j < dataList.size(); j++) {
            //判断获取的值是否为空
            BigDecimal b = (BigDecimal) dataList.get(j).get(seriesNameArr.get(i + 1));
            String value = b.toPlainString();
            if (!"0".equals(value)) {
                CTNumVal numVal = numData.addNewPt();//序列值
                numVal.setIdx(idx);

                if (isPercentArr.get(i+1)) {// 是否设置百分比
                    // 设置Y轴的数字为百分比样式显示
                    StringBuilder sb = new StringBuilder();
                    if (scaleArr.get(i+1)==0) {// 保留几位小数
                        sb.append("0");
                        if (isPercentArr.get(i+1)) {// 是否百分比
                            sb.append("%");
                        }
                    } else {
                        sb.append("0.");
                        for (int k = 0; k < scaleArr.get(i+1); k++) {
                            sb.append("0");
                        }
                        if (isPercentArr.get(i+1)) {// 是否百分比
                            sb.append("%");
                        }
                    }
                    numVal.setFormatCode(sb.toString());
                } else {
                    // 是否设置百分比
                    // 设置Y轴的数字为百分比样式显示
                    StringBuilder sb = new StringBuilder();

                    if (scaleArr.get(i+1)==0) {// 保留几位小数
                        sb.append("0");
                    } else {
                        sb.append("0.");
                        for (int k = 0; k < scaleArr.get(i+1); k++) {
                            sb.append("0");
                        }
                    }
                    numVal.setFormatCode(sb.toString());
                }
                numVal.setV(value);
            }
            CTStrVal sVal = strData.addNewPt();//序列名称
            sVal.setIdx(idx);
            sVal.setV((String) dataList.get(j).get(seriesNameArr.get(0)));
            idx++;
        }


}

图表工厂

为了适应更多组合场景,可以把类型进行泛化,按照目前POI支持的图表类型可以分为为无坐标轴图形,XY坐标轴图形,XZY坐标轴图形

  • 无坐标轴图形: 饼图 圆环图

  • XY坐标轴图形: 折线图 柱状图 散点图 面积图 。。。

  • XZY坐标轴图形:气泡图
    按照这个逻辑,代码要做一些调整🤮

    public class JChartFactory {
     // 无坐标轴图表
     private static String[] thinCharts = new String[] { 
             ChartSeries.TYPE_PIE, 
             ChartSeries.TYPE_DOUGHNUT };
     // 双坐标轴图表
     private static String[] xyCharts = new String[] { 
             ChartSeries.TYPE_AREA, 
             ChartSeries.TYPE_BAR,
             ChartSeries.TYPE_LINE, 
             ChartSeries.TYPE_STACK, 
             ChartSeries.TYPE_STACK100};
     private static String[] xyzCharts = new String[]{
         ChartSeries.TYPE_BUBBLE
     }
     public JChart getChart(String type) throws Exception {
         if (canDrowChart(type, thinCharts)) {
             return new JThinChart();
         } else if (canDrowChart(type, xyCharts)) {
             return new JXYChart();
         } else if (canDrowChart(type, xyzCharts)) {
             return new JBubbleChart();
         }else{
             throw new Exception("暂不支持的图列表类型【"+type+"】");
         }
         return new JXYChart();
     }
    
     private boolean canDrowChart(String type, String[] types) {
    
         for (String str : types) {
             if (type.equalsIgnoreCase(str)) {
                 return true;
             }
         }
         return false;
     }
    }
    
### 无坐标轴图形
```JAVA
public class JThinChart extends JBaseChart {

    @Override
    public void GenerateChart(XWPFDocument document, WordChart data) {
        super.GenerateChart(document, data);
        CTChartSpace ctChartSpace = chart.getCTChartSpace();
        ctChartSpace.addNewDate1904().setVal(false);
        ctChartSpace.addNewRoundedCorners().setVal(false);

        CTChart ctChart = chart.getCTChart();
        setChartTitle(ctChart, data.getTitle().getText());

        CTPlotArea ctPlotArea = ctChart.getPlotArea();

        setAxis(ctChart,ctPlotArea,data);
        setChartLegend(ctChart, data.getLegend());
    }

    /**
     * 创建系列
     * @param ctChart
     * @param ctPlotArea
     * @param data
     */
    void setAxis(CTChart ctChart, CTPlotArea ctPlotArea,WordChart data){
        for (int i = 0; i < data.getSeriesList().size(); i++) {
            ChartSeries chartSeries = data.getSeriesList().get(i);
            if(chartSeries.getType().equalsIgnoreCase(ChartSeries.TYPE_PIE)){
                PieSeries.create(ctChart, ctPlotArea, data,this);
            }else if(chartSeries.getType().equalsIgnoreCase(ChartSeries.TYPE_DOUGHNUT)){
                DoughnutSeries.create(ctChart, ctPlotArea, data,this);
            }
        }
    }


}

XY坐标轴图形

public class JXYChart extends JBaseChart {

    protected static Long PRIMARYIDX1 = 123456L;
    protected static Long PRIMARYIDX2 = 123457L;
    protected static Long SECONDARYIDX1 = 1234567L;
    protected static Long SECONDARYIDX2 = 1234578L;

    @Override
    public void GenerateChart(XWPFDocument document, WordChart data) {
        super.GenerateChart(document, data);
        // 柱状图 分组类型 簇状图 堆积图等
        CTChartSpace ctChartSpace = chart.getCTChartSpace();
        ctChartSpace.addNewDate1904().setVal(false);
        ctChartSpace.addNewRoundedCorners().setVal(false);
        CTChart ctChart = chart.getCTChart();
        setChartTitle(ctChart, data.getTitle().getText());//设置图表标题
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        setAxis(ctChart, ctPlotArea, data);//是设置序列
        setSeries(ctPlotArea, data);//设置横纵轴
        setChartLegend(ctChart, data.getLegend());//设置图例

    }

    /**
     * 创建系列
     * 
     * @param ctChart
     * @param ctPlotArea
     * @param data
     */
    void setAxis(CTChart ctChart, CTPlotArea ctPlotArea, WordChart data) {
        // NOTE 同一种类型只用同一量纲
        List<String> types = new ArrayList<String>();
        for (ChartSeries series : data.getSeriesList()) {
            if (!types.contains(series.getType())) {
                types.add(series.getType());
            }
        }

        for (String type : types) {
            if (type.equalsIgnoreCase(ChartSeries.TYPE_BAR)) {
                BarSeries.create(ctChart, ctPlotArea, data,this);
            } else if (type.equalsIgnoreCase(ChartSeries.TYPE_LINE)) {
                LineSeries.create(ctChart, ctPlotArea, data,this);
            } else if (type.equalsIgnoreCase(ChartSeries.TYPE_STACK)) {
                StackSeries.create(ctChart, ctPlotArea, data,this);
            } else if (type.equalsIgnoreCase(ChartSeries.TYPE_STACK100)) {
                Stack100Series.create(ctChart, ctPlotArea, data,this);
            } else if (type.equalsIgnoreCase(ChartSeries.TYPE_AREA)) {
                AreaSeries.create(ctChart, ctPlotArea, data,this);
            } else if (type.equalsIgnoreCase("Scatter")) {
                ScatterSeries.create(ctChart, ctPlotArea, data,this);//暂时WordChart未支持
            }
        }
    }

    /**
     * 设置横纵坐标轴信息
     * 
     * @param ctPlotArea
     * @param wordChart
     */
    void setSeries(CTPlotArea ctPlotArea, WordChart wordChart) {

        // 主要横坐标
        CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
        ctCatAx.addNewAxId().setVal(PRIMARYIDX1); // id of the cat axis
        CTScaling ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(PRIMARYIDX2); // id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
        if (StringUtils.isNotEmpty(wordChart.getXAxis().getTitle())) {
            CTTitle xAxisTitle = ctCatAx.addNewTitle();
            xAxisTitle.addNewOverlay().setVal(false);
            xAxisTitle.addNewLayout();
            xAxisTitle.addNewTx().addNewRich().addNewP().addNewR().setT(wordChart.getXAxis().getTitle());
        }

        // 主要纵坐标
        CTValAx ctValAx = ctPlotArea.addNewValAx();
        ctValAx.addNewAxId().setVal(PRIMARYIDX2); // id of the val axis
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(PRIMARYIDX1); // id of the cat axis
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
        if (StringUtils.isNotEmpty(wordChart.getYPrimaryAxis().getTitle())) {
            CTTitle xAxisTitle = ctValAx.addNewTitle();
            xAxisTitle.addNewOverlay().setVal(false);
            xAxisTitle.addNewLayout();
            xAxisTitle.addNewTx().addNewRich().addNewP().addNewR().setT(wordChart.getYPrimaryAxis().getTitle());
        }

        // 次要横坐标
        CTCatAx ctCatAxline = ctPlotArea.addNewCatAx();
        ctCatAxline.addNewAxId().setVal(SECONDARYIDX1); // id of the cat axis
        CTScaling ctScalingline = ctCatAxline.addNewScaling();
        ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAxline.addNewDelete().setVal(true);
        ctCatAxline.addNewAxPos().setVal(STAxPos.B);
        ctCatAxline.addNewMajorTickMark().setVal(STTickMark.OUT);
        ctCatAxline.addNewMinorTickMark().setVal(STTickMark.NONE);
        ctCatAxline.addNewAuto().setVal(true);
        ctCatAxline.addNewLblAlgn().setVal(STLblAlgn.CTR);
        // ctCatAxline.addNewLblOffset().setVal(100);
        ctCatAxline.addNewNoMultiLvlLbl().setVal(false);
        ctCatAxline.addNewCrossAx().setVal(SECONDARYIDX2); // id of the val axis
        ctCatAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        // 次要纵坐标
        CTValAx ctValAxline = ctPlotArea.addNewValAx();
        ctValAxline.addNewAxId().setVal(SECONDARYIDX2); // id of the val axis
        ctScalingline = ctValAxline.addNewScaling();
        ctScalingline.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAxline.addNewAxPos().setVal(STAxPos.R);
        ctValAxline.addNewMajorTickMark().setVal(STTickMark.OUT);
        ctValAxline.addNewMinorTickMark().setVal(STTickMark.NONE);
        ctValAxline.addNewCrosses().setVal(STCrosses.MAX);
        ctValAxline.addNewCrossBetween().setVal(STCrossBetween.BETWEEN);
        ctValAxline.addNewCrossAx().setVal(SECONDARYIDX1); // id of the cat axis
        ctValAxline.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
        if (StringUtils.isNotEmpty(wordChart.getYSecondaryAxis().getTitle())) {
            CTTitle xAxisTitle = ctValAxline.addNewTitle();
            xAxisTitle.addNewOverlay().setVal(false);
            xAxisTitle.addNewLayout();
            xAxisTitle.addNewTx().addNewRich().addNewP().addNewR().setT(wordChart.getYSecondaryAxis().getTitle());
        }

        // 显示坐标轴
        ctCatAx.addNewDelete().setVal(false);
        ctValAx.addNewDelete().setVal(false);
        // Y轴右侧坐标true删除,false保留
        ctValAxline.addNewDelete().setVal(!checkIsDoubleValaxis(wordChart));
    }


    /**
     * 判断是否存在右侧坐标轴
     * 
     * @param wordChart
     * @return
     */
    boolean checkIsDoubleValaxis(WordChart wordChart) {
        for (ChartSeries series : wordChart.getSeriesList()) {
            if (series.getAxisGroup() == 1) {
                // 存在次轴
                return true;
            }
        }
        return false;
    }

}

创建系列

创建系列大同小异,下面是面积系列创建代码


class AreaSeries extends JXYChart{
    /**
     * 面积图实现
     * 
     * @param i
     * @param ctChart
     * @param ctPlotArea
     * @param data
     * @param idx1
     * @param idx2
     */

    static void create(CTChart ctChart, CTPlotArea ctPlotArea, WordChart data,JXYChart xyChart) {
        CTAreaChart ctAreaChart = ctPlotArea.addNewAreaChart();
        CTBoolean ctBoolean = ctAreaChart.addNewVaryColors();
        ctAreaChart.addNewGrouping().setVal(STGrouping.STANDARD);

        // 创建序列,并且设置选中区域
        for (int i = 0; i < data.getSeriesList().size(); i++) {
            if (!data.getSeriesList().get(i).getType().equalsIgnoreCase(ChartSeries.TYPE_AREA)) {
                continue;
            }

            // 创建序列,并且设置选中区域
            CTAreaSer ctAreaSer = ctAreaChart.addNewSer();
            CTSerTx ctSerTx = ctAreaSer.addNewTx();
            CTStrRef ctStrRef = ctSerTx.addNewStrRef();
            // 图例区 设施图例title
            xyChart.fillLegendData(ctStrRef, data.getSeriesList().get(i).getTitle());
            String legendDataRange = new CellRangeAddress(0, 0, i + 1, i + 1).formatAsString(data.getTitle().getText(),
                    true);
            ctStrRef.setF(legendDataRange);
            ctAreaSer.addNewIdx().setVal(i);

            // 横坐标区
            CTAxDataSource cttAxDataSource = ctAreaSer.addNewCat();
            ctStrRef = cttAxDataSource.addNewStrRef();
            String axisDataRange = new CellRangeAddress(1, data.getXAxis().getDatas().size(), 0, 0)
                    .formatAsString(data.getTitle().getText(), true);
            ctStrRef.setF(axisDataRange);

            // 数据区域
            CTNumDataSource ctNumDataSource = ctAreaSer.addNewVal();
            CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
            String numDataRange = new CellRangeAddress(1, data.getXAxis().getDatas().size(), i + 1, i + 1)
                    .formatAsString(data.getTitle().getText(), true);
            ctNumRef.setF(numDataRange);

            // strData.set
            CTStrData strData = cttAxDataSource.getStrRef().addNewStrCache();
            CTNumData numData = ctNumDataSource.getNumRef().addNewNumCache();

            xyChart.fillViewData(strData, numData, data.getXAxis(), data.getSeriesList().get(i));

            // 设置标签格式
            xyChart.setLabelFormat(ctAreaSer.addNewDLbls(), ctBoolean, data.getSeriesList().get(i).getDataLabels());

            /*
             * //是否是平滑曲线 CTBoolean addNewSmooth = ctAreaSer.addNewSmooth();
             * addNewSmooth.setVal(false); / /是否是堆积曲线 CTMarker addNewMarker =
             * ctAreaSer.addNewMarker(); CTMarkerStyle addNewSymbol =
             * addNewMarker.addNewSymbol();
             * addNewSymbol.setVal(STMarkerStyle.NONE);
             */
            // telling the BarChart that it has axes and giving them Ids
            if (data.getSeriesList().get(i).getAxisGroup() == 0) {
                ctAreaChart.addNewAxId().setVal(PRIMARYIDX1);
                ctAreaChart.addNewAxId().setVal(PRIMARYIDX2);
            } else {
                ctAreaChart.addNewAxId().setVal(SECONDARYIDX1);
                ctAreaChart.addNewAxId().setVal(SECONDARYIDX2);
            }
        }
    }

}

只要有树叶飞舞的地方,火就会燃烧。