EasyExcel 读取Excel 和 上传Excel 文件

1、导入maven 依赖

   <!--excel 导入导出用到的依赖-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.1.2</version>
    </dependency>

　　

2、 编写ExcelListener.java

package com.wlc.sheetConfig;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

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

/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class ExcelListener extends AnalysisEventListener<Object> {

    private List<Object> data = new ArrayList<>();
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        data.add(o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("所有数据解析完毕");
    }

    public List<Object> getData() {
        return data;
    }

    public void setData(List<Object> data) {
        this.data = data;
    }
}

　　

3、把要读取的Excel 对应的 sheet 的实体类

package com.wlc.sheetConfig;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @author 王立朝
 * @date 2020-11-14
 * @description: 要读取的Excel 对应的第一个sheet 
 */
public class Sheet1 {
    @ExcelProperty(index = 0)
    private String no;
    @ExcelProperty(index = 1)
    private String field;
    @ExcelProperty(index = 2)
    private String lastDate;
    @ExcelProperty(index = 3)
    private String thisDate;
    @ExcelProperty(index = 4)
    private String fieldShow;
    @ExcelProperty(index = 5)
    private String require;


    public String getNo() {
        return no;
    }

    public void setNo(String no) {
        this.no = no;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getLastDate() {
        return lastDate;
    }

    public void setLastDate(String lastDate) {
        this.lastDate = lastDate;
    }

    public String getThisDate() {
        return thisDate;
    }

    public void setThisDate(String thisDate) {
        this.thisDate = thisDate;
    }

    public String getFieldShow() {
        return fieldShow;
    }

    public void setFieldShow(String fieldShow) {
        this.fieldShow = fieldShow;
    }

    public String getRequire() {
        return require;
    }

    public void setRequire(String require) {
        this.require = require;
    }

    @Override
    public String toString() {
        return "Sheet1{" +
                "no='" + no + '\'' +
                ", field='" + field + '\'' +
                ", lastDate='" + lastDate + '\'' +
                ", thisDate='" + thisDate + '\'' +
                ", fieldShow='" + fieldShow + '\'' +
                ", require='" + require + '\'' +
                '}';
    }
}

　　

package com.wlc.sheetConfig;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:要读取的Excel 对应的第一个sheet 
 */
public class Sheet2 {

    @ExcelProperty(index = 0)
    private String no;
    @ExcelProperty(index = 1)
    private String field;
    @ExcelProperty(index = 2)
    private String lastDate;
    @ExcelProperty(index = 3)
    private String thisDate;
    @ExcelProperty(index = 4)
    private String fieldShow;
    @ExcelProperty(index = 5)
    private String require;

    public String getNo() {
        return no;
    }

