工单统计生成Excel文件

来源：互联网发布：网络层协议包括编辑：程序博客网时间：2024/06/05 00:31

在action中添加方法

/**
* 个人工单统计生成Excel文件
*
* @param mapping
* @param form
* @param request
* @param response
* @return
* @throws Exception
* //20091113改为只要全网，地网的代码删了
*/

public void report_sms(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {

  try {
   String draftertime_s = request.getParameter("draftertime_s4");
   String draftertime_e = request.getParameter("draftertime_e4");
   Flow fl = new Flow();
   String uploadPath = Config.getProperty("upload_Path_flow");
   String targetfile = uploadPath + fl.getRandom() + ".xls";
   String[][] result = fl.report_sms(draftertime_s,
     draftertime_e);
   File file = new File(targetfile);
   file.createNewFile();
   FileInputStream fis = new FileInputStream(targetfile);
   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(file);
   jxl.write.WritableSheet ws = wwb.createSheet("短信用户投诉处理流程", 0);
   // 1.添加Label对象
   jxl.write.Label labelA0 = new jxl.write.Label(0, 0, "工单名称");
   jxl.write.Label labelA1 = new jxl.write.Label(1, 0, "工单类型");
   jxl.write.Label labelA2 = new jxl.write.Label(2, 0, "工单号");
   jxl.write.Label labelA3 = new jxl.write.Label(3, 0, "退费金额");
   ws.addCell(labelA0);
   ws.addCell(labelA1);
   ws.addCell(labelA2);
   ws.addCell(labelA3);

   if (result != null) {
    for (int i = 0; i < result.length; i++) {
     labelA0 = new jxl.write.Label(0, i + 1, result[i][0]); // 工单号
     labelA1 = new jxl.write.Label(1, i + 1, "短信用户投诉处理流程"); // SP名称
     labelA2 = new jxl.write.Label(2, i + 1, result[i][1]); // 业务名称
     labelA3 = new jxl.write.Label(3, i + 1, result[i][2]); // 接入号
     ws.addCell(labelA0);
     ws.addCell(labelA1);
     ws.addCell(labelA2);
     ws.addCell(labelA3);
    }
   }
   result = null;
   // 写入Exel工作表
   wwb.write();

// 关闭Excel工作薄对象
wwb.close();

   byte bytes[] = new byte[(int) (file.length())];
   int n = fis.read(bytes);
   fis.close();
   file.delete();
   file = null;
   String fileName = "sms.xls";
   response.setContentType("application/ms-excel");
   response.setHeader("Content-disposition", "attachment; filename="
     + fileName);
   ServletOutputStream out = response.getOutputStream();
   out.write(bytes);
   out.close();
   fl = null;
  } catch (Exception ex) {
   ex.printStackTrace();
  }
}

查询数据的方法

/**
* 个人工单统计生成Excel文件
*
* @param starttime
* @param endtime
* @param useralias
* @return
*/
public String[][] report_sms(String starttime, String endtime) {
  try {
   VOServiceTest vot = new VOServiceTest();
   StringBuffer sqlstr = new StringBuffer("");
   sqlstr
     .append("select distinct fi from FlowInfo fi where (fi.ispass is null or fi.ispass='0') and (fi.status<>'0') and fi.typeId=1211");
   if (starttime != null && !starttime.equals("") && endtime != null
     && !endtime.equals("")) {
    sqlstr
      .append(" and fi.draftTime > TO_DATE( '"
        + starttime
        + ":00"
        + "','YYYY-MM-DD HH24:MI:SS') and fi.draftTime < TO_DATE( '"
        + endtime + ":00"
        + "','YYYY-MM-DD HH24:MI:SS')");
   }
   List templist = vot.query(sqlstr.toString());
   allnum = vot.getAllNum();
   String[][] arrayReport = new String[templist.size()][3];
   if (templist != null) {
    for (int i = 0; i < templist.size(); i++) {
     FlowInfo arr = (FlowInfo) templist.get(i);
     arrayReport[i][0] = arr.getTitle() ;// 工单名称
     arrayReport[i][1] = String.valueOf(arr.getInfoId()); //工单号
     arrayReport[i][2] = arr.getText4(); // 退费金额
    }
   }
   vot = null;
   return arrayReport;
  } catch (Exception ex) {
   return null;
  }

}

0 0