1 package cn.sh.bzt.kwj.action; 2 3 import java.io.IOException; 4 import java.io.OutputStream; 5 import java.text.SimpleDateFormat; 6 import java.util.ArrayList; 7 import java.util.Calendar; 8 import java.util.Date; 9 import java.util.List; 10 11 import javax.annotation.Resource; 12 13 import org.apache.poi.hssf.usermodel.HSSFCell; 14 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 15 import org.apache.poi.hssf.usermodel.HSSFFont; 16 import org.apache.poi.hssf.usermodel.HSSFRow; 17 import org.apache.poi.hssf.usermodel.HSSFSheet; 18 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 19 20 import cn.sh.bzt.base.action.BaseAction; 21 import cn.sh.bzt.base.page.Pager; 22 import cn.sh.bzt.base.page.PagerHelp; 23 import cn.sh.bzt.kwj.pojo.TEnterprise; 24 import cn.sh.bzt.kwj.pojo.TOrgnization; 25 import cn.sh.bzt.kwj.pojo.TUser; 26 import cn.sh.bzt.kwj.service.IUserService; 27 import cn.sh.bzt.kwj.service.TotalFormService; 28 29 /** 30 * 统计报表--行政统计 31 * @author 姚智韧 32 * 33 */ 34 @SuppressWarnings("serial") 35 public class TotalFormAction extends BaseAction { 36 37 // private Log log = LogFactory.getLog(TEnterpriseIdAction.class);// 日志类 38 private List<TEnterprise> list = null; 39 private TEnterprise enterprise = new TEnterprise(); 40 41 private String projectCode = "33"; 42 private String licenseName = "口岸卫生许可证核发 "; 43 private String childNodeCode = "3300"; 44 private String childNodeName = "无"; 45 public OutputStream out; 46 47 public TEnterprise getEnterprise() { 48 return enterprise; 49 } 50 51 public void setEnterprise(TEnterprise enterprise) { 52 this.enterprise = enterprise; 53 } 54 55 private TotalFormService totalFormService; 56 57 public TotalFormService getTotalFormService() { 58 return totalFormService; 59 } 60 61 @Resource(name = "totalFormServiceImpl") 62 public void setTotalFormService(TotalFormService totalFormService) { 63 this.totalFormService = totalFormService; 64 } 65 66 private IUserService userService; 67 68 public IUserService getUserService() { 69 return userService; 70 } 71 72 @Resource(name = "userServiceImpl") 73 public void setUserService(IUserService userService) { 74 this.userService = userService; 75 } 76 77 /** 78 * 查询行政统计数据 79 * 80 * @return 81 */ 82 public String findAllTotalForm() { 83 try { 84 TUser user = (TUser) request.getSession().getAttribute( 85 "USER_IN_SESSION"); 86 request.setAttribute("userLoginName", user.getUserLoginName()); 87 88 TOrgnization toi = new TOrgnization(); 89 Integer i = user.getTOrgnization().getOrgId(); 90 if (i != null) { 91 toi = userService.getAllOrgList(i); 92 } 93 request.setAttribute("orgName", toi.getOrgName()); 94 95 request.getSession().setAttribute("companyName", ""); 96 request.getSession().setAttribute("stDate", ""); 97 request.getSession().setAttribute("endDate",""); 98 // 总行数 99 int totalRows = totalFormService.getTotalFormCount(enterprise); 100 System.out.println("总行数totalRows----->" + totalRows); 101 102 Pager pager = PagerHelp.getPager(request, totalRows); 103 pager.setUrl("tjbb/findAllTotalForm.action"); 104 105 list = new ArrayList<TEnterprise>(); 106 list = ListUtils(enterprise, pager); 107 System.out.println("list----->" + list.get(0)); 108 request.setAttribute("ALL_Total_LIST", list); 109 request.setAttribute("PAGE", pager); 110 return SUCCESS; 111 } catch (Exception e) { 112 request.setAttribute("ERROR_CAUSE", "数据库操作失败"); 113 return ERROR; 114 } 115 } 116 117 /** 118 * 按条件查询 119 * 120 * @return 121 */ 122 public String findByCondition() { 123 try { 124 TUser user = (TUser) request.getSession().getAttribute( 125 "USER_IN_SESSION"); 126 request.setAttribute("userLoginName", user.getUserLoginName()); 127 TOrgnization toi = new TOrgnization(); 128 Integer i = user.getTOrgnization().getOrgId(); 129 if (i != null) { 130 toi = userService.getAllOrgList(i); 131 request.setAttribute("orgName", toi.getOrgName()); 132 } 133 134 String filingdate1 = request.getParameter("enterprise.filingdate1"); 135 System.out.println("filingdate1------->" + filingdate1); 136 137 String filingdate2 = request.getParameter("enterprise.filingdate2"); 138 System.out.println("filingdate2------->" + filingdate2); 139 140 String companyName = request.getParameter("enterprise.companyName"); 141 142 enterprise.setFilingdate1(convertFormat(filingdate1)); 143 enterprise.setFilingdate2(convertFormat(filingdate2)); 144 145 request.getSession().setAttribute("companyName", companyName); 146 request.getSession().setAttribute("stDate", filingdate1); 147 request.getSession().setAttribute("endDate",filingdate2); 148 149 // 总行数 150 int totalRows = totalFormService.getTotalFormCount(enterprise); 151 System.out.println("总行数totalRows----->" + totalRows); 152 153 Pager pager = PagerHelp.getPager(request, totalRows); 154 155 list = new ArrayList<TEnterprise>(); 156 list = ListUtils(enterprise, pager); 157 request.setAttribute("ALL_Total_LIST", list); 158 request.setAttribute("PAGE", pager); 159 System.out.println("list---->" + list.get(0)); 160 return SUCCESS; 161 } catch (Exception e) { 162 request.setAttribute("ERROR_CAUSE", "数据库操作失败"); 163 e.printStackTrace(); 164 return ERROR; 165 } 166 167 } 168 169 /** 170 * 导出数据到Excel 171 * @param model 172 * @throws IOException 173 * @throws Exception 174 */ 175 public String exportDataToExcel() throws IOException { 176 try { 177 response.setCharacterEncoding("UTF-8"); 178 String filename = "统计报表-行政统计.xls"; 179 HSSFWorkbook wb = new HSSFWorkbook(); 180 response.setContentType("application/x-download"); 181 try { 182 response.setHeader("Content-Disposition", "attachment;filename=" 183 + new String(filename.getBytes("gbk"), "iso-8859-1")); 184 } catch (Exception e1) { 185 e1.printStackTrace(); 186 } 187 HSSFSheet sheet = wb.createSheet("行政统计一"); 188 // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short 189 HSSFRow row = sheet.createRow((int) 0); 190 // 第四步,创建单元格,并设置值表头 设置表头居中 191 HSSFFont font = wb.createFont(); 192 font.setFontName("宋体"); 193 font.setFontHeightInPoints((short) 16); 194 195 HSSFCellStyle style = wb.createCellStyle(); 196 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 197 198 HSSFCell cell = row.createCell((short) 0); 199 cell.setCellValue("许可项目代码"); 200 cell.setCellStyle(style); 201 cell = row.createCell((short) 1); 202 cell.setCellValue("许可项目代码"); 203 cell.setCellStyle(style); 204 cell = row.createCell((short) 2); 205 cell.setCellValue("子项代码"); 206 cell.setCellStyle(style); 207 cell = row.createCell((short) 3); 208 cell.setCellValue("子项名称"); 209 cell.setCellStyle(style); 210 cell = row.createCell((short) 4); 211 cell.setCellValue("许可证书编号"); 212 cell.setCellStyle(style); 213 cell = row.createCell((short) 5); 214 cell.setCellValue("许可项目及范围"); 215 216 cell = row.createCell((short) 6); 217 cell.setCellValue("发证日期"); 218 cell.setCellStyle(style); 219 cell = row.createCell((short) 7); 220 cell.setCellValue("有效期至"); 221 cell.setCellStyle(style); 222 cell = row.createCell((short) 8); 223 cell.setCellValue("生产地址"); 224 cell.setCellStyle(style); 225 cell = row.createCell((short) 9); 226 cell.setCellValue("发证机构 "); 227 cell.setCellStyle(style); 228 cell = row.createCell((short) 10); 229 cell.setCellValue("发证机构代码"); 230 cell.setCellStyle(style); 231 cell = row.createCell((short) 11); 232 cell.setCellValue("被许可机构"); 233 cell.setCellStyle(style); 234 cell = row.createCell((short) 12); 235 cell.setCellValue("被许可机构代码(证件号码) "); 236 cell.setCellStyle(style); 237 cell = row.createCell((short) 13); 238 cell.setCellValue("被许可机构行政区划 "); 239 cell.setCellStyle(style); 240 241 String filingdate1 = (String) request.getSession().getAttribute("stDate"); 242 System.out.println("filingdate1------->" + filingdate1); 243 244 String filingdate2 = (String) request.getSession().getAttribute("endDate"); 245 System.out.println("filingdate2------->" + filingdate2); 246 247 String companyName = (String) request.getSession().getAttribute("companyName"); 248 System.out.println("companyName------->" + companyName); 249 250 enterprise.setFilingdate1(convertFormat(filingdate1)); 251 enterprise.setFilingdate2(convertFormat(filingdate2)); 252 enterprise.setCompanyName(companyName); 253 list = new ArrayList<TEnterprise>(); 254 255 list = totalFormService.findExportData(enterprise); 256 257 for (int i = 0; i < list.size(); i++) 258 { 259 row = sheet.createRow((int) i + 1); 260 TEnterprise enterprise = (TEnterprise) list.get(i); 261 String managescope = enterprise.getApprovalmanagescope(); 262 String managescope1 = null; 263 if(managescope!=null){ 264 managescope1 = managescope.substring(1, managescope.length()-1); 265 }else{ 266 managescope1 = managescope; 267 } 268 // 第四步,创建单元格,并设置值 269 row.createCell((short) 0).setCellValue(projectCode); //许可项目代码 270 row.createCell((short) 1).setCellValue(licenseName); //许可项目代码 271 row.createCell((short) 2).setCellValue(childNodeCode); //子项代码 272 row.createCell((short) 3).setCellValue(childNodeName);//子项名称 273 row.createCell((short) 4).setCellValue(enterprise.getLicenseCode()); //许可证书编号 274 row.createCell((short) 5).setCellValue(managescope1); //许可项目及范围 275 row.createCell((short) 6).setCellValue(enterprise.getJdTime()); //发证日期 276 row.createCell((short) 7).setCellValue(enterprise.getInDate()); //有效期至 277 row.createCell((short) 8).setCellValue(enterprise.getBusinessAddress()); //生产地址 278 row.createCell((short) 9).setCellValue(enterprise.getEmbranchment());//发证机构 279 row.createCell((short) 10).setCellValue(enterprise.getOrgCode()); //发证机构代码 280 row.createCell((short) 11).setCellValue(enterprise.getCompanyName()); //被许可机构 281 row.createCell((short) 12).setCellValue(enterprise.getLegalPersonCard()); //被许可机构代码 (证件号码) 282 row.createCell((short) 13).setCellValue(enterprise.getAdminZoning());//被许可机构行政区划 283 } 284 System.out.println("导出表格的数据大小---->"+list.size()); 285 request.setAttribute("ALL_Total_LIST", list); 286 out = response.getOutputStream(); 287 wb.write(out); 288 if(((filingdate1!=null)||(filingdate2!=null)||(companyName!=null))&&((filingdate1!=null)&&(filingdate2!=null))){ 289 out.close(); 290 return "EXPORT"; 291 }else{ 292 out.close(); 293 return SUCCESS; 294 } 295 296 } catch (Exception e) { 297 e.printStackTrace(); 298 return ERROR; 299 } 300 301 } 302 /** 303 * 获取经处理后的list 304 * @return 305 */ 306 public List<TEnterprise> ListUtils(TEnterprise enterprise, Pager pager){ 307 try { 308 list = new ArrayList<TEnterprise>(); 309 310 // 得到行政统计List 311 List<TEnterprise> tList = totalFormService.findAll(enterprise, 312 pager); 313 for (int j = 0; j < tList.size(); j++) { 314 // System.out.println("遍历list--------->"); 315 enterprise = tList.get(j); 316 String jdTime = enterprise.getJdTime(); 317 // System.out.println("jdTime------>" + jdTime); 318 String inDate = enterprise.getInDate(); 319 // System.out.println("inDate------>" + inDate); 320 if (inDate != null) { 321 String jzTime2 = convertDate(jdTime, new Integer(inDate)); 322 enterprise.setInDate(convertFormat(jzTime2)); 323 } else { 324 enterprise.setInDate(""); 325 } 326 String managescope = enterprise.getApprovalmanagescope(); 327 if(managescope!=null){ 328 String managescope1 = managescope.substring(1, managescope.length()-1); 329 enterprise.setApprovalmanagescope(managescope1); 330 } 331 list.add(enterprise); 332 } 333 return list; 334 }catch (Exception e) { 335 e.printStackTrace(); 336 return null; 337 } 338 339 } 340 341 /** 342 * 日期转换 将yyyy-MM-dd格式日期转换成yyyy年MM月dd日 343 * 344 * @param jdTime 345 * @return 346 * @throws Exception 347 */ 348 public String convertFormat(String jdTime) throws Exception { 349 // Calendar objCalendar = Calendar.getInstance(); 350 Date objDate = new SimpleDateFormat("yyyy-MM-dd").parse(jdTime); 351 352 SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日"); 353 354 String jdTime2 = sdf.format(objDate); 355 System.out.println("jdTime2---->" + jdTime2); 356 return jdTime2; 357 } 358 359 /** 360 * 日期转换函数 将数据库中日期格式为“yyyy年MM月日”加上天数得到"yyyy-MM-dd"格式数据 361 * @param jdTime 发证日期 362 * @param inDate 有效期 363 * @return 364 * @throws Exception 365 */ 366 public String convertDate(String jdTime, int inDate) throws Exception { 367 System.out.println("进入日期转换函数----->" + 1111111); 368 369 Calendar objCalendar = Calendar.getInstance(); 370 Date objDate = new SimpleDateFormat("yyyy年MM月dd日").parse(jdTime); 371 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 372 373 SimpleDateFormat sdf0 = new SimpleDateFormat("yyyy"); 374 SimpleDateFormat sdf1 = new SimpleDateFormat("MM"); 375 SimpleDateFormat sdf2 = new SimpleDateFormat("dd"); 376 int year = Integer.parseInt(sdf0.format(objDate)); 377 int month = Integer.parseInt(sdf1.format(objDate)); 378 int date = Integer.parseInt(sdf2.format(objDate)); 379 // System.out.println(Integer.valueOf(inDate).intValue()); 380 objCalendar.set(year, month - 1, date); 381 objCalendar.add(Calendar.DATE, inDate); 382 System.out.println("Calendar.DATE---------->" + Calendar.DATE); 383 objDate = objCalendar.getTime(); 384 System.out.println("objDate---->" + objDate); 385 String jzTime = sdf.format(objDate); 386 System.out.println("截止日期--->" + jzTime); 387 return jzTime; 388 } 389 390 }
代码仅供参考,在CSDN下有个大牛实现数据导出时,可以实现图片等的导出,还可以既本地调用和在WEB服务器端用Servlet的调用。详情可参考(http://blog.csdn.net/lenotang/article/details/2823230)