一：简介

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.1.1</version>
</dependency>

二：API

2.1 EasyExcel

public static ExcelReaderBuilder read(String pathName, ReadListener readListener);

public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener);

2.2 ExcelReaderSheetBuilder

// 指定表头所在的行号，默认是1
public T headRowNumber(Integer headRowNumber);

public void doRead();

// 同步读(不推荐使用)
public <T> List<T> doReadSync();

2.3 ReadListener

// 1. 第一先读表头
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context);

// 2. 第二解析内容
public void invoke(SimpleUserInfo data, AnalysisContext context);
// 解析数据发生异常时会调用
public void onException(Exception exception, AnalysisContext context) throws Exception;

// 3. 第三解析额外信息
public void extra(CellExtra extra, AnalysisContext context);

// 4. 最后调用
public void doAfterAllAnalysed(AnalysisContext context);

三：案例

2.1 分页读监听器 PageReadListener

• 使用框架提供的分页的ReadListener，一页读100条数据。

• 通过index或者value(即列名)来将excel中的列映射到java的属性上，如果通过列名来映射当列名重复时只会读取一个，注意：官方不建议同时使用index和value同时指定。

• Model中也可以使用CellData类型，CellDate中不仅包含了值，也包含了数据类型等更多信息。

2.1.1 Model

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class SimpleUserInfo {
	//  @ExcelProperty(index = 0)
	//  private String username;

    @ExcelProperty(index = 0)
    private CellData<String> usernameCellData;

    @ExcelIgnore
    private String homePage;

    @ExcelProperty(index = 1)
    private String name;

    @ExcelProperty(index = 3, converter = CustomPhoneConverter.class)
    private String phone;

    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(index = 4)
    private Date createTime;

    @NumberFormat("#.###,##")
    @ExcelProperty(index = 5)
    private BigDecimal amount;

    @ExcelProperty(index = 6)
    private String status;
}

2.1.2 转换器

注意：转换器读的时候和写的时候不是重写的同一个方法。

public class CustomPhoneConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    /**
     * 读的时候会调用
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        final String originalValue = context.getReadCellData().getStringValue();
        return originalValue.substring(0, 3) + "*****" + originalValue.substring(8);
    }
    /**
     * 写的时候会调用
     *
     * @return
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>(context.getValue());
    }
}

2.1.3 Controller

@PostMapping("/upload")
public List<SimpleUserInfo> uploadExcel(MultipartFile file) throws IOException {
    List<SimpleUserInfo> allList = new ArrayList<>();
    // 分页读取数据,一行一行的读取，默认从第一行开始读
    EasyExcel.read(file.getInputStream(), SimpleUserInfo.class, new PageReadListener<SimpleUserInfo>(dataList -> {
        allList.addAll(dataList);
    })).sheet().headRowNumber(3).doRead();
    return allList;
}

2.2 自定义读监听器ReadListener

2.2.1 UserService

@Slf4j
@Service
public class UserService {

    public void saveData(List<SimpleUserInfo> cachedDataList) {
        log.info("开始保存数据cachedDataList");
    }
}

2.2.2 CustomPageReadListener

/**
 * CustomPageReadListener 不能被Spring管理，使用时需要手动new，
 * 如果内部需要使用Spring中的bean可通过构造函数传入
 */
@Slf4j
public class CustomPageReadListener implements ReadListener<SimpleUserInfo> {
    // 每批缓存数量
    private static final int BATCH_COUNT = 100;
    private List<SimpleUserInfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private UserService userService;

    public CustomPageReadListener(UserService userService) {
        // 这里可以传入spring的bean
        this.userService = userService;
    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        // {0=用户名, 1=姓名, 2=null, 3=手机号, 4=加入时间, 5=身价, 6=状态}
        Map<Integer, String> integerStringMap = ConverterUtils.convertToStringMap(headMap, context);
        log.info("表头数据:headMap={}, integerStringMap={}", JSONObject.toJSONString(headMap), integerStringMap);
    }

