easyExcel分批導(dǎo)入文件方式

更新時(shí)間：2023年06月09日 09:52:31 作者：molaiya

這篇文章主要介紹了easyExcel分批導(dǎo)入文件方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

easyExcel分批導(dǎo)入文件

一些關(guān)于easyExcel導(dǎo)入文件操作

需求: 導(dǎo)入大數(shù)據(jù)量文件其中數(shù)據(jù)達(dá)到萬(wàn)級(jí)、十萬(wàn)級(jí), 錯(cuò)誤文件進(jìn)行錯(cuò)誤單元格標(biāo)紅, 可導(dǎo)出修改完繼續(xù)導(dǎo)入

由于數(shù)據(jù)量多大一次行全部讀到內(nèi)存中可能會(huì)導(dǎo)致內(nèi)存溢出問(wèn)題

使用easyExcel poi的監(jiān)聽(tīng)器進(jìn)行操作

三步曲

1、解析excel為inputStream流, 讀取流,解析excel

2、判斷excel中每條數(shù)據(jù)的格式, 正確和錯(cuò)誤相對(duì)記錄

3、通過(guò)監(jiān)聽(tīng)器每解析150條數(shù)據(jù), 進(jìn)行入庫(kù)操作, 錯(cuò)誤數(shù)據(jù)存在內(nèi)存中(考慮錯(cuò)誤數(shù)據(jù)不多的情況)

// 這里用到ossfs 反正就是讀取excel為input流,
涉及到兩個(gè)系統(tǒng)之間流的傳輸, 這里直接把文件上傳到oss
try {
    in = new FileInputStream(localFileName);
} catch (FileNotFoundException e) {
    in = HttpUtil.io(HttpUtil.Atom.builder().url(diseaseDto.getFileUrl()).build());
}

// 這里解析excel其中
OltHosIcdDiseaseListener為自定義監(jiān)聽(tīng)器
try {
    LoggerUtil.info(LOGGER, "開(kāi)始解析IcdDisease");
    OltHosIcdDiseaseListener oltHosIcdDiseaseListener = new OltHosIcdDiseaseListener(isCfgPrd, icdCodeList, delIcdCodeList, diseaseDto, oltConfigService, exportTaskHandler);
    excelReader = EasyExcel.read(in, oltHosIcdDiseaseListener).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
} finally {
    try {
        if (in != null) {
            in.close();
        }
        if (excelReader != null) {
            // 這里千萬(wàn)別忘記關(guān)閉，讀的時(shí)候會(huì)創(chuàng)建臨時(shí)文件，到時(shí)磁盤(pán)會(huì)崩的
            excelReader.finish();
        }
    } catch (Exception e) {
        LoggerUtil.error(LOGGER, "{0},{1}", e.getMessage(), e);
    }
}

