newcpms/master/src/main/java/com/ruoyi/project/training/controller/TTrainingController.java

package com.ruoyi.project.training.controller;

import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

import com.alibaba.fastjson.JSON;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.DictUtils;
import com.ruoyi.common.utils.file.ExcelUtils;
import com.ruoyi.common.utils.file.FileUploadUtils;
import com.ruoyi.common.utils.poi.CustomXWPFDocument;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.project.plant.domain.TStaffmgr;
import com.ruoyi.project.plant.mapper.TStaffmgrMapper;
import com.ruoyi.project.plant.service.ITStaffmgrService;
import com.ruoyi.project.system.domain.SysDept;
import com.ruoyi.project.system.domain.SysDictData;
import com.ruoyi.project.system.service.ISysDeptService;
import com.ruoyi.project.system.service.ISysDictTypeService;
import com.ruoyi.project.training.domain.TTrainingDevice;
import com.ruoyi.project.training.domain.TTrainingNon;
import com.ruoyi.project.training.domain.TTrainingRegular;
import com.ruoyi.project.training.mapper.TTrainingMapper;
import com.ruoyi.project.training.service.ITTrainingDeviceService;
import com.ruoyi.project.training.service.ITTrainingNonService;
import com.ruoyi.project.training.service.ITTrainingRegularService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.ruoyi.framework.aspectj.lang.annotation.Log;
import com.ruoyi.framework.aspectj.lang.enums.BusinessType;
import com.ruoyi.project.training.domain.TTraining;
import com.ruoyi.project.training.service.ITTrainingService;
import com.ruoyi.framework.web.controller.BaseController;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.framework.web.page.TableDataInfo;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * 培训计划Controller
 *
 * @author ruoyi
 * @date 2020-12-02
 */
@RestController
@RequestMapping("/training/training")
public class TTrainingController extends BaseController {
    @Autowired
    private ITTrainingService tTrainingService;

    @Autowired
    private ITTrainingRegularService tTrainingRegularService;

    @Autowired
    private ISysDeptService iSysDeptService;

    @Autowired
    private ISysDictTypeService iSysDictTypeService;

    @Autowired
    private ITTrainingNonService tTrainingNonService;

    @Resource
    private TTrainingMapper tTrainingMapper;
    @Resource
    private TStaffmgrMapper tStaffmgrMapper;
    @Autowired
    private ITStaffmgrService tStaffmgrService;

    @Autowired
    private ITTrainingDeviceService tTrainingDeviceService;

    /**
     * 查询培训计划列表
     */
    @PreAuthorize("@ss.hasPermi('training:training:list')")
    @GetMapping("/list")
    public TableDataInfo list(TTraining tTraining) {
        startPage();
        List<TTraining> list = tTrainingService.selectTTrainingList(tTraining);
        logger.info("====1111111::::::" + list.size());
        for (TTraining t : list) {
            int mustTraining = 0;
            if (t.getDesignatedStaff() != null) {
                String[] designatedStaff = t.getDesignatedStaff().split(",");
                mustTraining = designatedStaff.length;
            }
            t.setMustTraining(t.getPositionNum() + mustTraining);
            TTrainingNon tTrainingNon = new TTrainingNon();
            tTrainingNon.setTrainingId(t.getId());
            List<TTrainingNon> tTrainingNons = tTrainingNonService.selectTTrainingNonList(tTrainingNon);

            int i = 0;
            for (TTrainingNon n : tTrainingNons) {
                if (n.getTrainingDate() == null && n.getTrainingStaff() == null) {
                    i++;
                    logger.info("nons:" + n);
                }
            }
            t.setHaveTraining(t.getPositionNum() + mustTraining - i);
        }
        return getDataTable(list);
    }

    /**
     * 导出培训计划列表
     */
    @PreAuthorize("@ss.hasPermi('training:training:export')")
    @Log(title = "培训计划", businessType = BusinessType.EXPORT)
    @GetMapping("/export")
    public AjaxResult export(TTraining tTraining) {
        List<TTraining> list = tTrainingService.selectTTrainingList(tTraining);
        ExcelUtil<TTraining> util = new ExcelUtil<TTraining>(TTraining.class);
        return util.exportExcel(list, "training");
    }

