JAVA笔记 | EasyExcel创建带有简单下拉框的导入模板

目录

前文

业务需求

具体代码

新增Handler

控制层

---

前文

SpringBoot笔记 | EasyExcel导入导出及基于模板导出_easyexcel模板导出-CSDN博客

业务需求

需要一个导出模板。一个列需要填写固定的值，或者方便用户填写。

自己需求，几个固定的字段对应固定的id，所以导出时，用户填写固定字段中文，导入时，通过名字匹配到id，插入数据库。然后网上找了挺多个的，但是要么很复杂，要么就是没说明白，最后找了这个最简单的实现。

具体代码

新增Handler

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;import java.util.Map;/*** @Author: ljd* @CreateTime: 2024-10-16*/
public class CustomSheetWriteHandler implements SheetWriteHandler {private Map<Integer, String[]> mapDropDown;public CustomSheetWriteHandler(Map<Integer, String[]> mapDropDown) {this.mapDropDown = mapDropDown;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());DataValidation dataValidation = helper.createValidation(constraint, addressList);dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.setShowErrorBox(true);dataValidation.setSuppressDropDownArrow(true);dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");sheet.addValidationData(dataValidation);}}
}

控制层

为了方便都在写这层

/*** 获取导入模板*/@PostMapping("/importTemplate")@SaIgnorepublic void importTemplate(HttpServletResponse response) throws IOException {//用于下拉的数据源List<EduSubject> subjects = subjectMapper.selectList();if(CollectionUtil.isNotEmpty(subjects)){Map<Integer, String[]> mapDropDown = new HashMap<>();String[] sub = subjects.stream().map(EduSubject::getSubjectName).toArray(String[]::new);mapDropDown.put(1, sub);String fileName = URLEncoder.encode("XXX导入模板", "UTF-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), EduKnowledgePointsVo.class).sheet("知识点").registerWriteHandler(new CustomSheetWriteHandler(mapDropDown)).doWrite(Collections.EMPTY_LIST);}}