EasyExcel生成导入模板方案

1、模板实体类

@Data
public class SysUserImportExcel {

    private static final String bigTitle= "填写须知： \n" +
            "1.第1、2行为固定结构，不可更改；以下示例行，导入前请先删除\n" +
            "2.请严格按照填写规则输入数据，不合规的数据无法成功导入";

    @ExcelProperty(value = {bigTitle,"姓名（必填）"}, index = 0)
    private String userName;

    @ExcelProperty(value = {bigTitle,"性别（必填）"}, index = 1)
    private String userSexName;

    @ExcelProperty(value = {bigTitle,"手机号码（必填）"}, index = 2)
    private String userMobile;

    @ExcelProperty(value = {bigTitle,"出生年月（必填）"}, index = 3)
    private Date userBirthday;

    @ExcelProperty(value = {bigTitle,"工作单位(必填)"}, index = 4)
    private String deptName;

    @ExcelProperty(value = {bigTitle,"职务(必填)"}, index = 5)
    private String unitPosition;

    @ExcelProperty(value = {bigTitle,"干部类别(必填)"}, index = 6)
    private String leaderTypeName;

    @ExcelProperty(value = {bigTitle,"用户状态(必填)"}, index = 7)
    private String userStatusName;
}

多行表头方法二：

 /**
     * 设置模板表格的表头
     * @return
     */
    private List<List<String>> getMorningCheckHead(){
        String bigTitle= "填写须知： \n" +
                "1.第1、2行为固定结构，不可更改；以下示例行，导入前请先删除\n" +
                "2.请严格按照填写规则输入数据，不合规的数据无法成功导入";
        List<List<String>> head = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<>();
        head0.add(bigTitle);
        head0.add("姓名（必填）");
        List<String> head1 = new ArrayList<>();
        head1.add(bigTitle);
        head1.add("性别（必填）");
        List<String> head2 = new ArrayList<>();
        head2.add(bigTitle);
        head2.add("手机号码（必填）");
        List<String> head3 = new ArrayList<>();
        head3.add(bigTitle);
        head3.add("出生年月（必填）");
        List<String> head4 = new ArrayList<>();
        head4.add(bigTitle);
        head4.add("工作单位(必填)");
        List<String> head5 = new ArrayList<>();
        head5.add(bigTitle);
        head5.add("职务(必填)");
        List<String> head6 = new ArrayList<>();head.add(head0);
        head.add(head1);
        head.add(head2);
        head.add(head3);
        head.add(head4);
        head.add(head5);
     
        return head;
    }
}

2、自定义下拉框

@Data
@Slf4j
public class SysUserWriteHandler implements SheetWriteHandler {

    private Map<String, Map<String, String>> paramMap = null;

    public SysUserWriteHandler() {}

