AbstractXlsxView导出带有下拉框列的Excel

AbstractXlsxView 在导出Excel数据的时候，遇到了某一列要设置为下拉框选择，前辈们在网上都是简单的例子，没有下拉框的设置，索性自己折腾一下。Controller:… @Autowired private ActivityMonitorService monitorService;… @GetMapping("/downloadExcel") public ModelAndView downloadExcel() { ExcelView excelView = new ExcelView(); // 该map组装excelView用到的数据及表头 Map<String, Object> map = monitorService.uploadExcelParam(); return new ModelAndView(excelView, map); }… AbstractXlsxView实现类：import com.jason.dto.ActivityMonitorDto;import org.apache.commons.lang.StringUtils;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.usermodel.XSSFDataValidation;import org.springframework.stereotype.Service;import org.springframework.web.servlet.view.document.AbstractXlsxView;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.net.URLEncoder;import java.util.List;import java.util.Map;/** * 导出xlsx数据 /@Servicepublic class ExcelView extends AbstractXlsxView { private final int MAX_ROW = 65535; @Override protected void buildExcelDocument(Map<String, Object> map, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { String name = (String) map.get(“fileName”); if (StringUtils.isEmpty(name)) { name = “Template”; } String fileName = name + “.xlsx”; // 火狐浏览器乱码解决 String agent = request.getHeader(“User-Agent”); if (agent != null) { if (“firefox”.contains(agent.toLowerCase())) { response.setHeader(“content-disposition”, String.format(“attachment;filename=utf-8’zh_cn’%s”, URLEncoder.encode(fileName,“utf-8”))); } else { response.setHeader(“content-disposition”, “attachment;filename=” + URLEncoder.encode(fileName, “utf-8”)); } } response.setCharacterEncoding(“UTF-8”); response.setContentType(“application/ms-excel;charset=utf-8”); Sheet sheet = workbook.createSheet(); String[] headerTitle = (String[]) map.get(“headers”); Row header = sheet.createRow(0); for (int i = 0; i < headerTitle.length; i++) { header.createCell(i).setCellValue(headerTitle[i]); } // 需要导出的数据 List<ActivityMonitorDto> list = (List<ActivityMonitorDto>) map.get(“content”); for (int i = 0; i < list.size(); i++) { Row row = sheet.createRow(i + 1); row.createCell(0).setCellValue(list.get(i).getActName()); row.createCell(1).setCellValue(list.get(i).getActId()); // 第三列填写默认值 row.createCell(2).setCellValue(list.get(i).getReleaseTime()); row.createCell(3).setCellValue(list.get(i).getPointPositionName()); // 第五列填写默认值 row.createCell(4).setCellValue(list.get(i).getAttributeName()); row.createCell(5).setCellValue(list.get(i).getUseChannelName()); row.createCell(6).setCellValue(list.get(i).getRemark()); } // 普通格式组装完毕，开始设置下拉框 DataValidationHelper helper = sheet.getDataValidationHelper(); // 下拉框的option数据 String[] pointPositionArray = (String[]) map.get(“pointPositionArray”); String[] channelArray = (String[]) map.get(“channelArray”); // 第三和第五列设置为下拉框 DataValidationConstraint constraintPosition = helper.createExplicitListConstraint(pointPositionArray); CellRangeAddressList regionsPosotion = new CellRangeAddressList(1, MAX_ROW, 3, 3); DataValidation positionData = helper.createValidation(constraintPosition, regionsPosotion); DataValidationConstraint constraintChannel = helper.createExplicitListConstraint(channelArray); CellRangeAddressList regionChannel = new CellRangeAddressList(1, MAX_ROW, 5, 5); DataValidation channelData = helper.createValidation(constraintChannel, regionChannel); //处理Excel兼容性问题 if(positionData instanceof XSSFDataValidation) { positionData.setSuppressDropDownArrow(true); positionData.setShowErrorBox(true); }else { positionData.setSuppressDropDownArrow(false); } if(channelData instanceof XSSFDataValidation) { channelData.setSuppressDropDownArrow(true); channelData.setShowErrorBox(true); }else { channelData.setSuppressDropDownArrow(false); } sheet.addValidationData(positionData); sheet.addValidationData(channelData); }}