Java easyexcel导出

时间:2022-06-18 chenxjuc 人气:0

EasyExcel简介

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

Excel解析流程图:

EasyExcel读取Excel的解析原理:

开始准备工作

1、导入Maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.10</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <scope>provided</scope>
    <version>4.12</version>
</dependency>

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<Student> generateStudent(int size) {
        List<Student> 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 {
    /**
     * 导出方法
     *
     * @param fileName       文件
     * @param writerConsumer consumer
     */
    public static void export(String fileName, Consumer<ExcelWriter> 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<ExcelWriter> consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);
    }
}

运行export1结果

20220316201222

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<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) throws Exception {
        return "男".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> 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()单元测试

20220316201838

6、把体重保留2位小数

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

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

运行export1()单元测试

20220316202342

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

@ContentStyle(dataFormat = 2)
private BigDecimal weight2;

运行export1()单元测试

20220316212147

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

  /**
     * 测试统一类型转换
     */
    @Test
    public void export2() {
        Consumer<ExcelWriter> 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<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));
    }
}

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

20220316213349

8、冻结列, 冻结姓名列

冻结列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);
    }

总结

加载全部内容