Easys Excel的表格導(dǎo)入(讀)導(dǎo)出(寫)-----java

這篇具有很好參考價值的文章主要介紹了Easys Excel的表格導(dǎo)入(讀)導(dǎo)出(寫)-----java。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點(diǎn)擊"舉報違法"按鈕提交疑問。

一,EasyExcel官網(wǎng):

可以學(xué)習(xí)一些新知識:

EasyExcel官方文檔 - 基于Java的Excel處理工具 | Easy Excel

二,為什么要使用easyexcle

excel的一些優(yōu)點(diǎn)和缺點(diǎn)

java解析excel的框架有很多：

poi jxl,存在問題：非常的消耗內(nèi)存，

easyexcel 我們遇到再大的excel都不會出現(xiàn)內(nèi)存溢出的問題能夠?qū)⒁粋€原本3M excel文件，poi來操作將會占用內(nèi)存

100MB，使用easyexcel減低到幾Mb,使用起來更加簡單

poi讀 1、創(chuàng)建xsshworkbook/hssfworkbook（inputstream in）

2、讀取sheet

3、拿到當(dāng)前sheet所有行row

4、通過當(dāng)前行去拿到對應(yīng)的單元格的值

easyexcel擬解決的問題

1.excel讀寫時內(nèi)存溢出

2.使用簡單

3.excel格式解析

工作原理

Easys Excel的表格導(dǎo)入(讀)導(dǎo)出(寫)-----java,excel,java

三，項目的步驟

依賴包：

基本上要用的的依賴包:

       <!--		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>
<!--        數(shù)據(jù)池-->
        <!-- 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>

一，導(dǎo)出(寫)流程寫法:

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】的數(shù)據(jù)庫操作Service
* @createDate 2023-08-02 11:10:56
*/
public interface ExcelService extends IService<Excel> {
    public Integer getCount(); //總條數(shù)
    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】的數(shù)據(jù)庫操作Service實(shí)現(xiàn)
* @createDate 2023-08-02 11:10:56
*/
@Service
@Transactional
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, Excel>
    implements ExcelService {
    @Autowired
    private ExcelMapper excelMapper;
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public Integer getCount() {
        return excelMapper.selectCount(null);
    }
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<Excel> getListBYPage(Integer pageOn) {
        Integer begin=(pageOn-1)*50000+1;
        //第幾頁顯示多少數(shù)據(jù)
        return excelMapper.ListPage(begin,50000);
    }
}

3. 3.Test測試：

    /**
     * 寫數(shù)據(jù)
     * */
    @Test
    void contextLoads4(){
        //保存路徑
        String path="E:\\aaa\\ciy2.xls";
        //查詢數(shù)據(jù)總條數(shù)
        Integer count = excelService.getCount();
        //創(chuàng)建easyexcel的寫出類構(gòu)造器 參數(shù) 告訴構(gòu)造器 我的excel將來要寫到哪里 以及excel中數(shù)據(jù)是基于哪個java對象模板創(chuàng)建的
        ExcelWriter excelWriter = EasyExcel.write(path, Excel.class).build();
        //判斷一頁能放多少條數(shù)據(jù)
        Integer sheetNum = count % 50000 == 0 ? count / 50000 : count / 50000 + 1;
        log.info("sheetNum=={}",sheetNum);
        for(int i=1;i<sheetNum;i++){
            log.info("第"+i+"批次");
            //分頁查詢數(shù)據(jù)庫
            List<Excel> listPage = excelService.getListBYPage(i);
            //創(chuàng)建sheet構(gòu)造器
            WriteSheet sheet = EasyExcel.writerSheet("test").build();
            //使用excel對象將數(shù)據(jù)寫入到創(chuàng)建的sheet當(dāng)中
            excelWriter.write(listPage,sheet);
        }
        excelWriter.finish();
        log.info("導(dǎo)出成功");
    }

二,導(dǎo)入(讀)流程寫法

1. 監(jiān)聽器:

讀取數(shù)據(jù)要通過監(jiān)聽器來實(shí)現(xiàn)，監(jiān)聽讀取的每一條數(shù)據(jù)：

監(jiān)聽器:

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;

/**
 * 監(jiān)聽器
 */

@Slf4j
public class EasyExcelListenner extends AnalysisEventListener<FmAddress> {

    static Integer a = 0;

    @Override
    public 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));
        //獲取bean
        FmAddressService fmAddressService = SpringJobBeanFactory.getBean(FmAddressService.class);
        fmAddressService.insert(fmAddress);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

a. 知識點(diǎn)普及：

1.監(jiān)聽器我們是繼承Excel中的AnalysisEventListener方法來

2.該監(jiān)聽器是不被Spring (bean) 容器管理的，我要手動注入容器

2. 手動將監(jiān)聽器注入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;

    @Override
    public 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. 普及知識點(diǎn)：

手動注入bean 調(diào)用的是ApplicationContextAware接口文章來源地址http://www.zghlxwxcb.cn/news/detail-647777.html

3.Test測試類：

    /**
     * 讀數(shù)據(jù)
     */
    @Test
    void 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();

    }

到了這里，關(guān)于Easys Excel的表格導(dǎo)入(讀)導(dǎo)出(寫)-----java的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！