    /**
     * 获取培训计划详细信息
     */
    @PreAuthorize("@ss.hasPermi('training:training:query')")
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id) {
        return AjaxResult.success(tTrainingService.selectTTrainingById(id));
    }

    /**
     * 新增培训计划
     */
    @PreAuthorize("@ss.hasPermi('training:training:add')")
    @Log(title = "培训计划", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody TTraining tTraining) {
        tTraining.setCreaterCode(getUserId().toString());
        int doInsert = tTrainingService.insertTTraining(tTraining);
        //培训岗位人员
        if (tTraining.getPosition() != null) {
            TStaffmgr s = new TStaffmgr();
            s.setActualposts(tTraining.getPosition());
            List<TStaffmgr> trainingStaff = tStaffmgrService.selectAllTStaffmgrList(s);
            try {
                //判断离职
                if (!org.apache.commons.lang.StringUtils.isEmpty(tTraining.getYear())) {
                    Iterator<TStaffmgr> iterator = trainingStaff.iterator();
                    while (iterator.hasNext()) {
                        TStaffmgr t = iterator.next();
                        if (t.getLeftDate() != null && t.getDelFlag() == 9) {
                            if (t.getLeftDate().getTime() - tTraining.getStartDate().getTime() < 0l) {
                                logger.debug(t.getName() + "离职时间小于培训时间");
                                iterator.remove();
                            }
                        }
                    }
                }
            } catch (Exception e) {
                logger.error(e.toString());
            }
            for (TStaffmgr staff : trainingStaff) {
                TTrainingDevice tTrainingDevice = new TTrainingDevice();
                tTrainingDevice.setStaffId(staff.getStaffid());
                tTrainingDevice.setRegularId(tTraining.getId());
                tTrainingDevice.setStartDate(tTraining.getCourseStartdate());
                tTrainingDevice.setSupplementary("0");
                tTrainingDeviceService.insertTTrainingDevice(tTrainingDevice);
            }
        }
        if (tTraining.getDesignatedStaff() != null) {
            String[] designated = tTraining.getDesignatedStaff().split(",");
            for (String staffid : designated) {
                TStaffmgr staffmgr = tStaffmgrService.selectTStaffmgrByStaffId(staffid);
                TTrainingDevice tTrainingDevice = new TTrainingDevice();
                tTrainingDevice.setStaffId(staffmgr.getStaffid());
                tTrainingDevice.setRegularId(tTraining.getId());
                tTrainingDevice.setStartDate(tTraining.getCourseStartdate());
                tTrainingDevice.setSupplementary("0");
                tTrainingDeviceService.insertTTrainingDevice(tTrainingDevice);
            }
        }
        return toAjax(doInsert);
    }

    /**
     * 修改培训计划
     */
    @PreAuthorize("@ss.hasPermi('training:training:edit')")
    @Log(title = "培训计划", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody TTraining tTraining) {
        tTraining.setUpdaterCode(getUserId().toString());
        tTraining.setUpdatedate(new Date());
        //查找跟这条培训计划相关联的装置级培训记录   修改其培训的年份，课程名称和课程代码
        //如果是定期类 就修改下培训计划里面的内容
        if (tTraining.getTrainingType().equals("10")) {
            if (tTraining.getRegularId() == null) {
                logger.info("没有对应培训计划");
            } else {
                TTrainingRegular t = tTrainingRegularService.selectTTrainingRegularById(tTraining.getRegularId());
                t.setItem(tTraining.getCourse());
                t.setCourseCode(tTraining.getCourseid());
                t.setYear(tTraining.getYear());
                t.setDelFlag(tTraining.getDelFlag());
                int result = tTrainingRegularService.updateTTrainingRegular(t);
            }
        }
        TTraining oldTraining = tTrainingService.selectTTrainingById(tTraining.getId());
        int i = 0;
        if (oldTraining.getPosition() != null) {
            if (!oldTraining.getPosition().equals(tTraining.getPosition())) {
                i = 1;
            }
        } else {
            if (tTraining.getPosition() != null) {
                i = 1;
            }
        }
        if (oldTraining.getDesignatedStaff() != null) {
            if (!oldTraining.getDesignatedStaff().equals(tTraining.getDesignatedStaff())) {
                i = 1;
            }
        } else {
            if (tTraining.getDesignatedStaff() != null) {
                i = 1;
            }
        }
        if (tTraining.getCourseStartdate() != null) {
            logger.info("修改了时间");
            i = 1;
        }
        if (i == 1) {
            logger.info("修改了需参培人员名单");
            //如果存在条目
            if (oldTraining.getPosition() != null || oldTraining.getDesignatedStaff() != null) {
                TTrainingDevice tTrainingDevice = new TTrainingDevice();
                tTrainingDevice.setRegularId(tTraining.getId());
                tTrainingDeviceService.deleteTTrainingDevice(tTrainingDevice);
            }
            //参培岗位人员
            if (tTraining.getPosition() != null) {
                TStaffmgr s = new TStaffmgr();
                s.setActualposts(tTraining.getPosition());
                List<TStaffmgr> trainingStaff = tStaffmgrService.selectAllTStaffmgrList(s);
                try {
                    //判断离职
                    if (!org.apache.commons.lang.StringUtils.isEmpty(tTraining.getYear())) {
                        Iterator<TStaffmgr> iterator = trainingStaff.iterator();
                        while (iterator.hasNext()) {
                            TStaffmgr t = iterator.next();
                            if (t.getLeftDate() != null && t.getDelFlag() == 9) {
                                if (t.getLeftDate().getTime() - tTraining.getStartDate().getTime() < 0l) {
                                    logger.debug(t.getName() + "离职时间小于培训时间");
                                    iterator.remove();
                                }
                            }
                        }
                    }
                } catch (Exception e) {
                    logger.error(e.toString());
                }
                for (TStaffmgr staff : trainingStaff) {
                    TTrainingDevice tTrainingDevice = new TTrainingDevice();
                    tTrainingDevice.setStaffId(staff.getStaffid());
                    tTrainingDevice.setRegularId(tTraining.getId());
                    tTrainingDevice.setStartDate(tTraining.getCourseStartdate());
                    tTrainingDevice.setSupplementary("0");
                    tTrainingDeviceService.insertTTrainingDevice(tTrainingDevice);
                }
            }
            if (tTraining.getDesignatedStaff() != null) {
                String[] designated = tTraining.getDesignatedStaff().split(",");
                for (String staffid : designated) {
                    TStaffmgr staffmgr = tStaffmgrService.selectTStaffmgrByStaffId(staffid);
                    TTrainingDevice tTrainingDevice = new TTrainingDevice();
                    tTrainingDevice.setStaffId(staffmgr.getStaffid());
                    tTrainingDevice.setRegularId(tTraining.getId());
                    tTrainingDevice.setStartDate(tTraining.getCourseStartdate());
                    tTrainingDevice.setSupplementary("0");
                    tTrainingDeviceService.insertTTrainingDevice(tTrainingDevice);
                }
            }
        }
        return toAjax(tTrainingService.updateTTraining(tTraining));
    }

    /**
     * 删除培训计划
     */
    @PreAuthorize("@ss.hasPermi('training:training:remove')")
    @Log(title = "培训计划", businessType = BusinessType.DELETE)
    @DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids) {
        for (Long id : ids) {
            TTraining training = tTrainingService.selectTTrainingById(id);
            if (training.getPosition() != null || training.getDesignatedStaff() != null) {
                TTrainingDevice tTrainingDevice = new TTrainingDevice();
                tTrainingDevice.setRegularId(id);
                tTrainingDeviceService.deleteTTrainingDevice(tTrainingDevice);
            }
        }
        return toAjax(tTrainingService.deleteTTrainingByIds(ids));
    }

    /**
     * 装置程序清单附件上传
     */
    @Log(title = "培训计划图片上传", businessType = BusinessType.UPDATE)
    @PostMapping("/uploadFile")
    public AjaxResult uploadFile(@RequestParam("file") MultipartFile file) throws IOException {
        if (!file.isEmpty()) {
            String avatar = FileUploadUtils.upload(RuoYiConfig.getFilePath("/trainingMOC"), file);
            return AjaxResult.success(avatar);
        }
        return AjaxResult.error("上传图片异常，请联系管理员");
    }

    /**
     * 批量导入培训成绩
     */
    @PreAuthorize("@ss.hasPermi('training:trainingrecords:add')")
    @Log(title = "培训成绩", businessType = BusinessType.INSERT)
    @PostMapping("/importData")
    public AjaxResult importData(@RequestParam("file") MultipartFile file) throws IOException {
        //获取操作人员ID
        Long userId = getUserId();
        //报错行数统计
        List<Integer> failRow = new ArrayList<Integer>();
        Workbook workbook = ExcelUtils.getWorkBook(file);
        Sheet sheet = workbook.getSheetAt(0);
        List<TTraining> list = new ArrayList<TTraining>();
        //字典查询
        List<SysDictData> plant = iSysDictTypeService.selectDictDataByType("PLANT_DIVIDE");
        List<SysDictData> training = iSysDictTypeService.selectDictDataByType("ACTUALPOST");
        List<SysDictData> yesno = iSysDictTypeService.selectDictDataByType("YES_NO_EN");
        List<SysDictData> staffUnit = iSysDictTypeService.selectDictDataByType("STAFF_UNIT");
        List<SysDictData> trainingType = iSysDictTypeService.selectDictDataByType("TRAINING_TYPE");

        //部门查询
        List<SysDept> dept = iSysDeptService.selectDeptList(new SysDept());
        int rowNum = sheet.getPhysicalNumberOfRows();
        int failNumber = 0;
        for (int i = 1; i < rowNum; i++) {
            try {
                logger.info("读取行数：" + i);
                Row row = sheet.getRow(i);
                int cellNum = row.getPhysicalNumberOfCells();
                TTraining entity = new TTraining();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    //  cell.setCellType(CellType.STRING);
                    String cellValue = ExcelUtils.getCellValue(cell);
                    logger.info("cellValue:" + cellValue);
                    if (j == 0) {
                        if (cellValue != "") {
                            entity.setYear(cellValue);//年份
                        }
                    } else if (j == 1) {
                        entity.setCourse(cellValue);//培训课程
                    } else if (j == 2) {
                        for (SysDictData p : trainingType) {
                            if (p.getDictLabel().equals(cellValue)) {
                                entity.setTrainingType(p.getDictValue());
                            }
                        }//培训类型
                    } else if (j == 3) {
                        entity.setContent(cellValue);//培训内容
                    } else if (j == 4) {
                        entity.setCourseid(cellValue);//课程代码
                    } else if (j == 5) {
                        entity.setPeriod(cellValue);//培训周期
                    } else if (j == 6) {
                        if (cellValue != "") {
                            entity.setDuration(cellValue);//课时
                        }
                    } else if (j == 7) {
                        entity.setTrainer(cellValue);//培训人
                    } else if (j == 8) {
                        String[] units = cellValue.split(",");
                        String unit = "";
                        for (int num = 0; num < units.length; num++) {
                            for (SysDictData p : staffUnit) {
                                if (p.getDictLabel().equals(units[num].trim())) {
                                    if (num == 0) {
                                        unit = p.getDictValue();
                                    } else {
                                        unit = unit + "," + p.getDictValue();
                                    }
                                }
                                entity.setUnit(unit);//培训部门
                            }
                        }
                    } else if (j == 9) {
                        String[] positionName = cellValue.split(",");
                        String position = null;
                        for (int num = 0; num < positionName.length; num++) {
                            for (SysDictData p : training) {
                                if (p.getDictLabel().equals(positionName[num].trim())) {
                                    if (num == 0) {
                                        position = p.getDictValue();
                                    } else {
                                        position = position + "," + p.getDictValue();
                                    }
                                }
                                entity.setPosition(position);//培训岗位
                            }
                        }
                    } else if (j == 10) {
                        if (cellValue.length() > 3) {
                            entity.setCourseStartdate(new SimpleDateFormat(DateUtils.getDateFormat(cellValue)).parse(cellValue));//培训开始日期
                        }
                    } else if (j == 11) {
                        if (cellValue.length() > 3) {
                            entity.setCourseEnddate(new SimpleDateFormat(DateUtils.getDateFormat(cellValue)).parse(cellValue));//培训结束日期
                        }
                    } else if (j == 12) {
                        for (SysDictData p : yesno) {
                            if (p.getDictLabel().equals(cellValue)) {
                                entity.setIsfinish(Long.parseLong(p.getDictValue()));//是否完成
                            }
                        }
                    } else if (j == 13) {
                        for (SysDept d : dept) {
                            if (d.getDeptName().equals(cellValue)) {
                                entity.setDeptId(d.getDeptId());//部门编号
                            }
                        }
                    } else if (j == 14) {
                        entity.setRemarks(cellValue);//备注
                    }
                }
                entity.setCreaterCode(userId.toString());
                logger.info("entity:" + entity);
                list.add(entity);
            } catch (Exception e) {
                failNumber++;
                failRow.add(i + 1);
            }
        }
        int successNumber = 0;
        int failNum = 0;
        for (TTraining t : list
        ) {
            failNum++;
            try {
                tTrainingService.insertTTraining(t);
                successNumber++;
            } catch (Exception e) {
                failNumber++;
                failRow.add(failNum + 1);
            }
        }
        logger.info("list:" + JSON.toJSONString(list));
        logger.info("successNumber:" + String.valueOf(successNumber));
        logger.info("failNumber:" + String.valueOf(failNumber));
        logger.info("failRow:" + String.valueOf(failRow));
        return AjaxResult.success(String.valueOf(successNumber), failRow);
    }

    /**
     * 生成报告
     */
    @RequestMapping(value = "/trainingReport", produces = "application/json;charset=utf-8")
    @ResponseBody
    public void AutoReport(HttpServletRequest request, HttpServletResponse response, Long year, Long trainingId) {
        TTraining tTraining = tTrainingMapper.selectTTrainingById(trainingId);
        String tempUrl = "";
        if (tTraining.getDeptId() == 103 || tTraining.getDeptId() == 10012) {
            if (tTraining.getDeptId() == 10012) {
                tempUrl = "static/word/training/signCBPS.docx"; // 模板文件
            }
            else if (tTraining.getTrainingType().equals("12")) {
                tempUrl = "static/word/training/signMOC.docx"; // 模板文件
            } else {
                tempUrl = "static/word/training/sign.docx"; // 模板文件
            }
            // 保存结果文
            InputStream is = null;
            try {
                is = Thread.currentThread().getContextClassLoader().getResourceAsStream(tempUrl);
                CustomXWPFDocument doc = new CustomXWPFDocument(is);
                // 替换word模板数据
                replaceAll(doc, year, trainingId);

                response.setContentType("application/octet-stream");// 下载,默认就是下载
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Content-Disposition",
                        "attachment;fileName=" + URLEncoder.encode(tTraining.getCourse() + ".docx", "UTF-8"));
                // 刷新缓冲
                response.flushBuffer();
                OutputStream ouputStream = response.getOutputStream();
                doc.write(ouputStream);
                ouputStream.flush();
                ouputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                tempUrl = "static/word/training/signBYX.xlsx"; // 模板文件
                InputStream is = null;
                is = Thread.currentThread().getContextClassLoader().getResourceAsStream(tempUrl);
                XSSFWorkbook wb1 = new XSSFWorkbook(is);
                SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 1000);
                // 替换excel模板数据
                replaceExcel(wb, trainingId);
                response.setContentType("application/octet-stream");// 下载,默认就是下载
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Content-Disposition",
                        "attachment;fileName=" + URLEncoder.encode(tTraining.getCourse() + ".xlsx", "UTF-8"));
                // 刷新缓冲
                response.flushBuffer();
                OutputStream ouputStream = response.getOutputStream();
                wb.write(ouputStream);
                ouputStream.flush();
                ouputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

    }



    //苯乙烯 Excel 渲染
    private void replaceExcel(SXSSFWorkbook wb, Long trainingId) {
        Sheet sheet = wb.getSheet("CTAM");
        TTraining tTraining = tTrainingMapper.selectTTrainingById(trainingId);
        TStaffmgr tStaffmgr = new TStaffmgr();
        tStaffmgr.setActualposts(tTraining.getPosition());
        tStaffmgr.setDeptId(tTraining.getDeptId());
        List<TStaffmgr> staffmgrList = tStaffmgrMapper.selectAllTStaffmgrList(tStaffmgr);
        List<SysDictData> dictList = iSysDictTypeService.selectDictDataByType("STAFF_UNIT");
        try {
            //判断离职
            if (!org.apache.commons.lang.StringUtils.isEmpty(tTraining.getYear())) {
                Iterator<TStaffmgr> iterator = staffmgrList.iterator();
                while (iterator.hasNext()) {
                    TStaffmgr t = iterator.next();
                    if (t.getLeftDate() != null && t.getDelFlag() == 9) {
                        if (t.getLeftDate().getTime() - tTraining.getStartDate().getTime() < 0l) {
                            iterator.remove();
                        }
                    }
                }
            }
        } catch (Exception e) {
        }
        List<TStaffmgr> staffmgrs = new ArrayList<TStaffmgr>();
        if (tTraining.getDesignatedStaff() != null) { //判断指定人员
            String[] staffIds = tTraining.getDesignatedStaff().split(",");
            for (int i = 0; i < staffIds.length; i++) {
                TStaffmgr staffmgr = tStaffmgrMapper.selectTStaffmgrByStaffId(staffIds[i]);
                staffmgrs.add(staffmgr);
            }
            staffmgrList.addAll(staffmgrs);
        }
        replaceSheet(wb,"CTAM",staffmgrList);
        replaceSheet(wb,"CTMM",staffmgrList);
        replaceSheet(wb,"CTSL",staffmgrList);
        replaceSheet(wb,"白班",staffmgrList);
        replaceSheet(wb,"班组",staffmgrList);
    }

    //苯乙烯 Excel 渲染
    private void replaceSheet(SXSSFWorkbook wb,String sheetName,List<TStaffmgr> staffmgrList) {
        List<SysDictData> dictList = iSysDictTypeService.selectDictDataByType("STAFF_UNIT");
        Sheet sheet = wb.getSheet(sheetName);
        List<TStaffmgr> staffmgrs = new ArrayList<>();
        if ("CTAM".equals(sheetName)) {
            for (TStaffmgr t: staffmgrList
            ) {
                if ( t.getUnit().equals("22")){
                    staffmgrs.add(t);
                }
            }
        }
        if ("CTMM".equals(sheetName)) {
            for (TStaffmgr t: staffmgrList
            ) {
                if ( t.getUnit().equals("24")){
                    staffmgrs.add(t);
                }
            }
        }
        if ("CTSL".equals(sheetName)) {
            for (TStaffmgr t: staffmgrList
            ) {
                if ( t.getUnit().equals("16")){
                    staffmgrs.add(t);
                }
            }
        }
        if ("白班".equals(sheetName)) {
            for (TStaffmgr t: staffmgrList
            ) {
                if ( t.getTeam().equals("18") && t.getUnit().equals("20")){
                    staffmgrs.add(t);
                }
            }
        }
        if ("班组".equals(sheetName)) {
            for (TStaffmgr t: staffmgrList
            ) {
                if ( t.getTeam().equals("10") ||t.getTeam().equals("12") ||t.getTeam().equals("14") ||t.getTeam().equals("16") ){
                    staffmgrs.add(t);
                }
            }
        }
        //设置style
        CellStyle cellStyle = wb.createCellStyle();
        //设置水平对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        //设置上边框线条颜色
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框线条颜色
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置下边框线条颜色
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框线条颜色
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //字体
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 11);
        cellStyle.setFont(headerFont);
        int rowIndex = 7;//初始行数
        for (int i = 0; i < staffmgrs.size(); i++) { //加载签名数据
            Row row = sheet.createRow(rowIndex);
            row.setHeight((short) 400);
            row.createCell(0).setCellValue(i + 1);
            row.getCell(0).setCellStyle(cellStyle);
            row.createCell(1).setCellValue(DictUtils.getDictVale(dictList, staffmgrs.get(i).getUnit()));
            row.getCell(1).setCellStyle(cellStyle);
            row.createCell(2).setCellValue(staffmgrs.get(i).getStaffid());
            row.getCell(2).setCellStyle(cellStyle);
            row.createCell(3).setCellValue(staffmgrs.get(i).getName());
            row.getCell(3).setCellStyle(cellStyle);
            row.createCell(4).setCellStyle(cellStyle);
            row.createCell(5).setCellStyle(cellStyle);
            if (i + 1 < staffmgrs.size()) {
                row.createCell(6).setCellValue(i + 2);
                row.getCell(6).setCellStyle(cellStyle);
                row.createCell(7).setCellValue(DictUtils.getDictVale(dictList, staffmgrs.get(i + 1).getUnit()));
                row.getCell(7).setCellStyle(cellStyle);
                row.createCell(8).setCellValue(staffmgrs.get(i + 1).getStaffid());
                row.getCell(8).setCellStyle(cellStyle);
                row.createCell(9).setCellValue(staffmgrs.get(i + 1).getName());
                row.getCell(9).setCellStyle(cellStyle);
                row.createCell(10).setCellStyle(cellStyle);
                row.createCell(11).setCellStyle(cellStyle);
            }
            i++;
            rowIndex++;
        }
        sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex, rowIndex, 0, 1));
        sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex, rowIndex, 2, 11));
        // 最后一行
        Row rowLast = sheet.createRow(rowIndex);
        rowLast.setHeight((short) 3000);
        rowLast.createCell(0).setCellValue("培训有效性");
        rowLast.getCell(0).setCellStyle(cellStyle);
        //设置style
        CellStyle lastStyle = wb.createCellStyle();
        //设置水平对齐方式
        lastStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐方式
        lastStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
        lastStyle.setBorderBottom(BorderStyle.THIN); //下边框
        lastStyle.setBorderLeft(BorderStyle.THIN);//左边框
        lastStyle.setBorderTop(BorderStyle.THIN);//上边框
        lastStyle.setBorderRight(BorderStyle.THIN);//右边框
        //设置上边框线条颜色
        lastStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框线条颜色
        lastStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置下边框线条颜色
        lastStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框线条颜色
        lastStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        lastStyle.setFont(headerFont);
        rowLast.createCell(2).setCellValue("                                                                                                                                                          签名：");
        rowLast.getCell(2).setCellStyle(lastStyle);
        rowLast.createCell(1).setCellStyle(cellStyle);
        rowLast.createCell(3).setCellStyle(cellStyle);
        rowLast.createCell(4).setCellStyle(cellStyle);
        rowLast.createCell(5).setCellStyle(cellStyle);
        rowLast.createCell(6).setCellStyle(cellStyle);
        rowLast.createCell(7).setCellStyle(cellStyle);
        rowLast.createCell(8).setCellStyle(cellStyle);
        rowLast.createCell(9).setCellStyle(cellStyle);
        rowLast.createCell(10).setCellStyle(cellStyle);
        rowLast.createCell(11).setCellStyle(cellStyle);

    }

    /**
     * @Description: 替换段落和表格
     */
    private void replaceAll(CustomXWPFDocument doc, Long year, Long meetingId) throws Exception {
        doParagraphs(doc, meetingId);// 处理段落文字数据，包括文字和表格、图片
    }

    /**
     * 处理段落文字数据，包括文字和表格、图片
     *
     * @param doc 一个文档包含多个段落，一个段落包含多个Runs，一个Runs包含多个Run，Run是文档的最小单元
     */
    private void doParagraphs(CustomXWPFDocument doc, Long trainingId) throws IOException, InvalidFormatException {
        // 文本数据
        Calendar cal = Calendar.getInstance();
        Map<String, String> textMap = new HashMap<String, String>();
        TTraining tTraining = tTrainingService.selectTTrainingById(trainingId);

        textMap.put("{{course}}", "        " + String.valueOf(tTraining.getCourse()));
        //
        /**
         * 替换表格中指定的文字
         */
        Iterator<XWPFTable> itTable = doc.getTablesIterator();
        int i = 1;
        while (itTable.hasNext()) {
            XWPFTable table = (XWPFTable) itTable.next();
            changeTableMessage(textMap, table, false, null, i, doc, tTraining);
            i++;
        }
        /** ----------------------------处理段落------------------------------------ **/
        List<XWPFParagraph> paragraphList = doc.getParagraphs();
        if (paragraphList != null && paragraphList.size() > 0) {
            for (XWPFParagraph paragraph : paragraphList) {
                List<XWPFRun> runs = paragraph.getRuns();
                for (XWPFRun run : runs) {
                    String text = run.getText(0);
                    logger.info("text======" + text);
                    if (text != null) {
                        // 替换文本信息
                        String tempText = text;
                        String key = tempText.replaceAll("\\{\\{", "").replaceAll("}}", "");
                        if (!StringUtils.isEmpty(textMap.get(key))) {
                            run.setText(textMap.get(key), 0);
                        }

                        /**
                         * ----------------------------替换表格数据
                         * ------------------------------------
                         **/
                        doJLParagraphs(doc, paragraph, run, tempText, trainingId);// 处理段落文字数据，包括文字和表格、图片
                    }
                }
            }
        }

    }

    private void changeTableMessage(Map<String, String> textMap, XWPFTable table, boolean isBold, Integer fontSize, int num, CustomXWPFDocument doc, TTraining tTraining) throws IOException, InvalidFormatException {
        int count = table.getNumberOfRows();//获取table的行数
        //插入图片测试
        XWPFTableRow rowTest = table.getRow(0);

        for (int i = 0; i < count; i++) {
            XWPFTableRow row = table.getRow(i);
            List<XWPFTableCell> cells = row.getTableCells();
            for (XWPFTableCell cell : cells) {//遍历每行的值并进行替换
                logger.info(cell.getText() + ":::::");
                for (Map.Entry<String, String> e : textMap.entrySet()) {
                    if (cell.getText().equals("${picture1}")) {
                        cell.removeParagraph(0);
                        //拆入图片
                        if (tTraining.getPicUrl() != null) {
                            String[] picList = tTraining.getPicUrl().split(",");
                            logger.info(tTraining.getPicUrl());
                            logger.info(JSON.toJSONString(picList));
                            for (int j = 0; j < picList.length; j++) {
                                XWPFParagraph newPara = new XWPFParagraph(cell.getCTTc().addNewP(), cell);
                                cell.addParagraph(newPara);
                                Map<String, Object> header = new HashMap<String, Object>();
                                header.put("width", 500);
                                header.put("height", 500);
                                if (picList[j].endsWith("jpg")) {
                                    header.put("type", "jpg");
                                } else if (picList[j].endsWith("png")) {
                                    header.put("type", "png");
                                }
                                header.put("content", RuoYiConfig.getProfile() + picList[j].replace("/profile", ""));
                                String imgUrl = (String) ((Map<?, ?>) header).get("content");
                                String type = (String) ((Map<?, ?>) header).get("type");
                                int width = (Integer) ((Map<?, ?>) header).get("width");
                                int height = (Integer) ((Map<?, ?>) header).get("height");
                                String blipId = doc.addPictureData(new FileInputStream(new File(imgUrl)), getPictureType(type));
                                doc.createPicture(blipId, doc.getNextPicNameNumber(getPictureType(type)), width, height, newPara);
                            }
                        }

                    } else if (cell.getText().equals(e.getKey())) {
                        XWPFParagraph newPara = new XWPFParagraph(cell.getCTTc().addNewP(), cell);
                        XWPFRun r1 = newPara.createRun();
                        if (num == 1) {
                            r1.setFontSize(10);
                            r1.setTextPosition(10);
                            r1.setBold(true);
                        } else if (num == 2) {
                            r1.setFontSize(13);
                            r1.setTextPosition(10);
                        }
                        r1.setText(e.getValue());
                        cell.removeParagraph(0);
                        cell.setParagraph(newPara);
                    }
                }
            }
        }
    }

    /**
     * 处理段落文字--经理
     *
     * @param doc
     * @throws InvalidFormatException
     * @throws FileNotFoundException
     * @throws IOException
     */
    public void doJLParagraphs(CustomXWPFDocument doc, XWPFParagraph paragraph, XWPFRun run, String text, Long meetingId) {
        // 执行表
        if (text.contains("${table1}")) {
            tTrainingService.dealTraininglist(doc, paragraph, run, meetingId);
        }
    }

    /**
     * 根据图片类型，取得对应的图片类型代码
     *
     * @param picType
     * @return int
     */
    private static int getPictureType(String picType) {
        int res = CustomXWPFDocument.PICTURE_TYPE_PICT;
        if (picType != null) {
            if (picType.equalsIgnoreCase("png")) {
                res = CustomXWPFDocument.PICTURE_TYPE_PNG;
            } else if (picType.equalsIgnoreCase("dib")) {
                res = CustomXWPFDocument.PICTURE_TYPE_DIB;
            } else if (picType.equalsIgnoreCase("emf")) {
                res = CustomXWPFDocument.PICTURE_TYPE_EMF;
            } else if (picType.equalsIgnoreCase("jpg") || picType.equalsIgnoreCase("jpeg")) {
                res = CustomXWPFDocument.PICTURE_TYPE_JPEG;
            } else if (picType.equalsIgnoreCase("wmf")) {
                res = CustomXWPFDocument.PICTURE_TYPE_WMF;
            }
        }
        return res;
    }

}