java导出Excel合并单元格

104 阅读 0 评论 69 点赞

我是靠谱客的博主鳗鱼蜗牛，这篇文章主要介绍java导出Excel合并单元格，现在分享给大家，希望可以做个参考。

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

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

②模拟数据类

复制代码

package org;

public class WorkSheetDetail {
    //工作内容
    private String workCtx;
    // 用工人总数	工日数 = gwnNum+tmnNum
    private Float totalHumanDays;
    //普工用工数 1-4小时为半天，4-8小时为一天;120每天
    private Integer gwnNum;
    //技工用工数 1-4小时为半天，4-8小时为一天；160每天
    private Integer tmnNum;
    // 单价（元）
    private Float unitPrice;
    // 金额(元） = gwnNum*120+tmnNum+160
    private Float unitAmount;
    // 备注
    private String notes; 
    
	public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,
			Float unitAmount, String notes) {
		super();
		this.workCtx = workCtx;
		this.totalHumanDays = totalHumanDays;
		this.gwnNum = gwnNum;
		this.tmnNum = tmnNum;
		this.unitPrice = unitPrice;
		this.unitAmount = unitAmount;
		this.notes = notes;
	}
	public String getWorkCtx() {
		return workCtx;
	}
	public void setWorkCtx(String workCtx) {
		this.workCtx = workCtx;
	}
	public Float getTotalHumanDays() {
		return totalHumanDays;
	}
	public void setTotalHumanDays(Float totalHumanDays) {
		this.totalHumanDays = totalHumanDays;
	}
	public Integer getGwnNum() {
		return gwnNum;
	}
	public void setGwnNum(Integer gwnNum) {
		this.gwnNum = gwnNum;
	}
	public Integer getTmnNum() {
		return tmnNum;
	}
	public void setTmnNum(Integer tmnNum) {
		this.tmnNum = tmnNum;
	}
	public Float getUnitPrice() {
		return unitPrice;
	}
	public void setUnitPrice(Float unitPrice) {
		this.unitPrice = unitPrice;
	}
	public Float getUnitAmount() {
		return unitAmount;
	}
	public void setUnitAmount(Float unitAmount) {
		this.unitAmount = unitAmount;
	}
	public String getNotes() {
		return notes;
	}
	public void setNotes(String notes) {
		this.notes = notes;
	}
    
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
package org;

public class WorkSheetDetail {
    //工作内容
    private String workCtx;
    // 用工人总数	工日数 = gwnNum+tmnNum
    private Float totalHumanDays;
    //普工用工数 1-4小时为半天，4-8小时为一天;120每天
    private Integer gwnNum;
    //技工用工数 1-4小时为半天，4-8小时为一天；160每天
    private Integer tmnNum;
    // 单价（元）
    private Float unitPrice;
    // 金额(元） = gwnNum*120+tmnNum+160
    private Float unitAmount;
    // 备注
    private String notes; 
    
	public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,
			Float unitAmount, String notes) {
		super();
		this.workCtx = workCtx;
		this.totalHumanDays = totalHumanDays;
		this.gwnNum = gwnNum;
		this.tmnNum = tmnNum;
		this.unitPrice = unitPrice;
		this.unitAmount = unitAmount;
		this.notes = notes;
	}
	public String getWorkCtx() {
		return workCtx;
	}
	public void setWorkCtx(String workCtx) {
		this.workCtx = workCtx;
	}
	public Float getTotalHumanDays() {
		return totalHumanDays;
	}
	public void setTotalHumanDays(Float totalHumanDays) {
		this.totalHumanDays = totalHumanDays;
	}
	public Integer getGwnNum() {
		return gwnNum;
	}
	public void setGwnNum(Integer gwnNum) {
		this.gwnNum = gwnNum;
	}
	public Integer getTmnNum() {
		return tmnNum;
	}
	public void setTmnNum(Integer tmnNum) {
		this.tmnNum = tmnNum;
	}
	public Float getUnitPrice() {
		return unitPrice;
	}
	public void setUnitPrice(Float unitPrice) {
		this.unitPrice = unitPrice;
	}
	public Float getUnitAmount() {
		return unitAmount;
	}
	public void setUnitAmount(Float unitAmount) {
		this.unitAmount = unitAmount;
	}
	public String getNotes() {
		return notes;
	}
	public void setNotes(String notes) {
		this.notes = notes;
	}
    
}

③java导出excel方法

复制代码

