easyexcel使用教程-导出篇

easyExcel使用教程–导出篇开始准备工作 1、导入Maven依赖


    com.alibaba
    easyexcel
    2.2.10


    org.apache.poi
    poi
    3.17


    org.apache.poi
    poi-ooxml
    3.17


    org.projectlombok
    lombok
    provided


    junit
    junit
    provided
    4.12

2、新建Student.java类

package com.test.easyexcel.bean;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.math.BigDecimal;
import java.util.Date;

@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor  // 一定要有无参构造方法
public class Student {
    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private Integer sex;

    @ExcelProperty(value = "生日")
    private Date birthday;

    @ExcelProperty(value = "体重KG")
    private BigDecimal weight;

    private String memo;
}

3、generateStudentUtil.java类，随机生成Student对象

package com.test.easyexcel.util;

import com.test.easyexcel.bean.Student;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class generateStudentUtil {

    public static List generateStudent(int size) {
        List stues = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            stues.add(new Student("姓名" + i, (int) (Math.random() * 2), randomDate(), randomWeight(), "备注"));
        }
        return stues;
    }

    public static Date randomDate() {
        LocalDateTime localDateTime = LocalDateTime.of(randomInt(1990, 2022), randomInt(1, 12), randomInt(1, 28), randomInt(0, 23), randomInt(0, 59), randomInt(0, 59), randomInt(0, 999));
        ZonedDateTime zdt = localDateTime.atZone(ZoneId.systemDefault());
        return Date.from(zdt.toInstant());
    }

    public static int randomInt(int min, int max) {
        int de = max - min;
        // 二进制长度
        int bitCount = Integer.toBinaryString(de).length();
        int ans = 0;
        do {
            ans = 0;
            for (int i = 0; i < bitCount; i++) {
                ans += random0_1() << i;
            }
        } while (ans > de);
        return ans + min;
    }

    public static int random0_1() {
        return (int) (Math.random() * 2);
    }

    public static BigDecimal randomWeight() {
        return BigDecimal.valueOf((Math.random() * 10));
    }
}

4、baseTest.java

package com.test.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import java.util.function.Consumer;
// 父类
public class baseTest {
    
    public static void export(String fileName, Consumer writerConsumer) {
        ExcelWriter writer = EasyExcel.write(fileName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();
        writerConsumer.accept(writer);
        writer.finish();
    }

}

4、导出Excel报表

package com.test.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.test.easyexcel.bean.Student;
import org.junit.Test;
import java.util.function.Consumer;

import static com.test.easyexcel.util.generateStudentUtil.generateStudent;

public class TestEasyExcel extends baseTest {

    @Test
    public void export1() {
        Consumer consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);
    }
}

运行export1结果

5、把姓名格式化：1显示男，0显示女

新建SexConverter.java

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;

public class SexConverter implements Converter {

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

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

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

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData<>(integer.equals(1) ? "男" : "女");
    }
}

Student类sex属性注入SexConverter转换器

@ExcelProperty(value = "性别", converter = SexConverter.class)
private Integer sex;

再次运行export1()单元测试

6、把体重保留2位小数

方法1，@NumberFormat 注解。修改Student类

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

运行export1()单元测试

方法2：@ContentStyle(dataFormat = 2) 注解，我们新建一个字段weight2

@ContentStyle(dataFormat = 2)
private BigDecimal weight2;

运行export1()单元测试

方法3：利用**registerConverter(new BigDecimalConverter())**统一类型处理

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

BigDecimalConverter.java

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 {

    @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));
    }
}

7、过滤字段不生成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 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);
    }

8、冻结列, 冻结姓名列

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

需要完整demo代码可以在评论区留言，后续会传到github

easyexcel使用教程-导出篇

Java相关栏目本月热门文章