背景
项目需求要把某些用户数据通过下拉框的形式带出,供用户手动选择与自己相关的数据,从而实现excel的数据权限。由于字段有两个,后面可能更多,方便起见,使用自定义注解的形式,动态配置,并且动态实现下拉数据。
依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>5.0.0</version>
</dependency>
实现
1.创建动态配置
public interface ExcelDynamicSelect {
/**
* 获取动态生成的下拉框可选数据
* @return 动态生成的下拉框可选数据
*/
String[] getSource();
}
2.创建动态导出自定义注解,用来制定动态导出项
/**
* 标注导出的列为下拉框类型,并为下拉框设置内容
*/
@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;
}
3.设置动态解析类
@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;
}
}
4.创建动态解析拦截器
@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);
// 设置下拉列表的值
int sheetTotal = sheet.getWorkbook().getNumberOfSheets();
List<String> source = Arrays.asList(v.getSource());
//这里**为了防止下拉框超过255限制,将数据先放在一个新建的sheet页,设置约束时再把值拿回来
String name = k.toString();
Sheet hidden = sheet.getWorkbook().createSheet(name);
for (int i = 0; i < source.size(); i++) {
Row row = hidden.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue(source.get(i));
}
sheet.getWorkbook().setSheetHidden(sheetTotal, true);
String strFormula = name + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
}
5.创建导出工具类
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;
}
}
6.测试验证
编写导出实体类,并在需要动态导出的字段上使用自定义注解
@Data
public class DataExcel {
/**
* 工号
*/
@ExcelProperty(index = 0,value ="工号")
private String userCode;
/**
* 产业
*/
@ExcelProperty(index = 1,value ="产业")
@ColumnWidth(25)
@ExcelSelected(sourceClass = IndustrySelected.class)
private String industry;
/**
* 设计
*/
@ExcelProperty(index = 2,value ="设计")
@ColumnWidth(25)
@ExcelSelected(sourceClass = DesignMicroSelected.class)
private String designMicro;
}
编写动态导出源数据获取
public class IndustrySelected implements ExcelDynamicSelect {
@Override
public String[] getSource() {
UtilsDao bean = SpringContextUtil.getBean(UtilsDao.class);
return bean.queryNames().toArray(new String[]{});
}
}
public class DesignMicroSelected implements ExcelDynamicSelect {
@Override
public String[] getSource() {
UtilsDao bean = SpringContextUtil.getBean(UtilsDao.class);
return bean.queryNames2().toArray(new String[]{});
}
}
bean.queryNames()和bean.queryNames2()是数据库查询语句,查询的是要导出的列
编写controller实现接口调用导出
@RestController
@RequestMapping("/util")
public class UtilController {
@RequestMapping("/report")
public void report(HttpServletRequest request, HttpServletResponse response){
String fileName = "test";
ExcelWriter writer = null;
WriteSheet test = EasyExcelUtil.writeSelectedSheet(DataExcel.class, 1, "test");
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //设置响应内容类型
response.setCharacterEncoding("utf-8");//编码
// 设置文件名, ps:把字符串中所有的'+'替换成'%20',在URL中%20代表空格
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//设置响应头
response.setHeader("fileName", fileName + ".xlsx");//设置响应头
/** 导出的数据集合 */
List<DataExcel> modelList = new ArrayList<>();
/** registerWriteHandler(合并行数,那些列需要合并) */
WriteSheet sheet = test;
writer = EasyExcel.write(response.getOutputStream(), DataExcel.class).build();//获取写出流
writer.write(modelList, sheet);//读出
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
if (null != writer) {
writer.finish();//关闭流
}
}
}
}
至此已经实现动态下拉框的数据导出,并且下拉数据没有被255字符所限制。
额外
思考
在动态获取下拉数据源的IndustrySelected 和 DesignMicroSelected 中,最开始时是使用@Autowired的方式注入UtilsDao,但是发现实际使用时,却出现了空指针,发现没有获取到UtilsDao对象,但是bean容器中也确实有。后来查阅资料,发现很多情景会出现这个情况
一般无法@Autowired无法注入的情况:
1.该对象在使用过程中,使用了new方式创建对象
2.该对象没有使用spring注入类注解
3.没有被spring包扫描到
由于我的UtilsDao是继承了mybatisplus的BaseMapper,在excel某个拦截器中出现了new的方式,所以无法注入。
以上只是个人猜想,如有错误,欢迎指正。