需求：
需要一个读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 /11/17 16:41*/@Datapublic 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 /11/17 16:45*/@Datapublic 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>();@Overridepublic void invoke(Object o, AnalysisContext analysisContext) {LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));//一条数据添加到暂时存储的存储结构中datas.add(o);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (datas.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listdatas.clear();}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//获取sheetNamesheetName = context.readSheetHolder().getSheetName();// LOGGER.info("所有数据解析完成！");}//获取表头@Overridepublic 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>();@Overridepublic void invoke(Student student, AnalysisContext analysisContext) {//每解析一行数据，就会调用该方法一次LOGGER.info("解析到一条数据:{}", JSON.toJSONString(student));//一条数据添加到暂时存储的存储结构中datas.add(student);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (datas.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listdatas.clear();}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//获取sheetNamesheetName = context.readSheetHolder().getSheetName();// LOGGER.info("所有数据解析完成！");}//获取表头@Overridepublic 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>>();@Overridepublic void invoke(Map<Integer,String> o, AnalysisContext analysisContext) {//每解析一行数据，就会调用该方法一次LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));datas.add(o);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (datas.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listdatas.clear();}}//获取表头@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {headList.add(headMap);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//获取sheetNamesheetName = 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 {// 有实体单独写一个sheetpublic 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--StudentMap<String, List<Object>> m1 = ReadExcelUtil.getMap(fileName, Student.class, 0, 1);//第二张sheet--CatMap<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);
