POI通过读取Excel模板生成Excel文件

Java代码  

1. /** 
2.      * 导出 
3.      * @param mapping 
4.      * @param form 
5.      * @param request 
6.      * @param response 
7.      * @return 
8.      */  
9.     public ActionForward export(ActionMapping mapping, ActionForm form,  
10.             HttpServletRequest request, HttpServletResponse response) {  
11.           
12.         String checkBoxValueList = request.getParameter("ckv");  
13.         if(!CommonUtil.isNotNullorEmtry(checkBoxValueList)) {  
14.             this.saveMessages(request, "export.fail");  
15.             return mapping.findForward("export.fail");  
16.         }  
17.         List<Order> listOrder = orderService.exportOrderList(checkBoxValueList);  
18.           
19.         try {  
20. //          SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmm");  
21. //          Calendar calendar = Calendar.getInstance();  
22.               
23.             ServletOutputStream os = response.getOutputStream(); //获得输出流  
24.             response.reset();   //清空输出流  
25.             String fileName = new String("订单列表".getBytes("gb2312"), "ISO8859-1") +".xls";  
26.             response.setHeader("Content-disposition", "attachment; filename="+ fileName); //设定输出文件头  
27.             response.setContentType("application/msexcel"); //定义输出类型  
28.   
29.             String filePath = request.getSession().getServletContext().getRealPath("/excel/model/order.xls");  
30.   
31.             HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));    //读取excel模板  
32.             try {  
33.                 HSSFSheet sheet = workbook.getSheetAt(0);   //读取第一个工作簿  
34.                 HSSFRow row;  
35.                 HSSFCell cell = null;  
36.                 int rownum = 3; //添加的起始行  
37.                 HSSFCellStyle style = this.getStyle(workbook);  
38.                 Iterator<Order> it = listOrder.iterator();  
39.                 Order order = null;  
40.                                   
41.                 while(it.hasNext()) {  
42.                     order = new Order();  
43.                     order = it.next();  
44.                       
45.                     row = sheet.createRow(rownum);  
46.                       
47.                     myCreateCell(1, String.valueOf(rownum-2), row, cell, style);    //列1  
48.                     myCreateCell(2, order.getAdName(), row, cell, style);   //列2  
49.                     myCreateCell(3, order.getSmallAreaName(), row, cell, style);    //列1      
50.                                           
51.                     rownum++;  
52.                 }  
53.                                   
54.                 workbook.write(os);  
55.                 os.flush();  
56.                 os.close();  
57.             } catch (Exception e) {  
58.                 e.printStackTrace();  
59.             }  
60.         } catch (IOException e) {  
61.             e.printStackTrace();  
62.         }  
63.         return null;  
64.     }  
65.       
66.     private void myCreateCell(int cellnum, String value, HSSFRow row, HSSFCell cell, HSSFCellStyle style) {  
67.         cell = row.createCell((short) cellnum);  
68.         cell.setCellValue(new HSSFRichTextString(value));  
69.         cell.setCellStyle(style);  
70.     }  
71.       
72.     public HSSFCellStyle getStyle(HSSFWorkbook workbook) {  
73.         //设置字体;  
74.         HSSFFont font = workbook.createFont();  
75.         //设置字体大小;  
76.         font.setFontHeightInPoints((short) 5);  
77.         //设置字体名字;  
78.         font.setFontName("Courier New");  
79.         //font.setItalic(true);  
80.         //font.setStrikeout(true);  
81.         //设置样式;  
82.         HSSFCellStyle style = workbook.createCellStyle();  
83.         //设置底边框;  
84.         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
85.         //设置底边框颜色;  
86.         style.setBottomBorderColor(HSSFColor.BLACK.index);  
87.         //设置左边框;  
88.         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
89.         //设置左边框颜色;  
90.         style.setLeftBorderColor(HSSFColor.BLACK.index);  
91.         //设置右边框;  
92.         style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
93.         //设置右边框颜色;  
94.         style.setRightBorderColor(HSSFColor.BLACK.index);  
95.         //设置顶边框;  
96.         style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
97.         //设置顶边框颜色;  
98.         style.setTopBorderColor(HSSFColor.BLACK.index);  
99.         //在样式用应用设置的字体;  
100.         style.setFont(font);  
101.         //设置自动换行;  
102.         style.setWrapText(false);  
103.         //设置水平对齐的样式为居中对齐;  
104.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
105.         //设置垂直对齐的样式为居中对齐;  
106.         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
107.         return style;  
108.     }