java 实现excel 导出功能
实现功能：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 }
梦想是一个说出来就矫情的东西，它是生长在暗地里的一颗种子，只有破土而出，拔节而长，终有一日开出花来，才能正大光明的让所有人都知道。在此之前，除了坚持，别无选择。
