CIAQ图表替换

linzhijie

2023-04-23 94c73409b82edbfeef2b2dc678526f09f9d28169

CIAQ图表替换

 src/main/java/com/ots/project/tool/report/CIAQ/chart/CIAQChart.java

New file
@@ -0,0 +1,367 @@
package com.ots.project.tool.report.CIAQ.chart;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.ots.common.utils.StringUtils;
import com.ots.project.tool.report.CIAQ.CIAQReport;
import com.ots.project.tool.report.MAQ.base.RowData;
import com.ots.project.tool.report.MAQ.base.SeriesData;
import com.ots.project.tool.report.reportCalculation.response.ReportCIAQAPIResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFChart;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description: CIAQ图表 散点图跟柱形图数据变更
 * @author: zhijie
 * @create: 2023-04-23 16:31
 **/
@Slf4j
public class CIAQChart {

    private static Double PR_Dim01, PR_Dim02, PR_Dim03, PR_Dim04, PR_Dim05, PR_Dim06, PR_Dim07, PR_Dim08;

    public static void main(String[] args) throws IOException {
        String str = "{\"occ1L\":\"地质勘探工程技术人员（比如地质矿产调查工程技术人员）\",\"occ3L\":\"矿山工程技术人员（比如选矿与矿物加工工程技术人员）\",\"PR_Dim01\":\"56\",\"PR_Dim02\":\"78\",\"PR_Dim03\":\"40\",\"PR_Dim04\":\"67\",\"occ5H\":\"音乐指挥与演员（比如电影电视演员、乐器演奏员）\",\"occ9L\":\"信息通信网络运行管理人员（比如信息通信网络运行管理员）\",\"occ7H\":\"工艺美术与创意设计专业人员（比如服装设计人员、环境设计人员）\",\"occ1H\":\"绿化与园艺服务人员（比如盆景师、插花花艺师）\",\"occ5L\":\"机械工程技术人员（比如机械设计工程技术人员、自动控制工程技术人员）\",\"occ3H\":\"文学、艺术学研究人员\",\"occ7L\":\"工业机器人操作运维人员（比如工业机器人系统运维员、）\",\"occ10H\":\"酒类饮品调制师（比如酿酒师、酒体设计师）\",\"occ10L\":\"地质勘查人员（比如物探工、地质调查员）\",\"occ8H\":\" 陶瓷制品制造人员（比如陶瓷装饰工、古建琉璃工）\",\"PR_Dim05\":\"89\",\"PR_Dim06\":\"12\",\"PR_Dim07\":\"57\",\"PR_Dim08\":\"33\",\"occ2L\":\"测绘和地理信息工程技术人员（比如工程测量工程技术人员）\",\"occ4H\":\"文艺创作与编导人员（比如文学作家、舞蹈编导）\",\"occ8L\":\"电子工程技术人员（比如电子材料/电子元器件工程技术人员）\",\"occ6H\":\"美术专业人员（比如画家、雕塑家）\",\"occ4L\":\"冶金工程技术人员（比如冶金热能工程技术人员、稀土工程技术人员）\",\"occ2H\":\"舞台专业人员（比如灯光师、化妆师）\",\"occ6L\":\"航空工程技术人员（比如飞行器制造工程技术人员）\",\"condition\":\"1\",\"occ9H\":\"摄影扩印服务人员（比如商业摄影师）\"}";
        Map<String ,Object> textMap = JSON.parseObject(str, HashMap.class);
        try (OutputStream os = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\CIAQ1.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\Administrator\\Desktop\\CIAQ.docx"))){
            changeChart(document,textMap);
            document.write(os);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 替换CIAQ图表数据
     * @param document 文档对象
     * @param textMap 报告字典键值对
     * @return
     */
    public static boolean changeChart(XWPFDocument document, Map<String, Object> textMap){
        List<SeriesData> seriesDatas = initData(textMap);
        if(seriesDatas == null){
            log.error("替换CIAQ图表数据,初始化数据失败!");
            return false;
        }
        //CIAQ图表数据
        changeCIAQChart(document,seriesDatas);
        return true;
    }

    /**
     * 判断是否为空
     * @param value
     * @return
     */
    public static boolean checkParamsIsNull(Double... value) {
        for (int i = 0; i < value.length; i++) {
            if (Objects.isNull(value[i])) {
                return true;
            }
        }
        return false;
    }

    /**
     * 初始化报告数据
     * @param textMap
     * @return
     */
    public static List<SeriesData> initData(Map<String, Object> textMap){
        //获取命中数值
        PR_Dim01 = Double.valueOf((String) textMap.get("PR_Dim01"));
        PR_Dim02 = Double.valueOf((String) textMap.get("PR_Dim02"));
        PR_Dim03 = Double.valueOf((String) textMap.get("PR_Dim03"));
        PR_Dim04 = Double.valueOf((String) textMap.get("PR_Dim04"));
        PR_Dim05 = Double.valueOf((String) textMap.get("PR_Dim05"));
        PR_Dim06 = Double.valueOf((String) textMap.get("PR_Dim06"));
        PR_Dim07 = Double.valueOf((String) textMap.get("PR_Dim07"));
        PR_Dim08 = Double.valueOf((String) textMap.get("PR_Dim08"));

        //判断是否为空
        if (checkParamsIsNull(PR_Dim01, PR_Dim02, PR_Dim03, PR_Dim04, PR_Dim05, PR_Dim06, PR_Dim07, PR_Dim08)) {
            return null;
        }

        // 测试数据
        List<SeriesData> seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"健康科学"),
                        new RowData(" ", 0,"创造性表达"),
                        new RowData(" ", 0,"科技"),
                        new RowData(" ", 0,"人"),
                        new RowData(" ", 0,"组织"),
                        new RowData(" ", 0,"影响"),
                        new RowData(" ", 0,"自然"),
                        new RowData(" ", 0,"事物")
                )),
                new SeriesData("价值观分数", Arrays.asList(
                        new RowData("价值观分数", PR_Dim01),
                        new RowData("价值观分数", PR_Dim02),
                        new RowData("价值观分数", PR_Dim03),
                        new RowData("价值观分数", PR_Dim04),
                        new RowData("价值观分数", PR_Dim05),
                        new RowData("价值观分数", PR_Dim06),
                        new RowData("价值观分数", PR_Dim07),
                        new RowData("价值观分数", PR_Dim08)
                ))
        );
        return seriesDatas;
    }

    /**
     * 替换word图表 雷达图带标记图表
     * @param doc
     * @param seriesDatas
     */
    public static void changeCIAQChart(XWPFDocument doc,List<SeriesData> seriesDatas) {
        try {
            //动态刷新图表
            List<POIXMLDocumentPart> relations = doc.getRelations();
            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;

                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();
                    // excel内置表格
                    XSSFWorkbook workbook = chart.getWorkbook();
                    XSSFSheet sheet = workbook.getSheetAt(0);

                    // 查看图表类型
                    // getChartType(part);
                    // 遍历数据
                    //printData(sheet);

                    // 雷达图
                    if (!plot.getRadarChartList().isEmpty()) {
                        CTRadarChart radarChart = plot.getRadarChartArray(0);
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, workbook, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        //雷达图数据源 散点坐标
                        for (CTRadarSer ser : radarChart.getSerList()) {
                            //更新雷达图缓存
                            updateRadarChart(seriesDatas, ser.getCat(),ser.getVal());
                        }
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            log.error("CIAQGS替换word图表 散点图跟柱形图组合图形失败：{}",e);
        }
    }

    private static void printData(XSSFSheet sheet){
        //先要遍历行
        for(int rowNum=0;rowNum<=sheet.getLastRowNum();rowNum++) {
            XSSFRow hssRow = sheet.getRow(rowNum);
            if (hssRow == null) {
                continue;
            }
            for (int cellNum = 0; cellNum <= hssRow.getLastCellNum(); cellNum++) {
                XSSFCell hssfCell = hssRow.getCell(cellNum);
                if (hssfCell == null) {
                    continue;
                }
                System.out.print("--" + getValue(hssfCell));
            }
            System.out.println();
        }
    }

    private static String getValue(XSSFCell hssfCell){
        if(hssfCell.getCellType()== CellType.BOOLEAN){
            return String.valueOf(hssfCell.getBooleanCellValue());
        }else if(hssfCell.getCellType()==CellType.NUMERIC){
            return String.valueOf(hssfCell.getNumericCellValue());
        }else{
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 更新图表的关联 excel
     *
     * @param seriesDatas
     * @param workbook
     * @param sheet
     */
    protected static void refreshExcel(List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet) {
        XSSFRow title = sheet.getRow(0);
        //遍历数据数组进行excel赋值
        for (int i = 0; i < seriesDatas.size(); i++) {
            SeriesData data = seriesDatas.get(i);
            if (data.name != null && !data.name.isEmpty()) {
                // 第一行标题
                XSSFCell cell = title.getCell(i);
                if (cell == null) {
                    cell = title.createCell(i + 1);
                }
                cell.setCellValue(data.name);
            }
            int size = data.value.size();
            //遍历数据进行赋值
            for (int j = 0; j < size; j++) {
                //从第二行开始赋值
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                RowData cellValu = data.value.get(j);

                //第一列开始赋值
                XSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cell = row.createCell(i);
                }
                //优先使用字符串值 CIAQ第一列是中文
                if(StringUtils.isNotEmpty(cellValu.valueStr)){
                    cell.setCellValue(cellValu.valueStr);
                }else{
                    cell.setCellValue(cellValu.value);
                }
            }
            //删除多余行数
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
    }


    /**
     * 更新 雷达图 缓存数据
     *
     * @param seriesDatas 数据
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    protected static void updateRadarChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
                                             CTNumDataSource yDataSource) {

        //获取xy坐标数据条数
        long xStrCnt = xDataSource.getStrRef().getStrCache().getPtCount().getVal();
        long yNumCnt = yDataSource.getNumRef().getNumCache().getPtCount().getVal();

        //获取XY最新数据
        List<RowData> xRowDatas = seriesDatas.get(0).getValue();
        int xRowDataSize = xRowDatas.size();
        List<RowData> yRowDatas = seriesDatas.get(1).getValue();
        int yRowDataSize = yRowDatas.size();

        //更新X坐标缓存
        for (int i = 0; i < xRowDatas.size(); i++) {
            RowData cellValu = xRowDatas.get(i);
            CTStrVal val = xStrCnt > i ? xDataSource.getStrRef().getStrCache().getPtArray(i)
                    : xDataSource.getStrRef().getStrCache().addNewPt();
            val.setIdx(i);
            val.setV(cellValu.valueStr);
        }

        //更新Y坐标缓存
        for (int i = 0; i < yRowDatas.size(); i++) {
            RowData cellValu = yRowDatas.get(i);
            CTNumVal val = yNumCnt > i ? yDataSource.getNumRef().getNumCache().getPtArray(i)
                    : yDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        // 更新对应excel的范围
        xDataSource.getStrRef().setF(
                replaceRowEnd(xDataSource.getStrRef().getF(),
                        xStrCnt,
                        xRowDataSize));
        yDataSource.getNumRef().setF(
                replaceRowEnd(yDataSource.getNumRef().getF(),
                        yNumCnt,
                        yRowDataSize));

        // 删除多的
        if (xStrCnt > xRowDataSize) {
            for (int idx = xRowDataSize; idx < xStrCnt; idx++) {
                xDataSource.getStrRef().getStrCache().removePt(xRowDataSize);
            }
        }
        if (yNumCnt > yRowDataSize) {
            for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
                yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
            }
        }
        // 更新个数
        xDataSource.getStrRef().getStrCache().getPtCount().setVal(xRowDataSize);
        // 更新个数
        yDataSource.getNumRef().getNumCache().getPtCount().setVal(yRowDataSize);
    }

    /**
     * 替换 形如： Sheet1!$A$2:$A$4 的字符
     *
     * @param range
     * @return
     */
    public static String replaceRowEnd(String range, long oldSize, long newSize) {
        Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
        Matcher matcher = pattern.matcher(range);
        if (matcher.find()) {
            long old = Long.parseLong(matcher.group(2));
            return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
        }
        return range;
    }

    /**
     * 获取图表有哪些类型
     * @param part
     */
    public static void getChartType(POIXMLDocumentPart part){
        XWPFChart chart = (XWPFChart) part;
        //根据属性第一列名称切换数据类型
        CTChart ctChart = chart.getCTChart();
        CTPlotArea plotArea = ctChart.getPlotArea();

        if (!plotArea.getBarChartList().isEmpty()) {
            System.out.println("柱状图{"+plotArea.getBarChartList().size()+"}");
        }
        if (!plotArea.getPieChartList().isEmpty()) {
            System.out.println("饼图{"+plotArea.getPieChartList().size()+"}");
        }
        if (!plotArea.getLineChartList().isEmpty()) {
            System.out.println("线形图{"+plotArea.getLineChartList().size()+"}");
        }
        if (!plotArea.getBar3DChartList().isEmpty()) {
            System.out.println("柱状图3D{"+plotArea.getBar3DChartList().size()+"}");
        }
        if (!plotArea.getScatterChartList().isEmpty()) {
            System.out.println("散点图{"+plotArea.getScatterChartList().size()+"}");
        }
        if (!plotArea.getRadarChartList().isEmpty()) {
            System.out.println("雷达图{"+plotArea.getRadarChartList().size()+"}");
        }
    }

}