1.原始模板
2.导出模板,下拉框为数据库中得到动态数据
public void downloadTemplate(HttpServletResponse response) throws IOException {
// 所有部门
List<String, String> departments = expertManageMapper.selectAllDepartment();
//所有职位
List<String, String> posts = expertManageMapper.selectAllPost();
//所有级别
List<String, String> levels = expertManageMapper.selectAllLevel();
// 创建或读取已有的Excel模板
InputStream templateFileStream = new ClassPathResource("/templates/excel/expert_template.xlsx").getInputStream();
Workbook workbook = new XSSFWorkbook(templateFileStream);
Sheet sheet = workbook.getSheetAt(0);
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
// 工作表的第二行索引
int firstRowNum = 1;
// 工作表的最后一行索引
int lastRowNum = 10000;
//添加需要校验的单元格,startCol和endCol根据模板中列进行修改
// 科室校验
applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 3, 3), ArrayUtils.toArray(departments));
// 职位校验
applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 4, 4),ArrayUtils.toArray(posts));
// 等级校验
applyDataValidation(validationHelper, sheet, addressList(firstRowNum, lastRowNum, 5, 5), ArrayUtils.toArray(levels));
// 设置响应头信息
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String encodedFilename = UriUtils.encode("专家信息录入模板.xlsx", StandardCharsets.UTF_8);
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + encodedFilename);
try (ServletOutputStream outputStream = response.getOutputStream()) {
// 将修改后的Excel内容写入到输出流
workbook.write(outputStream);
} finally {
workbook.close();
}
}
/**
* 简化创建和应用数据验证的过程
*/
private void applyDataValidation(DataValidationHelper validationHelper, Sheet sheet, CellRangeAddressList addressList, String[] validationData) {
if (validationData.length > 0) {
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(validationData);
DataValidation validation = validationHelper.createValidation(constraint, addressList);
sheet.addValidationData(validation);
}
}
/**
* 创建CellRangeAddressList
*/
private CellRangeAddressList addressList(int startRow, int endRow, int startCol, int endCol) {
return new CellRangeAddressList(startRow, endRow, startCol, endCol);
}