    public void setNo(String no) {
        this.no = no;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getLastDate() {
        return lastDate;
    }

    public void setLastDate(String lastDate) {
        this.lastDate = lastDate;
    }

    public String getThisDate() {
        return thisDate;
    }

    public void setThisDate(String thisDate) {
        this.thisDate = thisDate;
    }

    public String getFieldShow() {
        return fieldShow;
    }

    public void setFieldShow(String fieldShow) {
        this.fieldShow = fieldShow;
    }

    public String getRequire() {
        return require;
    }

    public void setRequire(String require) {
        this.require = require;
    }

    @Override
    public String toString() {
        return "Sheet2{" +
                "no='" + no + '\'' +
                ", field='" + field + '\'' +
                ", lastDate='" + lastDate + '\'' +
                ", thisDate='" + thisDate + '\'' +
                ", fieldShow='" + fieldShow + '\'' +
                ", require='" + require + '\'' +
                '}';
    }
}

　　

package com.wlc.sheetConfig;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class Sheet3 {

    @ExcelProperty(index = 0)
    private String uuid;
    @ExcelProperty(index = 1)
    private String mecFlag;
    @ExcelProperty(index = 2)
    private String month;
    @ExcelProperty(index = 3)
    private String startDate;
    @ExcelProperty(index = 4)
    private String endDate;
    @ExcelProperty(index = 5)
    private String type;
    @ExcelProperty(index = 6)
    private String amount;
    @ExcelProperty(index = 7)
    private String delFlag;

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getMecFlag() {
        return mecFlag;
    }

    public void setMecFlag(String mecFlag) {
        this.mecFlag = mecFlag;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public String getStartDate() {
        return startDate;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    public String getEndDate() {
        return endDate;
    }

    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getAmount() {
        return amount;
    }

    public void setAmount(String amount) {
        this.amount = amount;
    }

    public String getDelFlag() {
        return delFlag;
    }

    public void setDelFlag(String delFlag) {
        this.delFlag = delFlag;
    }

    @Override
    public String toString() {
        return "Sheet3{" +
                "uuid='" + uuid + '\'' +
                ", mecFlag='" + mecFlag + '\'' +
                ", month='" + month + '\'' +
                ", startDate='" + startDate + '\'' +
                ", endDate='" + endDate + '\'' +
                ", type='" + type + '\'' +
                ", amount='" + amount + '\'' +
                ", delFlag='" + delFlag + '\'' +
                '}';
    }
}

　　

4、编写测试类

package com.wlc.sheetConfig;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class Test {
    public static void main(String[] args) throws IOException {
        ExcelReader excelReader = null;
        InputStream inputStream = null;
        try {
          // 要读取的Excel的路径
            String filePath = "/Users/wanglichao/Downloads/确认版测试用.xlsx";
            File file = new File(filePath);
            inputStream = new FileInputStream(file);

            excelReader = EasyExcel.read(inputStream).build();
            ExcelListener sheet1Listener = new ExcelListener();
            ExcelListener sheet2Listener = new ExcelListener();
            ExcelListener sheet3Listener = new ExcelListener();

            //获取sheet对象
            ReadSheet readSheet1 =
                    EasyExcel.readSheet(0).head(Sheet1.class).registerReadListener(sheet1Listener).build();
            ReadSheet readSheet2 =
                    EasyExcel.readSheet(1).head(Sheet2.class).registerReadListener(sheet2Listener).build();
            ReadSheet readSheet3 =
                    EasyExcel.readSheet(2).head(Sheet3.class).registerReadListener(sheet3Listener).build();

            //读取数据
            excelReader.read(readSheet1, readSheet2,readSheet3);
            //业务处理
            System.out.println("sheet1->" + sheet1Listener.getData().size());
            System.out.println("sheet2->" + sheet2Listener.getData().size());
            System.out.println("sheet3->" + sheet3Listener.getData().size());
            List<Sheet1> sheet1List = new ArrayList<>();
            int sheet1Size = sheet1Listener.getData().size();
            for (int i = 0; i < sheet1Size; i++) {
                Sheet1 sheet1 = new Sheet1();
                sheet1.setNo(((Sheet1) sheet1Listener.getData().get(i)).getNo());
                sheet1.setField(((Sheet1) sheet1Listener.getData().get(i)).getField());
                sheet1.setLastDate(((Sheet1) sheet1Listener.getData().get(i)).getLastDate());
                sheet1.setThisDate(((Sheet1) sheet1Listener.getData().get(i)).getThisDate());
                sheet1.setFieldShow(((Sheet1) sheet1Listener.getData().get(i)).getFieldShow());
                sheet1.setRequire(((Sheet1) sheet1Listener.getData().get(i)).getRequire());
                sheet1List.add(sheet1);
            }
            List<Sheet2> sheet2List = new ArrayList<>();
            int sheet2Size = sheet2Listener.getData().size();
            for (int i = 0; i < sheet2Size; i++) {
                Sheet2 sheet2 = new Sheet2();
                sheet2.setNo(((Sheet2) sheet2Listener.getData().get(i)).getNo());
                sheet2.setField(((Sheet2) sheet2Listener.getData().get(i)).getField());
                sheet2.setLastDate(((Sheet2) sheet2Listener.getData().get(i)).getLastDate());
                sheet2.setThisDate(((Sheet2) sheet2Listener.getData().get(i)).getThisDate());
                sheet2.setFieldShow(((Sheet2) sheet2Listener.getData().get(i)).getFieldShow());
                sheet2.setRequire(((Sheet2) sheet2Listener.getData().get(i)).getRequire());
                sheet2List.add(sheet2);
            }
            List<Sheet3> sheet3List = new ArrayList<>();
            int sheet3Size = sheet3Listener.getData().size();
            for (int i = 0; i < sheet3Size; i++) {
                Sheet3 sheet3 = new Sheet3();
                sheet3.setUuid(((Sheet3) sheet3Listener.getData().get(i)).getUuid());
                sheet3.setMecFlag(((Sheet3) sheet3Listener.getData().get(i)).getMecFlag());
                sheet3.setMonth(((Sheet3) sheet3Listener.getData().get(i)).getMonth());
                sheet3.setStartDate(((Sheet3) sheet3Listener.getData().get(i)).getStartDate());
                sheet3.setEndDate(((Sheet3) sheet3Listener.getData().get(i)).getEndDate());
                sheet3.setType(((Sheet3) sheet3Listener.getData().get(i)).getType());
                sheet3.setAmount(((Sheet3) sheet3Listener.getData().get(i)).getAmount());
                sheet3.setDelFlag(((Sheet3) sheet3Listener.getData().get(i)).getDelFlag());
                sheet3List.add(sheet3);
            }


            System.out.println(sheet1List.toString()+"   ");
            System.out.println(sheet2List.toString()+"   ");
            System.out.println(sheet3List.toString()+"   ");
        } catch (Exception  e) {
            e.printStackTrace();
        } finally {
            inputStream.close();
            if (excelReader != null) {
                excelReader.finish();
            }
        }

    }
}