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;}@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(), "下载导入模板失败");} }

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 bloba.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}});},

 

总结

以上是生活随笔为你收集整理的EasyExcel生成导入模板方案的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。