Java|Java easyexcel使用教程之导出篇 Javaeasyexcel使用教程之导出篇

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解析流程图:

文章图片

【Java|Java easyexcel使用教程之导出篇】EasyExcel读取Excel的解析原理:

文章图片

开始准备工作

1、导入Maven依赖

com.alibabaeasyexcel2.2.10org.apache.poipoi3.17org.apache.poipoi-ooxml3.17org.projectlomboklombokprovidedjunitjunitprovided4.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 = "https://www.it610.com/article/姓名")private String name; @ExcelProperty(value = "https://www.it610.com/article/性别")private Integer sex; @ExcelProperty(value = "https://www.it610.com/article/生日")private Date birthday; @ExcelProperty(value = "https://www.it610.com/article/体重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 {@Testpublic 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 {@Overridepublic Class supportJavaTypeKey() {return Integer.class; }@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING; }@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return "男".equals(cellData.getStringValue()) ? 1 : 0; }@Overridepublic CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData<>(integer.equals(1) ? "男" : "女"); }}

Student类sex属性注入SexConverter转换器

@ExcelProperty(value = "https://www.it610.com/article/性别", converter = SexConverter.class)private Integer sex;

再次运行export1()单元测试

文章图片

6、把体重保留2位小数方法1，@NumberFormat 注解。修改Student类

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

运行export1()单元测试

文章图片

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

@ContentStyle(dataFormat = 2)private BigDecimal weight2;

运行export1()单元测试

文章图片

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

/*** 测试统一类型转换*/@Testpublic 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 {@Overridepublic Class supportJavaTypeKey() {return BigDecimal.class; }@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.NUMBER; }@Overridepublic BigDecimal convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return cellData.getNumberValue(); }@Overridepublic 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// 一定要有无参构造方法@ExcelIgnoreUnannotatedpublic class Student {.....}

方式2：指定字段加注解

@ExcelIgnore // demo2不生成excelprivate String demo2;

方式3：代码指定过滤字段, 同一个excel生成两个sheet分别过滤不同字段

/*** 过滤字段*/@Testpublic 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 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 {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 获取到当前的sheetSheet sheet = writeSheetHolder.getSheet(); /***第一个参数：冻结的列数*第二个参数：冻结的行数*第三个参数：冻结后第一列的列号*第四个参数：冻结后第一行的行号**/sheet.createFreezePane(1, 0, 1, 0); }}

注册handler

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

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