根据数据批量生成excel文件

第一步导入依赖:

 

<!--excel支持-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.11</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11</version>
</dependency>

第二步前端控制器实现:

 

/**
     * 日志下载
     * @param year
     * @param month
     * @param daily
     * @param name
     * @param depts
     * @param response
     * @return
     */
    @GetMapping("download")
    public ResponseEntity download(Integer year, Integer month, Integer daily, String name, String depts, HttpServletResponse response) throws ParseException {
        if(StringUtils.isEmpty(year)||StringUtils.isEmpty(month)){
            return new ResponseEntity(SupConstant.BAD_PARAMETER, HttpStatus.BAD_REQUEST);
        }
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        //起始时间
        Date stateTime;
        //结束时间
        Date endTime;
        if(StringUtils.isEmpty(daily)){
            stateTime=format.parse(year+"-"+month+"-01");
            if(month==12){
                endTime=format.parse((year+1)+"-01-01");
            }else {
                endTime=format.parse(year+"-"+(month+1)+"-01");
            }
        }else {
            stateTime=format.parse(year+"-"+month+"-"+daily);
            endTime=new Date(stateTime.getTime()+24*60*60*1000);
        }
        Wrapper<DailyRecord> wrapper=new EntityWrapper<>();
        wrapper.ge("record_time",stateTime);
        wrapper.lt("record_time",endTime);
        //用户名
        if (!StringUtils.isEmpty(name)) {
            wrapper.like("creator_name",name);
        }
        //部门集合
        if(!StringUtils.isEmpty(depts)){
            wrapper.andNew();
            String[] split = depts.split("-");
            for (int i = 0; i < split.length; i++) {
                String s = split[i];
                if(i==0){
                    wrapper.where("dept_id={0}",s);
                }else {
                    wrapper.or("dept_id={0}",s);
                }

            }
        }

        List<DailyRecord> dailyRecords = dailyRecordService.selectList(wrapper);
        if (dailyRecords.size()>0) {
            //创建excel生成对象
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
            Sheet sheet = sxssfWorkbook.createSheet("journail");
            //创建标题行
            Row row = sheet.createRow(0);
            //创建样式对象
            CellStyle cellStyle = sxssfWorkbook.createCellStyle();
            //设置背景
            cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //创建字体样式
            Font font = sxssfWorkbook.createFont();
            font.setFontHeightInPoints((short) 14);//设置字体大小
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
            //赋予字体样式
            cellStyle.setFont(font);
            //标题数组
            String[] titles={"姓名","部门","岗位","日志应填日期","实际填写日期",
                    "领导指示安排","批注建议","个人工作日志","批注建议","工作完成情况"
                    ,"批注建议","问题及思路","批注建议"};
            for (int i = 0; i < titles.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(cellStyle);
            }

            for (int i = 0; i < dailyRecords.size(); i++) {
                DailyRecord dailyRecord = dailyRecords.get(i);
                //内容
                Row row1 = sheet.createRow(i + 1);

                row1.createCell(0).setCellValue(dailyRecord.getCreatorName());
                row1.createCell(1).setCellValue(dailyRecord.getDeptName());
                if(!StringUtils.isEmpty(dailyRecord.getCreatorPostion())){
                    row1.createCell(2).setCellValue(dailyRecord.getCreatorPostion());
                }
                row1.createCell(3).setCellValue(dailyRecord.getRecordTime());
                row1.createCell(4).setCellValue(dailyRecord.getGmtCreate());
                if(!StringUtils.isEmpty(dailyRecord.getArrange())){
                    row1.createCell(5).setCellValue(dailyRecord.getArrange());
                }
                if(!StringUtils.isEmpty(dailyRecord.getCreatorPostion())){
                    row1.createCell(6).setCellValue(dailyRecord.getArrangeComment());
                }
                if(!StringUtils.isEmpty(dailyRecord.getPlan())){
                    row1.createCell(7).setCellValue(dailyRecord.getPlan());
                }
                if(!StringUtils.isEmpty(dailyRecord.getPlanComment())){
                    row1.createCell(8).setCellValue(dailyRecord.getPlanComment());
                }
                if(!StringUtils.isEmpty(dailyRecord.getFinish())){
                    row1.createCell(9).setCellValue(dailyRecord.getFinish());
                }
                if(!StringUtils.isEmpty(dailyRecord.getFinishComment())){
                    row1.createCell(10).setCellValue(dailyRecord.getFinishComment());
                }
                if(!StringUtils.isEmpty(dailyRecord.getAdvise())){
                    row1.createCell(11).setCellValue(dailyRecord.getAdvise());
                }
                if(!StringUtils.isEmpty(dailyRecord.getAdviseComment())){
                    row1.createCell(12).setCellValue(dailyRecord.getAdviseComment());
                }

            }
            //创建文件名
            String filename = "日志.xlsx";
            //创建输出对象
            OutputStream out;
            response.setContentType("application/ms-excel;charset=UTF-8");
            try {
                response.setHeader("Content-Disposition", "attachment;filename=" +
                        new String(filename.getBytes("gb2312"), "ISO8859-1"));
                out = response.getOutputStream();
                sxssfWorkbook.write(out);// 将数据写出去
                log.info("导出" + filename + "成功！");
                out.close();
                return new ResponseEntity("数据导出成功", HttpStatus.OK);
            } catch (Exception e) {
                e.printStackTrace();
                log.info("导出" + filename + "失败！");
                return new ResponseEntity("数据导出失败", HttpStatus.OK);
            }
        }else {
            return new ResponseEntity(SupConstant.NOT_INQUIRED,HttpStatus.OK);
        }
    }

第三步测试页面js(条件模拟a标签):

 

//下载日志
function download(){
    var work= $('form[id="tree_form"]').serializeArray();
    //年
    var year=$("select[name='year']").val();
    //月
    var month=$("select[name='month']").val();
    if(year==""||month==""){
        createDomHint.msgIter('请选择日期')
        return;
    }
    let  a = document.createElement('a');
    if(work.length>0){
        if(work.length==total){
                a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts=&'+ $('#header_conter_left_form').serialize(),
                document.body.appendChild(a),
                a.click(),
                document.body.removeChild(a)
        }else {
            var ids="";
            for (var i = 0; i < work.length; i++) {
                ids += work[i].value+"-";
            }
            a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts='+ids+'&'+ $('#header_conter_left_form').serialize(),
                document.body.appendChild(a),
                a.click(),
                document.body.removeChild(a)

        }
    }else {
            a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts=&'+ $('#header_conter_left_form').serialize(),
            document.body.appendChild(a),
            a.click(),
            document.body.removeChild(a)
    }

}

第四步实现效果: