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 mon.easyexcel.annotation.ExcelSelected;import mon.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 .URLEncoder;import java.nio.charset.StandardCharsets;import java.util.HashMap;import java.util.List;import java.util.Map;@Slf4jpublic 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() {@Overridepublic 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;@ExcelIgnoreprivate 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.ponent;@Componentpublic class SpringContextUtil implements ApplicationContextAware {private static ApplicationContext applicationContext;@Overridepublic void setApplicationContext(ApplicationContext applicationContext) throws BeansException {SpringContextUtil.applicationContext = applicationContext;}// 获取ApplicationContextpublic static ApplicationContext getApplicationContext() {return applicationContext;}// 通过class获取Beanpublic static <T> T getBean(Class<T> clazz) {return applicationContext.getBean(clazz);}// 通过name以及class获取Beanpublic static <T> T getBean(String name, Class<T> clazz) {return applicationContext.getBean(name, clazz);}}
动态下拉框配置类
public class CityExcelSelectedImpl implements ExcelDynamicSelect{@Overridepublic 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@Slf4jpublic class exportController {@PostMapping("/exportSelectedExcel")public void export(HttpServletResponse response){EasyExcelUtil.writeExcelBySelect(response, "测试", "", new ArrayList<UserDTO>())}}
