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 CustomSheetWriteHandler implements SheetWriteHandler {
|
|
private static final Logger LOGGER = LoggerFactory.getLogger(CustomSheetWriteHandler.class);
|
|
@Override
|
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
|
|
}
|
|
@Override
|
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
|
LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
|
|
// 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
|
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2000, 11, 11);
|
CellRangeAddressList cellRangeAddressList1 = new CellRangeAddressList(1, 2000, 4, 4);
|
CellRangeAddressList cellRangeAddressList2 = new CellRangeAddressList(1, 2000, 6, 6);
|
CellRangeAddressList cellRangeAddressList3 = new CellRangeAddressList(1, 2000, 13, 13);
|
CellRangeAddressList cellRangeAddressList4 = new CellRangeAddressList(1, 2000, 14, 14);
|
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
|
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"男", "女", "未知"});
|
DataValidationConstraint constraint1 = helper.createExplicitListConstraint(new String[] {"中共党员", "中共预备党员",
|
"共青团员", "民革党员", "民盟盟员", "民建会员", "农工党党员", "致公党党员", "九三学社社员", "台盟盟员", "无党派人士", "群众"});
|
DataValidationConstraint constraint2 = helper.createExplicitListConstraint(new String[] {"本地", "外地"});
|
DataValidationConstraint constraint3 =
|
helper.createExplicitListConstraint(new String[] {"未婚", "已婚", "初婚", "再婚", "复婚", "丧偶", "离婚", "未说明的婚育状况"});
|
DataValidationConstraint constraint4 =
|
helper.createExplicitListConstraint(new String[] {"很好", "较好", "一般", "较差", "很差"});
|
DataValidation dataValidation = helper.createValidation(constraint2, cellRangeAddressList);
|
DataValidation dataValidation1 = helper.createValidation(constraint, cellRangeAddressList1);
|
DataValidation dataValidation2 = helper.createValidation(constraint1, cellRangeAddressList2);
|
DataValidation dataValidation3 = helper.createValidation(constraint3, cellRangeAddressList3);
|
DataValidation dataValidation4 = helper.createValidation(constraint4, cellRangeAddressList4);
|
|
writeSheetHolder.getSheet().addValidationData(dataValidation);
|
writeSheetHolder.getSheet().addValidationData(dataValidation1);
|
writeSheetHolder.getSheet().addValidationData(dataValidation2);
|
writeSheetHolder.getSheet().addValidationData(dataValidation3);
|
writeSheetHolder.getSheet().addValidationData(dataValidation4);
|
}
|
}
|
