Easys Excel的表格导入(读)导出(写)-----java

一,EasyExcel官网:

可以学习一些新知识:

EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

二,为什么要使用easyexcle

excel的一些优点和缺点

java解析excel的框架有很多 ：

poi jxl,存在问题：非常的消耗内存，

easyexcel 我们遇到再大的excel都不会出现内存溢出的问题 能够将一个原本3M excel文件，poi来操作将会占用内存

100MB，使用easyexcel减低到几Mb,使用起来更加简单

poi读 1、创建xsshworkbook/hssfworkbook（inputstream in）

2、读取sheet

3、拿到当前sheet所有行row

4、通过当前行去拿到对应的单元格的值

---

easyexcel拟解决的问题

1.excel读写时内存溢出

2.使用简单

3.excel格式解析

---

工作原理

三，项目的步骤

依赖包：

基本上要用的的依赖包:

       <!--		mybatis-plus--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.2</version></dependency><!--	mysql	--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.28</version></dependency><!--        reds依赖--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis-reactive</artifactId></dependency><!--     连接池依赖   --><!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 --><dependency><groupId>org.apache.commons</groupId><artifactId>commons-pool2</artifactId><version>2.11.1</version></dependency>
<!--        数据池--><!-- https://mvnrepository.com/artifact/com.alibaba/druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.14</version></dependency>
<!--    mapper    --><!-- https://mvnrepository.com/artifact/tk.mybatis/mapper --><dependency><groupId>tk.mybatis</groupId><artifactId>mapper</artifactId><version>4.0.2</version></dependency><!--    easyexcel依赖    --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.3</version></dependency><!--log4j --><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency>

一，导出(写)流程写法:

1. Server接口:

package com.example.excel01.generator.service;import com.example.excel01.generator.domain.Excel;
import com.baomidou.mybatisplus.extension.service.IService;import java.util.List;/**
* @author zeng
* @description 针对表【excel】的数据库操作Service
* @createDate 2023-08-02 11:10:56
*/
public interface ExcelService extends IService<Excel> {public Integer getCount(); //总条数public List<Excel> getListBYPage(Integer pageOn); //分页查询}

---

2. 2.ServiceImpl类：

package com.example.excel01.generator.service.impl;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.excel01.generator.domain.Excel;
import com.example.excel01.generator.mapper.ExcelMapper;
import com.example.excel01.generator.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;import java.util.List;/**
* @author zeng
* @description 针对表【excel】的数据库操作Service实现
* @createDate 2023-08-02 11:10:56
*/
@Service
@Transactional
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, Excel>implements ExcelService {@Autowiredprivate ExcelMapper excelMapper;@Transactional(propagation = Propagation.SUPPORTS)@Overridepublic Integer getCount() {return excelMapper.selectCount(null);}@Transactional(propagation = Propagation.SUPPORTS)@Overridepublic List<Excel> getListBYPage(Integer pageOn) {Integer begin=(pageOn-1)*50000+1;//第几页显示多少数据return excelMapper.ListPage(begin,50000);}
}

---

3. 3.Test测试：

    /*** 写数据* */@Testvoid contextLoads4(){//保存路径String path="E:\\aaa\\ciy2.xls";//查询数据总条数Integer count = excelService.getCount();//创建easyexcel的写出类构造器 参数 告诉构造器 我的excel将来要写到哪里 以及excel中数据是基于哪个java对象模板创建的ExcelWriter excelWriter = EasyExcel.write(path, Excel.class).build();//判断一页能放多少条数据Integer sheetNum = count % 50000 == 0 ? count / 50000 : count / 50000 + 1;log.info("sheetNum=={}",sheetNum);for(int i=1;i<sheetNum;i++){log.info("第"+i+"批次");//分页查询数据库List<Excel> listPage = excelService.getListBYPage(i);//创建sheet构造器WriteSheet sheet = EasyExcel.writerSheet("test").build();//使用excel对象将数据写入到创建的sheet当中excelWriter.write(listPage,sheet);}excelWriter.finish();log.info("导出成功");}

二,导入(读)流程写法

1. 监听器:

读取数据要通过监听器来实现，监听读取的每一条数据：

---

监听器:

package com.example.excel01.generator.listenner;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.excel01.generator.domain.FmAddress;
import com.example.excel01.generator.service.FmAddressService;
import lombok.extern.slf4j.Slf4j;/*** 监听器*/@Slf4j
public class EasyExcelListenner extends AnalysisEventListener<FmAddress> {static Integer a = 0;@Overridepublic void invoke(FmAddress fmAddress, AnalysisContext analysisContext) {++a;log.info("a={}",a);if (a == 1000) {try {a=0;Thread.sleep(500);} catch (InterruptedException e) {throw new RuntimeException(e);}}log.info(String.valueOf(fmAddress));//获取beanFmAddressService fmAddressService = SpringJobBeanFactory.getBean(FmAddressService.class);fmAddressService.insert(fmAddress);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}

---

a. 知识点普及：

1.监听器我们是继承Excel中的AnalysisEventListener方法来

2.该监听器是不被Spring (bean) 容器管理的，我要手动注入容器

---

2. 手动将监听器注入Spring容器：

package com.example.excel01.generator.listenner;import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;/*** @ClassName: SpringJobBeanFactory*/
@Component
public class SpringJobBeanFactory implements ApplicationContextAware {private static ApplicationContext applicationContext;@Overridepublic void setApplicationContext(ApplicationContext applicationContext) throws BeansException {SpringJobBeanFactory.applicationContext=applicationContext;}public static ApplicationContext getApplicationContext() {return applicationContext;}@SuppressWarnings("unchecked")public static <T> T getBean(String name) throws BeansException {if (applicationContext == null){return null;}return (T)applicationContext.getBean(name);}public static <T> T getBean(Class<T>  name) throws BeansException {if (applicationContext == null){return null;}return applicationContext.getBean(name);}
}

a. 普及知识点：

手动注入bean 调用的是ApplicationContextAware接口

---

3.Test测试类：

    /*** 读数据*/@Testvoid contextLoads5(){//保存路径String path="E:\\aaa\\ciy.xls";ExcelReader build = EasyExcel.read(path, FmAddress.class, new EasyExcelListenner()).build();ReadSheet build1 = EasyExcel.readSheet(0).build();log.info("build1={}",build1);build.read(build1);build.finish();}