增加空行判断

林致杰

2022-08-02 54aecc07ca143841e66e25dc587bd2629fa0ba8f

 src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java

@@ -1,6 +1,7 @@
package com.ots.project.tool.report.MAQTR.chart;

import com.alibaba.fastjson.JSON;
import com.ots.common.enums.CapabilityEnum;
import com.ots.common.utils.StringUtils;
import com.ots.project.tool.report.MAQ.base.RowData;
import com.ots.project.tool.report.MAQ.base.SeriesData;
@@ -13,8 +14,7 @@
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.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.io.*;
@@ -33,15 +33,16 @@

    public static void main(String[] args) throws IOException {
        try {
/*            OutputStream os = new FileOutputStream("C:\\Users\\大头\\Desktop\\MAQ组别报告\\林_PAQ_IA_CN1.docx");
            XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\大头\\Desktop\\MAQ组别报告\\终稿【0607中文版】MAQ组别对比报告.docx"));
            OutputStream os = new FileOutputStream("C:\\Users\\大头\\Desktop\\MAQ组别报告\\林_PAQ_IA_CN2.docx");
            XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\大头\\Desktop\\MAQ组别报告\\终稿【0607中文版】MAQ组别对比报告(1).docx"));

            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\MAQ组别导入数据模板.xlsx";
            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\MAQ组别导入数据模板 (2).xlsx";
            //变更图表数据
            changeMAQTRChart(document,fileName);
            document.write(os);*/
            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\组别对比报告测试数据.xlsx";
            replaceContent(fileName);
            document.write(os);
/*            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\组别对比报告测试数据.xlsx";
            //replaceContent(fileName);
            initDataNew(fileName,1);*/
        }catch (Exception e){
            e.printStackTrace();
        }
@@ -51,7 +52,9 @@
    /**
     * 计算数据并生成模板
     */
    public static void replaceContent(String fileName){
    public static Map<String,Map<Integer,Double>> replaceContent(String fileName){
        //平均值集合
        Map<String,Map<Integer,Double>> averageMap = new HashMap<>();
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
            Map<String,Map<Integer,List<Double>>> dataMap = new HashMap<>();
@@ -59,19 +62,26 @@
                XSSFSheet sheet = wb.getSheetAt(i);
                String companyName = sheet.getRow(1).getCell(0).getStringCellValue();
                Map<Integer,List<Double>> map = new HashMap<>();
                //统计人数
                int person = 0;
                //行
                for (int j = 1; j < sheet.getLastRowNum(); j++) {
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    XSSFRow row = sheet.getRow(j);
                    if(row == null){
                        break;
                    }
                    //列
                    for (int k = 1; k < row.getLastCellNum(); k++) {
                        XSSFCell cell = row.getCell(k);
/*
                        if (CellType.NUMERIC == cell.getCellType()) {
                            System.out.println(cell.getNumericCellValue());
                        } else if (CellType.STRING == cell.getCellType()) {
                            System.out.println(cell.getStringCellValue());
                        if(cell == null){
                            break;
                        }
*/
                        //table1 多个答题结果处理
                        //不是分数 忽略
                        switch (cell.getCellType()) {
                            case STRING:
                                break;
                        }
                        //归类个性
                        if(map.get(k) != null){
                            map.get(k).add(cell.getNumericCellValue());
                        }else {
@@ -80,14 +90,167 @@
                            map.put(k,list);
                        }
                    }
                    person++;
                }

                //设置人数
                List<Double> list = new ArrayList<>();
                list.add(Double.valueOf(person));
                map.put(999,list);

                //设置不同团队的数据
                dataMap.put(companyName,map);
            }
            System.out.println(dataMap);
            //平均值集合
            for (Map.Entry<String, Map<Integer, List<Double>>> map : dataMap.entrySet()) {
                //33个个性集合值
                Map<Integer,List<Double>> valMap = map.getValue();
                //统计个性平均值
                Map<Integer,Double> doubleMap = new HashMap<>();
                for (Map.Entry<Integer, List<Double>> val : valMap.entrySet()) {
                    List<Double> doubleList = val.getValue();
                    //计算平均值
                    double average = doubleList.stream().mapToDouble(e -> e).average().orElse(0.00);
                    doubleMap.put(val.getKey(),average);
                }
                averageMap.put(map.getKey(),doubleMap);
            }
            //System.out.println(averageMap);
        }catch (Exception e){
            e.printStackTrace();
            log.error("计算平均值出错!");
        }
        return averageMap;
    }

    /**
     * 初始化报告数据
     * @param fileName 数据源文件名
     * @param index 模板sheet索引
     * @return
     */
    public static List<SeriesData> initDataNew(String fileName,Integer index){
        Map<String,Map<Integer,Double>> dataMap = replaceContent(fileName);
        List<SeriesData> seriesDatas = new ArrayList<>();
        if(dataMap != null){
            int oneIndex = 22;
            int twoIndex = 33;
            //1-22个性
            if(index == 0){
                //个性 [第一行]
                SeriesData title = new SeriesData();
                String name = "个性";
                title.setName(name);
                List<RowData> rowDataList = new ArrayList<>();
                for (int i = oneIndex; i > 0; i--) {
                    RowData rowData = new RowData();
                    rowData.setName(name);
                    rowData.setValueStr(CapabilityEnum.codeOf(i));
                    rowDataList.add(rowData);
                }
                title.setValue(rowDataList);
                seriesDatas.add(title);
                //动态值
                for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
                    SeriesData valSeriesData = new SeriesData();
                    Map<Integer,Double> valMap = map.getValue();
                    valSeriesData.setName(map.getKey()+"（n="+ valMap.get(999).intValue() +"）");
                    List<RowData> valRowDataList = new ArrayList<>();
                    for (int i = oneIndex; i > 0; i--) {
                        RowData rowData = new RowData();
                        rowData.setName(map.getKey());
                        rowData.setValue(valMap.get(i));
                        valRowDataList.add(rowData);
                    }
                    valSeriesData.setValue(valRowDataList);
                    seriesDatas.add(valSeriesData);
                }
                //固定辅助列
                String auxiliary = "辅助";
                SeriesData auxiliaryTitle = new SeriesData();
                auxiliaryTitle.setName(auxiliary);
                List<RowData> auxiliaryRowDataList = new ArrayList<>();
                for (int i = 1; i <= oneIndex; i++) {
                    RowData rowData = new RowData();
                    rowData.setName(auxiliary);
                    rowData.setValue(i);
                    auxiliaryRowDataList.add(rowData);
                }
                auxiliaryTitle.setValue(auxiliaryRowDataList);
                seriesDatas.add(auxiliaryTitle);
                //坐标线
                String blank = " ";
                SeriesData blankTitle = new SeriesData();
                blankTitle.setName(blank);
                List<RowData> blankTitleRowDataList = new ArrayList<>();
                for (int i = 1; i <= oneIndex; i++) {
                    RowData rowData = new RowData();
                    rowData.setName(blank);
                    rowData.setValue(0);
                    blankTitleRowDataList.add(rowData);
                }
                blankTitle.setValue(blankTitleRowDataList);
                seriesDatas.add(blankTitle);
            }
            //23-33个性
            if(index == 1){
                //个性 [第一行]
                SeriesData title = new SeriesData();
                String name = "个性";
                title.setName(name);
                List<RowData> rowDataList = new ArrayList<>();
                for (int i = twoIndex; i > oneIndex; i--) {
                    RowData rowData = new RowData();
                    rowData.setName(name);
                    rowData.setValueStr(CapabilityEnum.codeOf(i));
                    rowDataList.add(rowData);
                }
                title.setValue(rowDataList);
                seriesDatas.add(title);
                //动态值
                for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
                    SeriesData valSeriesData = new SeriesData();
                    Map<Integer,Double> valMap = map.getValue();
                    valSeriesData.setName(map.getKey()+"（n="+valMap.get(999).intValue()+"）");
                    List<RowData> valRowDataList = new ArrayList<>();
                    for (int i = twoIndex; i > oneIndex; i--) {
                        RowData rowData = new RowData();
                        rowData.setName(map.getKey());
                        rowData.setValue(valMap.get(i));
                        valRowDataList.add(rowData);
                    }
                    valSeriesData.setValue(valRowDataList);
                    seriesDatas.add(valSeriesData);
                }
                //固定辅助列
                String auxiliary = "辅助";
                SeriesData auxiliaryTitle = new SeriesData();
                auxiliaryTitle.setName(auxiliary);
                List<RowData> auxiliaryRowDataList = new ArrayList<>();
                for (int i = 1; i <= (twoIndex-oneIndex); i++) {
                    RowData rowData = new RowData();
                    rowData.setName(auxiliary);
                    rowData.setValue(i);
                    auxiliaryRowDataList.add(rowData);
                }
                auxiliaryTitle.setValue(auxiliaryRowDataList);
                seriesDatas.add(auxiliaryTitle);
                //坐标线
                String blank = " ";
                SeriesData blankTitle = new SeriesData();
                blankTitle.setName(blank);
                List<RowData> blankTitleRowDataList = new ArrayList<>();
                for (int i = 1; i <= (twoIndex-oneIndex); i++) {
                    RowData rowData = new RowData();
                    rowData.setName(blank);
                    rowData.setValue(0);
                    blankTitleRowDataList.add(rowData);
                }
                blankTitle.setValue(blankTitleRowDataList);
                seriesDatas.add(blankTitle);
            }
        }
        return seriesDatas;
    }

    /**
@@ -200,7 +363,9 @@
                        index = 1;
                    }
                    //获取模板数据
                    List<SeriesData> seriesDatas = initData(fileName,index);
                    List<SeriesData> seriesDatas = initDataNew(fileName,index);
                    //List<SeriesData> seriesDatas = initData(fileName,index);
                    //log.error(JSON.toJSONString(seriesDatas));
                    //数据长度
                    int size = seriesDatas.size();
                    // 查看里面的图表数据，才能知道是什么图表
@@ -242,7 +407,7 @@
                            }

                            CTScatterSer ser = scatterChart.getSerList().get(i);
                            updateScatterChart(scatterDatas, ser.getXVal(), ser.getYVal());
                            updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
                        }
                    }
                }
@@ -347,6 +512,9 @@
            //log.error("lastRowNum:{},size:{}",lastRowNum,size);
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    if(sheet.getRow(idx) == null){
                        continue;
                    }
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
@@ -361,9 +529,15 @@
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    protected static void updateScatterChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
    protected static void updateScatterChart(List<SeriesData> seriesDatas,CTSerTx serTitle, CTAxDataSource xDataSource,
                                             CTNumDataSource yDataSource) {

        // 更新系列标题
        if(StringUtils.isNotEmpty(seriesDatas.get(0).getName())){
            serTitle.getStrRef().setF(serTitle.getStrRef().getF());
            serTitle.getStrRef().getStrCache().getPtArray(0).setV(seriesDatas.get(0).getName());
        }

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