将数据库数据保存成excel文件，并支持页面下载

将数据库数据保存成excel文件，并支持页面下载

controller层代码

@GetMapping("/result/export")
    public void  resultExport(InfoQuery infoQuery, HttpServletResponse response) {
        try {
            ggnewsMonitorService.monitorResultExport(infoQuery, response);
        } catch (Exception e) {
            log.error("导出时发生错误", e);
        }
    }

因为要将文件流传给response进行返回，所以不返回具体对象，指定为void。

service层代码

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public void resultExport(InfoQuery infoQuery, HttpServletResponse response) throws Exception {
        //初始化excel文件
        String fileName = infoQuery.getId() + ".xlsx";
        String excelPath = PARAM_INFO_MAP.get("export.excel.path") + DateUtil.today() + "/" + fileName;
        File file = new File(excelPath);
        if (!FileUtil.exist(file.getParent())) {
            FileUtil.mkdir(file.getParent());
        }
        //已经存在则删除
        if (FileUtil.exist(file)) {
            FileUtil.del(file);
        }
    //export.excel.temp 为提前放置的模板execl文件地址。复制一份到excelPath路径下
        FileUtil.copy(PARAM_INFO_MAP.get("export.excel.temp"), excelPath, false);
        FileInputStream fs = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        //获取到工作表，因为一个excel可能有多个工作表
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(0);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        try (OutputStream output = response.getOutputStream()) {
            row = sheet.createRow((short) (sheet.getLastRowNum()));
            row.createCell(0).setCellValue("id");
            row.createCell(1).setCellValue("(标题)"); //设置第一个（从0开始）单元格的数据
            row.createCell(2).setCellValue("(链接)"); //设置第二个（从0开始）单元格的数据
            row.createCell(3).setCellValue("(时间)");
            FileOutputStream out=new FileOutputStream(excelPath);
            out.flush();
            wb.write(out);
            out.close();
            int pageSize = 50;
            for (int pageNum = 1; pageSize == 50; pageNum++) {
                PageModel page = PageModel.initPage(50, pageNum);
                List<GgnewsListInfoVO> monitorResult = ggnewsMonitorMapper.getMonitorResultExport(infoQuery, keywords, page);
                //每50条写入excel
                for (GgnewsListInfoVO info : monitorResult) {
                    row=sheet.createRow((short)(sheet.getLastRowNum()+1));
                    row.createCell(0).setCellValue(info.getId()); //设置第一个（从0开始）单元格的数据
                    row.createCell(1).setCellValue(info.getTitle()); //设置第二个（从0开始）单元格的数据
                    row.createCell(2).setCellValue(info.getNewsUrl());
                    //超过32767字符报错
                    if (info.getContent().length() >= 32767) {
                        row.createCell(6).setCellValue(info.getContent().substring(0, 32767));
                    } else {
                        row.createCell(6).setCellValue(info.getContent());
                    }
                    //out2和wb.write()要挨着，否则报错
                    FileOutputStream out2=new FileOutputStream(excelPath);
                    out2.flush();
                    wb.write(out2);
                    out2.close();
                }
                pageSize = monitorResult.size();
            }
            log.info("导出的文件本地已生成");
            //把文件流传入response
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try{
                response.setContentType("application/x-msdownload;");
                response.setHeader("Content-disposition", "attachment; filename=" + fileName);
                response.setHeader("Content-Length", String.valueOf(file.length()));

                bis = new BufferedInputStream(new FileInputStream(file));
                bos = new BufferedOutputStream(response.getOutputStream());
                byte[] buff = new byte[2048];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
                bos.flush();
                log.info("导出文件成功！");
            }catch (Exception e) {
                // TODO: handle exception
                log.error("导出文件失败！", e);
            } finally {
                try {
                    if (bis != null) {
                        bis.close();
                    }
                    if (bos != null) {
                        bos.close();
                    }
                    //file.delete();
                } catch (Exception e) {
               log.error("导出文件关闭流出错！", e);
                }
            }

        } catch (Exception e) {
            log.error("导出数据时发生错误", e);
        }
    }