// 通過(guò)構(gòu)造函數(shù), 初始化一些list與對(duì)象
// 這個(gè)是導(dǎo)入的核心方法, 所有的導(dǎo)入邏輯, 判斷邏輯與入庫(kù)都在這里操作
// 采用無(wú)對(duì)象方式
@Slf4j
public class OltHosIcdDiseaseListener extends AnalysisEventListener<Map<Integer, String>> {
    private OltConfigService oltConfigService;
    private ExportTaskHandler exportTaskHandler;
    private static final int batchCount = 150;
    private int countNum = 0;
    private boolean isCfgPrd;
    private int successCount = 0;
    private int errorCount = 0;
    private List<String> checkRepeatCode = new ArrayList<>();
    private List<String> icdCodeList;
    private List<String> delIcdCodeList;
    private OltHosIcdDiseaseDto diseaseDto;
    private List<OltHosIcdDiseaseDto> successList = new ArrayList<>();
    private List<OltHosIcdDiseaseDto> errorList = new ArrayList<>();
    private List<OltHosIcdDiseaseDto> tempErrorList = new ArrayList<>();
    public OltHosIcdDiseaseListener(boolean isCfgPrd, List<String> icdCodeList, List<String> delIcdCodeList, OltHosIcdDiseaseDto diseaseDto,
                                    OltConfigService oltConfigService, ExportTaskHandler exportTaskHandler) {
        this.isCfgPrd = isCfgPrd;
        this.icdCodeList = icdCodeList;
        this.delIcdCodeList = delIcdCodeList;
        this.diseaseDto = diseaseDto;
        this.oltConfigService = oltConfigService;
        this.exportTaskHandler = exportTaskHandler;
    }
/**
  * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來(lái)調(diào)用
  * data --> 實(shí)體類(lèi)
  * analysisContext excel信息
 */
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
    int rouNumber = context.readRowHolder().getRowIndex() + 1;
    // 這里是因?yàn)楸眍^在第二行
    if (rouNumber == 2) {
        // 這里是校驗(yàn)表頭
        checkExcelHead(data);
    } else if (rouNumber > 2) {
        // 這里是校驗(yàn)數(shù)據(jù)
        checkReadData(data);
    }
    // 超過(guò)150條就先入庫(kù)
    if (countNum >= batchCount) {
        // 處理excel導(dǎo)出的正確數(shù)據(jù)
        batchOperateData();
    }
    countNum++;
}
/**
 * @author songhc
 * @create 
 * @desc 調(diào)用完成監(jiān)聽(tīng), 確保數(shù)據(jù)已全部處理完
 **/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    // 這里也要保存數(shù)據(jù)，確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫(kù)
    Map<String, Object> objMap = new HashMap<>();
    // 處理excel導(dǎo)出的正確數(shù)據(jù)
    batchOperateData();
    // 錯(cuò)誤數(shù)據(jù)填充oss表格
    Object object = uploadErrorData(errorList, diseaseDto);
    objMap.put("errorInfo", object);
    objMap.put("successCount", successCount);
    objMap.put("errorCount", errorCount);
    // 錯(cuò)誤數(shù)據(jù)記錄redis, 下次使用
    RedisStringHandler.set(String.format(RedisKeyConstants.EXPORT_ERROR_RESULT, "disease" + diseaseDto.getUserId() + "_" + diseaseDto.getRgId() + "_" + diseaseDto.getHosId()), JSONObject.toJSONString(objMap));
}
// 這里是封裝所有的錯(cuò)誤數(shù)據(jù)
// 包括封裝單元格
private Object uploadErrorData (List<OltHosIcdDiseaseDto> errorList, OltHosIcdDiseaseDto dto) {
    Map<Integer, List<Integer>> map = new HashMap<>();
    LinkedList<OltHosIcdDiseaseDto> newErrorList = new LinkedList<>();
    if (CollectionUtils.isNotEmpty(errorList)) {
        for (int i = 0; i < errorList.size(); i++) {
            OltHosIcdDiseaseDto e = errorList.get(i);
            List<Integer> integerList = new ArrayList<>();
            if (e.getErrorReasonMap() != null && !e.getErrorReasonMap().isEmpty()) {
                List<String> reasonList = new ArrayList<>();
                for (Integer key: e.getErrorReasonMap().keySet()) {
                    // 標(biāo)紅單元格
                    integerList.add(key);
                    reasonList.add(e.getErrorReasonMap().get(key));
                }
                map.put(i + 2, integerList);
                e.setErrorReason(String.join("、", reasonList));
            }
            newErrorList.add(e);
        }
    }
    // 封裝導(dǎo)出服務(wù)入?yún)?
    String uuid = UUIDUtil.create();
    String errorFileName = dto.getHosName() + "(待處理診斷數(shù)據(jù))" + dto.getStatDataStr() + ".xlsx";
    SysExportRecordDto sysExportRecordDto = SysExportRecordDto.builder().batchId(uuid).userId(dto.getUserId()).pfCode(dto.getPfCode())
            .source(dto.getSource()).fileName(errorFileName).creator(dto.getCreator()).operator(dto.getCreator()).build();
    // 創(chuàng)建導(dǎo)出記錄
    QueueHandler.createTaskRecord(sysExportRecordDto);
    // 獲取url
    // 偽代碼
    String fileName = "aaa.xlsx";
    String BUCKET_NAME = "bbb";
    String fileUrl = String.format(OssClientConfig.OSS_REAL_PATH, BUCKET_NAME,
            UploadFileType.getFolderByType(UploadFileType.REPORT)).concat(fileName);
    // 加入異步線(xiàn)程任務(wù)
    this.exportTaskHandler.exportIcdErrorDiseaseData(OltErrorResult.builder().map(map).errorList(newErrorList)
                    .fileName(errorFileName).source(dto.getSource()).build(),
            uuid, errorFileName, fileUrl);
    // 構(gòu)建返回隊(duì)列信息
    return QueueHandler.buildQueueInfo(sysExportRecordDto);
}
private void batchOperateData() {
    checkErrorExcelList(tempErrorList, icdCodeList);
    checkSuccessExcelList(successList, tempErrorList, icdCodeList);
    // 將臨時(shí)錯(cuò)誤數(shù)據(jù)存儲(chǔ)到所有錯(cuò)誤數(shù)據(jù)列表
    this.errorList.addAll(tempErrorList);
    // 清理list
    this.successList.clear();
    this.tempErrorList.clear();
    this.countNum = 0;
}
private void checkExcelHead(Map<Integer, String> data) {
    boolean templateFlag = true;
    // 第二行  校驗(yàn)excel標(biāo)題
    try {
        String diseaseCategoryStr = data.get(0);
        if (StringUtils.isBlank(diseaseCategoryStr) || !"診eee（必填）".equals(diseaseCategoryStr)) {
            templateFlag = false;
        }
    } catch (Exception e) {
        templateFlag = false;
    }
    try {
        String icdNameStr = data.get(1);
        if (StringUtils.isBlank(icdNameStr) || !"醫(yī)vv稱(chēng)（必填）".equals(icdNameStr)) {
            templateFlag = false;
        }
    } catch (Exception e) {
        templateFlag = false;
    }
    try {
        String icdCodeStr = data.get(2);
        if (StringUtils.isBlank(icdCodeStr) || !"醫(yī)aa（必填）".equals(icdCodeStr)) {
            templateFlag = false;
        }
    } catch (Exception e) {
        templateFlag = false;
    }
    if (!templateFlag) {
        throw new PlatException("文件模版不匹配");
    }
}
private void checkReadData(Map<Integer, String> data) {
    // 循環(huán)cell
    OltHosIcdDiseaseDto temDisDto = OltHosIcdDiseaseDto.buildDefault();
    temDisDto.setHosId(diseaseDto.getHosId());
    // key為所在的列, value為錯(cuò)誤原因
    Map<Integer, String> map = new HashMap<>();
    boolean flag = true;
    try {
        // 解析第二列
        String diseaseCategory = data.get(0);
        if (StringUtils.isBlank(diseaseCategory)) {
            temDisDto.setDiseaseCategoryStr(StringUtils.EMPTY);
            map.put(0, "aaa為空");
            flag = false;
        } else {
            temDisDto.setDiseaseCategoryStr(diseaseCategory);
        }
    } catch (Exception e) {
        temDisDto.setDiseaseCategoryStr(StringUtils.EMPTY);
        map.put(0, "bbb為空");
        flag = false;
    }
    try {
        String icdName = data.get(1);
        if (StringUtils.isBlank(icdName)) {
            temDisDto.setIcdName(StringUtils.EMPTY);
            map.put(1, "為空");
            flag = false;
        } else {
            temDisDto.setIcdName(icdName);
        }
    } catch (Exception e) {
        temDisDto.setIcdName(StringUtils.EMPTY);
        map.put(1, "ccc稱(chēng)為空");
        flag = false;
    }
    try {
        String icdCode = data.get(2);
        if (StringUtils.isBlank(icdCode)) {
            temDisDto.setIcdCode(StringUtils.EMPTY);
            map.put(2, "ddd為空");
            flag = false;
        } else {
            temDisDto.setIcdCode(icdCode);
        }
    } catch (Exception e) {
        temDisDto.setIcdCode(StringUtils.EMPTY);
        map.put(2, "ddd為空");
        flag = false;
    }
    try {
        if (!DiseaseCategory.TCM_SYNDROME.getDesc().equals(temDisDto.getDiseaseCategoryStr())) {
            String standardIcdName = data.get(3);
            if (isCfgPrd && StringUtils.isBlank(standardIcdName)) {
                temDisDto.setStandardIcdName(StringUtils.EMPTY);
                map.put(3, "vvv為空");
                flag = false;
            } else {
                temDisDto.setStandardIcdName(standardIcdName);
            }
        }
    } catch (Exception e) {
        temDisDto.setStandardIcdName(StringUtils.EMPTY);
        map.put(3, "vvv為空");
        flag = false;
    }
    try {
        if (!DiseaseCategory.TCM_SYNDROME.getDesc().equals(temDisDto.getDiseaseCategoryStr())) {
            String standardIcdCode = data.get(4);
            if (isCfgPrd && StringUtils.isBlank(standardIcdCode)) {
                temDisDto.setStandardIcdCode(StringUtils.EMPTY);
                map.put(4, "eee為空");
                flag = false;
            } else {
                temDisDto.setStandardIcdCode(standardIcdCode);
            }
        }
    } catch (Exception e) {
        temDisDto.setStandardIcdCode(StringUtils.EMPTY);
        map.put(4, "eee為空");
        flag = false;
    }
    temDisDto.setErrorReasonMap(map);
    // 如果flag為 false 說(shuō)明數(shù)據(jù)有問(wèn)題
    if (!flag) {
        tempErrorList.add(temDisDto);
    } else {
        successList.add(temDisDto);
    }
}
private void checkErrorExcelList(List<OltHosIcdDiseaseDto> errorList, List<String> icdCodeList) {
    if (CollectionUtils.isNotEmpty(errorList)) {
        // 錯(cuò)誤就往里加, 正確重新定義列表
        errorList.forEach(e -> {
            Map<Integer, String> map = new HashMap<>();
            if (!DiseaseCategory.belongTo(e.getDiseaseCategoryStr())) {
                map.put(0, "aaa不正確");
            } else {
                e.setDiseaseCategory(DiseaseCategory.getCodeByDesc(e.getDiseaseCategoryStr()));
            }
            // excel是否存在重復(fù)數(shù)據(jù)
            if (checkRepeatCode.contains(e.getIcdCode())) {
                map.put(2, "bbb重復(fù)");
            }
            if (CollectionUtils.isNotEmpty(icdCodeList) && icdCodeList.contains(e.getIcdCode())) {
                map.put(2, "ttt重復(fù)");
            }
            if (e.getErrorReasonMap() != null && !e.getErrorReasonMap().isEmpty()) {
                Map<Integer, String> errorReasonMap = e.getErrorReasonMap();
                errorReasonMap.putAll(map);
                e.setErrorReasonMap(errorReasonMap);
            }
            errorCount++;
        });
    }
}
/**
 * 侵入式給errorList賦值
 * @param list
 * @param errorList
 * @param icdCodeList
 */
