导出excel部分字体标红,和导出两份sheet

两份sheet  其中学历是代码

package com.guangl.approval.serviceImpl;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.nacos.api.config.annotation.NacosValue;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.github.pagehelper.PageHelper;
import com.google.common.base.Strings;
import com.guangl.approval.constants.CmnConstants;
import com.guangl.approval.dao.DictAreaCodeMapper;
import com.guangl.approval.dao.ExamExaminerMapper;
import com.guangl.approval.entity.*;
import com.guangl.approval.enums.ErrorCodeMsg;
import com.guangl.approval.exception.AttemptException;
import com.guangl.approval.pagehelper.PageDataInfo;
import com.guangl.approval.pojo.BasicPojo.ResponseStruct;
import com.guangl.approval.pojo.feignPojo.sso.GetCodeByGuidParam;
import com.guangl.approval.service.ApprovalOSSOService;
import com.guangl.approval.service.DictCommService;
import com.guangl.approval.service.DictDataService;
import com.guangl.approval.service.ExamExaminerService;
import com.guangl.approval.utils.FastDFSUtils;
import com.guangl.approval.utils.IdcardUtils;
import com.guangl.approval.utils.UUIDGenerator;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Transactional
public class ExamExaminerServiceImpl implements ExamExaminerService {
  private static final Logger logger = LoggerFactory.getLogger(ExamExaminerServiceImpl.class);
  @Autowired private ExamExaminerMapper examExaminerMapper;

  @Autowired private DictDataService dictDataService;

  @Autowired private DictCommService dictCommService;
  // 是否有效：1.有效；2.失效
  @NacosValue(value = "${properties.exam_examiner.is_enabled}", autoRefreshed = true)
  private String idEnable;

  @Autowired private ExamExaminerService examExaminerService;

  @Autowired private ApprovalOSSOService approvalOSSOService;

  @Autowired private DictAreaCodeMapper dictAreaCodeMapper;

  // 附件地址前缀
  @NacosValue(value = "${app.file-server}", autoRefreshed = true)
  private String fileServer;

