Excel导出

Excel导出数据库中数据的方法：

 

1.  从数据库中取出将要导出的数据，放在list中

 

2.  得到模板

 

3.  循环迭代list中的数据，然后生成一列（row）中的数据，迭代完一个对象后，加一行，直到整个list迭代完

    

导出的service方法如下，供大家参考！

public void exportExcel2(String payUsername) {
  //得到要导出的数据
  StringBuffer sb = new StringBuffer("from LogClaimRecord ");
  if(StringUtils.isNotBlank(payUsername)){
   sb=sb.append(" where payUserName='"+payUsername+"'");
  }
  sb.append(" order by createTime");
  String sql = sb.toString();
  List<LogClaimRecord> list = baseTransaction.find(sql);
  //下载模板
  SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
     String msg = "";
     HttpServletResponse response = (HttpServletResponse)ActionContext.getContext().get("com.opensymphony.xwork2.dispatcher.HttpServletResponse");
     response.reset();
     try
     {
       response.setContentType("application/vnd.ms-excel;charset=UTF-8");
       response.setHeader("Content-disposition", "attachment;filename=\"" + new String("Member Information.xls".getBytes("GBK"), "ISO-8859-1") + "\"");
       //会员信息
     } catch (UnsupportedEncodingException e1) {
       msg = "不支持编码异常！";
     }
     String separator = java.io.File.separator; // 分隔符
  String filePath = Platform.getInstance().getRealPath()+ separator+"templateFile"+separator;
     Workbook rwb = null;
     try {
      InputStream is = new FileInputStream(filePath+"待认领记录.xls");
      System.out.println("is==========="+is);
       rwb = Workbook.getWorkbook(is);
       System.out.println("rwb==========="+rwb);
       WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream(), rwb);
       WritableSheet ws = wwb.getSheet(0);
       int rowNum = 1;
       int colNum = 0;
       WritableFont wFont = new WritableFont(WritableFont.ARIAL, 10);
       WritableCellFormat wcf = new WritableCellFormat(wFont);
       wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
       wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
       wcf.setAlignment(Alignment.CENTRE);
       wcf.setWrap(true);
       for (int iRow = 0; iRow < list.size(); iRow++) {
         colNum = 0;
        //汇款金额
         String payAmounts = list.get(iRow).getPayAmounts();
         if(payAmounts == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else{
          ws.addCell(new Label(colNum, rowNum, payAmounts+"元", wcf));
         }
         colNum++;
         //汇款人 
         String payUserName = list.get(iRow).getPayUserName();
         if(payAmounts == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else{
          ws.addCell(new Label(colNum, rowNum, payUserName, wcf));
         }
         colNum++;
         //汇款方式
         String payWay = list.get(iRow).getPayWay();
         if(payAmounts == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else if("1".equals(payWay.trim())){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else if("2".equals(payWay.trim())){
          ws.addCell(new Label(colNum, rowNum, "现场转账", wcf));
         }else if("3".equals(payWay.trim())){
          ws.addCell(new Label(colNum, rowNum, "银行转账", wcf));
         }else if("4".equals(payWay.trim())){
          ws.addCell(new Label(colNum, rowNum, "邮局汇款", wcf));
         }
         colNum++;
         //汇款时间
         Timestamp payTime = list.get(iRow).getPayTime();
         if(payTime == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else{
          ws.addCell(new Label(colNum, rowNum, df.format(payTime), wcf));
         }
         colNum++;
         //创建时间
         Timestamp createTime = list.get(iRow).getCreateTime();
         if(payTime == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else{
          ws.addCell(new Label(colNum, rowNum, df.format(createTime), wcf));
         }
         colNum++;
         //认领原因
         String reason = list.get(iRow).getReason();
         if(reason == null){
          ws.addCell(new Label(colNum, rowNum, "", wcf));
         }else{
          ws.addCell(new Label(colNum, rowNum, reason, wcf));
         }
         colNum++;
         rowNum++;
       }
       wwb.write();
       wwb.close();
     }
     catch (Exception e)
     {
       msg = "数据没法写入excel中！！";
       e.printStackTrace();
     } finally {
      System.out.println("finally.rwb==========="+rwb);
       rwb.close();
     }
  
  
 }