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;
    }

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

    }

    @Override
    public 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")
    @SaIgnore
    public 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);
        }
    
    }

原文地址：https://blog.csdn.net/qq_37630282/article/details/142997668

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！