SpringBoot集成阿里EasyExcel导出excel高级实战

Blueeyedboy521

已于 2023-04-19 08:55:26 修改

阅读量1.9k

点赞数

分类专栏：开发组件文章标签： spring boot excel java

于 2022-12-12 08:00:00 首次发布

本文链接：https://blog.csdn.net/Blueeyedboy521/article/details/128257388

版权

开发组件专栏收录该内容

54 篇文章 6 订阅

订阅专栏

参考

easyexcel使用教程-导出篇

一、引入包


<!--easyexcel -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.2.6</version>
 </dependency>

二、导出到文件并输出到后台



import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.SneakyThrows;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

/**
 * excel导出
 * @create 2022-12-09
 */
public class ExcelUtil {

    private ExcelWriter excelWriter;
    private  File file;
    private String fileName;
    private HttpServletResponse response;
    private WriteSheet writeSheet;


    @SneakyThrows
    public static <T> ExcelUtil create(HttpServletResponse response,String fileNamePrefix,  Class<T> excelModeClass){
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.response = response;
        excelUtil.fileName = fileNamePrefix+ ".xlsx";
        // 临时文件
        String filePath =  "/" +fileNamePrefix+ System.currentTimeMillis() + ".xlsx";
        excelUtil.file = new File(filePath);
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        excelUtil.excelWriter = EasyExcel.write(filePath, excelModeClass).build();
        excelUtil.writeSheet = EasyExcel.writerSheet("第一页").build();

            // 写入数据
        return excelUtil;
    }

    @SneakyThrows
    public void export(){
            ServletOutputStream out = response.getOutputStream();

            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();

            // 导出
            String fileName = new String(this.fileName
                    .getBytes(StandardCharsets.UTF_8), "iso8859-1");
            response.setHeader("Content-disposition", "attachment;filename="+fileName);
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            //4.获取要下载的文件输入流
            InputStream in = Files.newInputStream(Paths.get(this.file.getPath()));
            int len;
            //5.创建数据缓冲区
            byte[] buffer = new byte[1024];
            //6.通过response对象获取OutputStream流
            //7.将FileInputStream流写入到buffer缓冲区
            while ((len = in.read(buffer)) > 0) {
                //8.使用OutputStream将缓冲区的数据输出到客户端浏览器
                out.write(buffer,0,len);
            }
            in.close();
            this.file.deleteOnExit();
            out.flush();
    }


    @SneakyThrows
    public <T> void writeData(List<T> data){
        excelWriter.write(data, writeSheet);
    }
}

三、过滤字段

过滤字段不生成excel

方式1：类上加注解 @ExcelIgnoreUnannotated，过滤属性没有@ExcelProperty注解的字段

@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor  // 一定要有无参构造方法
@ExcelIgnoreUnannotated
public class Student {
    .....
}

方式2：指定字段加注解

@ExcelIgnore // demo2不生成excel
private String demo2;

方式3：代码指定过滤字段, 同一个excel生成两个sheet分别过滤不同字段

/**
 * 过滤字段
 */
@Test
public void exportExcludeColumn() {
    Consumer<ExcelWriter> consumer = writer ->
            writer.write(generateStudent(10), EasyExcel.writerSheet(1, "学生信息")
                    .excludeColumnFiledNames(Arrays.asList("name", "sex")) // sheet1过滤姓名、性别
                    .head(Student.class)
                    .build());
    consumer = consumer.andThen(writer ->
            writer.write(generateStudent(10), EasyExcel.writerSheet(2, "学生信息2")
                    .excludeColumnFiledNames(Arrays.asList("birthday", "weight")) // sheet2过滤生日和体重
                    .head(Student.class)
                    .build()));
    export("D:/报表.xlsx", consumer);

四、冻结列

冻结列, 冻结姓名列

冻结列handler,FreezeNameHandler.java

package com.learning.easyexcel.converter;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * 冻结姓名列
 */
public class FreezeNameHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取到当前的sheet
        Sheet sheet = writeSheetHolder.getSheet();
        /**
         *第一个参数：冻结的列数
         *第二个参数：冻结的行数
         *第三个参数：冻结后第一列的列号
         *第四个参数：冻结后第一行的行号
         **/
        sheet.createFreezePane(1, 0, 1, 0);
    }
}

注册handler

  /**
     * 冻结姓名列
     */
    @Test
    public void exportFreezeColumn() {
        Consumer<ExcelWriter> consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .registerWriteHandler(new FreezeNameHandler()) // 冻结姓名列
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);

五、格式化

把保留2位小数

方法1，@NumberFormat 注解。修改Student类，如下做法会以字符串导出到excel，单元格靠左

@ExcelProperty(value = "体重KG")
@NumberFormat("0.##") // 会以字符串形式生成单元格，要计算的列不推荐
private BigDecimal weight;

方法2：@ContentStyle(dataFormat = 2) 注解，我们新建一个字段weight2，会以数字导出，单元格中靠右

@ContentStyle(dataFormat = 2)
private BigDecimal weight2;

统一数字转换器

package com.test.easyexcel.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.math.BigDecimal;
import java.math.RoundingMode;

public class BigDecimalConverter implements Converter<BigDecimal> {

    @Override
    public Class supportJavaTypeKey() {
        return BigDecimal.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    @Override
    public BigDecimal convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return cellData.getNumberValue();
    }

    @Override
    public CellData convertToExcelData(BigDecimal value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.setScale(2, RoundingMode.DOWN));
    }

枚举转换器

public class StatusConverter implements Converter<Integer> {

    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return "正常".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        return new CellData<>(integer.equals(1) ? "正常" : "异常");
    }
}

