自定义注解:
import java.lang.annotation.*;
/**
* @author li
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {
//定义固定下拉内容
String[]source()default {};
//定义动态下拉内容,
Class[]sourceClass()default {};
}
自定义动态下拉框内容接口:
/**
* @author li
*/
public interface ExplicitInterface {
/**
* 下拉列表的内容数组
* @return
*/
String[] source();
}
动态下拉框内容实现类:
import main.java.demo.explicitinterface.ExplicitInterface;
public class RoleNameExplicitConstraint implements ExplicitInterface {
public String[] source() {
return new String[]{"角色1","角色2","角色3","角色4","角色5"};
}
}
定义导出对象:
import main.java.demo.annotation.ExplicitConstraint;
import main.java.demo.explicitinterface.imp.RoleNameExplicitConstraint;
public class UserModel {
@ExcelProperty("名字")
private String name;
@ExcelProperty("年龄")
private Integer age;
@ExplicitConstraint(source = {"男","女"}) //固定下拉内容
@ExcelProperty("性别")
private String sex;
@ExplicitConstraint(sourceClass = RoleNameExplicitConstraint.class) //动态下拉内容
private String roleName;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
导出:
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import main.java.demo.annotation.ExplicitConstraint;
import main.java.demo.explicitinterface.ExplicitInterface;
import main.java.demo.model.UserModel;
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.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
public class EasyExcelMain {
public static void main(String arg[]) throws IOException {
File file = new File("D:/test.xlsx");
file.createNewFile();
FileOutputStream fileOutputStream = new FileOutputStream(file);
// 处理下拉列表
//下拉列表集合
Map<Integer, String[]> explicitListConstraintMap = new HashMap<>();
//循环获取对应列得下拉列表信息
Field[] declaredFields = UserModel.class.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field field = declaredFields[i];
//解析注解信息
ExplicitConstraint explicitConstraint = field.getAnnotation(ExplicitConstraint.class);
String[] explicitArray = resolveExplicitConstraint(explicitConstraint);
if (explicitArray != null && explicitArray.length > 0) {
explicitListConstraintMap.put(i, explicitArray);
}
}
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, UserModel.class).registerWriteHandler(new SheetWriteHandler() {
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//通过sheet处理下拉信息
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
explicitListConstraintMap.forEach((k, v) -> {
CellRangeAddressList rangeList = new CellRangeAddressList();
CellRangeAddress addr = new CellRangeAddress(1, 1000, k, k);
rangeList.addCellRangeAddress(addr);
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
DataValidation validation = helper.createValidation(constraint, rangeList);
sheet.addValidationData(validation);
});
}
}).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
excelWriter.write(null,sheet).finish();
}
/**
* 解析注解内容 获取下列表信息
* @param explicitConstraint
* @return
*/
public static String[] resolveExplicitConstraint(ExplicitConstraint explicitConstraint){
if (explicitConstraint == null) {
return null;
}
//固定下拉信息
String[] source = explicitConstraint.source();
if (source.length > 0) {
return source;
}
//动态下拉信息
Class<? extends ExplicitInterface>[] classes = explicitConstraint.sourceClass();
if (classes.length>0){
ExplicitInterface explicitInterface = null;
try {
explicitInterface = classes[0].newInstance();
String[] source1 = explicitInterface.source();
if (source1.length>0){
return source1;
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return null;
}
}