java后端Excel表格导出 poi

java后端Excel表格导出

该篇的导出没有什么复杂的点,值得注意的一点是,一张Sheet页最多大概是65000条数据,当导出数据较多时,要自动切换sheet页,该代码写了此功能,注意查看.现在也有叫Csv的导出,就不用考虑数据溢出问题.可以查看其它博主的博客

	//Controller 注意是Post请求,Get请求走不通
    @ApiOperation(value = "代理销售额以及信息导出", notes = "代理销售额以及信息导出")
    @PostMapping("downLoadAgentAllInfo")
    public void downLoadAgentAllInfo(HttpServletResponse response, @RequestBody MemberSearchDTO memberSearchDTO) {
        umsMemberService.downLoadAgentAllInfo(response, memberSearchDTO);
    }
//Service
void downLoadAgentAllInfo(HttpServletResponse response, MemberSearchDTO memberSearchDTO);
//ServiceImpl
@Override
    public void downLoadAgentAllInfo(HttpServletResponse response, MemberSearchDTO memberSearchDTO) {
        Date beginTime = memberSearchDTO.getBeginTime();
        Date endTime = memberSearchDTO.getEndTime();
        Map map = new HashMap();
        if(beginTime != null){
            map.put("beginTime",beginTime);
        }
        if(endTime != null){
            map.put("endTime",endTime);
        }
        List<UmsAgentAllInfoDO> list  = new ArrayList<>();
        list = umsMemberMapper.selectAllData(map);


        if(list != null && list.size() > 0){

        }
        HSSFWorkbook wk = new HSSFWorkbook();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //每个sheet页导出的数据行数 60000
        int sheetRow = 65000;
        //一共几个sheet页
        int sheetNum = list.size() / sheetRow;
        for (int i = 0; i < sheetNum + 1; i++) {

            // 创建一张工作表
            HSSFSheet sheet2 = wk.createSheet("代理信息" + (i + 1));
            sheet2.setColumnWidth(0, 5000);
            HSSFRow row2 = sheet2.createRow(0);
            String[] head = new String[]{
                    "昵称", "用户id", "工号", "等级", "推荐人id", "推荐人工号", "推荐人等级", "新增Q数", "新增P数", "自己购买订单数","自己购买销量小计","双费合一自己下单数","横向下单数","纵向下单数","销量小计","订单数小计"
            };
            int headInt = 0;
            //创建表格头
            for (String title : head) {
                row2.createCell(headInt++).setCellValue(title);
            }
            int num = i * sheetRow;
            int index = 0;
            int rowInt = 1;
            for (int m = num; m < list.size(); m++) {
                if (index == sheetRow) {
                    break;
                }
                UmsAgentAllInfoDO umsAgentAllInfoDO = list.get(m);
                //塞值
                row2 = sheet2.createRow(rowInt++);
                row2.createCell(0).setCellValue(umsAgentAllInfoDO.getWxName());
                row2.createCell(1).setCellValue(umsAgentAllInfoDO.getMemberId() == null ?"":umsAgentAllInfoDO.getMemberId().toString());
                row2.createCell(2).setCellValue(umsAgentAllInfoDO.getCode());
                row2.createCell(3).setCellValue(umsAgentAllInfoDO.getLevel() == null?"":umsAgentAllInfoDO.getLevel().toString());
                row2.createCell(4).setCellValue(umsAgentAllInfoDO.getRefereeId() == null ?"":umsAgentAllInfoDO.getRefereeId().toString());
                row2.createCell(5).setCellValue(umsAgentAllInfoDO.getRefereeCode());
                row2.createCell(6).setCellValue(umsAgentAllInfoDO.getRefereeLevel() == null ?"":umsAgentAllInfoDO.getRefereeLevel().toString());
                row2.createCell(7).setCellValue(umsAgentAllInfoDO.getNumQ() == null?"":umsAgentAllInfoDO.getNumQ().toString() );
                row2.createCell(8).setCellValue(umsAgentAllInfoDO.getNumP() == null ?"":umsAgentAllInfoDO.getNumP().toString());
                row2.createCell(9).setCellValue(umsAgentAllInfoDO.getOrderNum() == null ?"":umsAgentAllInfoDO.getOrderNum().toString());
                row2.createCell(10).setCellValue(umsAgentAllInfoDO.getTotalPrice() == null ?"":umsAgentAllInfoDO.getTotalPrice().toString());
                row2.createCell(11).setCellValue(umsAgentAllInfoDO.getShuangfeiOrderNum() == null ?"":umsAgentAllInfoDO.getShuangfeiOrderNum().toString());
                row2.createCell(12).setCellValue(umsAgentAllInfoDO.getShuangfeiHengOrderNum() == null ?"":umsAgentAllInfoDO.getShuangfeiHengOrderNum().toString());
                row2.createCell(13).setCellValue(umsAgentAllInfoDO.getShuangfeiZongOrderNum() == null ?"":umsAgentAllInfoDO.getShuangfeiZongOrderNum().toString());
                row2.createCell(14).setCellValue(umsAgentAllInfoDO.getShuangfeiTotalPrice() == null ?"":umsAgentAllInfoDO.getShuangfeiTotalPrice().toString());
                row2.createCell(15).setCellValue(umsAgentAllInfoDO.getShuangfeiOrderTotalNum() == null ?"":umsAgentAllInfoDO.getShuangfeiOrderTotalNum().toString());
                index++;
            }
        }

        try {
            /**
             * 弹出下载选择路径框
             */
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=Opinion.xls");//默认Excel名称
            wk.write(response.getOutputStream());
            response.flushBuffer();
            logger.info("downLoadAgentAllInfo----->downLoadAgentAllInfo:导出成功");
        } catch (IOException e) {
            logger.error("downLoadAgentAllInfo----->downLoadAgentAllInfo:导出Execl异常");
            e.printStackTrace();
        } finally {
            try {
                wk.close();
            } catch (IOException e) {
                logger.error("downLoadAgentAllInfo----->downLoadAgentAllInfo:导出Execl,Io流关闭异常");
                e.printStackTrace();
            }
        }
    }