POI 批量导出文档

html页面代码
<div class="columns pull-left">
   <button class="btn btn-success" style="margin-left: 10px" shiro:hasPermission="app:activeInfo:download" onclick="downLoad();"> 
      <i class="fa fa-cloud-download" aria-hidden="true">批量导出</i>
   </button>
</div>

function downLoad() {
   $.ajax({
      url: "/app/activeInfo/downLoad",
      type: "post",
      data: {"activeno":$('#activeno').val(),"serverno":$('#serverno').val(), "checkState":$('#checkState').val()},
      success: function (data) {
         if (data.code == 0){
                layer.msg("正在生成EXCEL");
                var filename = data.filename;
                window.open("/app/activeInfo/downloadF?fileName="+filename)
         }else {
                layer.msg(data.msg);
         }
        }
   })
}

java代码

@RequestMapping("/downLoad")
@ResponseBody
@RequiresPermissions("app:activeInfo:download")
public R downLoad(@RequestParam Map<String, Object> params){
   //查询列表数据
   params.put("offset",0);
   params.put("limit",Integer.MAX_VALUE);
   Query query = new Query(params);
   List<ActiveInfoDO> list = activeInfoService.list(query);
   for (ActiveInfoDO activeInfoDO : list) {
      Long id = activeInfoDO.getId();
      long cardedAmount = activeInfoDO.getUserdamountbs();
      List<BusinessCardDO> totalfromDb = businessCardService.getTotalfromDb(id);
      for (BusinessCardDO businessCardDO : totalfromDb) {
         if (businessCardDO.getIsfixed()==1){
            cardedAmount += businessCardDO.getNum()*businessCardDO.getPrice();
         }else if (businessCardDO.getIsfixed()==2){
            cardedAmount += businessCardDO.getUnfixedprice();
         }
      }
      DecimalFormat df = new DecimalFormat("#,###.00");
      if (cardedAmount!=0){
         String cardedAmountTmp = df.format(cardedAmount / 100.0);
         activeInfoDO.setCardedAmountTmp(cardedAmountTmp);
      }else{
         activeInfoDO.setCardedAmountTmp("0.00");
      }
   }
   List<ActiveInfoExport> resultList = list.stream().map(e -> new ActiveInfoExport(e)).collect(Collectors.toList());
   String timeString = DateUtils.getTimeString();
   if (resultList.size() != 0){
      String filename = "demandList" +timeString;
      String[] titles = new String[] {"和包营销资源编号","需求单编号","需求单名称","和包券别编号","和包券别名称","需求单总金额(元)","已制卡金额(元)","已发放金额(元)","需求单开始时间","需求单结束时间","创建时间","渠道编号","状态"};
      String[] fieldNames = new String[] {"activeno","serverno","name","productid","productname","amount","cardedAmount","amountReleased","begintime","endtime","cdate","channelno","checkstatus"};
      XssfExcelHelper helper = XssfExcelHelper.getInstance(temp_path + filename +".xlsx");
      try {
         helper.writeExcel(ActiveInfoExport.class, resultList, fieldNames, titles);
         R r = new R();
         r.put("code",0);
         r.put("msg","批量导出成功");
         r.put("filename",filename+".xlsx");
         return r;
      } catch (Exception e) {
         e.printStackTrace();
         R r = new R();
         r.put("code",-1);
         r.put("msg","数据错误，请稍后再试");
         return r;
      }
   }
   R r = new R();
   r.put("code",-2);
   r.put("msg","查询数据为空");
   return r;
}

/**
 * 文件下载
 *
 * @param request
 * @param response
 * @return
 */
