1.导入EasyExcel，maven依赖
com.alibabaeasyexcel2.2.10
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;@Slf4jpublic class EasyExcelUtil {/** * 创建即将导出的sheet页（sheet页中含有带下拉框的列） * @param head 导出的表头信息和配置 * @param sheetNo sheet索引 * @param sheetName sheet名称 * @param 泛型 * @return sheet页 */public static WriteSheet writeSelectedSheet(Class head, Integer sheetNo, String sheetName) {Map selectedMap = resolveSelectedAnnotation(head);return EasyExcel.writerSheet(sheetNo, sheetName).head(head).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).build();}/** * 解析表头类中的下拉注解 * @param head 表头类 * @param 泛型 * @return Map map */private static Map resolveSelectedAnnotation(Class head) {Map selectedMap = new HashMap();// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性Field[] fields = head.getDeclaredFields();for (int i = 0; i 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@AllArgsConstructorpublic class SelectedSheetWriteHandler implements SheetWriteHandler {private final Map selectedMap;/** * Called before create the sheet */@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/** * Called after the sheet is created */@Overridepublic 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[] 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@Slf4jpublic 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[] 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 {@Overridepublic 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;@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 getBean(Class clazz) {return applicationContext.getBean(clazz);}// 通过name以及class获取Beanpublic static T getBean(String name, Class clazz) {return applicationContext.getBean(name, clazz);}}
9.编写查询下拉框显示内容mapping
public interface QuestionMapper extends BaseMapper {public List selectAllChapter();}
SELECTCONCAT(sc.NAME,'/',sc1.NAME) AS chapterNameFROMsxdx_course scINNER JOIN sxdx_chapter sc1 ON sc.id = sc1.course_idWHEREsc.is_deleted = 0AND sc1.is_deleted = 0
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 {@Overridepublic String[] getSource() {//直接返回需要的数据return new ArrayList(){{add("单选");add("多选");add("判断");add("问答");}}.toArray(new String[]{});}}
11.最终效果
