import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.NoArgsConstructor;
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.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;
import java.util.Map;
/**
* @author leishen
*/
@NoArgsConstructor
public class FreezeAndFilter implements SheetWriteHandler {
/**
* 默认一个sheet页的长度
*/
private Integer firstRow =0;
/**
* 默认一个sheet页的长度
*/
private Integer lastRow =65535;
private Map<Integer,String []> selectMap;
/**
* 设置下拉框
* @param firstRow 开始行号
* @param lastRow 结束行号
* @param selectMap 下拉框选择 列号+下拉框选项
*/
public FreezeAndFilter(Integer firstRow, Integer lastRow, Map<Integer,String []> selectMap){
this.firstRow = firstRow;
this.lastRow = lastRow;
this.selectMap = selectMap;
}
/**
* 设置下拉框
* @param firstRow 开始行号
* @param selectMap 下拉框选择 列号+下拉框选项
*/
public FreezeAndFilter(Integer firstRow,Map<Integer,String []> selectMap){
this.firstRow = firstRow;
this.selectMap = selectMap;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
if (!CollectionUtils.isEmpty(selectMap)) {
DataValidationHelper helper = sheet.getDataValidationHelper();
selectMap.forEach((colIndex, content)->{
DataValidationConstraint constraint = helper.createExplicitListConstraint(content);
CellRangeAddressList cellRangeAddressList=new CellRangeAddressList(firstRow,lastRow,colIndex,colIndex);
DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
}
}
具体使用:
//设置下拉选择
Map<Integer,String []> map= Maps.newHashMap();
map.put(3,new String []{"男","女"});
String[] ageArr = new String[99];
for (int i = 0; i < 99; i++) {
ageArr[i]=String.valueOf(i+1);
}
map.put(2,ageArr);
EasyExcel.write(out,StudentExport.class).registerWriteHandler(new FreezeAndFilter(2,51,map)).sheet("zyp").doWrite(studentList);