SpringBoot导出Excel、Excel配置下拉框、单元格标注、数据校验错误提示

M小南瓜

已于 2024-08-16 17:31:37 修改

阅读量399

点赞数 4

分类专栏： java 文章标签： java 后端 spring boot

于 2024-08-14 09:40:46 首次发布

本文链接：https://blog.csdn.net/2401_86579778/article/details/140949075

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、SpringBoot使用EasyExcel导出excel

二、EasyExcel配置：Excel下拉框、单元格换行、配置列宽度、数据输入校验

前言

SpringBoot中通过EasyExcel导出excel及Excel样式、数据校验配置。

提示：以下是本篇文章正文内容，下面案例可供参考

一、SpringBoot使用EasyExcel导出excel

public void downloadTemplete( HttpServletResponse response) throws IOException {
    
    List<demoDo>> list = new ArrayList<>();
    list.add(new demoDo())//添加数据

    // 这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("导出文件名", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

    // 单个sheet下载，这里需要设置不关闭流
    EasyExcel
            .write(response.getOutputStream(), demoDo.class)
            .autoCloseStream(Boolean.FALSE)
            .sheet("项目表")
            .doWrite(list);
}

二、EasyExcel配置：Excel下拉框、单元格换行、配置列宽度、数据输入校验

//自定义内容样式及头样式
WriteCellStyle customBodyWriteCellStyle = new WriteCellStyle();
customBodyWriteCellStyle.setWrapped(true); //设置换行
WriteCellStyle customHeaderWriteCellStyle = new WriteCellStyle();
//自定义下拉框数据
Map<Integer, String[]> dropDownMap = new HashMap<>();
//生成excel
EasyExcel
        .write(response.getOutputStream(), demoDo.class)
        .autoCloseStream(Boolean.FALSE)
        .sheet("工作表名称")
        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))//配置列宽度
        .registerWriteHandler(new XMCustomSheet(dropDownMap))//配置下拉框（自定义sheet样式参考下面）
        .registerWriteHandler(new HorizontalCellStyleStrategy(customHeaderWriteCellStyle,customBodyWriteCellStyle))//配置内容、头样式
        .doWrite(list);

2.自定义工作表配置(以下目前只有下拉框配置、禁止输入配置)

public class XMCustomSheet implements SheetWriteHandler {

    private Map<Integer,String[]> dropDownMap;
    
    public XMCustomSheet(Map<Integer,String[]> dropDownMap) {
        this.dropDownMap=dropDownMap;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        //设置下拉框
        for(Map.Entry<Integer,String[]> entry:this.dropDownMap.entrySet()){
            //起始行、终止行、起始列、终止列  起始行为1即表示表头不设置
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            //设置下拉框数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            //创建校验规则
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            dataValidation.setSuppressDropDownArrow(true);//禁止输入只能选择
            dataValidation.createErrorBox("提示", "请选择下拉框中的值,缺少参数联系管理员维护！！！");
            //设置是否显示错误窗口
            dataValidation.setShowErrorBox(true);
            sheet.addValidationData(dataValidation);
        }
        //设置日期格式
        CellRangeAddressList addressList1 = new CellRangeAddressList(firstRow, lastRow, 3, 3);
        DataValidationConstraint dateConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"Date(1900,01,01)", "Date(2124,01,01)", "yyyy/MM/dd");
        DataValidation dateValidation = helper.createValidation(dateConstraint, addressList1);
        //设置错误窗口
        dateValidation.createErrorBox("提示", "请输入【yyyy-MM-dd、yyyy/MM/dd】格式的日期！！！");
        dateValidation.setShowErrorBox(true);
        dateValidation.setEmptyCellAllowed(false);
        //设置提示框
        dateValidation.createPromptBox("例：", "2024-08-14");
        dateValidation.setShowPromptBox(true);

        dateValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dateValidation);

    }
}