private void checkSuccessExcelList(List<OltHosIcdDiseaseDto> list, List<OltHosIcdDiseaseDto> errorList,
                                   List<String> icdCodeList) {
    List<OltHosIcdDiseaseDto> newList = new ArrayList<>();
    if (CollectionUtils.isNotEmpty(list)) {
        // 錯(cuò)誤就往里加, 正確重新定義列表
        list.forEach(e -> {
            Map<Integer, String> map = new HashMap<>();
            boolean flag = false;
            // 判
            if (!DiseaseCategory.belongTo(e.getDiseaseCategoryStr())) {
                map.put(0, "不正確");
                flag = true;
            } else {
                e.setDiseaseCategory(DiseaseCategory.getCodeByDesc(e.getDiseaseCategoryStr()));
            }
            // excel是否存在重復(fù)數(shù)據(jù)
            if (checkRepeatCode.contains(e.getIcdCode())) {
                map.put(2, "重復(fù)");
                flag = true;
            } else {
                // 判斷診斷編碼
                if (CollectionUtils.isNotEmpty(icdCodeList) && icdCodeList.contains(e.getIcdCode())) {
                    map.put(2, "重復(fù)");
                    flag = true;
                }
            }
            e.setErrorReasonMap(map);
            if (flag) {
                errorCount++;
                errorList.add(e);
            } else {
                e.setIcdPinyin(HzUtils.getPinyinCap(e.getIcdName(), HzUtils.CaseType.UPPERCASE));
                e.setIcdWb(HzUtils.getWbCap(e.getIcdName(), HzUtils.CaseType.UPPERCASE));
                newList.add(e);
                checkRepeatCode.add(e.getIcdCode());
                successCount++;
            }
        });
    }
    // 正確數(shù)據(jù)入庫(kù)
    if (CollectionUtils.isNotEmpty(newList)) {
        oltConfigService.batchAddHosIcdDisease(delIcdCodeList, newList);
    }
}

