Java easyexcel导出
chenxjuc 人气:0EasyExcel简介
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); }
总结
加载全部内容