eazyexcel生成校验单元格内容的excel文件

功能描述

有的时候需要生成excel文件 对单元格填的值进行校验符不符合规则。

这里给出的例子是 excel中填充下拉框选项，如果输入的值不符合下拉框选项 则弹出提示

maven依赖

  
            cn.afterturn
            easypoi-spring-boot-starter
            4.4.0
        

controller内方法

@GetMapping("/down") 
public void downTemplate(HttpServletResponse response) throws IOException {
 
 
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("FileName", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    
    ArrayList optionList= Lists.newArrayList();
    optionList.add("选项A");
    optionList.add("选项B");
    optionList.add("选项C");
    SelectedWriteStyleHandler selectedWriteStyleHandler = SelectedWriteStyleHandler.buildSelectList(2,optionList);
    EasyExcel.write(response.getOutputStream(), EvaluationUserImportExcel.class)
            .registerWriteHandler(selectedWriteStyleHandler).sheet("sheetName").doWrite(Lists.newArrayList());
 
}

自定义SelectedWriteStyleHandler 

 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
 
import java.util.List;
import java.util.Map;
 
/**
 * @Description 
 * @Date 2024-04-29 16:27 
 **/
 
public class SelectedWriteStyleHandler implements SheetWriteHandler {
 
 
    public SelectedWriteStyleHandler(int column,List selectedList){
        this.selectedList=selectedList;
        this.column=column;
 
    }
 
    public static SelectedWriteStyleHandler buildSelectList(int column,List selectedList){
       return new SelectedWriteStyleHandler(column,selectedList);
    }
    private List selectedList;
 
    private int column;
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
 
        Map> selectMapParam= Maps.newHashMap();
        selectMapParam.put(column,selectedList);
 
        Sheet sheet = writeSheetHolder.getCachedSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
 
        Workbook cachedWorkbook = writeWorkbookHolder.getCachedWorkbook();
 
        int index=1;
 
        for (Map.Entry> entry : selectMapParam.entrySet()) {
 
            String sheetDicName="dictSheel"+index;
            Sheet dicSheel = cachedWorkbook.createSheet(sheetDicName);
 
            // 隐藏字典页
            cachedWorkbook.setSheetHidden(index++,true);
 
            CellRangeAddressList cellList=new CellRangeAddressList(1,1048575,entry.getKey(),entry.getKey());
 
            int rowLength=entry.getValue().size();
            for (int i = 0; i < rowLength; i++) {
                dicSheel.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));
            }
 
            String refers=sheetDicName+"!$A$1:$A$"+entry.getValue().size();
            Name name = cachedWorkbook.createName();
            name.setNameName(sheetDicName);
            name.setRefersToFormula(refers);
 
            DataValidationConstraint listConstraint = helper.createFormulaListConstraint(sheetDicName);
 
            DataValidation validation = helper.createValidation(listConstraint, cellList);
            // 显示下拉箭头
            validation.setSuppressDropDownArrow(true);
            // 显示错误弹出框
            validation.setShowErrorBox(true);
            validation.createErrorBox("数据错误", "您输入的内容，不符合限制条件。");
//            validation.createPromptBox("XXX","请选择下拉项中的字典值");
            // 选择下拉项后是否允许清除单元格
            validation.setEmptyCellAllowed(false);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
 
            sheet.addValidationData(validation);
 
        }
 
 
    }
}