package org;
import java.io.FileOutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExportExcel {
	public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){
		  try{
	            //1.创建工作簿
	            HSSFWorkbook workbook = new HSSFWorkbook();
	            //1.1创建合并单元格对象
	            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);//起始行,结束行,起始列,结束列
	            //班组与时间start
	            CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);//起始行,结束行,起始列,结束列
	            //班组与时间end
	            
	            //标题
	            CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7);//起始行,结束行,起始列,结束列
	            
	            //金额
	            CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7);//起始行,结束行,起始列,结束列

//负责人
	            CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7);//起始行,结束行,起始列,结束列

//说明
	            CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressinfo1 = new CellRangeAddress(userList.size()+8,userList.size()+8,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7);//起始行,结束行,起始列,结束列

//部项目经理部
	            HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);
	            //派工单
	            HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true);
	            //班组和时间
	            HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);
	            //标题样式
	            HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true);
	            //内容样式
	            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);
	            //2.创建工作表
	            HSSFSheet sheet = workbook.createSheet("派单");
	            //2.1加载合并单元格对象
	            sheet.addMergedRegion(callRangeAddress);
	            sheet.addMergedRegion(callRangeAddress1);
	            sheet.addMergedRegion(callRangeAddress20);
	            sheet.addMergedRegion(callRangeAddress21);
	            sheet.addMergedRegion(callRangeAddress22);
	            sheet.addMergedRegion(callRangeAddress31);
	            sheet.addMergedRegion(callRangeAddress32);
	            sheet.addMergedRegion(callRangeAddress33);
	            sheet.addMergedRegion(callRangeAddress34);
	            sheet.addMergedRegion(callRangeAddress35);
	            sheet.addMergedRegion(callRangeAddress36);
	            sheet.addMergedRegion(callRangeAddress37);
	            sheet.addMergedRegion(callRangeAddressnumber1);
	            sheet.addMergedRegion(callRangeAddressnumber2);
	            sheet.addMergedRegion(callRangeAddressPersion1);
	            sheet.addMergedRegion(callRangeAddressPersion2);
	            sheet.addMergedRegion(callRangeAddressPersion3);
	            sheet.addMergedRegion(callRangeAddressinfo);
	            sheet.addMergedRegion(callRangeAddressinfo1);
	            sheet.addMergedRegion(callRangeAddressinfo2);
	            //设置默认列宽
	            sheet.setDefaultColumnWidth(15);
	            //3.创建行
	            //3.1创建头标题行;并且设置头标题
	            HSSFRow row = sheet.createRow(0);
	            HSSFCell cell = row.createCell(0);
	            //加载单元格样式
	            cell.setCellStyle(headStyle);
	            cell.setCellValue("xxxx项目部");
	            
	            HSSFRow rower = sheet.createRow(1);
	            HSSFCell celler = rower.createCell(0);
	            //加载单元格样式
	            celler.setCellStyle(erStyle);
	            celler.setCellValue("派 工 单");
	            
	            HSSFRow rowsan = sheet.createRow(2);
	            HSSFCell cellsan = rowsan.createCell(0);
	            HSSFCell cellsan1 = rowsan.createCell(3);
	            HSSFCell cellsan2 = rowsan.createCell(5);
	            //加载单元格样式
	            cellsan.setCellStyle(sanStyle);
	            cellsan.setCellValue("协作单位：x施工一堆");
	            cellsan1.setCellStyle(sanStyle);
	            cellsan1.setCellValue("");
	            cellsan2.setCellStyle(sanStyle);
	            cellsan2.setCellValue("时间：2017年 10月 20日");
	            
	            //3.2创建列标题;并且设置列标题
	            HSSFRow row2 = sheet.createRow(3);
	            String[] titles = {"序号","工作内容","用工总人数","工日数","","单价（元）","金额(元）","备注"};//""为占位字符串
	            for(int i=0;i<titles.length;i++)
	            {
	                HSSFCell cell2 = row2.createCell(i);
	                //加载单元格样式
	                cell2.setCellStyle(colStyle);
	                cell2.setCellValue(titles[i]);
	            }
	            
	            HSSFRow rowfour = sheet.createRow(4);
	            String[] titlefour = {"普工用工数","技工用工数"};
	            for(int i=0;i<titlefour.length;i++)
	            {
	                HSSFCell cell2 = rowfour.createCell(i+3);
	                //加载单元格样式
	                cell2.setCellStyle(colStyle);
	                cell2.setCellValue(titlefour[i]);
	            }
	            
	            
	            //4.操作单元格;将用户列表写入excel
	            if(userList != null)
	            {
	            	int i=1;
	                for(int j=0;j<userList.size();j++)
	                {
	                    //创建数据行,前面有两行,头标题行和列标题行
	                    HSSFRow row3 = sheet.createRow(j+5);
	                    HSSFCell cell0 = row3.createCell(0);
	                    cell0.setCellStyle(cellStyle);
	                    cell0.setCellValue(i++);
	                    
	                    HSSFCell cell1 = row3.createCell(1);
	                    cell1.setCellStyle(cellStyle);
	                    cell1.setCellValue(userList.get(j).getWorkCtx());
	                    
	                    HSSFCell cell2 = row3.createCell(2);
	                    cell2.setCellStyle(cellStyle);
	                    cell2.setCellValue(userList.get(j).getTotalHumanDays());
	                    
	                    HSSFCell cell3 = row3.createCell(3);
	                    cell3.setCellStyle(cellStyle);
	                    cell3.setCellValue(userList.get(j).getGwnNum());
	                    
	                    HSSFCell cell4 = row3.createCell(4);
	                    cell4.setCellStyle(cellStyle);
	                    cell4.setCellValue(userList.get(j).getTmnNum());
	                    
	                    HSSFCell cell5 = row3.createCell(5);
	                    cell5.setCellStyle(cellStyle);
	                    cell5.setCellValue(userList.get(j).getTotalHumanDays());
	                    
	                    HSSFCell cell6 = row3.createCell(6);
	                    cell6.setCellStyle(cellStyle);
	                    cell6.setCellValue(userList.get(j).getUnitAmount());
	                    
	                    HSSFCell cell7= row3.createCell(7);
	                    cell7.setCellStyle(cellStyle);
	                    cell7.setCellValue(userList.get(j).getUnitPrice());
	                }
	            }
	            
	            HSSFRow rownumber = sheet.createRow(userList.size()+5);
	            HSSFCell cellnumber = rownumber.createCell(0);
	            HSSFCell cellnumber1 = rownumber.createCell(3);
	            //加载单元格样式
	            cellnumber.setCellStyle(sanStyle);
	            cellnumber.setCellValue("金额合计(大写)");
	            cellnumber1.setCellStyle(sanStyle);
	            cellnumber1.setCellValue("￥ 78 元； 大写：柒拾捌元整");

