通用的导出模板样式

1.controller

@ApiOperation(value = "导出")
    @PostMapping("/getExcel")
    public void getExcel(@RequestBody Conditions query, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        File destFile=null;
        try {
            String fileName = informationStatisticalService.getExcel(query); //query 导出条件
             destFile = new File(fileName.replace("\\","/"));
            //将文件输入
            InputStream inputStream = new FileInputStream(destFile);
            // 设置response参数，可以打开下载页面
            response.reset();
            //设置响应文本格式
            response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//
//        response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName.substring(fileName.lastIndexOf('/') + 1)).getBytes("gb2312"), "iso-8859-1"));
            //将文件输出到页面
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(inputStream);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[1024];
            int bytesRead;
            // 根据读取并写入
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            e.printStackTrace();


        } finally {
            try {
                if (bis != null) {
                    bis.close();
                }
                if (bos != null) {
                    bos.close();
                }
            } catch (Exception e) {
            }
        }
        destFile.delete();  // excel输出到页面后，删除项目中保存的excel
    }
}

 

2.service.impl

    @Value("${spring.indicator.filePath}")
    private String filePath;  

@Override
    public String getExcel(Conditions query) throws Exception {
        query.setPageIndex(1);
        query.setPageSize(100000);
        IPagination<Map<String, Object>> result = querydata(query);
        List<Map<String, Object>> datas = result.getRecords();
        List<String> head = new ArrayList<>();
        for (String str : query.getCondMap().keySet()) {
            head.add(query.getCondMap().get(str));
        }
        if (filePath.charAt(filePath.length() - 1) != '\\' && filePath.charAt(filePath.length() - 1) != '/') {
            filePath = filePath + "/";
        }
        filePath = filePath.replace("\\", "/");
        //文件不存在，先创建
        File dir = new File(filePath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        //输出文件路径
        StringBuilder outPath = new StringBuilder(filePath).append("统计分析_").append(System.currentTimeMillis()).append(".xls");
        File file = new File(outPath.toString());
        if (file.exists()) {
            return outPath.toString();
        }
        OutputStream os = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFSheet sheet = workbook.createSheet("sheet");
        //style.setAlignment(HorizontalAlignment.CENTER); //水平居中
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setWrapText(false);//自动换行
        HSSFRow row0 = sheet.createRow(0);
        int cellIndex = 1;
        for (String tf : head) {
            //设置宽度
            sheet.setColumnWidth(cellIndex, 1000 * 5);
            HSSFCell cell_00 = row0.createCell(cellIndex);//获取表头对应的cell单元格
            cell_00.setCellStyle(style);
            cell_00.setCellValue(tf);
            cellIndex++;
        }
        for (int i = 0; i < datas.size(); i++) {
            cellIndex = 0;
            row0 = sheet.createRow(i + 1);
            for (String str : query.getCondMap().keySet()) {
                sheet.setColumnWidth(cellIndex, 1000 * 5);
                HSSFCell cell_00 = row0.createCell(cellIndex);
                if (cellIndex == 0) {
                    cell_00.setCellValue(String.valueOf(datas.get(i).get("num")));
                     cellIndex++;
                } else {
                    if (datas.get(i).get(str) != null) {
                        cell_00.setCellValue(String.valueOf(datas.get(i).get(str)));
                    } else {
                        cell_00.setCellValue("");
                    }
                    cellIndex++;
                }
            }
        }
        os = new FileOutputStream(outPath.toString());
        workbook.write(os);
        os.close();
        workbook.close();
        return outPath.toString();
    }

3.application.yml

spring:
  indicator:
    filePath: E:\test

4.pom.xml

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

 

整个的导出过程：

在 E:\test 下创建一个 .xls 空文件，把查询后的列表 以数据流的形式 读取到.xls 空文件里
后在controller方法下 加载.xls文件数据流，将数据流读取到 response.getOutputStream()里返回到前端 ，
再删除当前E:\test 目录下 .xls文件