报表导出功能
jsp页面中书写的 java代码
参数 quarter 代表时间日期格式 20161 2016年第一季度
dptid 部门编号
1 <%@page contentType="text/html; charset=GBK"%> 2 <%@page import="java.net.URLEncoder"%> 3 <%@page import="com.projectcycle.process.document.attachments.FileInfoTools"%> 4 <%@page import="java.io.File"%> 5 <%@page import="java.io.FileOutputStream"%> 6 <%@page import="java.util.*"%> 7 <%@page import="org.apache.commons.lang.StringUtils"%> 8 <%@page import="com.projectcycle.business.utils.DBManager"%> 9 <%@page import="net.sf.json.*"%> 10 <%@page import="com.projectcycle.second.util.ToolsUtil"%> 11 <%@page import="com.projectcycle.web.action.UserInfo"%> 12 <%@page import="com.projectcycle.web.utils.SessionConstant"%> 13 <%@page import="com.projectcycle.business.utils.DateUtil1"%> 14 <%@page import="com.projectcycle.business.utils.Tools"%> 15 <%@page import="java.text.SimpleDateFormat"%> 16 <%@page import="java.text.DecimalFormat"%> 17 <%@page import="java.math.BigDecimal"%> 18 <%@page import="org.apache.poi.hssf.usermodel.*"%> 19 <%@page import="org.apache.poi.hssf.util.*"%> 20 <%! 21 public static void setContent(HSSFCell cell, HSSFCellStyle style, 22 String content) { 23 cell.setCellStyle(style); 24 cell.setCellValue(new HSSFRichTextString(content)); 25 26 } 27 28 public static void HcreateCell(HSSFSheet sheet, HSSFRow row, 29 HSSFWorkbook w, int c, String v, int start_r, int end_r, 30 int start_c, int end_c) { 31 try { 32 HSSFCell cell2 = null; 33 if (start_c != end_c||start_r!=end_r) { 34 sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c)); 35 } 36 HSSFCellStyle s=null; 37 if(start_r==0){ 38 s = getTitleStyle(w,0); 39 }else{ 40 s = getTitleStyle(w,1); 41 } 42 cell2 = row.createCell(c); 43 cell2.setCellStyle(s); 44 cell2.setCellValue(v); 45 } catch (Exception e) { 46 e.printStackTrace(); 47 } 48 } 49 50 public static HSSFCellStyle getTitleStyle(HSSFWorkbook wb,int i) { 51 52 HSSFCellStyle cellStyle1_2 = wb.createCellStyle();//标题样式 53 cellStyle1_2.setWrapText(true); 54 if(i==0){ 55 cellStyle1_2.setFillForegroundColor(HSSFColor.YELLOW.index); 56 }else{ 57 cellStyle1_2.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); 58 } 59 cellStyle1_2.setFillPattern(HSSFCellStyle.BORDER_THIN); 60 cellStyle1_2.setBorderBottom((short) 1); 61 cellStyle1_2.setBorderLeft((short) 1); 62 cellStyle1_2.setBorderRight((short) 1); 63 cellStyle1_2.setBorderTop((short) 1); 64 cellStyle1_2.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index); 65 cellStyle1_2.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index); 66 cellStyle1_2.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index); 67 cellStyle1_2.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index); 68 cellStyle1_2.setFont(getContentFont(wb)); 69 cellStyle1_2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 70 cellStyle1_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 71 cellStyle1_2.setLocked(true); 72 return cellStyle1_2; 73 } 74 75 public static void createCell(HSSFSheet sheet, HSSFRow row, 76 HSSFWorkbook w,HSSFCellStyle s, int c, Object v, int start_r, int end_r, 77 int start_c, int end_c) { 78 try { 79 80 HSSFCell cell2 = null; 81 if (start_c != end_c||start_r!=end_r) { 82 sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c)); 83 } 84 cell2 = row.createCell(c); 85 cell2.setCellStyle(s); 86 cell2.setCellValue(v.toString()); 87 } catch (Exception e) { 88 e.printStackTrace(); 89 } 90 } 91 92 public static void createCellForNum(HSSFSheet sheet, HSSFRow row, 93 HSSFWorkbook w,HSSFCellStyle s, int c, Double v, int start_r, int end_r, 94 int start_c, int end_c) { 95 try { 96 97 HSSFCell cell2 = null; 98 if (start_c != end_c||start_r!=end_r) { 99 sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c)); 100 } 101 HSSFDataFormat df = w.createDataFormat(); 102 s.setDataFormat(df.getFormat("##0")); 103 cell2 = row.createCell(c); 104 cell2.setCellStyle(s); 105 cell2.setCellValue(v); 106 } catch (Exception e) { 107 e.printStackTrace(); 108 } 109 } 110 111 112 public static HSSFCellStyle setSelfColor(HSSFWorkbook wb) { 113 114 String str = "#FFFFFF"; 115 // 处理把它转换成十六进制并放入一个数 116 int[] color = new int[3]; 117 color[0] = Integer.parseInt(str.substring(1, 3), 16); 118 color[1] = Integer.parseInt(str.substring(3, 5), 16); 119 color[2] = Integer.parseInt(str.substring(5, 7), 16); 120 // 自定义颜色 121 HSSFPalette palette = wb.getCustomPalette(); 122 palette.setColorAtIndex(HSSFColor.BLACK.index, (byte) color[0], 123 (byte) color[1], (byte) color[2]); 124 HSSFCellStyle cellStyle = wb.createCellStyle(); 125 cellStyle.setFillForegroundColor((short) 8); 126 return cellStyle; 127 } 128 129 public static HSSFCellStyle getCellStyle(HSSFWorkbook wb,int num) { 130 HSSFCellStyle cellStyle = wb.createCellStyle(); 131 cellStyle.setWrapText(true); 132 if(num==1){ 133 cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); 134 }else if(num==2){ 135 cellStyle.setFillForegroundColor(HSSFColor.RED.index); 136 }else if(num==3){ 137 cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); 138 }else if(num==4){ 139 cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); 140 } 141 cellStyle.setFillPattern(HSSFCellStyle.BORDER_THIN); 142 cellStyle.setBorderBottom((short) 1); 143 cellStyle.setBorderLeft((short) 1); 144 cellStyle.setBorderRight((short) 1); 145 cellStyle.setBorderTop((short) 1); 146 cellStyle.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index); 147 cellStyle.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index); 148 cellStyle.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index); 149 cellStyle.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index); 150 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 151 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 152 cellStyle.setFont(getContentFont(wb)); 153 cellStyle.setLocked(false); 154 return cellStyle; 155 } 156 public static HSSFCellStyle getCellStyleLock(HSSFWorkbook wb) { 157 HSSFCellStyle cellStyle = wb.createCellStyle(); 158 cellStyle.setWrapText(true); 159 cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); 160 cellStyle.setFillPattern(HSSFCellStyle.BORDER_THIN); 161 cellStyle.setBorderBottom((short) 1); 162 cellStyle.setBorderLeft((short) 1); 163 cellStyle.setBorderRight((short) 1); 164 cellStyle.setBorderTop((short) 1); 165 cellStyle.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index); 166 cellStyle.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index); 167 cellStyle.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index); 168 cellStyle.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index); 169 cellStyle.setFont(getContentFont(wb)); 170 cellStyle.setLocked(true); 171 return cellStyle; 172 } 173 public static HSSFFont getContentFont(HSSFWorkbook wb) { 174 HSSFFont fontStyle = wb.createFont(); 175 fontStyle.setFontName("微软雅黑"); 176 fontStyle.setFontHeightInPoints((short) 9); 177 fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 178 fontStyle.setColor(HSSFColor.GREY_80_PERCENT.index); 179 return fontStyle; 180 } 181 public Vector getCardInfo(DBManager dbm,String quarter,String departmentid) throws Exception{ 182 String sql = "select id,quarter from a_templet where quarter= "+quarter+" and department="+departmentid; 183 Vector vc = dbm.executeQueryVector3LowerCase(sql); 184 return vc; 185 } 186 public void createSheet(DBManager dbm,HSSFWorkbook w,Vector vt) throws Exception{ 187 Hashtable temp = (Hashtable)vt.get(0); 188 String id = (String)temp.get("id"); 189 String quarter = (String)temp.get("quarter"); 190 System.out.println("DumpExcel.jsp id"+id+"quarter:"+quarter); 191 //查询得到当前模板对应的页签 192 String sheets = "select a7 from a_templet_col_num where templet_id='"+id+"' group by a7 order by a7 "; 193 Vector sheetsVc = dbm.executeQueryVector3LowerCase(sheets.toString()); 194 if (sheetsVc != null && sheetsVc.size() > 0) { 195 for (int i = 0; i < sheetsVc.size(); i++) { 196 Hashtable ht = (Hashtable) sheetsVc.get(i); 197 System.out.println("导出的页签：ht"+ht.get("a7")); 198 HSSFSheet sheet = w.createSheet(ht.get("a7").toString()); 199 //写入数据 200 wirteSheet(dbm,sheet,w,id,quarter,ht.get("a7").toString()); 201 202 } 203 } 204 } 205 //创建表头 206 public void wirteSheet(DBManager dbm,HSSFSheet sheet, HSSFWorkbook w,String templetid,String time,String paraname) throws Exception{ 207 int ji=Integer.parseInt(time.substring(4,5)); 208 int year=Integer.parseInt(time.substring(0,4)); 209 //当前日期 210 Calendar now = Calendar.getInstance(); 211 //int month=(now.get(Calendar.MONTH))+1; 212 int yue=0; 213 if(ji==3){ 214 ji=3; 215 yue=7; 216 }else if(ji==2){ 217 ji=2; 218 yue=4; 219 }else if(ji==1){ 220 ji=1; 221 yue=1; 222 }else if(ji==4){ 223 ji=4; 224 yue=10; 225 } 226 //结束 227 List<String> lists=new ArrayList<String>(); 228 List<Map<String,Object>> listcount=new ArrayList<Map<String,Object>>(); 229 List<Map<String, Object>> listOne = new ArrayList<Map<String, Object>>(); 230 List<Map<String, Object>> listTwe = new ArrayList<Map<String, Object>>(); 231 //同样的参数 要分成2个数组 232 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); 233 List listvalues=new ArrayList(); 234 List<Map<String,Object>> listvaluesed = new ArrayList<Map<String,Object>>(); 235 List<Map<String,Object>> colorList = new ArrayList<Map<String,Object>>(); 236 237 System.out.println("id"+templetid+"quarter:"+time+"---paraname:"+paraname); 238 //第一列数据 239 String oneStr = "select t.group_name, count(1) as col,min(order_number)as order_number from a_templet_col_info t where tempid = '"+templetid+"' and flag=1 group by group_name order by order_number"; 240 Vector one = dbm.executeQueryVector3LowerCase(oneStr.toString()); 241 if (one != null && one.size() > 0) { 242 for (int i = 0; i < one.size()-1; i++) { 243 Hashtable ht = (Hashtable) one.get(i); 244 245 if(ht.get("group_name")=="数据收集方法"||"数据收集方法".equals(ht.get("group_name"))){ 246 int av=Integer.parseInt(ht.get("col")+"")-1; 247 Hashtable hts=new Hashtable(); 248 hts.put("group_name", "数据收集方法"); 249 hts.put("col", av+""); 250 listOne.add(hts); 251 }else{ 252 listOne.add(ht); 253 } 254 } 255 } 256 //第二列数据 257 String tweStr = "select show_name,col_name from a_templet_col_info where tempid='"+templetid+"' and flag=1 and col_name not in('a19','a20','a21','a22','a5','a18') order by order_number"; 258 Vector twe = dbm.executeQueryVector3LowerCase(tweStr.toString()); 259 if (twe != null && twe.size() > 0) { 260 for (int i = 0; i < twe.size(); i++) { 261 Hashtable ht = (Hashtable) twe.get(i); 262 listTwe.add(ht); 263 } 264 } 265 //配置显示的固定列数 266 String thrStr = "select count(t.group_name) as col from a_templet_col_info t where tempid = '"+templetid+"' and flag=1 and col_name not in('a19','a20','a21','a22','a5','a18') order by t.group_name"; 267 int col_num = dbm.executeQueryInt(thrStr); 268 String coun=""; 269 String kong=""; 270 int extendCol=0;//统计动态列列数 271 //得到相同数据有几条 后面就是跨行使用的数据 272 //如果当前不是第一季的月份 则去除半年和一年收集一次的数据 273 //if(ji==4){ 274 coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' group by a8"; 275 kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' and t2.status=1"); 276 /*}else if(ji==2){ 277 //一年直接忽略 278 coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and a18 in ('每月一次','每季度一次','每半年一次') group by a8"; 279 kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t2.status=1 and a18 in ('每月一次','每季度一次','每半年一次') "); 280 }else{ 281 //半年和一年直接忽略 282 coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and a18 in ('每月一次','每季度一次') group by a8"; 283 kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t2.status=1 and a18 in ('每月一次','每季度一次')"); 284 }*/ 285 if(kong.contains("每月一次")){ 286 Hashtable ht = new Hashtable(); 287 ht.put("show_name",year+"年"+yue+"月"); 288 ht.put("col_name","yue"); 289 listTwe.add(ht); 290 ht = new Hashtable(); 291 ht.put("show_name",year+"年"+(yue+1)+"月"); 292 ht.put("col_name","oyue"); 293 listTwe.add(ht); 294 ht = new Hashtable(); 295 ht.put("show_name",year+"年"+(yue+2)+"月"); 296 ht.put("col_name","tyue"); 297 listTwe.add(ht); 298 extendCol+=3; 299 } 300 if(kong.contains("每季度一次")){ 301 Hashtable ht = new Hashtable(); 302 ht.put("show_name",year+"年第"+ji+"季度"); 303 ht.put("col_name","jd"); 304 listTwe.add(ht); 305 extendCol+=1; 306 } 307 if(kong.contains("每半年一次")){ 308 Hashtable ht = new Hashtable(); 309 String sname=ji==2?(year+"年上半年"):(year+"年下半年"); 310 ht.put("show_name",sname); 311 ht.put("col_name","bn"); 312 listTwe.add(ht); 313 extendCol+=1; 314 } 315 if(kong.contains("每年一次")){ 316 Hashtable ht = new Hashtable(); 317 ht.put("show_name",year+"年"); 318 ht.put("col_name","mn"); 319 listTwe.add(ht); 320 extendCol+=1; 321 } 322 if(listOne.size()>0){ 323 Hashtable h=new Hashtable(); 324 h.put("group_name","数据收集"); 325 h.put("col",extendCol+""); 326 listOne.add(h); 327 } 328 col_num+=extendCol; 329 //System.out.println("col_num:"+col_num+"extendCol:"+extendCol); 330 Vector c = dbm.executeQueryVector3LowerCase(coun.toString()); 331 if(c!=null && c.size()>0){ 332 for (int i=0;i<c.size();i++){ 333 Map ht = (Map)c.get(i); 334 listcount.add(ht); 335 //System.out.println(ht.get("counts")); 336 } 337 } 338 339 String pmoxqrw = "select t1.* from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' order by t1.a8,t1.a5"; 340 Vector vcr = dbm.executeQueryVector3LowerCase(pmoxqrw.toString()); 341 if(vcr!=null && vcr.size()>0){ 342 for (int i=0;i<vcr.size();i++){ 343 Hashtable ht = (Hashtable)vcr.get(i); 344 listvalues.add(ht.get("id")); 345 //把相同的编号和数据插入到ht中 346 for(int jj=0;jj<listcount.size();jj++){ 347 if((listcount.get(jj).get("a8")).equals(ht.get("a8"))){ 348 ht.put("counts", listcount.get(jj).get("counts")); 349 } 350 } 351 352 //如果当前不是第一季的月份 则去除半年和一年收集一次的数据 353 //if(ji==4){ 354 if(("每季度一次").equals(ht.get("a18"))){ 355 String jidu=year+"年第"+ji+"季度"; 356 String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 357 if(jd==null||jd==""){ 358 jd=""; 359 } 360 ht.put("jd", jd); 361 //背景颜色 362 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 363 Map<String,Object> map=new HashMap<String,Object>(); 364 map.put("id", ht.get("id")); 365 map.put("jd", jd); 366 if(backcolor!=null&&backcolor!=""){ 367 map.put("backcolor", backcolor); 368 } 369 colorList.add(map); 370 }else if(("每月一次").equals(ht.get("a18"))){ 371 String y=year+"年"+yue+"月"; 372 String yuess=""; 373 try{ 374 yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 375 if(yuess==null){ 376 yuess=""; 377 } 378 }catch(Exception e){ 379 yuess=""; 380 } 381 ht.put("yue", yuess); 382 //背景颜色 383 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 384 Map<String,Object> map=new HashMap<String,Object>(); 385 map.put("id", ht.get("id")); 386 map.put("yue", yuess); 387 if(backcolor!=null&&backcolor!=""){ 388 map.put("backcolor", backcolor); 389 } 390 colorList.add(map); 391 392 String yy=year+"年"+(yue+1)+"月"; 393 String oyuess=""; 394 try{ 395 oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 396 if(oyuess==null){ 397 oyuess=""; 398 } 399 }catch(Exception e){ 400 oyuess=""; 401 } 402 ht.put("oyue", oyuess); 403 //背景颜色 404 String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 405 Map<String,Object> maps=new HashMap<String,Object>(); 406 maps.put("id", ht.get("id")); 407 maps.put("oyue", oyuess); 408 if(backcolors!=null&&backcolors!=""){ 409 maps.put("backcolor", backcolors); 410 } 411 colorList.add(maps); 412 413 String yyy=year+"年"+(yue+2)+"月"; 414 String tyuess=""; 415 try{ 416 tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 417 if(tyuess==null){ 418 tyuess=""; 419 } 420 }catch(Exception e){ 421 tyuess=""; 422 } 423 ht.put("tyue", tyuess); 424 //背景颜色 425 String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 426 Map<String,Object> mapss=new HashMap<String,Object>(); 427 mapss.put("id", ht.get("id")); 428 mapss.put("tyue", tyuess); 429 if(backcolorss!=null&&backcolorss!=""){ 430 mapss.put("backcolor", backcolorss); 431 } 432 colorList.add(mapss); 433 }else if(("每半年一次").equals(ht.get("a18"))){ 434 String sname=ji==2?(year+"年上半年"):(year+"年下半年"); 435 String bannian=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'"); 436 if(bannian==null||bannian==""){ 437 bannian=""; 438 } 439 ht.put("bn", bannian); 440 //背景颜色 441 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'"); 442 Map<String,Object> map=new HashMap<String,Object>(); 443 map.put("id", ht.get("id")); 444 map.put("bn", bannian); 445 if(backcolor!=null&&backcolor!=""){ 446 map.put("backcolor", backcolor); 447 } 448 colorList.add(map); 449 }else if(("每年一次").equals(ht.get("a18"))){ 450 String snian=year+"年"; 451 String mn=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+snian+"'"); 452 if(mn==null||mn==""){ 453 mn=""; 454 } 455 ht.put("mn", mn); 456 //背景颜色 457 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+snian+"'"); 458 Map<String,Object> map=new HashMap<String,Object>(); 459 map.put("id", ht.get("id")); 460 map.put("mn", mn); 461 if(backcolor!=null&&backcolor!=""){ 462 map.put("backcolor", backcolor); 463 } 464 colorList.add(map); 465 } 466 list.add(ht); 467 lists.add(""+ht.get("a18")); 468 /*}else if(ji==2){ 469 //半年和一年直接忽略 470 if("每月一次".equals(ht.get("a18"))||"每季度一次".equals(ht.get("a18"))||"每半年一次".equals(ht.get("a18"))){ 471 if(("每季度一次").equals(ht.get("a18"))){ 472 String jidu=year+"年第"+ji+"季度"; 473 String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 474 if(jd==null||jd==""){ 475 jd=""; 476 } 477 ht.put("jd", jd); 478 //背景颜色 479 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 480 Map<String,Object> map=new HashMap<String,Object>(); 481 map.put("id", ht.get("id")); 482 map.put("jd", jd); 483 if(backcolor!=null&&backcolor!=""){ 484 map.put("backcolor", backcolor); 485 } 486 colorList.add(map); 487 }else if(("每月一次").equals(ht.get("a18"))){ 488 String y=year+"年"+yue+"月"; 489 String yuess=""; 490 try{ 491 yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 492 if(yuess==null){ 493 yuess=""; 494 } 495 }catch(Exception e){ 496 yuess=""; 497 } 498 ht.put("yue", yuess); 499 //背景颜色 500 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 501 Map<String,Object> map=new HashMap<String,Object>(); 502 map.put("id", ht.get("id")); 503 map.put("yue", yuess); 504 if(backcolor!=null&&backcolor!=""){ 505 map.put("backcolor", backcolor); 506 } 507 colorList.add(map); 508 509 String yy=year+"年"+(yue+1)+"月"; 510 String oyuess=""; 511 try{ 512 oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 513 if(oyuess==null){ 514 oyuess=""; 515 } 516 }catch(Exception e){ 517 oyuess=""; 518 } 519 ht.put("oyue", oyuess); 520 //背景颜色 521 String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 522 Map<String,Object> maps=new HashMap<String,Object>(); 523 maps.put("id", ht.get("id")); 524 maps.put("oyue", oyuess); 525 if(backcolors!=null&&backcolors!=""){ 526 maps.put("backcolor", backcolors); 527 } 528 colorList.add(maps); 529 530 String yyy=year+"年"+(yue+2)+"月"; 531 String tyuess=""; 532 try{ 533 tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 534 if(tyuess==null){ 535 tyuess=""; 536 } 537 }catch(Exception e){ 538 tyuess=""; 539 } 540 ht.put("tyue", tyuess); 541 //背景颜色 542 String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 543 Map<String,Object> mapss=new HashMap<String,Object>(); 544 mapss.put("id", ht.get("id")); 545 mapss.put("tyue", tyuess); 546 if(backcolorss!=null&&backcolorss!=""){ 547 mapss.put("backcolor", backcolorss); 548 } 549 colorList.add(mapss); 550 }else if(("每半年一次").equals(ht.get("a18"))){ 551 String sname=ji==2?(year+"年上半年"):(year+"年下半年"); 552 String bannian=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'"); 553 if(bannian==null||bannian==""){ 554 bannian=""; 555 } 556 ht.put("bn", bannian); 557 //背景颜色 558 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'"); 559 Map<String,Object> map=new HashMap<String,Object>(); 560 map.put("id", ht.get("id")); 561 map.put("bn", bannian); 562 if(backcolor!=null&&backcolor!=""){ 563 map.put("backcolor", backcolor); 564 } 565 colorList.add(map); 566 567 } 568 list.add(ht); 569 lists.add(""+ht.get("a18")); 570 } 571 }else{ 572 //一年直接忽略 573 if("每月一次".equals(ht.get("a18"))||"每季度一次".equals(ht.get("a18"))){ 574 if(("每季度一次").equals(ht.get("a18"))){ 575 String jidu=year+"年第"+ji+"季度"; 576 String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 577 if(jd==null||jd=="mn"){ 578 jd=""; 579 } 580 ht.put("jd", jd); 581 //背景颜色 582 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'"); 583 Map<String,Object> map=new HashMap<String,Object>(); 584 map.put("id", ht.get("id")); 585 map.put("jd", jd); 586 if(backcolor!=null&&backcolor!=""){ 587 map.put("backcolor", backcolor); 588 } 589 colorList.add(map); 590 }else if(("每月一次").equals(ht.get("a18"))){ 591 String y=year+"年"+yue+"月"; 592 String yuess=""; 593 try{ 594 yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 595 if(yuess==null){ 596 yuess=""; 597 } 598 }catch(Exception e){ 599 yuess=""; 600 } 601 ht.put("yue", yuess); 602 //背景颜色 603 String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'"); 604 Map<String,Object> map=new HashMap<String,Object>(); 605 map.put("id", ht.get("id")); 606 map.put("yue", yuess); 607 if(backcolor!=null&&backcolor!=""){ 608 map.put("backcolor", backcolor); 609 } 610 colorList.add(map); 611 612 String yy=year+"年"+(yue+1)+"月"; 613 String oyuess=""; 614 try{ 615 oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 616 if(oyuess==null){ 617 oyuess=""; 618 } 619 }catch(Exception e){ 620 oyuess=""; 621 } 622 ht.put("oyue", oyuess); 623 //背景颜色 624 String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'"); 625 Map<String,Object> maps=new HashMap<String,Object>(); 626 maps.put("id", ht.get("id")); 627 maps.put("oyue", oyuess); 628 if(backcolors!=null&&backcolors!=""){ 629 maps.put("backcolor", backcolors); 630 } 631 colorList.add(maps); 632 633 String yyy=year+"年"+(yue+2)+"月"; 634 String tyuess=""; 635 try{ 636 tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 637 if(tyuess==null){ 638 tyuess=""; 639 } 640 }catch(Exception e){ 641 tyuess=""; 642 } 643 ht.put("tyue", tyuess); 644 //背景颜色 645 String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'"); 646 Map<String,Object> mapss=new HashMap<String,Object>(); 647 mapss.put("id", ht.get("id")); 648 mapss.put("tyue", tyuess); 649 if(backcolorss!=null&&backcolorss!=""){ 650 mapss.put("backcolor", backcolorss); 651 } 652 colorList.add(mapss); 653 } 654 list.add(ht); 655 lists.add(""+ht.get("a18")); 656 } 657 }*/ 658 } 659 } 660 661 //查询前台添加的数据 662 for(int i=0;i<listvalues.size();i++){ 663 String listStr = "select templet_num_id,write_time,write_value from a_templet_col_values where templet_num_id="+listvalues.get(i); 664 Vector listv = dbm.executeQueryVector3LowerCase(listStr.toString()); 665 if (listv != null && listv.size() > 0) { 666 for (int j = 0; j < listv.size(); j++) { 667 Hashtable ht = (Hashtable) listv.get(j); 668 //System.out.println("ht----"+ht.get("templet_num_id")+"==="+ht.get("write_time")+"==="+ht.get("write_value")); 669 if(("").equals(ht.get("write_value"))||ht.get("write_value")==null){ 670 ht.put("write_value","录入数据"); 671 } 672 listvaluesed.add(ht); 673 } 674 } 675 } 676 //创建表头 677 HSSFRow row0 = sheet.createRow(0); 678 HSSFRow row1 = sheet.createRow(1); 679 for(int j=0;j<col_num;j++){ 680 HSSFCellStyle s = getTitleStyle(w,1); 681 HSSFCell cell0 = row0.createCell(j); 682 HSSFCell cell1 = row1.createCell(j); 683 cell0.setCellStyle(s); 684 cell1.setCellStyle(s); 685 } 686 int startCol=0; 687 for(int i=0;i<listOne.size();i++){ 688 Hashtable ht = (Hashtable)listOne.get(i); 689 String group_name = (String)ht.get("group_name"); 690 int colspan =Integer.parseInt((String)ht.get("col")); 691 HcreateCell(sheet, row0, w, startCol, group_name, 0, 0, startCol, (startCol+colspan-1)); 692 startCol+=colspan; 693 } 694 startCol=0; 695 for(int i=0;i<listTwe.size();i++){ 696 Hashtable ht = (Hashtable)listTwe.get(i); 697 String show_name = (String)ht.get("show_name"); 698 //System.out.println("i==="+i); 699 if(i==2||i==4||i==10||i==12){ 700 sheet.setColumnWidth((short)i, (short)5000);//设置列宽 701 } 702 HcreateCell(sheet, row1, w, startCol, show_name, 1, 1, startCol, startCol); 703 startCol++; 704 } 705 //写入数据 706 HSSFRow row=null; 707 HSSFCellStyle s = getCellStyle(w,1); 708 HSSFCellStyle r = getCellStyle(w,2); 709 HSSFCellStyle y = getCellStyle(w,3); 710 HSSFCellStyle g = getCellStyle(w,4); 711 int rowspan=2; 712 for(int j=0;j<lists.size();j++){ 713 row =sheet.createRow(j+2); 714 for(int col=0;col<col_num;col++){ 715 HSSFCell cell = row.createCell(col); 716 cell.setCellStyle(s); 717 } 718 } 719 for(int j=0;j<lists.size();j++){ 720 int nums=0; 721 for(int k=0;k<j;k++){ 722 if(list.get(k).get("a8").equals(list.get(j).get("a8"))){ 723 nums+=1; 724 } 725 } 726 for(int k=0;k<listTwe.size();k++){ 727 if(",a1,a2,a3,a4,a8,a10,a6,a9,".indexOf(","+(String)listTwe.get(k).get("col_name")+",")>-1){ 728 if(nums==0){ 729 //System.out.println("-------------"+list.get(j).get(listTwe.get(k).get("col_name"))+"---"+listTwe.get(k).get("col_name")); 730 if(listTwe.get(k).get("col_name")=="a1"||listTwe.get(k).get("col_name").equals("a1")){ 731 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k); 732 }else if(listTwe.get(k).get("col_name")=="a2"||listTwe.get(k).get("col_name").equals("a2")){ 733 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k); 734 }else if(listTwe.get(k).get("col_name")=="a4"||listTwe.get(k).get("col_name").equals("a4")){ 735 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k); 736 }else{ 737 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k); 738 } 739 740 } 741 }else{ 742 //System.out.println("-------------"+list.get(j).get(listTwe.get(k).get("col_name"))+"---"+listTwe.get(k).get("col_name")); 743 if(listTwe.get(k).get("col_name").equals("mn")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 744 for(int h=0;h<colorList.size();h++){ 745 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("mn")==colorList.get(h).get("mn")){ 746 //System.out.println("输出一年"); 747 if(colorList.get(h).get("backcolor")!=null){ 748 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 749 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 750 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 751 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 752 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 753 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 754 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 755 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 756 } 757 }else{ 758 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 759 } 760 } 761 } 762 }else if(listTwe.get(k).get("col_name").equals("jd")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 763 for(int h=0;h<colorList.size();h++){ 764 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("jd")==colorList.get(h).get("jd")){ 765 //System.out.println("输出季度"); 766 if(colorList.get(h).get("backcolor")!=null){ 767 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 768 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 769 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 770 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 771 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 772 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 773 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 774 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 775 } 776 }else{ 777 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 778 } 779 } 780 } 781 }else if(listTwe.get(k).get("col_name").equals("yue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 782 for(int h=0;h<colorList.size();h++){ 783 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("yue")==colorList.get(h).get("yue")){ 784 //System.out.println("输出月"); 785 if(colorList.get(h).get("backcolor")!=null){ 786 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 787 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 788 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 789 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 790 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 791 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 792 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 793 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 794 } 795 }else{ 796 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 797 } 798 } 799 } 800 }else if(listTwe.get(k).get("col_name").equals("oyue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 801 for(int h=0;h<colorList.size();h++){ 802 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("oyue")==colorList.get(h).get("oyue")){ 803 //System.out.println("输出月"); 804 if(colorList.get(h).get("backcolor")!=null){ 805 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 806 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 807 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 808 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 809 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 810 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 811 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 812 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 813 } 814 }else{ 815 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 816 } 817 } 818 } 819 820 }else if(listTwe.get(k).get("col_name").equals("tyue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 821 for(int h=0;h<colorList.size();h++){ 822 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("tyue")==colorList.get(h).get("tyue")){ 823 //System.out.println("输出月"); 824 if(colorList.get(h).get("backcolor")!=null){ 825 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 826 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 827 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 828 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 829 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 830 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 831 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 832 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 833 } 834 }else{ 835 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 836 } 837 } 838 } 839 }else if(listTwe.get(k).get("col_name").equals("bn")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){ 840 for(int h=0;h<colorList.size();h++){ 841 if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("bn")==colorList.get(h).get("bn")){ 842 //System.out.println("输出半年"); 843 if(colorList.get(h).get("backcolor")!=null){ 844 if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){ 845 createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 846 }else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){ 847 createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 848 }else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){ 849 createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 850 }else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){ 851 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 852 } 853 }else{ 854 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 855 } 856 } 857 } 858 859 860 }else{ 861 createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k); 862 } 863 864 865 866 867 } 868 } 869 rowspan++; 870 } 871 872 } 873 %> 874 <% 875 UserInfo userInfo=(UserInfo)request.getSession().getAttribute("userInfo"); 876 String userid=userInfo.getUserID(); 877 String quarter = request.getParameter("quarter"); 878 String dptid = request.getParameter("dptid"); 879 /***********************EXCEL导出部分**************************/ 880 String str3 = FileInfoTools.getSystemFilePath() 881 + "/documentTemp/"; 882 File file = new File(str3); 883 if (!file.exists() && !file.isDirectory()) { 884 file.mkdir(); 885 } 886 String names = new SimpleDateFormat("yyyyMMddhhmmssSSS").format(new Date()); 887 String ourl = str3 + names + "_"+userid+".xls"; 888 FileOutputStream fos = null; 889 String fileName = ""; 890 try { 891 HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄 892 fileName = ""; 893 DBManager dbm = new DBManager(); 894 try{ 895 dbm.newConnection(); 896 String str = "select departmentname from dpt_department where departmentid='"+dptid+"'"; //获取唯一id 897 String dptName = dbm.executeQueryString(str); 898 fileName=dptName+"部门信息科技关键风险指标清单"+"_"+userid+"_"+names+".xls"; 899 //创建sheet页,并写入内容 900 createSheet(dbm,wb,getCardInfo(dbm,quarter,dptid)); 901 }catch(Exception e){ 902 e.printStackTrace(); 903 }finally{ 904 dbm.closeConnection(); 905 } 906 File ff = new File(ourl); 907 if (ff.exists()) { 908 ff.delete(); 909 } 910 fos = new FileOutputStream(ourl); 911 wb.write(fos); 912 fos.close(); 913 String u = "/project/system/fileAction.do?filePath=" + URLEncoder.encode(ourl) 914 + "&fileName=" + URLEncoder.encode(fileName); 915 response.sendRedirect(u); 916 } catch (Exception e) { 917 e.printStackTrace(); 918 } 919 %>
<%@page contentType="text/html; charset=GBK"%><%@page import="java.net.URLEncoder"%><%@page import="com.projectcycle.process.document.attachments.FileInfoTools"%><%@page import="java.io.File"%><%@page import="java.io.FileOutputStream"%><%@page import="java.util.*"%><%@page import="org.apache.commons.lang.StringUtils"%><%@page import="com.projectcycle.business.utils.DBManager"%><%@page import="net.sf.json.*"%><%@page import="com.projectcycle.second.util.ToolsUtil"%><%@page import="com.projectcycle.web.action.UserInfo"%><%@page import="com.projectcycle.web.utils.SessionConstant"%><%@page import="com.projectcycle.business.utils.DateUtil1"%><%@page import="com.projectcycle.business.utils.Tools"%><%@page import="java.text.SimpleDateFormat"%><%@page import="java.text.DecimalFormat"%><%@page import="java.math.BigDecimal"%><%@page import="org.apache.poi.hssf.usermodel.*"%><%@page import="org.apache.poi.hssf.util.*"%><%! public static void setContent(HSSFCell cell, HSSFCellStyle style,String content) {cell.setCellStyle(style);cell.setCellValue(new HSSFRichTextString(content));
}
public static void HcreateCell(HSSFSheet sheet, HSSFRow row,HSSFWorkbook w, int c, String v, int start_r, int end_r,int start_c, int end_c) {try {HSSFCell cell2 = null;if (start_c != end_c||start_r!=end_r) {sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c));}HSSFCellStyle s=null;if(start_r==0){s = getTitleStyle(w,0);}else{s = getTitleStyle(w,1);}cell2 = row.createCell(c);cell2.setCellStyle(s);cell2.setCellValue(v);} catch (Exception e) {e.printStackTrace();}}
public static HSSFCellStyle getTitleStyle(HSSFWorkbook wb,int i) {HSSFCellStyle cellStyle1_2 = wb.createCellStyle();//标题样式cellStyle1_2.setWrapText(true);if(i==0){cellStyle1_2.setFillForegroundColor(HSSFColor.YELLOW.index);}else{cellStyle1_2.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);}cellStyle1_2.setFillPattern(HSSFCellStyle.BORDER_THIN);cellStyle1_2.setBorderBottom((short) 1);cellStyle1_2.setBorderLeft((short) 1);cellStyle1_2.setBorderRight((short) 1);cellStyle1_2.setBorderTop((short) 1);cellStyle1_2.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle1_2.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle1_2.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle1_2.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle1_2.setFont(getContentFont(wb));cellStyle1_2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 cellStyle1_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中cellStyle1_2.setLocked(true);return cellStyle1_2;}public static void createCell(HSSFSheet sheet, HSSFRow row,HSSFWorkbook w,HSSFCellStyle s, int c, Object v, int start_r, int end_r,int start_c, int end_c) {try {
HSSFCell cell2 = null;if (start_c != end_c||start_r!=end_r) {sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c));}cell2 = row.createCell(c);cell2.setCellStyle(s);cell2.setCellValue(v.toString());} catch (Exception e) {e.printStackTrace();}}public static void createCellForNum(HSSFSheet sheet, HSSFRow row,HSSFWorkbook w,HSSFCellStyle s, int c, Double v, int start_r, int end_r,int start_c, int end_c) {try {
HSSFCell cell2 = null;if (start_c != end_c||start_r!=end_r) {sheet.addMergedRegion(new CellRangeAddress(start_r, end_r,start_c, end_c));}HSSFDataFormat df = w.createDataFormat();s.setDataFormat(df.getFormat("##0"));cell2 = row.createCell(c);cell2.setCellStyle(s);cell2.setCellValue(v);} catch (Exception e) {e.printStackTrace();}}
public static HSSFCellStyle setSelfColor(HSSFWorkbook wb) {
String str = "#FFFFFF";// 处理把它转换成十六进制并放入一个数int[] color = new int[3];color[0] = Integer.parseInt(str.substring(1, 3), 16);color[1] = Integer.parseInt(str.substring(3, 5), 16);color[2] = Integer.parseInt(str.substring(5, 7), 16);// 自定义颜色HSSFPalette palette = wb.getCustomPalette();palette.setColorAtIndex(HSSFColor.BLACK.index, (byte) color[0],(byte) color[1], (byte) color[2]);HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setFillForegroundColor((short) 8);return cellStyle;}
public static HSSFCellStyle getCellStyle(HSSFWorkbook wb,int num) {HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setWrapText(true);if(num==1){cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);}else if(num==2){cellStyle.setFillForegroundColor(HSSFColor.RED.index);}else if(num==3){cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);}else if(num==4){cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);}cellStyle.setFillPattern(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderBottom((short) 1);cellStyle.setBorderLeft((short) 1);cellStyle.setBorderRight((short) 1);cellStyle.setBorderTop((short) 1);cellStyle.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中cellStyle.setFont(getContentFont(wb));cellStyle.setLocked(false);return cellStyle;}public static HSSFCellStyle getCellStyleLock(HSSFWorkbook wb) {HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setWrapText(true);cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);cellStyle.setFillPattern(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderBottom((short) 1);cellStyle.setBorderLeft((short) 1);cellStyle.setBorderRight((short) 1);cellStyle.setBorderTop((short) 1);cellStyle.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index);cellStyle.setFont(getContentFont(wb));cellStyle.setLocked(true);return cellStyle;}public static HSSFFont getContentFont(HSSFWorkbook wb) {HSSFFont fontStyle = wb.createFont();fontStyle.setFontName("微软雅黑");fontStyle.setFontHeightInPoints((short) 9);fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);fontStyle.setColor(HSSFColor.GREY_80_PERCENT.index);return fontStyle;}public Vector getCardInfo(DBManager dbm,String quarter,String departmentid) throws Exception{String sql = "select id,quarter from a_templet where quarter= "+quarter+" and department="+departmentid;Vector vc = dbm.executeQueryVector3LowerCase(sql);return vc;}public void createSheet(DBManager dbm,HSSFWorkbook w,Vector vt) throws Exception{Hashtable temp = (Hashtable)vt.get(0);String id = (String)temp.get("id");String quarter = (String)temp.get("quarter");System.out.println("DumpExcel.jsp id"+id+"quarter:"+quarter);//查询得到当前模板对应的页签String sheets = "select a7 from a_templet_col_num where templet_id='"+id+"' group by a7 order by a7 ";Vector sheetsVc = dbm.executeQueryVector3LowerCase(sheets.toString());if (sheetsVc != null && sheetsVc.size() > 0) {for (int i = 0; i < sheetsVc.size(); i++) {Hashtable ht = (Hashtable) sheetsVc.get(i);System.out.println("导出的页签：ht"+ht.get("a7"));HSSFSheet sheet = w.createSheet(ht.get("a7").toString());//写入数据wirteSheet(dbm,sheet,w,id,quarter,ht.get("a7").toString());}}}//创建表头public void wirteSheet(DBManager dbm,HSSFSheet sheet, HSSFWorkbook w,String templetid,String time,String paraname) throws Exception{int ji=Integer.parseInt(time.substring(4,5)); int year=Integer.parseInt(time.substring(0,4));//当前日期 Calendar now = Calendar.getInstance(); //int month=(now.get(Calendar.MONTH))+1; int yue=0; if(ji==3){ ji=3; yue=7; }else if(ji==2){ ji=2; yue=4; }else if(ji==1){ ji=1; yue=1; }else if(ji==4){ ji=4; yue=10; } //结束List<String> lists=new ArrayList<String>();List<Map<String,Object>> listcount=new ArrayList<Map<String,Object>>();List<Map<String, Object>> listOne = new ArrayList<Map<String, Object>>();List<Map<String, Object>> listTwe = new ArrayList<Map<String, Object>>(); //同样的参数 要分成2个数组List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();List listvalues=new ArrayList();List<Map<String,Object>> listvaluesed = new ArrayList<Map<String,Object>>();List<Map<String,Object>> colorList = new ArrayList<Map<String,Object>>();
System.out.println("id"+templetid+"quarter:"+time+"---paraname:"+paraname);//第一列数据String oneStr = "select t.group_name, count(1) as col,min(order_number)as order_number from a_templet_col_info t where tempid = '"+templetid+"' and flag=1 group by group_name order by order_number";Vector one = dbm.executeQueryVector3LowerCase(oneStr.toString());if (one != null && one.size() > 0) {for (int i = 0; i < one.size()-1; i++) {Hashtable ht = (Hashtable) one.get(i);if(ht.get("group_name")=="数据收集方法"||"数据收集方法".equals(ht.get("group_name"))){int av=Integer.parseInt(ht.get("col")+"")-1;Hashtable hts=new Hashtable();hts.put("group_name", "数据收集方法");hts.put("col", av+"");listOne.add(hts);}else{listOne.add(ht);}}}//第二列数据String tweStr = "select show_name,col_name from a_templet_col_info where tempid='"+templetid+"' and flag=1 and col_name not in('a19','a20','a21','a22','a5','a18') order by order_number";Vector twe = dbm.executeQueryVector3LowerCase(tweStr.toString());if (twe != null && twe.size() > 0) {for (int i = 0; i < twe.size(); i++) {Hashtable ht = (Hashtable) twe.get(i);listTwe.add(ht);}}//配置显示的固定列数String thrStr = "select count(t.group_name) as col from a_templet_col_info t where tempid = '"+templetid+"' and flag=1 and col_name not in('a19','a20','a21','a22','a5','a18') order by t.group_name";int col_num = dbm.executeQueryInt(thrStr);String coun="";String kong="";int extendCol=0;//统计动态列列数//得到相同数据有几条 后面就是跨行使用的数据//如果当前不是第一季的月份 则去除半年和一年收集一次的数据//if(ji==4){coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' group by a8";kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' and t2.status=1");/*}else if(ji==2){//一年直接忽略coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and a18 in ('每月一次','每季度一次','每半年一次') group by a8";kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t2.status=1 and a18 in ('每月一次','每季度一次','每半年一次') ");}else{//半年和一年直接忽略coun = "select count(*)as counts,a8 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and a18 in ('每月一次','每季度一次') group by a8";kong=dbm.executeQueryString("select wm_concat(a18) a188 from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t2.status=1 and a18 in ('每月一次','每季度一次')");}*/if(kong.contains("每月一次")){Hashtable ht = new Hashtable();ht.put("show_name",year+"年"+yue+"月");ht.put("col_name","yue");listTwe.add(ht);ht = new Hashtable();ht.put("show_name",year+"年"+(yue+1)+"月");ht.put("col_name","oyue");listTwe.add(ht);ht = new Hashtable();ht.put("show_name",year+"年"+(yue+2)+"月");ht.put("col_name","tyue");listTwe.add(ht);extendCol+=3;}if(kong.contains("每季度一次")){Hashtable ht = new Hashtable();ht.put("show_name",year+"年第"+ji+"季度");ht.put("col_name","jd");listTwe.add(ht);extendCol+=1;}if(kong.contains("每半年一次")){Hashtable ht = new Hashtable();String sname=ji==2?(year+"年上半年"):(year+"年下半年");ht.put("show_name",sname);ht.put("col_name","bn");listTwe.add(ht);extendCol+=1;}if(kong.contains("每年一次")){Hashtable ht = new Hashtable();ht.put("show_name",year+"年");ht.put("col_name","mn");listTwe.add(ht);extendCol+=1;}if(listOne.size()>0){Hashtable h=new Hashtable();h.put("group_name","数据收集");h.put("col",extendCol+"");listOne.add(h);}col_num+=extendCol;//System.out.println("col_num:"+col_num+"extendCol:"+extendCol);Vector c = dbm.executeQueryVector3LowerCase(coun.toString());if(c!=null && c.size()>0){for (int i=0;i<c.size();i++){Map ht = (Map)c.get(i);listcount.add(ht);//System.out.println(ht.get("counts"));}}String pmoxqrw = "select t1.* from a_templet_col_num t1 left join a_templet t2 on t1.templet_id=t2.id where t2.id='"+templetid+"' and t1.a7='"+paraname+"' order by t1.a8,t1.a5";Vector vcr = dbm.executeQueryVector3LowerCase(pmoxqrw.toString());if(vcr!=null && vcr.size()>0){for (int i=0;i<vcr.size();i++){Hashtable ht = (Hashtable)vcr.get(i);listvalues.add(ht.get("id"));//把相同的编号和数据插入到ht中for(int jj=0;jj<listcount.size();jj++){if((listcount.get(jj).get("a8")).equals(ht.get("a8"))){ht.put("counts", listcount.get(jj).get("counts"));}}//如果当前不是第一季的月份 则去除半年和一年收集一次的数据//if(ji==4){if(("每季度一次").equals(ht.get("a18"))){String jidu=year+"年第"+ji+"季度";String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");if(jd==null||jd==""){jd="";}ht.put("jd", jd);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("jd", jd);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}else if(("每月一次").equals(ht.get("a18"))){String y=year+"年"+yue+"月";String yuess="";try{yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");if(yuess==null){yuess="";}}catch(Exception e){yuess="";}ht.put("yue", yuess);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("yue", yuess);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);String yy=year+"年"+(yue+1)+"月";String oyuess="";try{oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");if(oyuess==null){oyuess="";}}catch(Exception e){ oyuess="";}ht.put("oyue", oyuess);//背景颜色String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");Map<String,Object> maps=new HashMap<String,Object>();maps.put("id", ht.get("id"));maps.put("oyue", oyuess);if(backcolors!=null&&backcolors!=""){maps.put("backcolor", backcolors);}colorList.add(maps);String yyy=year+"年"+(yue+2)+"月";String tyuess="";try{tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");if(tyuess==null){tyuess="";}}catch(Exception e){tyuess="";}ht.put("tyue", tyuess);//背景颜色String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");Map<String,Object> mapss=new HashMap<String,Object>();mapss.put("id", ht.get("id"));mapss.put("tyue", tyuess);if(backcolorss!=null&&backcolorss!=""){mapss.put("backcolor", backcolorss);}colorList.add(mapss);}else if(("每半年一次").equals(ht.get("a18"))){String sname=ji==2?(year+"年上半年"):(year+"年下半年");String bannian=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'");if(bannian==null||bannian==""){bannian="";}ht.put("bn", bannian);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("bn", bannian);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}else if(("每年一次").equals(ht.get("a18"))){String snian=year+"年";String mn=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+snian+"'");if(mn==null||mn==""){mn="";}ht.put("mn", mn);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+snian+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("mn", mn);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}list.add(ht);lists.add(""+ht.get("a18"));/*}else if(ji==2){//半年和一年直接忽略if("每月一次".equals(ht.get("a18"))||"每季度一次".equals(ht.get("a18"))||"每半年一次".equals(ht.get("a18"))){if(("每季度一次").equals(ht.get("a18"))){String jidu=year+"年第"+ji+"季度";String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");if(jd==null||jd==""){jd="";}ht.put("jd", jd);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("jd", jd);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}else if(("每月一次").equals(ht.get("a18"))){String y=year+"年"+yue+"月";String yuess="";try{yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");if(yuess==null){yuess="";}}catch(Exception e){yuess="";}ht.put("yue", yuess);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("yue", yuess);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);String yy=year+"年"+(yue+1)+"月";String oyuess="";try{oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");if(oyuess==null){oyuess="";}}catch(Exception e){ oyuess="";}ht.put("oyue", oyuess);//背景颜色String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");Map<String,Object> maps=new HashMap<String,Object>();maps.put("id", ht.get("id"));maps.put("oyue", oyuess);if(backcolors!=null&&backcolors!=""){maps.put("backcolor", backcolors);}colorList.add(maps);String yyy=year+"年"+(yue+2)+"月";String tyuess="";try{tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");if(tyuess==null){tyuess="";}}catch(Exception e){tyuess="";}ht.put("tyue", tyuess);//背景颜色String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");Map<String,Object> mapss=new HashMap<String,Object>();mapss.put("id", ht.get("id"));mapss.put("tyue", tyuess);if(backcolorss!=null&&backcolorss!=""){mapss.put("backcolor", backcolorss);}colorList.add(mapss);}else if(("每半年一次").equals(ht.get("a18"))){String sname=ji==2?(year+"年上半年"):(year+"年下半年");String bannian=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'");if(bannian==null||bannian==""){bannian="";}ht.put("bn", bannian);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+sname+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("bn", bannian);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}list.add(ht);lists.add(""+ht.get("a18"));}}else{//一年直接忽略if("每月一次".equals(ht.get("a18"))||"每季度一次".equals(ht.get("a18"))){if(("每季度一次").equals(ht.get("a18"))){String jidu=year+"年第"+ji+"季度";String jd=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");if(jd==null||jd=="mn"){jd="";}ht.put("jd", jd);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+jidu+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("jd", jd);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);}else if(("每月一次").equals(ht.get("a18"))){String y=year+"年"+yue+"月";String yuess="";try{yuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");if(yuess==null){yuess="";}}catch(Exception e){yuess="";}ht.put("yue", yuess);//背景颜色String backcolor=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+y+"'");Map<String,Object> map=new HashMap<String,Object>();map.put("id", ht.get("id"));map.put("yue", yuess);if(backcolor!=null&&backcolor!=""){map.put("backcolor", backcolor);}colorList.add(map);String yy=year+"年"+(yue+1)+"月";String oyuess="";try{oyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");if(oyuess==null){oyuess="";}}catch(Exception e){ oyuess="";}ht.put("oyue", oyuess);//背景颜色String backcolors=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yy+"'");Map<String,Object> maps=new HashMap<String,Object>();maps.put("id", ht.get("id"));maps.put("oyue", oyuess);if(backcolors!=null&&backcolors!=""){maps.put("backcolor", backcolors);}colorList.add(maps);String yyy=year+"年"+(yue+2)+"月";String tyuess="";try{tyuess=dbm.executeQueryString("select write_value from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");if(tyuess==null){tyuess="";}}catch(Exception e){tyuess="";}ht.put("tyue", tyuess);//背景颜色String backcolorss=dbm.executeQueryString("select back_color from a_templet_col_values where templet_num_id='"+ht.get("id")+"' and write_time='"+yyy+"'");Map<String,Object> mapss=new HashMap<String,Object>();mapss.put("id", ht.get("id"));mapss.put("tyue", tyuess);if(backcolorss!=null&&backcolorss!=""){mapss.put("backcolor", backcolorss);}colorList.add(mapss);}list.add(ht);lists.add(""+ht.get("a18"));}}*/}}//查询前台添加的数据for(int i=0;i<listvalues.size();i++){String listStr = "select templet_num_id,write_time,write_value from a_templet_col_values where templet_num_id="+listvalues.get(i);Vector listv = dbm.executeQueryVector3LowerCase(listStr.toString());if (listv != null && listv.size() > 0) {for (int j = 0; j < listv.size(); j++) {Hashtable ht = (Hashtable) listv.get(j);//System.out.println("ht----"+ht.get("templet_num_id")+"==="+ht.get("write_time")+"==="+ht.get("write_value"));if(("").equals(ht.get("write_value"))||ht.get("write_value")==null){ht.put("write_value","录入数据");}listvaluesed.add(ht);}}}//创建表头HSSFRow row0 = sheet.createRow(0);HSSFRow row1 = sheet.createRow(1);for(int j=0;j<col_num;j++){HSSFCellStyle s = getTitleStyle(w,1);HSSFCell cell0 = row0.createCell(j);HSSFCell cell1 = row1.createCell(j);cell0.setCellStyle(s);cell1.setCellStyle(s);}int startCol=0;for(int i=0;i<listOne.size();i++){Hashtable ht = (Hashtable)listOne.get(i);String group_name = (String)ht.get("group_name");int colspan =Integer.parseInt((String)ht.get("col"));HcreateCell(sheet, row0, w, startCol, group_name, 0, 0, startCol, (startCol+colspan-1));startCol+=colspan;}startCol=0;for(int i=0;i<listTwe.size();i++){Hashtable ht = (Hashtable)listTwe.get(i);String show_name = (String)ht.get("show_name");//System.out.println("i==="+i);if(i==2||i==4||i==10||i==12){sheet.setColumnWidth((short)i, (short)5000);//设置列宽}HcreateCell(sheet, row1, w, startCol, show_name, 1, 1, startCol, startCol);startCol++;}//写入数据HSSFRow row=null;HSSFCellStyle s = getCellStyle(w,1);HSSFCellStyle r = getCellStyle(w,2);HSSFCellStyle y = getCellStyle(w,3);HSSFCellStyle g = getCellStyle(w,4);int rowspan=2;for(int j=0;j<lists.size();j++){row =sheet.createRow(j+2);for(int col=0;col<col_num;col++){HSSFCell cell = row.createCell(col);cell.setCellStyle(s);}}for(int j=0;j<lists.size();j++){int nums=0;for(int k=0;k<j;k++){if(list.get(k).get("a8").equals(list.get(j).get("a8"))){ nums+=1; }}for(int k=0;k<listTwe.size();k++){if(",a1,a2,a3,a4,a8,a10,a6,a9,".indexOf(","+(String)listTwe.get(k).get("col_name")+",")>-1){if(nums==0){//System.out.println("-------------"+list.get(j).get(listTwe.get(k).get("col_name"))+"---"+listTwe.get(k).get("col_name"));if(listTwe.get(k).get("col_name")=="a1"||listTwe.get(k).get("col_name").equals("a1")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k);}else if(listTwe.get(k).get("col_name")=="a2"||listTwe.get(k).get("col_name").equals("a2")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k);}else if(listTwe.get(k).get("col_name")=="a4"||listTwe.get(k).get("col_name").equals("a4")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k);}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan+Integer.parseInt((String)list.get(j).get("counts"))-1, k, k);}}}else{//System.out.println("-------------"+list.get(j).get(listTwe.get(k).get("col_name"))+"---"+listTwe.get(k).get("col_name"));if(listTwe.get(k).get("col_name").equals("mn")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("mn")==colorList.get(h).get("mn")){//System.out.println("输出一年");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else if(listTwe.get(k).get("col_name").equals("jd")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("jd")==colorList.get(h).get("jd")){//System.out.println("输出季度");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else if(listTwe.get(k).get("col_name").equals("yue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("yue")==colorList.get(h).get("yue")){//System.out.println("输出月");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else if(listTwe.get(k).get("col_name").equals("oyue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("oyue")==colorList.get(h).get("oyue")){//System.out.println("输出月");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else if(listTwe.get(k).get("col_name").equals("tyue")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("tyue")==colorList.get(h).get("tyue")){//System.out.println("输出月");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else if(listTwe.get(k).get("col_name").equals("bn")&&list.get(j).get(listTwe.get(k).get("col_name"))!=null){for(int h=0;h<colorList.size();h++){if(list.get(j).get("id")==colorList.get(h).get("id")&&list.get(j).get("bn")==colorList.get(h).get("bn")){//System.out.println("输出半年");if(colorList.get(h).get("backcolor")!=null){if(colorList.get(h).get("backcolor")=="red"||colorList.get(h).get("backcolor").equals("red")){createCell(sheet, sheet.getRow(rowspan), w, r, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="yellow"||colorList.get(h).get("backcolor").equals("yellow")){createCell(sheet, sheet.getRow(rowspan), w, y, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor")=="green"||colorList.get(h).get("backcolor").equals("green")){createCell(sheet, sheet.getRow(rowspan), w, g, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}else if(colorList.get(h).get("backcolor").equals("")||colorList.get(h).get("backcolor")==null){createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}}else{createCell(sheet, sheet.getRow(rowspan), w, s, k, list.get(j).get(listTwe.get(k).get("col_name"))==null?"":list.get(j).get(listTwe.get(k).get("col_name")), rowspan, rowspan, k, k);}}}rowspan++;}}%><%UserInfo userInfo=(UserInfo)request.getSession().getAttribute("userInfo");String userid=userInfo.getUserID();String quarter = request.getParameter("quarter");String dptid = request.getParameter("dptid");/***********************EXCEL导出部分**************************/String str3 = FileInfoTools.getSystemFilePath()+ "/documentTemp/";File file = new File(str3);if (!file.exists() && !file.isDirectory()) {file.mkdir();}String names = new SimpleDateFormat("yyyyMMddhhmmssSSS").format(new Date());String ourl = str3 + names + "_"+userid+".xls";FileOutputStream fos = null;String fileName = "";try {HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄fileName = "";DBManager dbm = new DBManager();try{dbm.newConnection();String str = "select departmentname from dpt_department where departmentid='"+dptid+"'"; //获取唯一idString dptName = dbm.executeQueryString(str);fileName=dptName+"部门信息科技关键风险指标清单"+"_"+userid+"_"+names+".xls";//创建sheet页,并写入内容createSheet(dbm,wb,getCardInfo(dbm,quarter,dptid));}catch(Exception e){e.printStackTrace();}finally{dbm.closeConnection();}File ff = new File(ourl);if (ff.exists()) {ff.delete();}fos = new FileOutputStream(ourl);wb.write(fos);fos.close();String u = "/project/system/fileAction.do?filePath=" + URLEncoder.encode(ourl)+ "&fileName=" + URLEncoder.encode(fileName);response.sendRedirect(u);} catch (Exception e) {e.printStackTrace();}%>
