注解的方式导出excel 数据
业务场景：使用注解的方式导出excel数据
第一步：新建一个列的注解
第二步：新建类对应注解类
第三步：调用
第四步：工具类
/**
* 数据导出
*
* @param dataList 数据信息
* @param <T> 数据类型
*/
@SneakyThrows
public static <T> void dataExcelResponse(List<T> dataList,HttpServletResponse response) {
Class<?> aClass = dataList.get(0).getClass();
ExcelFileHead fileHead = getFileHead(aClass);
ExcelWriter writer = dataWriter(dataList, fileHead);
String finalFileName = fileHead.fileName() + DateUtil.today() + ".xlsx";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=\"" + URLEncoder.encode(finalFileName, StandardCharsets.UTF_8.name()) + "\"");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
writer.close();
IoUtil.close(out);
}
/**
* 获取对象文件导出头信息
*
* @param data
* @return
*/
public static ExcelFileHead getFileHead(Class<?> data) {
return data.getAnnotation(ExcelFileHead.class);
}
/**
* 组装数据到单元格
*
* @param dataList 数据信息
* @param fileHead 单元格头信息
* @param <T> 类型
* @return
*/
public static <T> ExcelWriter dataWriter(List<T> dataList, ExcelFileHead fileHead) {
return dataWriter(null, dataList, fileHead);
}
/**
* 组装数据到单元格
*
* @param writer 单元格对象
* @param dataList 数据信息
* @param fileHead 单元格头信息
* @param <T> 类型
* @return
*/
public static <T> ExcelWriter dataWriter(ExcelWriter writer, List<T> dataList, ExcelFileHead fileHead) {
Class<?> aClass = dataList.get(0).getClass();
Map<String, Field> fieldMap = ReflectUtil.getFieldMap(aClass);
writer = getWriterWithSheet(writer, fileHead);
writer.clearHeaderAlias();
addHeaderAlias(fieldMap, writer, dataList.size(), fileHead.maxCol() > 0);
List<Map<String, Object>> result = dataList.stream().map(item -> getDate(item, fieldMap)).collect(Collectors.toList());
writer.write(result, true);
return writer;
}
/**
* 获取文件对象
*
* @param writer 文件对象
* @param excelFileHead 文件头信息定义
* @return
*/
public static ExcelWriter getWriterWithSheet(ExcelWriter writer, ExcelFileHead excelFileHead) {
if (writer == null) {
writer = ExcelUtil.getWriterWithSheet(excelFileHead.fileName());
} else {
writer = writer.setSheet(excelFileHead.fileName());
}
writer.setDefaultRowHeight(excelFileHead.defaultRowHeight());
merge(writer, excelFileHead);
return writer;
}
/**
* 增加标题别名
*
* @param fieldMap 字段对象
* @param writer 单元格对象
* @param size 数据大小
*/
public static void addHeaderAlias(Map<String, Field> fieldMap, ExcelWriter writer, Integer size, Boolean isHeaderLine) {
AtomicInteger index = new AtomicInteger();
fieldMap.values().stream()
.filter(item -> item.getAnnotation(ExcelColumn.class) != null)
.sorted(Comparator.comparing(item -> item.getAnnotation(ExcelColumn.class).col()))
.forEach(item -> {
ExcelColumn annotation = item.getAnnotation(ExcelColumn.class);
if (annotation != null) {
writer.addHeaderAlias(item.getName(), annotation.value());
writer.addValidationData(setValidationDataBo(annotation.value(), writer, index.get(), isHeaderLine));
if (annotation.validationData().length > 0) {
writer.addValidationData(setValidationDataSize(annotation, writer, index.get(), size));
}
writer.setColumnWidth(index.get(), annotation.width());
index.getAndIncrement();
}
});
}
/**
* 设置数据校验规则
*
* @param annotation 字段注解
* @param writer 单元格信息
* @param index 数据所在列
* @param size 行
* @return
*/
public static DataValidation setValidationDataSize(ExcelColumn annotation, ExcelWriter writer, Integer index, Integer size) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) writer.getSheet());
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(annotation.validationData());
CellRangeAddressList addressList = new CellRangeAddressList(2, size + 1, index, index);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
validation.createErrorBox("温馨提示", annotation.validationMessage());
return validation;
}
/**
* 设置数据校验规则
*
* @param value 字段名称
* @param writer 单元格信息
* @param index 数据所在列
* @return
*/
public static DataValidation setValidationDataBo(String value, ExcelWriter writer, int index, Boolean isHeaderLine) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) writer.getSheet());
String[] strings = {value};
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strings);
int firstRow = isHeaderLine ? 1 : 0;
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, firstRow, index, index);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
validation.createErrorBox("温馨提示", "标题行不可编辑");
return validation;
}
/**
* 组装数据
*
* @param data 数据信息
* @param fieldMap 字段信息
* @param <T> 数据类型
* @return 数据
*/
public static <T> Map<String, Object> getDate(T data, Map<String, Field> fieldMap) {
Map<String, Object> map = new LinkedHashMap<>();
fieldMap.forEach((key, value) -> {
ExcelColumn annotation = value.getAnnotation(ExcelColumn.class);
if (annotation != null) {
value.setAccessible(true);
Object o = null;
try {
o = value.get(data);
} catch (IllegalAccessException ignored) {
}
if (o instanceof BigDecimal) {
map.put(key, new BigDecimal(o.toString()).setScale(annotation.decimalPlaces(), BigDecimal.ROUND_DOWN));
} else {
String replace = annotation.replace();
Map<String, String> keyValueMap = new HashMap<>();
if (o != null && StrUtil.isNotEmpty(replace)) {
String[] split = replace.split(",");
Arrays.stream(split).forEach(item -> {
String[] keyValue = item.split(":");
if (keyValue.length == 2) {
keyValueMap.put(keyValue[0], keyValue[1]);
}
});
String valueStr = keyValueMap.get(o.toString());
map.put(key, valueStr == null ? o : valueStr);
} else {
Object temp = o;
if (o instanceof Number && o.toString().length() > 15) {
temp = o.toString();
}
map.put(key, temp == null ? "" : temp);
}
}
}
});
return map;
}
/**
* 合并头文件标题单元格
*
* @param writer 文件对象流
* @param excelFileHead 文件头信息定义
*/
public static void merge(ExcelWriter writer, ExcelFileHead excelFileHead) {
if (excelFileHead.maxCol() > 0) {
writer.merge(excelFileHead.maxCol()-1, excelFileHead.fileName());
}
}
以上代码为完成代码，可以直接用；
