EasyExcel-最簡單的讀寫excel工具類

前言：
easyExcel 的官網文檔給的示例非常全，可以參考https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read
在此我貼出自己的工具類，可以直接用

導包

		<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.0</version></dependency>

讀excel

1：最簡單讀-使用demo對象

創建對象bean

package com.wkl.testdemo.excel;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;/*** @author wangkanglu* @version 1.0* @description* @date 2023-12-08 17:28*/
@Data
public class DemoBean {/*** 用名字去匹配，這里需要注意，如果名字重復，會導致只有一個字段讀取到數據*/@ExcelProperty("姓名")private String name;/*** 強制讀取第二個 這里不建議 index 和 name 同時用，要么一個對象只用index，要么一個對象只用name去匹配*/@ExcelProperty(index = 1)private String sex;
}

創建讀取器

package com.wkl.testdemo.excel;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;import java.util.List;/*** @author wangkanglu* @version 1.0* @description* @date 2023-12-08 17:27*/
@Data
public class DemoListener extends AnalysisEventListener<DemoBean> {/*** 每隔5條存儲數據庫，實際使用中可以100條，然后清理list ，方便內存回收*/private static final int BATCH_COUNT = 100;/*** 緩存的數據*/private List<DemoBean> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/*** 假設這個是一個DAO，當然有業務邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用。*/private DemoBean demoDAO;public DemoListener() {// 這里是demo，所以隨便new一個。實際使用如果到了spring,請使用下面的有參構造函數demoDAO = new DemoBean();}@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {if (exception instanceof ExcelDataConvertException) {Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;String message = "第" + rowIndex + "行，第" + columnIndex + "列,數據格式有誤，請核實";System.out.println("導入數據轉換出現錯誤： " + message);} else if (exception instanceof RuntimeException) {System.out.println("導入錯誤：" + exception);if (exception.getMessage().contains("列與模板上順序不符，請勿修改表頭模板")) {throw new RuntimeException(exception.getMessage());}}}@Overridepublic void invoke(DemoBean data, AnalysisContext analysisContext) {System.out.println("解析到一條數據:{}"+data);cachedDataList.add(data);// 達到BATCH_COUNT了，需要去存儲一次數據庫，防止數據幾萬條數據在內存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存儲完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 這里也要保存數據，確保最后遺留的數據也存儲到數據庫saveData();System.out.println("解析完了");}/*** 加上存儲數據庫*/private void saveData() {System.out.println("{}條數據，開始存儲數據庫！"+ cachedDataList.size());
//        demoDAO.save(cachedDataList);System.out.println("存儲數據庫成功！");}}

測試程序

package com.wkl.testdemo.excel;import com.alibaba.excel.EasyExcel;import java.util.List;/*** @author wangkanglu* @version 1.0* @description* @date 2023-12-08 17:45*/
public class SimpleTest {public static void main(String[] args) {String path = "C:\\Users\\Desktop\\demo.xlsx";DemoListener listener = new DemoListener();EasyExcel.read(path,DemoBean.class,listener).sheet().doRead();List<DemoBean> cachedDataList = listener.getCachedDataList();System.out.println("end----");}
}

2:讀取多個sheet

