个人项目笔记

最新推荐文章于 2024-09-03 01:10:43 发布

原创最新推荐文章于 2024-09-03 01:10:43 发布 · 380 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种创建Excel报告的Java方法，包括初始化页面列表翻页、排序、过滤条件参数，查询数据并生成带有特定列标题的Excel文件。

//创建exal 没有模板的

public void reportDownLoad(HttpServletRequest request,

           HttpServletResponse response) throws Exception {
       try {
       request.setCharacterEncoding("UTF-8");
       String fileName = "卡认证记录信息";
       String fileheader = new String("卡认证记录信息-");
       initializePagingSortingFiltering(request);// 初始化页面列表翻页、排序、过滤条件的参数
       List<Map> list =pFCardVerifyRecordService.queryForAll(filterMap);

//       List<Map> list = pageinfo.getPageResults();
       String[] headers = {"日期","时间","操作类型","用户号","姓名", "预留手机号","卡类型", "银行名称", "卡号","交易渠道","证件类型", "证件号",
               "cp认证结果", "cp认证返回信息", "ulink认证结果", "ulink认证信息"};
       try {
       HSSFWorkbook workbook = new HSSFWorkbook();
               //确定报表的页数
               int sheetNum = 0;
                if(list.size()%5000>0){
                   sheetNum = list.size()/5000+1;
                }else{
                   sheetNum = list.size()/5000;
                }
                HSSFSheet sheet = null;
         if(list.size()>0){
            for(int k=0;k<sheetNum;k++){
               if(list.size()%5000>0){
                   if(k==(list.size()/5000)){
                      sheet = workbook.createSheet(fileheader+"("+(k*5000+1)+"~"+(list.size())+")");
                   }else{
                      sheet = workbook.createSheet(fileheader+"("+(k*5000+1)+"~"+5000*(k+1)+")");
                   }
               }else{
                      sheet = workbook.createSheet(fileheader+"("+(k*5000+1)+"~"+5000*(k+1)+")");
               }
               sheet.setDefaultColumnWidth((short)18);
               HSSFCellStyle style = workbook.createCellStyle();
               style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
               sheet.autoSizeColumn(7);
               HSSFRow row0 = sheet.createRow(0);
               HSSFRow row = sheet.createRow(1);
               CellRangeAddress range = new CellRangeAddress(0, 0, 0, 12);
                sheet.addMergedRegion(range);
                HSSFCell cell0 = row0.createCell(0);
                cell0.setCellValue(fileName);
                cell0.setCellStyle(style);
               for (short i = 0; i < headers.length; i++) {
                   HSSFCell cell = row.createCell(i);
                   cell.setCellStyle(style);
                   HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                   cell.setCellValue(text);
               }
               for (int i = 2, j = 0; j <(list.size()%5000>0?(k==(list.size()/5000)? list.size()-k*5000:5000):5000); i++, j++) {
                   row = sheet.createRow(i);
                   HSSFCell cell = row.createCell(0);
                   String cvrDate=list.get(k*5000+i-2).get("VERIFYDATE").toString();
                   if(cvrDate.length()>6){
                           StringBuilder sb=new StringBuilder(cvrDate);
                           sb.insert(4, "-");
                           sb.insert(7, "-");
                           cvrDate=sb.toString();
                   }
                   cell.setCellValue(cvrDate);

                   cell = row.createCell(1);
                   String cvrTime=list.get(k*5000+i-2).get("VERIFYTIME").toString();
                   if(cvrTime.length()>5){
                           StringBuilder sbTime=new StringBuilder(cvrTime);
                           sbTime.insert(2, ":");
                           sbTime.insert(5, ":");
                           cvrTime=sbTime.toString();
                   }
                   cell.setCellValue(cvrTime);

                   cell = row.createCell(2);
                   cell.setCellStyle(style);
                   if((list.get(k*5000+i-2).get("UserNo"))!=null)
                   {
                       cell.setCellValue(list.get(k*5000+i-2).get("UserNo").toString());
                   }
               }
           }
              }
               if (workbook != null) {
                   response.setContentType("application/ms-excel");
                   fileheader = fileheader
                           + DateUtils.format(new Date(), "yyyy-MM-dd") + ".xls";
                   fileheader = WebUtils.encodeReqStr(request, fileheader);
                   response.setHeader("Content-Disposition",
                           "attachment;filename=" + fileheader);
                   workbook.write(response.getOutputStream());
               }
           } catch (BusinessException e) {
               logger.error("查询失败", e);
               responseMessage(response, "查询失败:" + e.getMessage(), false);
           } catch (IOException e) {
           }
       } catch (UnsupportedEncodingException e) {
           logger.error("字符编码异常", e);
       }
   }