官網：https://easyexcel.opensource.alibaba.com/docs/current/

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

一、讀

1.1簡單讀

@Getter
@Setter
@EqualsAndHashCode
public class khTable {//@ExcelProperty(index = 0)@ExcelProperty("字段名稱")private String filedEN;@ExcelProperty("字段說明")private String filedCN;@ExcelProperty("類型")private String type;
}

fileName 文件路徑
khTable.class 實體類
sheet1:讀哪個sheet頁，默認第一個sheet，可以輸入sheetName也可以輸入sheetNO
headRowNumber 表頭所在行數

• 0 表示沒有表頭
• 1默認值，表示表頭占1行，數據從第2行開始
• 2表示表示表頭占2行，數據從第3行開始

        List<khTable> list = new ArrayList<>();EasyExcel.read(fileName, khTable.class, new PageReadListener<khTable>(c -> {list.addAll(c);})).sheet("sheet1").headRowNumber(2).doRead();System.out.println(list);

1.2 讀-自定義監聽器

讀取條額外信息：批注、超鏈接、合并單元格信息等

以讀合并單元格為例

        //創建監聽器ExcelDateListener<HisTable> listener = new ExcelDateListener<>();//讀取excelEasyExcel.read(hisExcel, HisTable.class,listener).extraRead(CellExtraTypeEnum.MERGE).sheet(1).doRead();//從監聽器中獲取合并單元格的數據List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();//從監聽器中獲取其他數據List<HisTable> cacheList = listener.getCacheList();//把合并單元格的數據和其他數據進行合并List<HisTable> list1 = EasyExcelUtil.explainMergeData(cacheList, extraMergeInfoList, 1);System.out.println(list1);

自定義監聽器

@Slf4j
public class ExcelDateListener<M> extends AnalysisEventListener<M> {// 表頭數據Map<Integer,String> headMap=new HashMap<>();// 緩存數據List<M> cacheList = new ArrayList<>();// 合并單元格private final List<CellExtra> extraMergeInfoList = new ArrayList<>();/*** 獲取合并單元格*/public List<CellExtra> getExtraMergeInfoList() {return this.extraMergeInfoList;}/*** 獲取合并單元格*/public List<M> getCacheList() {return this.cacheList;}/*** 這里會一行行的返回頭*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {this.headMap=headMap;}@Overridepublic void invoke(M data, AnalysisContext analysisContext) {cacheList.add(data);}/*** 所有數據解析完成了 都會來調用*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}/*** 在轉換異常 獲取其他異常下會調用本接口。拋出異常則停止讀取。如果這里不拋出異常則 繼續讀取下一行*/@Overridepublic void onException(Exception exception, AnalysisContext context) {// 如果是某一個單元格的轉換異常 能獲取到具體行號// 如果要獲取頭的信息 配合invokeHeadMap使用if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;log.error("第{}行，第{}列解析異常", excelDataConvertException.getRowIndex(),excelDataConvertException.getColumnIndex());}}/*** 讀取條額外信息：批注、超鏈接、合并單元格信息等*/@Overridepublic void extra(CellExtra extra, AnalysisContext context) {switch (extra.getType()) {case COMMENT:log.info("額外信息是批注,在rowIndex:{},columnIndex;{},內容是:{}", extra.getRowIndex(), extra.getColumnIndex(),extra.getText());break;case HYPERLINK:if ("Sheet1!A1".equals(extra.getText())) {log.info("額外信息是超鏈接,在rowIndex:{},columnIndex;{},內容是:{}", extra.getRowIndex(),extra.getColumnIndex(), extra.getText());} else if ("Sheet2!A1".equals(extra.getText())) {log.info("額外信息是超鏈接,而且覆蓋了一個區間,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"+ "內容是:{}",extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),extra.getLastColumnIndex(), extra.getText());} else {log.error("Unknown hyperlink!");}break;case MERGE:log.info("額外信息是合并單元格,而且覆蓋了一個區間,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),extra.getLastColumnIndex());extraMergeInfoList.add(extra);break;default:}}
}

package com.wang.ahlht.utils;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;@Slf4j
public class EasyExcelUtil {/*** 初始化響應體* @param response 請求頭* @param fileName 導出名稱*/public static void initResponse(HttpServletResponse response, String fileName) {String finalFileName = fileName + "_(截止"+ System.currentTimeMillis()+")";// 設置content—type 響應類型// response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");try {// 這里URLEncoder.encode可以防止中文亂碼finalFileName = URLEncoder.encode(finalFileName, "UTF-8");} catch (UnsupportedEncodingException e) {e.printStackTrace();}response.setHeader("Content-disposition", "attachment;filename=" + finalFileName + ".xlsx");}/*** 處理合并單元格* @param data               解析數據* @param extraMergeInfoList 合并單元格信息* @param headRowNumber      起始行* @return 填充好的解析數據*/public static <T> List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {// 循環所有合并單元格信息extraMergeInfoList.forEach(cellExtra -> {int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;int firstColumnIndex = cellExtra.getFirstColumnIndex();int lastColumnIndex = cellExtra.getLastColumnIndex();// 獲取初始值Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);// 設置值for (int i = firstRowIndex; i <= lastRowIndex; i++) {for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {setInitValueToList(initValue, i, j, data);}}});return data;}/*** 設置合并單元格的值** @param filedValue  值* @param rowIndex    行* @param columnIndex 列* @param data        解析數據*/private static <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {if (rowIndex >= data.size()) return;T object = data.get(rowIndex);for (Field field : object.getClass().getDeclaredFields()) {// 提升反射性能，關閉安全檢查field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == columnIndex) {try {field.set(object, filedValue);break;} catch (IllegalAccessException e) {log.error("設置合并單元格的值異常：{}", e.getMessage());}}}}}/*** 獲取合并單元格的初始值* rowIndex對應list的索引* columnIndex對應實體內的字段** @param firstRowIndex    起始行* @param firstColumnIndex 起始列* @param data             列數據* @return 初始值*/private static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {Object filedValue = null;T object = data.get(firstRowIndex);for (Field field : object.getClass().getDeclaredFields()) {// 提升反射性能，關閉安全檢查field.setAccessible(true);ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {if (annotation.index() == firstColumnIndex) {try {filedValue = field.get(object);break;} catch (IllegalAccessException e) {log.error("設置合并單元格的初始值異常：{}", e.getMessage());}}}}return filedValue;}}

二、寫

1.1 一次寫多個sheet

    private void writeExcel3(Map<String,List<Hbgxys>> map) {int sheetNum = 1;try (ExcelWriter excelWriter = EasyExcel.write(outPutExcel, Hbgxys.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build()) {for (Map.Entry<String, List<Hbgxys>> stringListEntry : map.entrySet()) {// 每次都要創建writeSheet 這里注意必須指定sheetNo 而且sheetName必須不一樣String key = stringListEntry.getKey();List<Hbgxys> value = stringListEntry.getValue();WriteSheet writeSheet = EasyExcel.writerSheet(sheetNum, key).build();excelWriter.write(value, writeSheet);sheetNum++;}}}