Java easyexcel使用教學之匯出篇

2022-06-07 14:07:28

EasyExcel簡介
1、匯入Maven依賴
2、新建Student.java類
3、generateStudentUtil.java類，隨機生成Student物件
4、BaseTest.java
4、匯出Excel報表
5、把姓名格式化：1顯示男，0顯示女
6、把體重保留2位小數
7、過濾欄位不生成excel
8、凍結列, 凍結姓名列
總結

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結果

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()單元測試

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

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