@RequestMapping("/downloadF")
public String downloadF(HttpServletRequest request, HttpServletResponse response) {
   response.setCharacterEncoding("UTF-8");
   // 设置ContentType字段值
   response.setContentType("text/html;charset=utf-8");
   // 获取所要下载的文件名称
   String fileName = request.getParameter("fileName");
   if (StringUtils.isEmpty(fileName)) {
      return null;
   }
   String folder = temp_path +"\\"+ fileName;
   File f = new File(folder);
   // 通知浏览器以下载的方式打开
   response.addHeader("Content-type", "appllication/octet-stream");
   response.addHeader("Content-Disposition", "attachment;filename=" + f.getName());
   // 通知文件流读取文件
   InputStream in = null;
   OutputStream out = null;
   try {
      in = new FileInputStream(f);
      // 获取response对象的输出流
      out = response.getOutputStream();
      byte[] buffer = new byte[1024];
      int len;
      // 循环取出流中的数据
      while ((len = in.read(buffer)) != -1)
      {
         out.write(buffer, 0, len);
      }

   } catch (Exception e) {
      e.printStackTrace();
   } finally {
      if (in != null) {
         try {
            in.close();
         } catch (IOException e) {
            e.printStackTrace();
         }
      }
      if (out != null) {
         try {
            out.close();
         } catch (IOException e) {
            e.printStackTrace();
         }
      }
   }
   return null;
}

 

public <T> void writeExcel(Class<T> clazz, List<T> dataModels, String[] fieldNames, String[] titles) throws Exception {
    XSSFWorkbook workbook = null;
    // 检测文件是否存在，如果存在则修改文件，否则创建文件
    if (file.exists()) {
        FileInputStream fis = new FileInputStream(file);
        workbook = new XSSFWorkbook(fis);
    } else {
        workbook = new XSSFWorkbook();
    }
    // 根据当前工作表数量创建相应编号的工作表
    String sheetName = DateUtils.format(new Date(), "yyyyMMddHHmmssSS");
    XSSFSheet sheet = workbook.createSheet(sheetName);
    XSSFRow headRow = sheet.createRow(0);
    // 添加表格标题
    for (int i = 0; i < titles.length; i++) {
        XSSFCell cell = headRow.createCell(i);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(titles[i]);
        // 设置字体加粗
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);
        // 设置自动换行
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);
        // 设置单元格宽度
        sheet.setColumnWidth(i, titles[i].length() * 1000);
    }
    // 添加表格内容
    for (int i = 0; i < dataModels.size(); i++) {
        T target = dataModels.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        // 遍历属性列表
        for (int j = 0; j < fieldNames.length; j++) {
            // 通过反射获取属性的值域
            String fieldName = fieldNames[j];
            if (fieldName == null || UID.equals(fieldName)) {
                continue; // 过滤serialVersionUID属性
            }
            Object result = ReflectUtil.invokeGetter(target, fieldName);
            XSSFCell cell = row.createCell(j);
            cell.setCellValue(StringUtil.toString(result));
            // 如果是日期类型则进行格式化处理
            if (isDateType(clazz, fieldName)) {
                cell.setCellValue(DateUtils.format((Date) result));
            }
        }
    }
    // 将数据写到磁盘上
    FileOutputStream fos = new FileOutputStream(file);
    try {
        workbook.write(new FileOutputStream(file));
    } finally {
        if (fos != null) {
            fos.close(); // 不管是否有异常发生都关闭文件输出流
        }
    }
}

 

public class ActiveInfoExport implements Serializable {

    @Autowired
    private BusinessCardService businessCardService;

   
    private String activeno;
  
    private String serverno;
 
    private String name;

    private String productid;

    private String productname;
   
    private String amount;

    private String cardedAmount;
   
    private String amountReleased;
 
    private String begintime;

    private String endtime;

    private Date cdate;

    private String channelno;

    private String checkstatus;

    public String getActiveno() {
        return activeno;
    }

    public void setActiveno(String activeno) {
        this.activeno = activeno;
    }

    public String getServerno() {
        return serverno;
    }

