需求:
需要一个读excel文件中多sheet的工具类,返回List/Map型的数据;同时也可以进行反向写操作
具体步骤
1. 引入maven依赖
<!--excel插件-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<!--简化实体插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
</dependency>
<!--日志-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.22</version>
</dependency>
<!--json-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
2.1 有实体类型
建立实体类,建立监听器类(通用型/实体类型)
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @author :maple
* @description:学生类
* @date :Created in 2020/11/17 16:41
*/
@Data
public class Student {
//这个注解用于对应表头,value为表头值,index为列值
@ExcelProperty(value = "姓名",index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private int age;
public Student(String name, int age) {
this.name = name;
this.age = age;
}
}
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @author :maple
* @description:
* @date :Created in 2020/11/17 16:45
*/
@Data
public class Cat {
@ExcelProperty(value = "昵称",index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private int age;
public Cat(String name, int age) {
this.name = name;
this.age = age;
}
}
通用型(Object)监听器:
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 通用监听器
*/
public class ObjectListener extends AnalysisEventListener<Object> {
private final static Logger LOGGER = LoggerFactory.getLogger(ObjectListener.class);
private static final int BATCH_COUNT = 5;
//存储最终数据
List<Object> objectList = new ArrayList<>();
//存储表头数据
List<Object> headList = new ArrayList<>();
//存储sheet名
private String sheetName;
//暂时存储数据
List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
//一条数据添加到暂时存储的存储结构中
datas.add(o);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (datas.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
datas.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//获取sheetName
sheetName = context.readSheetHolder().getSheetName();
// LOGGER.info("所有数据解析完成!");
}
//获取表头
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//把表头数据加入到存储结构中
headList.add(headMap);
}
/**
* 入库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", datas.size());
//添加到返回的存储结构中,也可直接存储到数据库
objectList.addAll(datas);
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public List<Object> getObjectList() {
return objectList;
}
public void setObjectList(List<Object> objectList) {
this.objectList = objectList;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Object> getHeadList() {
return headList;
}
public void setHeadList(List<Object> headList) {
this.headList = headList;
}
}
实体类型监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.maple.entity.Student;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 学生类专属监听器
*/
public class StudentListener extends AnalysisEventListener<Student> {
private final static Logger LOGGER = LoggerFactory.getLogger(StudentListener.class);
private static final int BATCH_COUNT = 5;
List<Object> objectList = new ArrayList<>();
List<Object> headList = new ArrayList<>();
private String sheetName;
List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Student student, AnalysisContext analysisContext) {
//每解析一行数据,就会调用该方法一次
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(student));
//一条数据添加到暂时存储的存储结构中
datas.add(student);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (datas.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
datas.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//获取sheetName
sheetName = context.readSheetHolder().getSheetName();
// LOGGER.info("所有数据解析完成!");
}
//获取表头
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//把表头数据加入到存储结构中
headList.add(headMap);
}
/**
* 入库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", datas.size());
//添加到返回的存储结构中,也可直接存储到数据库
objectList.addAll(datas);
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public List<Object> getObjectList() {
return objectList;
}
public void setObjectList(List<Object> objectList) {
this.objectList = objectList;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Object> getHeadList() {
return headList;
}
public void setHeadList(List<Object> headList) {
this.headList = headList;
}
}
2.2 无实体型
只需要一个监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.sunyard.util.file.excel.ExcelListener;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author :maple
* @description:无实体模型的监听器
*/
public class NoModelListener extends AnalysisEventListener<Map<Integer,String>> {
private final static Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
private static final int BATCH_COUNT = 5;
//数据存储结构
private List<Map<Integer,String>> lists = new ArrayList<>();
//表头存储结构
List<Map<Integer,String>> headList = new ArrayList<>();
//sheet名
private String sheetName;
List<Map<Integer,String>> datas = new ArrayList<Map<Integer,String>>();
@Override
public void invoke(Map<Integer,String> o, AnalysisContext analysisContext) {
//每解析一行数据,就会调用该方法一次
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
datas.add(o);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (datas.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
datas.clear();
}
}
//获取表头
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
headList.add(headMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//获取sheetName
sheetName = context.readSheetHolder().getSheetName();
saveData();
// LOGGER.info("所有数据解析完成!");
}
/**
* 入库
*/
private void saveData() {
// LOGGER.info("{}条数据,开始存储数据库!", datas.size());
lists.addAll(datas);
}
public List<Map<Integer,String>> getDatas() {
return datas;
}
public void setDatas(List<Map<Integer,String>> datas) {
this.datas = datas;
}
public List<Map<Integer, String>> getLists() {
return lists;
}
public void setLists(List<Map<Integer, String>> lists) {
this.lists = lists;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Map<Integer,String>> getHeadList() {
return headList;
}
public void setHeadList(List<Map<Integer,String>> headList) {
this.headList = headList;
}
}
3. 工具类
- 读取工具类ReadExcelUtil,为保证工具类的通用性,不使用专属实体监听器
import com.alibaba.excel.EasyExcel;
import com.maple.listener.NoModelListener;
import com.maple.listener.ObjectListener;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 读取工具类
*/
public class ReadExcelUtil {
/**
* 返回Map型的实体模型数据
* @param fileName 文件名
* @param obj 对象反射类 xxx.Class
* @param sheetNo sheet编号
* @param headNum 表头行数,1表示1行
* @return
*/
public static Map<String, List<Object>> getMap(String fileName, Class obj, Integer sheetNo, Integer headNum){
Map<String, List<Object>> map = new HashMap<>();
//new监听器
ObjectListener objectListener = new ObjectListener();
//读取操作
EasyExcel.read(fileName, obj, objectListener).sheet(sheetNo).headRowNumber(headNum).doRead();
//获取读取的数据
List<Object> objList = objectListener.getObjectList();
//获取sheet名
String objSheetName = objectListener.getSheetName();
map.put(objSheetName,objList);
return map;
}
//返回List型的实体模型数据
public static List<Object> getList(String fileName,Class obj,Integer sheetNo,Integer headNum){
ObjectListener objectListener = new ObjectListener();
EasyExcel.read(fileName, obj, objectListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Object> objList = objectListener.getObjectList();
return objList;
}
//返回有实体模型的表头
public static List<Object> getHeadList(String fileName,Class obj,Integer sheetNo,Integer headNum){
ObjectListener objectListener = new ObjectListener();
EasyExcel.read(fileName, obj, objectListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Object> objList = objectListener.getHeadList();
return objList;
}
//返回Map型的没有实体模型数据
public static Map<String,List<Map<Integer, String>>> getMapNoModel(String fileName,Integer sheetNo,Integer headNum){
Map<String,List<Map<Integer, String>>> map = new HashMap<>();
NoModelListener noModelListener = new NoModelListener();
EasyExcel.read(fileName, noModelListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Map<Integer, String>> lists = noModelListener.getLists();
String sheetName = noModelListener.getSheetName();
map.put(sheetName,lists);
return map;
}
//返回List型的没有实体模型数据
public static List<Map<Integer, String>> getListNoModel(String fileName,Integer sheetNo,Integer headNum){
NoModelListener noModelListener = new NoModelListener();
EasyExcel.read(fileName, noModelListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Map<Integer, String>> lists = noModelListener.getLists();
return lists;
}
//返回List型的没有实体模型数据(仅有数据)
public static List<List<Object>> getOnlyListNoModel(String fileName,Integer sheetNo,Integer headNum){
NoModelListener noModelListener = new NoModelListener();
EasyExcel.read(fileName, noModelListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Map<Integer, String>> lists = noModelListener.getLists();
List<List<Object>> allData = new ArrayList<>();
for (int i = 0; i < lists.size(); i++) {
Map<Integer, String> map = lists.get(i);
List<Object> rowData = new ArrayList<>();
for (Map.Entry<Integer, String> entry : map.entrySet()) {
rowData.add(entry.getValue());
}
allData.add(rowData);
}
return allData;
}
//返回没有实体模型的表头
public static List<Map<Integer,String>> getHeadListNoModel(String fileName,Integer sheetNo,Integer headNum){
NoModelListener noModelListener = new NoModelListener();
EasyExcel.read(fileName, noModelListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Map<Integer, String>> lists = noModelListener.getHeadList();
return lists;
}
/**
* 返回没有实体模型的具体行的表头
* @param fileName : 文件名
* @param sheetNo : sheet编号
* @param headNum : 表头行数,1表示1行
* @param rowNum : 行号,索引从0开始,即0为第一行
* @return java.util.List<java.lang.String>
* @throws
*/
public static List<String> getHeadListNoModel(String fileName,Integer sheetNo,Integer headNum,Integer rowNum){
NoModelListener noModelListener = new NoModelListener();
EasyExcel.read(fileName, noModelListener).sheet(sheetNo).headRowNumber(headNum).doRead();
List<Map<Integer, String>> headList = noModelListener.getHeadList();
List<String> oneHead = new ArrayList<>();
for (int i = 0; i < headList.size(); i++) {
if(i==rowNum){
Map<Integer, String> map = headList.get(i);
for (Map.Entry<Integer, String> entry : map.entrySet()) {
oneHead.add(entry.getValue());
}
}
}
return oneHead;
}
}
- 写入工具类WriteExcelUtil
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.util.List;
/**
* 写入工具类
*/
public class WriteExcelUtil {
// 有实体单独写一个sheet
public static void writeSheet(String fileName,List<Object> list,Class obj,Integer sheetNo,String sheetName){
ExcelWriter excelWriter = EasyExcel.write(fileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).head(obj).build();
excelWriter.write(list, writeSheet);
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
}
//获取流
public static ExcelWriter getExcelWriter(String fileName){
ExcelWriter excelWriter = EasyExcel.write(fileName).build();
return excelWriter;
}
//关闭流
public static void closeExcelWriter(ExcelWriter excelWriter){
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
}
/**
* 有实体多个sheet一起写,要使用同一个流
* @param excelWriter : 写入流
* @param list : 数据
* @param obj : 对应实体类
* @param sheetNo : sheet编号,0开始
* @param sheetName : sheetName
* @return void
* @throws
*/
public static void writeMultipleSheet(ExcelWriter excelWriter,List<Object> list,Class obj,Integer sheetNo,String sheetName){
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).head(obj).build();
excelWriter.write(list, writeSheet);
}
//
/**
* 无实体多个sheet一起写,要使用同一个流
* @param excelWriter : 写入流
* @param list : 数据,List<List<Object>>格式,每一个List<Object>代表一行数据
* @param headList : 表头,List<List<String>,每一个List<String>代表一列的表头
* @param sheetNo : sheet编号
* @param sheetName : sheetName
* @return void
* @throws
*/
public static void writeMultipleNoModel(ExcelWriter excelWriter,List<List<Object>> list,List<List<String>> headList,Integer sheetNo,String sheetName){
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).head(headList).build();
excelWriter.write(list, writeSheet);
}
}
执行案例
- 有实体的多sheet读取
//有实体
String fileName = "F:\\testFile\\excel\\stu.xlsx";
Map<String, List<Object>> excelMap = new HashMap<String, List<Object>>();
//第一张sheet--Student
Map<String, List<Object>> m1 = ReadExcelUtil.getMap(fileName, Student.class, 0, 1);
//第二张sheet--Cat
Map<String, List<Object>> m2 = ReadExcelUtil.getMap(fileName, Cat.class, 1, 1);
excelMap.putAll(m1);
excelMap.putAll(m2);
System.out.println(excelMap);
//{Student=[Student(name=AA, age=11), Student(name=BB, age=22), Student(name=AADD, age=33), Student(name=AACC, age=11), Student(name=AAEE, age=5)], Cat=[Cat(name=aa, age=1), Cat(name=bb, age=2), Cat(name=cc, age=3), Cat(name=dd, age=4), Cat(name=ee, age=1)]}
- 无实体的多sheet读取
String fileName = "F:\\testFile\\excel\\stu.xlsx";
Map<String, List<Map<Integer, String>>> excelMap = new HashMap<>();
Map<String, List<Map<Integer, String>>> stuMap = ReadExcelUtil.getMapNoModel(fileName, 0, 1);
Map<String, List<Map<Integer, String>>> catMap = ReadExcelUtil.getMapNoModel(fileName, 1, 1);
excelMap.putAll(stuMap);
excelMap.putAll(catMap);
System.out.println(excelMap);
//{Student=[{0=AA, 1=11}, {0=BB, 1=22}, {0=AADD, 1=33}, {0=AACC, 1=11}, {0=AAEE, 1=5}], Cat=[{0=aa, 1=1}, {0=bb, 1=2}, {0=cc, 1=3}, {0=dd, 1=4}, {0=ee, 1=1}]}
- 有实体的多sheet写入
List<Object> stuList = new ArrayList<>();
Object s1 = new Student("小明",10);
Object s2 = new Student("小红",20);
stuList.add(s1);
stuList.add(s2);
List<Object> catList = new ArrayList<>();
Object c1 = new Cat("小明",10);
Object c2 = new Cat("小红",20);
catList.add(c1);
catList.add(c2);
String file = "F:/testFile/excel/writeTest"+System.currentTimeMillis()+".xlsx";
ExcelWriter excelWriter = WriteExcelUtil.getExcelWriter(file);
WriteExcelUtil.writeMultipleSheet(excelWriter,stuList,Student.class,0,"Student");
WriteExcelUtil.writeMultipleSheet(excelWriter,stuList,Cat.class,1,"Cat");
WriteExcelUtil.closeExcelWriter(excelWriter);
- 无实体的单sheet写入
//此处造数据较麻烦,借无实体读获取数据再写入另一个文件
//读取
String fileName = "F:\\testFile\\excel\\stu.xlsx";
//数据
List<List<Object>> stuList = ReadExcelUtil.getOnlyListNoModel(fileName, 0, 1);
//表头
List<String> headList = ReadExcelUtil.getHeadListNoModel(fileName, 0, 1,0);
List<List<String>> writeHeadList = new ArrayList<>();
for (int i = 0; i < headList.size(); i++) {
List<String> oneHead = new ArrayList<>();
oneHead.add(headList.get(i));
writeHeadList.add(oneHead);
}
//写入
String file = "F:/testFile/excel/writeTest"+System.currentTimeMillis()+".xlsx";
ExcelWriter excelWriter = WriteExcelUtil.getExcelWriter(file);
WriteExcelUtil.writeMultipleNoModel(excelWriter,stuList,writeHeadList,0,"Student");
WriteExcelUtil.closeExcelWriter(excelWriter);