1
1. 创建行(从0开始):Row row0 = sheet.createRow(0);
2
(每一行都是独立的,需要单独创建,且不可多次创建)
3
2.合并所需的单元格:
4
CellRangeAddress mergedRegion = new  CellRangeAddress(起始行, 结束行, 起始列, 结束列);
5
sheet.addMergedRegion(mergedRegion);
6
3.单独创建单元格(相当于列,创建行是前提条件,也是从0开始):
7

8
Cell cell = row0.createCell(0);
9

10
4.根据所需要的逻辑在对应的位置依次插入数据:
11
cell.setCellValue(具体数值);
12
5.Tips:各种操作必须按行!!!
13

14
public String labourAttendanceDetailListExport(Map<String, Object> parameter) throws Exception {
15
String startTime = parameter.get("startTime").toString();
16
String endTime = parameter.get("endTime").toString();
17
LabourBaseReq labourBaseReq = new LabourBaseReq();
18
labourBaseReq.setProjectId(Long.valueOf(parameter.get("projectId").toString()));
19
List<LabourBaseRes> labourBaseRes = labourBaseMapper.selectPageList(labourBaseReq);
20
//标题头日期
21
List<String> everyDay = null;
22
try {
23
everyDay = com.luban.common.utils.DateUtils.findEveryDay(startTime, endTime);
24
} catch (Exception e) {
25
throw new RuntimeException(e);
26
}
27
List<String> finalEveryDay = everyDay;
28
XSSFWorkbook workbook = new XSSFWorkbook();
29
//根据班组分租
30
Map<Long, List<LabourBaseRes>> map = labourBaseRes.stream().collect(Collectors.groupingBy(LabourBaseRes::getOrganId));
31
map.forEach((key, value) -> {
32
//sheet名称设置
33
Sheet sheet = workbook.createSheet(value.get(0).getProjectName() + "-" + value.get(0).getOrganName() + "-" + "考勤明细");
34
//创建第0行
35
Row sheetTitle = sheet.createRow(0);
36
Cell sheetTitleCell = sheetTitle.createCell(0);
37
//合并第0行相关的格子
38
CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 5 + finalEveryDay.size());
39
sheet.addMergedRegion(mergedRegion);
40
//标题插入
41
sheetTitleCell.setCellValue(value.get(0).getEnterpriseName() + "-" + value.get(0).getProjectName() + "-" + value.get(0).getOrganName()
42
+ startTime + "~" + endTime);
43
//创建第1行
44
Row headerRow = sheet.createRow(1);
45
//标题头设置
46
List<String> sheetTitles = new ArrayList<>();
47
sheetTitles.add("项目名称");
48
sheetTitles.add("人员姓名");
49
sheetTitles.add("班组");
50
sheetTitles.add("工种");
51
sheetTitles.add("类型");
52
sheetTitles.add("日期");
53
for (int i = 0; i < sheetTitles.size(); i) {
54
Cell headerCell = headerRow.createCell(i);
55
headerCell.setCellValue(sheetTitles.get(i));
56
}
57
//合并标题头两行
58
for (int i = 0; i < 5; i) {
59
CellRangeAddress mergedRegion2 = new CellRangeAddress(1, 2, i, i);
60
sheet.addMergedRegion(mergedRegion2);
61
}
62
//日期合并
63
CellRangeAddress mergedRegion1 = new CellRangeAddress(1, 1, 5, finalEveryDay.size() + 4);
64
sheet.addMergedRegion(mergedRegion1);
65
//创建第2行(日期)
66
//日期插入
67
Row dataRow = sheet.createRow(2);
68
//列
69
for (int j = 5; j < finalEveryDay.size() + 5; j) {
70
Cell dataCell = dataRow.createCell(j);
71
dataCell.setCellValue(finalEveryDay.get(j - 5));
72
}
73
//两个人以上的数据插入
74
if (value.size() > 1) {
75
int rowNum  = 3;
76
for (LabourBaseRes item : value) {
77
//创建第3行(上班时间)
78
//合并数据标题两行
79
Row workRow = sheet.createRow(rowNum);
80
List<String> headTitle = new ArrayList<>();
81
headTitle.add(item.getWorkerName());
82
headTitle.add(item.getProjectName());
83
headTitle.add(item.getOrganName());
84
headTitle.add(item.getProfessionName());
85
for (int i = 0; i < 4; i) {
86
//列
87
//数据插入
88
Cell cell = workRow.createCell(i);
89
cell.setCellValue(headTitle.get(i));
90
}
91
//合并这一行的相关数据
92
for (int j = 0; j < 4; j++) {
93
//列
94
CellRangeAddress mergedRegion3 = new CellRangeAddress(rowNum, rowNum + 1, j, j);
95
sheet.addMergedRegion(mergedRegion3);
96
}
97
//上下班打卡时间填充
98
//获取数据
99
List<String> workTime = new ArrayList<>();
100
List<String> offWorkTime = new ArrayList<>();
101
for (String temp : finalEveryDay) {
102
Map<String, Object> param = new HashMap<>();
103
param.put("labourId", item.getId());
104
param.put("ioDirection", NumberConstants.ONE);
105
param.put("time", temp);
106
String time1 = labourIoRecordMapper.selectAttendanceTime(param);
107
//上班时间
108
if (Objects.isNull(time1)) {
109
workTime.add("无");
110
} else {
111
workTime.add(time1.substring(11, 16));
112
}
113
param.put("ioDirection", NumberConstants.TWE);
114
String time2 = labourIoRecordMapper.selectAttendanceTime(param);
115
//下班时间
116
if (Objects.isNull(time2) || Objects.equals(workTime.get(workTime.size() - 1), "0")) {
117
offWorkTime.add("无");
118
} else {
119
offWorkTime.add(time2.substring(11, 16));
120
}
121
}
122
//上下班时间及其对应的时间数据
123
//上班行创建
124
for (int j = 5; j < finalEveryDay.size() + 5; j++) {
125
Cell workCell = workRow.createCell(4);
126
workCell.setCellValue("上班时间");
127
Cell dataCell1 = workRow.createCell(j);
128
dataCell1.setCellValue(workTime.get(j - 5));
129
}
130
//创建第4行(下班行创建)
131
Row offWorkRow = sheet.createRow(rowNum + 1);
132
for (int j = 5; j < finalEveryDay.size() + 5; j++) {
133
Cell offWorkCell = offWorkRow.createCell(4);
134
offWorkCell.setCellValue("下班时间");
135
Cell dataCell1 = offWorkRow.createCell(j);
136
dataCell1.setCellValue(offWorkTime.get(j - 5));
137
}
138
rowNum = rowNum + 2;
139
}
140
} else {
141
//创建第3行(上班时间)
142
//合并数据标题两行
143
Row workRow = sheet.createRow(3);
144
List<String> headTitle = new ArrayList<>();
145
headTitle.add(value.get(0).getWorkerName());
146
headTitle.add(value.get(0).getProjectName());
147
headTitle.add(value.get(0).getOrganName());
148
headTitle.add(value.get(0).getProfessionName());
149
for (int i = 0; i < 4; i) {
150
//列
151
//数据插入
152
Cell cell = workRow.createCell(i);
153
cell.setCellValue(headTitle.get(i));
154
}
155
//合并这一行的相关数据
156
for (int j = 0; j < 4; j) {
157
//列
158
CellRangeAddress mergedRegion3 = new CellRangeAddress(3, 4, j, j);
159
sheet.addMergedRegion(mergedRegion3);
160
}
161
//上下班打卡时间填充
162
//获取数据
163
List<String> workTime = new ArrayList<>();
164
List<String> offWorkTime = new ArrayList<>();
165
for (String temp : finalEveryDay) {
166
Map<String, Object> param = new HashMap<>();
167
param.put("labourId", value.get(0).getId());
168
param.put("ioDirection", NumberConstants.ONE);
169
param.put("time", temp);
170
String time1 = labourIoRecordMapper.selectAttendanceTime(param);
171
//上班时间
172
if (Objects.isNull(time1)) {
173
workTime.add("无");
174
} else {
175
workTime.add(time1.substring(11, 16));
176
}
177
param.put("ioDirection", NumberConstants.TWE);
178
String time2 = labourIoRecordMapper.selectAttendanceTime(param);
179
//下班时间
180
if (Objects.isNull(time2) || Objects.equals(workTime.get(workTime.size() - 1), "0")) {
181
offWorkTime.add("无");
182
} else {
183
offWorkTime.add(time2.substring(11, 16));
184
}
185
}
186
//上下班时间及其对应的时间数据
187
//上班行创建
188
Cell workCell = workRow.createCell(4);
189
workCell.setCellValue("上班时间");
190
for (int j = 5; j < finalEveryDay.size() + 5; j++) {
191
Cell dataCell1 = workRow.createCell(j);
192
dataCell1.setCellValue(workTime.get(j - 5));
193
}
194
//创建第4行(下班行创建)
195
Row offWorkRow = sheet.createRow(4);
196
Cell offWorkCell = offWorkRow.createCell(4);
197
offWorkCell.setCellValue("下班时间");
198
for (int j = 5; j < finalEveryDay.size() + 5; j++) {
199
Cell dataCell1 = offWorkRow.createCell(j);
200
dataCell1.setCellValue(offWorkTime.get(j - 5));
201
}
202
}
203
});
204
File path = File.createTempFile("考勤明细", ".xlsx");
205
ByteArrayOutputStream os = new ByteArrayOutputStream();
206
try {
207
workbook.write(os);
208
} catch (IOException e) {
209
e.printStackTrace();
210
}
211
byte[] content = os.toByteArray();
212
File file = FileUtils.byteToFile(content, path.getPath());
213
return MinIOUtil.upload(file, "labourAttendanceDetailListExport");
214
}