private Long id;
@Excel(name = “姓名”, orderNum = “1”, width = 15)
private String name;
@Excel(name = “年龄”, orderNum = “2”, width = 15)
private Integer age;
@Excel(name = “性别”, replace = {“男_1”, “女_2”, “未知_3”}, orderNum = “3”, width = 15)
private Integer gender;
}
获取列名和列位置的映射
该类在初始化时,需要指定当前导出 Excel 对应的实体类的类类型,然后通过遍历类中字段的注解,生成字段和列排序(位置)的映射关系
public class FieldOrderMappingHelper {
/**
* 支持的最大字段数
*/
private final static int MAX_LIST_SIZE = 26;
public FieldOrderMappingHelper(Class pojo) {
this.pojo = pojo;
initMap();
}
/**
* 解析注释的 pojo 对象
*/
private Class pojo;
/**
* 字段和序号的映射关系
*/
private HashMap<String, Integer> fieldAndOrderMap;
/**
* 功能:初始化类的字段内容,建立字段和序号以及字段和 excel 列名的映射关系
* @author kangshuai@gridsum.com
* @date 2021/4/9 12:06
*/
private void initMap() {
HashMap<String, Integer> fieldAndOrderMap = new HashMap<>(16);
HashSet existOrderNumSet = new HashSet<>(16);
List list = new ArrayList<>();
list = initList(list, pojo);
if (list.size() > MAX_LIST_SIZE) {
throw new RuntimeException(pojo.getName() + “目前最大支持 26 个字段,26+ 需要改代码”);
}
// 排序
list.sort(Comparator.comparing(FiledAndOrder::getOrder));
for (int i = 0; i < list.size(); i++) {
if (existOrderNumSet.contains(list.get(i).getOrder())) {
throw new RuntimeException(pojo.getName() + “类内部或与父类字段中存在重复的 excel 排序,请修改”);
}
existOrderNumSet.add(list.get(i).getOrder());
fieldAndOrderMap.put(list.get(i).getFiledName(), i);
}
this.fieldAndOrderMap = fieldAndOrderMap;
}
/**
* 功能:初始化类的字段信息,转换成 ArrayList
* @return java.util.List<com.gridsum.ad.ooh.project.entity.FiledAndOrder>
* @author kangshuai@gridsum.com
* @date 2021/4/9 12:09
*/
private List initList(List list, Class<?> pojoClass) {
if (Object.class.equals(pojoClass)) {
return list;
}
Field[] fields = pojoClass.getDeclaredFields();
for (Field f : fields) {
// 找到所有加了 Excel 注解的字段
Excel annotation = f.getAnnotation(Excel.class);
if (annotation == null) {
continue;
}
// 过滤隐藏行
if (annotation.isColumnHidden()) {
continue;
}
FiledAndOrder filedAndOrder = new FiledAndOrder(f.getName(), Integer.parseInt(annotation.orderNum()));
list.add(filedAndOrder);
}
// 递归查找父类
Class<?> superclass = pojoClass.getSuperclass();
return initList(list, superclass);
}
public HashMap<String, Integer> getFieldAndOrderMap() {
return fieldAndOrderMap;
}
}
设置验证规则
setValidation 方法有两个参数,第一个是导出 Excel 对应的实体类的类类型,第二个是 FieldOrderMappingHelper.getFieldAndOrderMap()
获取到的列名和排序映射,该类通过反射字段上的注解,自动为生成的 workbook 添加验证规则
public class ExcelStyleHelper {
/**
* 添加列值验证的最小行
*/
public static final int EXCEL_VALID_ROW_MIN = 1;
/**
* 添加列值验证的最大行
*/
public static final int EXCEL_VALID_ROW_MAX = (2 << 15) - 1;
/**
* Excel 对象
*/
private Workbook workbook;
/**
* Sheet 页,默认取第一个 sheet 页
*/
private Sheet sheet;
public ExcelStyleHelper(Workbook workbook) {
this.workbook = workbook;
this.sheet = workbook.getSheetAt(0);
}
/**
* 功能:单元格添加下拉框,仅支持 xls
* @author kangshuai@gridsum.com
* @date 2021/4/8 18:55
*/
public void setValidation(Class<?> pojoClass, HashMap<String, Integer> map) {
// 递归到 Object 就停下
if (Object.class.equals(pojoClass)) {