后台数据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(); 
    
}

 

posted @   wujixing909  阅读(930)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示