EasyExcel 读取Excel 和 上传Excel 文件
<!--excel 导入导出用到的依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.2</version>
</dependency>
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;
}
}
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 + '\'' +
'}';
}
}
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();
}
}
}
}
阳光总在风雨后！
