导出excel java实现
1.前台页面代码：
1 <tr> 2 <td><input dataId="excel" type="button" value="导出Excel" onclick="exportExcel(this)"/></td> 3 </tr>
2.js代码：
1 function exportExcel(){ 2 var pros = $('#problemType').combotree('getValues'); 3 var pro = ""; 4 if(pros.length>0){ 5 for(var i=0;i<pros.length;i++){ 6 if(i!=pros.length-1){ 7 pro += pros[i] + ","; 8 }else{ 9 pro += pros[i]; 10 } 11 } 12 } 13 location.href='<@s.url namespace="/app/pollEnterprise" action="psCheckInfor!exportExcel" includeParams="none" />?startTime='+$("#startTime").val()+ 14 '&endTime='+$("#endTime").val()+'&areacode='+$("#areaCode").combotree('getValue')+'&hasProblem='+$("#hasProblem").val()+ 15 '&psname='+$("#psname").val()+'&deal='+$('#deal').val()+'&dealOther='+$('#dealOther').val()+'&pstype='+$('#hy').val()+ 16 '&problemType='+pro+'&isComplete='+$('#isComplete').val(); 17 };
3.java代码：
（1）导出方法exportExcel()代码：
1 /** 2 * 获取数据,导出excel 3 */ 4 public void exportExcel() throws Exception { 5 String excelName = "排污企业检查.xls"; 6 String sheetName = "排污企业检查"; 7 //结果集，表头，excel名，页签名 8 Map<String,Object> map = new HashMap<String,Object>(); 9 map = this.argMap(map); 10 List<Object> list = this.psCheckInforManager.findAllObject(map); 11 this.generateExcel(list,excelName, sheetName); 12 }
（2）拼装excel文件方法generateExcel(list,excelName, sheetName)代码：
1 /** 2 * 生成excel，并触发下载的方法 3 */ 4 public void generateExcel(List<Object> list,String excelName,String sheetName) throws Exception { 5 //文件生成到临时目录中 6 String parentPath = JointFrameConfigManager.getDatafileRootRelPath("excel/temp/"); 7 //在parentPath路径下创建一个文件 8 File pFile = new File(parentPath); 9 if (!pFile.exists()) { 10 //创建此抽象路径名，包括必要的和不存在的父目录的目录 11 pFile.mkdirs(); 12 } 13 //System.nanoTime():系统计时器的当前值;file.separator:考虑到了跨平台，将分隔符用File.separator 代替 14 String filePath = parentPath + File.separator + System.nanoTime() 15 + ".xls"; 16 //创建一个可读写的工作簿 17 WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath)); 18 //生成名为"sheetName"的工作表，参数0表示这是第一页 19 WritableSheet sheet = workbook.createSheet(sheetName, 0); 20 21 22 //标题样式 23 WritableFont titleFont = new WritableFont(WritableFont.TIMES, 12, 24 WritableFont.NO_BOLD, false); 25 //设置标题字体颜色 26 titleFont.setColour(Colour.WHITE); 27 //设置标题字体样式 28 WritableCellFormat titleStyle = new WritableCellFormat(titleFont); 29 //设置标题的背景颜色 30 titleStyle.setBackground(Colour.LIGHT_BLUE); 31 //设置边框 32 titleStyle.setBorder(Border.ALL, BorderLineStyle.THIN); 33 //将标题的字体设置为居中显示 34 titleStyle.setAlignment(Alignment.CENTRE); 35 36 37 //报表尾样式 38 WritableFont toolFont = new WritableFont(WritableFont.TIMES, 11, 39 WritableFont.NO_BOLD, false); 40 //设置字体颜色 41 toolFont.setColour(Colour.WHITE); 42 //设置样式 43 WritableCellFormat toolStyle = new WritableCellFormat(toolFont); 44 //设置背景颜色 45 toolStyle.setBackground(Colour.LIGHT_BLUE); 46 //设置边框 47 toolStyle.setBorder(Border.ALL, BorderLineStyle.THIN); 48 //设置居中 49 toolStyle.setAlignment(Alignment.RIGHT); 50 51 52 // 表格头样式 53 WritableFont times16font = new WritableFont(WritableFont.TIMES, 11, 54 WritableFont.NO_BOLD, false); 55 //设置字体颜色 56 times16font.setColour(Colour.WHITE); 57 //设置样式 58 WritableCellFormat headStyle = new WritableCellFormat(times16font); 59 //设置背景颜色 60 headStyle.setBackground(Colour.LIGHT_BLUE); 61 //设置边框 62 headStyle.setBorder(Border.ALL, BorderLineStyle.THIN); 63 64 65 // 高亮行样式 66 WritableFont highLigthRowFont = new WritableFont(WritableFont.TIMES, 67 10, WritableFont.NO_BOLD, false); 68 highLigthRowFont.setColour(Colour.BLACK); 69 WritableCellFormat highLigthRowStyle = new WritableCellFormat( 70 highLigthRowFont); 71 highLigthRowStyle.setBackground(Colour.LIGHT_GREEN); 72 highLigthRowStyle.setBorder(Border.ALL, BorderLineStyle.THIN); 73 74 75 // 非高亮行样式 76 WritableFont unHighLigthRowFont = new WritableFont(WritableFont.TIMES, 77 10, WritableFont.NO_BOLD, false); 78 unHighLigthRowFont.setColour(Colour.BLACK); 79 WritableCellFormat unHighLigthRowStyle = new WritableCellFormat( 80 unHighLigthRowFont); 81 unHighLigthRowStyle.setBackground(Colour.WHITE); 82 unHighLigthRowStyle.setBorder(Border.ALL, BorderLineStyle.THIN); 83 84 85 //定义表头列名数组 86 // String[] columns =cols; 87 // 设置Excel页签名字 88 sheet.setName(sheetName); 89 //合并标题单元格（mergeCells(a,b,c,d)）a:要合并单元格的开始x坐标，b:要合并单元格的开始y坐标 90 //c:要合并单元格的结束x坐标,d:要合并单元格的结束y坐标 91 92 sheet.mergeCells(0, 0, 42, 0); 93 //设置标题 94 sheet.mergeCells(0, 1, 0, 3); 95 sheet.mergeCells(1, 1, 1, 3); 96 sheet.mergeCells(2, 1, 2, 3); 97 sheet.mergeCells(3, 1, 3, 3); 98 sheet.mergeCells(4, 1, 4, 3); 99 sheet.mergeCells(5, 1, 5, 3); 100 sheet.mergeCells(6, 1, 6, 3); 101 sheet.mergeCells(7, 1, 7, 3); 102 sheet.mergeCells(8, 1, 8, 3); 103 sheet.mergeCells(9, 1, 9, 3); 104 sheet.mergeCells(10, 1, 10, 3); 105 sheet.mergeCells(11, 1, 17, 1);//合并“存在问题类型” 106 sheet.mergeCells(11, 2, 11, 3); 107 sheet.mergeCells(12, 2, 12, 3); 108 sheet.mergeCells(13, 2, 13, 3); 109 sheet.mergeCells(14, 2, 14, 3); 110 sheet.mergeCells(15, 2, 15, 3); 111 sheet.mergeCells(16, 2, 16, 3); 112 sheet.mergeCells(17, 2, 17, 3); 113 sheet.mergeCells(18, 1, 25, 1);//合并“处理情况” 114 sheet.mergeCells(18, 2, 18, 3); 115 sheet.mergeCells(19, 2, 19, 3); 116 sheet.mergeCells(20, 2, 20, 3); 117 sheet.mergeCells(21, 2, 21, 3); 118 sheet.mergeCells(22, 2, 22, 3); 119 sheet.mergeCells(23, 2, 24, 2);//合并“处罚” 120 sheet.mergeCells(23, 3, 23, 3); 121 sheet.mergeCells(24, 3, 24, 3); 122 sheet.mergeCells(25, 2, 25, 3); 123 sheet.mergeCells(26, 1, 30, 1);//合并“其它措施” 124 sheet.mergeCells(26, 2, 26, 3); 125 sheet.mergeCells(27, 2, 27, 3); 126 sheet.mergeCells(28, 2, 28, 3); 127 sheet.mergeCells(29, 2, 29, 3); 128 sheet.mergeCells(30, 2, 30, 3); 129 sheet.mergeCells(31, 1, 31, 3); 130 sheet.mergeCells(32, 1, 32, 3); 131 sheet.mergeCells(33, 1, 33, 3); 132 sheet.mergeCells(34, 1, 34, 3); 133 sheet.mergeCells(35, 1, 35, 3); 134 sheet.mergeCells(36, 1, 36, 3); 135 sheet.mergeCells(37, 1, 37, 3); 136 sheet.mergeCells(38, 1, 38, 3); 137 sheet.mergeCells(39, 1, 39, 3); 138 sheet.mergeCells(40, 1, 40, 3); 139 sheet.mergeCells(41, 1, 41, 3); 140 sheet.mergeCells(42, 1, 42, 3); 141 142 143 //将定义好的单元格添加到工作表中 144 /*例如：label=new Label(0,2,"编号",format2); 145 sheet.addCell(label);*/ 146 sheet.addCell(new Label(0, 0, sheetName, titleStyle)); 147 sheet.addCell(new Label(0, 1, "企业名称", headStyle)); 148 sheet.addCell(new Label(1, 1, "行业", headStyle)); 149 sheet.addCell(new Label(2, 1, "企业所属地", headStyle)); 150 sheet.addCell(new Label(3, 1, "填报单位", headStyle)); 151 sheet.addCell(new Label(4, 1, "检查时间", headStyle)); 152 sheet.addCell(new Label(5, 1, "建设项目名称", headStyle)); 153 sheet.addCell(new Label(6, 1, "建成投运时间", headStyle)); 154 sheet.addCell(new Label(7, 1, "是否属于工业园区", headStyle)); 155 sheet.addCell(new Label(8, 1, "工业园区名称", headStyle)); 156 sheet.addCell(new Label(9, 1, "是否存在问题", headStyle)); 157 sheet.addCell(new Label(10, 1, "存在问题", headStyle)); 158 sheet.addCell(new Label(11, 1, "存在问题类型", headStyle)); 159 sheet.addCell(new Label(11, 2, "超标排放 ", headStyle)); 160 sheet.addCell(new Label(12, 2, "未批先建 ", headStyle)); 161 sheet.addCell(new Label(13, 2, "未执行三同时 ", headStyle)); 162 sheet.addCell(new Label(14, 2, "不正常使用污染防治设施 ", headStyle)); 163 sheet.addCell(new Label(15, 2, "偷排偷放 ", headStyle)); 164 sheet.addCell(new Label(16, 2, "伪造或篡改环境监测数据 ", headStyle)); 165 sheet.addCell(new Label(17, 2, "其它 ", headStyle)); 166 sheet.addCell(new Label(18, 1, "处理情况", headStyle)); 167 sheet.addCell(new Label(18, 2, "责令停止建设", headStyle)); 168 sheet.addCell(new Label(19, 2, "责令停产", headStyle)); 169 sheet.addCell(new Label(20, 2, "责令限期改正或治理 ", headStyle)); 170 sheet.addCell(new Label(21, 2, "责令关停取缔 ", headStyle)); 171 sheet.addCell(new Label(22, 2, "限期补办环评手续 ", headStyle)); 172 sheet.addCell(new Label(23, 2, "处罚", headStyle)); 173 sheet.addCell(new Label(23, 3, "是/否 ", headStyle)); 174 sheet.addCell(new Label(24, 3, "处罚金额", headStyle)); 175 sheet.addCell(new Label(25, 2, "其它", headStyle)); 176 sheet.addCell(new Label(26, 1, "其它措施", headStyle)); 177 sheet.addCell(new Label(26, 2, "实施查封、扣押 ", headStyle)); 178 sheet.addCell(new Label(27, 2, "实施按日连续处罚", headStyle)); 179 sheet.addCell(new Label(28, 2, "实施限产、停产 ", headStyle)); 180 sheet.addCell(new Label(29, 2, "移送司法机关及相关部门", headStyle)); 181 sheet.addCell(new Label(30, 2, "其它 ", headStyle)); 182 sheet.addCell(new Label(31, 1, "按日处罚开始时间", headStyle)); 183 sheet.addCell(new Label(32, 1, "按日处罚结束时间", headStyle)); 184 sheet.addCell(new Label(33, 1, "按日处罚金额", headStyle)); 185 sheet.addCell(new Label(34, 1, "整改时限", headStyle)); 186 sheet.addCell(new Label(35, 1, "完成情况", headStyle)); 187 sheet.addCell(new Label(36, 1, "完成时间 ", headStyle)); 188 sheet.addCell(new Label(37, 1, "完成情况描述 ", headStyle)); 189 sheet.addCell(new Label(38, 1, "未完成情况描述 ", headStyle)); 190 sheet.addCell(new Label(39, 1, "责任单位", headStyle)); 191 sheet.addCell(new Label(40, 1, "监管单位", headStyle)); 192 sheet.addCell(new Label(41, 1, "填报人", headStyle)); 193 sheet.addCell(new Label(42, 1, "审核人", headStyle)); 194 195 196 //循环设置单元格宽度和高度 197 for(int j=0;j<42;j++){ 198 sheet.setColumnView(j, 15);//设excel单元格的宽度 199 sheet.setRowView(j, 300);//设excel单元格的高度 200 if(j==0 || j==1 || j==2 || j==3){ 201 sheet.setColumnView(j, 25); 202 sheet.setRowView(j, 400); 203 } 204 } 205 206 207 //生成数据行，并且间隔行高亮显示 208 int rowIndex = 4; 209 210 211 //循环遍历结果集 212 for (int i=0;i<list.size();i++) { 213 int colIndex2 = 0; 214 Object[] objs = (Object[]) list.get(i); 215 //{objs[0]==null ? "" : objs[0].toString()}：三目运算：如果?前面的结果为true，那么执行冒号后面第一个条件""，如果?前面的结果为false，那么执行后面的结果，即objs[0].toString(); 216 sheet.addCell(new Label(colIndex2++, rowIndex, objs[0]==null ? "" : objs[0].toString()));//企业名称 217 sheet.addCell(new Label(colIndex2++, rowIndex, objs[1]==null ? "" : objs[1].toString()));//行业 218 sheet.addCell(new Label(colIndex2++, rowIndex, objs[3]==null ? "" : objs[3].toString()));//填报单位 219 sheet.addCell(new Label(colIndex2++, rowIndex, objs[2]==null ? "" : objs[2].toString()));//企业所属地 220 sheet.addCell(new Label(colIndex2++, rowIndex, objs[4]==null ? "" : objs[4].toString()));//检查时间 221 sheet.addCell(new Label(colIndex2++, rowIndex, objs[5]==null ? "" : objs[5].toString()));//建设项目名称 222 sheet.addCell(new Label(colIndex2++, rowIndex, objs[6]==null ? "" : objs[6].toString()));//建成投运时间 223 sheet.addCell(new Label(colIndex2++, rowIndex, objs[7]==null ? "" : objs[7].toString()));//是否属于<br/>工业园区 224 sheet.addCell(new Label(colIndex2++, rowIndex, objs[8]==null ? "" : objs[8].toString()));//工业园区名称 225 sheet.addCell(new Label(colIndex2++, rowIndex, objs[9]==null ? "" : objs[9].toString()));//是否存在问题 226 sheet.addCell(new Label(colIndex2++, rowIndex, objs[10]==null ? "" : objs[10].toString()));//存在问题 227 sheet.addCell(new Label(colIndex2++, rowIndex, objs[11]==null ? "" : objs[11].toString()));//超标排放 228 sheet.addCell(new Label(colIndex2++, rowIndex, objs[12]==null ? "" : objs[12].toString()));//未批先建 229 sheet.addCell(new Label(colIndex2++, rowIndex, objs[13]==null ? "" : objs[13].toString()));//未执行三同时 230 sheet.addCell(new Label(colIndex2++, rowIndex, objs[14]==null ? "" : objs[14].toString()));//不正常使用污染防治设施 231 sheet.addCell(new Label(colIndex2++, rowIndex, objs[15]==null ? "" : objs[15].toString()));//偷排偷放 232 sheet.addCell(new Label(colIndex2++, rowIndex, objs[16]==null ? "" : objs[16].toString()));//伪造或篡改环境监测数据 233 sheet.addCell(new Label(colIndex2++, rowIndex, objs[17]==null ? "" : objs[17].toString()));//其它 234 sheet.addCell(new Label(colIndex2++, rowIndex, objs[18]==null ? "" : objs[18].toString()));//责令停止建设 235 sheet.addCell(new Label(colIndex2++, rowIndex, objs[19]==null ? "" : objs[19].toString()));//责令停产 236 sheet.addCell(new Label(colIndex2++, rowIndex, objs[20]==null ? "" : objs[20].toString()));//责令限期改正或治理 237 sheet.addCell(new Label(colIndex2++, rowIndex, objs[21]==null ? "" : objs[21].toString()));//责令关停取缔 238 sheet.addCell(new Label(colIndex2++, rowIndex, objs[22]==null ? "" : objs[22].toString()));//限期补办环评手续 239 sheet.addCell(new Label(colIndex2++, rowIndex, objs[23]==null ? "" : objs[23].toString()));//是/否 处罚 240 sheet.addCell(new Label(colIndex2++, rowIndex, objs[24]==null ? "" : objs[24].toString()));//处罚金额 241 sheet.addCell(new Label(colIndex2++, rowIndex, objs[25]==null ? "" : objs[25].toString()));//其它 242 sheet.addCell(new Label(colIndex2++, rowIndex, objs[26]==null ? "" : objs[26].toString()));//实施查封、扣押 243 sheet.addCell(new Label(colIndex2++, rowIndex, objs[27]==null ? "" : objs[27].toString()));//实施按日连续处罚 244 sheet.addCell(new Label(colIndex2++, rowIndex, objs[28]==null ? "" : objs[28].toString()));//实施限产、停产 245 sheet.addCell(new Label(colIndex2++, rowIndex, objs[29]==null ? "" : objs[29].toString()));//移送司法机关及相关部门 246 sheet.addCell(new Label(colIndex2++, rowIndex, objs[30]==null ? "" : objs[30].toString()));//其它 247 sheet.addCell(new Label(colIndex2++, rowIndex, objs[31]==null ? "" : objs[31].toString()));//按日处罚开始时间 248 sheet.addCell(new Label(colIndex2++, rowIndex, objs[32]==null ? "" : objs[32].toString()));//按日处罚结束时间 249 sheet.addCell(new Label(colIndex2++, rowIndex, objs[33]==null ? "" : objs[33].toString()));//按日处罚金额 250 sheet.addCell(new Label(colIndex2++, rowIndex, objs[34]==null ? "" : objs[34].toString()));//整改时限 251 sheet.addCell(new Label(colIndex2++, rowIndex, objs[35]==null ? "" : objs[35].toString()));//完成情况 252 sheet.addCell(new Label(colIndex2++, rowIndex, objs[36]==null ? "" : objs[36].toString()));//完成时间 253 if(objs[37]!=null && !"".equals(objs[37].toString())){ 254 sheet.addCell(new Label(colIndex2++, rowIndex, objs[37].toString()));//完成情况描述 255 }else{ 256 sheet.addCell(new Label(colIndex2++, rowIndex, "")); 257 } 258 sheet.addCell(new Label(colIndex2++, rowIndex, objs[38]==null ? "" : objs[38].toString()));//未完成情况描述 259 if(objs[39]!=null && !"".equals(objs[39].toString())){ 260 sheet.addCell(new Label(colIndex2++, rowIndex, objs[39].toString()));//责任单位 261 }else{ 262 sheet.addCell(new Label(colIndex2++, rowIndex, "")); 263 } 264 if(objs[40]!=null && !"".equals(objs[40].toString())){ 265 sheet.addCell(new Label(colIndex2++, rowIndex, objs[40].toString()));//监管单位 266 }else{ 267 sheet.addCell(new Label(colIndex2++, rowIndex, "")); 268 } 269 sheet.addCell(new Label(colIndex2++, rowIndex, objs[41]==null ? "" : objs[41].toString()));//填报人 270 sheet.addCell(new Label(colIndex2++, rowIndex, objs[42]==null ? "" : objs[42].toString()));//审核人 271 rowIndex++; 272 } 273 274 275 //获取总记录条数 276 int totle = 0; 277 if (list != null) { 278 totle = list.size(); 279 } 280 281 282 //设置报表尾 283 sheet.mergeCells(0, rowIndex, 42, rowIndex); 284 sheet.mergeCells(0, rowIndex+1, 42, rowIndex+1); 285 sheet.addCell(new Label(0, rowIndex+1, "报告已完成 / 共计：" + totle + " 条。 ",toolStyle)); 286 workbook.write(); 287 workbook.close(); 288 //生成完毕 289 290 291 292 //下载生成的报表文件 293 File xlsFile = new File(filePath); 294 if (xlsFile.exists()) { 295 response.setHeader("Content-Disposition", "attachment; filename=" 296 + new String(excelName.getBytes("GBK"), "ISO8859-1")); // filename应该是编码后的(utf-8) 297 response.setHeader("charset", "utf-8"); 298 response.setContentType("application/vnd.ms-excel");//设置下载文件的格式，此处为msexcel 299 OutputStream os = response.getOutputStream(); 300 InputStream is = new FileInputStream(xlsFile); 301 byte[] tmp = new byte[1024]; 302 int len; 303 while ((len = is.read(tmp)) != -1) { 304 os.write(tmp, 0, len); 305 } 306 is.close(); 307 os.flush(); 308 os.close(); 309 310 // 下载完成后删除临时文件 311 xlsFile.delete(); 312 } 313 314 }
（3）封装查询条件方法argMap(Map<String,Object> map)代码:
1 /** 2 * 封装查询条件 3 * @param map 4 * @return 5 */ 6 public Map<String,Object> argMap(Map<String,Object> map){ 7 String userId = LoginUtil.getAppLoginUserId(request); 8 UserInforVo mdvo = enterpriseManager.findById(userId); 9 //appadmin不进行数据过滤 10 if(!mdvo.getUserName().equals("平台管理员")){ 11 level = enterpriseManager.getUserRoleRegion(mdvo.getRegionCode()); 12 String regions=enterpriseManager.getUserRoleChildRegion(mdvo.getRegionCode(), level); 13 if(regions.length()>0){ 14 regions = regions.substring(0, regions.length()-1); 15 } 16 if(areacode!=null && !"".equals(areacode)){ 17 level = enterpriseManager.getUserRoleRegion(areacode); 18 String regions_=enterpriseManager.getUserRoleChildRegion(areacode, level); //得到该行政区划下所有子区划 19 //如果行政区划选“全部” 20 if(areacode.equals("all")) 21 { 22 map.put("regions",regions); 23 }else if("hz".equals(areacode)){ //以下是行政区划选“**本级” 24 map.put("regions", "610700000"); 25 }else if("ak".equals(areacode)){ 26 map.put("regions", "610900000"); 27 }else if("bj".equals(areacode)){ 28 map.put("regions", "610300000"); 29 }else if("tc".equals(areacode)){ 30 map.put("regions", "610200000"); 31 }else if("hc".equals(areacode)){ 32 map.put("regions", "610581000"); 33 }else if("xy".equals(areacode)){ 34 map.put("regions", "610400000"); 35 }else if("ya".equals(areacode)){ 36 map.put("regions", "610600000"); 37 }else if("xa".equals(areacode)){ 38 map.put("regions", "610100000"); 39 }else if("xx".equals(areacode)){ 40 map.put("regions", "610101000"); 41 }else if("ylq".equals(areacode)){ 42 map.put("regions", "610403000"); 43 }else if("yl".equals(areacode)){ 44 map.put("regions", "610800000"); 45 }else if("wn".equals(areacode)){ 46 map.put("regions", "610500000"); 47 }else if("sl".equals(areacode)){ 48 map.put("regions", "611000000"); 49 }else if("sx".equals(areacode)){ 50 map.put("regions", "610000000"); 51 }else if(level==2 || level==1){ //选择省级或市级 52 map.put("regions", regions_); 53 }else { 54 map.put("regions", areacode); 55 } 56 } 57 else 58 { 59 map.put("regions",regions); 60 } 61 62 } 63 64 if((startTime!=null && !"".equals(startTime)) && (endTime!=null && !"".equals(endTime))){ 65 map.put("startTime", startTime); 66 map.put("endTime", endTime); 67 } 68 if(isComplete!=null && !"".equals(isComplete)){ 69 if(!"2".equals(isComplete)){ 70 map.put("isComplete", isComplete); 71 } 72 } 73 if(pstype!=null && !"".equals(pstype)){ 74 map.put("pstype", pstype); 75 } 76 if(psname!=null && !"".equals(psname)){ 77 map.put("psname", psname); 78 } 79 80 if(hasProblem!=null && !"".equals(hasProblem)){ 81 if(!"2".equals(hasProblem)){ 82 map.put("hasProblem", hasProblem); 83 } 84 } 85 if(deal!=null && !"".equals(deal)){ 86 if("0".equals(deal)){ 87 88 } 89 if("1".equals(deal)){ 90 map.put("dealStopBuild", 1); 91 } 92 if("2".equals(deal)){ 93 map.put("dealStopProduction", 1); 94 } 95 if("3".equals(deal)){ 96 map.put("dealPunishment", 1); 97 } 98 if("4".equals(deal)){ 99 map.put("dealClampdown", 1); 100 } 101 if("5".equals(deal)){ 102 map.put("dealXqbbhpsx", 1); 103 } 104 if("6".equals(deal)){ 105 map.put("dealRestrict", 1); 106 } 107 if("7".equals(deal)){ 108 map.put("dealPunishmentOther", 1); 109 } 110 map.put("deal", deal.equals("0") ? "" : deal);//导出excel时用 111 } 112 if(problemType!=null && !"".equals(problemType)){ 113 String[] problemTypes = problemType.split(","); 114 if(problemTypes.length>0){ 115 for(int k=0;k<problemTypes.length;k++){ 116 if("0".equals(problemTypes[k])){ 117 } 118 if("1".equals(problemTypes[k])){ 119 map.put("dealNotExecutCbpf", 1); 120 } 121 if("2".equals(problemTypes[k])){ 122 map.put("dealNotapprovedBeforeConstruction", 1); 123 } 124 if("3".equals(problemTypes[k])){ 125 map.put("dealNotExecutThreetongshizhidu", 1); 126 } 127 if("4".equals(problemTypes[k])){ 128 map.put("dealbzcsywrfzcs", 1); 129 } 130 if("5".equals(problemTypes[k])){ 131 map.put("dealStealingDischarge", 1); 132 } 133 if("6".equals(problemTypes[k])){ 134 map.put("dealCounterfeitMisrepresentData", 1); 135 } 136 if("7".equals(problemTypes[k])){ 137 map.put("dealOther", 1); 138 } 139 } 140 } 141 142 } 143 if(dealOther!=null && !"".equals(dealOther)){ 144 if("0".equals(dealOther)){ 145 146 } 147 if("1".equals(dealOther)){ 148 map.put("dealOtherAttachment", 1); 149 } 150 if("2".equals(dealOther)){ 151 map.put("dealOtherPunishmentperday", 1); 152 } 153 if("3".equals(dealOther)){ 154 map.put("dealOtherControlProduction", 1); 155 } 156 if("4".equals(dealOther)){ 157 map.put("dealOtherForlaw", 1); 158 } 159 if("5".equals(dealOther)){ 160 map.put("dealOtherOther", 1); 161 } 162 //map.put("dealOther", dealOther.equals("0") ? "" : dealOther);//导出excel时用 163 } 164 return map; 165 166 }
（4）查找符合条件信息的方法findAllObject(Map<String, Object> map)代码:
1 public List<Object> findAllObject(Map<String, Object> map) { 2 int isComplete = 3; 3 String psname = ""; 4 String pstype = ""; 5 String hasProblem = ""; 6 String startTime = ""; 7 String endTime = ""; 8 String deal = ""; 9 String dealOther = ""; 10 List<String> psids = new ArrayList<String>(); 11 // 完成状态 12 if (map.get("isComplete") != null && !"".equals(map.get("isComplete"))) { 13 isComplete = Integer.parseInt(map.get("isComplete").toString()); 14 } 15 // 是否存在问题 16 if (map.get("hasProblem") != null && !"".equals(map.get("hasProblem"))) { 17 hasProblem = (String) map.get("hasProblem"); 18 } 19 // 企业名称 20 if (map.get("psname") != null && !"".equals(map.get("psname"))) { 21 psname = (String) map.get("psname"); 22 } 23 // 行业 24 if (map.get("pstype") != null && !"".equals(map.get("pstype"))) { 25 pstype = (String) map.get("pstype"); 26 } 27 // 检查时间 28 if (map.get("startTime") != null && !"".equals(map.get("startTime"))) { 29 startTime = (String) map.get("startTime"); 30 } 31 if (map.get("endTime") != null && !"".equals(map.get("endTime"))) { 32 endTime = (String) map.get("endTime"); 33 } 34 // 填报单位 35 String newstr = ""; 36 if (map.get("regions") != null && !"".equals(map.get("regions"))) { 37 38 String str = map.get("regions").toString(); 39 String[] str1 = str.split(","); 40 if (str1.length > 0) { 41 for (int i = 0; i < str1.length; i++) { 42 if (i == str1.length - 1) { 43 newstr += "'" + str1[i] + "'"; 44 } else { 45 newstr += "'" + str1[i] + "'" + ","; 46 } 47 } 48 } 49 } 50 if (map.get("deal") != null && !"".equals(map.get("deal"))) { 51 deal = (String) map.get("deal"); 52 } 53 if (map.get("dealOther") != null && !"".equals(map.get("dealOther"))) { 54 dealOther = (String) map.get("dealOther"); 55 } 56 57 boolean flag = false; 58 StringBuffer str = new StringBuffer(); 59 if (map.get("dealNotExecutCbpf") != null 60 && !"".equals(map.get("dealNotExecutCbpf"))) { 61 str.append(" and (ill.deal_not_execut_cbpf = 1"); 62 flag = true; 63 } 64 65 if (map.get("dealNotapprovedBeforeConstruction") != null 66 && !"".equals(map.get("dealNotapprovedBeforeConstruction"))) { 67 if (flag) { 68 str.append(" or ill.deal_not_approved_before_construction = 1"); 69 } else { 70 str.append(" and (ill.deal_not_approved_before_construction = 1"); 71 flag = true; 72 } 73 } 74 75 if (map.get("dealNotExecutThreetongshizhidu") != null 76 && !"".equals(map.get("dealNotExecutThreetongshizhidu"))) { 77 if (flag) { 78 str.append(" or ill.deal_not_Execut_threetongshizhidu = 1"); 79 } else { 80 str.append(" and (ill.deal_not_Execut_threetongshizhidu = 1"); 81 flag = true; 82 } 83 } 84 85 if (map.get("dealbzcsywrfzcs") != null 86 && !"".equals(map.get("dealbzcsywrfzcs"))) { 87 if (flag) { 88 str.append(" or ill.deal_bzcsywrfzcs = 1"); 89 } else { 90 str.append(" and (ill.deal_bzcsywrfzcs = 1"); 91 flag = true; 92 } 93 } 94 95 if (map.get("dealStealingDischarge") != null 96 && !"".equals(map.get("dealStealingDischarge"))) { 97 if (flag) { 98 str.append(" or ill.deal_stealing_discharge = 1"); 99 } else { 100 str.append(" and (ill.deal_stealing_discharge = 1"); 101 flag = true; 102 } 103 } 104 105 if (map.get("dealCounterfeitMisrepresentData") != null 106 && !"".equals(map.get("dealCounterfeitMisrepresentData"))) { 107 if (flag) { 108 str.append(" or ill.deal_counterfeit_misrepresentData = 1"); 109 } else { 110 str.append(" and (ill.deal_counterfeit_misrepresentData = 1"); 111 flag = true; 112 } 113 } 114 115 if (map.get("dealOther") != null && !"".equals(map.get("dealOther"))) { 116 if (flag) { 117 str.append(" or ill.deal_other = 1"); 118 } else { 119 str.append(" and (ill.deal_other = 1"); 120 flag = true; 121 } 122 } 123 if (flag) { 124 str.append(")"); 125 } 126 String str_psids = ""; 127 if (isComplete == 0) { 128 psids = this.getPollEnterprises(startTime, endTime, newstr); 129 if (psids != null && psids.size() > 0) { 130 for (int i = 0; i < psids.size(); i++) { 131 if (i == psids.size() - 1) { 132 str_psids += "'" + psids.get(i) + "'"; 133 } else { 134 str_psids += "'" + psids.get(i) + "',"; 135 } 136 } 137 } 138 } 139 Session session = this.getSession(); 140 SQLQuery query = session 141 .createSQLQuery("{Call psCheck_ExcelData(?,?,?,?,?,?,?,?,?,?,?)}"); 142 query.setString(0, "".equals(startTime) ? null : startTime); 143 query.setString(1, "".equals(endTime) ? null : endTime); 144 query.setString(2, "".equals(newstr) ? null : newstr); 145 query.setString(3, "".equals(String.valueOf(hasProblem)) ? null 146 : String.valueOf(hasProblem)); 147 if(psname!=null && !"".equals(psname)){ 148 try { 149 psname = new String(psname.getBytes("iso8859-1"),"utf-8"); 150 query.setString(4, psname); 151 } catch (UnsupportedEncodingException e) { 152 e.printStackTrace(); 153 } 154 }else{ 155 query.setString(4, null); 156 } 157 query.setString(5, "".equals(deal) ? null : deal); 158 query.setString(6, "".equals(dealOther) ? null : dealOther); 159 query.setString(7, pstype.equals("0") ? null : pstype); 160 query.setString(8, "".equals(str.toString()) ? null : str.toString()); 161 query.setString(9, "3".equals(String.valueOf(isComplete)) ? null 162 : String.valueOf(isComplete)); 163 query.setString(10, "".equals(str_psids) ? null : str_psids); 164 List<Object> list = query.list(); 165 return list; 166 }
4.导出excel文件总结：
（1）所需jar包：
（2）exportExcel()方法：
这个方法使用与生成文件名和sheet名（即列表名），设置map是为了接收查询条件的。
其中调用了argMap()方法，是用来添加查询条件用于限制结果集的；
调用findAllObject()方法，是用来查找满足条件的对象的，将所有满足条件的对象放入list中；
然后调用了生成excel文件并触发下载的generateExcel()方法。
（3）argMap(Map<String,Object> map)方法：
此方法用于将页面中需要查询的条件及search_username和search_password放入map集合中封装起来。
（4）findAllObject(Map<String, Object> map)方法：
此方法通过封装好的查询条件：map中的值来拼装出hql语句，然后通过调用find() 方法来查找出，所有符合此条件查询的对象放入list集合中。
（5）generateExcel(list,excelName, sheetName)方法：
此方法用于创建excel文件，生成成功后触发下载。
