应用poi导出excel的基本action操作

实体bean(SingleMonthData[set/get]): 

private long  id;
 private long  statYear;
 private long  typeid;
 private String className;
 private Date  lastUpdateTime;
 private String  lastUpdateUser;
 private long  month1;
 private long  month2;
 private long  month3;
 private long  month4;
 private long  month5;
 private long  month6;
 private long  month7;
 private long  month8;
 private long  month9;
 private long  month10;
 private long  month11;
 private long  month12;
 private TotalMonthData MonthData;

 

实体bean（KeyValue/set*/get*）：
private String key;
 private String value; 

 

BEAN:

public interface  Constract {
   static final KeyValue [] EXCEL_COLUMN_NAME = {
   new KeyValue("ClassName", "工单类型"),
   new KeyValue("Month1", "1月"),  //Month1...字段，1月，为标题
   new KeyValue("Month2", "2月"),
   new KeyValue("Month3", "3月"),
   new KeyValue("Month4", "4月"),
   new KeyValue("Month5", "5月"),
   new KeyValue("Month6", "6月"),
   new KeyValue("Month7", "7月"),
   new KeyValue("Month8", "8月"),
   new KeyValue("Month9", "9月"),
   new KeyValue("Month10", "10月"),
   new KeyValue("Month11", "11月"),
   new KeyValue("Month12", "12月")
 };
}

 

ACTION:

 

