导出需求存在多样化，有时我们会需要在导出时新增几列，然后再进行导入更新操作；导出新增的列比如性别、城市等等，表头设置批注信息，提醒用户填写时的注意事项；单元格设置下拉框，让用户方便选择，减少不必要的填写错误；本文将介绍下easyexcel导出时如何设置单元格批注和下拉框列表。
二、实战案例基于平台实现
1、新增批注注解：ExcelAnnotation
import java.lang.annotation.Target;
import java.lang.annotation.Retention;
import java.lang.annotation.ElementType;
import java.lang.annotation.RetentionPolicy;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
/**
* 批注内容
*/
String value() default "";
}
2、新增下拉框注解：ExcelDropdown
import java.lang.annotation.Target;
import java.lang.annotation.Retention;
import java.lang.annotation.ElementType;
import java.lang.annotation.RetentionPolicy;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDropdown {
/**
* 下拉框内容
*/
String value() default "";
}
3、学生实体类（Student）
import lombok.Data;
import java.io.Serializable;
import com.alibaba.excel.annotation.ExcelProperty;
import com.easyexcel.demo.annotation.ExcelDropdown;
import com.easyexcel.demo.annotation.ExcelAnnotation;
@Data
public class Student implements Serializable {
private static final long serialVersionUID = 7685359843020686195L;
/**
* 主键值
*/
@ExcelProperty(value = "主键值", index = 0)
private Integer id;
/**
* 学生姓名
*/
@ExcelProperty(value = "学生姓名", index = 1)
private String stuName;
/**
* 学生编码
*/
@ExcelProperty(value = "学生编码", index = 2)
private String stuCode;
/**
* 性别
*/
@ExcelProperty(value = "性别", index = 3)
@ExcelAnnotation(value = "选择学生性别") // 单元格批注信息
@ExcelDropdown(value = "男,女") // 单元格下拉框
private String gender;
/**
* 城市名称
*/
@excelProperty(value = "城市名称", index = 4)
@ExcelAnnotation(value = "选择城市名称") // 单元格批注信息
private String cityName;
}
4、导出模板类改造（ExportTemplate）
解析单元格批注、和下拉框注解、并注册WriteHandler
import com.alibaba.fastjson.JSON;
import com.easyexcel.demo.entity.Student;
import lombok.extern.slf4j.Slf4j;
import com.alibaba.excel.ExcelWriter;
import com.google.common.collect.Maps;
import com.easyexcel.demo.so.ExportSo;
import com.alibaba.excel.EasyExcelFactory;
import com.easyexcel.demo.so.TaskDefinition;
import org.springframework.stereotype.Service;
import com.easyexcel.demo.entity.ExportTaskEntity;
import com.easyexcel.demo.mapper.ExportTaskMapper;
import com.alibaba.excel.annotation.ExcelProperty;
import com.easyexcel.demo.annotation.ExcelDropdown;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.easyexcel.demo.service.ExportBaseService;
import com.easyexcel.demo.annotation.ExcelAnnotation;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import java.util.Map;
import java.util.Date;
import java.util.List;
import java.util.Arrays;
import java.lang.reflect.Field;
import javax.annotation.Resource;
@Slf4j
@Service
public class ExportTemplate {
@Resource
private ExportTaskMapper exportTaskMapper;
public void exportData(ExportSo exportSo, String taskId) {
String businessTypeCode = exportSo.getBusinessTypeCode();
TaskDefinition taskDefinition = TaskDefinition.getTaskDefinition(businessTypeCode);
if (null == taskDefinition) {
throw new IllegalArgumentException("导出业务类型编码异常！");
}
String FileName = taskDefinition.getFileName();
String filePath = "D:\\excel\\" fileName ".xlsx";
ExportTaskEntity updateExportTaskEntity = new ExportTaskEntity();
updateExportTaskEntity.setTaskId(taskId);
updateExportTaskEntity.setFilePath(filePath);
updateExportTaskEntity.setUpdateTime(new Date());
updateExportTaskEntity.setTaskStatusCode(TaskStatusEnum.SUCCESS.getCode());
updateExportTaskEntity.setTaskStatusName(TaskStatusEnum.SUCCESS.getDesc());
try {
// 执行导出业务逻辑(查询数据、写入excel)
Class clazz = parseClazz(taskDefinition.getExportClass());
// 注意：此处需设置表头实体类对象：Student.class
ExcelWriter excelWriter = EasyExcelFactory.write(filePath, Student.class).build();
String exportService = taskDefinition.getExportService();
ExportBaseService exportBaseService = (ExportBaseService) SpringUtils.getBean(exportService);
Map<Integer, List<String>> dropdownDataMap = Maps.newHashMap();
Map<Integer, String> annotationDataMap = Maps.newHashMap();
// 解析列下拉框列表数据、和列批注数据
parseAnnotationData(clazz, dropdownDataMap, annotationDataMap);
// 注册ReportCellWriteHandler，且needHead设置为true
WriteSheet writeSheet = EasyExcelFactory.writerSheet(fileName).registerWriteHandler(new (dropdownDataMap, annotationDataMap)).needHead(true).build();
exportBaseService.export(exportSo, excelWriter, writeSheet);
excelWriter.finish();
} catch (Exception e) {
updateExportTaskEntity.setTaskStatusCode(TaskStatusEnum.FAIL.getCode());
updateExportTaskEntity.setTaskStatusName(TaskStatusEnum.FAIL.getDesc());
log.error("导出任务异常 任务ID: {} 导出参数: {} 异常信息: ", taskId, JSON.toJSONString(exportSo), e);
}
// 更新导出任务
exportTaskMapper.updateExportTask(updateExportTaskEntity);
}
private Class parseClazz(String exportClass) {
if (StringUtils.isBlank(exportClass)) {
throw new IllegalArgumentException("导出实体类全类名不可为空！");
}
Class clazz = null;
try {
clazz = Class.forName(exportClass);
} catch (ClassNotFoundException e) {
log.warn("parseClazz error: ", e);
}
if (null == clazz) {
throw new RuntimeException("clazz解析异常");
}
return clazz;
}
private void parseAnnotationData(Class clazz, Map<Integer, List<String>> dropdownDataMap, Map<Integer, String> annotationDataMap) {
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (!field.isAnnotationPresent(ExcelProperty.class)) {
continue;
}
// 解析列索引
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
int index = excelProperty.index();
if (field.isAnnotationPresent(ExcelDropdown.class)) {
// 解析下拉框列表
ExcelDropdown excelDropdown = field.getAnnotation(ExcelDropdown.class);
dropdownDataMap.put(index, Arrays.asList(excelDropdown.value().split(",")));
}
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
// 解析列批注
ExcelAnnotation excelAnnotationComment = field.getAnnotation(ExcelAnnotation.class);
annotationDataMap.put(index, excelAnnotationComment.value());
}
}
}
}
5、新增ReportCellWriteHandler（实现CellWriteHandler接口，重写afterCellDispose方法）
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import com.alibaba.excel.metadata.Head;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.metadata.data.WriteCellData;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.commons.collections4.CollectionUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import java.util.Map;
import java.util.List;
@Slf4j
public class ReportCellWriteHandler implements CellWriteHandler {
/**
* 下拉框数据
*/
private final Map<Integer, List<String>> dropDownDataMap;
/**
* 批注数据
*/
private final Map<Integer, String> annotationDataMap;
public ReportCellWriteHandler(Map<Integer, List<String>> dropDownDataMap, Map<Integer, String> annotationDataMap) {
this.dropDownDataMap = dropDownDataMap;
this.annotationDataMap = annotationDataMap;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder,
WriteTableHolder writeTableHolder,
List<WriteCellData<?>> cellDataList,
Cell cell,
Head head,
Integer relativeRowIndex,
Boolean isHead) {
if (null == cell || null == cell.getRow()) {
return;
}
try {
if (cell.getRow().getRowNum() != 0) { // 非表头处理
// 设置下拉框数据
dealDropdownData(writeSheetHolder, cell.getRowIndex(), cell.getColumnIndex());
} else { // 表头处理
// 设置批注信息
dealAnnotationData(writeSheetHolder, cell);
}
} catch (Exception e) {
log.warn("afterCellDispose error: ", e);
}
}
/**
* 设置下拉框数据
*
* @param writeSheetHolder sheet holder
* @param rowIndex 行号
* @param columnIndex 列号
*/
private void dealDropdownData(WriteSheetHolder writeSheetHolder, int rowIndex, int columnIndex) {
if (dropDownDataMap.isEmpty() || CollectionUtils.isEmpty(dropDownDataMap.get(columnIndex))) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// 设置下拉列表的行： 首行，末行（目前限制3w），首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, 30000, columnIndex, columnIndex);
// 设置下拉列表的值
DataValidationConstraint constraint;
// 直接设置下拉选
constraint = helper.createExplicitListConstraint(dropDownDataMap.get(columnIndex).toArray(new String[0]));
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
}
private void dealAnnotationData(WriteSheetHolder writeSheetHolder, Cell cell) {
if (annotationDataMap.isEmpty() || StringUtils.isBlank(annotationDataMap.get(cell.getColumnIndex()))) {
return;
}
int columnIndex = cell.getColumnIndex();
int rowIndex = cell.getRowIndex();
Drawing<?> drawing = writeSheetHolder.getSheet().createDrawingPatriarch();
Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex, columnIndex 5, rowIndex 5));
comment.setString(new XSSFRichTextString(annotationDataMap.get(columnIndex)));
cell.setCellComment(comment);
}
}
6、发送测试请求
接口：
http://localhost:8008/export/addExportTaskInfo
POST请求
参数：
{
"businessTypeCode": "1", // 导出任务定义中定义的业务类型枚举
"exportParam": { // 这里是具体的业务参数；比如根据城市导出学生信息
}
}
