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

- 日理万妓 2024-03-22 11:52 68阅读 0赞

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

![035d2b93be2647bbafb9bd06a5ba8068.png][]

![4b878312d8d2401c9f87b98e5f4574f8.png][]

[035d2b93be2647bbafb9bd06a5ba8068.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/286c30ccfda44fc8af4af0adfdb2dd75.png
[4b878312d8d2401c9f87b98e5f4574f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4612e162fc8f4908afc1a4d17b7bc008.png