@Data
public class SpinnerWriteHandler implements SheetWriteHandler {
private String[] pidName;
private String[] name;
public SpinnerWriteHandler() {
}
public SpinnerWriteHandler(String[] pidName, String[] name) {
this.pidName = pidName;
this.name = name;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Map<Integer, String[]> map = new HashMap<>();
map.put(1, this.pidName);
map.put(2, this.name);
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
CellRangeAddressList addressList = new CellRangeAddressList(1, 3000, entry.getKey(), entry.getKey());
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
if (dataValidation instanceof XSSFDataValidation){
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
}
public void exportTemplate(HttpServletResponse response) throws Exception {
List<GamsApplicationAssetcardDTO> list = new ArrayList<>();
String[] pidNames = {"小学", "初中", "高中", "大学"};
String[] names = {"语文", "数学", "英语"};
List targetList = new ArrayList<>(list.size());
for (Object source : list) {
Object target = GamsApplicationAssetcardExcel.class.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel
.write(response.getOutputStream(), GamsApplicationAssetcardExcel.class)
.registerWriteHandler(new SpinnerWriteHandler(pidNames, names))
.sheet("测试")
.doWrite(list);
}