java将数据库百万数据量导出到EXCEL

一、背景

今天接到一个需求，需要将数据库中数据导出下载成EXCEL，初看是个比较简单的功能，采用POI中自带的EXCEL导出即可，细想之下隐约记起EXCEL是有数量限制的，同时表中的数据量是在不短叠加的，真实数据导入后突破百万。

二、EXCEL限制

1、Excel 2003及以下的版本。一张表最大支持65536行数据，256列。也就是说excel2003完全不可能满足百万数据导出的需求。
2、Excel 2007-2010版本。一张表最大支持1048576行，16384列；

三、解决思路

仔细考虑后有一个初步的思路，将所有数据分别写入EXCEL的多个sheet中，通过分页查询实现每一个sheet写入一页的数据，同时为了提高下载速度，需要启用多线程，在每一个线程中实现当前线程下的分页查询以及写入一个sheet，话不多说，下面上代码：

四、代码模块

前面的数据库查询，以及将表中字段封装成一个list就不做详细描述了，这里核心代码如下：
1、封装的基础类（里面是mybatisplus的service以及其他参数，UTIL工具类中解决不了CountDownLatch 注入bean的问题）：

@Data
public class ExcelDTO {

    private CountDownLatch countDownLatch;

    private Sheet sheet;

    private List<TsryColumn> title;

    private CellStyle style;

    private Integer preSheetLimit;

    private Integer pageNum;

    private String type;

    private AidsService aidsService;

    private DrugService drugService;

    private TeenagerService teenagerService;

    private MentalIllnessService mentalIllnessService;

    private CommunityCorrectionService communityCorrectionService;

    private FullSentenceService fullSentenceService;

