POI Excel导出，小弟我的又一个模板方法

POI Excel导出，我的又一个模板方法

下面有源码，下载就可以用，如果因为包的问题，就拷贝我的包就行了

首先是模板工具类的写法：

1. package?com.util;??
2. ??
3. import?java.io.IOException;??
4. import?java.io.OutputStream;??
5. import?java.math.BigDecimal;??
6. import?java.text.MessageFormat;??
7. import?java.util.List;??
8. import?javax.servlet.http.HttpServletRequest;??
9. import?org.apache.poi.hssf.usermodel.HSSFCell;??
10. import?org.apache.poi.hssf.usermodel.HSSFCellStyle;??
11. import?org.apache.poi.hssf.usermodel.HSSFFont;??
12. import?org.apache.poi.hssf.usermodel.HSSFPrintSetup;??
13. import?org.apache.poi.hssf.usermodel.HSSFRow;??
14. import?org.apache.poi.hssf.usermodel.HSSFSheet;??
15. import?org.apache.poi.hssf.usermodel.HSSFWorkbook;??
16. import?org.apache.poi.hssf.util.HSSFColor;??
17. import?org.apache.poi.hssf.util.Region;??
18. ??
19. /**?
20. ?*?导出Excel的工具类，只需给相应参数即可?
21. ?*??
22. ?*?@author?Administrator?
23. ?*??
24. ?*/??
25. public?class?ExcelUtil?{??
26. ????/**?
27. ?????*?导出Excel的方法?
28. ?????*??
29. ?????*?@param?request?
30. ?????*????????????Web应用中的request对象?
31. ?????*?@param?excelContent?
32. ?????*????????????数据体集合，集合内放置String数组?
33. ?????*?@param?columnNames?
34. ?????*????????????数据列的头，使用数组?
35. ?????*?@param?titleName?
36. ?????*????????????要导出的文件名称?
37. ?????*?@param?fOut?
38. ?????*????????????导出流，Web中使用response得到?
39. ?????*?@throws?IOException?
40. ?????*?????????????可能会爆出的异常?
41. ?????*/??
42. ????@SuppressWarnings("deprecation")??
43. ????public?static?void?create(HttpServletRequest?request,?List<?>?excelContent,??
44. ????????????String[]?columnNames,?String?titleName,?OutputStream?fOut)??
45. ????????????throws?IOException?{??
46. ????????HSSFWorkbook?wb?=?new?HSSFWorkbook();//?建立新HSSFWorkbook对象??
47. ????????HSSFSheet?sheet?=?wb.createSheet("sheet1");//?建立新的sheet对象??
48. ????????//?================合并标题列=========================??
49. ????????Region?region?=?new?Region();??
50. ????????region.setColumnFrom((short)?0);??
51. ????????region.setColumnTo((short)?(columnNames.length?-?1));??
52. ????????region.setRowFrom((short)?0);??
53. ????????region.setRowTo((short)?0);??
54. ????????sheet.addMergedRegion(region);??
55. ????????//?===============================================??
56. ????????//?标题列列的字体样式??
57. ????????HSSFFont?titleFont?=?wb.createFont();??
58. ????????titleFont.setColor(HSSFFont.COLOR_RED);??
59. ????????titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);??
60. ????????titleFont.setFontHeight((short)?300);??
61. ??
62. ????????//?选择列的字体样式??
63. ????????HSSFFont?headerFont?=?wb.createFont();??
64. ????????headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);??
65. ????????headerFont.setColor(HSSFFont.BOLDWEIGHT_NORMAL);??
66. ????????HSSFPrintSetup?printSetup?=?sheet.getPrintSetup();??
67. ????????printSetup.setLandscape(true);??
68. ????????sheet.setFitToPage(true);??
69. ????????sheet.setHorizontallyCenter(true);??
70. ????????sheet.createFreezePane(0,?1);??
71. ????????sheet.setAutobreaks(true);??
72. ????????sheet.setDefaultColumnWidth((short)?13.5);??
73. ????????printSetup.setFitHeight((short)?100);??
74. ????????printSetup.setFitWidth((short)?180);??
75. ????????//?标题列样式??
76. ????????HSSFCellStyle?titlestyle?=?wb.createCellStyle();??
77. ????????titlestyle.setFont(titleFont);??
78. ????????titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);??
79. ????????titlestyle.setFillForegroundColor(HSSFColor.WHITE.index);??
80. ????????titlestyle.setFillPattern(HSSFCellStyle.SQUARES);??
81. ????????titlestyle.setLeftBorderColor(HSSFColor.BLACK.index);??
82. ????????titlestyle.setRightBorderColor(HSSFColor.BLACK.index);??
83. ????????titlestyle.setTopBorderColor(HSSFColor.BLACK.index);??
84. ????????titlestyle.setBottomBorderColor(HSSFColor.BLACK.index);??
85. ????????//?titlestyle.setFillPattern((short)300);??
86. ??
87. ????????titlestyle.setWrapText(true);??
88. ????????//?选择列样式??
89. ????????HSSFCellStyle?style?=?wb.createCellStyle();??
90. ????????style.setFont(headerFont);??
91. ????????style.setBorderBottom(HSSFCellStyle.BORDER_THIN);??
92. ????????style.setAlignment(HSSFCellStyle.ALIGN_CENTER);??
93. ????????style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);??
94. ????????style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);??
95. ????????style.setHidden(true);??
96. ????????//?内容列的样式??
97. ????????HSSFCellStyle?style2?=?wb.createCellStyle();??
98. ????????style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);??
99. ????????style2.setDataFormat(wb.createDataFormat().getFormat("0.00"));??
100. ????????style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);??
101. ????????//?写入标题??
102. ????????HSSFRow?titleRow?=?null;??
103. ??
104. ????????if?(!titleName.equals(""))?{??
105. ????????????titleRow?=?sheet.createRow((short)?0);??
106. ????????????titleRow.setHeightInPoints(30.120f);??
107. ????????????HSSFCell?titlecell?=?titleRow.createCell((short)?0);//?标题??
108. ????????????titlecell.setEncoding(HSSFCell.ENCODING_UTF_16);//??
109. ????????????titlecell.setCellStyle(titlestyle);??
110. ????????????titlecell.setCellValue(titleName);??
111. ????????}??
112. ??
113. ????????//?创建列名称??
114. ????????HSSFRow?headRow?=?sheet.createRow((short)?1);//??
115. ????????headRow.setHeightInPoints(20.120f);??
116. ??
117. ????????for?(int?i?=?0;?i?<?columnNames.length;?i++)?{??
118. ????????????HSSFCell?cell?=?headRow.createCell((short)?i);??
119. ????????????cell.setCellStyle(style);??
120. ????????????cell.setEncoding(HSSFCell.ENCODING_UTF_16);//?设置cell编码解决中文高位字节截断??
121. ????????????cell.setCellValue(columnNames[i]);??
122. ????????}??
123. ????????//?创建内容??
124. ????????for?(int?i?=?0;?i?<?excelContent.size();?i++)?{??
125. ????????????//?建立新行??
126. ????????????HSSFRow?row?=?sheet.createRow((short)?i?+?2);??
127. ????????????for?(int?j?=?0;?j?<?((Object[])?excelContent.get(i)).length;?j++)?{??
128. ????????????????//?新建一列??
129. ????????????????HSSFCell?cell?=?row.createCell((short)?j);??
130. ????????????????cell.setCellStyle(style2);??
131. ????????????????cell.setEncoding(HSSFCell.ENCODING_UTF_16);//?设置cell编码解决中文高位字节截断??
132. ????????????????if?(j?==?0)?{??
133. ????????????????????cell.setCellValue((i?+?1)?+?"");??
134. ????????????????????continue;??
135. ????????????????}??
136. ????????????????Object?t?=?((Object[])?excelContent.get(i))[j];??
137. ????????????????if?(t?instanceof?BigDecimal)?{??
138. ????????????????????cell.setCellValue(((BigDecimal)?((Object[])?excelContent??
139. ????????????????????????????.get(i))[j]).toString());??
140. ????????????????????continue;??
141. ????????????????}??
142. ????????????????if?(t?instanceof?java.sql.Date)?{??
143. ????????????????????cell.setCellValue(((java.sql.Date)?((Object[])?excelContent??
144. ????????????????????????????.get(i))[j]).toString());??
145. ????????????????????continue;??
146. ????????????????}??
147. ????????????????if?(t?instanceof?java.util.Date)?{??
148. ????????????????????cell??
149. ????????????????????????????.setCellValue(((java.util.Date)?((Object[])?excelContent??
150. ????????????????????????????????????.get(i))[j]).toString());??
151. ????????????????????continue;??
152. ????????????????}??
153. ????????????????cell.setCellValue((String)?((Object[])?excelContent.get(i))[j]);??
154. ????????????}??
155. ????????}??
156. ??
157. ????????request.setCharacterEncoding("gbk");??
158. ????????MessageFormat.format("{0}/{1}.xls",?request.getRealPath("expExcel"),??
159. ????????????????Long.toString(System.currentTimeMillis()));?//?filename是工作薄的存放位置??
160. ????????wb.write(fOut);??
161. ????????fOut.flush();??
162. ????????fOut.close();??
163. ????}??
164. ??
165. }??

