Java easyexcel使用教程之导出篇

Java easyexcel使用教程之导出篇

目录EasyExcel简介1、导入Maven依赖2、新建Student.java类3、generateStudentUtil.java类，随机生成Student对象4、BaseTest.java4、导出Excel报表5、把姓名格式化：1显示男，0显示女6、把体重保留2位小数7、过滤字段不生成excel8、冻结列, 冻结姓名列总结

EasyExcel简介

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

Excel解析流程图:

EasyExcel读取Excel的解析原理:

开始准备工作

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 {

/**

* 导出方法

*

* @param fileName 文件

* @param writerConsumer consumer

*/

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 zwkyWbglobalConfiguration) 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、冻结列, 冻结姓名列

冻结列handler,FreezeNameHandler.java

package cohttp://m.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 consumer = writer -> {

writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")

.registerWriteHandler(new FreezeNameHandler()) // 冻结姓名列

.head(Student.class)

.build());

};

export("D:/报表.xlsx", consumer);

}

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。