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
| /**
* easyExcel 工具类
* @author rstyro
*/
@Slf4j
public class EasyExcelUtils {
/**
* 浏览器导出excel文件
* 官网文档地址：https://www.yuque.com/easyexcel/doc/easyexcel
* @param data 数据
* @param templateClass 模板对象class
* @param pageSize 每页多少条
* @param fileName 文件名称
* @param response 输出流
* @throws Exception err
*/
public static void exportBrowser(List data, Class templateClass, Integer pageSize, String fileName, HttpServletResponse response) throws Exception {
pageSize = Optional.ofNullable(pageSize).orElse(50000);
fileName = fileName + System.currentTimeMillis();
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 获取改类声明的所有字段
Field[] fields = templateClass.getDeclaredFields();
// 响应字段对应的下拉集合
Map<Integer, String[]> map = processDropDown(fields);
// 解析级联下拉字段
Map<Integer, ChainDropDown> integerChainDropDownMap = processChainDropDown(fields);
ExcelWriter excelWriter = null;
OutputStream out = null;
try {
out = response.getOutputStream();
excelWriter = EasyExcel.write(out, templateClass).registerWriteHandler(new DropDownWriteHandler(map))
.registerWriteHandler(new ChainDropDownWriteHandler(integerChainDropDownMap)).build();
// 分页写入
pageWrite(excelWriter,data,pageSize);
} catch (Throwable e) {
response.setHeader("Content-Disposition", "attachment;filename=下载失败");
e.printStackTrace();
log.error("文档下载失败:" + e.getMessage());
} finally {
data.clear();
if (excelWriter != null) {
excelWriter.finish();
}
assert out != null;
out.flush();
out.close();
}
}
/**
* 分页写入
* @param writer ExcelWriter
* @param data 数据
* @param pageSize 分页大小
*/
public static void pageWrite(ExcelWriter writer,List<Object> data,Integer pageSize){
List<List<Object>> lt = Lists.partition(data, pageSize);
for (int i = 0; i < lt.size(); i++) {
int j = i + 1;
WriteSheet writeSheet = EasyExcel.writerSheet(i, "第" + j + "页").build();
writer.write(lt.get(i), writeSheet);
}
}
/**
* 读取excel 并解析
* @param file 文件
* @param clazz 解析成哪个dto
* @param <T> t
* @return list
* @throws IOException error
*/
public static <T> List<T> read(MultipartFile file,Class<T> clazz) throws IOException {
BaseExcelDataListener<T> baseExcelDataListener = new BaseExcelDataListener();
EasyExcel.read(file.getInputStream(), clazz, baseExcelDataListener).sheet().doRead();
return baseExcelDataListener.getResult();
}
/**
* 处理有下拉框注解的属性
* @param fields 字段属性集合
* @return map
*/
public static Map<Integer, String[]> processDropDown(Field[] fields){
// 响应字段对应的下拉集合
Map<Integer, String[]> map = new HashMap<>();
Field field = null;
// 循环判断哪些字段有下拉数据集，并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 解析注解信息
DropDownFields dropDownField = field.getAnnotation(DropDownFields.class);
if (null != dropDownField) {
String[] sources = ResolveAnnotation.resolve(dropDownField);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
}
return map;
}
public static Map<Integer, ChainDropDown> processChainDropDown(Field[] fields){
// 响应字段对应的下拉集合
Map<Integer, ChainDropDown> map = new HashMap<>();
Field field = null;
int rowIndex=0;
// 循环判断哪些字段有下拉数据集，并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 解析注解信息
ChainDropDownFields chainDropDownFields = field.getAnnotation(ChainDropDownFields.class);
if (null != chainDropDownFields) {
// List<ChainDropDown> sources = ResolveAnnotation.resolve(chainDropDownFields);
ChainDropDown resolve = ResolveAnnotation.resolve(chainDropDownFields);
long collect = resolve.getDataMap().keySet().size();
System.out.println("collect="+collect);
if(resolve.isRootFlag()){
resolve.setRowIndex(rowIndex);
rowIndex+=1;
}else {
resolve.setRowIndex(rowIndex);
rowIndex+=collect;
}
if (!ObjectUtils.isEmpty(resolve)) {
map.put(i, resolve);
}
}
}
return map;
}
/**
* 获取Excel列的号码A-Z - AA-ZZ - AAA-ZZZ 。。。。
* @param num
* @return
*/
public static String getColNum(int num) {
int MAX_NUM = 26;
char initChar = 'A';
if(num == 0){
return initChar+"";
}else if(num > 0 && num < MAX_NUM){
int result = num % MAX_NUM;
return (char) (initChar + result) + "";
}else if(num >= MAX_NUM){
int result = num / MAX_NUM;
int mod = num % MAX_NUM;
String starNum = getColNum(result-1);
String endNum = getColNum(mod);
return starNum+endNum;
}
return "";
}
}
|