HSSFRow rowpersion = sheet.createRow(userList.size()+6);
	            HSSFCell cellpersion = rowpersion.createCell(0);
	            HSSFCell cellpersion1 = rowpersion.createCell(3);
	            HSSFCell cellpersion2 = rowpersion.createCell(5);

//加载单元格样式
	            cellpersion.setCellStyle(sanStyle);
	            cellpersion.setCellValue("协作单位负责人：");
	            cellpersion1.setCellStyle(sanStyle);
	            cellpersion1.setCellValue("经办人：");
	            cellpersion2.setCellStyle(sanStyle);
	            cellpersion2.setCellValue("部门负责人：");
	            
	            HSSFRow rowinfo = sheet.createRow(userList.size()+7);
	            HSSFCell cellinfo = rowinfo.createCell(0);
	            cellinfo.setCellStyle(sanStyle);
	            cellinfo.setCellValue("说明：1、本标工单一式两联，第一联为派工人（工长）存根，第二联用作结算。");
	            
	            HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);
	            HSSFCell cellinfo1 = rowinfo1.createCell(0);
	            cellinfo1.setCellStyle(sanStyle);
	            cellinfo1.setCellValue("2、本标工单必须在用工当日签认，否则不予认可；三日内交合同处汇总。");
	            
	            HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);
	            HSSFCell cellinfo2 = rowinfo2.createCell(0);
	            cellinfo2.setCellStyle(sanStyle);
	            cellinfo2.setCellValue("3、工日数填写精确到半个工日。");
	            //5.输出
	            workbook.write(fout);