 public static void main(String[] args) {String path = "C:\\Users\\wenge\\Desktop\\demo.xlsx";DemoListener listener = new DemoListener();//生成讀取對象ExcelReader excelReader = EasyExcel.read(path).build();//生成一個sheetReadSheet build = EasyExcel.readSheet(0).head(DemoBean.class).registerReadListener(listener).build();//讀取多個sheetExcelReader read = excelReader.read(Arrays.asList(build));List<DemoBean> cachedDataList = listener.getCachedDataList();System.out.println("end----");

3:復雜頭的讀取

如果遇到這樣的excel
在這里插入圖片描述

創建對象bean

package com.wkl.testdemo.excel;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;/*** @author wangkanglu* @version 1.0* @description* @date 2023-12-08 17:28*/
@Data
public class DemoBeanheads {/*** 用名字去匹配，這里需要注意，如果名字重復，會導致只有一個字段讀取到數據*/@ExcelProperty({"大學","姓名"})private String name;/*** 強制讀取第二個 這里不建議 index 和 name 同時用，要么一個對象只用index，要么一個對象只用name去匹配*/@ExcelProperty({"大學","性別"})private String sex;
}

讀取器不變

測試程序

public static void main(String[] args) {String path = "C:\\Users\\Desktop\\demo.xlsx";DemoHeadListener listener = new DemoHeadListener();//生成讀取對象ExcelReader excelReader = EasyExcel.read(path).build();//生成一個sheetReadSheet build = EasyExcel.readSheet(0).head(DemoBeanheads.class).registerReadListener(listener).headRowNumber(2).build();//讀取多個sheetExcelReader read = excelReader.read(Arrays.asList(build));List<DemoBeanheads> cachedDataList = listener.getCachedDataList();System.out.println("end----");}

4：不創建對象的對

讀取器

package com.wkl.testdemo.excel;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;@Slf4j
public class NoHeadMapListener extends AnalysisEventListener<Map<Integer, String>> {private List<Map<Integer, String>> list = new ArrayList<>();private static final SimpleDateFormat sdfFormat = new SimpleDateFormat("yyyy-MM-dd");//表頭private List<String> headList = Arrays.asList("姓名","性別");@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {if (headMap==null){throw new RuntimeException("文件表頭為空，請勿上傳非法excel文件，請先下載對應的導入模板文件" );}if (headMap.size()!=headList.size()){throw new RuntimeException("文件表頭列與模板不符,請勿上傳非法excel文件，請先下載對應的導入模板文件" );}for (int i = 0; i < headList.size(); i++) {String tableHead = headMap.get(i);String head = headList.get(i);if (!head.equals(tableHead)) {throw new RuntimeException("表頭中'" + tableHead + "'列與模板上順序不符，請勿修改模板表頭");}}}@Overridepublic void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
//        log.info("解析到一條數據:{}", JSON.toJSONString(integerStringMap));List<String> picList = new ArrayList<>();if (!ObjectUtils.isEmpty(integerStringMap)) {list.add(integerStringMap);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {log.info("所有數據解析完成！");}@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {if (exception instanceof ExcelDataConvertException) {Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;String message = "第" + rowIndex + "行，第" + columnIndex + "列,數據格式有誤，請核實";log.error("導入數據轉換出現錯誤： " + message);} else if (exception instanceof RuntimeException) {log.error("導入錯誤：" + exception);if (exception.getMessage().contains("列與模板上順序不符，請勿修改表頭模板")) {throw new RuntimeException(exception.getMessage());}}}public List<String> getHeadList() {return headList;}public void setHeadList(List<String> headList) {this.headList = headList;}public List<Map<Integer, String>> getList() {return list;}}

測試程序

 public static void main(String[] args) {String path = "C:\\Users\\Desktop\\demo.xlsx";NoHeadMapListener listener = new NoHeadMapListener();EasyExcel.read(path, listener).sheet().doRead();List<String> headList = listener.getHeadList();List<Map<Integer, String>> list = listener.getList();System.out.println("end");}

讀取后的得到的數據列表：
在這里插入圖片描述

寫excel

1：最簡單的寫

創建對象

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {@ExcelProperty("字符串標題")private String string;@ExcelProperty("日期標題")private Date date;@ExcelProperty("數字標題")private Double doubleData;/*** 忽略這個字段*/@ExcelIgnoreprivate String ignore;
}

測試程序

List<DemoData> data = new ArrayList();
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";// 這里 需要指定寫用哪個class去寫，然后寫到第一個sheet，名字為模板 然后文件流會自動關閉// 如果這里想使用03 則 傳入excelType參數即可EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data);

測試程序-導出指定列

List<DemoData> data = new ArrayList();
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";// 根據用戶傳入字段 假設我們要忽略 name列Set<String> excludeColumnFiledNames = new HashSet<String>();excludeColumnFiledNames.add("name");// 這里 需要指定寫用哪個class去寫，然后寫到第一個sheet，名字為模板 然后文件流會自動關閉EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板").doWrite(data);// 根據用戶傳入字段 假設我們只要導出 name列Set<String> includeColumnFiledNames = new HashSet<String>();includeColumnFiledNames.add("name");// 這里 需要指定寫用哪個class去寫，然后寫到第一個sheet，名字為模板 然后文件流會自動關閉EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板").doWrite(data);

2:數據寫到不同的sheet

fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";// 這里 指定文件try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {// 去調用寫入,這里我調用了五次，實際使用時根據數據庫分頁的總的頁數來。這里最終會寫到5個sheet里面for (int i = 0; i < 5; i++) {// 每次都要創建writeSheet 這里注意必須指定sheetNo 而且sheetName必須不一樣WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();// 分頁去數據庫查詢數據 這里可以去數據庫查詢每一頁的數據List<DemoData> data = data();excelWriter.write(data, writeSheet);}}

3:動態表頭寫入web

private List<List<String>> head() {List<List<String>> list = new ArrayList<List<String>>();List<String> head0 = new ArrayList<String>();head0.add("字符串" + System.currentTimeMillis());List<String> head1 = new ArrayList<String>();head1.add("數字" + System.currentTimeMillis());List<String> head2 = new ArrayList<String>();head2.add("日期" + System.currentTimeMillis());list.add(head0);list.add(head1);list.add(head2);return list;}private List<DemoData> data() {List<DemoData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}@GetMapping("download")public void download(HttpServletResponse response) throws IOException {// 這里注意 有同學反應使用swagger 會導致各種問題，請直接用瀏覽器或者用postmanresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 這里URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());}