其中,導(dǎo)入錯(cuò)誤數(shù)據(jù)用了easyExcel的模版填充方式, 模版存于oss上

/**
?* @author songhc
?* @create
?* @desc 導(dǎo)出錯(cuò)誤數(shù)據(jù)
?**/
@Async
public void exportIcdErrorDiseaseData(OltErrorResult dto, String fileBatch, String fileName, String fileUrl) {
? ? Map<Integer, List> map = new HashMap<>();
? ? map.put(0, dto.getErrorList());
? ? Map<Integer, Map<Integer, List<Integer>>> styleMap = new HashMap<>();
? ? styleMap.put(0, dto.getMap());
? ? ExportExistHandler.exportExistTemplateData(map, styleMap, fileBatch, fileName, fileUrl);
}

接下來(lái)就是填充錯(cuò)誤模版的實(shí)現(xiàn)

/**
 * @param errorMap key為sheetNo, value為填充的數(shù)據(jù)
 * @param styleMap key為sheetNo, value為錯(cuò)誤數(shù)據(jù)坐標(biāo)
 * @param fileBatch 批次號(hào)
 * @param fileName 文件名
 * @param fileUrl 文件路徑
 * @description 導(dǎo)出服務(wù)封裝方法(無(wú)需分頁(yè)查詢(xún), 數(shù)據(jù)為動(dòng)態(tài)傳入)
 * @className exportNoModelData
 */
