图表替换数值

zhijie

2023-10-16 e80006ac27c3370e9a4dc02f28898d3380c57993

图表替换数值

 src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java

@@ -1,14 +1,22 @@
package com.ots.project.tool.report.LAQ.chart;

import com.alibaba.fastjson.JSON;
import com.ots.common.enums.CapabilityEnum;
import com.ots.common.utils.DateUtils;
import com.ots.common.utils.MapDataUtil;
import com.ots.common.utils.StringUtils;
import com.ots.common.utils.poi.ExcelUtil;
import com.ots.common.utils.poi.WordUtil;
import com.ots.framework.config.EssConfig;
import com.ots.project.exam.domain.TReportTemplate;
import com.ots.project.tool.PdfUtil;
import com.ots.project.tool.report.LAQ.LAQTemplate;
import com.ots.project.tool.report.MAQ.base.RowData;
import com.ots.project.tool.report.MAQ.base.SeriesData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
@@ -19,10 +27,8 @@
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -36,401 +42,281 @@
public class LAQChart {


    public static void main(String[] args) throws IOException {
        try {
//            OutputStream os = new FileOutputStream("D:\\测评系统\\需求\\2023\\5.领导潜力报告\\report\\LAQ英文报告-20230926.docx");
//            XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\2023\\5.领导潜力报告\\report\\LAQ英文报告-20230926.docx"));
//
//            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\MAQ组别导入数据模板 -3组(1).xlsx";
//            //变更图表数据
//            changeMAQTRChart(document,fileName);
//            document.write(os);
    public static void main(String[] args) throws Exception {
        String dataPath = "D:\\测评系统\\需求\\LAQ\\report\\LAQ表头数据文件.xlsx";
        // 获取导入数据
        File file = new File(dataPath);
        InputStream in = new FileInputStream(file);
        ExcelUtil<LAQTemplate> util = new ExcelUtil<>(LAQTemplate.class);
        List<LAQTemplate> laqTemplateList = util.importExcel(in);
        // 生成文件集合
        List<String> fileNameList = new ArrayList<>();
        List<String> deleteStrList = new ArrayList<>();
        for (int i = 0; i < laqTemplateList.size(); i++) {
            LAQTemplate laqTemplate = laqTemplateList.get(i);
            //组装报告所用数据
            Map<String, Object> textMap = new HashMap<>();
            textMap.put("sendEmailFileName",laqTemplate.getName());
            textMap.put("nowDate", DateUtils.getDate());
            MapDataUtil.objectToMap(laqTemplate,textMap);

            OutputStream os = new FileOutputStream("D:\\测评系统\\需求\\2023\\5.领导潜力报告\\report\\LAQ英文报告-111.docx");
//            PdfUtil.convertPDF("D:\\测评系统\\需求\\2023\\5.领导潜力报告\\report\\LAQ英文报告-20230926.docx");
            XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\2023\\5.领导潜力报告\\report\\LAQ英文报告-20230926.docx"));

//            TReportTemplate template = null;
//            String zipFileName = WordUtil.makeReportFile("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文报告-20230926.docx", template, textMap, new HashMap<>(), deleteStrList);
//            fileNameList.add(zipFileName);
            ZipSecureFile.setMinInflateRatio(0.001);
            try (OutputStream os = new FileOutputStream("D:\\测评系统\\需求\\LAQ\\report\\LAQ.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文报告-20230926.docx"))){
                Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
                changeChart(document,seriesDatas,textMap);
            document.write(os);

/*            String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\组别对比报告测试数据.xlsx";
            //replaceContent(fileName);
            initDataNew(fileName,1);*/
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 计算数据并生成模板
     */
    public static Map<String,Map<Integer,Double>> replaceContent(String fileName){
        //平均值集合
        Map<String,Map<Integer,Double>> averageMap = new TreeMap<>();
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
            Map<String,Map<Integer,List<Double>>> dataMap = new TreeMap<>();
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                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++) {
                    XSSFRow row = sheet.getRow(j);
                    if(isEmptyRow(row)){
                        break;
            log.info("删除的文件名:{}", JSON.toJSONString(deleteStrList));
            WordUtil.deleteFileByStr(deleteStrList);
                    }
                    //列
                    for (int k = 1; k < row.getLastCellNum(); k++) {
                        XSSFCell cell = row.getCell(k);
                        if(cell == null){
                            break;
                        }
                        //不是分数 忽略
                        Double cellValue = null;
                        try {
                            switch (cell.getCellType()) {
                                case STRING:
                                    cellValue = Double.valueOf(cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    cellValue = cell.getNumericCellValue();
                                    break;
                            }
                        }catch (Exception e){
                            log.error("获取分数失败,{}"+e.getMessage());
                            e.printStackTrace();
                        }

                        //归类个性
                        if(map.get(k) != null){
                            map.get(k).add(cellValue);
                        }else {
                            List<Double> list = new ArrayList<>();
                            list.add(cellValue);
                            map.put(k,list);
                        }
                    }
                    person++;
                }

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

                //设置不同团队的数据
                dataMap.put(companyName,map);
            }
            //平均值集合
            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索引
     * @param textMap
     * @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;
    public static Map<Integer,List<SeriesData>> initData(Map<String, Object> textMap){
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        //获取命中数值
        BigDecimal olas = (BigDecimal) textMap.get("olas");
        BigDecimal ca = (BigDecimal) textMap.get("ca");
        BigDecimal bla = (BigDecimal) textMap.get("bla");
        BigDecimal nr = (BigDecimal) textMap.get("nr");
        BigDecimal ar = (BigDecimal) textMap.get("ar");
        BigDecimal open = (BigDecimal) textMap.get("open");
        BigDecimal cons = (BigDecimal) textMap.get("cons");
        BigDecimal extra = (BigDecimal) textMap.get("extra");
        BigDecimal agree = (BigDecimal) textMap.get("agree");
        BigDecimal emosta = (BigDecimal) textMap.get("emosta");

        // 图表1
        List<SeriesData> seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"类别 1")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", olas.doubleValue())
                ))
        );
        seriesDataMap.put(1,seriesDatas);

        // 图表2
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Behavioral Leadership Alignment"),
                        new RowData(" ", 0,"Cognitive Ability")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", bla.doubleValue()),
                        new RowData("系列 1", ca.doubleValue())
                ))
        );
        seriesDataMap.put(2,seriesDatas);

        // 图表3
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Abstract Reasoning"),
                        new RowData(" ", 0,"Numeric Reasoning")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", ar.doubleValue()),
                        new RowData("系列 1", nr.doubleValue())
                ))
        );
        seriesDataMap.put(3,seriesDatas);

        // 图表4
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"类别 1")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", bla.doubleValue())
                ))
        );
        seriesDataMap.put(4,seriesDatas);

        // 图表5
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(5,seriesDatas);

        // 图表6
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(6,seriesDatas);

        // 图表7
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(7,seriesDatas);

        // 图表8
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(8,seriesDatas);

        // 图表9
        seriesDatas = Arrays.asList(
                new SeriesData(" ", Arrays.asList(
                        new RowData(" ", 0,"Emotional Stability"),
                        new RowData(" ", 0,"Agreeableness"),
                        new RowData(" ", 0,"Extraversion"),
                        new RowData(" ", 0,"Conscientiousness"),
                        new RowData(" ", 0,"Openness to Experience")
                )),
                new SeriesData("系列 1", Arrays.asList(
                        new RowData("系列 1", emosta.doubleValue()),
                        new RowData("系列 1", agree.doubleValue()),
                        new RowData("系列 1", extra.doubleValue()),
                        new RowData("系列 1", cons.doubleValue()),
                        new RowData("系列 1", open.doubleValue())
                ))
        );
        seriesDataMap.put(9,seriesDatas);

        // 图表10
        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", olas.doubleValue())
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", 44)
                ))
        );
        seriesDataMap.put(10,seriesDatas);

        return seriesDataMap;
    }

    /**
     * 初始化报告数据
     * @param fileName 数据源文件名
     * @param index 模板sheet索引
     * @return
     */
    public static List<SeriesData> initData(String fileName,Integer index){
        List<SeriesData> seriesDatas = new ArrayList<>();
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
            XSSFSheet sheet1 = wb.getSheetAt(index);

            //数据处理  先获取表头长度  遍历表头行数、一一对应放进表头集合里
            Map<Integer,SeriesData> map = new HashMap<>();
            XSSFRow row = sheet1.getRow(0);
            for (int i = 0; i < row.getLastCellNum(); i++) {
                //为空忽略
                /*if(StringUtils.isEmpty(getName(row.getCell(i)))){
                    continue;
                }*/
                SeriesData seriesData = new SeriesData(getName(row.getCell(i)));
                map.put(i,seriesData);
                //读取每一行每一列
                for (int j = 1; j <= sheet1.getLastRowNum(); j++) {
                    //每一行
                    XSSFRow row1 = sheet1.getRow(j);
                    RowData rowData = new RowData();
                    if(seriesData.getValue() == null){
                        List<RowData> rowDatas = new ArrayList<>();
                        //设置表头
                        rowData.setName(getName(row.getCell(i)));
                        //根据类型判断赋值
                        switch (row1.getCell(i).getCellType()) {
                            case STRING:
                                rowData.setValueStr(row1.getCell(i).getStringCellValue());
                                break;
                            case NUMERIC:
                                rowData.setValue(row1.getCell(i).getNumericCellValue());
                                break;
                            default:
                                break;
                        }
                        rowDatas.add(rowData);
                        seriesData.setValue(rowDatas);
                    }else{
                        //设置表头
                        rowData.setName(getName(row.getCell(i)));
                        //根据类型判断赋值
                        switch (row1.getCell(i).getCellType()) {
                            case STRING:
                                rowData.setValueStr(row1.getCell(i).getStringCellValue());
                                break;
                            case NUMERIC:
                                rowData.setValue(row1.getCell(i).getNumericCellValue());
                                break;
                            default:
                                break;
                        }
                        seriesData.getValue().add(rowData);
                    }
                }
                seriesDatas.add(seriesData);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return seriesDatas;
    }

    /**
     * 根据单元格类型取值
     * @return 返回字符值
     */
    private static String getName(XSSFCell cell){
        String name = null;
        switch (cell.getCellType()) {
            case STRING:
                name = cell.getStringCellValue();
                break;
            case NUMERIC:
                name = cell.getNumericCellValue()+"";
                break;
        }
        return name;
    }

    /**
     * 替换MAQTR图表数据
     * 替换LAQ图表数据
     * @param document 文档对象
     * @param fileName 数据源excel
     * @param seriesDataMap
     * @param textMap 数据源
     */
    public static void changeMAQTRChart(XWPFDocument document,String fileName) {
        if(StringUtils.isEmpty(fileName)){
    public static void changeChart(XWPFDocument document, Map<Integer,List<SeriesData>> seriesDataMap, Map<String, Object> textMap) {
        if(StringUtils.isEmpty(textMap)){
            return;
        }
        try {
            List<POIXMLDocumentPart> relations = document.getRelations();
            int barIndex = 1;
            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;
                    String partName = chart.getPackagePart().getPartName().getName();

                    Integer index = 0;
                    if(partName.contains("chart1.xml")){
                        index = 0;
                    }else if(partName.contains("chart2.xml")){
                        index = 1;
                    }
                    //获取模板数据
                    List<SeriesData> seriesDatas = initDataNew(fileName,index);
                    //List<SeriesData> seriesDatas = initData(fileName,index);
                    //log.error(JSON.toJSONString(seriesDatas));
                    //数据长度
                    int size = seriesDatas.size();
                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();
                    getChartType(chart);
                    // excel内置表格
                    XSSFWorkbook workbook = chart.getWorkbook();
                    XSSFSheet sheet = workbook.getSheetAt(0);

                    //散点图
                    if (!plot.getScatterChartList().isEmpty()) {
                        CTScatterChart scatterChart = plot.getScatterChartArray(0);

                        //去除多余数据源
                        int remain = 8 - size;
                        if(remain != 0){
                            for (int j = 1; j <= remain; j++) {
                                int removeSize = scatterChart.getSerList().size() - j - 1;
                                scatterChart.getSerList().remove(removeSize);
                                //删除excel多余列
                                //deleteColumn(sheet,8 - remain);
                            }
                        }

                    //柱形图
                    if (!plot.getBarChartList().isEmpty()) {
                        List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
                        CTBarChart barChart = plot.getBarChartArray(0);
                        //System.out.println("柱形图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        //散点图数据源 XY坐标
                        for (int i = 0; i < scatterChart.getSerList().size(); i++) {
                            //根据图表数据线获取对应数据源  数值是X轴 辅助Y轴
                            List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
                            //最后一个数据源需拿取最后一列0000 作为x轴
                            if((i+1) == size-2){
                                scatterDatas.add(seriesDatas.get(size-1));
                                scatterDatas.add(seriesDatas.get(size-2));
                            }else{
                                scatterDatas.add(seriesDatas.get(i+1));
                                scatterDatas.add(seriesDatas.get(size-2));
                        //柱形图数据源
//                        List<SeriesData> barDatas = seriesDatas.subList(1,4);
                        //因为柱形图CTBarSer没按excel顺序走 调换数据位置保持对应
                        Collections.swap(seriesDatas, 0, 1);
                        int j = 0;
                        for (CTBarSer ser : barChart.getSerList()) {
                            //更新柱形图数据缓存
                            updateBarChart(seriesDatas.get(j), ser.getVal());
                            ++j;
                        }
                        barIndex++;
                            }

                            CTScatterSer ser = scatterChart.getSerList().get(i);
                            updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
                    //散点图
                    if (!plot.getScatterChartList().isEmpty()) {
                        List<SeriesData> seriesDatas = seriesDataMap.get(10);
                        CTScatterChart scatterChart = plot.getScatterChartArray(0);
                        //System.out.println("散点图{"+plot.getScatterChartList().size()+"}");
                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

/*                        //散点图数据源 XY坐标
                        List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
                        scatterDatas.add(seriesDatas.get(0));
                        scatterDatas.add(seriesDatas.get(4));*/
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {
                            //更新散点图缓存
                            updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
                            ++i;
                        }
                    }
                }
@@ -442,72 +328,41 @@
        }
    }

    /**
     * 删除列
     * @param sheet
     * @param columnToDelete
     */
    public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            XSSFRow row = sheet.getRow(r);
            for (int c = columnToDelete; c <= row.getLastCellNum(); c++) {
                XSSFCell cOld = row.getCell(c);
                if (cOld != null) {
                    row.removeCell(cOld);
                }
                XSSFCell cNext = row.getCell(c + 1);
                if (cNext != null) {
                    XSSFCell cNew = row.createCell(c, cNext.getCellType());
                    cloneCell(cNew, cNext);
                    if (r == 0) {
                        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
                    }
                }
            }
        }
    }


    /**
     * 右边列左移,样式值设置
     * @param cNew
     * @param cOld
     * 更新 柱形图 的缓存数据
     *
     * @param seriesDatas   数据
     * @param numDataSource 数据的缓存
     */
    private static void cloneCell(XSSFCell cNew, XSSFCell cOld) {
        cNew.setCellComment(cOld.getCellComment());
        cNew.setCellStyle(cOld.getCellStyle());
        if (CellType.BOOLEAN == cNew.getCellType()) {
            cNew.setCellValue(cOld.getBooleanCellValue());
        } else if (CellType.NUMERIC == cNew.getCellType()) {
            cNew.setCellValue(cOld.getNumericCellValue());
        } else if (CellType.STRING == cNew.getCellType()) {
            cNew.setCellValue(cOld.getStringCellValue());
        } else if (CellType.ERROR == cNew.getCellType()) {
            cNew.setCellValue(cOld.getErrorCellValue());
        } else if (CellType.FORMULA == cNew.getCellType()) {
            cNew.setCellValue(cOld.getCellFormula());
        }
    protected static void updateBarChart(SeriesData seriesDatas, CTNumDataSource numDataSource) {
        //获取柱形图数据数量
        long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
        int dataSize = seriesDatas.value.size();
        for (int i = 0; i < dataSize; i++) {
            RowData cellValu = seriesDatas.value.get(i);
            CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
                    : numDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));

    }

    /**
     * 空行判断
     * @param row
     * @return
     */
    public static boolean isEmptyRow(XSSFRow row){
        if(row == null || row.toString().isEmpty()){
            return  true;
        }else{
            Iterator<Cell> it = row.iterator();
            boolean isEmpty = true;
            while (it.hasNext()){
                Cell cell = it.next();
                if(cell.getCellType() != CellType.BLANK){
                    isEmpty = false;
                    break;
        // 更新对应excel的范围
        numDataSource.getNumRef().setF(
                replaceRowEnd(numDataSource.getNumRef().getF(),
                        ptNumCnt,
                        dataSize));

        // 删除多的
        if (ptNumCnt > dataSize) {
            for (int idx = dataSize; idx < ptNumCnt; idx++) {
                numDataSource.getNumRef().getNumCache().removePt(dataSize);
                }
            }
            return isEmpty;
        }
        // 更新个数
        numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
    }


@@ -575,14 +430,8 @@
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    protected static void updateScatterChart(List<SeriesData> seriesDatas,CTSerTx serTitle, CTAxDataSource xDataSource,
    protected static void updateScatterChart(List<SeriesData> seriesDatas, 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();