导出excel（一）

       现在许多项目用到了导出excel的功能，目前主要有两种方式：1.在服务器生成excel文件，然后再下载；2.利用输出流直接输出到客户端。

       现在来实现第一种方法。

       1.  jsp页面点击导出excel按钮用ajax到后台。

       2. 在DAO中         

/**
  * 导出excel
  * @param phone 手机号
  * @param startime 开始时间
  * @param endtime 结束时间
  * @return string
  */
 @SuppressWarnings({ "rawtypes", "unchecked" })
 public String exprt(String phone,String startime,String endtime){
  
  StringBuffer sql = new StringBuffer();
  sql.append("select dcusermobile,dddeltime from smspt014 where dcsvrindex='2011' and dddeltime is not null");
  //判断手机号是否为空，不为空加入查询
  if(!phone.equals("undefined")&&!phone.equals("")){
   sql.append(" and dcusermobile="+ phone +"");
  }
  //判断起止时间是否为空，不为空加入查询
  if(!startime.equals("undefined")&&!startime.equals("")){
   sql.append(" and to_char(dddeltime,'YYYY-MM-DD')>='"+ startime +"'");
  }
  if(!endtime.equals("undefined")&&!endtime.equals("")){
   sql.append(" and to_char(dddeltime,'YYYY-MM-DD')<='"+ endtime +"'");
  }
  
  List<DBClass> userlist = con.creatMent(sql.toString()).findList();
  
  String creatExcel = null;
  int num = userlist.size();
  List tittleList = new ArrayList();
  List list = new ArrayList();
  
  list.add("dcusermobile");
  list.add("dddeltime");
  
  int num2 = list.size();
  // 用于存储用户选择条件的list
  List selList = new ArrayList();
  if(userlist.size()>0){
  // 总的循环次数，数据库里有多少条记录循环多少次,由于每两条数据一样，所以隔二取一
  for (int i = 0; i < num; i++) {
   // 循环CC中的KEY
   for (int j = 0; j < num2; j++) {
                 if ("dcusermobile".equals(list.get(j).toString())) {
                  selList.add(userlist.get(i).get("dcusermobile").toString());
         tittleList.add("手机号");
                 }

                 if("dddeltime".equals(list.get(j).toString())){                
                  selList.add(userlist.get(i).get("dddeltime").toString());
                  tittleList.add("退订时间");
                 }
   }

  }
  creatExcel = creatExcel(selList, num2, tittleList, num);
  }
     return creatExcel;
  
 }
 
 /**
  * 导出excel工具类
  * @param data2                     列的内容
  * @param num2                        列的个数
  * @param tittleList  列的标题
  * @param num         内容的行数   
  * @return
  */
 @SuppressWarnings("rawtypes")
 public String creatExcel(List data2, int num2, List tittleList, int num) {

  String url = DBHelper.get(DBEnum.VIEW);
  
  String fileurl = null;
  WritableWorkbook workbook = null;
  try {
   
   fileurl = "业务退订.xls";
   workbook = Workbook.createWorkbook(new File(url + "/excel/"
     + fileurl));
   // 创建一个工作表
   WritableSheet sheet = workbook.createSheet("first sheet", 0);
   //if (num2 == num2) {
    // 创建表头
    for (int i = 0; i < tittleList.size() / num; i++) {

     Label id = new Label(i, 0, (String) tittleList.get(i)
       .toString());
     sheet.addCell(id);
    }
    int b = 1;
    int c = 0;

    for (int i = 0; i < data2.size(); i++) {
     Label id = null;
     if (data2.get(i) == null) {
      id = new Label(c, b, "0");
     } else {
      id = new Label(c, b, (String) data2.get(i).toString());
     }

     sheet.addCell(id);
     c++;
     if (c % num2 == 0) {
      c = 0;
      b++;
     }
    }

   //}
   // 将文件写入
   workbook.write();
   // 关闭工作薄
   workbook.close();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (RowsExceededException e) {
   e.printStackTrace();
  } catch (WriteException e) {
   e.printStackTrace();
  }
  return fileurl;
 }

      3.在action中根据返回的值确定json的值，返回0提示失败，返回1则在ajax中 

             json="业务退订.xls";
             window.open('show.jsp?fileurl=' + json, '下载', 'height=100, width=400, top=420px, left=580px, toolbar=no, menubar=no, scrollbars=no, resizable=no,location=no, status=no');

 

       4.在show.jsp中

          <%@page language="java" contentType="application/x-msdownload"  import="java.io.*,java.net.*"   pageEncoding="gb2312"%> 

          <%
                response.reset();//可以加也可以不加 
                response.setContentType("application/x-download");

               

                String filedisplay = request.getParameter("fileurl");  //如果文件名是中文的话，在这里要先注意转码，否则文件名是乱码

                String filedownload = ”这是文件在服务器上的地址+文件名“;

                       

                filedisplay = URLEncoder.encode(filedisplay,"UTF-8"); 
                response.addHeader("Content-Disposition","attachment;filename=" + filedisplay); 

                OutputStream outp = null; 
                FileInputStream in = null; 
                try 
                    { 
                        outp = response.getOutputStream(); 
                        in = new FileInputStream(filedownload); 
 
                        byte[] b = new byte[1024];  
                        int i = 0; 
 
                        while((i = in.read(b)) > 0) 
                        { 
                            outp.write(b, 0, i);  
                        }  
    
                outp.flush(); 
                out.clear();
                out = pageContext.pushBody(); 
            }catch(Exception e){   
                e.printStackTrace(); 
           }finally{ 
              if(in != null) 
              { 
                 in.close(); 
                  in = null; 
              } 

           } 
 %>

好了现在就可以下载excel文件了