poi导出excel合并单元格(包括列合并、行合并)

1 工程所需jar包如下：
commons-codec-1.5.jar
commons-logging-1.1.jar
log4j-1.2.13.jar
junit-3.8.1.jar
poi-3.9-20121203.jar

2 Code:

Java代码  

1. /** 
2.  * 导出设备信息Excel 
3.  * @param form      和 HTTP 请求相关的表格对象 
4.  * @param resources 信息资源对象 
5.  * @param locale    本地化对象 
6.  * @param session   HTTP 会话对象 
7.  * @param request   HTTP 请求对象 
8.  * @param response  HTTP 响应对象 
9.  * @return 
10.  */  
11. public String exportExcel(DynaBean form, MessageResources resources,  
12.         Locale locale, HttpSession session, HttpServletRequest request,  
13.         HttpServletResponse response) throws Exception{  
14.     int iLanguage = (locale.getLanguage().indexOf("en")>=0)?0:1;  
15.     response.reset();  
16.     response.setContentType("application/vnd.ms-excel");  
17.     response.setHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(resources.getMessage(locale, "device.details")+".xls","UTF-8"));  
18.     OutputStream sos = response.getOutputStream();  
19.     List<DeviceVO> deviceList = dao.getAllDevice();  
20.     HSSFWorkbook wb = new HSSFWorkbook();  
21.     Map<String, CellStyle> styles = createStyles(wb);  
22.     // 创建sheet页  
23.     Sheet sheet = wb.createSheet("Sheet");  
24.     PrintSetup printSetup = sheet.getPrintSetup();  
25.     printSetup.setLandscape(true);  
26.     sheet.setFitToPage(true);  
27.     sheet.setHorizontallyCenter(true);  
28.     /** 
29.     *合并单元格的行或者列 
30.     */  
31.     sheet.addMergedRegion(CellRangeAddress.valueOf("$F$1:$H$1"));  
32.     sheet.addMergedRegion(CellRangeAddress.valueOf("$M$1:$P$1"));  
33.     sheet.addMergedRegion(CellRangeAddress.valueOf("$Q$1:$S$1"));  
34.     sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$A$2"));  
35.     sheet.addMergedRegion(CellRangeAddress.valueOf("$B$1:$B$2"));  
36.     sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$C$2"));  
37.     sheet.addMergedRegion(CellRangeAddress.valueOf("$D$1:$D$2"));  
38.     sheet.addMergedRegion(CellRangeAddress.valueOf("$E$1:$E$2"));  
39.     sheet.addMergedRegion(CellRangeAddress.valueOf("$I$1:$I$2"));  
40.     sheet.addMergedRegion(CellRangeAddress.valueOf("$J$1:$J$2"));  
41.     sheet.addMergedRegion(CellRangeAddress.valueOf("$K$1:$K$2"));  
42.     sheet.addMergedRegion(CellRangeAddress.valueOf("$L$1:$L$2"));  
43.     sheet.addMergedRegion(CellRangeAddress.valueOf("$T$1:$T$2"));  
44.     sheet.addMergedRegion(CellRangeAddress.valueOf("$U$1:$U$2"));  
45.     sheet.addMergedRegion(CellRangeAddress.valueOf("$V$1:$V$2"));  
46.     sheet.addMergedRegion(CellRangeAddress.valueOf("$W$1:$W$2"));  
47.     sheet.addMergedRegion(CellRangeAddress.valueOf("$X$1:$X$2"));  
48.     sheet.addMergedRegion(CellRangeAddress.valueOf("$Y$1:$Y$2"));  
49.     sheet.addMergedRegion(CellRangeAddress.valueOf("$Z$1:$Z$2"));  
50.     sheet.addMergedRegion(CellRangeAddress.valueOf("$AA$1:$AA$2"));  
51.     sheet.addMergedRegion(CellRangeAddress.valueOf("$AB$1:$AB$2"));  
52.       
53.   
54.       
55.     // 创建表头  
56.     Row headerRow = sheet.createRow(0);  
57.     headerRow.setHeightInPoints(30);  
58.     Cell headerCell;  
59.       
60.     headerCell = headerRow.createCell(0);  
61.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.number")); //设备编号  
62.     headerCell.setCellStyle(styles.get("header"));  
63.   
64.     headerCell = headerRow.createCell(1);  
65.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.qrcode")); //设备二维码  
66.     headerCell.setCellStyle(styles.get("header"));  
67.   
68.     headerCell = headerRow.createCell(2);  
69.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customerbase")); //客户群  
70.     headerCell.setCellStyle(styles.get("header"));  
71.   
72.     headerCell = headerRow.createCell(3);  
73.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customertype")); //客户类别  
74.     headerCell.setCellStyle(styles.get("header"));  
75.   
76.     headerCell = headerRow.createCell(4);  
77.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customername")); //客户名称  
78.     headerCell.setCellStyle(styles.get("header"));  
79.   
80.     headerCell = headerRow.createCell(5);  
81.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.area")); //设备区域  
82.     headerCell.setCellStyle(styles.get("header"));  
83.   
84.     headerCell = headerRow.createCell(8);  
85.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.itemname")); //所属项目名称  
86.     headerCell.setCellStyle(styles.get("header"));  
87.   
88.     headerCell = headerRow.createCell(9);  
89.     headerCell.setCellValue(resources.getMessage(locale, "device.category")); //设备类别  
90.     headerCell.setCellStyle(styles.get("header"));  
91.   
92.     headerCell = headerRow.createCell(10);  
93.     headerCell.setCellValue(resources.getMessage(locale, "device.name")); //设备名称  
94.     headerCell.setCellStyle(styles.get("header"));  
95.   
96.     headerCell = headerRow.createCell(11);  
97.     headerCell.setCellValue(resources.getMessage(locale, "device.no")); //设备信息编号  
98.     headerCell.setCellStyle(styles.get("header"));  
99.   
100.     headerCell = headerRow.createCell(12);  
101.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.baseinfomation")); //设备基本信息  
102.     headerCell.setCellStyle(styles.get("header"));  
103.       
104.     headerCell = headerRow.createCell(16);  
105.     headerCell.setCellValue(resources.getMessage(locale, "device.location")); //设备位置  
106.     headerCell.setCellStyle(styles.get("header"));  
107.       
108.     headerCell = headerRow.createCell(19);  
109.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.enabledate")); //设备启用日期  
110.     headerCell.setCellStyle(styles.get("header"));  
111.       
112.     headerCell = headerRow.createCell(20);  
113.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.backendload")); //后端负载  
114.     headerCell.setCellStyle(styles.get("header"));  
115.       
116.     headerCell = headerRow.createCell(21);  
117.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.operationtips")); //操作提示  
118.     headerCell.setCellStyle(styles.get("header"));  
119.       
120.     headerCell = headerRow.createCell(22);  
121.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.maintenancepeople")); //维护责任人  
122.     headerCell.setCellStyle(styles.get("header"));  
123.       
124.     headerCell = headerRow.createCell(23);  
125.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.usetime")); //巡检时间  
126.     headerCell.setCellStyle(styles.get("header"));  
127.       
128.     headerCell = headerRow.createCell(24);  
129.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.intervaltime")); //巡检下台设备时间间隔  
130.     headerCell.setCellStyle(styles.get("header"));  
131.       
132.     headerCell = headerRow.createCell(25);  
133.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.patrolpath")); //巡检路径  
134.     headerCell.setCellStyle(styles.get("header"));  
135.       
136.     headerCell = headerRow.createCell(26);  
137.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.enable")); //是否启用巡检路径  
138.     headerCell.setCellStyle(styles.get("header"));  
139.       
140.     headerCell = headerRow.createCell(27);  
141.     headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.other")); //其他信息  
142.     headerCell.setCellStyle(styles.get("header"));  
143.       
144.       
145.     Row headerRowRegion = sheet.createRow(1);  
146.     headerRowRegion.setHeightInPoints(15);  
147.     Cell headerCellRegion;  
148.     headerCellRegion = headerRowRegion.createCell(5);  
149.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.province")); //省份  
150.     headerCellRegion.setCellStyle(styles.get("header"));  
151.       
152.     headerCellRegion = headerRowRegion.createCell(6);  
153.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.region")); //市  
154.     headerCellRegion.setCellStyle(styles.get("header"));  
155.       
156.     headerCellRegion = headerRowRegion.createCell(7);  
157.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.county")); //县/区  
158.     headerCellRegion.setCellStyle(styles.get("header"));  
159.       
160.     headerCellRegion = headerRowRegion.createCell(12);  
161.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.brand")); //品牌  
162.     headerCellRegion.setCellStyle(styles.get("header"));  
163.       
164.     headerCellRegion = headerRowRegion.createCell(13);  
165.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.model")); //型号  
166.     headerCellRegion.setCellStyle(styles.get("header"));  
167.       
168.     headerCellRegion = headerRowRegion.createCell(14);  
169.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.capacity")); //容量  
170.     headerCellRegion.setCellStyle(styles.get("header"));  
171.       
172.     headerCellRegion = headerRowRegion.createCell(15);  
173.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.systemtype")); //系统类型  
174.     headerCellRegion.setCellStyle(styles.get("header"));  
175.       
176.     headerCellRegion = headerRowRegion.createCell(16);  
177.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.build")); //楼号  
178.     headerCellRegion.setCellStyle(styles.get("header"));  
179.       
180.     headerCellRegion = headerRowRegion.createCell(17);  
181.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.floor")); //楼层  
182.     headerCellRegion.setCellStyle(styles.get("header"));  
183.       
184.     headerCellRegion = headerRowRegion.createCell(18);  
185.     headerCellRegion.setCellValue(resources.getMessage(locale, "device.room")); //房间号  
186.     headerCellRegion.setCellStyle(styles.get("header"));  
187.       
188.     for(int i=0;i<deviceList.size();i++){  
189.         DeviceVO device = deviceList.get(i);  
190.         Cell rowCell;  
191.         Row cellRow = sheet.createRow(i + 2);  
192.           
193.         rowCell = cellRow.createCell(0);  
194.         rowCell.setCellValue(device.getDeviceId());  
195.         rowCell.setCellStyle(styles.get("cell"));  
196.           
197.         rowCell = cellRow.createCell(1);  
198.         rowCell.setCellValue(device.getQrcode());  
199.         rowCell.setCellStyle(styles.get("cell"));  
200.           
201.         rowCell = cellRow.createCell(2);  
202.         //判断是否是中文  
203.         if(iLanguage==1){  
204.             rowCell.setCellValue(device.getItemId().getCustomerBase().substring(device.getItemId().getCustomerBase().indexOf(":")+1,device.getItemId().getCustomerBase().length()));  
205.         }else if(iLanguage==0){ //判断是否是英文  
206.             rowCell.setCellValue(device.getItemId().getCustomerBase().substring(0,device.getItemId().getCustomerBase().indexOf(":")));  
207.         }  
208.         rowCell.setCellStyle(styles.get("cell"));  
209.           
210.         rowCell = cellRow.createCell(3);  
211.         //判断是否是中文  
212.         if(iLanguage==1){  
213.             rowCell.setCellValue(device.getItemId().getCustomerType().substring(device.getItemId().getCustomerType().indexOf(":")+1,device.getItemId().getCustomerType().length()));  
214.         }else if(iLanguage==0){ //判断是否是英文  
215.             rowCell.setCellValue(device.getItemId().getCustomerType().substring(0,device.getItemId().getCustomerType().indexOf(":")));  
216.         }  
217.         rowCell.setCellStyle(styles.get("cell"));  
218.           
219.         rowCell = cellRow.createCell(4);  
220.         rowCell.setCellValue(device.getItemId().getCustomerName());  
221.         rowCell.setCellStyle(styles.get("cell"));  
222.           
223.         rowCell = cellRow.createCell(5);  
224.         rowCell.setCellValue(device.getItemId().getProvince());  
225.         rowCell.setCellStyle(styles.get("cell"));  
226.           
227.         rowCell = cellRow.createCell(6);  
228.         rowCell.setCellValue(device.getItemId().getCity());  
229.         rowCell.setCellStyle(styles.get("cell"));  
230.           
231.         rowCell = cellRow.createCell(7);  
232.         rowCell.setCellValue(device.getItemId().getCounty());  
233.         rowCell.setCellStyle(styles.get("cell"));  
234.           
235.         rowCell = cellRow.createCell(8);  
236.         //判断是否是中文  
237.         if(iLanguage==1){  
238.             rowCell.setCellValue(device.getItemId().getItemName());  
239.         }else if(iLanguage==0){ //判断是否是英文  
240.             rowCell.setCellValue(device.getItemId().getForShort());  
241.         }  
242.         rowCell.setCellStyle(styles.get("cell"));  
243.           
244.         rowCell = cellRow.createCell(9);  
245.         //判断是否是中文  
246.         if(iLanguage==1){  
247.             rowCell.setCellValue(device.getZequipId().getZequipGroup().getNameLoc());  
248.         }else if(iLanguage==0){ //判断是否是英文  
249.             rowCell.setCellValue(device.getZequipId().getZequipGroup().getNameEn());  
250.         }  
251.         rowCell.setCellStyle(styles.get("cell"));  
252.           
253.         rowCell = cellRow.createCell(10);  
254.         rowCell.setCellValue(device.getDeviceName());  
255.         rowCell.setCellStyle(styles.get("cell"));  
256.           
257.         rowCell = cellRow.createCell(11);  
258.         rowCell.setCellValue(device.getDeviceNo());  
259.         rowCell.setCellStyle(styles.get("cell"));  
260.           
261.         rowCell = cellRow.createCell(12);  
262.         rowCell.setCellValue(device.getDeviceBrand());  
263.         rowCell.setCellStyle(styles.get("cell"));  
264.           
265.         rowCell = cellRow.createCell(13);  
266.         rowCell.setCellValue(device.getEquipDriveId().getModel());  
267.         rowCell.setCellStyle(styles.get("cell"));  
268.           
269.         rowCell = cellRow.createCell(14);  
270.         rowCell.setCellValue(device.getDeviceCapacity());  
271.         rowCell.setCellStyle(styles.get("cell"));  
272.           
273.         rowCell = cellRow.createCell(15);  
274.         rowCell.setCellValue(device.getSystemType());  
275.         rowCell.setCellStyle(styles.get("cell"));  
276.           
277.         rowCell = cellRow.createCell(16);  
278.         rowCell.setCellValue(device.getStairsNo());  
279.         rowCell.setCellStyle(styles.get("cell"));  
280.           
281.         rowCell = cellRow.createCell(17);  
282.         rowCell.setCellValue(device.getFloor());  
283.         rowCell.setCellStyle(styles.get("cell"));  
284.           
285.         rowCell = cellRow.createCell(18);  
286.         rowCell.setCellValue(device.getRoomNo());  
287.         rowCell.setCellStyle(styles.get("cell"));  
288.           
289.         rowCell = cellRow.createCell(19);  
290.         rowCell.setCellValue(device.getEnableDate()!=null ? new SimpleDateFormat("yyyy-MM-dd").format(device.getEnableDate()) : "");  
291.         rowCell.setCellStyle(styles.get("cell"));  
292.           
293.         rowCell = cellRow.createCell(20);  
294.         rowCell.setCellValue(device.getBackendLoad());  
295.         rowCell.setCellStyle(styles.get("cell"));  
296.           
297.         rowCell = cellRow.createCell(21);  
298.         rowCell.setCellValue(device.getOperationTips());  
299.         rowCell.setCellStyle(styles.get("cell"));  
300.           
301.         rowCell = cellRow.createCell(22);  
302.         rowCell.setCellValue(device.getPersonId().getUserName());  
303.         rowCell.setCellStyle(styles.get("cell"));  
304.           
305.         rowCell = cellRow.createCell(23);  
306.         rowCell.setCellValue(device.getInspecTime()+resources.getMessage(locale, "device.export.excel.minute"));  
307.         rowCell.setCellStyle(styles.get("cell"));  
308.           
309.         rowCell = cellRow.createCell(24);  
310.         rowCell.setCellValue(device.getIntervalTime()+resources.getMessage(locale, "device.export.excel.minute"));  
311.         rowCell.setCellStyle(styles.get("cell"));  
312.           
313.         rowCell = cellRow.createCell(25);  
314.         rowCell.setCellValue(device.getDevicePath());  
315.         rowCell.setCellStyle(styles.get("cell"));  
316.           
317.         rowCell = cellRow.createCell(26);  
318.         rowCell.setCellValue(device.getValidityNr() == 0 ? resources.getMessage(locale, "mmc.soft.person.disabled") : resources.getMessage(locale, "mmc.soft.person.enable"));  
319.         rowCell.setCellStyle(styles.get("cell"));  
320.           
321.         rowCell = cellRow.createCell(27);  
322.         rowCell.setCellValue(device.getOtherInfo() != null ? device.getOtherInfo() : "");  
323.         rowCell.setCellStyle(styles.get("cell"));  
324.     }  
325.     wb.write(sos);  
326.     sos.flush();  
327.     sos.close();  
328.     return null;  
329. }  
330.   
331. //excel样式  
332. private Map<String, CellStyle> createStyles(Workbook wb)  
333. {  
334.     Map<String, CellStyle> styles = new HashMap<String, CellStyle>();  
335.     CellStyle style;  
336.     Font titleFont = wb.createFont();  
337.     titleFont.setFontHeightInPoints((short) 18);  
338.     titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);  
339.     style = wb.createCellStyle();  
340.     style.setAlignment(CellStyle.ALIGN_CENTER);  
341.     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
342.     style.setFont(titleFont);  
343.     styles.put("title", style);  
344.   
345.     style = wb.createCellStyle();  
346.     style.setAlignment(CellStyle.ALIGN_CENTER);  
347.     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
348.     style.setWrapText(true);  
349.     styles.put("header", style);  
350.   
351.     style = wb.createCellStyle();  
352.     style.setAlignment(CellStyle.ALIGN_CENTER);  
353.     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
354.     style.setWrapText(true);  
355.     styles.put("cell", style);  
356.   
357.     style = wb.createCellStyle();  
358.     style.setAlignment(CellStyle.ALIGN_CENTER);  
359.     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
360.     style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());  
361.     style.setFillPattern(CellStyle.SOLID_FOREGROUND);  
362.     style.setDataFormat(wb.createDataFormat().getFormat("0.00"));  
363.     styles.put("formula", style);  
364.   
365.     style = wb.createCellStyle();  
366.     style.setAlignment(CellStyle.ALIGN_CENTER);  
367.     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
368.     style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());  
369.     style.setFillPattern(CellStyle.SOLID_FOREGROUND);  
370.     style.setDataFormat(wb.createDataFormat().getFormat("0.00"));  
371.     styles.put("formula_2", style);  
372.   
373.     return styles;  
374. }