    /**
     * 解析每条数据都会调用该方法
     */
    @Override
    public void invoke(SimpleUserInfo data, AnalysisContext context) {
        log.info("解析数据：{}", JSONObject.toJSONString(data));
        cachedDataList.add(data);

        // 每次缓存BATCH_COUNT条数据，防止数据达到几万条还留在内容中，容易出现OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            userService.saveData(cachedDataList);
            // 清理缓存
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 读取额外信息：先读表头，再读数据、再读额外信息
     */
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        switch (extra.getType()) {
            case COMMENT:
                log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}",
                        extra.getRowIndex(),
                        extra.getColumnIndex(),
                        extra.getText());
                break;
            case HYPERLINK:
                log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}",
                        extra.getRowIndex(),
                        extra.getColumnIndex(),
                        extra.getText());
                SimpleUserInfo simpleUserInfo = cachedDataList.get(extra.getRowIndex() - context.readSheetHolder().getHeadRowNumber());
                simpleUserInfo.setHomePage(extra.getText());
                break;
            case MERGE:
                log.info("额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(),
                        extra.getFirstColumnIndex(),
                        extra.getLastRowIndex(),
                        extra.getLastColumnIndex());
                break;
            default:
                break;
        }
    }

    /**
     * 解析数据出现异常时会调用该方法（如转换异常）
     * 如果该方法不抛出异常则继续读取下一行，抛出异常则终止读取
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.error("解析数据出现异常,但会继续解析下一行数据", exception);
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            log.error("第{}行，第{}列，数据={}解析异常", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(),
                    excelDataConvertException.getCellData());
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析玩，最终会调用该方法：{}", JSONArray.toJSONString(cachedDataList));
        // 保存最后一次分页的数据
        userService.saveData(cachedDataList);
    }
}

2.2.3 Controller

@RequestMapping("/excel")
public class EasyExcelController {

    @Autowired
    private UserService userService;

    @PostMapping("/upload")
    public void uploadExcel(MultipartFile file) throws IOException {
    	// 默认是不读取注释、超链接、合并单元格等信息，要想读必须显式设置
        EasyExcel.read(file.getInputStream(), SimpleUserInfo.class, new CustomPageReadListener(userService))
    		.extraRead(CellExtraTypeEnum.COMMENT)
            .extraRead(CellExtraTypeEnum.HYPERLINK)
            .extraRead(CellExtraTypeEnum.MERGE)
            .sheet()
            .headRowNumber(3)
            .doRead();
    }
}    

headMap
{
    "0": {
        "columnIndex": 0, 
        "dataFormatData": {
            "format": "General", 
            "index": 0
        }, 
        "rowIndex": 2, 
        "stringValue": "用户名", 
        "type": 0
    }, 
    "1": {
        "columnIndex": 1, 
        "dataFormatData": {
            "$ref": "$.dataFormatData"
        }, 
        "rowIndex": 2, 
        "stringValue": "姓名", 
        "type": 0
    }, 
    "3": {
        "columnIndex": 3, 
        "dataFormatData": {
            "$ref": "$.dataFormatData"
        }, 
        "rowIndex": 2, 
        "stringValue": "手机号", 
        "type": 0
    }, 
    "4": {
        "columnIndex": 4, 
        "dataFormatData": {
            "$ref": "$.dataFormatData"
        }, 
        "rowIndex": 2, 
        "stringValue": "加入时间", 
        "type": 0
    }, 
    "5": {
        "columnIndex": 5, 
        "dataFormatData": {
            "$ref": "$.dataFormatData"
        }, 
        "rowIndex": 2, 
        "stringValue": "身价", 
        "type": 0
    }, 
    "6": {
        "columnIndex": 6, 
        "dataFormatData": {
            "$ref": "$.dataFormatData"
        }, 
        "rowIndex": 2, 
        "stringValue": "状态", 
        "type": 0
    }
}

integerStringMap={0=用户名, 1=姓名, 2=null, 3=手机号, 4=加入时间, 5=身价, 6=状态}

[
    {
        "amount": 6666666, 
        "createTime": 1663913277003, 
        "homePage": "https://blog.csdn.net/vbirdbest", 
        "name": "小龙女", 
        "phone": "166*****666", 
        "status": "正常", 
        "usernameCellData": {
            "data": "xiaolongnv", 
            "dataFormatData": {
                "format": "General", 
                "index": 0
            }, 
            "stringValue": "xiaolongnv", 
            "type": 0
        }
    }, 
    {
        "amount": 8888888, 
        "createTime": 1663913277003, 
        "homePage": "https://blog.csdn.net/vbirdbest", 
        "name": "任莹莹", 
        "phone": "188*****888", 
        "status": "锁定", 
        "usernameCellData": {
            "data": "renyingying", 
            "dataFormatData": {
                "format": "General", 
                "index": 0
            }, 
            "stringValue": "renyingying", 
            "type": 0
        }
    }
]

2.3 读多个Sheet

try (ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build()) {
    ReadSheet readSheet1 = EasyExcel.readSheet(0)
    	.head(SimpleUserInfo.class)
    	.registerReadListener(new CustomPageReadListener(userService))
    	.headRowNumber(3)
    	.build();
    ReadSheet readSheet2 = EasyExcel.readSheet(0)
    	.head(SimpleUserInfo2.class)
    	.registerReadListener(new CustomPageReadListener2(userService2))
    	.headRowNumber(3)
    	.build();
    excelReader.read(readSheet1, readSheet2);
}

2.4 读没有表头

@Slf4j
public class CustomAnalysisEventListener extends AnalysisEventListener<Map<Integer, String>> {
    private static final int BATCH_COUNT = 100;
    private List<Map<Integer, String>> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private UserService userService;

    public CustomAnalysisEventListener(UserService userService) {
        // 这里可以传入spring的bean
        this.userService = userService;
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        // {0:"xiaolongnv",1:"小龙女",3:"16666666666",4:"2022年09月23日14时09分57秒",5:"6,666,666",6:"正常"}
        // {0:"renyingying",1:"任莹莹",3:"18888888888",4:"2022年09月23日14时09分57秒",5:"8,888,888",6:"锁定"}
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            userService.saveDataList(cachedDataList);
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        userService.saveDataList(cachedDataList);
        log.info("所有数据解析完成！");
    }
}

// {0:"xiaolongnv",1:"小龙女",3:"16666666666",4:"2022年09月23日14时09分57秒",5:"6,666,666",6:"正常"}
// {0:"renyingying",1:"任莹莹",3:"18888888888",4:"2022年09月23日14时09分57秒",5:"8,888,888",6:"锁定"}
EasyExcel.read(file.getInputStream(), new CustomAnalysisEventListener(userService))
     .sheet()
     .headRowNumber(3)
     .doRead();