public static void exportExistTemplateData(Map<Integer, List> errorMap, Map<Integer, Map<Integer, List<Integer>>> styleMap, String fileBatch, String fileName, String fileUrl) {
    String ossFileName = fileName.substring(0, fileName.lastIndexOf('.'))
            .concat("-").concat(LocalDateTime.now()
                    .format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))).concat(fileName.substring(fileName.lastIndexOf('.')));
    InputStream inputStream = HttpUtil.io(HttpUtil.Atom.builder().url(fileUrl).build());
    if (null == inputStream) {
        return;
    }
    String localFileName = String.format(TaskNoteHandler.staticExportConfig.getExportPath(), ossFileName);
    ExcelWriter excelWriter = null;
    int resultCount = 0;
    try {
        if (errorMap != null && !errorMap.isEmpty()) {
            excelWriter = EasyExcel.write(localFileName)
                        .withTemplate(inputStream)
                        .build();
            // for循環(huán)是一個(gè)excel可能有多個(gè)sheet的兼容寫(xiě)法
            for (Integer i: errorMap.keySet()) {
                // 這里使用easyExcel的 registerWriteHandler 方法, 自定義CellColorSheetWriteHandler實(shí)現(xiàn), 給每一個(gè)單元格填充顏色
                WriteSheet writeSheet = EasyExcel.writerSheet(i).registerWriteHandler(new CellColorSheetWriteHandler(styleMap.get(i),
                        IndexedColors.RED1.getIndex())).build();
                excelWriter.fill(errorMap.get(i), writeSheet);
            }
        }
    } catch (Exception e){
        LoggerUtil.error(LOGGER, "文件寫(xiě)入異常，error{0}", e);
        // 文件導(dǎo)出失敗
        TaskNoteHandler.doUploadFailed(fileBatch, resultCount);
        return;
    } finally {
        // 關(guān)閉流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
    // 1、上傳文件（多種方案）；2、更新記錄
    TaskNoteHandler.doUploadAndNote(fileBatch, ossFileName, localFileName, resultCount);
}

/**
 * @description 自定義單元格格式攔截器
 * @className CellColorSheetWriteHandler
 * @package 
 * @Author songhc
 */
