SpringBoot集成easyexcel

0.学习参考

官网 官网Demo 官网语雀Demo 源代码

1.引入依赖

easyexcel依赖于POI，所有POI也需要引入

<!--    SpringBoot父依赖-->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.6.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<dependencies>
    
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.16</version>
    </dependency>
    
    <!--        poi  -->
    <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>
    
    <!--   easyexcel     -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
    
</dependencies>

2.写Excel

2.1实体类

@Data
public class ExcelData {

    /**
     * @ExcelProperty 是标题
     *  @ColumnWidth  设置宽度
     */
    @ExcelProperty(value = "第一列")
    private String data1;
    @ExcelProperty(value = "第二列")
    private String data2;
}

2.2写操作

import com.alibaba.excel.EasyExcel;
import com.maizi.excel.domain.ExcelData;

import java.util.ArrayList;
import java.util.List;

public class ExcelWrite {

    public static void main(String[] args) {

        // 文件的下载路径(或写入路径)
        String fileName = "E:\\Excel写学习Demo.xlsx";

        /**
         * .write(fileName, ExcelData.class)   fileName 是写入路径， ExcelData.class 是接收实体类
         * .sheet 是sheet名称
         * .doWrite 是要写入的数据List
         *
         */
        EasyExcel.write(fileName, ExcelData.class).sheet("写Demo测试").doWrite(getData());
    }

    /**
     * 模拟写入的数据
     *
     * @return
     */
    private static List getData() {
        ArrayList<ExcelData> excelDataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            ExcelData excelData = new ExcelData();
            excelData.setData1("测试第一列" + i);
            excelData.setData2("测试第二列" + i);
            excelDataList.add(excelData);
        }

        return excelDataList;
    }
}

2.3结果

3.读Excel

3.1要读取的excel

3.2实体类

@Data
public class ExcelData {

    /**
     * @ExcelProperty  在原来的写的实体类中添加 index 
     * value = "第一列" 设置Excel表头名称
     * index = 0 表示第一列
     */
    @ExcelProperty(value = "第一列", index = 0)
    private String data1;
    @ExcelProperty(value = "第二列", index = 1)
    private String data2;
}

3.3创建监听

package com.maizi.excel.listener;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.maizi.excel.domain.ExcelData;


import java.util.Map;

public class ExcelListenter extends AnalysisEventListener<ExcelData> {
    /**
     * 读取表头
     *
     * @param headMap  表头
     * @param context
     */
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头内容 = " + headMap);
    }

    /**
     * 一行一行的读取
     *
     * @param readData        实体类
     * @param analysisContext
     */
    @Override
    public void invoke(ExcelData readData, AnalysisContext analysisContext) {
        System.out.println("readData = " + readData);
        System.out.println("readData.getData1() = " + readData.getData1());
        System.out.println("readData.getData2() = " + readData.getData2());
    }

    /**
     * 读取完成后要做的事
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

3.4测试读

 public static void main(String[] args) {

        // 读取excel的位置
        String fileName = "E:\\Excel写学习Demo.xlsx";

        /**
         *  fileName   读取excel的位置
         *  ExcelData.class   实体类
         *  new ExcelListenter()   监听器
         */
        EasyExcel.read(fileName, ExcelData.class,new ExcelListenter()).sheet().doRead();
    }

4.读Excel并保存

4.1实体类

@Data
public class SubjectData {

    // index = 0 为读取的第一列
    @ExcelProperty(index = 0)
    private String oneSubject;
    @ExcelProperty(index = 1)
    private String twoSubject;

}

4.2controller层

import com.alibaba.excel.EasyExcel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

@Controller
public class WriteController {

    @Autowired
    private SubjectService subjectService;

    @PostMapping("/upload/excel")
    public String writeAddSave(MultipartFile excelFile) {

        try {
            // 获取文件流
            InputStream inputStream = excelFile.getInputStream();
            /**
             *  inputStream   文件流
             *  SubjectData.class   实体类
             *  new SubjectExcelListener(subjectService)   监听器(主要功能都在监听器里，比如读取添加等操作)
             *  SubjectExcelListener  没有交给Spring进行管理，所有不能在此方法使用    @Autowired 注入，所以使用  SubjectExcelListener(subjectService)   传入方式
             */
            EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
            return "保存失败";
        }
        return "保存成功";

    }

}

4.3service层

@Service
public class SubjectService {

    /**
     * 模拟调用保存方法
     *
     * @param subjectData
     */
    public void save(SubjectData subjectData) {
        System.out.println("调用了保存方法 " + subjectData);
    }
}

4.4创建监听

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.maizi.excel.ExcelReadAndWriteDemo.domain.ExcelData;
import org.springframework.beans.factory.annotation.Autowired;

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    //  SubjectExcelListener  没有交给Spring进行管理，所有不能在此方法使用    @Autowired 注入
    //  不能使用数据库等操作,可以使用setter和getter方法，在调用方传入
    private SubjectService subjectService;

    public SubjectExcelListener() {
    }

    public SubjectExcelListener(SubjectService subjectService) {
        this.subjectService = subjectService;
    }

    //  以上是无参和全参

    // 读取Excel内容，一行一行的读
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        // 调用service层的保存方法
        subjectService.save(subjectData);
    }

    // 读取完成之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取完成之后 = " + analysisContext);

    }
}

4.5测试

Excel写学习Demo.xlsx

---

使用Postman来测试

---

IDEA控制台打印**