    private String dataFiled;
}

    public void exportSxssfExcel(HttpServletResponse response,String fileNames,Integer num,List<TsryColumn> list,String type,String dataFiled){
        //excel文件名
        String fileName = fileNames + ".xlsx";
        //sheet名
        if (num == 0){
            throw new RuntimeException("数据资源为空");
        }
        long start = System.currentTimeMillis();
        SXSSFWorkbook wb = getSXSSFWorkbookByPageThread(list, num,preSheetLimit,type,dataFiled);
        long millis = System.currentTimeMillis() - start;
        long second = millis / 1000;
        System.out.println("SXSSF Page Thread 导出" + num + "条数据，花费：" + second + "s/ " + millis + "ms");
        writeAndClose(response, fileName, wb);
        wb.dispose();
    }

    public SXSSFWorkbook getSXSSFWorkbookByPageThread(List<TsryColumn> list, Integer count,Integer preSheetLimit,String type,String dataFiled){
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //需要的sheet表数量
        int pageNum = count / preSheetLimit;
        //最后一个sheet表的数量
        int lastCount = count % preSheetLimit;

//        if (values.length > PER_SHEET_LIMIT) {
        CellStyle style = wb.createCellStyle();
        int sheet = lastCount == 0 ? pageNum : pageNum + 1;
        CountDownLatch downLatch = new CountDownLatch(sheet);
        Executor executor = Executors.newFixedThreadPool(sheet);
        for (int c = 0; c <= pageNum; c++) {
            int rowNum = preSheetLimit;
            if (c == pageNum) {
                if (lastCount == 0) {
                    continue;
                }
                rowNum = lastCount;
            }
            Sheet sheetExc = wb.createSheet("sheet" + c);
            //循环开启多线程，并在多线程中实现分页查询
            ExcelDTO dto = new ExcelDTO();
            dto.setCountDownLatch(downLatch);
            dto.setSheet(sheetExc);
            dto.setTitle(list);
            dto.setStyle(style);
            dto.setPreSheetLimit(preSheetLimit);
            dto.setPageNum(c+1);
            dto.setType(type);
            dto.setAidsService(aidsService);
            dto.setDrugService(drugService);
            dto.setTeenagerService(teenagerService);
            dto.setMentalIllnessService(mentalIllnessService);
            dto.setCommunityCorrectionService(communityCorrectionService);
            dto.setFullSentenceService(fullSentenceService);
            dto.setDataFiled(dataFiled);
            executor.execute(new PageTask(dto));
        }
        try {
            downLatch.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
//        }
        return wb;
    }

private void writeAndClose(HttpServletResponse response, String fileName, Workbook wb) {
        //构建内容
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2、多线程中写EXCEL列标题以及每一行数据

@Mapper
public class PageTask implements Runnable{

    private ExcelDTO excelDTO;


    public PageTask(ExcelDTO dto) {
            this.excelDTO = dto;
    }

    @Override
    public void run() {
        try {
            //根据type去查询相关的表
            List<Map<String, String>> returnList = getReturnList("1", excelDTO.getPageNum(), excelDTO.getPreSheetLimit());

            Row row = excelDTO.getSheet().createRow(0);
            Cell cell = null;
            int cellNum = 0;
            for (int i = 0; i < excelDTO.getTitle().size(); i++) {
                if (excelDTO.getDataFiled().contains(excelDTO.getTitle().get(i).getColumnName())){
                    cell = row.createCell(cellNum);
                    cell.setCellValue(excelDTO.getTitle().get(i).getColumnComment());
                    cell.setCellStyle(excelDTO.getStyle());
                    cellNum = cellNum+1;
                }
            }

            for (int i = 0; i < returnList.size(); i++) {
                Integer cellNumber = 0;
                row = excelDTO.getSheet().createRow(i + 1);
                Map<String, String> map = returnList.get(i);
                for (Map.Entry<String, String> integerStringEntry : map.entrySet()) {
                    //判断并过滤掉用户已选择的字段
                    String[] split = integerStringEntry.getKey().split(",");
                    if (excelDTO.getDataFiled().contains(split[1])){
                        row.createCell(cellNumber).setCellValue(integerStringEntry.getValue());
                        cellNumber = cellNumber+1;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (excelDTO.getCountDownLatch() != null) {
                excelDTO.getCountDownLatch().countDown();
            }
        }
    }


    public List<Map<String,String>> getReturnList(String type, Integer pageNum, Integer pagesize){
        List<Map<String,String>> list = new ArrayList<>();
        switch (type) {
            case "1":
                Page<Aids> aidsPage = new Page(pageNum,pagesize);
                Page<Aids> pageAids = excelDTO.getAidsService().page(aidsPage);
                for (int i=0;i<pageAids.getRecords().size();i++){
                    Aids aids = pageAids.getRecords().get(i);
                    list.add(dealAidsMap(aids));
                }
                return list;
            default:
                throw new RuntimeException("数据资源类型有误，请检查");
        }
    }



    public  Map<String,String> dealAidsMap(Aids data){
        Map<String,String> map = new HashMap<>(16);
        map.put("0"+","+"RID",data.getRid());
        map.put("1"+","+"RNAME",data.getRname());
        map.put("2"+","+"RSEX",data.getRsex());
        map.put("3"+","+"RBIRTH",String.valueOf(data.getRbirth()));
        map.put("4"+","+"RNATION",data.getRnation());
        map.put("5"+","+"MARRIAGER",data.getMarriager());
        map.put("6"+","+"POLITICAL",data.getPolitical());
        map.put("7"+","+"REDUCATION",data.getReducation());
        map.put("8"+","+"PROFESSIONTYPE",data.getProfessionType());
        map.put("9"+","+"RTEL",data.getRtel());
        map.put("10"+","+"RADDRESS_PROVINCE",data.getRaddressProvince());
        map.put("11"+","+"DETAILADDRESS",data.getDetailaddress());
        map.put("12"+","+"RXDIZHI_PROVINCE",data.getRxdizhiProvince());
        map.put("13"+","+"NOW_DETAILADDRESS",data.getDetailaddress());
        map.put("14"+","+"PROFESSION",data.getProfession());
        map.put("15"+","+"INFECTION_PATH",data.getInfectionPath());
        map.put("16"+","+"ISCRIMINALHISTORY",data.getIscriminalhistory());
        map.put("17"+","+"ATTENTION",data.getAttention());
        map.put("18"+","+"HELP_NAME",data.getHelpName());
        map.put("19"+","+"HELP_PHONE",data.getHelpPhone());
        map.put("20"+","+"RADDRESS_CITY",data.getRaddressCity());
        map.put("21"+","+"RADDRESS_AREA",data.getRaddressArea());
        map.put("22"+","+"RXDIZHI_CITY",data.getRxdizhiCity());
        map.put("23"+","+"RXDIZHI_AREA",data.getRxdizhiArea());
        map.put("24"+","+"NATIVEPLACE_PROVINCE",data.getNativeplaceProvince());
        map.put("25"+","+"NATIVEPLACE_CITY",data.getNativeplaceCity());
        map.put("26"+","+"NATIVEPLACE_AREA",data.getNativeplaceArea());
        map.put("27"+","+"ISDB",data.getIsdb());
        map.put("28"+","+"ISTEMPRELIEF",data.getIsTempRelief());
        map.put("29"+","+"ISDESTITUTE",data.getIsDestitute());
        map.put("30"+","+"MEDICAL_CARD",data.getMedicalCard());
        map.put("31"+","+"ISMEDICAL_HELP",data.getIsMedicalHelp());
        map.put("32"+","+"ISXSMZZ",data.getIsxsmzz());
        map.put("33"+","+"ISEDUCATION",data.getIsEducation());
        map.put("34"+","+"ISROOMHELP",data.getIsRoomHelp());
        map.put("35"+","+"ISEMPLOYTRAIN",data.getIsEmployTrain());
        map.put("36"+","+"ISSKILLSTRAIN",data.getIsSkillaTrain());
        map.put("37"+","+"ISDISCOUNT",data.getIsDiscount());
        map.put("38"+","+"ISUNBLOCK",data.getIsUnblock());
        map.put("39"+","+"LIVECODE",data.getLiveCode());
        map.put("40"+","+"ISEMPLOYMENT",data.getIsEmployment());
        map.put("41"+","+"UNBLOCKDATE",String.valueOf(data.getUnblockDate()));
        map.put("42"+","+"HEAD",data.getHead());
        map.put("43"+","+"STREETPRINCIPALNAME",data.getStreetPrincipalName());
        map.put("44"+","+"COMMUNITYPRINCIPALNAME",data.getCommunityPrincipalName());
        map.put("45"+","+"WORKDATE",String.valueOf(data.getWorkDate()));
        map.put("46"+","+"TWPASS",data.getTwpass());
        map.put("47"+","+"GAPASS",data.getGapass());
        map.put("48"+","+"PASSPORT",data.getPassport());
        map.put("49"+","+"ISMEDICALBX",data.getIsMedicalbx());
        map.put("50"+","+"MEDICALTYPE",data.getMedicaltype());
        return map;
    }

}

需要的小伙伴可以直接把代码copy下来，但是肯定会报错，因为缺少，service,换成你们自己的表对应的service就可以直接用了。
最后有更好方法的小伙伴，也欢迎在下方指正。