通用的导出模板样式

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;  @Overridepublic 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文件