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;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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(), "下载导入模板失败");}
}