java导出excel代码.docx
《java导出excel代码.docx》由会员分享,可在线阅读,更多相关《java导出excel代码.docx(29页珍藏版)》请在冰豆网上搜索。
java导出excel代码
Jsp代码
1.<%@ page language="java" pageEncoding="GBK"%>
2.<%
3.String path = request.getContextPath();
4.String basePath = request.getScheme()+":
//"+request.getServerName()+":
"+request.getServerPort()+path+"/";
5.%>
6.
7.
DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8.
9.
10.
导出excel 11.
12.
13.
14.
15.
16.
17.
18.
19. 导出excel
20.
23.
24.
Xml代码
1.
2. outPutExcel
3. output.OutputExcel
4.
5.
6. outPutExcel
7. /excel
8.
Java代码
1.package output;
2.
3.import java.io.File;
4.import java.io.FileNotFoundException;
5.import java.io.FileOutputStream;
6.import java.io.IOException;
7.
8.import org.apache.poi.hssf.usermodel.HSSFCell;
9.import org.apache.poi.hssf.usermodel.HSSFCellStyle;
10.import org.apache.poi.hssf.usermodel.HSSFFont;
11.import org.apache.poi.hssf.usermodel.HSSFRichTextString;
12.import org.apache.poi.hssf.usermodel.HSSFRow;
13.import org.apache.poi.hssf.usermodel.HSSFSheet;
14.import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15.import org.apache.poi.hssf.util.HSSFColor;
16.import org.apache.poi.hssf.util.Region;
17.
18./**
19.* EXCEL报表工具类.
20.*
21.* @author sun
22.* @version
23.*/
24.public class ExportExcel {
25.
26. private HSSFWorkbook wb = null;
27. private HSSFSheet sheet = null;
28.
29. /**
30. * @param wb
31. * @param sheet
32. */
33. public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {
34. //super();
35. this.wb = wb;
36. this.sheet = sheet;
37. }
38.
39. /**
40. * 创建通用EXCEL头部
41. *
42. * @param headString 头部显示的字符
43. * @param colSum 该报表的列数
44. */
45. public void createNormalHead(String headString, int colSum) {
46.
47. HSSFRow row = sheet.createRow(0);
48.
49. // 设置第一行
50. HSSFCell cell = row.createCell(0);
51. //row.setHeight((short) 1000);
52.
53. // 定义单元格为字符串类型
54. cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
55. cell.setCellValue(new HSSFRichTextString(headString));
56.
57. // 指定合并区域
58. /**
59. * public Region(int rowFrom,
60. * short colFrom,
61. * int rowTo,
62. * short colTo)
63. */
64. sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum));
65.
66. //定义单元格格式,添加单元格表样式,并添加到工作簿
67. HSSFCellStyle cellStyle = wb.createCellStyle();
68. //设置单元格水平对齐类型
69. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
70. cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
71. cellStyle.setWrapText(true);// 指定单元格自动换行
72.
73. // 设置单元格字体
74. HSSFFont font = wb.createFont();
75. //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
76. //font.setFontName("宋体");
77. //font.setFontHeight((short) 600);
78. //cellStyle.setFont(font);
79. cell.setCellStyle(cellStyle);
80. }
81.
82. /**
83. * 创建通用报表第二行
84. *
85. * @param params 统计条件数组
86. * @param colSum 需要合并到的列索引
87. */
88. public void createNormalTwoRow(String[] params, int colSum) {
89. //创建第二行
90. HSSFRow row1 = sheet.createRow
(1);
91.
92. row1.setHeight((short) 400);
93.
94. HSSFCell cell2 = row1.createCell(0);
95.
96. cell2.setCellType(HSSFCell.ENCODING_UTF_16);
97. cell2.setCellValue(new HSSFRichTextString("时间:
" + params[0] + "至" + params[1]));
98.
99. // 指定合并区域
100. /**
101. * public Region(int rowFrom,
102. short colFrom,
103. int rowTo,
104. short colTo)
105. */
106. sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum));
107.
108. HSSFCellStyle cellStyle = wb.createCellStyle();
109. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
110. cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
111. cellStyle.setWrapText(true);// 指定单元格自动换行
112.
113. // 设置单元格字体
114. HSSFFont font = wb.createFont();
115. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
116. font.setFontName("宋体");
117. font.setFontHeight((short) 250);
118. cellStyle.setFont(font);
119.
120. cell2.setCellStyle(cellStyle);
121. }
122. /**
123. * 设置报表标题
124. *
125. * @param columHeader 标题字符串数组
126. */
127. public void createColumHeader(String[] columHeader) {
128.
129. // 设置列头 在第三行
130. HSSFRow row2 = sheet.createRow
(2);
131.
132. // 指定行高
133. row2.setHeight((short) 600);
134.
135. HSSFCellStyle cellStyle = wb.createCellStyle();
136. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
137. cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
138. cellStyle.setWrapText(true);// 指定单元格自动换行
139.
140. // 单元格字体
141. HSSFFont font = wb.createFont();
142. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
143. font.setFontName("宋体");
144. font.setFontHeight((short) 250);
145. cellStyle.setFont(font);
146.
147. /*cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体
148. cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
149. cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
150. cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
151. cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
152. cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
153. cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
154. cellStyle.setTopBorderColor(HSSFColor.BLACK.index);*/
155.
156. // 设置单元格背景色
157. cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
158. cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
159.
160. HSSFCell cell3 = null;
161.
162. for (int i = 0; i < columHeader.length; i++) {
163. cell3 = row2.createCell(i);
164. cell3.setCellType(HSSFCell.ENCODING_UTF_16);
165. cell3.setCellStyle(cellStyle);
166. cell3.setCellValue(new HSSFRichTextString(columHeader[i]));
167. }
168. }
169.
170. /**
171. * 创建内容单元格
172. *
173. * @param wb HSSFWorkbook
174. * @param row HSSFRow
175. * @param col short型的列索引
176. * @param align 对齐方式
177. * @param val 列值
178. */
179. public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col,short align, String val) {
180. HSSFCell cell = row.createCell(col);
181. cell.setCellType(HSSFCell.ENCODING_UTF_16);
182. cell.setCellValue(new HSSFRichTextString(val));
183. HSSFCellStyle cellstyle = wb.createCellStyle();
184. cellstyle.setAlignment(align);
185. cell.setCellStyle(cellstyle);
186. }
187. /**
188. * 创建合计行
189. * @param colSum 需要合并到的列索引
190. * @param cellValue
191. */
192. public void createLastSumRow(int colSum, String[] cellValue) {
193.
194. HSSFCellStyle cellStyle = wb.createCellStyle();
195. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
196. cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
197. cellStyle.setWrapText(true);// 指定单元格自动换行
198.
199. // 单元格字体
200. HSSFFont font = wb.createFont();
201. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
202. font.setFontName("宋体");
203. font.setFontHeight((short) 250);
204. cellStyle.setFont(font);
205. //获取工作表最后一行
206. HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
207. HSSFCell sumCell = lastRow.createCell(0);
208.
209. sumCell.setCellValue(new HSSFRichTextString("合计"));
210. sumCell.setCellStyle(cellStyle);
211. //合并 最后一行的第零列-最后一行的第一列
212. sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,sheet.getLastRowNum(), (short) colSum));// 指定合并区域
213.
214. for (int i = 2; i < (cellValue.length + 2); i++) {
215. //定义最后一行的第三列
216. sumCell = lastRow.createCell(i);
217. sumCell.setCellStyle(cellStyle);
218. //定义数组 从0开始。
219. sumCell.setCellValue(new HSSFRichTextString(cellValue[i-2]));
220. }
221. }
222. /**
223. * 输入EXCEL文件
224. *
225.