java合并单元格同时导出excel_JAVA_编程开发_程序员俱乐部

POI进行跨行需要用到对象HSSFSheet对象，现在就当我们程序已经定义了一个HSSFSheet对象sheet。?

跨第1行第1个到第2个单元格的操作为?
sheet.addMergedRegion(new Region(0,(short)0,0,(short)1));?

跨第1行第1个到第2行第1个单元格的操作为?
sheet.addMergedRegion(new Region(0,(short)0,1,(short)0));?


重点注意事项：?
1.单元格CELL和ROW对象下标都是从0开始的。?
2.单元格合并时Region(1,2,3,4)第1个值的行号必须要比3位置的行号小，如果大于3就不能正常合并单元格?
3.合并单元格的时候要合并的单单元格必须先创建，这样方便后面再次获取这个单元格来填充数据，主要就是因为合并时不能由后向前进行合并引起的。?

1. import?java.io.IOException;??
2. ??
3. import?org.apache.poi.hssf.usermodel.HSSFCell;??
4. import?org.apache.poi.hssf.usermodel.HSSFCellStyle;??
5. import?org.apache.poi.hssf.usermodel.HSSFRow;??
6. import?org.apache.poi.hssf.usermodel.HSSFSheet;??
7. import?org.apache.poi.hssf.usermodel.HSSFWorkbook;??
8. import?org.apache.poi.hssf.util.Region;??
9. ??
10. ??
11. public?class?ExcelTest?{??????
12. ?????
13. ????/**????
14. ?????*?@param?args????
15. ?????*/?????
16. ????public?static?void?main(String[]?args)?throws?IOException?{??????
17. ?????
18. ????????try?{??????
19. ????????????HSSFWorkbook?wb?=?new?HSSFWorkbook();??????
20. ????????????HSSFSheet?sheet?=?wb.createSheet("new???sheet");??????
21. ????????????HSSFCellStyle?style?=?wb.createCellStyle();?//?样式对象??????
22. ?????
23. ????????????style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//?垂直??????
24. ????????????style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//?水平??????
25. ????????????HSSFRow?row?=?sheet.createRow((short)?0);??????
26. ????????????HSSFRow?row2?=?sheet.createRow((short)?1);??????
27. ?????
28. ????????????sheet.addMergedRegion(new?Region(0,?(short)?0,?1,?(short)?0));??????
29. ????????????HSSFCell?ce?=?row.createCell((short)?0);??????
30. ????????????ce.setEncoding(HSSFCell.ENCODING_UTF_16);//?中文处理??????
31. ????????????ce.setCellValue("项目\\日期");?//?表格的第一行第一列显示的数据??????
32. ????????????ce.setCellStyle(style);?//?样式，居中??????
33. ????????????int?num?=?0;??????
34. ????????????for?(int?i?=?0;?i?<?9;?i++)?{?//?循环9次，每一次都要跨单元格显示??????
35. ????????????????//?计算从那个单元格跨到那一格??????
36. ????????????????int?celln?=?0;??????
37. ????????????????int?celle?=?0;??????
38. ????????????????if?(i?==?0)?{??????
39. ????????????????????celln?=?0;??????
40. ????????????????????celle?=?1;??????
41. ????????????????}?else?{??????
42. ????????????????????celln?=?(i?*?2);??????
43. ????????????????????celle?=?(i?*?2?+?1);??????
44. ????????????????}??????
45. ????????????????//?单元格合并??????
46. ????????????????//?四个参数分别是：起始行，起始列，结束行，结束列??????
47. ????????????????sheet.addMergedRegion(new?Region(0,?(short)?(celln?+?1),?0,??????
48. ????????????????????????(short)?(celle?+?1)));??????
49. ????????????????HSSFCell?cell?=?row.createCell((short)?(celln?+?1));??????
50. ????????????????cell.setCellValue("merging"?+?i);?//?跨单元格显示的数据??????
51. ????????????????cell.setCellStyle(style);?//?样式??????
52. ????????????????//?不跨单元格显示的数据，如：分两行，上一行分别两格为一格，下一行就为两格，“数量”，“金额”??????
53. ????????????????HSSFCell?cell1?=?row2.createCell((short)?celle);??????
54. ????????????????HSSFCell?cell2?=?row2.createCell((short)?(celle?+?1));??????
55. ????????????????cell1.setEncoding(HSSFCell.ENCODING_UTF_16);??????
56. ????????????????cell1.setCellValue("数量");??????
57. ????????????????cell1.setCellStyle(style);??????
58. ????????????????cell2.setEncoding(HSSFCell.ENCODING_UTF_16);??????
59. ????????????????cell2.setCellValue("金额");??????
60. ????????????????cell2.setCellStyle(style);??????
61. ????????????????num++;??????
62. ????????????}??????
63. ?????
64. ????????????//?在后面加上合计百分比??????
65. ?????
66. ????????????//?合计?在最后加上，还要跨一个单元格??????
67. ????????????sheet.addMergedRegion(new?Region(0,?(short)?(2?*?num?+?1),?0,??????
68. ????????????????????(short)?(2?*?num?+?2)));??????
69. ????????????HSSFCell?cell?=?row.createCell((short)?(2?*?num?+?1));??????
70. ????????????cell.setEncoding(HSSFCell.ENCODING_UTF_16);??????
71. ????????????cell.setCellValue("合计");??????
72. ????????????cell.setCellStyle(style);??????
73. ????????????HSSFCell?cell1?=?row2.createCell((short)?(2?*?num?+?1));??????
74. ????????????HSSFCell?cell2?=?row2.createCell((short)?(2?*?num?+?2));??????
75. ????????????cell1.setEncoding(HSSFCell.ENCODING_UTF_16);??????
76. ????????????cell1.setCellValue("数量");??????
77. ????????????cell1.setCellStyle(style);??????
78. ????????????cell2.setEncoding(HSSFCell.ENCODING_UTF_16);??????
79. ????????????cell2.setCellValue("金额");??????
80. ????????????cell2.setCellStyle(style);??????
81. ?????
82. ????????????//?百分比?同上??????
83. ????????????sheet.addMergedRegion(new?Region(0,?(short)?(2?*?num?+?3),?0,??????
84. ????????????????????(short)?(2?*?num?+?4)));??????
85. ????????????HSSFCell?cellb?=?row.createCell((short)?(2?*?num?+?3));??????
86. ????????????cellb.setEncoding(HSSFCell.ENCODING_UTF_16);??????
87. ?????????????
88. ????????????cellb.setCellValue("百分比");??????
89. ????????????cellb.setCellStyle(style);??????
90. ??????????????
91. ????????????HSSFCell?cellb1?=?row2.createCell((short)?(2?*?num?+?3));??????
92. ????????????HSSFCell?cellb2?=?row2.createCell((short)?(2?*?num?+?4));??????
93. ????????????cellb1.setEncoding(HSSFCell.ENCODING_UTF_16);??????
94. ????????????cellb1.setCellValue("数量");??????
95. ????????????cellb1.setCellStyle(style);??????
96. ????????????cellb2.setEncoding(HSSFCell.ENCODING_UTF_16);??????
97. ????????????cellb2.setCellValue("金额");??????
98. ????????????cellb2.setCellStyle(style);??????
99. ?????
100. ????????????/***这里是问题的关键，将这个工作簿写入到一个流中就可以输出相应的名字，这里需要写路径就ok了。?
101. ????????????FileOutputStream?fileOut?=?new?FileOutputStream("workbook.xls");?????
102. ????????????wb.write(fileOut);?????
103. ????????????fileOut.close();?
104. ?????????????**/??
105. ??????????????
106. ??????????????
107. ????????????/**第二种是输出到也面中的excel名称?
108. ?????????????*?pName="栏目统计表";????
109. ????response.reset();????
110. ????response.setContentType("application/x-msdownload");????
111. ????response.setHeader("Content-Disposition","attachment;?filename="+new?String(pName.getBytes("gb2312"),"ISO-8859-1")+".xls");????
112. ????ServletOutputStream?outStream=null;????
113. ???
114. ????try{????
115. ????????outStream?=?response.getOutputStream();????
116. ????????wb.write(outStream);????
117. ????}catch(Exception?e)????
118. ????{????
119. ?????e.printStackTrace();????
120. ????}finally{????
121. ????????outStream.close();????
122. ????}????
123. ?????????????*?*/??
124. ????????????System.out.print("OK");??????
125. ????????}?catch?(Exception?ex)?{??????
126. ????????????ex.printStackTrace();??????
127. ????????}??????
128. ?????
129. ????}??????
130. ?????
131. } ? ?