目录
1批注相关
1.1 批注内容标注注解
/**
* excel批注内容标注注解
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelRemark {
/**
* 文本内容
*/
String value( ) default "";
/**
* 批注行高, 一般不用设置
* 这个参数可以设置不同字段 批注显示框的高度
*/
int remarkRowHigh() default 0;
/**
* 批注列宽, 根据导出情况调整
* 这个参数可以设置不同字段 批注显示框的宽度
*/
int remarkColumnWide() default 0;
}
1.2 批注内容存储使用实例
/**
* 批注使用实体列
*/
@Data
public class ExcelComment {
/** 列号 */
private Integer column;
/** 批注值 */
private String remarkValue;
/** 批注行高 */
int remarkRowHigh;
/** 批注列宽 */
int remarkColumnWide;
/**
* 批注所在行
*
* @return int
*/
int row;
}
1.3 注释单元格写入处理器
/**
* excel导出批注处理类
*/
public class CommentCellWriteHandler implements CellWriteHandler {
/**
* 批注
*/
private final Map<Integer, ExcelComment> notationMap;
public CommentCellWriteHandler(Map<Integer, ExcelComment> notationMap) {
this.notationMap = notationMap;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里使用isHead判断 是否是表头的时候为true ,填入数据的时候是false , 之前使用head判断,调整表头备注只能有一个,切记
if (isHead) {
Sheet sheet = writeSheetHolder.getSheet();
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
if (!CollectionUtils.isEmpty(notationMap) && notationMap.containsKey(cell.getColumnIndex())) {
// 批注内容
ExcelComment excelComment = notationMap.get(cell.getColumnIndex());
if (Objects.nonNull(excelComment)) {
// 创建绘图对象
Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0,
(short) excelComment.getRemarkColumnWide(), 1));
comment.setString(new XSSFRichTextString(excelComment.getRemarkValue()));
cell.setCellComment(comment);
}
}
}
}
/**
* 获取批注Map
*
* @param clazz 类class
* @return java.util.Map<java.lang.Integer, java.lang.String>
* @author SunLingDa
* @date 2022/11/3 13:24
*/
public static Map<Integer, ExcelComment> getNotationMap(Class<?> clazz) {
Map<Integer, ExcelComment> notationMap = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
//如果实体使用了index索引注解 即可不使用方式 直接从注解去拿出来index
int index = -1;
for (Field field : fields) {
// ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
// index = excelProperty.index();
++index;
if (!field.isAnnotationPresent(ExcelRemark.class)) {
//不需要批注 并且 是无需导出字段则 索引回归
if (field.isAnnotationPresent(ExcelIgnore.class)) {
--index;
}
continue;
}
//批注存放实体
ExcelComment excelComment = new ExcelComment();
//获取字段批注注解
ExcelRemark ExcelRemark = field.getAnnotation(ExcelRemark.class);
excelComment.setRemarkValue(ExcelRemark.value());
excelComment.setRemarkColumnWide(ExcelRemark.remarkColumnWide());
notationMap.put(index, excelComment);
}
return notationMap;
}
}
2 转换器
2.1 LocalDate时间转换
public abstract class LocalDateConvert implements Converter<LocalDateTime> {
//只显示到月
public static final String PATTERN_YYYY_MM = "yyyy-MM";
//显示到天
public static final String PATTERN_YYYY_MM_DD = "yyyy-MM-dd";
//全量显示
public static final String PATTERN_YYYY_MM_DD_MM = "yyyy-MM-dd HH:mm:ss";
public static LocalDateConvert getDateConvert(Integer dateType) {
return switch (dateType) {
case 1 -> new LocalDateDayConverter();
case 2 -> new LocalDateMonthConverter();
default -> new LocalDateTimeConverter();
};
}
public static LocalDateConvert getDateConvert() {
return getDateConvert(Integer.MAX_VALUE);
}
}
class DateDayConverter extends LocalDateConvert {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public WriteCellData<String> convertToExcelData(WriteConverterContext<LocalDateTime> context) {
LocalDateTime date = context.getValue();
if (date == null) {
return null;
}
return new WriteCellData<>(date.format(DateTimeFormatter.ofPattern(PATTERN_YYYY_MM_DD)));
}
}
class DateMonthConverter extends LocalDateConvert {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public WriteCellData<String> convertToExcelData(WriteConverterContext<LocalDateTime> context){
LocalDateTime date = context.getValue();
if (date == null) {
return null;
}
return new WriteCellData<>(date.format(DateTimeFormatter.ofPattern(PATTERN_YYYY_MM)));
}
}
class DateTimeConverter extends LocalDateConvert {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public WriteCellData<String> convertToExcelData(WriteConverterContext<LocalDateTime> context) {
LocalDateTime date = context.getValue();
if (date == null) {
return null;
}
return new WriteCellData<>(date.format(DateTimeFormatter.ofPattern(PATTERN_YYYY_MM_DD_MM)));
}
}
2.2 枚举转换器
public class EnumsConverter implements Converter<CodeEnums> {
@Override
public Class<CodeEnums> supportJavaTypeKey() {
return CodeEnums.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<?> convertToExcelData(CodeEnums value, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
return new WriteCellData<>(value.getText());
}
}
3导出工具类
/**
* Excel 工具类
*/
public class ExcelUtils {
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param filename 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param <T> 泛型,保证 head 和 data 类型的一致性
* @param converters 转换器动参 用于动态注册 转换器
* @throws IOException 写入失败的情况
*/
public static <T> void write(HttpServletResponse response, String filename, String sheetName,
Class<T> head, List<T> data, Converter... converters) throws IOException {
ExcelWriterBuilder writerBuilder;
//不在这边关流 其他地方还要使用的话 就把入参换成输出流 然后在用完的地方关掉
try (ServletOutputStream outputStream = response.getOutputStream()) {
// 设置 header 和 contentType
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
// 输出 Excel
writerBuilder = EasyExcel.write(outputStream, head);
if (!Objects.isNull(converters)) {
Arrays.stream(converters).forEach(writerBuilder::registerConverter);
}
writerBuilder
.inMemory(true)
// 不要自动关闭,交给 Servlet 自己处理
.autoCloseStream(false)
// 基于 column 长度,自动适配。最大 255 宽度
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(sheetName)
.doWrite(data);
}
}
/**
* 简单读取方法 目前都有监听那一套 不用这个
*
* @param file 传入文件 excel
* @param head 解析DTO
* @param <T> 泛型
* @return 解析DTO集合
* @throws IOException io异常
*/
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
//不在这边关流 其他地方还要使用的就把入参换成输入流 然后在用完的地方关掉
try (InputStream inputStream = file.getInputStream()) {
return EasyExcel.read(inputStream, head, null)
.autoCloseStream(false) // 不要自动关闭,交给 Servlet 自己处理
.doReadAllSync();
}
}
public static String buildFileName(String prefix) {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
return URLEncoder.encode(prefix + "-" + dateTimeFormatter.format(LocalDateTime.now()), StandardCharsets.UTF_8);
}
4 使用样例
4.1 导出实体
注: 使用工具导出的话, 宽度设置了自适应, 宽度注解可以不用
@Data
public class CExportDto {
@ColumnWidth(35)
@ExcelProperty(value = "XXX名称",index = 0)
@ExcelRemark(value = remark)
private String xxxName;
@ExcelIgnore
private String remark="批注示例:XXX名称";
@ColumnWidth(35)
@ExcelProperty(value = "XX名称")
private String xxName;
@ColumnWidth(35)
@ExcelProperty(value = "地区",converter = EnumsConverter.class)
private String areaNo;
@ColumnWidth(35)
@ExcelProperty(value = "到期时间")
private LocalDateTime expireTime;
}
4.2 导出接口
注: 业务侧代码还是方法service层好点,异常也是在service层就处理掉
4.2.1 直接导出样例
@PostMapping(value = "/export")
public void export(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + "fileName" + ".xlsx");
List<CExportDto> export = new ArrayList<>();
//编点数据
LocalDateConvert dateConvert = LocalDateConvert.getDateConvert();
EasyExcel.write(response.getOutputStream(), CExportDto.class)
.inMemory(true)
//注册时间转换器对所有 符合类型生效 注解上放 只对当前字段生效
.registerConverter(dateConvert)
.sheet()
.doWrite(export);
} catch (IOException e) {
e.printStackTrace();
}
}
4.2.2 使用工具导出接口样例
@PostMapping(value = "/export")
public void export(HttpServletResponse response) {
try {
List<CExportDto> export = new ArrayList<>();
//编点数据
LocalDateConvert dateConvert = LocalDateConvert.getDateConvert(1);
ExcelUtils.write(response, ExcelUtils.buildFileName("导出文件名"), "导出文件清单页",
CExportDto.class, export,dateConvert, "其他转换器...");
} catch (IOException e) {
e.printStackTrace();
}
}