java导出Excel合并单元格

原文地址https://blog.csdn.net/datangxiajun/article/details/78308979

网上java导出excel表格并合并单元格的资料不完全，我整理了一份，并亲测能用，附截图。

①java导出excel用到POI所有jar包，大家可以直接到下面地址下载点击打开链接

②模拟数据类

[java]  view plain  copy

1. package org;  
2.   
3. public class WorkSheetDetail {  
4.     //工作内容  
5.     private String workCtx;  
6.     // 用工人总数    工日数 = gwnNum+tmnNum  
7.     private Float totalHumanDays;  
8.     //普工用工数 1-4小时为半天，4-8小时为一天;120每天  
9.     private Integer gwnNum;  
10.     //技工用工数 1-4小时为半天，4-8小时为一天；160每天  
11.     private Integer tmnNum;  
12.     // 单价（元）  
13.     private Float unitPrice;  
14.     // 金额(元） = gwnNum*120+tmnNum+160  
15.     private Float unitAmount;  
16.     // 备注  
17.     private String notes;   
18.       
19.     public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,  
20.             Float unitAmount, String notes) {  
21.         super();  
22.         this.workCtx = workCtx;  
23.         this.totalHumanDays = totalHumanDays;  
24.         this.gwnNum = gwnNum;  
25.         this.tmnNum = tmnNum;  
26.         this.unitPrice = unitPrice;  
27.         this.unitAmount = unitAmount;  
28.         this.notes = notes;  
29.     }  
30.     public String getWorkCtx() {  
31.         return workCtx;  
32.     }  
33.     public void setWorkCtx(String workCtx) {  
34.         this.workCtx = workCtx;  
35.     }  
36.     public Float getTotalHumanDays() {  
37.         return totalHumanDays;  
38.     }  
39.     public void setTotalHumanDays(Float totalHumanDays) {  
40.         this.totalHumanDays = totalHumanDays;  
41.     }  
42.     public Integer getGwnNum() {  
43.         return gwnNum;  
44.     }  
45.     public void setGwnNum(Integer gwnNum) {  
46.         this.gwnNum = gwnNum;  
47.     }  
48.     public Integer getTmnNum() {  
49.         return tmnNum;  
50.     }  
51.     public void setTmnNum(Integer tmnNum) {  
52.         this.tmnNum = tmnNum;  
53.     }  
54.     public Float getUnitPrice() {  
55.         return unitPrice;  
56.     }  
57.     public void setUnitPrice(Float unitPrice) {  
58.         this.unitPrice = unitPrice;  
59.     }  
60.     public Float getUnitAmount() {  
61.         return unitAmount;  
62.     }  
63.     public void setUnitAmount(Float unitAmount) {  
64.         this.unitAmount = unitAmount;  
65.     }  
66.     public String getNotes() {  
67.         return notes;  
68.     }  
69.     public void setNotes(String notes) {  
70.         this.notes = notes;  
71.     }  
72.       
73. }  

③java导出excel方法

[java]  view plain  copy

