Java 导出Excel表格生成下拉框-EasyExcel

1.导入EasyExcel，maven依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

2.添加EasyExcelUtil工具类

package org.springblade.modules.system.EuipmentExcelImplement;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

@Slf4j
public class EasyExcelUtil {

	/**
	 * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
	 * @param head 导出的表头信息和配置
	 * @param sheetNo sheet索引
	 * @param sheetName sheet名称
	 * @param <T> 泛型
	 * @return sheet页
	 */
	public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
		Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

		return EasyExcel.writerSheet(sheetNo, sheetName)
			.head(head)
			.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
			.build();
	}

	/**
	 * 解析表头类中的下拉注解
	 * @param head 表头类
	 * @param <T> 泛型
	 * @return Map<下拉框列索引, 下拉框内容> map
	 */
	private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
		Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

		// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
		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) {
				ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
				String[] source = excelSelectedResolve.resolveSelectedSource(selected);
				if (source != null && source.length > 0){
					excelSelectedResolve.setSource(source);
					excelSelectedResolve.setFirstRow(selected.firstRow());
					excelSelectedResolve.setLastRow(selected.lastRow());
					if (property != null && property.index() >= 0){
						selectedMap.put(property.index(), excelSelectedResolve);
					} else {
						selectedMap.put(i, excelSelectedResolve);
					}
				}
			}
		}

		return selectedMap;
	}
}

package org.springblade.modules.system.EuipmentExcelImplement;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
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 java.util.Map;

@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {

	private final Map<Integer, ExcelSelectedResolve> selectedMap;

	/**
	 * Called before create the sheet
	 */
	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

	}

	/**
	 * Called after the sheet is created
	 */
	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		// 这里可以对cell进行任何操作
		Sheet sheet = writeSheetHolder.getSheet();
		DataValidationHelper helper = sheet.getDataValidationHelper();
		selectedMap.forEach((k, v) -> {
			// 设置下拉列表的行： 首行，末行，首列，末列
			CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
			// 设置下拉列表的值
			DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
			// 设置约束
			DataValidation validation = helper.createValidation(constraint, rangeList);
			// 阻止输入非下拉选项的值
			validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
			validation.setShowErrorBox(true);
			validation.setSuppressDropDownArrow(true);
			validation.createErrorBox("提示", "请输入下拉选项中的内容");
			sheet.addValidationData(validation);
		});
	}
}

3 .创建实体类

package org.springblade.modules.question.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springblade.modules.question.excelImplement.*;
import org.springblade.modules.system.EuipmentExcelImplement.DeptSelectedImpl;
import org.springblade.modules.system.EuipmentExcelImplement.ExcelSelected;

import java.io.Serializable;


@Data
@ColumnWidth(16)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class QuestionExcel implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * 课程名称
	 */
	@ExcelIgnore //忽略生成表头
	//@ExcelSelected(sourceClass = CourseSelected.class)
	private String courseName;

	/**
	 * 章节名称
	 */
	@ExcelProperty(index = 0,value ="课程章节名称")//表头名称
	@ExcelSelected(sourceClass = ChapterSelected.class)
	private String chapterName;

	/**
	 * 实验名称
	 */
	@ExcelProperty(index = 1,value ="实验名称")
	@ColumnWidth(25)
	@ExcelSelected(sourceClass = ExperimentSelected.class)
	private String experimentName;

	/**
	 * 题型(1:单选2:多选3:判断4:问答)
	 */
	@ExcelProperty(index = 2,value ="题型(1:单选2:多选3:判断4:问答)")
	@ExcelSelected(sourceClass = TypeSelected.class)
	private String questionType;
}

4.导入自定义注解ExcelSelected

package org.springblade.modules.system.EuipmentExcelImplement;

import java.lang.annotation.*;

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

	/**
	 * 动态下拉内容
	 */
	Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

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

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

5.导入Excel选择解析-ExcelSelectedResolve 

package org.springblade.modules.system.EuipmentExcelImplement;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

@Data
@Slf4j
public class ExcelSelectedResolve {
	/**
	 * 下拉内容
	 */
	private String[] source;

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

	/**
	 * 设置下拉框的结束行，默认为最后一行
	 */
	private int lastRow;

	public String[] resolveSelectedSource(ExcelSelected excelSelected) {
		if (excelSelected == null) {
			return null;
		}

		// 获取固定下拉框的内容
		String[] source = excelSelected.source();
		if (source.length > 0) {
			return source;
		}

		// 获取动态下拉框的内容
		Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
		if (classes.length > 0) {
			try {
				ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
				String[] dynamicSelectSource = excelDynamicSelect.getSource();
				if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
					return dynamicSelectSource;
				}
			} catch (InstantiationException | IllegalAccessException e) {
				log.error("解析动态下拉框数据异常", e);
			}
		}
		return null;
	}
}

6.添加动态选择接口-ExcelDynamicSelect

package org.springblade.modules.system.EuipmentExcelImplement;

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

7.编写自己需要展示下拉框实现-ExcelDynamicSelect接口

package org.springblade.modules.question.excelImplement;

import org.apache.commons.collections.CollectionUtils;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.modules.question.mapper.QuestionMapper;
import org.springblade.modules.system.EuipmentExcelImplement.ExcelDynamicSelect;
import org.springblade.modules.system.EuipmentExcelImplement.SpringContextUtil;

import java.util.List;

public class ChapterSelected implements ExcelDynamicSelect {

	@Override
	public String[] getSource() {

		QuestionMapper questionMapper = SpringContextUtil.getBean(QuestionMapper.class);
		return questionMapper.selectAllChapter().toArray(new String[]{});
	}
}

8.添加SpringContextUtil工具类

package org.springblade.modules.system.EuipmentExcelImplement;

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);
	}
}

9.编写查询下拉框显示内容mapping

public interface QuestionMapper extends BaseMapper<Question> {
public List<String> selectAllChapter();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.springblade.modules.question.mapper.QuestionMapper">


  <select id="selectAllChapter" resultType="java.lang.String">
        SELECT
            CONCAT(sc.NAME,'/',sc1.NAME) AS chapterName
        FROM
            sxdx_course sc
                INNER JOIN sxdx_chapter sc1 ON sc.id = sc1.course_id
        WHERE
            sc.is_deleted = 0
          AND sc1.is_deleted = 0
    </select>

</mapper>

10.也可以不查询数据库，直接再接口返回需要的数据。

package org.springblade.modules.question.excelImplement;

import org.springblade.modules.question.mapper.QuestionMapper;
import org.springblade.modules.system.EuipmentExcelImplement.ExcelDynamicSelect;
import org.springblade.modules.system.EuipmentExcelImplement.SpringContextUtil;

import java.util.ArrayList;

public class TypeSelected implements ExcelDynamicSelect {

	@Override
	public String[] getSource() {
//直接返回需要的数据
		return new ArrayList<String>(){{add("单选");add("多选");add("判断");add("问答");}}.toArray(new String[]{});
	}
}

11.最终效果