实体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="#" οnclick="exportExcel();" class="excel">导出成XLS</a>