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
|
package com.sdstc.anta.material.utils.excel.handlers_with_new_scheme;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class DropDownSheetHandler implements SheetWriteHandler {
private static final String DROP_BOX_HIDDEN_SHEET = "DROP_BOX_HIDDEN_SHEET";
// 下拉框值
private final HashMap<Integer, String[]> dropDownMap;
public DropDownSheetHandler(HashMap<Integer, String[]> dropDownMap) {
this.dropDownMap = dropDownMap;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
// region 在另一张Sheet中准备下拉数据
// 因为行只能创建一次，所以需要计算出最大行
int maxRows = Integer.MIN_VALUE;
for (String[] arr : dropDownMap.values()) {
if (arr.length > maxRows) {
maxRows = arr.length;
}
}
Sheet dropBoxHiddenSheet = workbook.createSheet(DROP_BOX_HIDDEN_SHEET);
for (int i = 0; i < maxRows; i++) {
Row row = dropBoxHiddenSheet.createRow(i);
for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
if (i < entry.getValue().length) {
row.createCell(entry.getKey()).setCellValue(entry.getValue()[i]);
}
}
}
// 隐藏Sheet
if (!workbook.isSheetHidden(workbook.getSheetIndex(DROP_BOX_HIDDEN_SHEET))) {
workbook.setSheetHidden(workbook.getSheetIndex(DROP_BOX_HIDDEN_SHEET), true);
}
// endregion
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
String excelTag = getExcelTag(entry.getKey(), entry.getValue().length);
DataValidationConstraint dataValidationConstraint = helper.createFormulaListConstraint(
String.format("%s!%s", DROP_BOX_HIDDEN_SHEET, excelTag));
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65536, entry.getKey(), entry.getKey());
DataValidation dataValidation = helper.createValidation(dataValidationConstraint, cellRangeAddressList);
// 设置错误提示
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("错误提示", "请选择下拉框内选项!");
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
private String getExcelTag(int row, int length) {
if (row < 0) {
throw new RuntimeException("Wrong RowIndex Show Bigger Then 0");
}
int leftRow = row / 26;
int rightRow = row % 26;
String leftRowChar = leftRow == 0 ? "" : String.valueOf((char) (65 + leftRow - 1));
String rightRowChar = String.valueOf((char) (65 + rightRow));
return String.format("$%s%s$%d:$%s%s$%d", leftRowChar, rightRowChar, 1, leftRowChar, rightRowChar, length);
}
}
|