public class CellColorSheetWriteHandler implements CellWriteHandler {
    /**
     * map
     * key：第i行
     * value：第i行中單元格索引集合
     */
    private Map<Integer, List<Integer>> map;
    /**
     * 顏色
     */
    private Short colorIndex;
    /**
     * 有參構(gòu)造
     */
    public CellColorSheetWriteHandler(Map<Integer, List<Integer>> map, Short colorIndex) {
        this.map = map;
        this.colorIndex = colorIndex;
    }
    /**
     * 無(wú)參構(gòu)造
     */
    public CellColorSheetWriteHandler() {
    }
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }
    /**
     * 在單元格創(chuàng)建后調(diào)用
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }
    /**
     * 在單元上的所有操作完成后調(diào)用
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //當(dāng)前行的第i列
        int i = cell.getColumnIndex();
        // 根據(jù)單元格獲取workbook
        Workbook workbook = cell.getSheet().getWorkbook();
        //不處理第一行
        if (0 != cell.getRowIndex()) {
            List<Integer> integerList = map.get(cell.getRowIndex());
            // 自定義單元格樣式 
            if (CollectionUtils.isNotEmpty(integerList)) {
                if (integerList.contains(i)) {
                    // 單元格策略
                    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                    // 設(shè)置背景顏色白色
                    contentWriteCellStyle.setFillForegroundColor(colorIndex);
                    // 設(shè)置垂直居中為居中對(duì)齊
                     contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 設(shè)置左右對(duì)齊為中央對(duì)齊
                     contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.RIGHT);
                    // 設(shè)置單元格上下左右邊框?yàn)榧?xì)邊框
                      contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
                      contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
                     contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
                     contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
                    // 創(chuàng)建字體實(shí)例
                     WriteFont cellWriteFont = new WriteFont();
                    // 設(shè)置字體大小
                     cellWriteFont.setFontName("宋體");
                     cellWriteFont.setFontHeightInPoints((short) 10);
                    //設(shè)置字體顏色
                    // cellWriteFont.setColor(IndexedColors.BLACK1.getIndex());
                    //單元格顏色
                    //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                    contentWriteCellStyle.setWriteFont(cellWriteFont);
                    CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                    //設(shè)置當(dāng)前行第i列的樣式
                    cell.getRow().getCell(i).setCellStyle(cellStyle);
                }
            }
        }
    }
}

對(duì)于一個(gè)excel多sheet, 操作也是一樣

// 不同的是這里可以定義多個(gè)監(jiān)聽(tīng)器
// readSheet(0) ?—-> 這里的數(shù)據(jù)代表sheet的位置
OltDrugFrequencyListener oltDrugFrequencyListener = new OltDrugFrequencyListener(isCfgPrd, dfCodeList, frequencyDto, oltConfigService);
OltDrugUsageListener oltDrugUsageListener = new OltDrugUsageListener(isCfgPrd, dUCodeList, OltDrugUsageDto.builder().hosId(frequencyDto.getHosId()).build(), oltConfigService);
OltDrugDurationListener oltDrugDurationListener = new OltDrugDurationListener(durationCodeList, OltDrugDurationDefDto.builder().hosId(frequencyDto.getHosId()).build(), oltConfigService);
ReadSheet readSheet = EasyExcel.readSheet(0).registerReadListener(oltDrugFrequencyListener).build();
ReadSheet readSheet2 = EasyExcel.readSheet(2).registerReadListener(oltDrugUsageListener).build();
ReadSheet readSheet4 = EasyExcel.readSheet(4).registerReadListener(oltDrugDurationListener).build();
excelReader.read(readSheet, readSheet2, readSheet4);
經(jīng)過(guò)測(cè)試, 該方法導(dǎo)出2W條數(shù)據(jù)差不多需要10秒, 也不會(huì)影響內(nèi)存

EasyExcel導(dǎo)出但是沒(méi)有數(shù)據(jù)

我在使用EasyExcel做單據(jù)導(dǎo)出的時(shí)候，發(fā)現(xiàn)導(dǎo)出的文件里面表頭是完整展示了，但是表中的數(shù)據(jù)卻一片空白。表頭能完好展示說(shuō)明我的@ExcelProperty、@ExcelIgnore 注解都起到作用了，但是字段中的數(shù)據(jù)展示不出來(lái)又是什么原因呢？

查閱了相關(guān)資料后，才知道了是字段命名的原因。EasyExcel調(diào)用的get方法名字和@Data注解自動(dòng)生產(chǎn)的get方法是不同的。

解決方法

每個(gè)字段的第二個(gè)字母改為小寫(xiě)，如圖所示：

這樣的話(huà) Getter and Setter 的方法名就是這樣的：

    public String getForderNumber() {
        return forderNumber;
    }
    public void setForderNumber(String forderNumber) {
        this.forderNumber = forderNumber;
    }
    public LocalDateTime getForderTime() {
        return forderTime;
    }
    public void setForderTime(LocalDateTime forderTime) {
        this.forderTime = forderTime;
    }
    public String getFexportUser() {
        return fexportUser;
    }
    public void setFexportUser(String fexportUser) {
        this.fexportUser = fexportUser;
    }

結(jié)果：

數(shù)據(jù)也成功導(dǎo)出了！

補(bǔ)充

改前和改后的 Getter and Setter的方法名對(duì)比

//改前：fOrderNumber、fExportDepartment、......
//失敗
public String getfOrderNumber() {return fOrderNumber;}
public void setfOrderNumber(String fOrderNumber) {this.fOrderNumber = fOrderNumber;}
public String getfExportDepartment() {return fExportDepartment;}
public void setfExportDepartment(String fExportDepartment) {this.fExportDepartment = fExportDepartment;}
//改后:forderNumber、fexportDepartment、......
//成功
public String getForderNumber() {return forderNumber;}
public void setForderNumber(String forderNumber) {this.forderNumber = forderNumber;}
public String getFexportDepartment() {return fexportDepartment;}
public void setFexportDepartment(String fexportDepartment) {this.fexportDepartment = fexportDepartment;}