java Excel导出 xlsx格式（超简单）_java导出xlsx

XSSF:xlsx
HSSF:xls
HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
XSSF是POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现
我之前是导出xls版本的，后来改成xlsx版本的，想切换版本就把全文的HSSF和XSSF替换了就可以了，可能会有极个别的方法不适用，自行百度一下就可以了

package com.wttech.tfjd.utils;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.wttech.tfjd.model.assess.MonthBalance;
import com.wttech.tfjd.model.assess.PointHistory;
import com.wttech.tfjd.model.assess.SalaryHistory;
import com.wttech.tfjd.model.assess.StatisticalHistory;

public class DownPOIUtils {
	/**
	 * 
	 * @param response:响应对象，类型是HttpServletResponse
	 * @param map:要封装的信息的map容器，其中key为Student，value为String类型的，在这里代表分数
	 * @throws Exception:代表异常对象
	 */
	public static void downPoi(HttpServletResponse response,MonthBalance monthBalance) throws Exception {
		String fname = "行政权力清单.xlsx";// Excel文件名
		OutputStream os = response.getOutputStream();// 取得输出流
		response.reset();// 清空输出流
		response.setHeader("Content-Disposition", "attachment;filename=" +URLEncoder.encode(fname, "UTF-8")); // 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。XSSF:xlsx    HSSF:xls  中文要用encode处理一下
		response.setContentType("application/msexcel");
		response.setContentType("text/html; charset=UTF-8"); //设置编码字符
		try {
			new DownPOIUtils().new POIS().createFixationSheet1(os, monthBalance);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	
class POIS {
	public void createFixationSheet1(OutputStream os,MonthBalance monthBalance) throws Exception {
			List<PointHistory> pointHistoryList = monthBalance.getPointHistoryList();
			List<StatisticalHistory> statisticalHistoryList = monthBalance.getStatisticalHistoryList();
			List<SalaryHistory> salaryHistoryList = monthBalance.getSalaryHistoryList();
			// 创建工作薄
			XSSFWorkbook wb = new XSSFWorkbook();
			// sheet1
			XSSFSheet sheet1 = wb.createSheet();
			//固定绩效
			int gdjx = 0;
			//考核绩效
			BigDecimal khjx = new BigDecimal(0);
			//应发绩效
			BigDecimal yfjx = new BigDecimal(0);
			
			
			//设置列宽
			sheet1.setColumnWidth(2, 4000); 
			sheet1.setColumnWidth(3, 6000); 
			
			XSSFRow sheet1row1 = sheet1.createRow((short) 0);
			sheet1.createFreezePane(0, 1);
			cteateCell(wb, sheet1row1, (short) 0, "序号");
			cteateCell(wb, sheet1row1, (short) 1, "姓名");
			cteateCell(wb, sheet1row1, (short) 2, "工作岗位");
			cteateCell(wb, sheet1row1, (short) 3, "身份证号码");
			cteateCell(wb, sheet1row1, (short) 4, "固定绩效");
			cteateCell(wb, sheet1row1, (short) 5, "考核绩效");
			cteateCell(wb, sheet1row1, (short) 6, "应发绩效");
			cteateCell(wb, sheet1row1, (short) 7, "备注");
			for(int i =1;i <= salaryHistoryList.size();i++){
				XSSFRow sheet1rowi = sheet1.createRow((short) i);
				cteateCell(wb, sheet1rowi, (short) 0, String.valueOf(i));
				cteateCell(wb, sheet1rowi, (short) 1, salaryHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet1rowi, (short) 2, salaryHistoryList.get(i-1).getJob());
				cteateCell(wb, sheet1rowi, (short) 3, salaryHistoryList.get(i-1).getIdnumber());
				cteateNumberCell(wb, sheet1rowi, (short) 4, salaryHistoryList.get(i-1).getFixedperformance());
				cteateNumberCell(wb, sheet1rowi, (short) 5, salaryHistoryList.get(i-1).getKhjx().toString());
				cteateNumberCell(wb, sheet1rowi, (short) 6, salaryHistoryList.get(i-1).getYfjx().toString());
				cteateCell(wb, sheet1rowi, (short) 7, "");
				
				gdjx += Integer.valueOf(salaryHistoryList.get(i-1).getFixedperformance());
				khjx = khjx.add(salaryHistoryList.get(i-1).getKhjx());
				yfjx = yfjx.add(salaryHistoryList.get(i-1).getYfjx());
			}
			XSSFRow sheet1row2 = sheet1.createRow((short) (salaryHistoryList.size()+1));
			cteateNoborderCell(wb,sheet1row2,(short) 4,String.valueOf(gdjx));
			cteateNoborderCell(wb,sheet1row2,(short) 5,khjx.toString());
			cteateNoborderCell(wb,sheet1row2,(short) 6,yfjx.toString());
			
			XSSFRow sheet1row3 = sheet1.createRow((short) (salaryHistoryList.size()+2));
			cteateNoborderCell(wb,sheet1row3,(short) 0,"负责人：");
			cteateNoborderCell(wb,sheet1row3,(short) 1,"池福波");
			
			
			//sheet2
			XSSFSheet sheet2 = wb.createSheet();
			//维修总数合计
			int total = 0;
			BigDecimal gd = new BigDecimal(0);
			BigDecimal rc = new BigDecimal(0);
			
			sheet2.setColumnWidth(6, 4000); 
			sheet2.setColumnWidth(7, 4000); 
			sheet2.setColumnWidth(8, 4000); 
			sheet2.setColumnWidth(9, 4000); 
			sheet2.setColumnWidth(10, 4000); 
			
			XSSFRow sheet2row1 = sheet2.createRow((short) 0);
			sheet2.createFreezePane(0, 1);
			cteateCell(wb, sheet2row1, (short) 0, "姓名");
			cteateCell(wb, sheet2row1, (short) 1, "简单");
			cteateCell(wb, sheet2row1, (short) 2, "一般");
			cteateCell(wb, sheet2row1, (short) 3, "较难");
			cteateCell(wb, sheet2row1, (short) 4, "困难");
			cteateCell(wb, sheet2row1, (short) 5, "重大");
			cteateCell(wb, sheet2row1, (short) 6, "维护总数合计");
			cteateCell(wb, sheet2row1, (short) 7, "维修量占比");
			cteateCell(wb, sheet2row1, (short) 8, "工单绩效工资");
			cteateCell(wb, sheet2row1, (short) 9, "日常绩效得分");
			cteateCell(wb, sheet2row1, (short) 10, "日常绩效工资");
			for(int i =1;i <= statisticalHistoryList.size();i++){
				XSSFRow sheet2rowi = sheet2.createRow((short) i);
				cteateCell(wb, sheet2rowi, (short) 0, statisticalHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet2rowi, (short) 1, statisticalHistoryList.get(i-1).getEasy()==null?"":statisticalHistoryList.get(i-1).getEasy().toString());
				cteateCell(wb, sheet2rowi, (short) 2, statisticalHistoryList.get(i-1).getNormal()==null?"":statisticalHistoryList.get(i-1).getNormal().toString());
				cteateCell(wb, sheet2rowi, (short) 3, statisticalHistoryList.get(i-1).getLessdifficult()==null?"":statisticalHistoryList.get(i-1).getLessdifficult().toString());
				cteateCell(wb, sheet2rowi, (short) 4, statisticalHistoryList.get(i-1).getDifficult()==null?"":statisticalHistoryList.get(i-1).getDifficult().toString());
				cteateCell(wb, sheet2rowi, (short) 5, statisticalHistoryList.get(i-1).getImportant()==null?"":statisticalHistoryList.get(i-1).getImportant().toString());
				cteateCell(wb, sheet2rowi, (short) 6, statisticalHistoryList.get(i-1).getTotal()==null?"":statisticalHistoryList.get(i-1).getTotal().toString());
				cteateCell(wb, sheet2rowi, (short) 7, statisticalHistoryList.get(i-1).getAccountedfor()==null?"":statisticalHistoryList.get(i-1).getAccountedfor());
				cteateNumberCell(wb, sheet2rowi, (short) 8, statisticalHistoryList.get(i-1).getGdwage().toString());
				cteateCell(wb, sheet2rowi, (short) 9, statisticalHistoryList.get(i-1).getScore().toString());
				cteateNumberCell(wb, sheet2rowi, (short) 10, statisticalHistoryList.get(i-1).getRcwage().toString());
				
				if(statisticalHistoryList.get(i-1).getTotal()!=null){
					total += statisticalHistoryList.get(i-1).getTotal();
				}
				gd = gd.add(statisticalHistoryList.get(i-1).getGdwage());
				rc = rc.add(statisticalHistoryList.get(i-1).getRcwage());
			}
			XSSFRow sheet2row2 = sheet2.createRow((short) statisticalHistoryList.size()+1);
			cteateCell(wb,sheet2row2,(short)0,"合计：");
			cteateCell(wb,sheet2row2,(short)1,"");
			cteateCell(wb,sheet2row2,(short)2,"");
			cteateCell(wb,sheet2row2,(short)3,"");
			cteateCell(wb,sheet2row2,(short)4,"");
			cteateCell(wb,sheet2row2,(short)5,"");
			cteateCell(wb,sheet2row2,(short)6,String.valueOf(total));
			cteateCell(wb,sheet2row2,(short)7,"100%");
			cteateCell(wb,sheet2row2,(short)8,gd.setScale(0, BigDecimal.ROUND_HALF_UP).toString());
			cteateCell(wb,sheet2row2,(short)9,"");
			cteateCell(wb,sheet2row2,(short)10,rc.setScale(0, BigDecimal.ROUND_HALF_UP).toString());
			
			//sheet3
			XSSFSheet sheet3 = wb.createSheet();
			
			sheet3.setColumnWidth(1, 4000); 
			sheet3.setColumnWidth(3, 6000); 
			sheet3.setColumnWidth(4, 6000); 
			sheet3.setColumnWidth(5, 6000); 
			
			XSSFRow sheet3row1 = sheet3.createRow((short) 0);
			sheet3.createFreezePane(0, 1);
			cteateCell(wb, sheet3row1, (short) 0, "序号");
			cteateCell(wb, sheet3row1, (short) 1, "时间");
			cteateCell(wb, sheet3row1, (short) 2, "姓名");
			cteateCell(wb, sheet3row1, (short) 3, "考核加（扣）分内容");
			cteateCell(wb, sheet3row1, (short) 4, "加（扣）分依据");
			cteateCell(wb, sheet3row1, (short) 5, "加（扣）分值");
			cteateCell(wb, sheet3row1, (short) 6, "备注");
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			for(int i =1;i <= pointHistoryList.size();i++){
				XSSFRow sheet3rowi = sheet3.createRow((short) i);
				cteateCell(wb, sheet3rowi, (short) 0, String.valueOf(i));
				cteateCell(wb, sheet3rowi, (short) 1, simpleDateFormat.format(pointHistoryList.get(i-1).getPointdate()));
				cteateCell(wb, sheet3rowi, (short) 2, pointHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet3rowi, (short) 3, pointHistoryList.get(i-1).getContent());
				cteateCell(wb, sheet3rowi, (short) 4, pointHistoryList.get(i-1).getEvidence());
				cteateCell(wb, sheet3rowi, (short) 5, pointHistoryList.get(i-1).getCount().toString());
				cteateCell(wb, sheet3rowi, (short) 6, "");
			}
			//给每个sheet页起名字
			wb.setSheetName(0, "绩效工资发放表");
			wb.setSheetName(1, "绩效工资统计表");
			wb.setSheetName(2, "考核加扣分统计表");
			wb.write(os);
			os.flush();
			os.close();
			System.out.println("文件生成");
 
		}
 
		@SuppressWarnings("deprecation")
		private void cteateCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			XSSFCell cell = row.createCell(col);
			cell.setCellValue(val);
			XSSFCellStyle cellstyle = wb.createCellStyle();
			//HSSFFont Font = wb.createFont();
			//Font.setFontHeightInPoints((short) 10);
			//cellstyle.setFont(Font);
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cellstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
			cellstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
			cellstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
			cellstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
			cell.setCellStyle(cellstyle);
		}
		@SuppressWarnings("deprecation")
		private void cteateNoborderCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			XSSFCell cell = row.createCell(col);
			cell.setCellValue(val);
			XSSFCellStyle cellstyle = wb.createCellStyle();
			//HSSFFont Font = wb.createFont();
			//Font.setFontHeightInPoints((short) 10);
			//cellstyle.setFont(Font);
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cell.setCellStyle(cellstyle);
		}
		@SuppressWarnings({ "deprecation", "static-access" })
		private void cteateNumberCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			//生成单元格
			XSSFCell cell = row.createCell(col);
			// 设置单元格内容为double类型
			cell.setCellValue(Float.valueOf(val));
			
			//生成单元格样式
			XSSFCellStyle cellstyle = wb.createCellStyle();
			
			XSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
			cellstyle.setDataFormat(df.getFormat("#,##0.00"));//保留两位小数点,("#,#0"数据格式只显示整数)("#,##0.00"保留两位)
			
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cellstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
			cellstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
			cellstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
			cellstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
			cell.setCellStyle(cellstyle);
		}
	}
}
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
248
249
250
251
252
253
254
255
256
257
258