public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws RemoteException,Exception{
      request.setCharacterEncoding("UTF-8");
      response.setContentType("text/json;chartset=UTF-8");
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
      String yearTime = request.getParameter("yearTime")==null?sdf.format(new Date()):request.getParameter("yearTime");
   String classId = request.getParameter("classId")==null?"":request.getParameter("classId");
   String opflag = request.getParameter("opflag")==null?"":request.getParameter("opflag");
   String excelFileName = "";
   HSSFWorkbook hwb;
   IFlowWorkplanStatisticsSV  statisticsValues = (IFlowWorkplanStatisticsSV)ServiceFactory.getService(IFlowWorkplanStatisticsSV.class);
   //open|close
   if("firstpage".equals(opflag)){//totalSum
    IFlowWorkplanStatisticsValue[] fwsBeans = statisticsValues.queryFlowWorkplan(yearTime);
    SingleMonthData[] monthDatas = this.initsMonthDatas(fwsBeans);
    String tmp = yearTime+"年流程工单合计量.xls";
    excelFileName = new String(tmp.getBytes(),"ISO-8859-1");
    hwb = this.grenteExcel(monthDatas,excelFileName);
   }else{
    IFlowWorkplanTranStatisticsValue[] tranStatisticsValues = statisticsValues.queryFlowWorkplanTranStatistics(yearTime, classId);
    IFlowWorkplanStatisticsValue[] fwsBeans = statisticsValues.queryFlowWorkplanGraph(yearTime, classId);
    SingleMonthData[] monthDatas = this.initsMonthDatas(tranStatisticsValues);
    String tmp = yearTime+"年流程"+fwsBeans[0].getClassName()+"工单量.xls";
    excelFileName = new String(tmp.getBytes(),"ISO-8859-1");
    hwb = this.grenteExcel(monthDatas,excelFileName);
   }
   response.reset();
   response.setContentType("contentType=application/vnd.ms-excel");
   response.setHeader("Content-Disposition", "attachment;filename=\"" + excelFileName + "\"");
   hwb.write(response.getOutputStream());
   response.flushBuffer();
    }

    public SingleMonthData[] initsMonthDatas(Object[] obj){
     SingleMonthData[] monthDatas = new SingleMonthData[obj.length];
     if(obj instanceof IFlowWorkplanStatisticsValue[]){
      IFlowWorkplanStatisticsValue[] fwsBeans  =  (FlowWorkplanStatisticsBean[]) obj;

    for(int i=0;i<obj.length;i++){
     SingleMonthData singleMonthData = new SingleMonthData();
     singleMonthData.setClassName(fwsBeans[i].getClassName());
     singleMonthData.setMonth1(fwsBeans[i].getMonth1());
     singleMonthData.setMonth2(fwsBeans[i].getMonth2());
     singleMonthData.setMonth3(fwsBeans[i].getMonth3());
     singleMonthData.setMonth4(fwsBeans[i].getMonth4());
     singleMonthData.setMonth5(fwsBeans[i].getMonth5());
     singleMonthData.setMonth6(fwsBeans[i].getMonth6());
     singleMonthData.setMonth7(fwsBeans[i].getMonth7());

     singleMonthData.setMonth8(fwsBeans[i].getMonth8());
     singleMonthData.setMonth9(fwsBeans[i].getMonth9());
     singleMonthData.setMonth10(fwsBeans[i].getMonth10());
     singleMonthData.setMonth11(fwsBeans[i].getMonth11());
     singleMonthData.setMonth12(fwsBeans[i].getMonth12());
     monthDatas[i] = singleMonthData;
    }
     }else if(obj instanceof IFlowWorkplanTranStatisticsValue[]){
      IFlowWorkplanTranStatisticsValue[] fwsBeans  =  (FlowWorkplanTranStatisticsBean[]) obj;
    for(int i=0;i<obj.length;i++){
     SingleMonthData singleMonthData = new SingleMonthData();
     singleMonthData.setClassName(fwsBeans[i].getTypeName());
     singleMonthData.setMonth1(fwsBeans[i].getMonth1());
     singleMonthData.setMonth2(fwsBeans[i].getMonth2());
     singleMonthData.setMonth3(fwsBeans[i].getMonth3());
     singleMonthData.setMonth4(fwsBeans[i].getMonth4());
     singleMonthData.setMonth5(fwsBeans[i].getMonth5());
     singleMonthData.setMonth6(fwsBeans[i].getMonth6());
     singleMonthData.setMonth7(fwsBeans[i].getMonth7());

     singleMonthData.setMonth8(fwsBeans[i].getMonth8());
     singleMonthData.setMonth9(fwsBeans[i].getMonth9());
     singleMonthData.setMonth10(fwsBeans[i].getMonth10());
     singleMonthData.setMonth11(fwsBeans[i].getMonth11());
     singleMonthData.setMonth12(fwsBeans[i].getMonth12());
     monthDatas[i] = singleMonthData;
    }
     }
     return monthDatas;
    }

  public  HSSFWorkbook grenteExcel(Object[] obj,String fileName){
   ExcelHelper  excelHelper = new ExcelHelper();
   HSSFWorkbook wbWorkbook = excelHelper.item2excel(obj, Constract.EXCEL_COLUMN_NAME,"流程工单量");
   HSSFSheet sheet = wbWorkbook.getSheetAt(0);

   sheet.setColumnWidth((short)0, (short)7000);
   sheet.setColumnWidth((short)1, (short)3000);
   sheet.setColumnWidth((short)2, (short)3000);
   sheet.setColumnWidth((short)3, (short)3000);
   sheet.setColumnWidth((short)4, (short)3000);
   sheet.setColumnWidth((short)5, (short)3000);
   sheet.setColumnWidth((short)6, (short)3000);
   sheet.setColumnWidth((short)7, (short)3000);
   sheet.setColumnWidth((short)8, (short)3000);
   sheet.setColumnWidth((short)9, (short)3000);
   sheet.setColumnWidth((short)10, (short)3000);
   sheet.setColumnWidth((short)11, (short)3000);
   sheet.setColumnWidth((short)12, (short)3000);
   return wbWorkbook;
   }

 

js:

function exportExcel(){
    var opflag = $("#opflag").val();
 var yearTime = $("#yearTime").val();
 var classId = $("#classId").val();
 window.location.href = path+"/business/com.*.statistics.web.action.StatisticsAction?action=exportExcel&yearTime="+yearTime+"&classId="+classId+"&opflag="+opflag;
}

 

 jsp:

<a href="#" onclick="exportExcel();" class="excel">导出成XLS</a>