Java|Java easyexcel使用教程之导出篇
目录
- 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.alibaba easyexcel2.2.10 org.apache.poi poi3.17 org.apache.poi poi-ooxml3.17 org.projectlombok lombokprovidedjunit junitprovided4.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, ConsumerwriterConsumer) {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() {Consumerconsumer = 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() {Consumerconsumer = 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() {Consumerconsumer = 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() {Consumerconsumer = writer -> {writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息").registerWriteHandler(new FreezeNameHandler()) // 冻结姓名列.head(Student.class).build()); }; export("D:/报表.xlsx", consumer); }
总结 到此这篇关于Java easyexcel使用教程之导出篇的文章就介绍到这了,更多相关Java easyexcel导出内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!
推荐阅读
- C++实例讲解四种类型转换的使用
- Tomcat介绍和配置使用
- Redis数据结构实战演练,看看微博、微信、购物车、抽奖小程序是如何使用的()
- 代码合规性(开发人员使用Helix QAC的5大原因)
- 某课 java全栈工程师(从java后端到全栈,高级电商全栈系统大课)
- 程序员|某互联网公司发招(使用高科技坐垫监视员工、厕所安装计时器!)
- java调用加密机实例_Enigma Java模拟实现恩尼格玛密码机加密解密过程 Develop 238万源代码下载- www.pudn.com...
- Java中LinkedList的模拟实现
- Python|Python Pandas中合并数据的5个函数使用详解
- JavaScript变量声明的var、let、const详解