Java导出Excel文档（poi），并上传到腾讯云对象存储服务器

October 21, 2017 19197

需求

后台生成周报月报季报年报Excel，将文件下载链接推送给对应客户

开发思路：

1.根据选定日期生成周报，月报，季报，年报数据
2.将这些数据报告生成Excel表格
3.把生成的文件上传到腾讯云对象存储服务器
4.将服务器返回的url存储到数据库

工具

poi-3.14-20160307.jar(点击可下载)

数据

获取数据部分省略了

代码

主方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public boolean addReportExcelToCloud(ReportResult rr) {

		OutputStream out = new ByteArrayOutputStream();
		ExcelProjectUtils eu = new ExcelProjectUtils();
		eu.exportExcel(rr, out);   //<1>
		ConvertUtil cu = new ConvertUtil();
		try {
			ByteArrayInputStream byteInput = cu.parse(out);
			String rs = PicUploadToYun.uploadExcel(SysContent.getFileRename("案场数据报.xls"), byteInput);  //<2>
			addReportExcelToDB(rr, rs);  //<3>
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;
	}

<1> 将数据生成二进制Excel文件 (方法详细见下面代码)
<2> 将生成的二进制文件上传到腾讯云对象存储服务器 (方法详细见下面代码)
<3> 将服务器返回的url存储到数据库 (方法详细见下面代码)

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
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
/**
	 * 周报年报生成excel
	 * 
	 * @param report
	 * @param out
	 */
	public void exportExcel(ReportResult report, OutputStream out) {

		// 判断传入的时间间隔
		String dateStr = "";
		String reportName = "";
		List<String> dateCount = DateUtil.getTwoDateEveryDay(report.getStartTime(), report.getEndTime());
		if (dateCount.size() <= 7) {
			dateStr += "本周";
			reportName += "案场周报";
		} else if (dateCount.size() >= 28 && dateCount.size() <= 31) {
			dateStr += "本月";
			reportName += "案场月报";
		} else if (dateCount.size() >= 85 && dateCount.size() <= 100) {
			dateStr += "本季度";
			reportName += "案场季报";
		} else if (dateCount.size() >= 180 && dateCount.size() <= 185) {
			dateStr += "本半年度";
			reportName += "案场半年报";
		} else if (dateCount.size() >= 360 && dateCount.size() <= 367) {
			dateStr += "本年度";
			reportName += "案场年报";
		} else {
			dateStr += "时间段内";
			reportName += "案场阶段报";
		}
		report.setReportName(reportName);
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(report.getReportName() + report.getStartTime() + " - " + report.getEndTime());
		// 设置表格默认列宽度为100个字节
		sheet.setDefaultColumnWidth((short) 100);
		/** ----------样式一：标题 ------------ **/
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		//font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 14);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		/***---------样式二：小标题---------***/
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		
		//style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		//font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short) 11);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		
		/***    样式三：右侧日期       ***/
		HSSFCellStyle style3 = workbook.createCellStyle();
		//样式
		style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//字体
		HSSFFont font3 = workbook.createFont();
		font3.setFontName("宋体");
		font3.setFontHeightInPoints((short) 11);
		style3.setFont(font3);
		
		/**       样式四：主内容        ***/
		HSSFCellStyle style4 = workbook.createCellStyle();
		//样式
		style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		//字体
		HSSFFont font4 = workbook.createFont();
		font4.setFontName("宋体");
		font4.setFontHeightInPoints((short) 11);
		style4.setFont(font4);
		
		/**       样式五：底侧空内容       ***/
		HSSFCellStyle style5 = workbook.createCellStyle();
		//样式
		style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style5.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style5.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//字体
		HSSFFont font5 = workbook.createFont();
		font5.setFontName("宋体");
		font5.setFontHeightInPoints((short) 11);
		style5.setFont(font5);
		
		
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("数据报"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("saas");

		// 产生表格标题行 -- 项目名称
		HSSFRow row = sheet.createRow(0);
		createCellAndRow(style4, report.getProjectName(), row);

		// 产生表格标题行 -- 周报名称
		row = sheet.createRow(1);
		createCellAndRow(style, report.getReportName(), row);

		// 产生表格标题行 -- 起始时间-终止时间
		row = sheet.createRow(2);
		String startTime = DateUtil.format(DateUtil.parse(report.getStartTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
				DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
		String endTime = DateUtil.format(DateUtil.parse(report.getEndTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
				DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
		String date = "日期：" + startTime + " - " + endTime;
		createCellAndRow(style3, date, row);

		// 接访情况标题
		row = sheet.createRow(3);
		createCellAndRow(style2, "·接访情况", row);

		// 接访客户组数
		row = sheet.createRow(4);
		Integer visitCount = report.getVisitCount();
		String visitNum = "1、" + dateStr + "共计接访客户" + visitCount + "组，来访量";
		if (visitCount < 40) {
			visitNum += "较少，有待提升";
		} else if (visitCount >= 41 && visitCount <= 99) {
			visitNum += "尚可，还有提高空间";
		} else if (visitCount >= 100 && visitCount <= 139) {
			visitNum += "很多";
		} else if (visitCount > 140) {
			visitNum += "火爆";
		}
		createCellAndRow(style4, visitNum, row);

		// 有效接访率
		row = sheet.createRow(5);
		Double visitRate = new Double(report.getValidVisitRate());
		String visitRateStr = "2、有效接访率为" + visitRate + "%，接访成效";
		if (visitRate < 50) {
			visitRateStr += "较低，有待提升";
		} else if (visitRate >= 50 && visitRate <= 65) {
			visitRateStr += "尚可，还有提高空间";
		} else if (visitRate >= 65 && visitRate <= 80) {
			visitRateStr += "很高";
		} else if (visitRate > 80) {
			visitRateStr += "极高";
		}
		createCellAndRow(style4, visitRateStr, row);

		// 首访有效率
		row = sheet.createRow(6);
		Double newVisitRate = new Double(report.getValidNewCuVisitRate());
		String newVisitStr = "3、首访有效率为" + newVisitRate + "%，来访转储客的概率";
		if (newVisitRate < 40) {
			newVisitStr += "较差，有待提升";
		} else if (newVisitRate >= 40 && newVisitRate <= 60) {
			newVisitStr += "尚可，还有提高空间";
		} else if (newVisitRate >= 60 && newVisitRate <= 75) {
			newVisitStr += "很高";
		} else if (newVisitRate > 75) {
			newVisitStr += "极高";
		}
		createCellAndRow(style4, newVisitStr, row);

		// 老客户接访占比
		row = sheet.createRow(7);
		Double oldVisitRate = new Double(report.getOldCuVisitRate());
		String oldVisitStr = "4、老客户接访比为" + oldVisitRate + "%，老客户接访的占比";
		if (oldVisitRate < 20) {
			oldVisitStr += "较低";
		} else if (oldVisitRate >= 20 && oldVisitRate <= 40) {
			oldVisitStr += "尚可";
		} else if (oldVisitRate >= 40 && oldVisitRate <= 60) {
			oldVisitStr += "很高";
		} else if (oldVisitRate > 60) {
			oldVisitStr += "极高";
		}
		createCellAndRow(style4, oldVisitStr, row);

		//空行
		row = sheet.createRow(8);
		createCellAndRow(style4, "", row);
		
		// 储客情况
		row = sheet.createRow(9);
		createCellAndRow(style2, "·储客情况", row);

		// 新增储客
		row = sheet.createRow(10);
		Integer newCuCount = report.getNewCuCount();
		String newCuStr = "1、" + dateStr + "新增储客" + newCuCount + "组，新增量";
		if (newCuCount < 30) {
			newCuStr += "较少，有待提升";
		} else if (newCuCount >= 31 && newCuCount <= 60) {
			newCuStr += "尚可，还有提高空间";
		} else if (newCuCount >= 61 && newCuCount <= 79) {
			newCuStr += "很多";
		} else if (newCuCount > 80) {
			newCuStr += "爆满";
		}
		createCellAndRow(style4, newCuStr, row);

		// 累计老客户
		row = sheet.createRow(11);
		Integer oldCuCount = report.getTotalOldCuCount();
		Integer totalCuCount = report.getTotalCuCount();
		Double oldCuRate = new Double(SysContent.getTwoNumberForValue(oldCuCount, totalCuCount));
		String oldCuStr = "2、累计老客户总量为" + oldCuCount + "组，老客户占比为" + oldCuRate + "%，显示老客户关注度";
		if (oldCuRate < 15) {
			oldCuStr += "较低，有待提升";
		} else if (oldCuRate >= 15 && oldCuRate <= 25) {
			oldCuStr += "尚可，还有提高空间";
		} else if (oldCuRate >= 25 && oldCuRate <= 40) {
			oldCuStr += "很高";
		} else if (oldCuRate > 40) {
			oldCuStr += "极高";
		}
		createCellAndRow(style4, oldCuStr, row);

		// 累计总储客
		row = sheet.createRow(12);
		String totalOldCuStr = "3、累计总储客" + totalCuCount + "组";
		createCellAndRow(style4, totalOldCuStr, row);

		// 成交情况(周报没有，其他有)
		if (report.getSubscribeHouseCount() != null) {
			
			//空行
			row = sheet.createRow(13);
			createCellAndRow(style4, "", row);
			
			row = sheet.createRow(14);
			createCellAndRow(style2, "·成交情况", row);

			// 新增认购套数
			row = sheet.createRow(15);
			Integer subscribeHouseCount = report.getSubscribeHouseCount();
			Double subscribeHouseRate = new Double(report.getSubscribeHouseRate());
			String subscribeHouseStr = "1、" + dateStr + "新增认购套数" + subscribeHouseCount + "套，较" + dateStr + "同期";
			if (subscribeHouseRate < 0) {
				subscribeHouseStr += "减少";
			} else {
				subscribeHouseStr += "增长";
			}
			subscribeHouseStr += Math.abs(subscribeHouseRate) + "%";
			createCellAndRow(style4, subscribeHouseStr, row);

			// 新增认购金额
			row = sheet.createRow(16);
			Long subscribeMoney = report.getSubscribeMoney();
			Double subscribeMoneyRate = new Double(report.getSubscribeMoneyRate());
			String subscribeMoneyStr = "   新增认购金额" + subscribeMoney + "万元，较" + dateStr + "同期";
			if (subscribeHouseRate < 0) {
				subscribeMoneyStr += "减少";
			} else {
				subscribeMoneyStr += "增长";
			}
			subscribeMoneyStr += Math.abs(subscribeMoneyRate) + "%";
			createCellAndRow(style4, subscribeMoneyStr, row);

			// 新增签约套数
			row = sheet.createRow(17);
			Integer signCount = report.getSignCount();
			Double signRate = new Double(report.getSignRate());
			String signStr = "2、新增签约套数" + signCount + "套,较" + dateStr + "同期";
			if (signRate < 0) {
				signStr += "减少";
			} else {
				signStr += "增长";
			}
			signStr += Math.abs(signRate) + "%";
			createCellAndRow(style4, signStr, row);

			// 新增签约金额
			row = sheet.createRow(18);
			Long signHouseMoney = report.getSignHouseMoney();
			Double signHouseMoneyRate = new Double(report.getSignHouseMoneyRate());
			String signHouseMoneyStr = "   新增签约金额" + signHouseMoney + "万元，较" + dateStr + "同期";
			if (signHouseMoneyRate < 0) {
				signHouseMoneyStr += "减少";
			} else {
				signHouseMoneyStr += "增长";
			}
			signHouseMoneyStr += Math.abs(signHouseMoneyRate) + "%";
			createCellAndRow(style4, signHouseMoneyStr, row);

			// 新接访签约率
			row = sheet.createRow(19);
			Double newCustomerSignedRate = new Double(report.getNewCustomerSignedRate());
			String newCustomerSignedStr = "3、" + dateStr + "新客户接访签约率" + newCustomerSignedRate + "%，接访签约概率";
			if (newCustomerSignedRate < 4) {
				newCustomerSignedStr += "较低，与理想值差距大";
			} else if (newCustomerSignedRate >= 4 && newCustomerSignedRate <= 6) {
				newCustomerSignedStr += "尚可，还有提高空间";
			} else if (newCustomerSignedRate >= 6 && newCustomerSignedRate <= 7) {
				newCustomerSignedStr += "很高";
			} else if (newCustomerSignedRate > 7) {
				newCustomerSignedStr += "非常高";
			}
			createCellAndRow(style4, newCustomerSignedStr, row);

			// 储客签约率
			row = sheet.createRow(20);
			Double momeryCustomerSignedRate = new Double(report.getMomeryCustomerSignedRate());
			String momeryCustomerSignedStr = "4、储客签约率" + momeryCustomerSignedRate + "%，储备客户签约概率";
			if (momeryCustomerSignedRate < 7) {
				momeryCustomerSignedStr += "较低，与理想值差距大";
			} else if (momeryCustomerSignedRate >= 7 && momeryCustomerSignedRate <= 12) {
				momeryCustomerSignedStr += "尚可，还有提高空间";
			} else if (momeryCustomerSignedRate >= 12 && momeryCustomerSignedRate <= 15) {
				momeryCustomerSignedStr += "很高";
			} else if (momeryCustomerSignedRate > 15) {
				momeryCustomerSignedStr += "非常高";
			}
			createCellAndRow(style4, momeryCustomerSignedStr, row);

			// 老客户签约率
			row = sheet.createRow(21);
			Double oldCustomerSignedRate = new Double(report.getOldCustomerSignedRate());
			String oldCustomerSignedStr = "5、老客户签约率为23.2%，高意向客户签约概率";
			if (oldCustomerSignedRate < 25) {
				oldCustomerSignedStr += "较低，与理想值差距大";
			} else if (oldCustomerSignedRate >= 25 && oldCustomerSignedRate <= 35) {
				oldCustomerSignedStr += "尚可，还有提高空间";
			} else if (oldCustomerSignedRate >= 35 && oldCustomerSignedRate <= 50) {
				oldCustomerSignedStr += "很高";
			} else if (oldCustomerSignedRate > 50) {
				oldCustomerSignedStr += "非常高";
			}
			createCellAndRow(style4, oldCustomerSignedStr, row);

			// 认购客户签约率
			row = sheet.createRow(22);
			Double contratCuSignedRate = new Double(report.getContratCuSignedRate());
			String contratCuSignedStr = "6、认购客户签约率为92%，已认购客户签约率";
			if (contratCuSignedRate < 95) {
				contratCuSignedStr += "不高，较多退订或拒签";
			} else if (contratCuSignedRate >= 95 && contratCuSignedRate <= 97) {
				contratCuSignedStr += "尚可，一定数量退订或拒签";
			} else if (contratCuSignedRate >= 97 && contratCuSignedRate <= 99) {
				contratCuSignedStr += "很高";
			} else if (contratCuSignedRate > 99) {
				contratCuSignedStr += "非常高";
			}
			createCellAndRow(style4, contratCuSignedStr, row);

			//空行
			row = sheet.createRow(23);
			createCellAndRow(style4, "", row);
			
			//底侧
			row = sheet.createRow(24);
			createCellAndRow(style5, "", row);
			
		}else{
			row = sheet.createRow(13);
			createCellAndRow(style5, "", row);
		}

		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void createCellAndRow(HSSFCellStyle style, String text, HSSFRow row) {
		HSSFCell cell = row.createCell(0);
		cell.setCellStyle(style);
		HSSFRichTextString rs = new HSSFRichTextString(text);
		cell.setCellValue(rs);
	}
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
/**
	 * 上传Excel
	 * @param fileNewName
	 * @param uploadFile
	 * @return
	 */
	public static String uploadExcel(String fileNewName,ByteArrayInputStream uploadFile){
		// 设置用户属性, 包括appid, secretId和SecretKey
				// 这些属性可以通过cos控制台获取(https://console.qcloud.com/cos)
				String version = PropertiesUtil.getValue("version");
					 long appId = "你的appId";
	                 String secretId = "你的secretId ";
	                 String secretKey = "你的secretKey ";
				
				// 设置要操作的bucket
				String bucketName = "root";
				// 初始化客户端配置
				ClientConfig clientConfig = new ClientConfig();
				// 设置bucket所在的区域，比如广州(gz), 天津(tj)
				clientConfig.setRegion("sh");
				// 初始化秘钥信息
				Credentials cred = new Credentials(appId, secretId, secretKey);
				// 初始化cosClient
				COSClient cosClient = new COSClient(clientConfig, cred);
				// 文件操作 //
				// 1. 上传文件(默认不覆盖)
				// 将本地的local_file_1.txt上传到bucket下的根分区下,并命名为sample_file.txt
				// 默认不覆盖, 如果cos上已有文件, 则返回错误
				String cosFilePath = "/report/" + fileNewName;
				
				byte[] localFilePath1 = null;
				try {
					localFilePath1 = ConvertUtil.toByteArray(uploadFile);
				} catch (IOException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
				UploadFileRequest uploadFileRequest = new UploadFileRequest(bucketName, cosFilePath, localFilePath1);
				uploadFileRequest.setEnableShaDigest(false);
				String uploadFileRet = cosClient.uploadFile(uploadFileRequest);
				System.out.println("upload file ret:" + uploadFileRet);
				//获取保存路径
				ObjectMapper om = new ObjectMapper();
				HashMap map = new HashMap<>();
				try {
					map = om.readValue(uploadFileRet, HashMap.class);
				} catch (JsonParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (JsonMappingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				HashMap<String, String> value = (HashMap<String, String>) map.get("data");
				return value.get("source_url");
				
	}
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
public boolean addReportExcelToDB(ReportResult rr, String url) {
		
		if(StringUtils.isEmpty(url)){
			return false;
		}
		if(rr == null){
			return false;
		}
		
		ProjectReportRecord prr = new ProjectReportRecord();
		prr.setCreateTime(DateUtil.format(new Date()));
		prr.setProjectId(rr.getProjectId());
		prr.setProjectName(rr.getProjectName());
		prr.setStartTime(rr.getStartTime());
		prr.setEndTime(rr.getEndTime());
		prr.setUrl(url);
		String report = "";
		if("案场周报".equals(rr.getReportName())){
			report = "week";
		}else if("案场月报".equals(rr.getReportName())){
			report = "month";
		}else if("案场季报".equals(rr.getReportName())){
			report = "quarter";
		}else if("案场半年报".equals(rr.getReportName())){
			report = "half";
		}else if("案场年报".equals(rr.getReportName())){
			report = "year";
		}else{
			report = "other";
		}
		prr.setReportName(report);
		
		baseDao.save(prr);
		
		return true;
	}

生成的文件示例

周报或者其他报告都是后台自动根据时间进行判断的

周报
这里写图片描述

季报

这里写图片描述

以上

查看评论