excle导出poi

Controller:

 1     @RequestMapping(value = "exportExcleList")
 2     public void exportBackPaymentList(OutUserCollecteStatisticsQuery query, HttpServletResponse response) {
 3         try (BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream())){
 4             XSSFWorkbook wb = financeService.exportBackPaymentList(query);
 5             response.reset();
 6             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 7             response.setCharacterEncoding("UTF-8");
 8             response.setHeader("Content-Disposition", "attachment; filename=" + new String("文件名".getBytes(),"iso-8859-1") + ".xlsx" );
 9             wb.write(out);
10         } catch (Exception e) {
11             logger.error("export data info errer message: " + e.getMessage() + "e:" + e + "parm :" + query);
12             throw new ServiceException(e);
13         }
14     }

 

Service:

 1    /**
 2      * companyId (必备)
 3      * @param query
 4      */
 5     @Override
 6     public XSSFWorkbook exportBackPaymentList(OutUserCollecteStatisticsQuery query) {
 7         WebResponse webResponse = outUserCollecteStatisticsService.backPaymentInquiry(query);
 8         List<OutUserCollecteStatisticsDto> list = (List<OutUserCollecteStatisticsDto>)webResponse.get("rows");
 9         // 创建一个webbook，对应一个Excel文件
10         XSSFWorkbook wb = new XSSFWorkbook();
11         // 在webbook中添加一个sheet,对应Excel文件中的sheet
12         XSSFSheet sheet = wb.createSheet("文件名");
13         sheet.setDefaultColumnWidth(4);
14         // ，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
15         XSSFRow row = sheet.createRow(0);
16         XSSFCellStyle style = wb.createCellStyle();
17         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
18         String[] title2 = {"xx员", "xx案件数", "xx金额", "xx金额", "xxx"};
19         row = sheet.createRow(0);
20         XSSFCell cell = row.createCell((short) 0);
21         for (int i = 0; i < title2.length; i++) {
22             cell.setCellValue(title2[i]);
23             cell.setCellStyle(style);
24             cell = row.createCell((short) i + 1);
25         }
26         for (int i = 0; i < list.size(); i++) {
27             OutUserCollecteStatisticsDto dto = list.get(i);
28             row = sheet.createRow(i + 1);
29             row.createCell((short) 0).setCellValue(dto.getUserName());
30             row.createCell((short) 1).setCellValue(dto.getCallbackCase());
31             row.createCell((short) 2).setCellValue(dto.getCallbackAmount());
32             row.createCell((short) 3).setCellValue(dto.getReductionAmount());
33             if(null!=dto.getRevokeCase()){
34                 row.createCell((short) 4).setCellValue(dto.getRevokeCase());
35             }else row.createCell((short) 4).setCellValue("/");
36 
37         }
38         return wb;
39     }

 

posted @ 2017-10-17 19:19  硬币  阅读(138)  评论(0)    收藏  举报