使用基于 java 的 POI 操作，限制 excel 导出某些单元格的可编辑性和单元格下拉列表的有效性。

//设置需要锁定的头部的样式
XSSFCellStyle lockstyle = workbook.createCellStyle();
lockstyle.setLocked(true);//设置锁定
XSSFFont f = workbook.createFont();
f.setFontHeightInPoints((short) 14);//字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
lockstyle.setFont(f);
//设置不需要锁定的内容的样式
XSSFCellStyle unlockStyle=workbook.createCellStyle();
unlockStyle.setLocked(false);//设置未锁定
//写头部数据,这里固定前两行为元素名和备注
for (int rowNum = 0; rowNum < 2; rowNum++) {
Row row = sheet.createRow(rowNum);//创建行
for (int cellNum = 0; cellNum < formComponents.size(); cellNum++) {
sheet.setDefaultColumnStyle(cellNum, unlockStyle);//设置这一列默认为可编辑
Cell cell = row.createCell(cellNum);//创建单元格
FormComponent component = formComponents.get(cellNum);
if (rowNum == 0){
//第一行是元素名
cell.setCellValue(component.getFiledName());
cell.setCellStyle(lockstyle);//将需要上锁的单元格进行锁定
//判断当前列是否是下拉框-2、单选框-3
if (component.getFiledInputType() == FormComponent.TYPE_SELECT ||
component.getFiledInputType() == FormComponent.TYPE_RADIO){
String dicCode = component.getDicCode();//获取到字典标识
if (StringUtils.isNotBlank(dicCode)){
List<Dict> dicts = new ArrayList<Dict>();
List<String> dictValus = new ArrayList<String>();
// 存在字典转义
if (StringUtils.isNotBlank(dicCode)) {
if(dicCode.contains("type=")){
//url形式普通下拉框
dicCode = dicCode.split("type=")[1];
}
dicts = ExchHelper.dictMap.get(dicCode);
if (dicts == null) {
ExchHelper.dictMap = DictUtils.getAllDict();
dicts = ExchHelper.dictMap.get(dicCode);
}
if (dicts != null){
for (Dict dict : dicts) {
dictValus.add(dict.getDictValue());
}
//创建数组，长度为list的长度
String[] textlist = new String[dictValus.size()];
//list转化成数组，也可以看成是list复制到数组中
dictValus.toArray(textlist);
//设置Excel数据有效性
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(textlist);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
//四个参数分别是：起始行、终止行、起始列、终止列
addressList = new CellRangeAddressList(2, 1000, cellNum, cellNum);
validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
validation.setShowErrorBox(true);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
//数据有效性对象
sheet.addValidationData(validation);
}

}
}
}
}else if (rowNum == 1){
//第二行是备注
cell.setCellValue(StringUtils.isNotBlank(formComponents.get(cellNum).getFiledComment()) ? formComponents.get(cellNum).getFiledComment(): formComponents.get(cellNum).getFiledDesc());
cell.setCellStyle(lockstyle);//将需要上锁的单元格进行锁定
}
sheet.setColumnWidth(cellNum,20 * 256);
}
}