提交花城代码

mitao

2025-02-21 31573d6180d15ef65ed0df9c2732495f40b12663

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
package com.panzhihua.common.excel;
 
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
 
/**
 * 自定义拦截器.对第一列第一行和第二行的数据新增下拉框，显示 测试1 测试2
 *
 * @author Jiaju Zhuang
 */
public class PEXCustomSheetWriteHandler implements SheetWriteHandler {
 
    private static final Logger LOGGER = LoggerFactory.getLogger(PEXCustomSheetWriteHandler.class);
 
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
 
    }
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
 
        // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
        CellRangeAddressList cellRangeAddressList1 = new CellRangeAddressList(1, 5000, 3, 3);
        CellRangeAddressList cellRangeAddressList2 = new CellRangeAddressList(1, 5000, 4, 4);
        CellRangeAddressList cellRangeAddressList3 = new CellRangeAddressList(1, 5000, 25, 25);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint1 = helper.createExplicitListConstraint(new String[] {"中共党员", "中共预备党员",
            "共青团员", "民革党员", "民盟盟员", "民建会员", "农工党党员", "致公党党员", "九三学社社员", "台盟盟员", "无党派人士", "群众"});
        DataValidationConstraint constraint2 = helper.createExplicitListConstraint(new String[] {"是", "否"});
        DataValidationConstraint constraint3 = helper.createExplicitListConstraint(new String[] {"本地", "外地"});
        DataValidation dataValidation1 = helper.createValidation(constraint1, cellRangeAddressList1);
        DataValidation dataValidation2 = helper.createValidation(constraint2, cellRangeAddressList2);
        DataValidation dataValidation3 = helper.createValidation(constraint3, cellRangeAddressList3);
 
        writeSheetHolder.getSheet().addValidationData(dataValidation1);
        writeSheetHolder.getSheet().addValidationData(dataValidation2);
        writeSheetHolder.getSheet().addValidationData(dataValidation3);
    }
}