EasyExcel中使用自定义注解解决Attempting to write a row[0] in the range

java.lang.IllegalArgumentException: Attempting to write a row[0] in the range [0,300] that is already written to disk.

的情况.例如说

List<DropDownOptions> options = new ArrayList<>();
        options.add(new DropDownOptions(0,userService.selectUserListName()));//两行都需要用户名称数据下拉框,并且下拉内容较多的
        options.add(new DropDownOptions(3,userService.selectUserListName()));
        ExcelUtil.exportExcel(new ArrayList<>(), "联锁管理", Lsglxx.class, response,
                options);

步骤1: 添加registerWriteHandler

在exportExcel(…)方法中的exportExcel 的 ExcelWriterSheetBuilder 里面添加下拉框builder.registerWriteHandler(new ExcelUserHandler());

2.添加Handler 去Handler implements SheetWriteHandler

import com.alibaba.excel.metadata.FieldCache;
import com.alibaba.excel.metadata.FieldWrapper;
import com.alibaba.excel.util.ClassUtils;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.ocse.provider.flowable.annotation.ExcelUserFormat;
import com.ocse.provider.flowable.utils.SpringUtils;
import com.ocse.provider.service.AuthService;
import com.ocse.provider.service.UserInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
public class ExcelUserHandler implements SheetWriteHandler {

    /**
     * 创建sheet页前的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        log.info("创建sheet之前");
    }

    /**
     * 创建sheet页后的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

       
        AuthService authService = SpringUtils.getBean(AuthService.class);
        UserInfoService userInfoService = SpringUtils.getBean(UserInfoService.class);
        List<String> collect = null;
        try {
            //常用数据的 数据来源 , 此处设置成 员工姓名-编码 例:李四-lisi
            collect = authService.findAllUserListByOrgUnitCode(userInfoService.getUserInfo().getOrgUnitCode()).stream().map(item ->
                    item.getUserName() + "-" + item.getUserCode()

            ).collect(Collectors.toList());
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 创建sheet，突破下拉框255的限制
        //获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        //定义sheet的名称
        String sheetName = "人员";
        //1.创建一个隐藏的sheet 名称为 人员
        Sheet proviceSheet = workbook.createSheet(sheetName);
        // 设置隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(proviceSheet), true);
        for (int i = 0, length = collect.size(); i < length; i++) {
            // i:表示你开始的行数  0表示你开始的列数
            proviceSheet.createRow(i).createCell(0).setCellValue(collect.get(i));
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(sheetName);
        // $A$1:$A$N代表 以A列1行开始获取N行下拉数据
        category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (collect.size()));
        //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
        FieldCache fieldCache = ClassUtils.declaredFields(writeWorkbookHolder.getClazz(), writeWorkbookHolder);
        for (Map.Entry<Integer, FieldWrapper> entry : fieldCache.getSortedFieldMap().entrySet()) {
            FieldWrapper wrapper = entry.getValue();
            Field field = wrapper.getField();
            // 循环实体中的每个属性
            // 可选的下拉值
            if (field.isAnnotationPresent(ExcelUserFormat.class)) {
                // 如果指定了@ExcelUserFormat
                ExcelUserFormat format = field.getDeclaredAnnotation(ExcelUserFormat.class);
                int index = 0;
                if (format.index() == -1) {
                    index = entry.getKey();
                }else {
                    index = format.index();
                }
                //5 将刚才设置的sheet引用到你的下拉列表中
                CellRangeAddressList addressList = new CellRangeAddressList(1, collect.size(), index, index);
                DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
                DataValidation dataValidation = helper.createValidation(constraint8, addressList);

                // 数据有效性对象
                // 处理Excel兼容性问题
                if (dataValidation instanceof XSSFDataValidation) {
                    //数据校验
                    dataValidation.setSuppressDropDownArrow(true);
                    //错误提示
                    dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                    dataValidation.createErrorBox("提示", "此值与单元格定义数据不一致");
                    dataValidation.setShowErrorBox(true);
                    //选定提示
                    dataValidation.createPromptBox("填写说明：", "填写内容格式为:人员名称-人员编号,其他将导致导入异常");
                    dataValidation.setShowPromptBox(true);
                    proviceSheet.addValidationData(dataValidation);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                writeSheetHolder.getSheet().addValidationData(dataValidation);
            }
        }
    }
}

3.实现自定义注解

package com.ocse.provider.flowable.annotation;


import java.lang.annotation.*;

/**
 * 需要给哪一列设置用户下拉框,加上该注解即可
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelUserFormat {

    /**
     * 需要给哪一列设置下拉框,设置列的数字即可
     */
    int index() default -1;

}

4.使用示例

@TableName(value = "t_lsglxx")
@Data
@ExcelIgnoreUnannotated
public class Lsglxx implements Serializable {
......
    /**
     * 负责人
     */
    @TableField(value = "fzr")
    @ExcelProperty(value = "负责人(统一身份账号)",index = 3)
    @ExcelUserFormat
    private String fzr;
    
        /**
     * 申请人
     */
    @TableField(value = "sqr")
    @ExcelProperty(value = "申请人(统一身份账号)",index = 17)
    @ExcelUserFormat
    private String sqr;
......
}