package com.ycl.jxkg.excel;
|
|
import com.alibaba.excel.metadata.Head;
|
import com.alibaba.excel.metadata.data.WriteCellData;
|
import com.alibaba.excel.write.handler.CellWriteHandler;
|
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
|
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
|
import java.util.List;
|
|
/**
|
* 设置easy-excel下拉处理器
|
*
|
* @author：xp
|
* @date：2024/3/15 17:27
|
*/
|
public class SelectExcel implements CellWriteHandler {
|
|
/**
|
* 题目类型
|
*/
|
private String[] questionTypeList = {"单选题", "多选题", "判断题"};
|
/**
|
* 课目
|
*/
|
private List<String> subjectNameList;
|
/**
|
* 选项下拉数据
|
*/
|
private String[] optionList = {"A", "B", "C", "D", "E", "F", "G", "H"};
|
|
public SelectExcel(List subjectNameList) {
|
this.subjectNameList = subjectNameList;
|
}
|
|
@Override
|
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
|
// 获取sheet对象
|
Sheet sheet = writeSheetHolder.getSheet();
|
|
|
// 获取数据校验helper，excel的下拉就是通过数据校验设置
|
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
|
|
// 设置题目下拉范围，第一列，1500行内
|
CellRangeAddressList questionTypeRange = new CellRangeAddressList(1, 1500, 0, 0);
|
// 添加题目下拉
|
DataValidationConstraint questionConstraint = dataValidationHelper.createExplicitListConstraint(questionTypeList);
|
DataValidation questionValidation = dataValidationHelper.createValidation(questionConstraint, questionTypeRange);
|
|
setValidation(questionValidation);
|
sheet.addValidationData(questionValidation);
|
|
|
// 设置课目下拉范围，第2列，1500行内
|
CellRangeAddressList subjectTypeRange = new CellRangeAddressList(1, 1500, 1, 1);
|
// 添加课目下拉
|
DataValidationConstraint subjectConstraint = dataValidationHelper.createExplicitListConstraint(subjectNameList.toArray((new String[0])));
|
DataValidation subjectValidation = dataValidationHelper.createValidation(subjectConstraint, subjectTypeRange);
|
|
setValidation(subjectValidation);
|
sheet.addValidationData(subjectValidation);
|
|
// 设置选项下拉范围，第4列，1500行内
|
CellRangeAddressList optionRange = new CellRangeAddressList(2, 1500, 3, 3);
|
// 添加选项下拉
|
DataValidationConstraint optionConstraint = dataValidationHelper.createExplicitListConstraint(optionList);
|
DataValidation optionValidation = dataValidationHelper.createValidation(optionConstraint, optionRange);
|
|
setValidation(optionValidation);
|
sheet.addValidationData(optionValidation);
|
|
// 设置选项下拉范围，第4列，1500行内
|
CellRangeAddressList answerRange = new CellRangeAddressList(2, 1500, 5, 5);
|
// 添加选项下拉
|
DataValidationConstraint answerConstraint = dataValidationHelper.createExplicitListConstraint(optionList);
|
DataValidation answerValidation = dataValidationHelper.createValidation(answerConstraint, answerRange);
|
setValidation(answerValidation);
|
sheet.addValidationData(answerValidation);
|
|
// 难道1-5
|
String[] difficultList = new String[]{"1", "2", "3", "4", "5"};
|
CellRangeAddressList difficultRange = new CellRangeAddressList(2, 1500, 8, 8);
|
// 添加选项下拉
|
DataValidationConstraint difficultConstraint = dataValidationHelper.createExplicitListConstraint(difficultList);
|
DataValidation difficultValidation = dataValidationHelper.createValidation(difficultConstraint, difficultRange);
|
setValidation(difficultValidation);
|
sheet.addValidationData(difficultValidation);
|
}
|
private DataValidation setValidation(DataValidation dataValidation){
|
dataValidation.setEmptyCellAllowed(false);
|
dataValidation.setSuppressDropDownArrow(true);
|
dataValidation.setShowErrorBox(true);
|
//设置输入错误提示信息
|
dataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值!");
|
return dataValidation;
|
}
|
}
|
