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

1.导入Easyexcel，Maven依赖

                    com.alibaba            easyexcel            2.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 {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();}private static  Map resolveSelectedAnnotation(Class head) {Map 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@AllArgsConstructorpublic class SelectedSheetWriteHandler implements SheetWriteHandler {private final Map selectedMap;@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@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;@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 {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();}

          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    

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.最终效果

来源地址：https://blog.csdn.net/weixin_53335648/article/details/130484195