feat: 新增第三方接口访问

wzp

2025-08-01 95eb8a2a75cbeebcb3393d404c1952bd59b8989a

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

@@ -1,171 +1,745 @@
package com.ots.project.tool.report.LAQ.chart;

import com.alibaba.fastjson.JSON;
import com.microsoft.schemas.vml.CTTextbox;
import com.ots.common.utils.StringUtils;
import com.ots.common.utils.poi.ExcelUtil;
import com.ots.common.utils.poi.WordUtil;
import com.ots.project.tool.report.LAQ.LAQReport;
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.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.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.awt.*;
import java.io.*;
import java.math.BigInteger;
import java.util.*;


import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class LAQTest {

    //这里是用来测试的
    public static void main(String[] args) throws Exception {
        System.out.println("启动测试..");
//        Map<String, Object> textMap = new HashMap<String, Object>();
//        textMap.put("%boxgrid1%","易紫轩、邵诗悦");
//        textMap.put("%boxgrid2%","叶诗悦");
//        textMap.put("%boxgrid3%","刘晨涵");
//        textMap.put("%boxgrid4%","段国贤");
//        textMap.put("%boxgrid5%","贺瑞辰");
//        textMap.put("%boxgrid6%","赵翔");
//        textMap.put("%boxgrid7%","钱佳毅");
//        textMap.put("%boxgrid8%","卢文轩");
//        textMap.put("%boxgrid9%","唐忠林");


        String dataPath = "D:\\桌面文件\\LAQ全景图报告\\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("olas", "85");
//            textMap.put("ca", "32");
//            textMap.put("bla", "34");
//            textMap.put("nr", "3");
//            textMap.put("ar", "45");
//            textMap.put("open", "67");
//            textMap.put("cons", "12");
//            textMap.put("extra", "48");
//            textMap.put("agree", "90");
//            textMap.put("emosta", "23");
//            textMap.put("perfomance", "60");
//
//        try {
//            FileInputStream fis = new FileInputStream("D:\\桌面文件\\TaiTest.docx");
//            XWPFDocument document = new XWPFDocument(fis);
//
//            List<XWPFParagraph> paragraphs = document.getParagraphs();
//            for (XWPFParagraph paragraph : paragraphs) {
//                for (XWPFRun run : paragraph.getRuns()) {
//                    for (XWPFPicture picture : run.getEmbeddedPictures()) {
//                        if (picture.getCTPicture() != null
//                                && picture.getCTPicture().getSpPr() != null
//                                && picture.getCTPicture().getSpPr().getXfrm() != null
//                                && picture.getCTPicture().getSpPr().getXfrm().getExt() != null) {
//            LAQReport report = new LAQReport();
//
//                            CTPositiveSize2D ext = picture.getCTPicture().getSpPr().getXfrm().getExt();
//                            if (ext != null) {
//                                long widthEmus = ext.getCx();
//                                long heightEmus = ext.getCy();
//
//                                int width = (int) (widthEmus / 9525); // Convert EMUs to points (assuming 1 inch = 9525 EMUs)
//                                int height = (int) (heightEmus / 9525); // Convert EMUs to points (assuming 1 inch = 9525 EMUs)
//
//                                System.out.println("Image Width: " + width + " points");
//                                System.out.println("Image Height: " + height + " points");
//                            }
//                        } else {
//                            System.err.println("Error: Unable to retrieve image width and height.");
//                        }
//                    }
//                }
//            ZipSecureFile.setMinInflateRatio(0.001);
//            try (OutputStream os = new FileOutputStream("D:\\桌面文件\\LAQ英文报告"+UUID.randomUUID().toString()+".docx");
//                 XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\桌面文件\\LAQ英文报告-叠加版本.docx"))) {
//                Map<Integer, List<SeriesData>> seriesDatas = initData(textMap);
//                Map<Integer,String> lableDatas = initLables();
//                changeChart(document, seriesDatas, textMap,lableDatas);
//                document.write(os);
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//
//
//            // 创建一个新的段落
////            XWPFParagraph paragraph = document.createParagraph();
////
////            // 设置段落的左边距和上边距
//////            paragraph.setIndentationLeft(0); // 0最小。7500
//////            paragraph.setSpacingBefore(0); // 设置上边距为0
////            paragraph.setIndentationLeft(7200);//最大8000
////            paragraph.setSpacingBefore(7000);//最大7200
////
////            // 添加文本到段落
////            XWPFRun run = paragraph.createRun();
////            run.setText("吴祝攀");
////
////
////            XWPFParagraph paragraph1 = document.createParagraph();
////            paragraph1.setIndentationLeft(5000);//最大8000
////            paragraph1.setSpacingBefore(5000);//最大7200
////            XWPFRun run1= paragraph1.createRun();
////            run1.setText("吴语晨");
//
//
//            // 创建一个形状作为文本框
//            Shape textBox = new Shape(document, ShapeType.TEXT_BOX);
//
//            // 设置文本框的内容
//            textBox.getTextPath().setText("文本框中的文本");
//
//            // 设置文本框的位置和大小
//            textBox.setWidth(200); // 设置宽度
//            textBox.setHeight(100); // 设置高度
//
//            // 设置文本框的背景透明
//            textBox.setFilled(false); // 设置填充色为透明
//            textBox.setStrokeColor(Color.WHITE); // 设置边框颜色为透明
//
//            // 将文本框添加到文档中
//            doc.getFirstSection().getBody().getFirstParagraph().appendChild(textBox);
//
//
//            // 将文档写入到文件
//            FileOutputStream fos = new FileOutputStream("D:\\桌面文件\\TaiTest_out_"+ UUID.randomUUID().toString() +".docx");
//            document.write(fos);
//
//            // 关闭文件流
//            fis.close();
//            fos.close();
//
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//

//        FileInputStream fis = new FileInputStream("D:\\桌面文件\\TaiTest.docx");
//        Document doc = new Document(fis);
//
//        // 创建文本框
//        Shape textBox = new Shape(doc, ShapeType.TEXT_BOX);
//        textBox.setWidth(200); // 设置文本框宽度
//        textBox.setHeight(50); // 设置文本框高度
//        textBox.setWrapType(WrapType.NONE); // 设置文本框不自动换行
//
//        // 创建文本框的段落和Run对象，并添加内容
//        Paragraph para = new Paragraph(doc);
//        Run run = new Run(doc, "这是文本框中的内容");
//        para.appendChild(run);
//        textBox.appendChild(para);
//
//        // 添加文本框到Word文档
//        doc.getFirstSection().getBody().getFirstParagraph().appendChild(textBox);
//
//        // 保存修改后的Word文档
//        doc.save("D:\\桌面文件\\TaiTest_out_"+ UUID.randomUUID().toString() +".docx");
//

        // 创建一个空的Word文档
//        XWPFDocument document = new XWPFDocument();
//
//        // 创建一个段落
//        XWPFParagraph paragraph = document.createParagraph();
//
//        // 创建一个文本框
//        XWPFRun run = paragraph.createRun();
//        CTTextbox textbox = run.getCTR().addNewTextbox();
//        CTTextboxContent textboxContent = textbox.addNewTextboxContent();
//
//        // 设置文本框的位置和大小
//        textbox.setInset(new BigInteger("0"));
//        textboxContent.setWMode(STTextbox.WMode.WINDOW);
//        textboxContent.setNoBorder(true);
//        textboxContent.setAnchor(STTextAnchoringType.MIDDLE);
//        textboxContent.setAnchorCenter(true);
//
//        // 设置文本框的内容
//        textboxContent.setT("This is a text box.");
//
//        // 保存文档
//        try {
//            FileOutputStream out = new FileOutputStream("example.docx");
//            document.write(out);
//            out.close();
//            System.out.println("Word文档创建成功！");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        ZipSecureFile.setMinInflateRatio(0.001);
        try (OutputStream os = new FileOutputStream("D:\\桌面文件\\可删除\\LAQ英文报告"+UUID.randomUUID().toString()+".docx");
             XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\桌面文件\\LAQ英文报告-叠加版本.docx"))) {
            changeChartNew(document,laqTemplateList);
            document.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }


        System.out.println("处理完成...");

    }


    /**
     * 初始化数据
     * @param list
     * @return
     */
    public static Map<Integer,List<SeriesData>> initNewData(List<LAQTemplate> list)
    {
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        for (int i = 0; i < list.size(); i++)
        {
            LAQTemplate laqTemplate = list.get(i);
            Double olas = Double.valueOf(laqTemplate.getOlas().toString());
            Double perfomance;
            if(laqTemplate.getPerfomance()!= null)
            {
                perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
            }
            else {
                perfomance = 0.0;
            }

            List<SeriesData> seriesDatas = new ArrayList<>();
            seriesDatas = Arrays.asList(
                    new SeriesData("X 值", Arrays.asList(
                            new RowData("X 值", olas)
                    )),
                    new SeriesData("Y 值", Arrays.asList(
                            new RowData("Y 值", perfomance)
                    ))
            );


            Integer box = laqTemplate.getBox();
            if(olas>=66)
            {
                seriesDataMap.put(100+i+1,seriesDatas);
            }
            else if(olas>33&&olas<66)
            {
                seriesDataMap.put(200+i+1,seriesDatas);
            }
            else
            {
                seriesDataMap.put(300+i+1,seriesDatas);
            }
        }

        return  seriesDataMap;
    }

    public static Map<Integer,List<SeriesData>> initData(Map<String, Object> textMap){
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        //获取命中数值
        Double olas = Double.valueOf((String)textMap.get("olas"));
        Double ca = Double.valueOf((String)textMap.get("ca"));
        Double bla = Double.valueOf((String)textMap.get("bla"));
        Double nr = Double.valueOf((String)textMap.get("nr"));
        Double ar = Double.valueOf((String)textMap.get("ar"));
        Double open = Double.valueOf((String)textMap.get("open"));
        Double cons = Double.valueOf((String)textMap.get("cons"));
        Double extra = Double.valueOf((String)textMap.get("extra"));
        Double agree = Double.valueOf((String)textMap.get("agree"));
        Double emosta = Double.valueOf((String)textMap.get("emosta"));
        Double perfomance;
        if(textMap.get("perfomance") != null){
            perfomance = Double.valueOf((String)textMap.get("perfomance"));
        }else{
            perfomance = -1D;
        }


        // 图表1
        List<SeriesData> seriesDatas = new ArrayList<>();
        // 图表1
        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", perfomance.doubleValue() == -1 ? perfomance.doubleValue() : olas.doubleValue())
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", perfomance.doubleValue())
                ))
        );
        seriesDataMap.put(1,seriesDatas);


        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", 95)
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", 87)
                ))
        );
        seriesDataMap.put(2,seriesDatas);


        seriesDatas = Arrays.asList(
                new SeriesData("X 值", Arrays.asList(
                        new RowData("X 值", 96)
                )),
                new SeriesData("Y 值", Arrays.asList(
                        new RowData("Y 值", 90)
                ))
        );
        seriesDataMap.put(3,seriesDatas);



        return seriesDataMap;
    }


    /**
     * 获取指定类型的SeriesData。
     * @param list
     * @param type
     * @return
     */
    public static Map<Integer,List<SeriesData>> getSeriesDataByType(List<LAQTemplate> list,Integer type)
    {
        Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
        int dataIndex =1;
        for (int i = 0; i < list.size(); i++)
        {
            LAQTemplate laqTemplate = list.get(i);
            Double olas = Double.valueOf(laqTemplate.getOlas().toString());
            Double perfomance;
            if(laqTemplate.getPerfomance()!= null)
            {
                perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
            }
            else {
                perfomance = 0.0;
            }

            List<SeriesData> seriesDatas = new ArrayList<>();
            seriesDatas = Arrays.asList(
                    new SeriesData("X 值", Arrays.asList(
                            new RowData("X 值", olas)
                    )),
                    new SeriesData("Y 值", Arrays.asList(
                            new RowData("Y 值", perfomance)
                    ))
            );


            if(type==1)
            {
                if(olas>67)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }

            if(type==2)
            {
                if(olas<=67 && olas>33)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }

            if(type==3)
            {
                if(olas<=33)
                {
                    seriesDataMap.put(dataIndex,seriesDatas);
                    dataIndex++;
                }
            }
        }

        return  seriesDataMap;
    }


    public static Map<Integer,String> getSeriesDataLablesByType(List<LAQTemplate> list,Integer type)
    {
        Map<Integer,String> seriesDataMapLables = new HashMap<>();
        int dataIndex =1;
        for (int i = 0; i < list.size(); i++)
        {
            LAQTemplate laqTemplate = list.get(i);
            Double olas = Double.valueOf(laqTemplate.getOlas().toString());
            Double perfomance;
            if(laqTemplate.getPerfomance()!= null)
            {
                perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
            }
            else {
                perfomance = 0.0;
            }


            String olasStr = olas.toString();
            String perfomanceStr = perfomance.toString();

            // 判断小数部分是否为0
            if (Math.abs(olas - Math.floor(olas)) < 1e-9) {
                // 如果小数部分接近于0，则取整数部分
                olasStr = String.valueOf(olas.intValue());
            }

            if (Math.abs(perfomance - Math.floor(perfomance)) < 1e-9) {
                // 如果小数部分接近于0，则取整数部分
                perfomanceStr = String.valueOf(perfomance.intValue());
            }

            if(type==1)
            {
                if(olas>67)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"("+olasStr+"-"+perfomanceStr+")");
                    dataIndex++;
                }
            }

            if(type==2)
            {
                if(olas<=67 && olas>33)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"("+olasStr+"-"+perfomanceStr+")");
                    dataIndex++;
                }
            }

            if(type==3)
            {
                if(olas<=33)
                {
                    seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"("+olasStr+"-"+perfomanceStr+")");

                    dataIndex++;
                }
            }
        }

        return  seriesDataMapLables;
    }


    public static void changeChartNew(XWPFDocument document,List<LAQTemplate> list) {
        if(list.size()<=0){
            return;
        }
        try {
            //获取数据
            Map<Integer,List<SeriesData>> seriesDataMapTop = getSeriesDataByType(list,1);
            Map<Integer,List<SeriesData>> seriesDataMapMiddle = getSeriesDataByType(list,2);
            Map<Integer,List<SeriesData>> seriesDataMapBottom = getSeriesDataByType(list,3);

            //获取标签
            Map<Integer,String> seriesDataLablesTop = getSeriesDataLablesByType(list,1);
            Map<Integer,String> seriesDataLablesMiddle = getSeriesDataLablesByType(list,2);
            Map<Integer,String> seriesDataLablesBottom = getSeriesDataLablesByType(list,3);

            List<POIXMLDocumentPart> relations = document.getRelations();
            int barIndex = 1;

            //三个图表的个数
            int topIndex =1;
            int middleIndex =1;
            int bottomIndex =1;

            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;
                    // 查看里面的图表数据，才能知道是什么图表
                    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);
                        //第一个散点图1-90，第二个91-180，第三个181-270
                        System.out.println("第"+barIndex+"个散点图");
                        String lables = "";
                        List<SeriesData> seriesDatas = new ArrayList<>();

                        // 获取图表的值轴,并把最小值设置为-0，
                        // chart.getAxes().get(1) 获取的纵坐标，chart.getAxes().get(0) 获取的是横坐标
                        chart.getAxes().get(1).setMinimum(0);
                        Double maximum = chart.getAxes().get(0).getMaximum();

                        Double minimum = chart.getAxes().get(0).getMinimum();
//                        System.out.println("X轴最小值是："+minimum+",最大值是："+maximum);

                        //判断图表所在位置
                        if(minimum>=67)
                        {
                            seriesDatas = seriesDataMapTop.get(topIndex);
                            lables = seriesDataLablesTop.get(topIndex);
                            topIndex++;
//                            System.out.println("第一页图表个数："+topIndex+"，标签："+lables+";"+"X轴最小值是："+minimum+",最大值是："+maximum);
                        }
                        else if(minimum>=33)
                        {
                            seriesDatas = seriesDataMapMiddle.get(middleIndex);
                            lables = seriesDataLablesMiddle.get(middleIndex);
//                            System.out.println("第二页图表个数："+middleIndex);

                            middleIndex++;
                        }
                        else
                        {
                            seriesDatas = seriesDataMapBottom.get(bottomIndex);
                            lables = seriesDataLablesBottom.get(bottomIndex);
                            bottomIndex++;
//                            System.out.println("第三页图表个数："+bottomIndex);
                        }


                        barIndex++;


                        if(seriesDatas==null)
                        {
                            for (CTScatterSer ser : scatterChart.getSerList()) {
                                ser.getTx().getStrRef().getStrCache().getPtArray(0).setV("");
                            }
                            continue;
                        }

                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {
                            //更新散点图缓存
                            updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
                            ser.getTx().getStrRef().getStrCache().getPtArray(0).setV(lables);
                            ++i;
                        }

                    }



                }
            }


            System.out.println("第一页图表个数："+(topIndex-1));
            System.out.println("第二页图表个数："+(middleIndex-1));
            System.out.println("第三页图表个数："+(bottomIndex-1));

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


    public static void changeChart(XWPFDocument document, Map<Integer,List<SeriesData>> seriesDataMap, Map<String, Object> textMap,Map<Integer,String> lableDatas) {
        if(StringUtils.isEmpty(textMap)){
            return;
        }
        try {


            List<POIXMLDocumentPart> relations = document.getRelations();
            int barIndex = 1;
            int chartIndex =1;//散点图表的数量
            for (POIXMLDocumentPart part : relations) {
                if (part instanceof XWPFChart) {
                    // 图表元素
                    XWPFChart chart = (XWPFChart) part;
                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();
                    getChartType(chart);
                    // excel内置表格
                    XSSFWorkbook workbook = chart.getWorkbook();
                    XSSFSheet sheet = workbook.getSheetAt(0);


                    //散点图
                    if (!plot.getScatterChartList().isEmpty()) {

                        if(chartIndex>=1&&chartIndex<=90)
                        {

                        }


                        List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);

                        CTScatterChart scatterChart = plot.getScatterChartArray(0);
                        System.out.println("散点图{"+plot.getScatterChartList().size()+"}");


                        if(seriesDatas==null)
                        {
                            for (CTScatterSer ser : scatterChart.getSerList()) {
                                ser.getTx().getStrRef().getStrCache().getPtArray(0).setV("");
                            }
                            continue;
                        }

                        // 获取图表的值轴,并把最小值设置为-0，
                        // chart.getAxes().get(1) 获取的纵坐标，chart.getAxes().get(0) 获取的是横坐标
                        chart.getAxes().get(1).setMinimum(0);
                        Double maximum = chart.getAxes().get(1).getMaximum();
                        Double minimum = chart.getAxes().get(1).getMinimum();


                        System.out.println("【开始】最大："+maximum+",最小："+minimum);


                        //刷新内置excel数据
                        refreshExcel(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());
                        int i = 0;
                        for (CTScatterSer ser : scatterChart.getSerList()) {

                            //更新散点图缓存
                            updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());

                            String lables = lableDatas.get(barIndex);
                            ser.getTx().getStrRef().getStrCache().getPtArray(0).setV(lables);

//                            updateDLbls(ser);
                            ++i;
                        }

                        barIndex++;
                        chartIndex++;
                    }



                }
            }

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

    /**
     * 更新标签
     * @param ser
     */
    private static void updateDLbls(CTScatterSer ser) {

        // 设置散点的大小
//        CTMarkerSize ctMarkerSize = CTMarkerSize.Factory.newInstance();
//        ctMarkerSize.setVal((short)0);
//        ser.getMarker().setSize(ctMarkerSize);

        ser.getTx().getStrRef().getStrCache().getPtArray(0).setV(UUID.randomUUID().toString());

    }




    /**
     * 更新图表的关联 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{
                    if(cellValu.value != -1){
                        cell.setCellValue(cellValu.value);
                    }else{
                        cell.setCellValue((String) null);
                    }
                }

            }
            //删除多余行数
            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, CTAxDataSource xDataSource,
                                             CTNumDataSource yDataSource) {

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


        System.out.println("[有数据]条数  x:"+xNumCnt+" y:"+yNumCnt);

        //获取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);
            if(cellValu.value == -1){
                val.setV(null);
            }else {
                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);
            if(cellValu.value == -1){
                val.setV(null);
            }else {
                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()+"}");
        }
    }
}