原文地址：https://www.cnblogs.com/lfyu/p/8318201.html
项目使用的是jeecg开源框架（springmvc+spring+hibernate+。。。。。。等）此代码仅供参考！如有更好的意见或建议可留言。创建excel大致分这几步：1、创建HSSFWorkbook对象（也就是excel文档对象）2、通过HSSFWorkbook对象创建sheet对象（也就是excel中的sheet）3、通过sheet对象创建HSSFROW对象（row行对象）
4、通过HSSFROW对象创建列cell并set值（列名） 1 controller 层
2
3 /**
4 * excel自定义导出
5 * @param hAqscTieupsummary
6 * @param request
7 * @param response
8 * @param dataGrid
9 * @param modelMap
10 * @return
11 */
12 @SuppressWarnings("deprecation")
13 @RequestMapping(params = "exportEXL")
14 public String exportEXL(HAqscTieupsummaryEntity hAqscTieupsummary,
15 HttpServletRequest request, HttpServletResponse response,
16 DataGrid dataGrid, ModelMap modelMap) {
17 try {
18 String dateType = "yyyy";
19 SimpleDateFormat df = new SimpleDateFormat(dateType);// 设置日期格式
20 SimpleDateFormat df1 = new SimpleDateFormat("yyyy.MM.dd");// 设置日期格式
21 // 创建HSSFWorkbook对象(excel的文档对象)
22 HSSFWorkbook wb = new HSSFWorkbook();
23 HSSFRow row = null;
24 HSSFCell cell = null;
25 // 建立新的sheet对象（excel的表单） 并设置sheet名字
26 HSSFSheet sheet = wb.createSheet("占压管线台账信息");
27 sheet.setDefaultRowHeightInPoints(30);// 设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽
28 //----------------标题样式---------------------
29 HSSFCellStyle titleStyle = wb.createCellStyle(); //标题样式
30 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
31 titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
32 Font ztFont = wb.createFont();
33 ztFont.setItalic(false); // 设置字体为斜体字
34 ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
35 ztFont.setFontHeightInPoints((short)16); // 将字体大小设置为18px
36 ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
37 ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
38 // ztFont.setUnderline(Font.U_DOUBLE); // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
39 // ztFont.setStrikeout(true); // 是否添加删除线
40 titleStyle.setFont(ztFont);
41 //-------------------------------------------
42 //----------------二级标题格样式----------------------------------
43 HSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式
44 titleStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
45 titleStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
46 Font ztFont2 = wb.createFont();
47 ztFont2.setItalic(false); // 设置字体为斜体字
48 ztFont2.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
49 ztFont2.setFontHeightInPoints((short)11); // 将字体大小设置为18px
50 ztFont2.setFontName("宋体"); // 字体应用到当前单元格上
51 ztFont2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
52 // ztFont.setUnderline(Font.U_DOUBLE); // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
53 // ztFont.setStrikeout(true); // 是否添加删除线
54 titleStyle2.setFont(ztFont2);
55 //----------------------------------------------------------
56 //----------------单元格样式----------------------------------
57 HSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式
58 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
59 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
60 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
61 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
62 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
63 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
64 Font cellFont = wb.createFont();
65 cellFont.setItalic(false); // 设置字体为斜体字
66 cellFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
67 cellFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px
68 cellFont.setFontName("宋体"); // 字体应用到当前单元格上
69 // cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
70 cellStyle.setFont(cellFont);
71 cellStyle.setWrapText(true);//设置自动换行
72 //----------------------------------------------------------
73 // ----------------------创建第一行---------------
74 // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
75 row = sheet.createRow(0);
76 // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
77 cell = row.createCell(0);
78 // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
79 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19));
80 // 设置单元格内容
81 cell.setCellValue("占压城市地下管线、输油气管道、化工产品管道违法违规建设汇总表");
82 cell.setCellStyle(titleStyle);
83 // ----------------------------------------------
84
85 // ------------------创建第二行(单位、填表日期)---------------------
86 row = sheet.createRow(1); // 创建第二行
87 cell = row.createCell(0);
88 cell.setCellValue("填报单位名称（盖章）： ");
89 cell.setCellStyle(titleStyle2);
90 sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
91 cell = row.createCell(4);
92 sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
93 TSBaseUser tb = ResourceUtil.getSessionUserName(); //获取当前登录用户信息
94 String uid = tb.getId();
95 String deptId = userDao.getDeptId(uid);
96 String deptName = userDao.getDeptName(deptId);
97 cell.setCellValue(deptName);
98 // cell.setCellValue("*****");
99 cell.setCellStyle(titleStyle2);
100 cell = row.createCell(13); // 填表时间
101 sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 16));
102 cell.setCellValue("填表时间："+df1.format(new Date()));
103 cell.setCellStyle(titleStyle2);
104 // HSSFCell cell14 = row.createCell(15); // 填表时间
105 // cell14.setCellValue();
106 // cell14.setCellValue("2017.11.30");
107 // cell14.setCellStyle(titleStyle2);
108 // ----------------------------------------------
109
110 // ------------------创建表头start---------------------
111 row = sheet.createRow(2); // 创建第三行
112 sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
113 cell = row.createCell(0);
114 cell.setCellValue("序号");
115 cell.setCellStyle(cellStyle);
116
117 sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
118 cell = row.createCell(1);
119 cell.setCellValue("隐患等级评定");
120 cell.setCellStyle(cellStyle);
121
122 sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
123 cell = row.createCell(2);
124 cell.setCellValue("隐患名称");
125 cell.setCellStyle(cellStyle);
126
127 sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
128 cell = row.createCell(3);
129 cell.setCellValue("位置描述");
130 cell.setCellStyle(cellStyle);
131
132 cell = row.createCell(4);
133 cell.setCellStyle(cellStyle);
134 cell = row.createCell(5);
135 cell.setCellStyle(cellStyle);
136
137 sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 10));
138 cell = row.createCell(6);
139 cell.setCellValue("管线情况");
140 cell.setCellStyle(cellStyle);
141
142 cell = row.createCell(7);
143 cell.setCellStyle(cellStyle);
144 cell = row.createCell(8);
145 cell.setCellStyle(cellStyle);
146 cell = row.createCell(9);
147 cell.setCellStyle(cellStyle);
148 cell = row.createCell(10);
149 cell.setCellStyle(cellStyle);
150
151 sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 13));
152 cell = row.createCell(11);
153 cell.setCellValue("占压物情况");
154 cell.setCellStyle(cellStyle);
155
156 cell = row.createCell(12);
157 cell.setCellStyle(cellStyle);
158 cell = row.createCell(13);
159 cell.setCellStyle(cellStyle);
160
161 sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 14));
162 cell = row.createCell(14);
163 cell.setCellValue("占压物用途");
164 cell.setCellStyle(cellStyle);
165
166 sheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));
167 cell = row.createCell(15);
168 cell.setCellValue("已采用的安全防护措施");
169 cell.setCellStyle(cellStyle);
170
171 sheet.addMergedRegion(new CellRangeAddress(2, 3, 16, 16));
172 cell = row.createCell(16);
173 cell.setCellValue("备注");
174 cell.setCellStyle(cellStyle);
175
176 sheet.addMergedRegion(new CellRangeAddress(2, 3, 17, 17));
177 cell = row.createCell(17);
178 cell.setCellValue("联系人电话");
179 cell.setCellStyle(cellStyle);
180
181 sheet.addMergedRegion(new CellRangeAddress(2, 3, 18, 18));
182 cell = row.createCell(18);
183 cell.setCellValue("是否已和区管委和供热办联系");
184 cell.setCellStyle(cellStyle);
185
186 sheet.addMergedRegion(new CellRangeAddress(2, 3, 19, 19));
187 cell = row.createCell(19);
188 cell.setCellValue("是否采取防范措施");
189 cell.setCellStyle(cellStyle);
190
191 //--------------------------- 创建第四行--------------------
192 row = sheet.createRow(3);
193 sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 3));
194 cell = row.createCell(3);
195 cell.setCellValue("所在区县");
196 cell.setCellStyle(cellStyle);
197
198 cell = row.createCell(0);
199 cell.setCellStyle(cellStyle);
200
201 sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4));
202 cell = row.createCell(4);
203 cell.setCellValue("所在街道");
204 cell.setCellStyle(cellStyle);
205
206 cell = row.createCell(1);
207 cell.setCellStyle(cellStyle);
208
209 sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 5));
210 cell = row.createCell(5);
211 cell.setCellValue("详细地址");
212 cell.setCellStyle(cellStyle);
213
214 sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 6));
215 cell = row.createCell(6);
216 cell.setCellValue("管线建成时间");
217 cell.setCellStyle(cellStyle);
218
219 sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 7));
220 cell = row.createCell(7);
221 cell.setCellValue("管线埋深");
222 cell.setCellStyle(cellStyle);
223
224 sheet.addMergedRegion(new CellRangeAddress(3, 3, 8, 8));
225 cell = row.createCell(8);
226 cell.setCellValue("管径");
227 cell.setCellStyle(cellStyle);
228
229 sheet.addMergedRegion(new CellRangeAddress(3, 3, 9, 9));
230 cell = row.createCell(9);
231 cell.setCellValue("管线压力等级");
232 cell.setCellStyle(cellStyle);
233
234 sheet.addMergedRegion(new CellRangeAddress(3, 3, 10, 10));
235 cell = row.createCell(10);
236 cell.setCellValue("占压管线长度");
237 cell.setCellStyle(cellStyle);
238
239 sheet.addMergedRegion(new CellRangeAddress(3, 3, 11, 11));
240 cell = row.createCell(11);
241 cell.setCellValue("占压单位(个人)名称");
242 cell.setCellStyle(cellStyle);
243
244 sheet.addMergedRegion(new CellRangeAddress(3, 3, 12, 12));
245 cell = row.createCell(12);
246 cell.setCellValue("占压物建成时间");
247 cell.setCellStyle(cellStyle);
248
249 sheet.addMergedRegion(new CellRangeAddress(3, 3, 13, 13));
250 cell = row.createCell(13);
251 cell.setCellValue("占压物面积(平方米)");
252 cell.setCellStyle(cellStyle);
253
254 sheet.addMergedRegion(new CellRangeAddress(3, 3, 14, 14));
255 cell = row.createCell(14);
256 cell.setCellValue("经营、出租、自用、居住");
257 cell.setCellStyle(cellStyle);
258
259 cell = row.createCell(15);
260 cell.setCellStyle(cellStyle);
261 cell = row.createCell(16);
262 cell.setCellStyle(cellStyle);
263 cell = row.createCell(17);
264 cell.setCellStyle(cellStyle);
265 cell = row.createCell(18);
266 cell.setCellStyle(cellStyle);
267 cell = row.createCell(19);
268 cell.setCellStyle(cellStyle);
269 //-------------------------表头end---------------------
270 CriteriaQuery cq = new CriteriaQuery(HAqscTieupsummaryEntity.class,
271 dataGrid);
272 org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq,
273 hAqscTieupsummary, request.getParameterMap());
274 List<HAqscTieupsummaryEntity> hAqscTieupsummarys = this.hAqscTieupsummaryService
275 .getListByCriteriaQuery(cq, false);
276 for (int i = 0; i < hAqscTieupsummarys.size(); i++) { //向表格插入数据
277 List<Object> data = new ArrayList<>(); //将前台传来的数据存入到list中
278 // System.out.println(hAqscTieupsummarys.get(i).getSeqNum());
279 HAqscTieupsummaryEntity entity = hAqscTieupsummarys.get(i);
280 data.add(entity.getSeqNum());
281 String yhjb = entity.getYhDjpd();
282 String hyjb = dao.getHyjb(yhjb);
283 data.add(hyjb); //隐患级别
284 data.add(entity.getYhName());
285 String countryName = dao.getCountryByCode(entity.getAtcounty()); //区县
286 data.add(countryName);
287 String code = entity.getAtdistrict();
288 String streetName = dao.getStreetByCode(code); //街道
289 data.add(streetName);
290 data.add(entity.getAddress());
291 Date buildtime = entity.getPipelineBuildtime();
292 if (buildtime!=null) {
293 String format = df.format(buildtime);
294 data.add(format);
295 }else{
296 data.add("");
297 }
298 data.add(entity.getPipelineDepth());
299 data.add(entity.getPipeSize());
300 data.add(entity.getPipelinePr());
301 data.add(entity.getTppipelineLength());
302 data.add(entity.getTieupName());
303 Date goodsBuildtime = entity.getTieupgoodsBuildtime();
304 if (buildtime!=null) {
305 String format = df.format(goodsBuildtime);
306 data.add(format);
307 }else{
308 data.add("");
309 }
310 data.add(entity.getTieupgoodsArea());
311 String useType = entity.getTieupgoodsUse();
312 data.add(dao.getUseType(useType));
313 data.add(entity.getUseSecuritymeasures());
314 data.add(entity.getRemark());
315 data.add(entity.getTelephone());
316 data.add(dao.getIsContact(entity.getIsContact()));
317 data.add(entity.getIsUsesecuritymeasures());
318 int rowNum = 4+i; //从第四行开始
319 row = sheet.createRow(rowNum);
320 for (int j = 0; j < data.size(); j++) { //将数据添加到单元格中
321 // System.out.println(data.get(j));
322 sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, j, j));
323 cell = row.createCell(j);
324 cell.setCellValue(""+data.get(j)+"");
325 cell.setCellStyle(cellStyle);
326 }
327 }
328
329 // 输出Excel文件
330 OutputStream output = response.getOutputStream();
331 response.reset();
332 response.setHeader("Content-disposition",
333 "attachment; filename=details.xls"); //filename = 文件名
334 response.setContentType("application/msexcel");
335 wb.write(output);
336 output.close();
337 } catch (IOException e) {
338 // TODO Auto-generated catch block
339 e.printStackTrace();
340 }
341 return null;
342 }
5738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
