后台数据download成excel的方法(controller/action)
jsp页面端
1 2 3 | <a href= "/portal/server/importExec" title= "Data Download" > <img src= "${pageContext.request.contextPath}/style/images/excel6.jpg" width=20px height=20px style= "padding-top:15px" /> </a> |
controller端处理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | @RequestMapping (value = "importExec" , method = RequestMethod.GET) @ResponseBody public void importExec(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException { String fname = "serverlist" ; response.reset(); // 清空输出流 response.setHeader( "Content-disposition" , "attachment; filename=" + fname + ".xls" ); // 设定输出文件头 response.setContentType( "application/msexcel" ); //EXCEL格式 Microsoft excel //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFFont f = workbook.createFont(); // f.setColor(HSSFColor.RED.index); f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗 style.setFont(f); style.setFillForegroundColor(HSSFColor.LIME.index); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建sheet页 HSSFSheet sheet = workbook.createSheet( "Server Info." ); //创建单元格 HSSFRow row = sheet.createRow( 0 ); HSSFCell c0 = row.createCell( 0 ); c0.setCellValue( new HSSFRichTextString( "No" )); c0.setCellStyle(style); HSSFCell c1 = row.createCell( 1 ); c1.setCellValue( new HSSFRichTextString( "IP" )); c1.setCellStyle(style); HSSFCell c2 = row.createCell( 2 ); c2.setCellValue( new HSSFRichTextString( "Server Name" )); c2.setCellStyle(style); HSSFCell c3 = row.createCell( 3 ); c3.setCellValue( new HSSFRichTextString( "Server Usage" )); c3.setCellStyle(style); HSSFCell c4 = row.createCell( 4 ); c4.setCellValue( new HSSFRichTextString( "CPU" )); c4.setCellStyle(style); HSSFCell c5 = row.createCell( 6 ); c5.setCellValue( new HSSFRichTextString( "Memory" )); c5.setCellStyle(style); HSSFCell c6 = row.createCell( 8 ); c6.setCellValue( new HSSFRichTextString( "HDD" )); c6.setCellStyle(style); HSSFCell c7 = row.createCell( 11 ); c7.setCellValue( new HSSFRichTextString( "OS Version" )); c7.setCellStyle(style); HSSFCell c8 = row.createCell( 12 ); c8.setCellValue( new HSSFRichTextString( "Manager" )); c8.setCellStyle(style); HSSFRow row1 = sheet.createRow( 1 ); HSSFCell c9 = row1.createCell( 4 ); c9.setCellValue( new HSSFRichTextString( "Count" )); c9.setCellStyle(style); HSSFCell c10 = row1.createCell( 5 ); c10.setCellValue( new HSSFRichTextString( "Core Num." )); c10.setCellStyle(style); HSSFCell c11 = row1.createCell( 6 ); c11.setCellValue( new HSSFRichTextString( "Count" )); c11.setCellStyle(style); HSSFCell c12 = row1.createCell( 7 ); c12.setCellValue( new HSSFRichTextString( "Size (GB)" )); c12.setCellStyle(style); HSSFCell c13 = row1.createCell( 8 ); c13.setCellValue( new HSSFRichTextString( "Count" )); c13.setCellStyle(style); HSSFCell c14 = row1.createCell( 9 ); c14.setCellValue( new HSSFRichTextString( "Type" )); c14.setCellStyle(style); HSSFCell c15 = row1.createCell( 10 ); c15.setCellValue( new HSSFRichTextString( "Size (GB)" )); c15.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 0 , ( short ) 0 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 1 , ( short ) 1 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 2 , ( short ) 2 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 3 , ( short ) 3 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 0 ,( short ) 4 , ( short ) 5 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 0 ,( short ) 6 , ( short ) 7 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 0 ,( short ) 8 , ( short ) 10 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 11 , ( short ) 11 )); sheet.addMergedRegion( new CellRangeAddress( 0 , 1 ,( short ) 12 , ( short ) 12 )); List<Server> serverList = serverService.findServerList( "" ); for ( int i= 0 ;i<serverList.size();i++){ row=sheet.createRow(( int )i+ 2 ); Server server = (Server)serverList.get(i); row.createCell(( short ) 0 ).setCellValue( new HSSFRichTextString(i+ 1 + "" )); row.createCell(( short ) 1 ).setCellValue( new HSSFRichTextString(server.getIp())); row.createCell(( short ) 2 ).setCellValue( new HSSFRichTextString(server.getName())); row.createCell(( short ) 3 ).setCellValue( new HSSFRichTextString(server.getUseFor())); row.createCell(( short ) 4 ).setCellValue( new HSSFRichTextString(String.valueOf(server.getCpuCount()))); row.createCell(( short ) 5 ).setCellValue( new HSSFRichTextString(server.getCpuNumber()+ "" )); row.createCell(( short ) 6 ).setCellValue( new HSSFRichTextString(server.getMemCount()+ "" )); row.createCell(( short ) 7 ).setCellValue( new HSSFRichTextString(server.getMemSize())); row.createCell(( short ) 8 ).setCellValue( new HSSFRichTextString(server.getHddCount()+ "" )); row.createCell(( short ) 9 ).setCellValue( new HSSFRichTextString(server.getHddType())); row.createCell(( short ) 10 ).setCellValue( new HSSFRichTextString(server.getHddSize())); row.createCell(( short ) 11 ).setCellValue( new HSSFRichTextString(server.getOsVersion())); row.createCell(( short ) 12 ).setCellValue( new HSSFRichTextString(server.getManager())); } try { workbook.write(response.getOutputStream()); } catch (Exception e){ e.printStackTrace(); } } |
action的处理方法:
jsp端
1 2 3 | <a href= "DownDefectServlet?projectname=<%=request.getParameter(" projectname ")%>&item=<%=request.getParameter(" item ")%>" title= "Data Download" > <img src= "${pageContext.request.contextPath}/images/excel6.jpg" width=20px height=20px style= "padding-top:15px" /> </a> |
web.xml加入
1 2 3 4 5 6 7 8 | <servlet> <servlet-name>DownDefectServlet</servlet-name> <servlet- class >net.nw.servlet.DownDefectServlet</servlet- class > </servlet> <servlet-mapping> <servlet-name>DownDefectServlet</servlet-name> <url-pattern>/DownDefectServlet</url-pattern> </servlet-mapping> |
后台servlet处理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //int currpage = Integer.parseInt(request.getParameter("currpage")==null?"1":request.getParameter("currpage")); String projectname=request.getParameter( "projectname" ); String item=request.getParameter( "item" ); String where= null ; int total= 0 ; if ( "" .equals(item)){ where= "where PROJECTNAME like '%" +projectname+ "%' AND STATUS != 'PLM_Deleted' and STATUS != 'Not_Related' and PLMFLAG='Y'" ; } else { //Opened where= "where PROJECTNAME like '%" +projectname+ "%' AND STATUS != 'PLM_Deleted' AND STATUS != 'Closed' AND STATUS != 'Resolved' and STATUS != 'Not_Related' and PLMFLAG='Y'" ; } response = ServletActionContext.getResponse(); String fname = "defectlist" ; response.reset(); // 清空输出流 response.setHeader( "Content-disposition" , "attachment; filename=" + fname + ".xls" ); // 设定输出文件头 response.setContentType( "application/msexcel" ); //EXCEL格式 Microsoft excel //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFFont f = workbook.createFont(); f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗 style.setFont(f); style.setFillForegroundColor(HSSFColor.LIME.index); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建sheet页 HSSFSheet sheet = workbook.createSheet( "Defect List" ); //创建单元格 HSSFRow row = sheet.createRow( 0 ); HSSFCell c0 = row.createCell( 0 ); c0.setCellValue( new HSSFRichTextString( "ID" )); c0.setCellStyle(style); HSSFCell c1 = row.createCell( 1 ); c1.setCellValue( new HSSFRichTextString( "Headline" )); c1.setCellStyle(style); HSSFCell c2 = row.createCell( 2 ); c2.setCellValue( new HSSFRichTextString( "Priority" )); c2.setCellStyle(style); HSSFCell c3 = row.createCell( 3 ); c3.setCellValue( new HSSFRichTextString( "ModelCode" )); c3.setCellStyle(style); HSSFCell c4 = row.createCell( 4 ); c4.setCellValue( new HSSFRichTextString( "Sub Component Name" )); c4.setCellStyle(style); HSSFCell c5 = row.createCell( 5 ); c5.setCellValue( new HSSFRichTextString( "Plat. Dev." )); c5.setCellStyle(style); HSSFCell c6 = row.createCell( 6 ); c6.setCellValue( new HSSFRichTextString( "Prod. Dev." )); c6.setCellStyle(style); HSSFCell c7 = row.createCell( 7 ); c7.setCellValue( new HSSFRichTextString( "Defect Solved Ver." )); c7.setCellStyle(style); HSSFCell c8 = row.createCell( 8 ); c8.setCellValue( new HSSFRichTextString( "Requester" )); c8.setCellStyle(style); HSSFCell c9 = row.createCell( 9 ); c9.setCellValue( new HSSFRichTextString( "Status" )); c9.setCellStyle(style); HSSFCell c10 = row.createCell( 10 ); c10.setCellValue( new HSSFRichTextString( "State Owner" )); c10.setCellStyle(style); ResultSet rs= null ; total= this .getResultCount_1(where); rs = this .getResultSet_1(where); int i = 0 ; try { while (rs.next()) { row=sheet.createRow(( int )++i); row.createCell(( short ) 0 ).setCellValue( new HSSFRichTextString(rs.getString( "ID" ))); row.createCell(( short ) 1 ).setCellValue( new HSSFRichTextString(rs.getString( "HEADLINE" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 2 ).setCellValue( new HSSFRichTextString(rs.getString( "SERIOUSNESS" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 3 ).setCellValue( new HSSFRichTextString(rs.getString( "MODELCODE" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 4 ).setCellValue( new HSSFRichTextString(rs.getString( "SUBCOMPONENTNAME" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 5 ).setCellValue( new HSSFRichTextString(rs.getString( "PLATFORMDEVELOPER" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 6 ).setCellValue( new HSSFRichTextString(rs.getString( "PRODUCTDEVELOPER" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 7 ).setCellValue( new HSSFRichTextString(rs.getString( "DEFECTSOLVEDVERSION" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 8 ).setCellValue( new HSSFRichTextString(rs.getString( "REQUESTER" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 9 ).setCellValue( new HSSFRichTextString(rs.getString( "STATUS" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); row.createCell(( short ) 10 ).setCellValue( new HSSFRichTextString(rs.getString( "STATEOWNER" ).replaceAll( "<" , " <" ).replaceAll( ">" , " >" ))); } rs.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } try { workbook.write(response.getOutputStream()); } catch (Exception e){ e.printStackTrace(); } } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步