使用easyexcel导出大数据

项目中,需要导出10万条数据,单表数据量比较大

1.采用分页查询数据库

2.分页的数据分批写入sheet页

 

1.导出方法

 @Override
    public void exportAlarmEventlist(TabSearchBO searchBO, HttpServletResponse response) throws Exception {
       
        response.setContentType(RESPONSE_CONTENTTYPE_EXCEL);
        response.setCharacterEncoding(UTF8);
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("设备事件记录", "UTF-8")+".xlsx");
        OutputStream out = response.getOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out).build();

        searchBO.setPage(new Page(0, 1));
        Result<List<EventLogBO>> result = queryAlarmEventList(searchBO);
        int threadSize = 10000;
        //总数据条数:上限10万
        int dataSize = result.getTotal() > 100000 ? 100000: result.getTotal();
        log.error("查询事件记录总数:{}", dataSize);
        int rest = dataSize % threadSize;
        //线程数
        int threadNum = rest == 0 ? dataSize/threadSize : dataSize/threadSize + 1;
        List<EventLogBO> cutList = null;
        log.error("导出开始:{},sheetNum:{}", System.currentTimeMillis(),threadNum);
        AtomicInteger atomicInteger = new AtomicInteger(0);
        int startNum = 0;
        for (int i = 0; i < threadNum; i++) {
            startNum = threadSize * i;
            if (i == threadNum - 1 && rest != 0) {
                cutList = findByPage(startNum, rest,searchBO);
            } else {
                cutList = findByPage(startNum, threadSize,searchBO);
            }
            List<EventLogExcelBO> exportList = buildExportList(startNum+1,cutList, customAlarmLevelMap, alarmCauseClassifyMap);
            int num = atomicInteger.getAndIncrement();
            WriteSheet writeSheet = EasyExcel.writerSheet(num, num +1+"")
                                            .head(EventLogExcelBO.class)
                                            .registerWriteHandler(EasyExcelUtil.getDefalutStyle())
                                            .registerWriteHandler(new FreezeWriteHandler())
                                            .build();
            try {
                excelWriter.write(exportList, writeSheet);
            }catch (Exception e){
                log.error("写入sheet数据报错", e);
            }finally {
                log.error("写入sheet结束");
            }
        }
        excelWriter.finish();
        log.error("导出结束:{}", System.currentTimeMillis());
    }


-----------------------------------------------------------------------------------------
 /**
     * 分页查询事件源表
     * @param startNum
     * @param pageSize
     * @param searchBO
     * @return
     */
    private List<EventLogBO> findByPage(int startNum, int pageSize,TabSearchBO searchBO) {
        log.error("startNum-{},pageSize:{}", startNum,pageSize);
        searchBO.setPage(new Page(startNum,pageSize));
        List<EventLogBO> data = queryAlarmEventList(searchBO).getData();
        return data;
    }

----------------------------------------------------------------------------------------

 /**
     * 组装导出事件数据
     * @param startNum
     * @param data
     * @param customAlarmLevelMap
     * @param alarmCauseClassifyMap
     * @return
     */
    private List<EventLogExcelBO> buildExportList(int startNum,List<EventLogBO> data ,Map<Long, String> customAlarmLevelMap ,Map<Long, String> alarmCauseClassifyMap){
        if(CollectionUtils.isEmpty(data)){
            return Collections.emptyList();
        }
        List<EventLogExcelBO> exportList = new ArrayList<>(data.size());
        String confirmStatus = null;
        EventLogExcelBO eventLogExcelBO =null;
        for (EventLogBO log : data) {
            eventLogExcelBO = new EventLogExcelBO();
            eventLogExcelBO.setNumber(String.valueOf(startNum++));
            eventLogExcelBO.setEventclass(log.getEventClass() == null ? LEVERAGE : customAlarmLevelMap.get(log.getEventClass()));
            eventLogExcelBO.setPecname(log.getPecName() == null ? LEVERAGE : log.getPecName());
            eventLogExcelBO.setDiscriprion(log.getDescription() == null ? LEVERAGE : log.getDescription());
            eventLogExcelBO.setEventtype(log.getEventType() == null ? LEVERAGE : EnumUtil.getEnumValue(ModelLabelConstant.PEC_EVENT_TYPE, log.getEventType()));
            eventLogExcelBO.setMatchingtimestart(log.getMatchingTimeStart() == null ?LEVERAGE: DateUtils.formatDateTime(log.getMatchingTimeStart(), DateUtils.DF_YYYY_MM_DD_HH_MM_SS_SSS));
            eventLogExcelBO.setMatchingtimeend(log.getMatchingTimeEnd() == null ? LEVERAGE : DateUtils.formatDateTime(log.getMatchingTimeEnd(), DateUtils.DF_YYYY_MM_DD_HH_MM_SS_SSS));
            eventLogExcelBO.setUltralimitsustaintime(log.getUltralimitsustainTime() == null ? LEVERAGE : FormatUtil.formatString(divisions(log.getUltralimitsustainTime())));
            confirmStatus = EnumUtil.getEnumValue(ModelLabelConstant.CONFIRM_EVENT_STATUS, log.getConfirmeventstatus());
            eventLogExcelBO.setConfirmeventstatus(LEVERAGE.equals(confirmStatus) ? "未确认" : confirmStatus);
            eventLogExcelBO.setMalarmstatus(log.getMalarmStatus() == null ? LEVERAGE : alarmCauseClassifyMap.get(log.getMalarmStatus()));
            exportList.add(eventLogExcelBO);
        }
        return exportList;
    }


---------------------------------------------------------------------------------------
@Data
@HeadRowHeight(25)
public class EventLogExcelBO extends BaseRowModel {
    @ExcelProperty(value = { "设备事件记录","序号" }, index = 0)
    @ColumnWidth(10)
    private String number;

    @ExcelProperty(value = { "设备事件记录","等级" }, index = 1)
    @ColumnWidth(12)
    private String eventclass;

    @ExcelProperty(value = { "设备事件记录","设备名称" }, index = 2)
    @ColumnWidth(25)
    private String pecname;

    @ExcelProperty(value = { "设备事件记录","描述" }, index = 3)
    @ColumnWidth(70)
    private String discriprion;

    @ExcelProperty(value = { "设备事件记录", "类型" }, index = 4)
    @ColumnWidth(15)
    private String eventtype;

    @ExcelProperty(value = { "设备事件记录", "开始时间" }, index = 5)
    @ColumnWidth(25)
    private String matchingtimestart;

    @ExcelProperty(value = { "设备事件记录", "结束时间" }, index = 6)
    @ColumnWidth(25)
    private String matchingtimeend;

    @ExcelProperty(value = { "设备事件记录", "持续时间" }, index = 7)
    @ColumnWidth(25)
    private String ultralimitsustaintime;

    @ExcelProperty(value = { "设备事件记录", "状态" }, index = 8)
    @ColumnWidth(15)
    private String confirmeventstatus;

    @ExcelProperty(value = { "设备事件记录", "处理分类" }, index = 9)
    @ColumnWidth(20)
    private String malarmstatus;
}

------------------------------------------------------------------------------------
//冻结表头
public class FreezeWriteHandler implements SheetWriteHandler {


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.createFreezePane(3,2,3,2);
    }
}