  /** @Author:王梓宁 @Date 2020/4/14 19:40 新增考官 */
  @Override
  public int addExaminer(ExamExaminerVO examinerVO) {
    examinerVO.setExaminerUuid(UUIDGenerator.generate());
    int insertCount = examExaminerMapper.insertSelective(examinerVO);
    if (insertCount == 0) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "新增考官出错");
    }
    return insertCount;
  }

  /** @Author:王梓宁 @Date 2020/4/14 20:14 修改考官信息 */
  @Override
  public int editExaminer(ExamExaminerVO examinerVO) {

    int editCount = examExaminerMapper.updateByPrimaryKeySelective(examinerVO);
    if (editCount == 0) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "修改考官出错");
    }
    return editCount;
  }

  /** @Author:王梓宁 @Date 2020/4/14 20:31 考官信息详情 */
  @Override
  public ExamExaminer selectDetail(ExamExaminerVO examinerVO) {
    ExamExaminer examExaminer =
        examExaminerMapper.selectByPrimaryKey(examinerVO.getExamExaminerId());
    if (null == examExaminer) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "查询考官详情出错");
    }
    return examExaminer;
  }

  /** @Author:王梓宁 @Date 2020/4/14 20:44 考官信息列表 */
  @Override
  public PageDataInfo selectLike(ExamExaminerVO examinerVO) {
    Example ex = new Example(ExamExaminer.class);
    Example.Criteria criteria = ex.createCriteria();
    if (null != examinerVO.getNameAndCardNum() && "" != examinerVO.getNameAndCardNum()) {
      criteria.andCondition(
          "(examiner_name like '%"
              + examinerVO.getNameAndCardNum()
              + "%' or "
              + "examiner_card_no like '%"
              + examinerVO.getNameAndCardNum()
              + "%')");
    }

    if (null != examinerVO.getExaminerProvince()) {
      criteria.andEqualTo("examinerProvince", examinerVO.getExaminerProvince());
    }
    if (null != examinerVO.getExaminerCity()) {
      criteria.andEqualTo("examinerCity", examinerVO.getExaminerCity());
    }

    List<Map<String, Object>> mapList = new ArrayList<>();
    Map<String, Object> map = null;

    List<ExamExaminer> examExaminerList2 = examExaminerMapper.selectByExample(ex);
    // 校验时间
    for (int i = 0; i < examExaminerList2.size(); i++) {
      Date date = new Date();
      Date endDate = examExaminerList2.get(i).getEndDate();
      int time = endDate.compareTo(date); // 大于返回1  小于返回-1  等于返回0
      if (time == -1) {
        // 调用更改有效/无效接口
        this.editExaminerIsEnable(String.valueOf(examExaminerList2.get(i).getExamExaminerId()));
      }
    }
    PageHelper.startPage(examinerVO.getPageNum(), examinerVO.getPageSize());
    List<ExamExaminer> examExaminerList = examExaminerMapper.selectByExample(ex);
    if (0 != examExaminerList.size()) {
      for (int i = 0; i < examExaminerList.size(); i++) {
        map = new HashMap<>();
        map.put("examinerName", examExaminerList.get(i).getExaminerName());
        List<DictDataVO> dictDataVOList = dictDataService.getSex();
        for (int k = 0; k < dictDataVOList.size(); k++) {
          if (dictDataVOList.get(k).getCode().equals(examExaminerList.get(i).getExaminerSex())) {
            map.put("examinerSex", dictDataVOList.get(k).getName());
          }
        }
        map.put("examinerCardNo", examExaminerList.get(i).getExaminerCardNo());
        map.put("examExaminerId", examExaminerList.get(i).getExamExaminerId());
        map.put(
            "examinerEducation",
            dictCommService.getDictNameByType(
                "degree_type", examExaminerList.get(i).getExaminerEducation()));
        map.put("examinerCity", examExaminerList.get(i).getExaminerCity());
        map.put("examinerCertNo", examExaminerList.get(i).getExaminerCertNo());
        try {
          Map<String, Object> objectMap = parseMap(idEnable);
          map.put("idEnable", objectMap.get(examExaminerList.get(i).getIsEnabled().toString()));
        } catch (Exception e) {
          e.printStackTrace();
        }
        mapList.add(map);
      }
    }

    return new PageDataInfo(mapList, examExaminerList);
  }

  @Override
  public List<Map<String, Object>> examinerList() {
    Example ex = new Example(ExamExaminer.class);
    Example.Criteria criteria = ex.createCriteria();
    Example.Criteria criteria2 = ex.createCriteria();
    List<Map<String, Object>> mapList = new ArrayList<>();
    Map<String, Object> map = null;
    List<ExamExaminer> examExaminerList = examExaminerMapper.selectByExample(ex);

    if (examExaminerList.size() == 0) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "未查询到考官列表信息");
    }
    for (int i = 0; i < examExaminerList.size(); i++) {
      map = new HashMap<>();
      map.put("examinerName", examExaminerList.get(i).getExaminerName());
      List<DictDataVO> dictDataVOList = dictDataService.getSex();
      for (int k = 0; k < dictDataVOList.size(); k++) {
        if (dictDataVOList.get(k).getCode().equals(examExaminerList.get(i).getExaminerSex())) {
          map.put("examinerSex", dictDataVOList.get(k).getName());
        }
      }
      map.put("examinerCardNo", examExaminerList.get(i).getExaminerCardNo());
      map.put(
          "examinerEducation",
          dictCommService.getDictNameByType(
              "degree_type", examExaminerList.get(i).getExaminerEducation()));
      map.put("examinerCity", examExaminerList.get(i).getExaminerCity());
      map.put("examinerCertNo", examExaminerList.get(i).getExaminerCertNo());
      try {
        Map<String, Object> objectMap = parseMap(idEnable);
        map.put("idEnable", objectMap.get(examExaminerList.get(i).getIsEnabled().toString()));
      } catch (Exception e) {
        e.printStackTrace();
      }
      mapList.add(map);
    }
    return mapList;
  }

  /** @Author:王梓宁 @Date 2020/4/15 9:14 理论考官下拉接口 */
  @Override
  public List<DictDataVO> upExaminer(Long guid, Integer isCity) {
    this.checkTime(guid, isCity);
    Example ex = new Example(ExamExaminer.class);
    Example.Criteria criteria = ex.createCriteria();
    // 获取省市
    GetCodeByGuidParam getCodeByGuidParam = new GetCodeByGuidParam();
    getCodeByGuidParam.setGuid(guid);
    ResponseStruct responseStruct = approvalOSSOService.getCodeByGuidParam(getCodeByGuidParam);
    if (null == responseStruct) {
      throw new AttemptException(ErrorCodeMsg.SERVER_ERROR, "未能通过Guid获取省市区");
    }
    Map<String, String> map = (Map<String, String>) responseStruct.getData();
    // 省市权限
    if ("1".equals(isCity.toString())) {
      criteria.andEqualTo("examinerProvince", map.get("provinceCode"));
    }
    if ("2".equals(isCity.toString())) {
      criteria.andEqualTo("examinerCity", map.get("cityCode"));
    }

    // 是否有效：1、有效；2、无效
    criteria.andEqualTo("isEnabled", CmnConstants.IS_ENABLED_YES);
    // 是否删除：1、未删除；2、已删除
    criteria.andEqualTo("isDeleted", CmnConstants.IS_DELETED_NO);
    List<ExamExaminer> examExaminerList = examExaminerMapper.selectByExample(ex);
    List<DictDataVO> arrayList = new ArrayList<>();
    if (null == examExaminerList) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "upExaminer查询考官出错");
    }
    for (int i = 0; i < examExaminerList.size(); i++) {
      DictDataVO dictDataVO = new DictDataVO();
      dictDataVO.setCode(String.valueOf(examExaminerList.get(i).getExamExaminerId()));
      dictDataVO.setName(examExaminerList.get(i).getExaminerName());
      arrayList.add(dictDataVO);
    }
    return arrayList;
  }

  /** @Author:王梓宁 @Date 2020/4/15 14:23 学历下拉列表接口 */
  @Override
  public List<DictDataVO> upEducation() {
    List<DictDataVO> dataVOList = dictCommService.getDictByType("degree_type");
    return dataVOList;
  }

  /** @Author:王梓宁 @Date 2020/4/17 9:40 校验考官有效期 此方法已废弃，融入到考官查询里面 */
  @Override
  public int checkTime(Long guid, Integer isCity) {
    Example ex = new Example(ExamExaminer.class);
    Example.Criteria criteria = ex.createCriteria();
    // 获取省市
    GetCodeByGuidParam getCodeByGuidParam = new GetCodeByGuidParam();
    getCodeByGuidParam.setGuid(guid);
    ResponseStruct responseStruct = approvalOSSOService.getCodeByGuidParam(getCodeByGuidParam);
    if (null == responseStruct) {
      throw new AttemptException(ErrorCodeMsg.SERVER_ERROR, "未能通过Guid获取省市区");
    }
    Map<String, String> map = (Map<String, String>) responseStruct.getData();
    // 省市权限
    if ("1".equals(isCity.toString())) {
      criteria.andEqualTo("examinerProvince", map.get("provinceCode"));
    }
    if ("2".equals(isCity.toString())) {
      criteria.andEqualTo("examinerCity", map.get("cityCode"));
    }
    List<ExamExaminer> examExaminerList = examExaminerMapper.selectByExample(ex);
    if (0 == examExaminerList.size()) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "checkTime未查询到考官列表");
    }
    for (int i = 0; i < examExaminerList.size(); i++) {
      Date date = new Date();
      Date endDate = examExaminerList.get(i).getEndDate();
      int time = endDate.compareTo(date); // 大于返回1  小于返回-1  等于返回0
      if (time == -1) {
        // 调用更改有效/无效接口
        this.editExaminerIsEnable(String.valueOf(examExaminerList.get(i).getExamExaminerId()));
      }
    }
    return 0;
  }

  /** @Author:王梓宁 @Date 2020/4/17 10:00 修改考官状态接口 */
  @Override
  public int editExaminerIsEnable(String examExaminerId) {
    ExamExaminer examExaminer = new ExamExaminer();
    examExaminer.setExamExaminerId(Integer.valueOf(examExaminerId));
    examExaminer.setIsEnabled(Byte.valueOf("2"));
    int editExaminerIsEnableCount = examExaminerMapper.updateByPrimaryKeySelective(examExaminer);
    if (0 == editExaminerIsEnableCount) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "editExaminerIsEnabled更新考官有效状态出错");
    }
    return editExaminerIsEnableCount;
  }

  /** @Author:王梓宁 @Date 2020/4/17 10:46 导出考官数据接口 */
  @Override
  public Map<String, Object> getDownloadExaminer(ExamExaminerVO examExaminerVO) {
    Map<String, Object> param = new HashMap<>();
    String clzName = "考官列表";
    // 导出数据
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 声明一个表格
    HSSFSheet sheet = workbook.createSheet("考官列表");
    // 设置表格默认宽度
    sheet.setDefaultColumnWidth((short) 30);
    // 需要导出的文件名字
    String fileName = clzName + ".xls";
    int rowNum = 1;
    // 导出Excel 表格头部
    String[] header = {"姓名", "性别", "身份证号", "学历", "所在地", "考官证号", "是否有效"};
    HSSFRow row = sheet.createRow(0);
    // 在sheet中添加表头
    for (int i = 0; i < header.length; i++) {
      HSSFCell cell = row.createCell(i);
      HSSFRichTextString text = new HSSFRichTextString(header[i]);
      cell.setCellValue(text);
    }
    HSSFRow row1 = null;
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    List<Map<String, Object>> mapList = this.examinerList();
    for (int i = 0; i < mapList.size(); i++) {
      row1 = sheet.createRow(rowNum);
      Map<String, Object> objectMap = mapList.get(i);
      row1.createCell(0).setCellValue((String) objectMap.get("examinerName"));
      row1.createCell(1).setCellValue((String) objectMap.get("examinerSex"));
      row1.createCell(2).setCellValue((String) objectMap.get("examinerCardNo"));
      row1.createCell(3).setCellValue((String) objectMap.get("examinerEducation"));
      row1.createCell(4).setCellValue(String.valueOf(objectMap.get("examinerCity")));
      row1.createCell(5).setCellValue(String.valueOf(objectMap.get("examinerCertNo")));
      row1.createCell(6).setCellValue((String) objectMap.get("idEnable"));
      rowNum++;
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    try {
      workbook.write(os);
    } catch (IOException e) {
      e.printStackTrace();
    }
    os.toByteArray();
    try {
      workbook.close();
    } catch (IOException e) {
      e.printStackTrace();
    }
    String httpUrl = null;
    String filePath = null;
    String url = null;
    try {
      url = FastDFSUtils.getDoUpload(examExaminerVO.getSessionToke(), fileName, os.toByteArray());
    } catch (Exception e) {
      errLog("getDownloadAllStaffWorkers", ErrorCodeMsg.UNKNOWN_ERROR.getMessage(), "获取文件服务下载地址为空");
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "获取文件服务下载地址为空");
    }
    if (StringUtils.isNotBlank(url)) {
      JSONObject jsonObject = JSONObject.parseObject(url);
      String data = jsonObject.getString("data");
      JSONObject dataObject = JSONObject.parseObject(data);
      httpUrl = dataObject.getString("httpUrl");
      filePath = dataObject.getString("filePath");
      if (StringUtils.isEmpty(filePath)) {
        errLog(
            "getDownloadTrainStuEnroll", ErrorCodeMsg.UNKNOWN_ERROR.getMessage(), "获取文件服务下载地址为空2");
        throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "获取文件服务下载地址为空2");
      }
    }
    Map<String, Object> map = new HashMap<>();
    map.put("url", fileServer + "/" + filePath);
    map.put("attname", fileName);
    return map;
  }

  /** @Author:王梓宁 @Date 2020/4/17 14:49 考官数据导入 */
  @Override
  @Transactional(rollbackFor = Exception.class)
  public Map<String, String> importAllExaminers(
      List<ExamExaminerParams> allExamExaminerVOList, Long guid) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    Map<String, Integer> mapOrgNum = new HashMap<String, Integer>();
    if (null == allExamExaminerVOList || allExamExaminerVOList.isEmpty()) {
      errLog("importAllExaminers", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "Excel为空");
      throw new AttemptException(ErrorCodeMsg.PARAMS_ISNULL_ERROR, "Excel为空");
    }
    int successNum = 0;
    int failureNum = 0;
    StringBuilder successMsg = new StringBuilder();
    StringBuilder failureMsg = new StringBuilder();
    for (int i = 0; i < allExamExaminerVOList.size(); i++) {
      ExamExaminerParams examExaminerParams = allExamExaminerVOList.get(i);
      if (examExaminerParams == null) {
        continue;
      }
      try {
        try {
          validImportStuInfo(examExaminerParams);
        } catch (RuntimeException e) {
          failureNum++;
          failureMsg.append("<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： " + e.getMessage());
          errLog("importAllStaffWorkers", ErrorCodeMsg.PARAM_ERROR.getMessage(), "参数错误");
          continue;
        }
        // 考官查重
        String cardNo = examExaminerParams.getExaminerCardNo();
        String examinerName = examExaminerParams.getExaminerName();
        List<ExamExaminer> examExaminerList = examExaminerService.checkExaminerByCardNo(cardNo);
        if (0 != examExaminerList.size()) {
          failureNum++;
          failureMsg.append(
              "<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： 考官" + examinerName + "已存在");
          continue;
        }
        ExamExaminer examExaminer = new ExamExaminer();
        examExaminer.setExaminerName(examExaminerParams.getExaminerName());
        examExaminer.setExaminerCardNo(examExaminerParams.getExaminerCardNo());
        Integer workerSex = null;
        if ("男".equals(examExaminerParams.getExaminerSex())) {
          workerSex = 1;
        } else if ("女".equals(examExaminerParams.getExaminerSex())) {
          workerSex = 2;
        }
        examExaminer.setExaminerSex(String.valueOf(workerSex));
        examExaminer.setExaminerUuid(UUIDGenerator.generate());
        examExaminer.setExaminerCertNo(examExaminerParams.getExaminerCertNo());
        Byte isEnabled = null;
        if ("有效".equals(examExaminerParams.getIsEnabled())) {
          isEnabled = 1;
        } else if ("无效".equals(examExaminerParams.getIsEnabled())) {
          isEnabled = 2;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        // 根据areaName查询areaCode

        examExaminer.setIsEnabled(isEnabled);

        examExaminer.setExaminerProvince(
            this.getDictAreaCodeByName(examExaminerParams.getExaminerProvince()));
        examExaminer.setExaminerCity(
            this.getDictAreaCodeByName(examExaminerParams.getExaminerCity()));
        examExaminer.setExaminerArea(
            this.getDictAreaCodeByName(examExaminerParams.getExaminerArea()));
        examExaminer.setBeginDate(examExaminerParams.getBeginDate());
        examExaminer.setEndDate(examExaminerParams.getEndDate());
        examExaminer.setExaminerPhone(examExaminerParams.getExaminerPhone());
        examExaminer.setExaminerWorkUnit(examExaminerParams.getExaminerWorkUnit());
        examExaminer.setExaminerEducation(examExaminerParams.getExaminerEducation());
        examExaminer.setExaminerMajor(examExaminerParams.getExaminerMajor());
        examExaminer.setExaminerSkills(examExaminerParams.getExaminerSkills());
        examExaminer.setExaminerResume(examExaminerParams.getExaminerResume());
        examExaminer.setExaminerUuid(UUIDGenerator.generate());
        examExaminer.setCreatedTm(new Date());
        examExaminer.setCreatedBy(guid);
        int num = examExaminerMapper.insertSelective(examExaminer);
        if (num > 0) {
          successNum++;
        } else {
          failureNum++;
          failureMsg.append("<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： 添加失败");
          continue;
        }
      } catch (RuntimeException e) {
        failureNum++;
        String msg = "<br/>" + failureNum + "、第" + (i + 2) + "行,导入失败：";
        failureMsg.append(msg + e.getMessage());
        errLog("importAllStaffWorkers", ErrorCodeMsg.SERVER_ERROR.getMessage(), "服务器错误");
      }
    }
    if (failureNum > 0) {
      failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
      map.put("data", "1");
      map.put("msg", failureMsg.toString());
      throw new Exception(failureMsg.toString());
    } else {
      successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条");
      map.put("data", "0");
      map.put("msg", successMsg.toString());
    }
    return map;
  }

  /** @Author:王梓宁 @Date 2020/4/17 16:08 根据身份证查询是否存在该考官 */
  @Override
  public List<ExamExaminer> checkExaminerByCardNo(String examinerCardNo) {
    Example ex = new Example(ExamExaminer.class);
    Example.Criteria criteria = ex.createCriteria();
    criteria.andEqualTo("examinerCardNo", examinerCardNo);
    List<ExamExaminer> examExaminerList = examExaminerMapper.selectByExample(ex);
    return examExaminerList;
  }

  /** @Author:王梓宁 @Date 2020/4/17 10:46 导出考官数据接口 */
  @Override
  public Map<String, Object> getDownloadExaminerempty(ExamExaminerVO examExaminerVO) {
    Map<String, Object> param = new HashMap<>();
    String clzName = "考官列表";
    // 导出数据
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 声明一个表格
    HSSFSheet sheet = workbook.createSheet("考官列表");
    HSSFSheet sheet2 = workbook.createSheet("学历表");
    // 设置表格默认宽度
    sheet.setDefaultColumnWidth((short) 30);
    sheet2.setDefaultColumnWidth((short) 30);
    //    HSSFFont font3 = workbook.createFont();
    //    font3.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
    // 需要导出的文件名字
    String fileName = clzName + ".xls";
    int rowNum = 1;
    // 导出Excel 表格头部
    String[] header = {
      "姓名", "身份证号", "性别", "考官证号", "是否有效", "开始日期", "结束日期", "所在省", "所在市", "所在区", "联系电话", "工作单位",
      "学历代码", "所学专业", "职称", "工作简历"
    };

    String[] header2 = {"学历", "学历代码"};
    HSSFRow row = sheet.createRow(0);
    HSSFRow row2 = sheet2.createRow(0);
    // 设置字体
    CellStyle redStyle = workbook.createCellStyle();
    HSSFFont redFont = workbook.createFont();
    // 颜色
    redFont.setColor(Font.COLOR_RED);
    // 设置字体大小
    redFont.setFontHeightInPoints((short) 10);
    // 字体
    redFont.setFontName("宋体");
    redStyle.setFont(redFont);
    // 在sheet中添加表头
    for (int i = 0; i < header.length; i++) {
      HSSFCell cell = row.createCell(i);
      if (i <= 9) {
        cell.setCellStyle(redStyle);
      }
      HSSFRichTextString text = new HSSFRichTextString(header[i]);
      cell.setCellValue(text);
    }
    // 在sheet2中添加表头
    for (int i = 0; i < header2.length; i++) {
      HSSFCell cell = row2.createCell(i);
      HSSFRichTextString text = new HSSFRichTextString(header2[i]);
      cell.setCellValue(text);
    }
    HSSFRow row1 = null;
    HSSFRow row3 = null;
    List<DictDataVO> dictDataVOList = this.upEducation();
    for (DictDataVO allStaffOrg : dictDataVOList) {
      row3 = sheet2.createRow(rowNum);
      row3.createCell(0).setCellValue(allStaffOrg.getName());
      row3.createCell(1).setCellValue(allStaffOrg.getCode());
      rowNum++;
    }
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    List<Map<String, Object>> mapList = this.examinerList();
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    try {
      workbook.write(os);
    } catch (IOException e) {
      e.printStackTrace();
    }
    os.toByteArray();
    try {
      workbook.close();
    } catch (IOException e) {
      e.printStackTrace();
    }
    String httpUrl = null;
    String filePath = null;
    String url = null;
    try {
      url = FastDFSUtils.getDoUpload(examExaminerVO.getSessionToke(), fileName, os.toByteArray());
    } catch (Exception e) {
      errLog("getDownloadAllStaffWorkers", ErrorCodeMsg.UNKNOWN_ERROR.getMessage(), "获取文件服务下载地址为空");
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "获取文件服务下载地址为空");
    }
    if (StringUtils.isNotBlank(url)) {
      JSONObject jsonObject = JSONObject.parseObject(url);
      String data = jsonObject.getString("data");
      JSONObject dataObject = JSONObject.parseObject(data);
      httpUrl = dataObject.getString("httpUrl");
      filePath = dataObject.getString("filePath");
      if (StringUtils.isEmpty(filePath)) {
        errLog(
            "getDownloadTrainStuEnroll", ErrorCodeMsg.UNKNOWN_ERROR.getMessage(), "获取文件服务下载地址为空2");
        throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "获取文件服务下载地址为空2");
      }
    }
    Map<String, Object> map = new HashMap<>();
    map.put("url", fileServer + "/" + filePath);
    map.put("attname", fileName);
    return map;
  }

  /** @Author:王梓宁 @Date 2020/4/28 16:09 根据地区Name获取地区code */
  @Override
  public String getDictAreaCodeByName(String name) {
    Example example = new Example(DictAreaCode.class);
    Example.Criteria criteria = example.createCriteria();
    // 是否有效：1、有效；2、无效
    criteria.andEqualTo("isEnabled", CmnConstants.IS_ENABLED_YES);
    // 是否删除：1、未删除；2、已删除
    criteria.andEqualTo("isDeleted", CmnConstants.IS_DELETED_NO);
    criteria.andEqualTo("areaName", name);
    List<DictAreaCode> dictAreaCodes = dictAreaCodeMapper.selectByExample(example);
    if (0 == dictAreaCodes.size()) {
      throw new AttemptException(ErrorCodeMsg.UNKNOWN_ERROR, "未能通过地区昵称获取到地区代码");
    }
    return dictAreaCodes.get(0).getAreaCode();
  }

  private Map parseMap(String param) throws Exception {
    if (Strings.isNullOrEmpty(param)) {
      return Collections.EMPTY_MAP;
    }
    try {
      ObjectMapper objectMapper = new ObjectMapper();
      Map paramMap = objectMapper.readValue(param, Map.class);
      return paramMap;
    } catch (Exception e) {
      errLog("getPlanStatusList", ErrorCodeMsg.JSON_DECODE_ERROR.getMessage(), "状态转换成Map");
      throw new AttemptException(ErrorCodeMsg.JSON_DECODE_ERROR);
    }
  }

  /**
   * param method 方法名
   *
   * @param errorMessage 错误信息
   * @param errMark 具体错误信息
   */
  private void errLog(String method, String errorMessage, String errMark) {
    logger.error(
        Strings.lenientFormat("【TRAIN-PLAN-IMPL-%s】：%s:%s", method, errorMessage, errMark));
  }

  private void validImportStuInfo(ExamExaminerParams examExaminerParams) {

    if (StringUtils.isBlank(examExaminerParams.getExaminerName())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "姓名参数为空");
      throw new RuntimeException("姓名不能为空!");
    }

    if (StringUtils.isBlank(examExaminerParams.getExaminerCardNo())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "身份证号参数为空");
      throw new RuntimeException("身份证号不能为空");
    }

    if (StringUtils.isBlank(examExaminerParams.getExaminerSex())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "性别为空");
      throw new RuntimeException("性别为空");
    }

    if (StringUtils.isBlank(examExaminerParams.getExaminerCertNo())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "考官证号为空");
      throw new RuntimeException("考官证号为空");
    }

    if (null == examExaminerParams.getIsEnabled()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "是否有效为空");
      throw new RuntimeException("是否有效为空");
    }

    if (null == examExaminerParams.getBeginDate()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "开始日期为空");
      throw new RuntimeException("开始日期为空");
    }
    if (null == examExaminerParams.getEndDate()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "结束日期为空");
      throw new RuntimeException("结束日期为空");
    }
    if (null == examExaminerParams.getExaminerProvince()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "所在省为空");
      throw new RuntimeException("所在省为空");
    }
    if (null == examExaminerParams.getExaminerCity()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "所在市为空");
      throw new RuntimeException("所在市为空");
    }
    if (null == examExaminerParams.getExaminerCity()) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "所在区为空");
      throw new RuntimeException("所在区为空");
    }
    if (examExaminerParams.getBeginDate().after(examExaminerParams.getEndDate())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "开始日期早于结束日期");
      throw new RuntimeException("开始日期早于结束日期");
    }
    // 校验身份证格式和身份证对应的性别
    if (!IdcardUtils.validateCard(examExaminerParams.getExaminerCardNo())) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "身份证格式不正确");
      throw new RuntimeException("身份证格式不正确");
    } else if (!examExaminerParams
        .getExaminerSex()
        .equals(IdcardUtils.getGenderNameByIdCard(examExaminerParams.getExaminerCardNo()))) {
      errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "性别与身份证不匹配");
      throw new RuntimeException("性别与身份证不匹配");
    }
  }
}