共计 3655 个字符，预计需要花费 10 分钟才能阅读完成。
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 数据
*/
@Service
public 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);
}
}