SpringBoot集成easyexcel

• • 0.学习参考
  • 1.引入依赖
  • 2.写Excel
  • • 2.1实体类
    • 2.2写操作
    • 2.3结果
  • 3.读Excel
  • • 3.1要读取的excel
    • 3.2实体类
    • 3.3创建监听
    • 3.4测试读
  • 4.读Excel并保存
  • • 4.1实体类
    • 4.2controller层
    • 4.3service层
    • 4.4创建监听
    • 4.5测试

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

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

    org.springframework.boot
    spring-boot-starter-parent
    2.1.6.RELEASE
     



    
    
    
        org.projectlombok
        lombok
        1.18.16
    
    
    
    
        org.apache.poi
        poi
        3.17
    
    
        org.apache.poi
        poi-ooxml
        3.17
    
    
    
    
        com.alibaba
        easyexcel
        2.1.1
    
    

@Data
public class ExcelData {

    
    @ExcelProperty(value = "第一列")
    private String data1;
    @ExcelProperty(value = "第二列")
    private String data2;
}

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";

        
        EasyExcel.write(fileName, ExcelData.class).sheet("写Demo测试").doWrite(getData());
    }

    
    private static List getData() {
        ArrayList 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;
    }
}

@Data
public class ExcelData {

    
    @ExcelProperty(value = "第一列", index = 0)
    private String data1;
    @ExcelProperty(value = "第二列", index = 1)
    private String data2;
}

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 {
    
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        System.out.println("表头内容 = " + headMap);
    }

    
    @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());
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

 public static void main(String[] args) {

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

        
        EasyExcel.read(fileName, ExcelData.class,new ExcelListenter()).sheet().doRead();
    }

@Data
public class SubjectData {

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

}

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();
            
            EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
            return "保存失败";
        }
        return "保存成功";

    }

}

@Service
public class SubjectService {

    
    public void save(SubjectData subjectData) {
        System.out.println("调用了保存方法 " + subjectData);
    }
}

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 {

    //  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);

    }
}

Excel写学习Demo.xlsx

使用Postman来测试

IDEA控制台打印**