<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
package com.gblfy.dca.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
public class S {
public static void main(String[] args) {
// List<ExcelData> list = list();
// //list写入excel
// EasyExcel.write("C:\\Users\\Administrator.DESKTOP-I5SJ4CK\\Desktop\\22\\用户信息2.xlsx")
// .head(ExcelData.class)
// .excelType(ExcelTypeEnum.XLSX)
// .sheet("用户信息梳理")
// .doWrite(list);
//测试读取Excel
String filePath = "C:\\Users\\Administrator.DESKTOP-I5SJ4CK\\Desktop\\22\\用户信息.xlsx";
List<ExcelData> dataList = parseData(filePath);
// 批量插入 。。。。。
}
public static List<ExcelData> list() {
List<ExcelData> list = new LinkedList<>();
ExcelData excelData = new ExcelData();
excelData.setId(0);
excelData.setName("ddd");
excelData.setEmail("dd");
excelData.setGender("dd");
excelData.setScope(0);
excelData.setIp("d");
excelData.setCount(0);
excelData.setDate(new Date());
list.add(excelData);
return list;
}
/**
* @param filePath
* @return
*/
public static List<ExcelData> parseData(String filePath) {
List<ExcelData> list = new LinkedList<>();
EasyExcel.read(filePath)
.head(ExcelData.class)
.sheet()
.registerReadListener(new AnalysisEventListener<ExcelData>() {
@Override
public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
list.add(excelData);
}
//读取表头内容
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头: " + headMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕!");
}
}).doReadSync();
return list;
}
/**
* 读多个或者全部sheet,这里注意一个sheet不能读取多次,多次读取需要重新读取文件
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
* <p>
* 3. 直接读即可
*/
@Test
public void repeatedRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();
// 读取部分sheet
fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
excelReader.read(readSheet1, readSheet2);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
}
}
}