//	            workbook.close();
	            //out.close();
	        }catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	}
	
    /**
     * 
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if(flag1){
        	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        }
       
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if(flag){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
package org;
import java.io.FileOutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExportExcel {
	public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){
		  try{
	            //1.创建工作簿
	            HSSFWorkbook workbook = new HSSFWorkbook();
	            //1.1创建合并单元格对象
	            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);//起始行,结束行,起始列,结束列
	            //班组与时间start
	            CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);//起始行,结束行,起始列,结束列
	            //班组与时间end
	            
	            //标题
	            CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7);//起始行,结束行,起始列,结束列
	            
	            //金额
	            CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7);//起始行,结束行,起始列,结束列

	            //负责人
	            CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7);//起始行,结束行,起始列,结束列

	            //说明
	            CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressinfo1 = new CellRangeAddress(userList.size()+8,userList.size()+8,0,7);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7);//起始行,结束行,起始列,结束列

	            //部项目经理部
	            HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);
	            //派工单
	            HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true);
	            //班组和时间
	            HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);
	            //标题样式
	            HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true);
	            //内容样式
	            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);
	            //2.创建工作表
	            HSSFSheet sheet = workbook.createSheet("派单");
	            //2.1加载合并单元格对象
	            sheet.addMergedRegion(callRangeAddress);
	            sheet.addMergedRegion(callRangeAddress1);
	            sheet.addMergedRegion(callRangeAddress20);
	            sheet.addMergedRegion(callRangeAddress21);
	            sheet.addMergedRegion(callRangeAddress22);
	            sheet.addMergedRegion(callRangeAddress31);
	            sheet.addMergedRegion(callRangeAddress32);
	            sheet.addMergedRegion(callRangeAddress33);
	            sheet.addMergedRegion(callRangeAddress34);
	            sheet.addMergedRegion(callRangeAddress35);
	            sheet.addMergedRegion(callRangeAddress36);
	            sheet.addMergedRegion(callRangeAddress37);
	            sheet.addMergedRegion(callRangeAddressnumber1);
	            sheet.addMergedRegion(callRangeAddressnumber2);
	            sheet.addMergedRegion(callRangeAddressPersion1);
	            sheet.addMergedRegion(callRangeAddressPersion2);
	            sheet.addMergedRegion(callRangeAddressPersion3);
	            sheet.addMergedRegion(callRangeAddressinfo);
	            sheet.addMergedRegion(callRangeAddressinfo1);
	            sheet.addMergedRegion(callRangeAddressinfo2);
	            //设置默认列宽
	            sheet.setDefaultColumnWidth(15);
	            //3.创建行
	            //3.1创建头标题行;并且设置头标题
	            HSSFRow row = sheet.createRow(0);
	            HSSFCell cell = row.createCell(0);
	            //加载单元格样式
	            cell.setCellStyle(headStyle);
	            cell.setCellValue("xxxx项目部");
	            
	            HSSFRow rower = sheet.createRow(1);
	            HSSFCell celler = rower.createCell(0);
	            //加载单元格样式
	            celler.setCellStyle(erStyle);
	            celler.setCellValue("派 工 单");
	            
	            HSSFRow rowsan = sheet.createRow(2);
	            HSSFCell cellsan = rowsan.createCell(0);
	            HSSFCell cellsan1 = rowsan.createCell(3);
	            HSSFCell cellsan2 = rowsan.createCell(5);
	            //加载单元格样式
	            cellsan.setCellStyle(sanStyle);
	            cellsan.setCellValue("协作单位：x施工一堆");
	            cellsan1.setCellStyle(sanStyle);
	            cellsan1.setCellValue("");
	            cellsan2.setCellStyle(sanStyle);
	            cellsan2.setCellValue("时间：2017年 10月 20日");
	            
	            //3.2创建列标题;并且设置列标题
	            HSSFRow row2 = sheet.createRow(3);
	            String[] titles = {"序号","工作内容","用工总人数","工日数","","单价（元）","金额(元）","备注"};//""为占位字符串
	            for(int i=0;i<titles.length;i++)
	            {
	                HSSFCell cell2 = row2.createCell(i);
	                //加载单元格样式
	                cell2.setCellStyle(colStyle);
	                cell2.setCellValue(titles[i]);
	            }
	            
	            HSSFRow rowfour = sheet.createRow(4);
	            String[] titlefour = {"普工用工数","技工用工数"};
	            for(int i=0;i<titlefour.length;i++)
	            {
	                HSSFCell cell2 = rowfour.createCell(i+3);
	                //加载单元格样式
	                cell2.setCellStyle(colStyle);
	                cell2.setCellValue(titlefour[i]);
	            }
	            
	            
	            //4.操作单元格;将用户列表写入excel
	            if(userList != null)
	            {
	            	int i=1;
	                for(int j=0;j<userList.size();j++)
	                {
	                    //创建数据行,前面有两行,头标题行和列标题行
	                    HSSFRow row3 = sheet.createRow(j+5);
	                    HSSFCell cell0 = row3.createCell(0);
	                    cell0.setCellStyle(cellStyle);
	                    cell0.setCellValue(i++);
	                    
	                    HSSFCell cell1 = row3.createCell(1);
	                    cell1.setCellStyle(cellStyle);
	                    cell1.setCellValue(userList.get(j).getWorkCtx());
	                    
	                    HSSFCell cell2 = row3.createCell(2);
	                    cell2.setCellStyle(cellStyle);
	                    cell2.setCellValue(userList.get(j).getTotalHumanDays());
	                    
	                    HSSFCell cell3 = row3.createCell(3);
	                    cell3.setCellStyle(cellStyle);
	                    cell3.setCellValue(userList.get(j).getGwnNum());
	                    
	                    HSSFCell cell4 = row3.createCell(4);
	                    cell4.setCellStyle(cellStyle);
	                    cell4.setCellValue(userList.get(j).getTmnNum());
	                    
	                    HSSFCell cell5 = row3.createCell(5);
	                    cell5.setCellStyle(cellStyle);
	                    cell5.setCellValue(userList.get(j).getTotalHumanDays());
	                    
	                    HSSFCell cell6 = row3.createCell(6);
	                    cell6.setCellStyle(cellStyle);
	                    cell6.setCellValue(userList.get(j).getUnitAmount());
	                    
	                    HSSFCell cell7= row3.createCell(7);
	                    cell7.setCellStyle(cellStyle);
	                    cell7.setCellValue(userList.get(j).getUnitPrice());
	                }
	            }
	            
	            HSSFRow rownumber = sheet.createRow(userList.size()+5);
	            HSSFCell cellnumber = rownumber.createCell(0);
	            HSSFCell cellnumber1 = rownumber.createCell(3);
	            //加载单元格样式
	            cellnumber.setCellStyle(sanStyle);
	            cellnumber.setCellValue("金额合计(大写)");
	            cellnumber1.setCellStyle(sanStyle);
	            cellnumber1.setCellValue("￥ 78 元； 大写：柒拾捌元整");

	            HSSFRow rowpersion = sheet.createRow(userList.size()+6);
	            HSSFCell cellpersion = rowpersion.createCell(0);
	            HSSFCell cellpersion1 = rowpersion.createCell(3);
	            HSSFCell cellpersion2 = rowpersion.createCell(5);

	            //加载单元格样式
	            cellpersion.setCellStyle(sanStyle);
	            cellpersion.setCellValue("协作单位负责人：");
	            cellpersion1.setCellStyle(sanStyle);
	            cellpersion1.setCellValue("经办人：");
	            cellpersion2.setCellStyle(sanStyle);
	            cellpersion2.setCellValue("部门负责人：");
	            
	            HSSFRow rowinfo = sheet.createRow(userList.size()+7);
	            HSSFCell cellinfo = rowinfo.createCell(0);
	            cellinfo.setCellStyle(sanStyle);
	            cellinfo.setCellValue("说明：1、本标工单一式两联，第一联为派工人（工长）存根，第二联用作结算。");
	            
	            HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);
	            HSSFCell cellinfo1 = rowinfo1.createCell(0);
	            cellinfo1.setCellStyle(sanStyle);
	            cellinfo1.setCellValue("2、本标工单必须在用工当日签认，否则不予认可；三日内交合同处汇总。");
	            
	            HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);
	            HSSFCell cellinfo2 = rowinfo2.createCell(0);
	            cellinfo2.setCellStyle(sanStyle);
	            cellinfo2.setCellValue("3、工日数填写精确到半个工日。");
	            //5.输出
	            workbook.write(fout);
//	            workbook.close();
	            //out.close();
	        }catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	}
	
    /**
     * 
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if(flag1){
        	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        }
       
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if(flag){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }
}

④main方法

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package org;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

public class MainOut {
	public static void main(String args[]){
		//模拟部分数据
		List<WorkSheetDetail> detail = new ArrayList<WorkSheetDetail>();

		WorkSheetDetail d1 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
		WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
		WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
		WorkSheetDetail d4 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
		WorkSheetDetail d5 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
		detail.add(d1);
		detail.add(d2);
		detail.add(d3);
		detail.add(d4);
		detail.add(d5);
        try  
        {  
            FileOutputStream fout = new FileOutputStream("E:/students.xls");
    		new ExportExcel().getValue(detail, fout);
            fout.close();  
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        }  

	}
}