使用EasyExcel导出带下拉框的Excel

1. 创建注解

import java.lang.annotation.*;

/**
 * 标注导出的列为下拉框类型，并为下拉框设置内容
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect> sourceClass() default ExcelDynamicSelect.class;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

2.创建动态下拉框的接口，设置动态下拉框都实现该接口

public interface ExcelDynamicSelect {
    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();
}

3.创建导出工具类

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.IterUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
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.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.study.common.easyexcel.annotation.ExcelSelected;
import com.study.common.easyexcel.select.ExcelSelectedResolve;
import lombok.extern.slf4j.Slf4j;
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 org.springframework.http.HttpHeaders;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class EasyExcelUtil {

	/**
     * 导出单sheet页且sheet页中含有下拉框的excel文件
     *
     * @param response  HttpServletResponse
     * @param fileName  文件名
     * @param sheetName sheet页名
     * @param data      要导出的数据
     */
    public static <T> void writeExcelBySelect(HttpServletResponse response, String fileName, String sheetName, List<T> data) {
        try {
            encodeFileName(response, fileName);
            Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(IterUtil.getElementType(data));
            EasyExcel.write(response.getOutputStream(), IterUtil.getElementType(data))
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(selectedSheetWriteHandler(selectedMap))
                    .sheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName)
                    .doWrite(data);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 设置文件名
     *
     * @param response HttpServletResponse
     * @param fileName 文件名
     */
    private static void encodeFileName(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, String.format("attachment;filename=\"%s\"", fileName + ".xlsx"));
        response.setHeader(HttpHeaders.CACHE_CONTROL, "no-cache");
        response.setHeader(HttpHeaders.PRAGMA, "no-cache");
        response.setDateHeader(HttpHeaders.EXPIRES, -1);
    }

    /**
     * 解析表头类中的下拉注解
     *
     * @param head 表头类
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, ExcelSelected> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelected> selectedMap = new HashMap<>(16);
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                if (property != null && property.index() >= 0) {
                    selectedMap.put(property.index(), selected);
                } else {
                    selectedMap.put(i, excelSelectedResolve);
                }
            }
        }
        return selectedMap;
    }

    /**
     * 为excel创建下拉框
     *
     * @param selectedMap 下拉框配置数据 Map<下拉框列索引, 下拉框内容>
     * @return intercepts handle sheet creation
     */
    private static SheetWriteHandler selectedSheetWriteHandler(Map<Integer, ExcelSelected> selectedMap) {
        return new SheetWriteHandler() {
            @Override
            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                Sheet sheet = writeSheetHolder.getSheet();
                DataValidationHelper helper = sheet.getDataValidationHelper();
                selectedMap.forEach((k, v) -> {
                	// 获取固定下拉框的内容
                	List<String> source = new ArrayList<>();
					if (v.source().length > 0) {
                        source.addAll(Arrays.asList(v.source()));
                    }
					// 获取动态下拉框的内容
					Class<? extends ExcelDynamicSelect> sourceClass = v.sourceClass();
					try {
					    ExcelDynamicSelect excelDynamicSelect = sourceClass.newInstance();
					    String[] dynamicSelectSource = excelDynamicSelect.getSource();
					    if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
					        source.addAll(Arrays.asList(dynamicSelectSource));
					    }
					} catch (InstantiationException | IllegalAccessException e) {
					    log.error("解析动态下拉框数据异常", e);
					}
					if (CollUtil.isNotEmpty(source)) {
						CellRangeAddressList rangeList = new CellRangeAddressList(v.firstRow(), v.lastRow(), k, k);
						DataValidationConstraint constraint = helper.createExplicitListConstraint(source.toArray(new String[0]));
						DataValidation validation = helper.createValidation(constraint, rangeList);
						validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
						validation.setShowErrorBox(true);
						validation.setSuppressDropDownArrow(true);
						validation.createErrorBox("提示", "请输入下拉选项中的内容");
						sheet.addValidationData(validation);
					}
                });
            }
        };
    }
}

4.测试导出

(1).创建需要导出的实体类并用该注解标注

public class UserDTO {
    @ExcelProperty(index = 0,value = "编号")
    private Long id;
    @ExcelProperty(index = 1,value = "姓名")
    private String name;
    @ExcelProperty(index = 2,value = "年龄")
    private Integer age;
    @ExcelProperty(index = 3,value = "性别")
    //静态下拉框
    @ExcelSelected(source = {"男","女"})
    private String sex;
    @ExcelProperty(index = 4,value = "出生日期")
    private Date birthday;
    @ExcelProperty(index = 5,value = "居住城市")
    //动态下拉框-去数据库中查询所有城市供其选择
    @ExcelSelected(sourceClass = CityExcelSelectedImpl.class)	
    private String city;
    @ExcelIgnore
    private Boolean isDeleted;
}

(2).创建动态下拉框配置类

这里不能用@Component注解标注该类，从而在Spring容器中通过@Resource或@Autowired获取Bean，只能通过ApplicationContext工具类获取Bean

ApplicationContext工具类

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
public class SpringContextUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }

    // 获取ApplicationContext
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    // 通过class获取Bean
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    // 通过name以及class获取Bean
    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }
}

动态下拉框配置类

public class CityExcelSelectedImpl implements ExcelDynamicSelect{

    @Override
    public String[] getSource() {
        CityMapper cityMapper = SpringContextUtil.getBean(CityMapper.class);
        return cityMapper.selectAllCity().toArray(new String[]{});
    }

}

web测试

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.practicevue.model.UserDTO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;

@RestController
@Slf4j
public class exportController {

    @PostMapping("/exportSelectedExcel")
    public void export(HttpServletResponse response){
        EasyExcelUtil.writeExcelBySelect(response, "测试", "", new ArrayList<UserDTO>())
    }
}