實現功能：java導出excel表
1、jsp代碼
1 <form id="zhanwForm" action="<%=path%>/conferences.do?" target="_self" method="get" > 2 <input type="hidden" name="method" value="outData"/> 3 <input type="hidden" name="compassId" value='1'/> 4 <input type="hidden" name="fromWhere" value="AAAA"/> 5 6 </from>
2、js代碼
1 $(".daochu1").click(function(){ 2 var bank = new Array(); 3 $.each($(".guangGaoLeiXing").find("input:checked"), function () { 4 bank.push($(this).val()); 5 }); 6 if(bank == false){ 7 alert("請選擇要統計類型"); 8 }else{ 9 $("#zhanwForm").submit(); 10 } 11 }) 12 13 $(".lyBtn").click(function(){ 14 $(".mask").show(); 15 $(".tishiDiv").show(); 16 $.ajax({ 17 url : '<%=path %>/conferences.do?method=getUserLy&fromWhere=csco', 18 type : 'GET', 19 dataType : 'json', 20 success : function(data) { 21 $(".mask").hide(); 22 $(".tishiDiv").hide(); 23 window.open(data.url); 24 } 25 }) 26 });
3、action處理代碼
1 //導出數據 2 //SELECT SUBSTRING(create_time,1,10) s,COUNT(*) FROM data_read WHERE data_id IN (SELECT data_id FROM DATA WHERE model_id=2 OR model_id=27) GROUP BY s 3 @RequestMapping(params = "method=outData",method = RequestMethod.GET) 4 public void outCompassTongJiData(Integer compassId,String fromWhere,ModelMap model,HttpServletRequest request,HttpServletResponse response) 5 { 6 String urlPath = request.getScheme()+"://"+request.getServerName()+request.getContextPath(); 7 XSSFWorkbook rwb1 = new XSSFWorkbook(); 8 9 while(true) 10 { 11 if(rwb1.getNumberOfSheets() != 0){ 12 rwb1.removeSheetAt(0); 13 } 14 else{ 15 break; 16 } 17 } 18 try { 19 int bankLong = 0; 20 int bank1Long = 0; 21 String [] bank = request.getParameterValues("bank");//廣告類型選擇 22 String [] bank1 = request.getParameterValues("bank1");//身份選擇 23 String startTime = request.getParameter("startTime"); 24 String endTime = request.getParameter("endTime"); 25 String[] shenfenList = {"醫生","醫學生","其他"}; 26 String[] zhichengArray = {"主任醫師","副主任醫師","主治醫師","住院醫師（有執業證）","住院醫師（未考執業證）","助理醫師（有執業證）","助理醫師（未考執業證）","其他"}; 27 String typeString1 =""; 28 String typeString2 =""; 29 if(bank!=null){ 30 bankLong = bank.length; 31 for(int i=0;i<bankLong;i++){ 32 if(bank[i].equals("1")||bank[i].equals("3")||bank[i].equals("8")){ 33 typeString1 = typeString1+bank[i]+","; 34 }else { 35 typeString2 = typeString2+bank[i]+","; 36 } 37 } 38 } 39 if(typeString1 != null && typeString1.length() != 0){ 40 typeString1 = typeString1.substring(0, typeString1.length()-1); 41 } 42 if(typeString2 != null && typeString2.length() != 0){ 43 typeString2 = typeString2.substring(0, typeString2.length()-1); 44 } 45 if(bank1!=null){ 46 bank1Long = bank1.length; 47 } 48 49 XSSFSheet ws1 = rwb1.createSheet("統計數據"); 50 ws1.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); 51 ws1.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); 52 ws1.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); 53 ws1.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); 54 ws1.addMergedRegion(new CellRangeAddress(0, 0, 4, 5)); 55 int count = 0;//行 56 int rolNUm = 0;//列 57 58 XSSFRow row1 = ws1.createRow(0); 59 60 row1.createCell(rolNUm).setCellValue("類型"); 61 rolNUm++; 62 row1.createCell(rolNUm).setCellValue("ID"); 63 rolNUm++; 64 row1.createCell(rolNUm).setCellValue("標題"); 65 rolNUm++; 66 row1.createCell(rolNUm).setCellValue("地址"); 67 rolNUm++; 68 row1.createCell(rolNUm).setCellValue("點擊量");//1/2 69 70 count++; 71 XSSFRow row2 = ws1.createRow(count); 72 row2.createCell(rolNUm).setCellValue("總點擊次數"); 73 rolNUm++; 74 row2.createCell(rolNUm).setCellValue("總點擊用戶數"); 75 //六種統計類型：首頁廣告、上方廣告、session相關性廣告、展商活動、動態（現場秀）、消息站 76 77 //得到需要統計的廣告類型的集合 78 List<CompassTongji> compassTongJis = conferencesService.getCompassTongJiByType(fromWhere,typeString1,typeString2,startTime,endTime);// i,group by dataId 查詢不重復的data 79 //如果不為空，遍歷 80 if(compassTongJis != null && compassTongJis.size() != 0){ 81 for(CompassTongji compassTongJi :compassTongJis){ 82 int dataId = compassTongJi.getDataId();//廣告ID 83 int type = compassTongJi.getType(); 84 String title = compassTongJi.getDataTitle();//廣告標題 85 String url1 = compassTongJi.getDataUrl()==null?"":compassTongJi.getDataUrl();//廣告地址 86 String url = ""; 87 if(type - 8 == 0){ 88 if(url1.indexOf("method=")!= -1){ 89 String[] urlStrings = url1.split("&"); 90 url = urlStrings[0]+"&"+urlStrings[1]; 91 }else{ 92 int index = url1.indexOf("?"); 93 if(index != -1){ 94 url = url1.substring(0, index); 95 }else{ 96 url = url1; 97 } 98 } 99 }else{ 100 int index = url1.indexOf("?"); 101 if(index != -1){ 102 url = url1.substring(0, index); 103 }else{ 104 url = url1; 105 } 106 107 } 108 109 //總點擊量 110 int totalClickNumber = conferencesService.getTotalClickNumber(dataId,url,startTime,endTime,type,fromWhere); 111 //總點擊人數 112 int totalClickPeopleNumber = conferencesService.getTotalClickPeopleNumber(dataId,url,startTime,endTime,type,fromWhere); 113 count++; 114 XSSFRow row3 = ws1.createRow(count); 115 rolNUm=0; 116 row3.createCell(rolNUm).setCellValue(type == 1?"首頁廣告":type ==2?"上方廣告":type==3?"session相關性廣告":type==4?"展商活動":type==5?"動態（現場秀）":type==6?"消息站":type == 7?"彈窗":type == 8?"推送":"首頁模塊"); 117 rolNUm++; 118 row3.createCell(rolNUm).setCellValue(dataId); 119 rolNUm++; 120 row3.createCell(rolNUm).setCellValue(title); 121 rolNUm++; 122 row3.createCell(rolNUm).setCellValue(url1); 123 rolNUm++; 124 row3.createCell(rolNUm).setCellValue(totalClickNumber); 125 rolNUm++; 126 row3.createCell(rolNUm).setCellValue(totalClickPeopleNumber); 127 for(int j=0;j<bank1Long;j++){ 128 int tongJiZiDuan = Integer.valueOf(bank1[j]); 129 if (tongJiZiDuan == 1) { 130 //領域統計 userLy 131 //先查詢所有領域列表 132 //遍歷領域列表(compassfield) 根據 領域id 查詢 用戶表（field_id 領域ID field 領域 ） 得到 領域統計信息 133 List<CompassField> compassList = conferencesService.getCompassList(); 134 int compassListLength = compassList.size(); 135 136 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, compassListLength+rolNUm)); 137 row1.createCell(rolNUm+1).setCellValue("領域"); 138 if(compassList != null && compassList.size()!=0){ 139 for (CompassField compassField : compassList) { 140 int compassFieldId = compassField.getCompassFieldId(); 141 int countLy = conferencesService.getUserLyInfo(dataId,url,compassFieldId,startTime,endTime,type,fromWhere); 142 rolNUm++; 143 row2.createCell(rolNUm).setCellValue(compassField.getFieldName()); 144 row3.createCell(rolNUm).setCellValue(countLy); 145 } 146 } 147 } 148 if(tongJiZiDuan == 2){//科室統計 userKs 149 List<CompassKs> compassKsListList = conferencesService.getCompassKsList(); 150 int userKsListLength = compassKsListList.size(); 151 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userKsListLength+rolNUm)); 152 row1.createCell(rolNUm+1).setCellValue("科室"); 153 if(compassKsListList != null && compassKsListList.size()!=0){ 154 for (CompassKs compassKs : compassKsListList) { 155 int compassKsId = compassKs.getCompassKsId(); 156 int countKs = conferencesService.getUserKsInfo(dataId,url,compassKsId,startTime,endTime,type,fromWhere); 157 rolNUm++; 158 row2.createCell(rolNUm).setCellValue(compassKs.getKsName()); 159 row3.createCell(rolNUm).setCellValue(countKs); 160 } 161 } 162 } 163 164 if(tongJiZiDuan == 3){//職稱統計 zhicheng 165 int userZhichengListLeng = zhichengArray.length; 166 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userZhichengListLeng+rolNUm)); 167 row1.createCell(rolNUm+1).setCellValue("職稱"); 168 for (int k=0;k<userZhichengListLeng;k++) { 169 String zhicheng = zhichengArray[k]; 170 int userZhichengList = conferencesService.getUserZhichengInfo(dataId,url,startTime,endTime,zhicheng,type,fromWhere); 171 rolNUm++; 172 row2.createCell(rolNUm).setCellValue(zhicheng); 173 row3.createCell(rolNUm).setCellValue(userZhichengList); 174 } 175 176 } 177 178 if(tongJiZiDuan == 4){//身份統計 179 int userShenfenListLength = shenfenList.length; 180 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userShenfenListLength + rolNUm)); 181 row1.createCell(rolNUm+1).setCellValue("身份"); 182 183 for (int k = 0;k<userShenfenListLength;k++) { 184 String shenfen = shenfenList[k]; 185 int countShenFen = conferencesService.getClickUserShenFenInfo( dataId,url,startTime,endTime,shenfen,type,fromWhere); 186 rolNUm++; 187 row2.createCell(rolNUm).setCellValue(shenfen); 188 row3.createCell(rolNUm).setCellValue(countShenFen); 189 } 190 } 191 if(tongJiZiDuan == 5){//省市（市）統計 192 List<Hospital> provinceList = conferencesService.getProvinceList(); 193 194 if(provinceList != null && provinceList.size()!=0){ 195 for (Hospital hospital : provinceList) { 196 Integer provinceId = hospital.getHospitalId(); 197 String provinceName = hospital.getProvince(); 198 199 if(provinceName!=null && provinceName.length() != 0){ 200 List<Hospital> cityList = conferencesService.getCityInfoByProvinceId(provinceId); 201 202 if(cityList != null && cityList.size() != 0){ 203 int cityListLength = cityList.size(); 204 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, rolNUm+cityListLength)); 205 row1.createCell(rolNUm+1).setCellValue(provinceName); 206 for (Hospital hospital2 : cityList) { 207 //得到市的名稱 208 String cityName = hospital2.getCity(); 209 Integer cityId = hospital2.getHospitalId(); 210 if(cityName!=null && cityName.length() != 0){ 211 int cityNum = conferencesService.getCityInfoByProvinceNameAndCityName(dataId,url,startTime, endTime, cityId,type,fromWhere); 212 rolNUm++; 213 row2.createCell(rolNUm).setCellValue(cityName); 214 row3.createCell(rolNUm).setCellValue(cityNum); 215 } 216 } 217 } 218 }//省不為空 219 } 220 } 221 }//省市統計結束 222 } 223 } 224 }//最外if結束 225 226 227 228 String filePath = request.getSession().getServletContext().getRealPath("files/execl"); 229 File conFile = new File(filePath); //目錄結構 230 if(!conFile.exists()) 231 { 232 conFile.mkdir(); 233 } 234 String fileName = "compassTongJi.xlsx"; 235 File file = new File(filePath+"/"+fileName); 236 if(file.exists()) 237 { 238 file.delete(); 239 } 240 else{ 241 file.createNewFile(); 242 } 243 FileOutputStream fout = new FileOutputStream(file); 244 rwb1.write(fout); 245 fout.close(); 246 //JSONObject jsonObject = new JSONObject(); 247 //jsonObject.accumulate("url",request.getContextPath()+"/files/execl/"+fileName); 248 //writeToJson(response, jsonObject.toString()); 249 String urlString ="<script type='text/javascript'>window.parent.open('"+urlPath+"/files/execl/"+fileName+"')</script>"; 250 PrintWriter out = response.getWriter(); 251 response.setCharacterEncoding("utf-8"); 252 response.setContentType("text/html;charset=UTF-8"); 253 out.write(urlString); 254 out.flush(); 255 out.close(); 256 } catch (Exception e) { 257 // TODO: handle exception 258 e.printStackTrace(); 259 } 260 }
夢想是一個說出來就矯情的東西，它是生長在暗地里的一顆種子，只有破土而出，拔節而長，終有一日開出花來，才能正大光明的讓所有人都知道。在此之前，除了堅持，別無選擇。
)
