代码需要下载一个模板，并且有两列数据是需要有级联效果，类似选择第一列选择山东，第二列只能选择济南、青岛，第一列选择河南，第二列只能选择郑州、洛阳这样的需求
public ResponseEntity<Resource> downloadSecondTemplate(@RequestParam String cycleId) throws IOException {
TemplateEnum selectedTemplate = TemplateEnum.valueOf("TEAM_PERSONNEL");
String templatePath = selectedTemplate.getTemplatePath();
Resource resource = resourceLoader.getResource("classpath:" + templatePath);
String dataSourceSheetName = "sheet1";
try (InputStream inputStream = resource.getInputStream();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
Map<String, List<String>> data = new HashMap<>();
data.put("山东",Arrays.asList("济南","青岛"));
data.put("河南",Arrays.asList("郑州 ","洛阳"));
Workbook workbook = WorkbookFactory.create(inputStream);
// 创建一个隐藏的sheet页，写入数据用于构建名称管理器
Sheet dataSourceSheet = workbook.createSheet(dataSourceSheetName);
workbook.setSheetHidden(workbook.getSheetIndex(dataSourceSheet), true);
Row headerRow = dataSourceSheet.createRow(0);
String[] firstValidationArray = data.keySet().toArray(new String[0]);
boolean firstTime = true;
int columnIndex = 0;
// 构造名称管理器数据源
for (String key : data.keySet()) {
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(key);
if (data.get(key) == null || data.get(key).size() == 0) {
continue;
}
List<String> values = data.get(key);
int dataRowIndex = 1;
for (String value : values) {
Row row = firstTime ? dataSourceSheet.createRow(dataRowIndex) : dataSourceSheet.getRow(dataRowIndex);
if (row == null) {
row = dataSourceSheet.createRow(dataRowIndex);
}
row.createCell(columnIndex).setCellValue(value);
dataRowIndex++;
}
// 构造名称管理器
String range = buildRange(columnIndex, 2, values.size());
Name name = workbook.createName();
name.setNameName(key);
String formula = dataSourceSheetName + "!" + range;
name.setRefersToFormula(formula);
columnIndex++;
firstTime = false;
}
Sheet assetSheet = workbook.getSheetAt(0);
// 第一级设置DataValidation
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) assetSheet);
DataValidationConstraint firstConstraint = dvHelper.createExplicitListConstraint(firstValidationArray);
CellRangeAddressList firstRangeAddressList = new CellRangeAddressList(2, 2 << 19 - 1, 11, 11);
DataValidation firstDataValidation = dvHelper.createValidation(firstConstraint, firstRangeAddressList);
firstDataValidation.setSuppressDropDownArrow(true);
assetSheet.addValidationData(firstDataValidation);
// 剩下的层级设置DataValidation
String formulaString = buildFormulaString("L", 2);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList regions = new CellRangeAddressList(1, 2 << 19 - 1, 13, 13);
XSSFDataValidation dataValidationList = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
dataValidationList.setSuppressDropDownArrow(true);
assetSheet.addValidationData(dataValidationList);
workbook.write(outputStream);
// 构建响应实体，将填充后的Excel文件作为响应
byte[] fileContent = outputStream.toByteArray();
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(fileContent);
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + selectedTemplate.getTemplateName())
.contentType(MediaType.APPLICATION_OCTET_STREAM)
.body(new InputStreamResource(byteArrayInputStream));
} catch (IOException e) {
// 处理异常
e.printStackTrace();
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
}
}
private String buildRange(int offset, int startRow, int rowCount) {
char start = (char) ('A' + offset);
return "$" + start + "$" + startRow + ":$" + start + "$" + (startRow + rowCount - 1);
}
private String buildFormulaString(String offset, int rowNum) {
return "INDIRECT($" + offset + (rowNum) + ")";
}
参考资料：http://www.manongjc.com/detail/57-iqvnluydvcfeobl.html