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
package com.yiboshi.science.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yiboshi.science.param.dto.ProjectImportDTO;
import com.yiboshi.science.param.dto.UserImportDTO;
import com.yiboshi.science.service.SystemSetService;
import lombok.Cleanup;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.io.*;
import java.util.Calendar;
import java.util.List;
import java.util.UUID;
@Component
public class ExcelUtils {
@Autowired
private SystemSetService systemSetService;
/**
* Excel
*
* @param list Excel表中的记录
*/
public void writeExcel(List<UserImportDTO> list) throws IOException {
@Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(os).build();
//定义工作表对象
WriteSheet sheet = EasyExcel.writerSheet(0, "sheet").head(UserImportDTO.class).build();
//向Excel文件中写入数据
excelWriter.write(list, sheet);
excelWriter.finish();
byte[] content = os.toByteArray();
@Cleanup InputStream in = new ByteArrayInputStream(content);
String filePath = getFilePath(systemSetService.getByKey(SystemSetKey.SysUploadFilePath));
//判断文件目录是否存在，否则自动生成
File savePath = new File(getPhysicalPath(filePath));
if (!savePath.exists()) {
savePath.mkdirs();
}
String newFileName = UUID.randomUUID().toString() + ".xlsx";
FileOutputStream o = new FileOutputStream(savePath + "/" + newFileName);
//创建一个缓冲区
byte buffer[] = new byte[1024];
//判断输入流中的数据是否已经读完的标识
int len = 0;
//循环将输入流读入到缓冲区当中，(len=in.read(buffer))>0就表示in里面还有数据
while ((len = in.read(buffer)) > 0) {
//使用FileOutputStream输出流将缓冲区的数据写入到指定的目录(savePath + "\\" + filename)当中
o.write(buffer, 0, len);
}
//关闭输入流
in.close();
//关闭输出流
o.close();
}
public void writeExcel2(List<ProjectImportDTO> list) throws IOException {
@Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(os).build();
//定义工作表对象
WriteSheet sheet = EasyExcel.writerSheet(0, "sheet").head(ProjectImportDTO.class).build();
//向Excel文件中写入数据
excelWriter.write(list, sheet);
excelWriter.finish();
byte[] content = os.toByteArray();
@Cleanup InputStream in = new ByteArrayInputStream(content);
String filePath = getFilePath(systemSetService.getByKey(SystemSetKey.SysUploadFilePath));
//判断文件目录是否存在，否则自动生成
File savePath = new File(getPhysicalPath(filePath));
if (!savePath.exists()) {
savePath.mkdirs();
}
String newFileName = UUID.randomUUID().toString() + ".xlsx";
FileOutputStream o = new FileOutputStream(savePath + "/" + newFileName);
//创建一个缓冲区
byte buffer[] = new byte[1024];
//判断输入流中的数据是否已经读完的标识
int len = 0;
//循环将输入流读入到缓冲区当中，(len=in.read(buffer))>0就表示in里面还有数据
while ((len = in.read(buffer)) > 0) {
//使用FileOutputStream输出流将缓冲区的数据写入到指定的目录(savePath + "\\" + filename)当中
o.write(buffer, 0, len);
}
//关闭输入流
in.close();
//关闭输出流
o.close();
}
protected String getFilePath(String basePath) {
if (null != basePath && "" != basePath) {
Calendar rightNow = Calendar.getInstance();
Integer year = rightNow.get(Calendar.YEAR);
//第一个月从0开始，所以得到月份＋1
Integer month = rightNow.get(Calendar.MONTH) + 1;
// Integer day = rightNow.get(rightNow.DAY_OF_MONTH);
return basePath + year + "/" + month + "/";
}
return "";
}
/**
* 获取物理路劲
*
* @param filePath
* @return
*/
protected String getPhysicalPath(String filePath) {
String savePath = System.getProperty("user.dir") + filePath;
return savePath;
}
}