通过ssh-ajax—poi技术实现excel文件的下载功能

--整个流程如下：具体内容根据需要导出的内容确定

页面部分代码如下：

function exportexcel(){   
   $.ajax({
    type:"post",
    url:"/admin/activity/extendExportTel.action",
    data:{
      roadType:roadType,
    receiveType:receiveType,
    accOpName:accOpName,
    validBuName:validBuName,
    endDate:endDate,
    beginDate:beginDate
    },
    dataType:"json",
    success:function(data){
     //alert("8888888888888888");
           if(data.excelFlag=="0")  
     {
     waiting = commentWaitTips("读取中...");
     var o =setInterval(function(){
      $.ajax({
      type : "post",
      url : "/admin/activity/extendExportFlag.action",
      data:{
      },
      dataType : "text",
      success : function(data) {
       if(data=="1"){
        waiting.close();
        clearInterval(o);
        var iframe = document.createElement("iframe");
        iframe.src = "/admin/activity/extendExportDownload.action";
        iframe.style.display = "none";
        document.body.appendChild(iframe);
       }
      }
      });
     }, 2000);
     }
    }
    });
}

action的实现内容如下：

//导出手机号文件内容
 @org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportTel", results = {
 })
 public String extendExportTel() {
   final HttpSession session = request.getSession(true);
  JSONObject excelFlag=new JSONObject();
  String flag ="";
  Thread thread = new Thread(new Runnable() {
   @Override
   public void run() {
    List<Object[]> orderList=new ArrayList<Object[]>();    
          StringBuffer sql=new StringBuffer();
          HSSFWorkbook wb=null;
          ExportOrderExcel exportExcel=new ExportOrderExcel();
     try{            
     orderList=provideRecordService.findBySql(sql.toString());//service层实现查询内容
     wb=exportExcel.exportExtendExcel(orderList);//通过poi写入excel格式及内容
     session.setAttribute("excelFlag", 1);
     session.setAttribute("orderResult", wb);     
     }catch(Exception e)
     {
      session.setAttribute("excelFlag", "2");
      session.setAttribute("orderResult", wb);
      e.printStackTrace();
     }    
    
  }    
  }
  );
  thread.start(); 
  excelFlag.put("excelFlag", "0");
  try {
   response.getWriter().print(excelFlag);
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return null;
 }

 @org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportFlag", results = {
 })
 public String extendExportFlag() throws Exception {
  String flag = request.getSession().getAttribute("excelFlag").toString();
  if("1".equals(flag)){
   request.getSession().removeAttribute("excelFlag");
   response.getWriter().print("1");
  }
  else{
   response.getWriter().print("0");
  }
  return null;
 }
 
 @org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportDownload", results = {
 })
 public String downloadExcel() throws Exception {
  try{
   response.reset();
      response.setContentType("application/octet-stream");
      response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode("充值账单.xls", "UTF-8") + "\"");
      OutputStream out = new BufferedOutputStream(response.getOutputStream());
      HSSFWorkbook wb=null;
      wb=(HSSFWorkbook)request.getSession().getAttribute("orderResult");
      wb.write(out);
      request.getSession().removeAttribute("orderResult");
      out.close();
  }
  catch(Exception e){
   e.printStackTrace();
  }
  return null;
 }

--poi实现内容的写出

