McBasicCmtySheetWriteHandler.java
3.23 KB
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
package com.chinagas.modules.schsf.utils.listener;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class McBasicCmtySheetWriteHandler implements SheetWriteHandler {
private List<String> selectDataList;
private int ls = 0;
public McBasicCmtySheetWriteHandler(List<String> selectDataList, int ls) {
this.selectDataList = selectDataList;
this.ls = ls;
}
/**
* 想实现Excel引用其他sheet页数据作为单元格下拉选项值,
* 需要重写该方法
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 构造下拉选项单元格列的位置,以及下拉选项可选参数值的map集合
Map<Integer, List<String>> selectParamMap = new HashMap<>();
selectParamMap.put(ls, selectDataList); // 假设下拉选项放在第一列
// 获取第一个sheet页
Sheet sheet = writeSheetHolder.getCachedSheet();
// 获取sheet页的数据校验对象
DataValidationHelper helper = sheet.getDataValidationHelper();
// 获取工作簿对象,用于创建存放下拉数据的字典sheet数据页
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 迭代索引,用于存放下拉数据的字典sheet命名
int index = 1;
for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {
// 设置存放下拉数据的字典sheet,并把这些sheet隐藏掉,这样用户交互更友好
String dictSheetName = "dict_hide_sheet" + index;
Sheet dictSheet = workbook.createSheet(dictSheetName);
// 隐藏字典sheet页
workbook.setSheetHidden(index++, true);
// 创建一个名为“dictSheetName”的名称,指向字典表中的数据区域
String refers = dictSheetName + "!$A$1:$A$" + (entry.getValue().size() + 1);
Name name = workbook.createName();
name.setNameName(dictSheetName);
name.setRefersToFormula(refers);
// 向字典sheet写数据,从第一行开始写
for (int i = 0; i < entry.getValue().size(); i++) {
Row row = dictSheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue(entry.getValue().get(i));
}
// 设置下拉列表覆盖的行数,从第一行开始到最后一行
CellRangeAddressList infoList = new CellRangeAddressList(1, Short.MAX_VALUE, entry.getKey(), entry.getKey());
// 创建下拉列表约束
DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
DataValidation dataValidation = helper.createValidation(constraint, infoList);
sheet.addValidationData(dataValidation);
index++;
}
}
}