java的excel导出_java 实现excel 导出功能

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 voidoutCompassTongJiData(Integer compassId,String fromWhere,ModelMap model,HttpServletRequest request,HttpServletResponse response)5 {6 String urlPath = request.getScheme()+"://"+request.getServerName()+request.getContextPath();7 XSSFWorkbook rwb1 = newXSSFWorkbook();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

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 compassTongJis = conferencesService.getCompassTongJiByType(fromWhere,typeString1,typeString2,startTime,endTime);//i,group by dataId 查询不重复的data79 //如果不为空，遍历

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

133 List 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 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

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

192 List 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 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 = newFileOutputStream(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 ="";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 }