1. package org;  
2. import java.io.FileOutputStream;  
3. import java.util.List;  
4. import org.apache.poi.hssf.usermodel.HSSFCell;  
5. import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
6. import org.apache.poi.hssf.usermodel.HSSFFont;  
7. import org.apache.poi.hssf.usermodel.HSSFHeader;  
8. import org.apache.poi.hssf.usermodel.HSSFRow;  
9. import org.apache.poi.hssf.usermodel.HSSFSheet;  
10. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
11. import org.apache.poi.ss.util.CellRangeAddress;  
12. public class ExportExcel {  
13.     public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){  
14.           try{  
15.                 //1.创建工作簿  
16.                 HSSFWorkbook workbook = new HSSFWorkbook();  
17.                 //1.1创建合并单元格对象  
18.                 CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列  
19.                 CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);//起始行,结束行,起始列,结束列  
20.                 //班组与时间start  
21.                 CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);//起始行,结束行,起始列,结束列  
22.                 CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);//起始行,结束行,起始列,结束列  
23.                 CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);//起始行,结束行,起始列,结束列  
24.                 //班组与时间end  
25.                   
26.                 //标题  
27.                 CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0);//起始行,结束行,起始列,结束列  
28.                 CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1);//起始行,结束行,起始列,结束列  
29.                 CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2);//起始行,结束行,起始列,结束列  
30.                 CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4);//起始行,结束行,起始列,结束列  
31.                 CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5);//起始行,结束行,起始列,结束列  
32.                 CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6);//起始行,结束行,起始列,结束列  
33.                 CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7);//起始行,结束行,起始列,结束列  
34.                   
35.                 //金额  
36.                 CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2);//起始行,结束行,起始列,结束列  
37.                 CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7);//起始行,结束行,起始列,结束列  
38.   
39.                 //负责人  
40.                 CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2);//起始行,结束行,起始列,结束列  
41.                 CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4);//起始行,结束行,起始列,结束列  
42.                 CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7);//起始行,结束行,起始列,结束列  
43.   
44.                 //说明  
45.                 CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7);//起始行,结束行,起始列,结束列  
46.                 CellRangeAddress callRangeAddressinfo1 = new CellRangeAddress(userList.size()+8,userList.size()+8,0,7);//起始行,结束行,起始列,结束列  
47.                 CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7);//起始行,结束行,起始列,结束列  
48.   
49.                 //部项目经理部  
50.                 HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);  
51.                 //派工单  
52.                 HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true);  
53.                 //班组和时间  
54.                 HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);  
55.                 //标题样式  
56.                 HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true);  
57.                 //内容样式  
58.                 HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);  
59.                 //2.创建工作表  
60.                 HSSFSheet sheet = workbook.createSheet("派单");  
61.                 //2.1加载合并单元格对象  
62.                 sheet.addMergedRegion(callRangeAddress);  
63.                 sheet.addMergedRegion(callRangeAddress1);  
64.                 sheet.addMergedRegion(callRangeAddress20);  
65.                 sheet.addMergedRegion(callRangeAddress21);  
66.                 sheet.addMergedRegion(callRangeAddress22);  
67.                 sheet.addMergedRegion(callRangeAddress31);  
68.                 sheet.addMergedRegion(callRangeAddress32);  
69.                 sheet.addMergedRegion(callRangeAddress33);  
70.                 sheet.addMergedRegion(callRangeAddress34);  
71.                 sheet.addMergedRegion(callRangeAddress35);  
72.                 sheet.addMergedRegion(callRangeAddress36);  
73.                 sheet.addMergedRegion(callRangeAddress37);  
74.                 sheet.addMergedRegion(callRangeAddressnumber1);  
75.                 sheet.addMergedRegion(callRangeAddressnumber2);  
76.                 sheet.addMergedRegion(callRangeAddressPersion1);  
77.                 sheet.addMergedRegion(callRangeAddressPersion2);  
78.                 sheet.addMergedRegion(callRangeAddressPersion3);  
79.                 sheet.addMergedRegion(callRangeAddressinfo);  
80.                 sheet.addMergedRegion(callRangeAddressinfo1);  
81.                 sheet.addMergedRegion(callRangeAddressinfo2);  
82.                 //设置默认列宽  
83.                 sheet.setDefaultColumnWidth(15);  
84.                 //3.创建行  
85.                 //3.1创建头标题行;并且设置头标题  
86.                 HSSFRow row = sheet.createRow(0);  
87.                 HSSFCell cell = row.createCell(0);  
88.                 //加载单元格样式  
89.                 cell.setCellStyle(headStyle);  
90.                 cell.setCellValue("xxxx项目部");  
91.                   
92.                 HSSFRow rower = sheet.createRow(1);  
93.                 HSSFCell celler = rower.createCell(0);  
94.                 //加载单元格样式  
95.                 celler.setCellStyle(erStyle);  
96.                 celler.setCellValue("派 工 单");  
97.                   
98.                 HSSFRow rowsan = sheet.createRow(2);  
99.                 HSSFCell cellsan = rowsan.createCell(0);  
100.                 HSSFCell cellsan1 = rowsan.createCell(3);  
101.                 HSSFCell cellsan2 = rowsan.createCell(5);  
102.                 //加载单元格样式  
103.                 cellsan.setCellStyle(sanStyle);  
104.                 cellsan.setCellValue("协作单位：x施工一堆");  
105.                 cellsan1.setCellStyle(sanStyle);  
106.                 cellsan1.setCellValue("");  
107.                 cellsan2.setCellStyle(sanStyle);  
108.                 cellsan2.setCellValue("时间：2017年 10月 20日");  
109.                   
110.                 //3.2创建列标题;并且设置列标题  
111.                 HSSFRow row2 = sheet.createRow(3);  
112.                 String[] titles = {"序号","工作内容","用工总人数","工日数","","单价（元）","金额(元）","备注"};//""为占位字符串  
113.                 for(int i=0;i<titles.length;i++)  
114.                 {  
115.                     HSSFCell cell2 = row2.createCell(i);  
116.                     //加载单元格样式  
117.                     cell2.setCellStyle(colStyle);  
118.                     cell2.setCellValue(titles[i]);  
119.                 }  
120.                   
121.                 HSSFRow rowfour = sheet.createRow(4);  
122.                 String[] titlefour = {"普工用工数","技工用工数"};  
123.                 for(int i=0;i<titlefour.length;i++)  
124.                 {  
125.                     HSSFCell cell2 = rowfour.createCell(i+3);  
126.                     //加载单元格样式  
127.                     cell2.setCellStyle(colStyle);  
128.                     cell2.setCellValue(titlefour[i]);  
129.                 }  
130.                   
131.                   
132.                 //4.操作单元格;将用户列表写入excel  
133.                 if(userList != null)  
134.                 {  
135.                     int i=1;  
136.                     for(int j=0;j<userList.size();j++)  
137.                     {  
138.                         //创建数据行,前面有两行,头标题行和列标题行  
139.                         HSSFRow row3 = sheet.createRow(j+5);  
140.                         HSSFCell cell0 = row3.createCell(0);  
141.                         cell0.setCellStyle(cellStyle);  
142.                         cell0.setCellValue(i++);  
143.                           
144.                         HSSFCell cell1 = row3.createCell(1);  
145.                         cell1.setCellStyle(cellStyle);  
146.                         cell1.setCellValue(userList.get(j).getWorkCtx());  
147.                           
148.                         HSSFCell cell2 = row3.createCell(2);  
149.                         cell2.setCellStyle(cellStyle);  
150.                         cell2.setCellValue(userList.get(j).getTotalHumanDays());  
151.                           
152.                         HSSFCell cell3 = row3.createCell(3);  
153.                         cell3.setCellStyle(cellStyle);  
154.                         cell3.setCellValue(userList.get(j).getGwnNum());  
155.                           
156.                         HSSFCell cell4 = row3.createCell(4);  
157.                         cell4.setCellStyle(cellStyle);  
158.                         cell4.setCellValue(userList.get(j).getTmnNum());  
159.                           
160.                         HSSFCell cell5 = row3.createCell(5);  
161.                         cell5.setCellStyle(cellStyle);  
162.                         cell5.setCellValue(userList.get(j).getTotalHumanDays());  
163.                           
164.                         HSSFCell cell6 = row3.createCell(6);  
165.                         cell6.setCellStyle(cellStyle);  
166.                         cell6.setCellValue(userList.get(j).getUnitAmount());  
167.                           
168.                         HSSFCell cell7= row3.createCell(7);  
169.                         cell7.setCellStyle(cellStyle);  
170.                         cell7.setCellValue(userList.get(j).getUnitPrice());  
171.                     }  
172.                 }  
173.                   
174.                 HSSFRow rownumber = sheet.createRow(userList.size()+5);  
175.                 HSSFCell cellnumber = rownumber.createCell(0);  
176.                 HSSFCell cellnumber1 = rownumber.createCell(3);  
177.                 //加载单元格样式  
178.                 cellnumber.setCellStyle(sanStyle);  
179.                 cellnumber.setCellValue("金额合计(大写)");  
180.                 cellnumber1.setCellStyle(sanStyle);  
181.                 cellnumber1.setCellValue("￥ 78 元； 大写：柒拾捌元整");  
182.   
183.                 HSSFRow rowpersion = sheet.createRow(userList.size()+6);  
184.                 HSSFCell cellpersion = rowpersion.createCell(0);  
185.                 HSSFCell cellpersion1 = rowpersion.createCell(3);  
186.                 HSSFCell cellpersion2 = rowpersion.createCell(5);  
187.   
188.                 //加载单元格样式  
189.                 cellpersion.setCellStyle(sanStyle);  
190.                 cellpersion.setCellValue("协作单位负责人：");  
191.                 cellpersion1.setCellStyle(sanStyle);  
192.                 cellpersion1.setCellValue("经办人：");  
193.                 cellpersion2.setCellStyle(sanStyle);  
194.                 cellpersion2.setCellValue("部门负责人：");  
195.                   
196.                 HSSFRow rowinfo = sheet.createRow(userList.size()+7);  
197.                 HSSFCell cellinfo = rowinfo.createCell(0);  
198.                 cellinfo.setCellStyle(sanStyle);  
199.                 cellinfo.setCellValue("说明：1、本标工单一式两联，第一联为派工人（工长）存根，第二联用作结算。");  
200.                   
201.                 HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);  
202.                 HSSFCell cellinfo1 = rowinfo1.createCell(0);  
203.                 cellinfo1.setCellStyle(sanStyle);  
204.                 cellinfo1.setCellValue("2、本标工单必须在用工当日签认，否则不予认可；三日内交合同处汇总。");  
205.                   
206.                 HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);  
207.                 HSSFCell cellinfo2 = rowinfo2.createCell(0);  
208.                 cellinfo2.setCellStyle(sanStyle);  
209.                 cellinfo2.setCellValue("3、工日数填写精确到半个工日。");  
210.                 //5.输出  
211.                 workbook.write(fout);  
212. //              workbook.close();  
213.                 //out.close();  
214.             }catch(Exception e)  
215.             {  
216.                 e.printStackTrace();  
217.             }  
218.     }  
219.       
220.     /** 
221.      *  
222.      * @param workbook 
223.      * @param fontsize 
224.      * @return 单元格样式 
225.      */  
226.     private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {  
227.         // TODO Auto-generated method stub  
228.         HSSFCellStyle style = workbook.createCellStyle();  
229.         //是否水平居中  
230.         if(flag1){  
231.             style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中  
232.         }  
233.          
234.         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中  
235.         //创建字体  
236.         HSSFFont font = workbook.createFont();  
237.         //是否加粗字体  
238.         if(flag){  
239.             font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
240.         }  
241.         font.setFontHeightInPoints(fontsize);  
242.         //加载字体  
243.         style.setFont(font);  
244.         return style;  
245.     }  
246. }  

④main方法

[java]  view plain  copy

1. package org;  
2.   
3. import java.io.FileOutputStream;  
4. import java.util.ArrayList;  
5. import java.util.List;  
6.   
7. public class MainOut {  
8.     public static void main(String args[]){  
9.         //模拟部分数据  
10.         List<WorkSheetDetail> detail = new ArrayList<WorkSheetDetail>();  
11.   
12.         WorkSheetDetail d1 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");  
13.         WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");  
14.         WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");  
15.         WorkSheetDetail d4 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");  
16.         WorkSheetDetail d5 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");  
17.         detail.add(d1);  
18.         detail.add(d2);  
19.         detail.add(d3);  
20.         detail.add(d4);  
21.         detail.add(d5);  
22.         try    
23.         {    
24.             FileOutputStream fout = new FileOutputStream("E:/students.xls");  
25.             new ExportExcel().getValue(detail, fout);  
26.             fout.close();    
27.         }    
28.         catch (Exception e)    
29.         {    
30.             e.printStackTrace();    
31.         }    
32.   
33.     }  
34. }  

⑤截图