LAQ支持

林致杰

2023-10-10 0f877d7ba62205f94e615eb0ab1950b3350c1436

LAQ支持

 src/main/java/com/ots/common/enums/ReportTypeEnum.java

@@ -5,7 +5,7 @@
public enum ReportTypeEnum {
    Brief("Brief", "简易版"), Complete("Complete", "完整版"), SAQ("SAQ", "SAQ报告"), RuiLin("RuiLin", "睿邻报告"), CAQ("CAQ", "CAQ报告"),
    JAQ("JAQ","JAQ报告"),MAQ("MAQ", "MAQ报告"),MAQV2("MAQV2", "MAQV2报告"),PAQ("PAQ", "PAQ报告"),MAQIAR("MAQIAR", "MAQIAR报告"),
    MAQTR("MAQTR", "MAQTR报告"),CIAQ("CIAQ", "CIAQ报告"),VAQ("VAQ", "VAQ报告");
    MAQTR("MAQTR", "MAQTR报告"),CIAQ("CIAQ", "CIAQ报告"),VAQ("VAQ", "VAQ报告"),LAQ("LAQ", "LAQ报告");
    private final String code;
    private final String message;
    ReportTypeEnum(String code, String message) {

 src/main/java/com/ots/common/utils/MapDataUtil.java

@@ -1,5 +1,8 @@
package com.ots.common.utils;
import cn.hutool.core.collection.CollectionUtil;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
@@ -32,4 +35,28 @@
        }
        return returnMap;
    }

    /**
     *  对传入的对象进行数据清洗，将属性值为null和""的去掉，其他字段名和属性值存入map集合
     *  requestParameters 为要转换为键值对的对象
     *  dynamicPara 转换后的键值对添加到的目标集合
     */
    public static void objectToMap(Object requestParameters,Map<String, Object> dynamicPara) throws IllegalAccessException {
        // 获取f对象对应类中的所有属性域
        Field[] fields = requestParameters.getClass().getDeclaredFields();
        for (Field field : fields) {
            String varName = field.getName();
            // 获取原来的访问控制权限
            boolean accessFlag = field.isAccessible();
            // 修改访问控制权限
            field.setAccessible(true);
            // 获取在对象f中属性fields[i]对应的对象中的变量
            Object obj = field.get(requestParameters);
            if (obj != null && StringUtils.isNotBlank(obj.toString()) || CollectionUtil.isNotEmpty((Iterable<?>) obj)) {
                dynamicPara.put(varName, obj);
                // 恢复访问控制权限
                field.setAccessible(accessFlag);
            }
        }
    }
}

 src/main/java/com/ots/project/tool/report/LAQ/LAQTemplate.java

New file
@@ -0,0 +1,57 @@
package com.ots.project.tool.report.LAQ;

import com.ots.framework.aspectj.lang.annotation.Excel;
import lombok.Data;

import java.math.BigDecimal;

/**
 * @description: LAQ导入模板
 * @author: zhijie
 * @create: 2023-10-10 14:46
 **/
@Data
public class LAQTemplate {

    @Excel(name = "Name")
    private String name;

    @Excel(name = "E-mail")
    private String email;

    @Excel(name = "Scored on")
    private String ScoredOn;

    @Excel(name = "Company")
    private String company;

    @Excel(name = "OLAS")
    private BigDecimal olas;

    @Excel(name = "CA")
    private BigDecimal ca;

    @Excel(name = "BLA")
    private BigDecimal bla;

    @Excel(name = "NR")
    private BigDecimal nr;

    @Excel(name = "AR")
    private BigDecimal ar;

    @Excel(name = "OPEN")
    private BigDecimal open;

    @Excel(name = "CONS")
    private BigDecimal cons;

    @Excel(name = "EXTRA")
    private BigDecimal extra;

    @Excel(name = "AGREE")
    private BigDecimal agree;

    @Excel(name = "EMOSTA")
    private BigDecimal emosta;
}

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

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

import com.ots.common.enums.CapabilityEnum;
import com.ots.common.utils.StringUtils;
import com.ots.framework.config.EssConfig;
import com.ots.project.tool.PdfUtil;
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.ss.usermodel.Cell;
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.FileInputStream;
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: LAQ图表
 * @author: zhijie
 * @create: 2023-10-10 15:01
 **/
@Slf4j
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);

            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"));

            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;
                    }
                    //列
                    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索引
     * @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;
    }

    /**
     * 初始化报告数据
     * @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图表数据
     * @param document 文档对象
     * @param fileName 数据源excel
     */
    public static void changeMAQTRChart(XWPFDocument document,String fileName) {
        if(StringUtils.isEmpty(fileName)){
            return;
        }
        try {
            List<POIXMLDocumentPart> relations = document.getRelations();
            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();
                    // 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);
                            }
                        }

                        //刷新内置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));
                            }

                            CTScatterSer ser = scatterChart.getSerList().get(i);
                            updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
                        }
                    }
                }
            }

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

    /**
     * 删除列
     * @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
     */
    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());
        }
    }

    /**
     * 空行判断
     * @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;
                }
            }
            return isEmpty;
        }
    }


    /**
     * 更新图表的关联 excel
     *
     * @param seriesDatas
     * @param sheet
     */
    protected static void refreshExcel(List<SeriesData> seriesDatas, 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);
                }
                //优先使用字符串值
                if(StringUtils.isNotEmpty(cellValu.valueStr)){
                    cell.setCellValue(cellValu.valueStr);
                }else{
                    cell.setCellValue(cellValu.value);
                }

            }
            //删除多余行数
            int lastRowNum = sheet.getLastRowNum();
            //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));
                }
            }
        }
    }


    /**
     * 更新 散点图 缓存数据
     *
     * @param seriesDatas 数据
     * @param xDataSource X坐标数据缓存
     * @param yDataSource Y坐标数据缓存
     */
    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();

        //获取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);
            CTNumVal val = xNumCnt > i ? xDataSource.getNumRef().getNumCache().getPtArray(i)
                    : xDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.0f", cellValu.value));
        }

        //更新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.getNumRef().setF(
                replaceRowEnd(xDataSource.getNumRef().getF(),
                        xNumCnt,
                        xRowDataSize));
        yDataSource.getNumRef().setF(
                replaceRowEnd(yDataSource.getNumRef().getF(),
                        yNumCnt,
                        yRowDataSize));

        // 删除多的
        if (xNumCnt > xRowDataSize) {
            for (int idx = xRowDataSize; idx < xNumCnt; idx++) {
                xDataSource.getNumRef().getNumCache().removePt(xRowDataSize);
            }
        }
        if (yNumCnt > yRowDataSize) {
            for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
                yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
            }
        }
        // 更新个数
        xDataSource.getNumRef().getNumCache().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()+"}");
        }
    }

}