应用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>