public class ProductCellWriteHandler implements SheetWriteHandler {
private Map<Integer,String[]> map = null;
private int index;
private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
public ProductCellWriteHandler(Map<Integer,String[]> map){
this.map = map;
this.index = 0;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// 需要设置下拉框的sheet页
String dictSheetName = "字典sheet";
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 数据字典的sheet页
Sheet dictSheet = workbook.createSheet(dictSheetName);
// 从第二个工作簿开始隐藏，为了用户的友好性，将字典sheet隐藏掉
this.index++;
// 设置隐藏
workbook.setSheetHidden(this.index, true);
// k 为存在下拉数据集的单元格下表 v为下拉数据集
map.forEach((k, v) -> {
int rowLen = v.length;
// 设置字典sheet页的值 每一列一个字典项
for (int i = 0; i < rowLen; i++) {
Row row = dictSheet.getRow(i);
if (row == null) {
row = dictSheet.createRow(i);
}
row.createCell(k).setCellValue(v[i]);
}
String excelColumn = getExcelColumn(k);
// 下拉框数据来源 eg:字典sheet!$B1:$B2
String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
// 创建可被其他单元格引用的名称
Name name = workbook.createName();
// 设置名称的名字
name.setNameName("dict" + k);
// 设置公式
name.setRefersToFormula(refers);
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + k);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
// validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
sheet.addValidationData(validation);
});
}
/**
