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生成导入模板方案的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。