?

再者是调用的Servlet，只是为了演示，具体使用看自己的了：

1. package?com.servlet;??
2. ??
3. import?java.io.IOException;??
4. import?java.io.OutputStream;??
5. import?java.util.ArrayList;??
6. import?java.util.List;??
7. ??
8. import?javax.servlet.ServletException;??
9. import?javax.servlet.http.HttpServlet;??
10. import?javax.servlet.http.HttpServletRequest;??
11. import?javax.servlet.http.HttpServletResponse;??
12. ??
13. import?com.util.ExcelUtil;??
14. ??
15. public?class?ExcelServlet?extends?HttpServlet?{??
16. ??
17. ????private?static?final?long?serialVersionUID?=?1L;??
18. ??
19. ????@SuppressWarnings("unchecked")??
20. ????public?void?doGet(HttpServletRequest?request,?HttpServletResponse?response)??
21. ????????????throws?ServletException,?IOException?{??
22. ??
23. ????????List<?>?excelContent?=?getPersonList();??
24. ????????String[]?columnNames?=?new?String[]?{?"编号",?"名称",?"住址",?"介绍",?"出生日期",??
25. ????????????????"月薪"?};??
26. ????????String?titleName?=?"测试Excel工具类导出.xls";??
27. ??
28. ????????OutputStream?fOut?=?response.getOutputStream();??
29. ????????response.setHeader("Content-Disposition",?"attachment;?filename="??
30. ????????????????+?new?String(titleName.getBytes("GB2312"),?"ISO8859-1"));??
31. ??
32. ????????ExcelUtil.create(request,?excelContent,?columnNames,?titleName,?fOut);??
33. ????}??
34. ??
35. ????@Override??
36. ????protected?void?doPost(HttpServletRequest?request,??
37. ????????????HttpServletResponse?response)?throws?ServletException,?IOException?{??
38. ????????this.doGet(request,?response);??
39. ????}??
40. ??
41. ????/**?
42. ?????*?组装数据实体，实际上是内涵数组的集合，具体组装根据业务自行编写代码实现?数据体中的数字，小数点，日期等不用考虑直接使用?
43. ?????*??
44. ?????*?@return?
45. ?????*/??
46. ????@SuppressWarnings("unchecked")??
47. ????public?static?List?getPersonList()?{??
48. ????????List?list?=?new?ArrayList();??
49. ????????String[]?p1?=?new?String[]?{?"00001",?"科研室主任",?"海淀区",?"北京大学研究生毕业",??
50. ????????????????"1988-9-6",?"4500.99"?};??
51. ????????String[]?p2?=?new?String[]?{?"00002",?"营销部经理",?"朝阳区",?"北京国际商务学院本科生",??
52. ????????????????"1995-6-8",?"6000.25"?};??
53. ????????String[]?p3?=?new?String[]?{?"00003",?"策划部专员",?"石景山区",?"清华大学应届毕业生",??
54. ????????????????"1889-9-5",?"10000"?};??
55. ????????list.add(p1);??
56. ????????list.add(p2);??
57. ????????list.add(p3);??
58. ????????return?list;??
59. ????}??
60. ??
61. }??

?

如果有问题，欢迎大家提出意见和建议，我们一同进步

• excelPOI.rar?(4.8 MB)
• 下载次数: 75 1 楼 suan2046 2012-05-02   仅仅只是针对小数据量的 导出，大数据量就有OOM问题