需求:
  1. 只有允许录入数据的部分可以编辑,其他单元格,包括说明文字所在行均不可编辑
    示例:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet名称");

HSSFCellStyle lockstyle = wb.createCellStyle();
lockstyle.setLocked(true);//设置锁定
lockstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());//设置上锁的单元格背景色

HSSFCellStyle unlockStyle=wb.createCellStyle();
unlockStyle.setLocked(false);//设置未锁定


for(int i=0;i<10;i++){
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(unlockStyle);//默认是锁定状态;将所有单元格设置为:未锁定;然后再对需要上锁的单元格单独锁定
if(j==1){//这里可以根据需要进行判断;我这就将第2列上锁了
cell.setCellStyle(lockstyle);//将需要上锁的单元格进行锁定
cell.setCellValue("上锁了");
}else{
cell.setCellValue("没上锁了");
}
}
}
//sheet添加保护,这个一定要否则光锁定还是可以编辑的
sheet.protectSheet("123456");
FileOutputStream os = new FileOutputStream("D:\\workbook.xls");
wb.write(os);
os.close();
  1. 由于涉及到一些字典值,比如性别(1-男 2-女),需要设置导出 excel 的下拉列表(有效性)
    示例:
//设置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);
  1. 综合示例:
//设置需要锁定的头部的样式
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);
}
}