public HSSFWorkbook exportExtendExcel(List<Object[]> orderList)
 {
  // TODO Auto-generated method stub
  if (orderList.size()==0)
  {
      Object[] obj={"2012-11-13 17:02:30","222","11111111111","ok"};
      orderList.add(obj);
  }
      HSSFWorkbook wb = null;
   // 创建Excel的工作书册 Workbook,对应到一个excel文档
  try{
   wb = new HSSFWorkbook();    
     // 创建Excel的工作sheet,对应到一个excel文档的tab
     HSSFSheet sheet = wb.createSheet("sheet1");

     // 设置excel每列宽度
     sheet.setColumnWidth(0, 6000);
     sheet.setColumnWidth(1, 6000);
     sheet.setColumnWidth(2, 6000);
//     // 创建字体样式
     HSSFFont font = wb.createFont();
     font.setFontName("Verdana");
     font.setBoldweight((short) 100);
     font.setFontHeight((short) 300);
     font.setColor(HSSFColor.BLACK.index);

   // 创建单元格样式1
     HSSFCellStyle style = wb.createCellStyle();
     style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     style.setFillForegroundColor(HSSFColor.ORANGE.index);
     style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
     style.setFont(font);// 设置字体
   // 创建单元格样式2
     HSSFCellStyle style1 = wb.createCellStyle();
     style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     style1.setFillForegroundColor(HSSFColor.TURQUOISE.index);
     style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
     style1.setFont(font);
   // 创建单元格样式3
     HSSFCellStyle style2 = wb.createCellStyle();
     style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     style2.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
     style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
     style2.setFont(font);
   // 创建单元格样式4
     HSSFCellStyle style3 = wb.createCellStyle();
     style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  // 创建单元格样式5
     HSSFCellStyle style4 = wb.createCellStyle();
     style4.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
     style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     HSSFRow row ;
     HSSFCell cell;    
  //头标题1
     String HeadOne="222";  
     row=sheet.createRow(0);
     cell=row.createCell(0);
     cell.setCellStyle(style);
     cell.setCellValue(HeadOne);    
     sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
   //标题2 
     String HeadTwo="111";
     row=sheet.createRow(1);
     cell=row.createCell(0);
     cell.setCellStyle(style2);
     cell.setCellValue(HeadTwo);    
     sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
   //标题3
     String HeadThree="xxxxx";
     row=sheet.createRow(2);
     cell=row.createCell(0);
     cell.setCellStyle(style1);
     cell.setCellValue(HeadThree);
     sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 2));
   //标题4
     String HeadFour_01="111";
     String HeadFour_02="222";
     String HeadFour_03="33";
     row=sheet.createRow(3);
     cell=row.createCell(0);
     cell.setCellStyle(style2);
     cell.setCellValue(HeadFour_01);
     cell=row.createCell(1);
     cell.setCellStyle(style2);
     cell.setCellValue(HeadFour_02);
     cell=row.createCell(2);
     cell.setCellStyle(style2);
     cell.setCellValue(HeadFour_03);
   //标题5
     String HeadFive_01="时间";
     String HeadFive_02="卡号/手机号";
     String HeadFive_03="数量";
     row=sheet.createRow(4);
     cell=row.createCell(0);
     cell.setCellStyle(style1);
     cell.setCellValue(HeadFive_01);
     cell=row.createCell(1);
     cell.setCellStyle(style1);
     cell.setCellValue(HeadFive_02);
     cell=row.createCell(2);
     cell.setCellStyle(style1);
     cell.setCellValue(HeadFive_03);
     //对账单内容
  int rowFNum=5;
   System.out.println(orderList.get(0).length+"==="+orderList.size());
  for(int i=0;i<orderList.size();i++)
  {
      row = sheet.createRow(rowFNum+i);
      row.setHeight((short) 500);// 设定行的高度
      int j=0;
      System.out.println(orderList.get(i)[1].toString()+i+"=="+j);
//          // 创建一个Excel的单元格
        cell = row.createCell(0);

       cell.setCellStyle(style4);
       cell.setCellValue(orderList.get(i)[0].toString());
       cell = row.createCell(1);
       cell.setCellStyle(style3);
       cell.setCellValue(orderList.get(i)[1].toString());
       cell = row.createCell(2);
       cell.setCellStyle(style3);
       cell.setCellValue(orderList.get(i)[2].toString());

  }
//     FileOutputStream os;  
//   os = new FileOutputStream("e://workbookTest.xls");
//    wb.write(os);
//    os.close();
    return wb;
  } catch (Exception e) {
   e.printStackTrace();
   return wb;
  }
  
 }