1.導(dǎo)入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 {
/**
* 創(chuàng)建即將導(dǎo)出的sheet頁（sheet頁中含有帶下拉框的列）
* @param head 導(dǎo)出的表頭信息和配置
* @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<下拉框列索引, 下拉框內(nèi)容> 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) {
// 這里可以對(duì)cell進(jìn)行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
selectedMap.forEach((k, v) -> {
// 設(shè)置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
// 設(shè)置下拉列表的值
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 設(shè)置約束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止輸入非下拉選項(xiàng)的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "請(qǐng)輸入下拉選項(xiàng)中的內(nèi)容");
sheet.addValidationData(validation);
});
}
}
3 .創(chuàng)建實(shí)體類
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;
/**
* 章節(jié)名稱
*/
@ExcelProperty(index = 0,value ="課程章節(jié)名稱")//表頭名稱
@ExcelSelected(sourceClass = ChapterSelected.class)
private String chapterName;
/**
* 實(shí)驗(yàn)名稱
*/
@ExcelProperty(index = 1,value ="實(shí)驗(yàn)名稱")
@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.導(dǎo)入自定義注解ExcelSelected
package org.springblade.modules.system.EuipmentExcelImplement;
import java.lang.annotation.*;
/**
* 標(biāo)注導(dǎo)出的列為下拉框類型，并為下拉框設(shè)置內(nèi)容
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
/**
* 固定下拉內(nèi)容
*/
String[] source() default {};
/**
* 動(dòng)態(tài)下拉內(nèi)容
*/
Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
/**
* 設(shè)置下拉框的起始行，默認(rèn)為第二行
*/
int firstRow() default 1;
/**
* 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行
*/
int lastRow() default 0x10000;
}
5.導(dǎo)入Excel選擇解析-ExcelSelectedResolve?
package org.springblade.modules.system.EuipmentExcelImplement;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
@Data
@Slf4j
public class ExcelSelectedResolve {
/**
* 下拉內(nèi)容
*/
private String[] source;
/**
* 設(shè)置下拉框的起始行，默認(rèn)為第二行
*/
private int firstRow;
/**
* 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 獲取固定下拉框的內(nèi)容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 獲取動(dòng)態(tài)下拉框的內(nèi)容
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("解析動(dòng)態(tài)下拉框數(shù)據(jù)異常", e);
}
}
return null;
}
}
6.添加動(dòng)態(tài)選擇接口-ExcelDynamicSelect
package org.springblade.modules.system.EuipmentExcelImplement;
public interface ExcelDynamicSelect {
/**
* 獲取動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
* @return 動(dòng)態(tài)生成的下拉框可選數(shù)據(jù)
*/
String[] getSource();
}
7.編寫自己需要展示下拉框?qū)崿F(xiàn)-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.編寫查詢下拉框顯示內(nèi)容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.也可以不查詢數(shù)據(jù)庫，直接再接口返回需要的數(shù)據(jù)。
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() {
//直接返回需要的數(shù)據(jù)
return new ArrayList<String>(){{add("單選");add("多選");add("判斷");add("問答");}}.toArray(new String[]{});
}
}
11.最終效果
文章來源：http://www.zghlxwxcb.cn/news/detail-508027.html
?
文章來源地址http://www.zghlxwxcb.cn/news/detail-508027.html
到了這里，關(guān)于Java 導(dǎo)出Excel表格生成下拉框-EasyExcel的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