    public void setServerno(String serverno) {
        this.serverno = serverno;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getProductid() {
        return productid;
    }

    public void setProductid(String productid) {
        this.productid = productid;
    }

    public String getProductname() {
        return productname;
    }

    public void setProductname(String productname) {
        this.productname = productname;
    }

    public String getAmount() {
        return amount;
    }

    public void setAmount(String amount) {
        this.amount = amount;
    }

    public String getCardedAmount() {
        return cardedAmount;
    }

    public void setCardedAmount(String cardedAmount) {
        this.cardedAmount = cardedAmount;
    }

    public String getAmountReleased() {
        return amountReleased;
    }

    public void setAmountReleased(String amountReleased) {
        this.amountReleased = amountReleased;
    }

    public String getBegintime() {
        return begintime;
    }

    public void setBegintime(String begintime) {
        this.begintime = begintime;
    }

    public String getEndtime() {
        return endtime;
    }

    public void setEndtime(String endtime) {
        this.endtime = endtime;
    }

    public Date getCdate() {
        return cdate;
    }

    public void setCdate(Date cdate) {
        this.cdate = cdate;
    }

    public String getChannelno() {
        return channelno;
    }

    public void setChannelno(String channelno) {
        this.channelno = channelno;
    }

    public String getCheckstatus() {
        return checkstatus;
    }

    public void setCheckstatus(String checkstatus) {
        this.checkstatus = checkstatus;
    }

    public ActiveInfoExport(ActiveInfoDO activeInfoDO){
        this.activeno = activeInfoDO.getActiveno();
        this.serverno = activeInfoDO.getServerno();
        this.name = activeInfoDO.getName();
        this.productid = activeInfoDO.getProductid();
        this.productname = activeInfoDO.getProductname();
        this.cardedAmount = activeInfoDO.getCardedAmountTmp();
        this.cdate = activeInfoDO.getCdate();
        this.channelno = activeInfoDO.getChannelno();
        DecimalFormat df = new DecimalFormat("#,###.00");
        //需求单金额
        Long amount = activeInfoDO.getAmount();
        if (amount !=null){
            String amountTmp = df.format(amount / 100.0);
            this.amount = amountTmp;
        }else{
            this.amount = "0.00";
        }
        //计算已发放金额
        long amountReleased = activeInfoDO.getUserdamount() + activeInfoDO.getUserdamountbs();
        if (amountReleased !=0){
            String amountReleasedTmp = df.format(amountReleased / 100.0);
            this.amountReleased = amountReleasedTmp;
        }else{
            this.amountReleased = "0.00";
        }
        //需求单开始时间和结束时间
        String begintime = activeInfoDO.getBegintime();
        String endtime = activeInfoDO.getEndtime();
        StringBuilder begintimeBuilder = new StringBuilder(begintime);
        begintimeBuilder.insert(6,"-");
        begintimeBuilder.insert(4,"-");
        StringBuilder endtimeBuilder = new StringBuilder(endtime);
        endtimeBuilder.insert(6,"-");
        endtimeBuilder.insert(4,"-");
        this.begintime = begintimeBuilder.toString();
        this.endtime = endtimeBuilder.toString();
        //状态
        Integer checkstatustwo = activeInfoDO.getCheckstatustwo();
        Integer checkstatusone = activeInfoDO.getCheckstatusone();
        if (checkstatusone == 0 && checkstatustwo == 0){
            this.checkstatus = "待审核";
        }
        if(checkstatusone == 2){
            this.checkstatus = "初审未通过";
        }
        if(checkstatusone == 1 && checkstatustwo == 0){
            this.checkstatus = "初审通过";
        }
        if (checkstatustwo == 2) {
            this.checkstatus = "复审未通过";
        }
        if (checkstatustwo == 1) {
            this.checkstatus = "生效";
        }
        SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
        Date date = new Date();
        String now = sd.format(date);
        try {
            int i = sd.parse(endtime).compareTo(sd.parse(now));
            if (sd.parse(now).compareTo(sd.parse(endtime)) == 1){
                this.checkstatus = "过期";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }
}