    public SysUserWriteHandler(Map<String, Map<String, String>> paramMap) {
        this.paramMap = paramMap;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //性别
        Map<String, String> userSexMap = paramMap.get(SysParamConstants.USER_SEX);
        //过滤key为-1的值
        Map<String, String> newUserSexMap = userSexMap.entrySet().stream().filter(map -> ObjectUtil.notEqual(CommonConstants.TOP_KEY, map.getKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        //名称转成数组
        List<String> userSexList = newUserSexMap.values().stream().collect(Collectors.toList());
        String[] userSex = userSexList.toArray(new String[userSexList.size()]);

        //干部类别
        Map<String, String> leaderTypeMap = paramMap.get(SysParamConstants.USER_LEADER_TYPE);
        //过滤key为-1的值
        Map<String, String> newLeaderTypeMap = leaderTypeMap.entrySet().stream().filter(map -> ObjectUtil.notEqual(CommonConstants.TOP_KEY, map.getKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        //名称转成数组
        List<String> leaderTypeList = newLeaderTypeMap.values().stream().collect(Collectors.toList());
        String[] leaderType = leaderTypeList.toArray(new String[leaderTypeList.size()]);

        Map<Integer,String[]> mapDropDown = new HashMap<>();
        mapDropDown.put(1,userSex); //性别
        mapDropDown.put(6,leaderType); //干部类别
      

        Sheet sheet = writeSheetHolder.getSheet();

        //设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();

        mapDropDown.forEach((k, v) -> {
            // 下拉列表约束数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
            // 设置下拉单元格的首行 末行 首列 末列
            CellRangeAddressList rangeList = new CellRangeAddressList(2, 65536, k, k);
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示","此值与单元格定义格式不一致");
            // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
            sheet.addValidationData(validation);

        });
    }

}

3、导出模板工具类

/**
	 * 导出excel模板
	 * @param response
	 * @param fileName
	 * @param sheetName
	 * @param model
	 * @throws Exception
	 */
	public static HttpServletResponse exportExcelModel(HttpServletResponse response, String fileName, String sheetName,List<? extends Object> data, Class<?> model, SheetWriteHandler sheetWriteHandler) throws Exception {
		// 头的策略
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		// 设置表头居中对齐
		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
		// 颜色
		headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		WriteFont headWriteFont = new WriteFont();
		headWriteFont.setFontHeightInPoints((short) 10);
		// 字体
		headWriteCellStyle.setWriteFont(headWriteFont);
		headWriteCellStyle.setWrapped(true);
		// 内容的策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		// 设置内容靠中对齐
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
		HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
		// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭

		EasyExcel.write(getOutputStream(fileName, response), model).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
				.registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(sheetWriteHandler).doWrite(data);

		return null;
	}

	/**
	 * 导出文件时为Writer生成OutputStream.
	 *
	 * @param fileName 文件名
	 * @param response response
	 * @return ""
	 */
	private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
		try {
			fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			response.setCharacterEncoding("utf8");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
			response.setHeader("Pragma", "public");
			response.setHeader("Cache-Control", "no-store");
			response.addHeader("downloadName", fileName  + ".xlsx");
			response.addHeader("Cache-Control", "max-age=0");
			return response.getOutputStream();
		} catch (IOException e) {
			throw new IOException("导出excel表格失败!", e);
		}
	}

4、下载模板方法：

 public void downloadExcelModel(HttpServletResponse response) {
        String fileName = "导入模板";
        String sheetName = "模板";
        try {
            List<SysUserImportExcel> sysUserImportExcelList = getSysUserImportExcel();
            //获取数字字典
            Map<String, Map<String, String>> paramMap = redisService.mget(CommonConstants.CacheKey.REIDS_SYS_PARAM_DATA);
            //输出文件流
            EasyExcelUtil.exportExcelModel(response,fileName,sheetName,sysUserImportExcelList,SysUserImportExcel.class,new SysUserWriteHandler(paramMap));

        }catch (Exception e){
            e.printStackTrace();
            throw new GlobalException(ExceptionCodeEnum.FAIL.getCode(), "下载导入模板失败");
        }
}

5、前端处理

1、点击事件：
<butn btntype="primary" title="下载模板" @click="downloadData"></butn>

2、请求js：/* 下载导入用户模板 */
export function downloadExcelModel(params) {
  return request({
    url: `/admin/sys/user/downloadModel`,
    method: "put",
    responseType: "blob",
    headers: {
      Accept: "application/json, text/plain, */*, application/octet-stream",
      "Content-Type": "application/json;charset=UTF-8"
    },
    data: params
  });
}


3、下载模板方法
    downloadData() {
      downloadExcelModel().then(response => {
        try {
          let jsonData = JSON.parse(this.result);
          console.log(jsonData);
          if (jsonData.code) {
            this.$message({
              message: jsonData.msg,
              type: "error"
            });
          }
        } catch (e) {
          let blob = new Blob([response.data]); //创建一个blob对象
          const uA = window.navigator.userAgent;
          const isIE =
                  /msie\s|trident\/|edge\//i.test(uA) &&
                  !!(
                          "uniqueID" in document ||
                          "documentMode" in document ||
                          "ActiveXObject" in window ||
                          "MSInputMethodContext" in window
                  );
          let a = document.createElement("a"); //创建一个<a></a>标签
          let href = window.URL.createObjectURL(blob); // response is a blob
          a.href = href;
          let title = decodeURI(escape(response.headers.downloadname)); //文件名称
          a.download = title;
          a.style.display = "none";
          document.body.appendChild(a);
          if (isIE) {
            // 兼容IE11无法触发下载的问题
            navigator.msSaveBlob(blob, title);
          } else {
            a.click();
          }
          document.body.removeChild(a); // 下载完成移除元素
          window.URL.revokeObjectURL(href); // 释放url
        }
      });
    },