POI动态导出EXCEL

 

1. /** 
2.  * 利用开源组件POI3.0.2动态导出EXCEL文档 
3.  *  
4.  * @author 朱鹏飞 
5.  * @version v1.0 
6.  * @param <T> 
7.  *  
8.  */ 
9. public class ExportExcel<T> { 
10.  
11.     public void exportExcel(Collection<T> dataset, OutputStream out) { 
12.         exportExcel("对账单EXCEL文档", null, dataset, out, "yyyy-MM-dd"); 
13.     } 
14.  
15.     public void exportExcel(String[] headers, Collection<T> dataset, 
16.             OutputStream out) { 
17.         exportExcel("对账单EXCEL文档", headers, dataset, out, "yyyy-MM-dd"); 
18.     } 
19.  
20.     public void exportExcel(String[] headers, Collection<T> dataset, 
21.             OutputStream out, String pattern) { 
22.         exportExcel("对账单EXCEL文档", headers, dataset, out, pattern); 
23.     } 
24.  
25.     /** 
26.      * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL 的形式输出到指定IO设备上 
27.      *  
28.      * @param title 
29.      *            表格标题名 
30.      * @param headers 
31.      *            表格属性列名数组 
32.      * @param dataset 
33.      *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 
34.      *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) 
35.      * @param out 
36.      *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 
37.      * @param pattern 
38.      *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd" 
39.      */ 
40.     @SuppressWarnings({ "unchecked", "deprecation" }) 
41.     public void exportExcel(String title, String[] headers, 
42.             Collection<T> dataset, OutputStream out, String pattern) { 
43.         // 声明一个工作薄 
44.         HSSFWorkbook workbook = new HSSFWorkbook(); 
45.         // 生成一个表格 
46.         HSSFSheet sheet = workbook.createSheet(title); 
47.         // 设置表格默认列宽度为15个字节 
48.         sheet.setDefaultColumnWidth((short) 15); 
49.         // 生成一个样式 
50.         HSSFCellStyle style = workbook.createCellStyle(); 
51.         // 设置这些样式 
52.         style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); 
53.         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
54.         style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
55.         style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
56.         style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
57.         style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
58.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
59.         // 生成一个字体 
60.         HSSFFont font = workbook.createFont(); 
61.         font.setColor(HSSFColor.VIOLET.index); 
62.         font.setFontHeightInPoints((short) 12); 
63.         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
64.         // 把字体应用到当前的样式 
65.         style.setFont(font); 
66.         // 生成并设置另一个样式 
67.       HSSFCellStyle style2 = workbook.createCellStyle(); 
68.      style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 
69.      style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
70.      style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
71.      style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
72.      style2.setBorderRight(HSSFCellStyle.BORDER_THIN); 
73.      style2.setBorderTop(HSSFCellStyle.BORDER_THIN); 
74.      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
75.      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
76.      // 生成另一个字体 
77.      HSSFFont font2 = workbook.createFont(); 
78.       font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 
79.       // 把字体应用到当前的样式 
80.       style2.setFont(font2); 
81.  
82.         // 产生表格标题行 
83.         HSSFRow row = sheet.createRow(0); 
84.         for (short i = 0; i < headers.length; i++) { 
85.             HSSFCell cell = row.createCell(i); 
86.             cell.setCellStyle(style); 
87.             HSSFRichTextString text = new HSSFRichTextString(headers[i]); 
88.             cell.setCellValue(text); 
89.         } 
90.  
91.         // 遍历集合数据，产生数据行 
92.         Iterator<T> it = dataset.iterator(); 
93.         int index = 0; 
94.         while (it.hasNext()) { 
95.             index++; 
96.             row = sheet.createRow(index); 
97.             T t = (T) it.next(); 
98.             // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 
99.             Field[] fields = t.getClass().getDeclaredFields(); 
100.             for (short i = 0; i < fields.length; i++) { 
101.                 HSSFCell cell = row.createCell(i); 
102. //              cell.setCellStyle(style2); 
103.                 Field field = fields[i]; 
104.                 String fieldName = field.getName(); 
105.                 String getMethodName = "get" 
106.                         + fieldName.substring(0, 1).toUpperCase() 
107.                         + fieldName.substring(1); 
108.                 try { 
109.                     Class tCls = t.getClass(); 
110.                     Method getMethod = tCls.getMethod(getMethodName, 
111.                             new Class[] {}); 
112.                     Object value = getMethod.invoke(t, new Object[] {}); 
113.                     // 判断值的类型后进行强制类型转换 
114.                     String textValue = null; 
115.  
116.                     if (value instanceof Date) { 
117.                         Date date = (Date) value; 
118.                         SimpleDateFormat sdf = new SimpleDateFormat(pattern); 
119.                         textValue = sdf.format(date); 
120.                     } else { 
121.                         // 其它数据类型都当作字符串简单处理 
122.                         textValue = value.toString(); 
123.                     } 
124.                     // 利用正则表达式判断textValue是否全部由数字组成 
125.                     if (textValue != null) { 
126.                         Pattern p = Pattern.compile("^//d+(//.//d+)?{1}quot"); 
127.                         Matcher matcher = p.matcher(textValue); 
128.                         if (matcher.matches()) { 
129.                             // 是数字当作double处理 
130.                             cell.setCellValue(Double.parseDouble(textValue)); 
131.                         } else { 
132.                             HSSFRichTextString richString = new HSSFRichTextString( 
133.                                     textValue); 
134.                             HSSFFont font3 = workbook.createFont(); 
135.                             font3.setColor(HSSFColor.BLUE.index); 
136.                             richString.applyFont(font3); 
137.                             cell.setCellValue(richString); 
138.                         } 
139.                     } 
140.                 } catch (SecurityException e) { 
141.                     // TODO Auto-generated catch block 
142.                     e.printStackTrace(); 
143.                 } catch (NoSuchMethodException e) { 
144.                     // TODO Auto-generated catch block 
145.                     e.printStackTrace(); 
146.                 } catch (IllegalArgumentException e) { 
147.                     // TODO Auto-generated catch block 
148.                     e.printStackTrace(); 
149.                 } catch (IllegalAccessException e) { 
150.                     // TODO Auto-generated catch block 
151.                     e.printStackTrace(); 
152.                 } catch (InvocationTargetException e) { 
153.                     // TODO Auto-generated catch block 
154.                     e.printStackTrace(); 
155.                 } finally { 
156.                     // 清理资源 
157.                 } 
158.             } 
159.  
160.         } 
161.         try { 
162.             workbook.write(out); 
163.         } catch (IOException e) { 
164.             // TODO Auto-generated catch block 
165.             e.printStackTrace(); 
166.         } 
167.  
168.     } 
169.  
170.     public static void main(String[] args) throws IOException { 
171.         ExportExcel<ReportBillsModel> ex = new ExportExcel<ReportBillsModel>(); 
172.         String[] headers = { "估价编号", "抵押物地址", "主贷人姓名", "支行名称", "扣款金额", 
173.                 "中介公司名字", "扣款时间" }; 
174.         List<ReportBillsModel> ls = new ArrayList<ReportBillsModel>(); 
175.         ReportBillsModel rm = new ReportBillsModel(); 
176.         rm.setAgentName("test"); 
177.         rm.setBankName("bank"); 
178.         rm.setCheckTime("1992-21-21"); 
179.         rm.setCreditorName("aa"); 
180.         rm.setDeductionAmount(21); 
181.         rm.setDeductionTime("2012-12-12"); 
182.         rm.setEstimationCode("aadf"); 
183.         rm.setResidenceAddr("aaaaaaaaaaaaa"); 
184.  
185.         ls.add(rm); 
186.         try { 
187.             OutputStream out = new FileOutputStream("E://a.xls"); 
188.             ex.exportExcel(headers, ls, out); 
189.             out.close(); 
190.  
191.             System.out.println("excel导出成功！"); 
192.         } catch (FileNotFoundException e) { 
193.             // TODO Auto-generated catch block 
194.             e.printStackTrace(); 
195.         } 
196.  
197.     } 

 如果是web应用程序，做成下载的功能，代码如下：

 

1. HttpServletResponse response = getResponse(); 
2.         response.setContentType("application/octet-stream"); 
3.         String fileName = URLEncoder.encode("bills.xls", 
4.                 "UTF-8"); 
5.         String contentDisposition = "p_w_upload; filename=" + fileName; 
6.         response.setHeader("Content-Disposition", contentDisposition); 
7.         ExportExcel<ReportBillsModel> ex = new ExportExcel<ReportBillsModel>(); 
8.         OutputStream out = response.getOutputStream(); 
9.         ex.exportExcel(headers, rbmList, out); 
10.         out.close(); 

 

转载于:https://blog.51cto.com/pengxiaofei/1051488