自定义格式转换器

@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelPropertyExt {
    String expression() default "";
}

自定义枚举转换器

/*
* 定义枚举类的实现接口
*/
public interface BaseEnum {
    /**
     * 获取枚举值
     * @return 值
     */
    Integer getValue();
    /**
     * 获取枚举name
     * @return name
     */
    String getName();

    /**
     * excel导出时需要根据 value值计算枚举名称
     *
     * @param value 值
     * @return 名称
     */
    static String getNameByValue(Integer value) {
        return null;
    }
}

/*
* 定义枚举类实现接口
*/
public enum EnvironmentQualityLevelEnum implements BaseEnum {
    /**
     * 优
     */
    ONE(1, "优",50),
    /**
     * 良
     */
    TWO(2, "良",100);

    private final Integer value;
    private final String name;
    private final Integer thresholdValue;


    EnvironmentQualityLevelEnum(Integer value, String name, Integer thresholdValue) {
        this.value = value;
        this.name = name;
        this.thresholdValue = thresholdValue;

    }

    public static String getNameByValue(Integer value) {
        EnvironmentQualityLevelEnum type = getType(value);
        return type == null?null:type.getName();
    }
}
/*
* 定义注解
*/
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelPropertyExt {
    String expression() default "";
    Class<? extends BaseEnum> enumType() default BaseEnum.class;

}
/*
* 定义导出注解字段
*/
@ColumnWidth(10)
    @ExcelPropertyExt(enumType= EnvironmentQualityLevelEnum.class)
    @ExcelProperty(value = "AQI类别",converter = CommonEnumConverter.class)
    protected Integer qualityLevel;

public class CommonEnumConverter implements Converter<Integer> {

    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return "正常".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        String value = integer.toString();
        Field field = excelContentProperty.getField();
        ExcelPropertyExt annotation = field.getAnnotation(ExcelPropertyExt.class);
        try {
            Class<? extends BaseEnum> convertClazz = annotation.enumType();
            Method method = convertClazz.getMethod("getNameByValue",Integer.class);
            value = String.valueOf(method.invoke(null,integer));
        } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            throw new RuntimeException(e);
        }
        return new CellData<>(value);
    }
}

自定义转换器


import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;

/**
 * easyExcel枚举转换
 * @create 2022-12-07
 */
public class CommonIntegerConverter implements Converter<Integer> {

    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return "正常".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        String value = integer.toString();
        Field field = excelContentProperty.getField();
        ExcelPropertyExt annotation = field.getAnnotation(ExcelPropertyExt.class);
        if(annotation != null){
            Method[] meth = annotation.annotationType().getDeclaredMethods();
            for(Method me : meth){
                if(!me.isAccessible()){
                    me.setAccessible(true);
                }
                try {
                    //给字段重新赋值
                    String expression = (String) me.invoke(annotation);
                    List<String> list =  StringUtil.split(expression, SymbolConstants.SEMICOLON);
                    for(String dic : list){
                        List<String> items = StringUtil.split(dic, SymbolConstants.C_COMMA);
                        String key = items.get(0);
                        String v = items.get(1);
                        if(value.equals(key)) {
                            value = v;
                            break;
                        }
                    }
//                    System.out.println("expression:"+expression);
                } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        return new CellData<>(value);
    }
}

上面的内容可以简化为

 public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        String value = integer.toString();
        Field field = excelContentProperty.getField();
        ExcelPropertyExt annotation = field.getAnnotation(ExcelPropertyExt.class);
        String expression = annotation.expression();
        List<String> list =  StringUtil.split(expression, SymbolConstants.SEMICOLON);
        for(String dic : list){
            List<String> items = StringUtil.split(dic, SymbolConstants.C_COMMA);
            String key = items.get(0);
            String v = items.get(1);
            if(value.equals(key)) {
                value = v;
                break;
            }
        }

        return new CellData<>(value);
    }

配置excel导出模型

@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataModelDTO implements Serializable {

    private static final long serialVersionUID = 15353L;

    @ExcelProperty(value = "设备名称")
    private String deviceName;

    @ExcelPropertyExt(expression = "0,停止;1,上;2,下")
    @ExcelProperty(value = "运行方向",converter = CommonIntegerConverter.class)
    private Integer direction;

    @ExcelProperty(value = "状态", converter = StatusConverter.class)
    private Integer Status;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "数据上报时间")
    private Date timestamp;
}

六、导出

通过controller导出

@Operation(summary = "导出")
@GetMapping("/data/export")
void export(HttpServletResponse response) {
    ExcelUtil excelUtil = ExcelUtil.create(response,"设备实时数据", DataModelDTO.class);
    // 写入数据
	excelUtil.writeData( getData());
	        // 导出
	excelUtil.export();
}

private List<UserDataModelDTO> getData(){
	List<UserDataModelDTO> list = new ArrayList<>();
	DataModelDTO data1 = new DataModelDTO("设备1",1,1,new Date());
	DataModelDTO data2 = new DataModelDTO("设备2",1,1,new Date());
	list.add(data1);
	list.add(data2);
	return list ;
}

统一导出转换器导出

Consumer<ExcelWriter> consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .registerConverter(new BigDecimalConverter())
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);

七、效果

在这里插入图片描述

八、其他用法

1、设置列宽

@ColumnWidth(10)

Blueeyedboy521

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot集成阿里EasyExcel导出excel高级实战

/*** easyExcel枚举转换return "正常" . equals(cellData . getStringValue())?1 : 0;} try {//给字段重新赋值 String expression =(String) me . invoke(annotation);break;} }./*** easyExcel枚举转换。
复制链接

扫一扫