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

论坛 › 期权论坛 › 脚本     
匿名技术用户   2021-1-2 18:43   32   0
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"));
  55. // 创建表头
  56. Row headerRow = sheet.createRow(0);
  57. headerRow.setHeightInPoints(30);
  58. Cell headerCell;
  60. headerCell = headerRow.createCell(0);
  61. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.number")); //设备编号
  62. headerCell.setCellStyle(styles.get("header"));
  64. headerCell = headerRow.createCell(1);
  65. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.qrcode")); //设备二维码
  66. headerCell.setCellStyle(styles.get("header"));
  68. headerCell = headerRow.createCell(2);
  69. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customerbase")); //客户群
  70. headerCell.setCellStyle(styles.get("header"));
  72. headerCell = headerRow.createCell(3);
  73. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customertype")); //客户类别
  74. headerCell.setCellStyle(styles.get("header"));
  76. headerCell = headerRow.createCell(4);
  77. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.customername")); //客户名称
  78. headerCell.setCellStyle(styles.get("header"));
  80. headerCell = headerRow.createCell(5);
  81. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.area")); //设备区域
  82. headerCell.setCellStyle(styles.get("header"));
  84. headerCell = headerRow.createCell(8);
  85. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.itemname")); //所属项目名称
  86. headerCell.setCellStyle(styles.get("header"));
  88. headerCell = headerRow.createCell(9);
  89. headerCell.setCellValue(resources.getMessage(locale, "device.category")); //设备类别
  90. headerCell.setCellStyle(styles.get("header"));
  92. headerCell = headerRow.createCell(10);
  93. headerCell.setCellValue(resources.getMessage(locale, "device.name")); //设备名称
  94. headerCell.setCellStyle(styles.get("header"));
  96. headerCell = headerRow.createCell(11);
  97. headerCell.setCellValue(resources.getMessage(locale, "device.no")); //设备信息编号
  98. headerCell.setCellStyle(styles.get("header"));
  100. headerCell = headerRow.createCell(12);
  101. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.baseinfomation")); //设备基本信息
  102. headerCell.setCellStyle(styles.get("header"));
  104. headerCell = headerRow.createCell(16);
  105. headerCell.setCellValue(resources.getMessage(locale, "device.location")); //设备位置
  106. headerCell.setCellStyle(styles.get("header"));
  108. headerCell = headerRow.createCell(19);
  109. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.enabledate")); //设备启用日期
  110. headerCell.setCellStyle(styles.get("header"));
  112. headerCell = headerRow.createCell(20);
  113. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.backendload")); //后端负载
  114. headerCell.setCellStyle(styles.get("header"));
  116. headerCell = headerRow.createCell(21);
  117. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.operationtips")); //操作提示
  118. headerCell.setCellStyle(styles.get("header"));
  120. headerCell = headerRow.createCell(22);
  121. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.maintenancepeople")); //维护责任人
  122. headerCell.setCellStyle(styles.get("header"));
  124. headerCell = headerRow.createCell(23);
  125. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.usetime")); //巡检时间
  126. headerCell.setCellStyle(styles.get("header"));
  128. headerCell = headerRow.createCell(24);
  129. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.intervaltime")); //巡检下台设备时间间隔
  130. headerCell.setCellStyle(styles.get("header"));
  132. headerCell = headerRow.createCell(25);
  133. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.patrolpath")); //巡检路径
  134. headerCell.setCellStyle(styles.get("header"));
  136. headerCell = headerRow.createCell(26);
  137. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.enable")); //是否启用巡检路径
  138. headerCell.setCellStyle(styles.get("header"));
  140. headerCell = headerRow.createCell(27);
  141. headerCell.setCellValue(resources.getMessage(locale, "device.export.excel.other")); //其他信息
  142. headerCell.setCellStyle(styles.get("header"));
  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"));
  152. headerCellRegion = headerRowRegion.createCell(6);
  153. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.region")); //市
  154. headerCellRegion.setCellStyle(styles.get("header"));
  156. headerCellRegion = headerRowRegion.createCell(7);
  157. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.county")); //县/区
  158. headerCellRegion.setCellStyle(styles.get("header"));
  160. headerCellRegion = headerRowRegion.createCell(12);
  161. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.brand")); //品牌
  162. headerCellRegion.setCellStyle(styles.get("header"));
  164. headerCellRegion = headerRowRegion.createCell(13);
  165. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.model")); //型号
  166. headerCellRegion.setCellStyle(styles.get("header"));
  168. headerCellRegion = headerRowRegion.createCell(14);
  169. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.capacity")); //容量
  170. headerCellRegion.setCellStyle(styles.get("header"));
  172. headerCellRegion = headerRowRegion.createCell(15);
  173. headerCellRegion.setCellValue(resources.getMessage(locale, "device.export.excel.systemtype")); //系统类型
  174. headerCellRegion.setCellStyle(styles.get("header"));
  176. headerCellRegion = headerRowRegion.createCell(16);
  177. headerCellRegion.setCellValue(resources.getMessage(locale, "device.build")); //楼号
  178. headerCellRegion.setCellStyle(styles.get("header"));
  180. headerCellRegion = headerRowRegion.createCell(17);
  181. headerCellRegion.setCellValue(resources.getMessage(locale, "device.floor")); //楼层
  182. headerCellRegion.setCellStyle(styles.get("header"));
  184. headerCellRegion = headerRowRegion.createCell(18);
  185. headerCellRegion.setCellValue(resources.getMessage(locale, "device.room")); //房间号
  186. headerCellRegion.setCellStyle(styles.get("header"));
  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);
  193. rowCell = cellRow.createCell(0);
  194. rowCell.setCellValue(device.getDeviceId());
  195. rowCell.setCellStyle(styles.get("cell"));
  197. rowCell = cellRow.createCell(1);
  198. rowCell.setCellValue(device.getQrcode());
  199. rowCell.setCellStyle(styles.get("cell"));
  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"));
  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"));
  219. rowCell = cellRow.createCell(4);
  220. rowCell.setCellValue(device.getItemId().getCustomerName());
  221. rowCell.setCellStyle(styles.get("cell"));
  223. rowCell = cellRow.createCell(5);
  224. rowCell.setCellValue(device.getItemId().getProvince());
  225. rowCell.setCellStyle(styles.get("cell"));
  227. rowCell = cellRow.createCell(6);
  228. rowCell.setCellValue(device.getItemId().getCity());
  229. rowCell.setCellStyle(styles.get("cell"));
  231. rowCell = cellRow.createCell(7);
  232. rowCell.setCellValue(device.getItemId().getCounty());
  233. rowCell.setCellStyle(styles.get("cell"));
  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"));
  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"));
  253. rowCell = cellRow.createCell(10);
  254. rowCell.setCellValue(device.getDeviceName());
  255. rowCell.setCellStyle(styles.get("cell"));
  257. rowCell = cellRow.createCell(11);
  258. rowCell.setCellValue(device.getDeviceNo());
  259. rowCell.setCellStyle(styles.get("cell"));
  261. rowCell = cellRow.createCell(12);
  262. rowCell.setCellValue(device.getDeviceBrand());
  263. rowCell.setCellStyle(styles.get("cell"));
  265. rowCell = cellRow.createCell(13);
  266. rowCell.setCellValue(device.getEquipDriveId().getModel());
  267. rowCell.setCellStyle(styles.get("cell"));
  269. rowCell = cellRow.createCell(14);
  270. rowCell.setCellValue(device.getDeviceCapacity());
  271. rowCell.setCellStyle(styles.get("cell"));
  273. rowCell = cellRow.createCell(15);
  274. rowCell.setCellValue(device.getSystemType());
  275. rowCell.setCellStyle(styles.get("cell"));
  277. rowCell = cellRow.createCell(16);
  278. rowCell.setCellValue(device.getStairsNo());
  279. rowCell.setCellStyle(styles.get("cell"));
  281. rowCell = cellRow.createCell(17);
  282. rowCell.setCellValue(device.getFloor());
  283. rowCell.setCellStyle(styles.get("cell"));
  285. rowCell = cellRow.createCell(18);
  286. rowCell.setCellValue(device.getRoomNo());
  287. rowCell.setCellStyle(styles.get("cell"));
  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"));
  293. rowCell = cellRow.createCell(20);
  294. rowCell.setCellValue(device.getBackendLoad());
  295. rowCell.setCellStyle(styles.get("cell"));
  297. rowCell = cellRow.createCell(21);
  298. rowCell.setCellValue(device.getOperationTips());
  299. rowCell.setCellStyle(styles.get("cell"));
  301. rowCell = cellRow.createCell(22);
  302. rowCell.setCellValue(device.getPersonId().getUserName());
  303. rowCell.setCellStyle(styles.get("cell"));
  305. rowCell = cellRow.createCell(23);
  306. rowCell.setCellValue(device.getInspecTime()+resources.getMessage(locale, "device.export.excel.minute"));
  307. rowCell.setCellStyle(styles.get("cell"));
  309. rowCell = cellRow.createCell(24);
  310. rowCell.setCellValue(device.getIntervalTime()+resources.getMessage(locale, "device.export.excel.minute"));
  311. rowCell.setCellStyle(styles.get("cell"));
  313. rowCell = cellRow.createCell(25);
  314. rowCell.setCellValue(device.getDevicePath());
  315. rowCell.setCellStyle(styles.get("cell"));
  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"));
  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. }
  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);
  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);
  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);
  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);
  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);
  373. return styles;
  374. }
回复
分享到 :
0 人收藏
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

16级独孤
匿名技术用户

积分:7942463
帖子:1588486
精华:0
+ 关注 私信
期权论坛 期权论坛
期权网络科技版权所有
关于我们
联系我们
加入我们
反馈问题
免责声明
积分充值
统一社会信用代码
积分规则
网站地图
爱文库
下属网站
官方
新浪微博
微信公众号
◆
下载
表情包
App下载
期权论坛 期权论坛

QQ咨询|关于我们|Archiver|手机版|小黑屋|( 辽ICP备15012455号-4 ) Powered by 期权论坛 X3.2 © 2001-2016 期权工具网&期权论坛 Inc.

发布
内容

下载期权论坛手机APP