Java easyexcel使用教程之导出篇

2022-11-13 09:11:38 java 导出 教程

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

总结

到此这篇关于Java easyexcel使用教程之导出篇的文章就介绍到这了,更多相关Java easyexcel导出内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

相关文章